Solver or What-If to Allocate Budget

**catccc** · 05-19-2010, 11:21 AM

I'm hoping someone can help me automate this to find a solution. I have a total budget of 37M that must be allocated among 6 accounts. There is a min and max value for the % of total budget that each account can contain. The total allocation to the first two accounts must equal 10.5M, and the total allocation to the last four accounts must equal 26.5M. The total of all allocated amounts and percents must be 37.5M and 100%, respectively.

I have multiple scenarios like this to run and I feel like there must be a way to reach a solution that is better than me guessing and tweaking percentages until it works. I'm wondering if excel solver or another tool or add-in might be able to solve this.

A file is attached. There is also an example of a solution I got to the long and hard way in the worksheet. If anyone can provide any help or even a partial solution, anything to make it go a little faster, I would really appreciate it. Thanks in advance, Excel Gurus!

Edit- oops. I simplified the numbers in the example attached for my narrative of the problem, but left the example solution with the original numbers. I think you'll still get the idea of the goal here despite the discrepancy. Sorry for any confusion.

**StephenR** · 05-19-2010, 11:36 AM

There would appear to be a mind-boggling number of possibilities.

Eg. for the first two accounts you could have 10,492,000 and 8,000 or 10,491,999 and 8,001, and so on. Is that right?

**catccc** · 05-19-2010, 12:18 PM

Yes, you are correct. There are many many solutions, though I only need one, and as long as the percentages are within the given min & max, it is a-ok.

So I just spent a little more time playing with it, and it looks like it is easily solved with Solver if you break down the problem into two parts, and focus on percentages.

Part one is to make the sum of percentages for the first two accounts = to 10.5M divided by 37M, or 28.38% within the min and max for each account.

Part two is to make the sum of percentages for the last 4 accounts = to 26.5M divided by 37M, or 71.62 within the min and max for each account.

So for part one, you set up a target cell that is the sum of the percentages for accounts 1 & 2. In solver, make your target cell equal to value of .2838 by the cells for the two percentages for accounts 1 & 2. Subject to the constraints where each percent cell's value falls between the specified min and max.... click solve... tada! A bit of multiplication and you are good to go, for the most part. I found some minimal tweaking was needed in one case, but nothing compared to the guessing from before.

So, sorry if I littered the board with this question since I already figured it out. But I'm glad to have discovered the forums anyway... I am just getting started on some of the more advanced excel techniques and I think I'll find the community here very helpful.

**StephenR** · 05-19-2010, 12:21 PM

Not a problem. Thanks for posting your thoughts.

**matthewdete** · 02-10-2014, 05:21 PM

Would you be able to attach what you have come up with?

**Alf** · 02-13-2014, 05:31 PM

Probably something like this.

Alf

Solver or What-If to Allocate Budget

LinkBack

Thread Tools

Rate This Thread

Display

Solver or What-If to Allocate Budget

Re: Solver or What-If to Allocate Budget

Re: Solver or What-If to Allocate Budget

Re: Solver or What-If to Allocate Budget

Re: Solver or What-If to Allocate Budget

Re: Solver or What-If to Allocate Budget

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions