Best approach for top sales deals?

maverick15

New Member
Joined
Jun 11, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi -

Thank you in advance for the help. I'm working on a sales runbook that combines actual and pipeline data to get to a forecast for the quarter. The sales leaders need to be able to determine, at a specific opportunity level, if an opportunity is going to close this quarter or not. If it will close, we'll take the "full" amount of the deal, if it won't close, we'll set the amount to 0, and if it might close, we'll take a weighted amount (specific field).

Right now I've been managing this by creating a pivot table of the top deals, then copying the data to another tab where I'm showing the pivot table info plus having a column where they can add a Y/N to each row. I think do a VLOOKUP on that row and update a column in the backend data, which adjusts the call $ amount based on the Y/N value (blank just takes the weighted amount).

I'd really like to use the original pivot so I don't have to keep copying/pasting each week when we update the data, however if they filter the pivot, the rows change and any comment they put in gets mixed up.

The workbook is being edited in Teams as well as in Excel, so I don't think VBA is an option.

I'm moving to using a Data Model for the backend data, but can have the source data "call" column update via the lookup method I'm using.

Is there a better way to do this? Any suggestions?

Thanks!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

ask2tsp

Well-known Member
Joined
Feb 18, 2015
Messages
506
Office Version
  1. 365
Platform
  1. Windows
This smells like a Power Query job to me. Build a table with the top n deals and a blank Y/N column.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,587
Messages
5,573,074
Members
412,502
Latest member
HMilne
Top