Variable units and prices to achieve a fixed revenue

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




Just a note I have also posted this on a math(s) help forum as to be honest I am a novice poster and I wasn't sure if this is best seen as a maths problem or an excel one?