Calculating this week's revenue based on probability, hourly charge, and hours consumed.

Options
Vanessa Munoz
edited 04/08/20 in Smartsheet Basics

I'm creating a sheet that shows my leadership a future week's revenue based on the following formula:

= hours x rate x probability of sale going through

This calculation get's complicated if the sales goes through in the middle of the week (e.g. Wednesday) because I'd have to do a calculation that takes a fraction of the week's revenue. Can you please help me find an efficient formula to do this? So far, I've come up with the following:

1.    IF Monday is less than Start date AND [Start date minus Monday] is less than or equal to 5:

a.    TRUE, then determine the amount of days that should be included in the week distribute the factored amount

b.    FALSE, then proceed to next step

2.    IF End date is greater or equal to Monday AND End date is less than or equal to Friday:

a.    TRUE, then determine the amount of days that should be included in the week and distribute the factored amount

b.    FALSE, then proceed to next step

3.    IF Last Friday is greater than or equal to Start date AND, Start date is less than or equal to End Date AND, Last Monday is less than or equal to End Date AND Monday is less than or equal to End Date,

a.    TRUE, distribute the full week’s factored amount

b.    FALSE, then it equals 0


Which translates to:

=IF(AND([Date - Monday]$1094 < $[Estimated Close Date]1094, $[Estimated Close Date]1094 - [Date - Monday]$1094 <= 5), (5 - NETWORKDAYS([Date - Monday]$1094, $[Estimated Close Date]1094) + 1) * (($[Budget HRS]1094 * $Rate1094 * $Probability1094 / $[# Weeks]1094) / 5), IF(AND(WORKDAY($[Estimated Close Date]1094, $[# Weeks]1094 * 5) >= [Date - Monday]$1094, (WORKDAY($[Estimated Close Date]1094, $[# Weeks]1094 * 5)) <= [Date - Monday]$1094 + 4), (($[Estimated Close Date]1094 + ($[# Weeks]1094 * 7) - [Date - Monday]$1094) * ($[Budget HRS]1094 * $Rate1094 * $Probability1094 / $[# Weeks]1094) / 5), IF(AND([Date - Monday]$1093 + 4 >= $[Estimated Close Date]1094, $[Estimated Close Date]1094 <= WORKDAY($[Estimated Close Date]1094, $[# Weeks]1094 * 5), [Date - Monday]$1093 <= WORKDAY($[Estimated Close Date]1094, $[# Weeks]1094 * 5), [Date - Monday]$1094 <= WORKDAY($[Estimated Close Date]1094, $[# Weeks]1094 * 5)), $[Budget HRS]1094 * $Rate1094 * $Probability1094 / $[# Weeks]1094, 0)))

Any help is appreciated!!

Answers