Exact Percentage Modifier - Automatically Using Goal Seek

Isn't budgeting fun?

No is the answer to that question. It isn't fun to meet budgets, and isn't fun to create budgets. 

The reason for the latter, is due to the higher ups giving you A number that you must reach. However well you modeled out your projections, however strong your argument, you usually are told to "put a modifier on this budget to get the end figure to be x". 

Is there a quick way to do that, so I can get this thing over with?

Yes there is! 

I start with my simple budget - and add in a second column linking to the modifier cell (which is just 1.00 currently). Let's say I am aiming for total sales of 2,321,000.

Simple Budget.png

Previously I would have just guessed at the modifier for awhile to get it close, however there is a handy feature called "What - If Analysis" in the data tab. 

What if analysis.png

This lets you tell Excel to play with a cell, until you get the value you specified. Click on it, and select "Goal Seek". Set cell H7 to value 2,321,000 by changing G9. 

Goal Seek.png

Hit okay, and enjoy watching Excel think about it for a few seconds. It will then say it finds a solution, in our case 1.48. 

Goal Seek Results.png

Does this mean I won't have a job anymore?

No. You can impress your manager by doing this easily, on the fly during your budgeting meeting. They will think you are doing magic, and as long as you are not burnt as a witch, they should be impressed. 

It isn't perfect and can fail. For example if you had some rounding formulas in the mix, I had it fail to find a solution and give me gibberish. So be sure to keep it simple, which is aways the best strategy when it comes to automation.