I have a set of sales figures that came from New Business and Upsells that I am trying to cross reference with the cost of sales effort. I am trying to add weights to the values to reflect that we consider one type of sale to require more effort, and then to calculate how much effort we really spent on each item.
Here's an example, assuming Product 1 sales effort of 600 and Product 2 sales effort of 200
Now in a simple calculation it's easy to determine how much sales effort went into each line, and therefore how much sales effort we would need to replicate these results:
| Product | Type of sale | Value of sale | Sales Effort |
|---|---|---|---|
| Product 1 | New business | 100 | 100 |
| Product 1 | Upsell | 500 | 500 |
| Product 2 | New business | 500 | 200 |
But we consider that new business requires 10x the sales effort compared to upsells, so I need to incorporate that. Here's how that should end up looking:
| Product | Type of sale | Value of sale | Weighted value of sale | Weighted sales effort |
|---|---|---|---|---|
| Product 1 | New business | 100 | 1000 | 400 |
| Product 1 | Upsell | 500 | 500 | 200 |
| Product 2 | New business | 500 | 5000 | 200 |
So far the best way I found of doing it was the following formula, for example for E2, then changing the value of the sales effort:
E2 = (total sales effort for that product) * D2 / (SUMIFS(D2:D4,A2:A4,A2))
I think the (sales effort for that product) could be replaced with an IFS statement to automatically pick that value given A-column value, but am I missing something? Is there a better way of doing this if I want to include it in a Pivot table with some easy to use slicers?