Looking for a formula for max price given an average price

Topic

New Reply

I saw this question somewhere and wonder if there’s an Excel solution.

I don’t know a formula that might solve this, but thought someone might. Here’s the post I saw:

I need help designing a formula which will give as a result, the maximum price per unit for which any number of units can be sold.
The variable which restricts this formula is, the campaign has a pre-determined “average price per unit” target, of which the campaign should end equalled to, or below, for all total units which were set to be sold.

So, here’s a scenario:
A store launches a sale campaign for orange juice whereby they intend to earn a gross of $100.00 by selling 500 fl. oz of oj, for an average of $0.2 per fl.oz or less.

Note: any campaign can start with the target average ($0.2/fl. oz), and give one or other variables: total units to be sold or total budget to be allocated.

But, since sales per unit are variable, some orange juice markets sell each fl. oz. for a higher price than the targeted $0.2/fl.oz, for example Alaska at $0.7/2 fl.oz, and some are lower like $0.10/3fl.oz in Florida, with various other price points possible between these, but never 0.

Therefore, in each sale, we approach the 500 ounces sold. We need a formula that returns the highest price possible over time as ounces are sold in order to ensure the price is at $0.2/fl. oz or less once 500 fl. ozs are sold at the end of the budgeted $100.

Reply | Quote

Replies

Let’s define the constants:
Total Volume = 500
Max Gross = 100
Let’s Define the Known Variables:
Avarage price
Volume Sold

Your Formula would then be:

(Max Gross-(Vol Sold x Avarage Price))/(Total Volume – Volume Sold)

or: (100-(Vol Sold x Avarage Price))/(500 – Volume Sold)

Reply | Quote

sorry
not making sense to me

when you raise the price you will sell fewer items
maybe none

when you lower the price you may sell more (or none if it is not low enough)
the relation is not at all linear and also depends on the market size and what you sold already

your first statment says to just set the price at the average or lower
and you get what you want. not sure you meant it that way.

and attempting to do what you indicate later does not mean that you will be able to do it at all.

and if you are trying to force an average over different markets that makes no sense either.

just put a big price on the juice. mail out coupons and advertise a big sale and sell all of it at the price you want.

or start with a high price and keep lowering it to create more sales.
repeat until all is sold.
you cant force a final average. so make the most you can and accept it.

I saw this question somewhere and wonder if there’s an Excel solution.

I don’t know a formula that might solve this, but thought someone might. Here’s the post I saw:

I need help designing a formula which will give as a result, the maximum price per unit for which any number of units can be sold.
The variable which restricts this formula is, the campaign has a pre-determined “average price per unit” target, of which the campaign should end equalled to, or below, for all total units which were set to be sold.

So, here’s a scenario:
A store launches a sale campaign for orange juice whereby they intend to earn a gross of $100.00 by selling 500 fl. oz of oj, for an average of $0.2 per fl.oz or less.

Note: any campaign can start with the target average ($0.2/fl. oz), and give one or other variables: total units to be sold or total budget to be allocated.

But, since sales per unit are variable, some orange juice markets sell each fl. oz. for a higher price than the targeted $0.2/fl.oz, for example Alaska at $0.7/2 fl.oz, and some are lower like $0.10/3fl.oz in Florida, with various other price points possible between these, but never 0.

Therefore, in each sale, we approach the 500 ounces sold. We need a formula that returns the highest price possible over time as ounces are sold in order to ensure the price is at $0.2/fl. oz or less once 500 fl. ozs are sold at the end of the budgeted $100.

Reply | Quote

What is not linear is the graph of the average prices the ounces are sold because it is arbitray. However, a scatter chart of them would be. What is liner, is the formula to find the average price per ounce for the remaining ounces to achieve the remaining amount of Gross needed.

I am on par with Ensemble but I would tweak it a bit so that the needed average price per ounce can be calculated at any point in time:

(Remaining Gross – (Volume sold that day x price sold that day)) / (Remaining volume – volume sold that day) = average price/oz that the remaining ounces must be sold.

So at the start it would be:
($100 – (0 x $0) / (500 – 0) = $.2 Average price as expected

If on day 1 you sold 75 oz. at $.15 ($11.25) then:
($100 – (75 x $.15) / 500 – 75) = .21 You need to sell the remaining 425 oz at an average of .21 to make your $100 gross

If on day 2 with a remaining gross of $88.75, you sold 190 oz at $.25 ($47.50) then:
($88.75 – (190 x $.25) / (425 – 190) = .18 You need to sell the remaining 235 oz at an average of .18 to make your $100 gross

If on day 3 with a remaining gross of $41.25, you sold 120 oz at $.1 ($12.50) then:
($41.25 – (120 x $.1) / (235 – 120) = .25 You need to sell the remaining 115 oz at an average of .25 to make your $100 gross

If on day 4 with a remaining gross of $28.75,you sold all 115 oz at $.25 ($28.75) then:
($28.75 – (115 x $.25) / (115 – 115) = 0 You sold your 500 Oz for $100 which is an average of $.2 / Oz.

(give or take rounding of fractions of cents)

…to ensure the price is at $0.2/fl. oz or less once 500 fl. ozs are sold

All the jargon about the average $.2/ Oz. is irrelevant. As long as you sold all 500 Oz. for $100, then the average price/oz will always be $.2 no matter how many you sold for what price between the start and finish dates.

Reply | Quote