Cost Phasing

[GIngerlomax]

Hi there. Can anyone help me with this little problem. I will try and explain as bets i can!!
We have a P6 schedule exported into Excel (Dont ask why we dont just do this in P6) and a some estimates. We have a start and finish date for each activity from the schedule dump and the cost of each activity from the estimte. What we would like to be able to do is be able to spread these costs in a cost loaded curve. Simple enough if every activity will have the same cost profile. But they dont and here is the tricky bit. We would like to be able to select the profile 6 in all and then excel would do its magic and proportion the cost between the 2 dates according to the cost profile selected... Simples, I hope!!

Curve Profiles
1	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%
2	1%	2%	3%	7%	13%	17%	20%	19%	13%	5%
3	5%	13%	19%	20%	17%	13%	7%	3%	2%	1%
4	3%	7%	11%	14%	15%	15%	14%	11%	7%	3%
5	100%	0%	0%	0%	0%	0%	0%	0%	0%	0%
6	0%	0%	0%	0%	0%	0%	0%	0%	0%	100%

<colgroup><col><col span="10"><col></colgroup><tbody>
</tbody>

 

[ask2tsp]

We have a P6 schedule exported into Excel

What's P6?
How does that look in excel?

proportion the cost between the 2 dates

How should that be calculated? Divide the period in 10 equal period's (rounded per day?) and apply the percentages?

Where do these percentages come from?

 

[GIngerlomax]

P6 is Primavera is a planning tool. We are going to use polynomial formula to determine what the split is along the line for the total value. =3.10862446895044E-15*C16^6 + 0.0000128205126657122*C16^5 - 0.000384615378834496*C16^4 + 0.00211538450821536*C16^3 + 0.0173076931969263*C16^2 - 0.0324778592548682*C16 + 0.0136363733254257 We are struggling to work out how to get excel to determine the date set automatically. Below is a cut of the data.

Activity ID	Activity Name	Duration	Start	Finish	Total Month	Total Float	Budgeted Total Cost
A1740	Major Permissions - Project Management	734	01-Apr-17	22-Feb-19	23	1939	£6,748,243
A1630	MPP2 - Main Site DCO Contracts	742	01-Apr-17	06-Mar-19	24	1931	£6,027,265
A1650	MPP3 - SP&C Contracts	553	01-Apr-17	08-Jun-18	15	2120	£299,795
A1660	MPP4 - Highways Contracts	443	01-Apr-17	29-Dec-17	9	2230	£881,005
A1670	MPP5 - Worker's Accomodation Contracts	445	01-Apr-17	03-Jan-18	10	2228	£920,193
A1690	MPP6 - Logistics, Park & Ride Contracts	746	01-Apr-17	12-Mar-19	24	1927	£581,667
A1720	MPP7 - Marine Licences Contracts	709	01-Apr-17	18-Jan-19	22	1964	£1,879,577
A1730	MPP8 - Environmental Permits Contracts	546	01-Apr-17	30-May-18	14	2127	£1,291,958

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[GIngerlomax]

We did it!! Dont ask me how but someone with a much bigger brain then me worked out the formula. If anyone wants it just ask and i can send it over without the data just the formula. Here is a taster!!
Used for working out the time scale.

=IF(MONTH($D8)&YEAR($D8)=MONTH(I$2)&YEAR(I$2),(1-((DAY($D8)-1)/HLOOKUP(MONTH($D8),Monthdays,'Base Data'!$A$4,FALSE))),IF(MONTH($E8)&YEAR($E8)=MONTH(I$2)&YEAR(I$2),(DAY($E8))/HLOOKUP(MONTH($E8),Monthdays,'Base Data'!$A$4,FALSE)+H8,IF(OR(MONTH($E8)&YEAR($E8)=(MONTH(I$2)-1)&YEAR(I$2),MONTH($E8)&YEAR($E8)=12&YEAR(I$2)-1),0,IF(H8-G8>0,H8+1,H8))))

For the cost.

=IF(OR('Cumulative Cash Flow'!$G176="Linear",'Cumulative Cash Flow'!$G176="Project-S; Early",'Cumulative Cash Flow'!$G176="Project-S",'Cumulative Cash Flow'!$G176="Project-S; Late"),((VLOOKUP('Cumulative Cash Flow'!$G176,Curvetype,2,FALSE)*(11*'Time-Phase'!I176/'Cumulative Cash Flow'!$H176)^6+VLOOKUP('Cumulative Cash Flow'!$G176,Curvetype,3,FALSE)*(11*'Time-Phase'!I176/'Cumulative Cash Flow'!$H176)^5+VLOOKUP('Cumulative Cash Flow'!$G176,Curvetype,4,FALSE)*(11*'Time-Phase'!I176/'Cumulative Cash Flow'!$H176)^4+VLOOKUP('Cumulative Cash Flow'!$G176,Curvetype,5,FALSE)*(11*'Time-Phase'!I176/'Cumulative Cash Flow'!$H176)^3+VLOOKUP('Cumulative Cash Flow'!$G176,Curvetype,6,FALSE)*(11*'Time-Phase'!I176/'Cumulative Cash Flow'!$H176)^2+VLOOKUP('Cumulative Cash Flow'!$G176,Curvetype,7,FALSE)*(11*'Time-Phase'!I176/'Cumulative Cash Flow'!$H176))
+VLOOKUP('Cumulative Cash Flow'!$G176,Curvetype,8,FALSE)*('Time-Phase'!I176/'Cumulative Cash Flow'!$H176))*'Cumulative Cash Flow'!$F176,IF(AND('Cumulative Cash Flow'!$G176="Upfront Payment",'Time-Phase'!I176>0,'Time-Phase'!I176<=1),'Cumulative Cash Flow'!$F176*VLOOKUP('Cumulative Cash Flow'!$G176,Curvetype,8,FALSE),IF(AND('Cumulative Cash Flow'!$G176="Final Payment",'Time-Phase'!I176='Cumulative Cash Flow'!$H176),'Cumulative Cash Flow'!$F176*VLOOKUP('Cumulative Cash Flow'!$G176,Curvetype,8,FALSE),0)))