Development Phasing

[Bigbz]

Hi Everyone,

I have struck a problem with a development spreadsheet I am working on. This is for a development / construction scenario. I know the total costs of the development, and how long each part will take. What I want is a formula to automatically spread the development costs based on the completion date, and number of months that part of the development takes. I have the month end dates running along the top of the top of the worksheet to the right, on the left I have a completion date for the project, a list of costs for the project, and how long each cost item will take. Eg, the total cost for housing is $1,000,000, and it will take 3 months to complete, so I want to put $333,333 into the completion month cell and previous 3 cells.

Using excel 2010

I am on a shared computer so cant install the add in to upload my workbook.

Thanks in advance.

 

[Michael M]

When will each project start ?
Is it partial months or whole months.
you could put a Border around the data then copy / paste back here on the forum.

 

[Bigbz]

The start date is determined by the completion date minus the number of months each development item will take. I have attempted to copy and paste the worksheet.


Start Period 01-Nov-14 01-Dec-14 01-Jan-15 01-Feb-15 01-Mar-15 01-Apr-15 01-May-15 01-Jun-15 01-Jul-15
End Period 31-Oct-14 30-Nov-14 31-Dec-14 31-Jan-15 28-Feb-15 31-Mar-15 30-Apr-15 31-May-15 30-Jun-15 31-Jul-15
Ok FY 2015 2015 2015 2015 2015 2015 2015 2015 2016 2016
unit



Site Name text [Site 1]
Development Start Date 01-Dec-14
Development End Date date 31-Jul-15
Size ha 600


Capital Expediture Profile Area / Quantity Cost Rate per ha Phasing Total $
Shed nzd 1 1,400,000 4 1,400,000 350,000 350,000 350,000 350,000
Housing - new nzd 4 250,000 3 1,000,000 333,333 333,333 333,333

 

[Michael M]

That doesn't help I'm afraid...
Have you looked at the FORECAST function ??
Also, have a look at thread posted by shg here...

http://www.mrexcel.com/forum/excel-questions/556463-auto-distribution-data-over-months.html