calculate/convert volume price to monthly average price

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Let's assume that we have a price that is dependant on volume:
0-1k units: $10
1k-2k units: $9
2k-5k units: $8
5k-10k units:$7
10k-20k units: $6

Now, based on a volume forecast, I want to forecast the average price in a
given month. I started out using IF-formulas (thought that it would be enough
with 5 conditions), but I soon discovered that it was far more complex than
what I anticipated. Is there a nice formula for this (or VBA code) or is
there someone who has an idea on how to attack this problem in a good way?
 
How will you predict the number of sales at each volume level?
If I thought the percentages would be: 5, 20, 50, 20, 5
I could compute an average with
=SUMPRODUCT({0.05,0.2,0.5,0.2,0.05},{10,9,8,7,6})
The answer, of course, is $8.00 since I used a symmetrical distribution.
Any help?
 
Unfortunately the total volume over the year is unpredictable so I can not
use percentages.

"Bernard Liengme" skrev:
 
Back
Top