# Cost Phasing

#### GIngerlomax

##### Board Regular
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>

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

##### Well-known Member
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

##### Board Regular
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

##### Board Regular
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)))

Last edited:

Replies
0
Views
54
Replies
0
Views
105
Replies
2
Views
148
Replies
4
Views
376
Replies
0
Views
59

1,191,687
Messages
5,988,011
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back