Need help fixing my weighted sales pipeline possibly with a nested IF function or VLOOKUP (?)

[jillianmans]

Hi,

I was passed down an excel spreadsheet from a work colleague, it is a weighted sales pipeline and it has been failing to work :/

We are estimating how much money we will make in sales based off the probability (J), the sales potential (K), the closing date (L), and the days remaining in the current year (M).

What I need help fixing is N19:P81.

I'm stuck- N19, for example, is correct because based on the est. closing date on 11/1/21, there are 60 days left in the year 2021, therefore making the total sales for 2021 roughly 16k. However, I need O19 & P19 to reflect the full $100,000 @ 100% probability because the sale will definitely be closed by then. What I have in cell N19 is =IFERROR(L19<DATE(2021,12,31),0)*K19*J19*M19/365.

Looking at P81, that value is correct and so are the values for N81 and O81. However, if I change L81 to 11/1/22, the 2023 estimate needs to change to the total sales potential for a whole year, which would be $30,000 (P81). This is the annual sales potential multiplied by the probability, $100,000 (K81) * 30% (J81).

N81 is correct for 2021 estimates because no money will be made in 2021 since the closing date is later than 12/31/2021.


Any insight would be appreciated Thank you.

 

[fadee2]

hi jillianmans,

Welcome to the board.

I would suggest providing desensitized (if necessary) part of your worksheet or sample data along with the query, using dropbox or onedrive, with the results you are getting and the results you need. that would make it easier for everyone to better understand and suggest solutions accordingly.