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!!
Business Systems Analyst ~ WithersRavenel
115 MacKenan Drive | Cary, NC 27511
Office: 919.469.3340 | Direct: 919.238.0436
Did I answer to your question or fix the problem? Please
Smartsheet Communitybyclicking Answer Accepted. It will make it easier for users to discover solutions and resolve problems.
Help Article Resources
Check out the Formula Handbook template!