Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,074
    Thanks
    12
    Thanked 36 Times in 35 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.

  2. #2
    New Lounger Ensemble's Avatar
    Join Date
    Apr 2013
    Location
    Centurion
    Posts
    23
    Thanks
    9
    Thanked 1 Time in 1 Post
    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)

  3. The Following User Says Thank You to Ensemble For This Useful Post:

    Maudibe (2014-04-04)

  4. #3
    4 Star Lounger
    Join Date
    Mar 2010
    Location
    east coast
    Posts
    416
    Thanks
    70
    Thanked 5 Times in 5 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.



    Quote Originally Posted by kweaver View Post
    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.

  5. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 227 Times in 210 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
    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.
    Last edited by Maudibe; 2014-04-04 at 22:45.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •