Variable units and prices to achieve a fixed revenue

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
OrdersRevenue
ProductUnit PriceMonth 1Month 2Month 3Month 1Month 2Month 3Average
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

Senior Member
Joined
Feb 17, 2004
Messages
1,437
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

New member
Joined
Feb 28, 2019
Messages
2
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

Senior Member
Joined
Feb 17, 2004
Messages
1,437
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!
 
Top