Calculating work completed and forecasts

It looks like your first formula is simply dividing the [Total Cost] by the number of months... whereas your second formula is doing that, but then weighing it both by the Rates and the % Complete column (which is why you're seeing different values from the two different formulas).

Try taking out the multiplications at the end of your second formula to see if that's a better result for you:

Otherwise, if you did want to weigh this by the Rates and % Complete, you'd need to add that element into your first formula (instead of removing it from your second). Does that make sense?

Cheers!

Genevieve

Hi Genevieve,

Thank you for your response, unfortunately, this has not worked. I added both Rates and % Complete to the first formula, however it is still over estimating how much the task is.

Can you copy/paste how you added these elements in the first formula?

What happens if you tried adjusting the second formula, instead?

Hi @Genevieve P,

This formula is actually resulting in an error:

=IF(TODAY() < [Start Date]1283, 0, [Total Cost]1283 / (MONTH([End Date]1283) - MONTH([Start Date]1283) + 1 + IF(YEAR([End Date]1283) = 2021, 12, 0) + IF(YEAR([End Date]1283) = 2022, 24, 0)) * (MONTH(TODAY()) - MONTH([Start Date]1283) + 1 + IF(YEAR(TODAY()) = 2021, 12, 0) + IF(YEAR(TODAY()) = 2022, 24, 0))) * $Rates$1347) * (1 - $[% Complete]1283), 0)

Below is a simplified sheet:

Task labelled "original formula" includes has the rates and % completed, included in the formula for forecasts only, where as the task labelled "smartsheet formula" has rates and % completed in both formulas (for WIP and forecast).

To summarise, I want the WIP to increase depending on % complete entered and the current months forecast to always = 0. For e.g. using sheet above. The task starts in september and ends in december. If someone completes 10% of the work, the forecast for september should be 0 and the remaining amount should be projected in october to december.

It looks like you may have just copied/pasted the same end of the formula from your second one onto the first, however they are built slightly differently. The second one has an IFERROR statement wrapped around it, which is why the end has , 0) - you don't need this in your first formula.

It also looks like your row references are for row 1283 in the sheet... is that where you're pasting this formula? I've adjusted this to be row 5, but you can change that if it really is row 1283. Try this:

=(IF(TODAY() < [Start Date]5, 0, [Total Cost]5 / (MONTH([End Date]5) - MONTH([Start Date]5) + 1 + IF(YEAR([End Date]5) = 2021, 12, 0) + IF(YEAR([End Date]5) = 2022, 24, 0)) * (MONTH(TODAY()) - MONTH([Start Date]1) + 1 + IF(YEAR(TODAY()) = 2021, 12, 0) + IF(YEAR(TODAY()) = 2022, 24, 0))) * $Rates$5) * (1 - $[% Complete]5)

If you always want the formula to look into its current row, you can also use the @row function instead of specifying the row number. Try this:

=(IF(TODAY() < [Start Date]@row, 0, [Total Cost]@row / (MONTH([End Date]@row) - MONTH([Start Date]@row) + 1 + IF(YEAR([End Date]@row) = 2021, 12, 0) + IF(YEAR([End Date]@row) = 2022, 24, 0)) * (MONTH(TODAY()) - MONTH([Start Date]@row) + 1 + IF(YEAR(TODAY()) = 2021, 12, 0) + IF(YEAR(TODAY()) = 2022, 24, 0))) * $Rates@row) * (1 - $[% Complete]@row)

This formula should no longer give you an error... however you'll have to test and see if it's returning the math that you're looking for. Let me know if it works for you!

Cheers,

Genevieve

Hi @Genevieve P

Unfortunately, the first formula returned "invalid data type" and the second returned a value of £1250.

Appreciate your help :)

So that value is because it's multiplying your division by the Rate in that row, in this case £50. Can I ask why you're adding in the rates to the formula, or what these rates represent? You may just want to weigh it by the %... try this:

=IF(TODAY() < [Start Date]@row, 0, [Total Cost]@row / (MONTH([End Date]@row) - MONTH([Start Date]@row) + 1 + IF(YEAR([End Date]@row) = 2021, 12, 0) + IF(YEAR([End Date]@row) = 2022, 24, 0)) * (MONTH(TODAY()) - MONTH([Start Date]@row) + 1 + IF(YEAR(TODAY()) = 2021, 12, 0) + IF(YEAR(TODAY()) = 2022, 24, 0))) * (1 - $[% Complete]@row)

Hi @Genevieve P,

Your formula has returned the following:

Nearly there... However, I want the person to be able to provide the % complete. For e.g. inputting 25% done, the WIP to = £25, the forecast for september to be 0, and the forecasts for Oct - Dec to be £25.

Changing the % complete to 25% returns the following values:

As you can see, this now amounts to £75.75.

I don't think I understood what your formula was originally doing, so adding a percent was perhaps not the right way to go.

I broke down the formula a bit to understand it, and I'll explain each step below. I've taken out the IF statements which are looking for different years and adding on either 12 or 14 months, since we're just looking in this year for now.

This is the core of the formula:

=[Total Cost]1 * (1 - $[% Complete]1) / (MONTH([End Date]1) - MONTH([Start Date]1) + 1) * (MONTH(TODAY()) - MONTH([Start Date]1) + 1)

The first thing it's doing is it's taking the Total Cost and multiplying it by the percent left to complete (in this case, 0.75) to figure out the cost left for what is not complete.

Then,

