Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bultgren
 
Posts: n/a
Default calculate/convert volume price to monthly average price

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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
 
Posts: n/a
Default calculate/convert volume price to monthly average price

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?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bultgren" wrote in message
...
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?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bultgren
 
Posts: n/a
Default calculate/convert volume price to monthly average price

Unfortunately the total volume over the year is unpredictable so I can not
use percentages.

"Bernard Liengme" skrev:

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?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bultgren" wrote in message
...
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?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I get the average price per bid for an individual month? matt Excel Discussion (Misc queries) 5 July 30th 05 11:54 AM
MSNStockQuote Function Parameters Bobtarrel Excel Worksheet Functions 2 July 17th 05 02:36 PM
Mix, Volume and Price impact on revenue Stumped in San Francisco Excel Discussion (Misc queries) 1 June 12th 05 01:16 PM
calculate monthly average percentage of change vikgarden Excel Worksheet Functions 2 April 15th 05 07:38 PM
Calculating Net Position and Average Price carl Excel Worksheet Functions 6 October 31st 04 02:08 PM


All times are GMT +1. The time now is 02:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"