How to calculate a rolling actuals plus forecast to create an Estimate at Completion
Hello,
I am trying to create a calculation that will give me an Estimate at Completion where it sums actual hours/cost up to today with the sum of forecasted hours/cost through the end of the project. I am struggling to get the date to be dynamic so it updates each week instead of having to manually adjust the columns that are being summed. Is this possible?
Answers
-
@mmfarr This depends entirely on how you have your columns/data structured.
Do you enter a date with each of your Actual hours/costs? Are you leaving the forecasted hours/cost date field blank?
Are you wanting to sum up by the end of each week? Or just through today (as today changes each day)?
Where are you wanting to display this calculated amount? In a column next to each entry? In a Summary field?
In general, you would use the entry date of the actual and compare that to todays date with a sumif function; then add the rows that have no date (that I'm assuming are forecasted).
So for hours: =sumif(Hours:Hours, Created:Created, <=today()) + sumif(Hours:Hours, Created:Created, "")
-
Hi Ryan,
I have my columns structured by week and I have individual rows for the contract baseline, forecast, and actuals per role (pic below). The baseline and forecast are prefilled and then I add actuals each week so I would want it to sum up the actual row to the end of the previous week then add it to the sum of forecast hours from current week through end of project.
I don't care if this calculates in a specific column/row/summary field.
-
The only way I could figure out how to do something similar was to change my week's to go down instead of across and reference the date column with a sumif and the Today formula
-
@mmfarr Ideally you’ll need to change the structure of your data so your weeks are listed in 1 column like @Hollie Green mentioned. Then Have columns for Contract Forecast and Actual.
with your current setup, you will be constantly updating the formulas when a new week is added because you must define the name of the (new) column in the formula.
Alternatively:
Do you know the timeframe at the beginning of the contract? If so, and you wanted to keep your data structure the same, you could change your column names to “week1” “week2” etc. then add the first or last day of that week in its own row at the top.
Then you could create one giant formula that referenced all of your “week” columns.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 409 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!