Trying to create a Projections sheet and need some suggestions please!
Hello everyone. I am hoping someone may be able to give me an idea on how to create a projections sheet for our engineering projects. I would like to build it out so I can spread the total hours per staff type over the duration of the project. I have tried a few different ways and cannot seem to figure out how to create it without always needing to manually input the hours and calculate the dollars per month. I would be incredibly grateful for any suggestions. Here are the main data points I am working with:
- Project Name
- Project Number
- Project Manager Name
- Start Date
- End Date
- Duration in Months (calculated using Start and End Date- need a formula to calculate this)
- 3 different staff types each with a different billing rate (PM $1000, Staff Professional $500, CAD Designer $250)
- Inputs for total number of hours for each staff type
- Then I need to take those hours and spread them evenly into monthly columns starting at the column matching the Start Date and ending in the column listed as the end date.
- I then need to calculate dollars per month by multiplying the hours that month by that staff types billing rate.
I have included an example spreadsheet I created in excel.
The goal of this sheet:
- See how many hours we have planned per staff type per month in order to determine if we need to hire or look for more work for the team
- See if the planned dollars over the next year are meeting our set budget.
I hope I have been able to properly convey exactly what I am attempting to do but please feel free to ask questions!!