Enable editing

Select columns through Col L and widen the fields to better view field names.

Click Row 1 and make bold. 

Click on top left corner to select all. Go to Data>Sort>Sort by D_Organization (A-Z)

Click on Col H (M_Free_Construction). Add 4 columns to the left. Give them the same names as columns D-G, but change each name from ending in "_Old" to "_New".

Then copy and paste the following formulas into Row 2 for each of the new columns, as follows:

1. Col. H (D_PEPMFee_New): =IF(O2*1>DATE(2024,1,1),D2,(D2*(1+(0.8*0.032))))   [See footnote 1 for formula explanation]

2. Replace values that appear in bold orange font in the instruction line above with (a) the new CY (upcoming year) and (b) the CPI % of the passing CY.

3. Col I (D_PEPMValue_New): =H2*C2*12

4. Col. J (D_ValueofOTHERContracts_New): =IF(O2*1>DATE(2024,1,1),F2,(F2*(1+(0.8*0.032))))  [See footnote 1 for formula explanation]

5. Replace values that appear in bold orange font in the instruction line above with (a) the new CY (upcoming year) and (b) the CPI % of the passing CY.

6. Col K (D_AccountValue_New): =IF(I2=0,J2,I2)   [See footnote 2 for formula explanation]

7. Click on Cols H through K and select font Calibri and font size 11. It will already seem as if that is chosen, but you must reselect it so that the rows below Row 1 also conform to that font type and size. With Cols H through K still highlighted, click $ to format those cells for accounting (like currency). 

Fill Down formulas in Column H, I, J and K to end..

Select Col. N (M_Cycle). Add column to left. Double-check to be sure Col P is now "M_CPIBeginDate". {As a side note, the formulas in Col H and Col J will automatically change to reference Column P instead of Column O and that is what we want). 

Entitle New Col. N: "New Each Invoice Amount". 

Set formula of Col. N: =IF(O2="ANNUAL",K2,K2/4). Double check that Col. O is "M_Cycle". [See footnote 3 for formula explanation]

Click on Col N and format to Calibri 11 and $.

Fill formula in Column N Down to end.

Select Col Q (D_Date_Billing_Starts). Insert Col to left. Name new column: "Increase"

Set formula for Row 2 in new Col Q (Increase): =IF(K2=G2,"NO","YES")  [See footnote 4 for formula explanation]

Fill formula in Column Q Down to end.

Click to highlight Col Q. Format as Calibri 11. 

Format Column headings (i.e., field names) for Cols. N (New Each Invoice Amount), O (M_Cycle) and Q (Increase) so that fill is dark red and font is white. 

Format Col heading K (D_AccountValue_new) so that fill is blue (2nd blue from top).

Review the values in Col R (Income_Account_Master_DateBillingStarts) and format the fill of these cells as follows: If the cells have values other than the first of the coming calendar year, make fill bright red and font as white. 

• This is to alert B.O. that billing for these accounts does not start Jan 1., but later in the year. 

• Please notify the Account Rel. Mgrs for any accounts with dates in Col R that those accounts will receive their first invoice on those dates. 

Select Col A. Make it about twice the width of other columns and Set as Wrap Text. 

Save document and send to B.O.