Hello,
Using Excel 2007.
I have a pricing scale based on total cumulative annual sales:
0-1000 units - $3/unit
1001-2000 units - $2/unit
2001- 3000 units - $1/unit
3001 units and over - $0.5/unit
I place 1 order per month, different quantity every time.
What I need to calculate is how much I will be paying per unit at any given month in a year.
For ex:
Cumulative sales in May are 900 units and cumulative sales in June happen to be 1200 units (a purchased of 300 in June), then June unit price is ((100*3)+(200*2))/300. In order to do what-if scenarios I need to find a formula that could do this calculation given the pricing scale.
Does anyone know how this can be done?
Thank you
Bookmarks