Allocating cost with changing durations

[steele57]

I have been given the following task by my employer and am in need of some major help.

I am trying to create a spreadsheet where I am given 20 different projects with various start dates, project duration's and cost and am being asked to allocate the cost of each job, per month, with the following categories: Material, Hourly Labor, Sub Contract Labor.
Example:
Job # 102
Start date January 1, 2014
Duration of project 6 months
Material Cost - $45,506 (total budgeted on whole job)
Hourly Labor - $2,850 (total budgeted on whole job)
Sub Contract Labor - $10,250 (total budgeted on whole job)

Material Cost is to be allocated
Month 1: 25%
Month 2: 30%
Month 3: 20%
Month 4: 5%
Month 5: 20%
Month 6:0%

Sub Contract Labor to be allocated
Month 1: 0%
Month 2: 30%
Month 3: 20%
Month 4: 30%
Month 5: 20%
Month 6:0%

Hourly Labor
Month 1: 15%
Month 2: 10%
Month 3: 20%
Month 4: 45%
Month 5: 5%
Month 6:5%

Problem I am facing is that I have 20 projects to calculate with various start dates, duration and budgets and am looking for a solution to allocate the cost per month, for all the projects.

One other issue is that when I have a project that has a duration of say 10 months, I would still like to proportionize the expenses based off the above.

Any help would be GREATLY APPRECIATED!!

 

[skywriter]

If I'm completely wrong in understanding what you are doing forgive me, but some suggestions would be building a template on one sheet and copying that sheet multiple times and putting each project on a separate sheet. If you need to see data from all projects on one sheet you could have a summary sheet where a cell or cells on the summary sheet are linked to each one of the separate project sheets, anything updated on the individual sheet would update on the summary sheet.

Hope that helps.