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.

Sample Data.xlsx

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!!

Find more posts tagged with

Finance

Formulas

Services

Project Management

Accepted answers

Dale Murphy

Melissa;

Building this kind of capability will probably take a while, or involve use of resource management capability.

Try this to start, for Duration in months:

=((MONTH([End Date]@row)) - (MONTH([Start Date]@row)) + 1) + (((YEAR([End Date]@row)) - (YEAR([Start Date]@row))) * 12)

Melissa Boehl

Thank you for that. That did replace the 3 columns I had created to make that calculation one step at a time. I edited the original post and included a sample I created in excel. The next step will be to take that duration and spread it evenly over 22 months and have it start in the appropriate column. I am not even sure that can be done. Maybe this would be better created with a couple different sheets feeding into a report. I am not sure though. I am quite new to Smartsheet! Thanks so much for that formula. It was very helpful!

All comments

Dale Murphy

Melissa;

Building this kind of capability will probably take a while, or involve use of resource management capability.

Try this to start, for Duration in months:

=((MONTH([End Date]@row)) - (MONTH([Start Date]@row)) + 1) + (((YEAR([End Date]@row)) - (YEAR([Start Date]@row))) * 12)

Melissa Boehl

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.