I am working with a project in which the cash-flows (CF) are irregular. I've solved the below problem, but in a rather ungainly fashion. I am hopeful the Forum can offer a more elegant solution or two.
The project's periodic cash-flow are subject to a sharing arrangement in which the party in question as a much smaller share of the CFs after attaining payout. The periodic net CFs drive a before payout balance. Once the before payout (BPO) balance goes positive, the reversion of interest occurs and the party reverts to the lower share of the gross CFs (the APO CFs). The combination the the BPO and after payout (APO) shares make up the total return. However, a large negative CF can drive the BPO balance back to the positive. Herein lies the rub... not only does this mess with the calculations, the structure of the deal does not allow for a reversal of the share of project CF... once it hits payout the APO interests apply for the balance of the project.
This would not be such a big deal if the analysis was a single iteration. We are using a Monte Carlo simulation and when we run the model, these number change and the payout dates and amounts can vary widely.
My solution works but is the a solution that works within the formula in the "CashFlow" column (doesn't require columns G & H)?
I've used MrExcel's awesome MrExcelHtml to add the screen shots.
[TABLE]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Day[/TD]
[TD="align: right"]Balance[/TD]
[TD="align: right"]Cashflow[/TD]
[TD="align: right"]After Payout Net Cashflow[/TD]
[TD="align: right"]Before Payout Balance[/TD]
[TD="align: right"]Before Payout Net Cashflow[/TD]
[TD="align: right"]BPO Neg. Bal. Count[/TD]
[TD="align: right"]Count of BPO Neg. Bal. Count[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Balance Forward[/TD]
[TD="align: right"] 200,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]1[/TD]
[TD="align: right"] 270,000[/TD]
[TD="align: right"] (70,000)[/TD]
[TD="align: right"] (19,000)[/TD]
[TD="align: right"] 270,000[/TD]
[TD="align: right"] (70,000)[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]2[/TD]
[TD="align: right"] 220,000[/TD]
[TD="align: right"] 50,000[/TD]
[TD="align: right"] 8,000[/TD]
[TD="align: right"] 220,000[/TD]
[TD="align: right"] 50,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]3[/TD]
[TD="align: right"] 154,000[/TD]
[TD="align: right"] 66,000[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] 154,000[/TD]
[TD="align: right"] 66,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]4[/TD]
[TD="align: right"] 104,000[/TD]
[TD="align: right"] 50,000[/TD]
[TD="align: right"] 8,000[/TD]
[TD="align: right"] 104,000[/TD]
[TD="align: right"] 50,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]5[/TD]
[TD="align: right"] 6,000[/TD]
[TD="align: right"] 98,000[/TD]
[TD="align: right"] 14,000[/TD]
[TD="align: right"] 6,000[/TD]
[TD="align: right"] 98,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]6[/TD]
[TD="align: right"] (8,000)[/TD]
[TD="align: right"] 14,000[/TD]
[TD="align: right"] 14,000[/TD]
[TD="align: right"] (76,000)[/TD]
[TD="align: right"] 82,000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]7[/TD]
[TD="align: right"] 18,000[/TD]
[TD="align: right"] (26,000)[/TD]
[TD="align: right"] (26,000)[/TD]
[TD="align: right"] 20,000[/TD]
[TD="align: right"] (96,000)[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]8[/TD]
[TD="align: right"] 6,000[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] (46,000)[/TD]
[TD="align: right"] 66,000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]9[/TD]
[TD="align: right"] (6,000)[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] (112,000)[/TD]
[TD="align: right"] 66,000[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]10[/TD]
[TD="align: right"] (18,000)[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] (178,000)[/TD]
[TD="align: right"] 66,000[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Calcs[/B][/COLOR][/CENTER]
[TABLE="width: 85%"]
<tbody>[TR]
[TD][B]Worksheet Formulas[/B][TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H1[/TH]
[TD]="Count of "&G1[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B3[/TH]
[TD]=B2-C3[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C3[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G3=1,H3=1[/COLOR]),MAX([COLOR=Red]D3,E2[/COLOR]),IF([COLOR=Red]H3>1,D3,F3[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4[/TH]
[TD]=B3-C4[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C4[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G4=1,H4=1[/COLOR]),MAX([COLOR=Red]D4,E3[/COLOR]),IF([COLOR=Red]H4>1,D4,F4[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B5[/TH]
[TD]=B4-C5[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C5[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G5=1,H5=1[/COLOR]),MAX([COLOR=Red]D5,E4[/COLOR]),IF([COLOR=Red]H5>1,D5,F5[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B6[/TH]
[TD]=B5-C6[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C6[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G6=1,H6=1[/COLOR]),MAX([COLOR=Red]D6,E5[/COLOR]),IF([COLOR=Red]H6>1,D6,F6[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B7[/TH]
[TD]=B6-C7[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C7[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G7=1,H7=1[/COLOR]),MAX([COLOR=Red]D7,E6[/COLOR]),IF([COLOR=Red]H7>1,D7,F7[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B8[/TH]
[TD]=B7-C8[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C8[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G8=1,H8=1[/COLOR]),MAX([COLOR=Red]D8,E7[/COLOR]),IF([COLOR=Red]H8>1,D8,F8[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B9[/TH]
[TD]=B8-C9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C9[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G9=1,H9=1[/COLOR]),MAX([COLOR=Red]D9,E8[/COLOR]),IF([COLOR=Red]H9>1,D9,F9[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B10[/TH]
[TD]=B9-C10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C10[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G10=1,H10=1[/COLOR]),MAX([COLOR=Red]D10,E9[/COLOR]),IF([COLOR=Red]H10>1,D10,F10[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B11[/TH]
[TD]=B10-C11[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C11[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G11=1,H11=1[/COLOR]),MAX([COLOR=Red]D11,E10[/COLOR]),IF([COLOR=Red]H11>1,D11,F11[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B12[/TH]
[TD]=B11-C12[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C12[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G12=1,H12=1[/COLOR]),MAX([COLOR=Red]D12,E11[/COLOR]),IF([COLOR=Red]H12>1,D12,F12[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G3[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E3,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H3[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G3,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G4[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E4,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H4[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G4,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G5[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E5,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H5[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G5,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G6[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E6,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H6[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G6,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G7[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E7,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H7[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G7,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G8[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E8,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H8[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G8,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G9[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E9,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H9[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G9,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G10[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E10,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H10[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G10,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G11[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E11,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H11[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G11,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G12[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E12,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H12[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G12,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E3[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E2[/COLOR]),B2,E2[/COLOR])-F3[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E4[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E3[/COLOR]),B3,E3[/COLOR])-F4[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E5[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E4[/COLOR]),B4,E4[/COLOR])-F5[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E6[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E5[/COLOR]),B5,E5[/COLOR])-F6[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E7[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E6[/COLOR]),B6,E6[/COLOR])-F7[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E8[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E7[/COLOR]),B7,E7[/COLOR])-F8[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E9[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E8[/COLOR]),B8,E8[/COLOR])-F9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E10[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E9[/COLOR]),B9,E9[/COLOR])-F10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E11[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E10[/COLOR]),B10,E10[/COLOR])-F11[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E12[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E11[/COLOR]),B11,E11[/COLOR])-F12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
The project's periodic cash-flow are subject to a sharing arrangement in which the party in question as a much smaller share of the CFs after attaining payout. The periodic net CFs drive a before payout balance. Once the before payout (BPO) balance goes positive, the reversion of interest occurs and the party reverts to the lower share of the gross CFs (the APO CFs). The combination the the BPO and after payout (APO) shares make up the total return. However, a large negative CF can drive the BPO balance back to the positive. Herein lies the rub... not only does this mess with the calculations, the structure of the deal does not allow for a reversal of the share of project CF... once it hits payout the APO interests apply for the balance of the project.
This would not be such a big deal if the analysis was a single iteration. We are using a Monte Carlo simulation and when we run the model, these number change and the payout dates and amounts can vary widely.
My solution works but is the a solution that works within the formula in the "CashFlow" column (doesn't require columns G & H)?
I've used MrExcel's awesome MrExcelHtml to add the screen shots.
[TABLE]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Day[/TD]
[TD="align: right"]Balance[/TD]
[TD="align: right"]Cashflow[/TD]
[TD="align: right"]After Payout Net Cashflow[/TD]
[TD="align: right"]Before Payout Balance[/TD]
[TD="align: right"]Before Payout Net Cashflow[/TD]
[TD="align: right"]BPO Neg. Bal. Count[/TD]
[TD="align: right"]Count of BPO Neg. Bal. Count[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Balance Forward[/TD]
[TD="align: right"] 200,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]1[/TD]
[TD="align: right"] 270,000[/TD]
[TD="align: right"] (70,000)[/TD]
[TD="align: right"] (19,000)[/TD]
[TD="align: right"] 270,000[/TD]
[TD="align: right"] (70,000)[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]2[/TD]
[TD="align: right"] 220,000[/TD]
[TD="align: right"] 50,000[/TD]
[TD="align: right"] 8,000[/TD]
[TD="align: right"] 220,000[/TD]
[TD="align: right"] 50,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]3[/TD]
[TD="align: right"] 154,000[/TD]
[TD="align: right"] 66,000[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] 154,000[/TD]
[TD="align: right"] 66,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]4[/TD]
[TD="align: right"] 104,000[/TD]
[TD="align: right"] 50,000[/TD]
[TD="align: right"] 8,000[/TD]
[TD="align: right"] 104,000[/TD]
[TD="align: right"] 50,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]5[/TD]
[TD="align: right"] 6,000[/TD]
[TD="align: right"] 98,000[/TD]
[TD="align: right"] 14,000[/TD]
[TD="align: right"] 6,000[/TD]
[TD="align: right"] 98,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]6[/TD]
[TD="align: right"] (8,000)[/TD]
[TD="align: right"] 14,000[/TD]
[TD="align: right"] 14,000[/TD]
[TD="align: right"] (76,000)[/TD]
[TD="align: right"] 82,000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]7[/TD]
[TD="align: right"] 18,000[/TD]
[TD="align: right"] (26,000)[/TD]
[TD="align: right"] (26,000)[/TD]
[TD="align: right"] 20,000[/TD]
[TD="align: right"] (96,000)[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]8[/TD]
[TD="align: right"] 6,000[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] (46,000)[/TD]
[TD="align: right"] 66,000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]9[/TD]
[TD="align: right"] (6,000)[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] (112,000)[/TD]
[TD="align: right"] 66,000[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]10[/TD]
[TD="align: right"] (18,000)[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] 12,000[/TD]
[TD="align: right"] (178,000)[/TD]
[TD="align: right"] 66,000[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Calcs[/B][/COLOR][/CENTER]
[TABLE="width: 85%"]
<tbody>[TR]
[TD][B]Worksheet Formulas[/B][TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H1[/TH]
[TD]="Count of "&G1[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B3[/TH]
[TD]=B2-C3[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C3[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G3=1,H3=1[/COLOR]),MAX([COLOR=Red]D3,E2[/COLOR]),IF([COLOR=Red]H3>1,D3,F3[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4[/TH]
[TD]=B3-C4[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C4[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G4=1,H4=1[/COLOR]),MAX([COLOR=Red]D4,E3[/COLOR]),IF([COLOR=Red]H4>1,D4,F4[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B5[/TH]
[TD]=B4-C5[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C5[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G5=1,H5=1[/COLOR]),MAX([COLOR=Red]D5,E4[/COLOR]),IF([COLOR=Red]H5>1,D5,F5[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B6[/TH]
[TD]=B5-C6[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C6[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G6=1,H6=1[/COLOR]),MAX([COLOR=Red]D6,E5[/COLOR]),IF([COLOR=Red]H6>1,D6,F6[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B7[/TH]
[TD]=B6-C7[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C7[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G7=1,H7=1[/COLOR]),MAX([COLOR=Red]D7,E6[/COLOR]),IF([COLOR=Red]H7>1,D7,F7[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B8[/TH]
[TD]=B7-C8[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C8[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G8=1,H8=1[/COLOR]),MAX([COLOR=Red]D8,E7[/COLOR]),IF([COLOR=Red]H8>1,D8,F8[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B9[/TH]
[TD]=B8-C9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C9[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G9=1,H9=1[/COLOR]),MAX([COLOR=Red]D9,E8[/COLOR]),IF([COLOR=Red]H9>1,D9,F9[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B10[/TH]
[TD]=B9-C10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C10[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G10=1,H10=1[/COLOR]),MAX([COLOR=Red]D10,E9[/COLOR]),IF([COLOR=Red]H10>1,D10,F10[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B11[/TH]
[TD]=B10-C11[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C11[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G11=1,H11=1[/COLOR]),MAX([COLOR=Red]D11,E10[/COLOR]),IF([COLOR=Red]H11>1,D11,F11[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B12[/TH]
[TD]=B11-C12[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C12[/TH]
[TD]=IF([COLOR=Blue]AND([COLOR=Red]G12=1,H12=1[/COLOR]),MAX([COLOR=Red]D12,E11[/COLOR]),IF([COLOR=Red]H12>1,D12,F12[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G3[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E3,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H3[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G3,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G4[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E4,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H4[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G4,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G5[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E5,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H5[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G5,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G6[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E6,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H6[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G6,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G7[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E7,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H7[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G7,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G8[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E8,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H8[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G8,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G9[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E9,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H9[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G9,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G10[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E10,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H10[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G10,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G11[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E11,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H11[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G11,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G12[/TH]
[TD]=COUNTIF([COLOR=Blue]$E$3:$E12,"<=0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H12[/TH]
[TD]=COUNTIF([COLOR=Blue]$G$3:$G12,">0"[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E3[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E2[/COLOR]),B2,E2[/COLOR])-F3[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E4[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E3[/COLOR]),B3,E3[/COLOR])-F4[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E5[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E4[/COLOR]),B4,E4[/COLOR])-F5[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E6[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E5[/COLOR]),B5,E5[/COLOR])-F6[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E7[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E6[/COLOR]),B6,E6[/COLOR])-F7[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E8[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E7[/COLOR]),B7,E7[/COLOR])-F8[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E9[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E8[/COLOR]),B8,E8[/COLOR])-F9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E10[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E9[/COLOR]),B9,E9[/COLOR])-F10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E11[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E10[/COLOR]),B10,E10[/COLOR])-F11[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E12[/TH]
[TD]=IF([COLOR=Blue]ISBLANK([COLOR=Red]E11[/COLOR]),B11,E11[/COLOR])-F12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
