Results 1 to 4 of 4

20140327, 13:36 #1
 Join Date
 Jan 2001
 Location
 La Jolla,CA
 Posts
 1,040
 Thanks
 11
 Thanked 35 Times in 34 Posts
Looking for a formula for max price given an average price
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 predetermined "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.

Subscribe to our Windows Secrets Newsletter  It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20140328, 08:03 #2
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)

The Following User Says Thank You to Ensemble For This Useful Post:
Maudibe (20140404)

20140403, 12:10 #3
 Join Date
 Mar 2010
 Location
 east coast
 Posts
 338
 Thanks
 54
 Thanked 3 Times in 3 Posts
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.

20140404, 22:32 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,014
 Thanks
 37
 Thanked 176 Times in 163 Posts
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 soldLast edited by Maudibe; 20140404 at 22:45.