2014-03-27, 13:36 #1
- Join Date
- Jan 2001
- La Jolla,CA
- 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 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.
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!
2014-03-28, 08:03 #2
Let's define the constants:
Total Volume = 500
Max Gross = 100
Let's Define the Known Variables:
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:
2014-04-03, 12:10 #3
- Join Date
- Mar 2010
- east coast
- Thanked 5 Times in 5 Posts
not making sense to me
when you raise the price you will sell fewer items
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.
2014-04-04, 22:32 #4
- Join Date
- Aug 2010
- Pa, USA
- Thanked 199 Times in 186 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 sold
Last edited by Maudibe; 2014-04-04 at 22:45.