# Variable units and prices to achieve a fixed revenue

#### Scooter222

##### New member
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.

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

##### Senior Member
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
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
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!