Scooter222
New member
- Joined
- Feb 28, 2019
- Messages
- 2
Hi,
I hope someone can help with a problem I know is solvable, just not sure how!
I have a client who wants to buy a combination of 500+ items (services) in varying quantities over a 12 month period.
We are going to use fixed resources to provide this and therefore I want to have the total revenue in each month the same.
I know in advance what the quantities per month to be ordered are and have free rein to set individual prices. I just can't work out how to create a formula that will achieve this.
As an example below I have 3 products over 3 months below which I have reverse-engineered to get pretty much the right answer but it was a manual process and impractical over a bigger data set.
Thank you in advance!
Scott
I hope someone can help with a problem I know is solvable, just not sure how!
I have a client who wants to buy a combination of 500+ items (services) in varying quantities over a 12 month period.
We are going to use fixed resources to provide this and therefore I want to have the total revenue in each month the same.
I know in advance what the quantities per month to be ordered are and have free rein to set individual prices. I just can't work out how to create a formula that will achieve this.
As an example below I have 3 products over 3 months below which I have reverse-engineered to get pretty much the right answer but it was a manual process and impractical over a bigger data set.
Thank you in advance!
Scott
Orders | Revenue | |||||||
Product | Unit Price | Month 1 | Month 2 | Month 3 | Month 1 | Month 2 | Month 3 | Average |
A | 5 | 5 | 1 | 8 | £ 25.00 | £ 5.00 | £ 40.00 | |
B | 7 | 3 | 3 | 2 | £ 21.00 | £ 21.00 | £ 14.00 | |
C | 10 | 2 | 4 | 1 | £ 20.00 | £ 40.00 | £ 10.00 | |
10 | 8 | 11 | £ 66.00 | £ 66.00 | £ 64.00 | £ 65.33 | ||
Variance | £ 0.67 | £ 0.67 | -£ 1.33 |