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

Vanessa Munoz

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!!

Paul Newcome

What is the logic behind this?

determine the amount of days that should be included in the week

Vanessa Munoz

[(Start date + End date) – Monday] / 5 = produces the fraction of days in the week

Vanessa Munoz

which translates into: (($[Estimated Close Date]1094 + ($[# Weeks]1094 * 7) - [Date - Monday]$1094) * ($[Budget HRS]1094 * $Rate1094 * $Probability1094 / $[# Weeks]1094) / 5)

Paul Newcome

So basically it is how many days into the workweek?

Do you think that

=WEEKDAY([Start Date]@row) - 1

Would generate the same thing?

So a Monday Start Date would generate a 1. Tuesday Start Date would generate a 2, Wednesday is 3, Thursday is 4 and Friday is 5.

Vanessa Munoz

Not sure if that would work because i need to determine the number of days that are between the start date and the beginning of a week. it could be 8 days, 20 days, etc.

This formula does accomplish what I want, just wasn't sure if I could shorten it somehow.

On another note, if I link another sheet's cell within a formula and I copy that formula horizontally, will the linked cell in the other sheet move accordingly? I really need it to, as I do not want a bunch of 52 extra columns that specify the start of the week's date.

Paul Newcome

Are you able to create a copy of the sheet, replace any sensitive data with "dummy data" and then provide a published (Edit by anyone) link to the copy? It seems like there are a lot of formulas being used that all tie together. Being able to understand exactly how each piece works towards the whole would be very helpful in trying to see if there is any way to create some efficiency.

Paul Newcome

In regards to your other question about linking through a formula (cross sheet reference) the answer is no. The range selected in the cross sheet reference is static, but there are ways of pulling data that could be used so that you don't need 52 helper columns.

The start of the week's date can actually be found using a basic table of two columns, seven rows, and an INDEX/MATCH formula. I do it quite frequently.