How to calculate a rolling actuals plus forecast to create an Estimate at Completion

mmfarr

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?

Ryan Sides

@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, "")

mmfarr

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.

Hollie Green

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

Ryan Sides

@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.