Identifying Opportunity Gaps

[pherman]

I'm creating a dealer profitability report looking at dealers' response rate and total sales per response. I would like to add an "Opportunity" column for those dealers that are in the red with their response rate and total sales per response.

I'd like to create a formula that shows a dealer would need X number of additional ROs in order to hit the regional average. I'd also like to create a second formula to show a dealer would need X amount of additional sales to hit the average total sales per response in their region.

In the below table, I've tallied up the Total ROs and Total Sales Response per region and their averages.

Here are some quick math steps I put together, but I have no idea if I'm on the right track. Any assistance is greatly appreciated!!!

For the Response Rate
-(Region – Red dealers rate) x unique customers = incremental customers
-X RO’s per customer x $/ro = Opportunity

For Total Sales/Per Response

(Region – Red dealer) x # of RO’s

 

[pherman]

Here is some more information regarding this question.

• Response rate: Find out how many unique customers are needed to reach the average response rate in their region for those dealers in the red.
• Total Sales per Response: Find out how many additional ROs needed to reach the average total sales in their region for those dealers in red.

Plus the ‘size of the prize’ for each from a financial perspective. So if region avg is 50% and you are at 25%...you would double responses X $/RO to get ‘what’s it worth to them. Same thing on the $/RO…if the average is $200 and dealer is at $100 and they have 10 RO’s a month…the prize is the 10 ROs at the additional $100 each.