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.