(MONTH([End Date]1) - MONTH([Start Date]1) + 1)

It's figuring out how many months are in this task (in this case, 4, from Sept - Dec)

and then it divides that total cost by the number of months

75 / 4

You could stop at this point if you simply wanted to see how much cost is left per month, however, you want to see a total Work in Progress (is that right? Are you wanting a total of what's already been completed?)

Then your formula it multiplies this by how many months have already passed:

(MONTH(TODAY()) - MONTH([Start Date]1) + 1)

Now, if we hadn't added in the Percentage, this would take the total (£100), divide it by how many months there are in the task (4, so £25), and then multiply that by how many months had already passed (in this case 1, so £25).

If we were in November, this would take the total (£100), divide it by task length (4 months, so £25), then multiply it by the months passed (£25 x 3 = £75) to show you a total of what should have been completed.

Based on this, it sounds like you have two ways to calculate how much is currently WIP. You can either use the original formula you had without the percents or, since you have the percent already, you can use this to create your WIP with a simple:

=[Total Cost]@row * $[% Complete]@row

This will tell you how much of the Total Cost has been completed, based on the percentage of task complete (instead of based on the months passed). Does that make sense?

Hi, How can I calculate this on a weekly basis instead of monthly?

I tweaked the formula above:

=IF(TODAY() < Start@row, 0, [Initial SOV Amount]@row / (WEEK(Finish@row) - WEEK(Start@row) + 1 + IF(YEAR(Finish@row) = 2022, 12, 0) + IF(YEAR(Finish@row) = 2024, 24, 0)) * (MONTH(TODAY()) - MONTH(Start@row) + 1 + IF(YEAR(TODAY()) = 2022, 12, 0) + IF(YEAR(TODAY()) = 2024, 24, 0))) * (1 - $[% Complete]@row)

https://community.smartsheet.com/discussion/comment/262433#Comment_262433

Hi Genevieve,

I used this formula to help forecast the revenue produced based on wip.:

=IF(TODAY() < Start@row, 0, [Total SOV]@row / (MONTH(Finish@row) - MONTH(Start@row) + 1 + IF(YEAR(Finish@row) = 2022, 12, 0) + IF(YEAR(Finish@row) = 2024, 24, 0)) * (MONTH(TODAY()) - MONTH(Start@row) + 1 + IF(YEAR(TODAY()) = 2022, 12, 0) + IF(YEAR(TODAY()) = 2024, 24, 0))) * (1 - $[% Complete]@row)

Do you know how I can change this to a weekly forecast? I'm hoping to see how much revenue we will accrue in a given week if we keep to our current schedule.

Hi @VBJBC

This comes down to what sort of math you want to accomplish.

What I might suggest doing here is base your calculation off of the Remaining Balance column instead of the original Total column.

Then you could look to see if the Start date is in the future (hasn't started yet) or if the Finish date is in the past (completed), and return 0.

=IF(OR(TODAY() < Start@row, Finish@row <= TODAY()), 0,

Otherwise, you could subtract today from the Finish date to see how many days are remaining. If there are more than 7 days remaining, you can break this down into weeks by dividing those total days by 7:

IF(Finish@row - TODAY() > 7, [Remaining Balance]@row / ((Finish@row - TODAY()) / 7),

Otherwise, if there are less than 7 days remaining, you can simply return the remaining balance, since that's what will be showing this week.

[Remaining Balance]@row))

Full potential formula:

=IF(OR(TODAY() < Start@row, Finish@row <= TODAY()), 0, IF(Finish@row - TODAY() > 7, [Remaining Balance]@row / ((Finish@row - TODAY()) / 7), [Remaining Balance]@row))

This does ignore the percentage column because it assumes that the Remaining Balance column is looking at the percentage. The number of Days and number of Weeks on the right is just to show you what the middle part of the formula is calculating.

Let me know if this makes sense and will achieve your goal!

Cheers,

Genevieve

Thanks, @Genevieve P. !

For this formula, is it only possible to forecast one week in advance? Is there a way I can pick a random week let's say sometime in May 2023 and get a forecast of revenue for that week?

Hi @VBJBC

This formula is only looking at the current week's forecast (using TODAY as the date to reference).

It's more complicated to look at the "next week" because your Remaining Balance is displaying for today's date. We could subtract this week's forecast from the Remaining Balance and then use TODAY(7) to look 7 days ahead... try something like this:

=IF(OR(TODAY(7) < Start@row, Finish@row <= TODAY(7)), 0, IF(Finish@row - TODAY(7) > 7, ([Remaining Balance]@row - ([Remaining Balance]@row / ((Finish@row - TODAY()) / 7))) / ((Finish@row - TODAY(7)) / 7), ([Remaining Balance]@row - ([Remaining Balance]@row / ((Finish@row - TODAY()) / 7)))))

In regards to picking a custom week, the way I would do this is break down the Total SOV by the days of the task so we have a weekly rate:

[Total SOV]@row / ((Finish@row - Start@row) / 7)

Then we'll need to add in statements for what to do if the Start date is in the future compared to your preferred date, etc. I've put my date in a Sheet Summary Field:

=IF(OR([Forecast Week Start]# + 7 < Start@row, Finish@row <= [Forecast Week Start]#), 0, [Total SOV]@row / ((Finish@row - Start@row) / 7))

Note that this doesn't take into account partial weeks... so if you select a date where the task only has 2 days in that week, it will still show you the generic 'weekly' breakdown. Is this what you were looking for?

Cheers,

Genevieve