Help!!
I have a spreadsheet table of project data including:
Project name, ID,
Estimated Start Date, Estimated Finish Date,
Actual Start Date, Actual Finish Date,
Original Baseline Budget, Latest Baseline Budget
Planned Costs to Date, Actual Costs to Date
Latest Estimate to Complete
Stage 1 Estimated or Actual Finish Date,
Stage 2 Estimated or Actual Finish Date,
Stage 3 Estimated or Actual Finish Date
Stage 4 Estimated or Actual Finish Date,
Stage 5 Estimated or Actual Finish Date,
Stage 6 Estimated or Actual Finish Date,
Current Stage
This data is updated monthly and reported against monthly. Projects are grouped into Sub-Portfolios and those are grouped under Portfolios. From this data I can calculate an average Burn Rate but this always give an incorrect view of future spend.
Instead, I would like to take each project's remaining budget (Latest Baseline Budget less Actual Costs to Date) and spread it across the remaining months until each project's Estimated Finish Date. I also want to do the same for each project's Latest Estimate to Complete. The tricky part is that I want to spread them according to percentages assigned to each Stage as follows:
Stage 1: 20%
Stage 2: 20%
Stage 3: 15%
Stage 4: 25%
Stage 5: 15%
Stage 6: 5%
Naturally there are projects starting and finishing throughout the year and each month Project Managers will update Actuals to Date and Latest Estimate to Complete accordingly while their projects progress through each stage.
Can somebody help me with how best to calculate this automatically so that I have a more accurate picture (Stacked Bar graph) of Burn Rate, Projected Spend and compared against Budget.
Any assistance or advise would be GREATLY APPRECIATED
I have a spreadsheet table of project data including:
Project name, ID,
Estimated Start Date, Estimated Finish Date,
Actual Start Date, Actual Finish Date,
Original Baseline Budget, Latest Baseline Budget
Planned Costs to Date, Actual Costs to Date
Latest Estimate to Complete
Stage 1 Estimated or Actual Finish Date,
Stage 2 Estimated or Actual Finish Date,
Stage 3 Estimated or Actual Finish Date
Stage 4 Estimated or Actual Finish Date,
Stage 5 Estimated or Actual Finish Date,
Stage 6 Estimated or Actual Finish Date,
Current Stage
This data is updated monthly and reported against monthly. Projects are grouped into Sub-Portfolios and those are grouped under Portfolios. From this data I can calculate an average Burn Rate but this always give an incorrect view of future spend.
Instead, I would like to take each project's remaining budget (Latest Baseline Budget less Actual Costs to Date) and spread it across the remaining months until each project's Estimated Finish Date. I also want to do the same for each project's Latest Estimate to Complete. The tricky part is that I want to spread them according to percentages assigned to each Stage as follows:
Stage 1: 20%
Stage 2: 20%
Stage 3: 15%
Stage 4: 25%
Stage 5: 15%
Stage 6: 5%
Naturally there are projects starting and finishing throughout the year and each month Project Managers will update Actuals to Date and Latest Estimate to Complete accordingly while their projects progress through each stage.
Can somebody help me with how best to calculate this automatically so that I have a more accurate picture (Stacked Bar graph) of Burn Rate, Projected Spend and compared against Budget.
Any assistance or advise would be GREATLY APPRECIATED
