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