Hello guys 
,
So I have an Excel where I put a Start date / end date and an amount and it fills up a table for me with the estimated amount for the # of months
There's a formula that makes that the amounts are like a parabola
I attached 2 examples:
This one is working great but there's a problem.
I would like to add a pre-construction start.
Let's say the project is 100$ pre-construction start is 4 month for 10$ and the project itself is 12 months
I would like to have the first 4 month at 2.5$ linear and start the parabola at month 5 for the remaining 8 months
The formula should be able to adapt, for an other project the pre-construction could be 7 months let's say.
Is that something that possible to do ?
Thanks a lot for whoever will take the time to help
,So I have an Excel where I put a Start date / end date and an amount and it fills up a table for me with the estimated amount for the # of months
There's a formula that makes that the amounts are like a parabola
I attached 2 examples:
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B4 | B4 | =DATEDIF(B2,B3,"m")+1 |
| D9 | D9 | =ACOS(1-2*D11)/PI() |
| C17:C32 | C17 | =IF(F17="","",IF(E17/B$4<D$13,+$E17*D$9/D$13,+(D$9+($E17/B$4-D$13)/(1-D$13)*(1-D$9))*B$4)) |
| D17:D32 | D17 | =IF(C17="","",YEAR(F17)+MONTH(F17)/100) |
| F17:F32 | F17 | =IF(E17<=B$4,EOMONTH(B$2,E17-1),"") |
| G17 | G17 | =+J$16*B17 |
| H17:H32 | H17 | =IF(A17="","",+B$7*A17) |
| G18:G32 | G18 | =IF(B18="","",+B$7*B18) |
| A18:A32 | A18 | =IF(C18>B$4,"",(1-COS($C18/$B$4*PI()))/2*(1-$D$12)+1*$D$12*$C18/$B$4) |
| B18:B32 | B18 | =IF(C18>B$4,"",+A18-A17) |
| MR Excel.xls | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | ||||||||||
| 2 | Start Date | January 1, 2020 | ||||||||
| 3 | End Date | December 31, 2020 | ||||||||
| 4 | Project Duration | 12 | ||||||||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | Contract Value | $ 10,000,000.00 | ||||||||
| 8 | ||||||||||
| 9 | BreakPoint | 0.564094217 | ||||||||
| 10 | (Unmodified Duration % to achieve speced %done) | |||||||||
| 11 | Percent Done | 60% | ||||||||
| 12 | Linearness | 0% | ||||||||
| 13 | At Percent Duration | 60% | ||||||||
| 14 | ||||||||||
| 15 | ||||||||||
| 16 | % Cumulative | % Per Month | Equiv MO | Year and Month | Month From Start | Period of Billing | Projected Monthly Billing | Projected Cumulative Billing | ||
| 17 | 0.0% | 0.0% | 0.00 | 2019.12 | 0 | Dec-19 | 0 | 0 | ||
| 18 | 1.5% | 1.5% | 0.94 | 2020.01 | 1 | Jan-20 | $ 150,690.00 | $ 150,690.00 | ||
| 19 | 5.9% | 4.4% | 1.88 | 2020.02 | 2 | Feb-20 | $ 442,987.02 | $ 593,677.02 | ||
| 20 | 13.0% | 7.1% | 2.82 | 2020.03 | 3 | Mar-20 | $ 708,582.55 | $ 1,302,259.56 | ||
| 21 | 22.3% | 9.3% | 3.76 | 2020.04 | 4 | Apr-20 | $ 931,467.55 | $ 2,233,727.12 | ||
| 22 | 33.3% | 11.0% | 4.70 | 2020.05 | 5 | May-20 | $ 1,098,207.42 | $ 3,331,934.54 | ||
| 23 | 45.3% | 12.0% | 5.64 | 2020.06 | 6 | Jun-20 | $ 1,198,751.73 | $ 4,530,686.27 | ||
| 24 | 57.6% | 12.3% | 6.58 | 2020.07 | 7 | Jul-20 | $ 1,227,040.09 | $ 5,757,726.36 | ||
| 25 | 70.8% | 13.2% | 7.64 | 2020.08 | 8 | Aug-20 | $ 1,324,798.90 | $ 7,082,525.25 | ||
| 26 | 82.8% | 12.0% | 8.73 | 2020.09 | 9 | Sep-20 | $ 1,195,174.16 | $ 8,277,699.41 | ||
| 27 | 92.1% | 9.3% | 9.82 | 2020.10 | 10 | Oct-20 | $ 930,187.60 | $ 9,207,887.02 | ||
| 28 | 98.0% | 5.9% | 10.91 | 2020.11 | 11 | Nov-20 | $ 589,999.76 | $ 9,797,886.78 | ||
| 29 | 100.0% | 2.0% | 12.00 | 2020.12 | 12 | Dec-20 | $ 202,113.22 | $ 10,000,000.00 | ||
| 30 | 13 | |||||||||
| 31 | 14 | |||||||||
| 32 | 15 | |||||||||
| 33 | 16 | |||||||||
Client | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B4 | B4 | =DATEDIF(B2,B3,"m")+1 |
| D9 | D9 | =ACOS(1-2*D11)/PI() |
| C17:C33 | C17 | =IF(F17="","",IF(E17/B$4<D$13,+$E17*D$9/D$13,+(D$9+($E17/B$4-D$13)/(1-D$13)*(1-D$9))*B$4)) |
| D17:D33 | D17 | =IF(C17="","",YEAR(F17)+MONTH(F17)/100) |
| F17:F33 | F17 | =IF(E17<=B$4,EOMONTH(B$2,E17-1),"") |
| G17 | G17 | =+J$16*B17 |
| H17:H33 | H17 | =IF(A17="","",+B$7*A17) |
| G18:G33 | G18 | =IF(B18="","",+B$7*B18) |
| A18:A33 | A18 | =IF(C18>B$4,"",(1-COS($C18/$B$4*PI()))/2*(1-$D$12)+1*$D$12*$C18/$B$4) |
| B18:B33 | B18 | =IF(C18>B$4,"",+A18-A17) |
This one is working great but there's a problem.
I would like to add a pre-construction start.
Let's say the project is 100$ pre-construction start is 4 month for 10$ and the project itself is 12 months
I would like to have the first 4 month at 2.5$ linear and start the parabola at month 5 for the remaining 8 months
The formula should be able to adapt, for an other project the pre-construction could be 7 months let's say.
Is that something that possible to do ?
Thanks a lot for whoever will take the time to help
