real life problem

[Mr Cheeseman]

I own a convenience store and I want to enter a formula into a spreadsheet. I buy pop for a cost per can (c). I calculate the retail by margin and the actual calculation uses 1 minus the margin and I will call that (m). I want to offer a can at 1 margin and a 2 can deal at another margin. I want to type in the blended margin I expect to get and return the 1 pack retail. I have found about 40% of the customer will buy 2 cans when I offer this type of deal. I want the retail of the 2 can deal to be 50 cents less per can than the 1 can deal. I will call the retail of 1 can (x) making the retail per can on the 2 can deal (x-.5). m = c/x This is the equation I wrote:

.6(c/x) + .4(c/(x-.5)) = m

Did I get the equation right?
How do I solve for x?

 

[JeffM]

I own a convenience store and I want to enter a formula into a spreadsheet. I buy pop for a cost per can (c). I calculate the retail by margin and the actual calculation uses 1 minus the margin and I will call that (m). I want to offer a can at 1 margin and a 2 can deal at another margin. I want to type in the blended margin I expect to get and return the 1 pack retail. I have found about 40% of the customer will buy 2 cans when I offer this type of deal. I want the retail of the 2 can deal to be 50 cents less per can than the 1 can deal. I will call the retail of 1 can (x) making the retail per can on the 2 can deal (x-.5). m = c/x This is the equation I wrote:

.6(c/x) + .4(c/(x-.5)) = m

Did I get the equation right?
How do I solve for x?

If I understand your question, it is tricky to answer because there is not a single price against which to calculate the margin. In the set-up you have described, there are two prices. If you calculate the margin relative to the higher cost, that will not be the average margin realized. Do you follow my logic, or have I completely missed the boat? You can, however, calculate the margin on the average price per can. The margin will be higher when selling a single can and lower when selling a pair of cans, but it will be the margin on the average can sold.
.
\(\displaystyle Let\ c = cost\ per\ can.\)
.
\(\displaystyle Let\ m = average\ margin\ per\ can.\)
.
\(\displaystyle Let\ x = price\ per\ can\ for\ a\ single\ can.\)
.
\(\displaystyle Let\ x - 0.5 = price\ per\ can\ for\ a\ pair\ of\ cans.\)
.
\(\displaystyle For\ 100\ sales,\ cans\ sold = 60\ singles + 40\ pairs= 60 + (2 * 40) = 60 + 80 = 140\ cans.\) Do I have this right?
.
\(\displaystyle For\ 100\ sales,\ revenue = 60x + 80(x - 0.5) = 60x +80x – 40 = 140x - 40.\)
.
\(\displaystyle Average\ price = \dfrac{140x - 40}{140} \approx x - 0.29.\)
.
\(\displaystyle x - 0.29 \approx \dfrac{c}{1 - m} \implies x \approx \dfrac{c}{1 - m} + 0.29.\)
.
So for example if your cost per can is 70 cents and you want a 35% margin,

.
then, approximately, x = 0.70 /(1 - 0.35) + 0.29 = 1.37.
.
On the 60 sales of single cans, your revenue is 60 * 1.37 = 82.20.
.
On the 40 sales of pairs of cans, your revenue is (1.37 - 0.50) * 80 = 0.87 * 80 = 69.60. Total revenue = 151.80.
.
Total cost = 140 * 0.70 = 98.00.
.
So Gross Profit = 53.60,and 35% of 151.80 is 53.13, which is close enough for government work.

 

[Mr Cheeseman]

Thank You!

As you say close enough. Your logic was spot on, we want a higher margin on the single sale and a lower margin on the two for sale. We are trying to get the blended margin to maintain our desired profits. I added your formula to my spreadsheet and then added another column that calculates the true blended margin which is easier to do when you have the retails. So i can just make slight adfjustment to get the blended margin I desire. Next I will make a seperate spreadsheet to do this calculation with any cost, margin and retail difference so I can do it for the many products we now compete with a two for price. Thank you again!

 

[JeffM]

As you say close enough. Your logic was spot on, we want a higher margin on the single sale and a lower margin on the two for sale. We are trying to get the blended margin to maintain our desired profits. I added your formula to my spreadsheet and then added another column that calculates the true blended margin which is easier to do when you have the retails. So i can just make slight adfjustment to get the blended margin I desire. Next I will make a seperate spreadsheet to do this calculation with any cost, margin and retail difference so I can do it for the many products we now compete with a two for price. Thank you again!

Glad to have been of help. Remember that the 29 cents add on is specific to the situation you described about 60 sales of singles and 40 sales of pairs and a price discount of 50 cents. If any one of those numbers changes, 29 cents is wrong.