Calculating work completed and forecasts

Sareena

I want the smartsheet to automatically update the “WIP (local)” column each month and reduce the current months forecast to £0. As you can see below, the “WIP (local)” column has increased and the forecast for September is £0 (this is fine). However, the smartsheet has now overestimated the total cost of the task (work done (wip local column) + forecast is = £338). When the cost of the task is actually £290. 

The formula used to calculate work done is

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

The formula used to calculate october forecast (£145 value) is

=IFERROR(IF(AND(MONTH(TODAY()) >= [Oct 20]$13, YEAR(TODAY()) = [Oct 20]$14), 0, (IF(OR(YEAR($[End Date]131) > [Oct 20]$14, AND(MONTH($[End Date]131) >= [Oct 20]$13, YEAR($[End Date]131) >= [Oct 20]$14)), IF(OR(YEAR($[Start Date]131) < [Oct 20]$14, AND(MONTH($[Start Date]131) <= [Oct 20]$13, YEAR($[Start Date]131) <= [Oct 20]$14)), $[Total Cost]131 / (MONTH($[End Date]131) - IF(AND(MONTH(TODAY()) > MONTH($[Start Date]131), YEAR(TODAY()) = YEAR($[Start Date]131)), MONTH(TODAY()), MONTH($[Start Date]131)) + 1 + IF(YEAR($[End Date]131) - YEAR($[Start Date]131) = 1, 12, 0) + IF(YEAR($[End Date]131) - YEAR($[Start Date]131) = 2, 24, 0)), 0))) * $Rates$202) * (1 - $[% Complete]131), 0)

Appreciate any help.

Genevieve P.

Hi @Sareena Bains

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:

=IFERROR(IF(AND(MONTH(TODAY()) >= [Oct 20]$13, YEAR(TODAY()) = [Oct 20]$14), 0, (IF(OR(YEAR($[End Date]131) > [Oct 20]$14, AND(MONTH($[End Date]131) >= [Oct 20]$13, YEAR($[End Date]131) >= [Oct 20]$14)), IF(OR(YEAR($[Start Date]131) < [Oct 20]$14, AND(MONTH($[Start Date]131) <= [Oct 20]$13, YEAR($[Start Date]131) <= [Oct 20]$14)), $[Total Cost]131 / (MONTH($[End Date]131) - IF(AND(MONTH(TODAY()) > MONTH($[Start Date]131), YEAR(TODAY()) = YEAR($[Start Date]131)), MONTH(TODAY()), MONTH($[Start Date]131)) + 1 + IF(YEAR($[End Date]131) - YEAR($[Start Date]131) = 1, 12, 0) + IF(YEAR($[End Date]131) - YEAR($[Start Date]131) = 2, 24, 0)), 0)))), 0)

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

Sareena

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.

Genevieve P.

Hi @Sareena Bains

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

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

Sareena

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.

Genevieve P.

Hi @Sareena Bains

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

Sareena

Hi @Genevieve P

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

Appreciate your help :)

Genevieve P.

Hi @Sareena Bains

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)

Sareena

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.

Genevieve P.

Hi @Sareena Bains

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?

VBJBC

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)

VBJBC

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.

Genevieve P.

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

VBJBC

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?

Genevieve P.

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

VBJBC

This is perfect! Thanks so much for your help Genevieve!

Best,

Jillian