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?

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.