Variable units and prices to achieve a fixed revenue

[Scooter222]

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

		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

 

[Denis]

What makes you think a single "formula" is possible?

From your example, 12 variables would be required:
5,7,10 (prices)
5,3,2 (1st month)
1,3,4 (2nd month)
8,2,1 (3rd month) ....... right?

From that, you want to end up with 65.33 as output ........ right?

Impossible for a "single formula" to handle this.
A computer program would be required (like Excel).

 

[Scooter222]

Hi Denis, thanks for taking the time to respond, I appreciate it.

The prices are variables, whereas the units sold (below) are constants.
5,3,2 (1st month)
1,3,4 (2nd month)
8,2,1 (3rd month)

You are correct, I want to end up with 65.33 as an output (or any value really as long as the prices are +ve and the monthly revenue is the same each month).

I suspected this may require a computer program but thought it was worth checking first as many smarter people than me out there.

Thanks,

Scott

 

[Denis]

Variables:
Product A: a = 5
Product B: b = 7
Product C: c = 10

Fixed:
Product A: 14
Product B: 8
Product C: 7
Total: 29

average = (14a + 8b + 7c) / 29 = [14(5) + 8(7) + 7(10)] / 29 = 65.33

As example, if at one time you had 5 products, then the job would be
similar to above, but using A,B,C,D,E

Good luck!