Excel Solver with VBA

The solver function in Excel is and add in which allows you to run scenarios.  I will explain using a simple example.  Let's say you wanted your expenses to be 33% of your total revenue.  You could use solver to work out what your revenue needs to be based on these expenses.  As you adjust your expenses running the solver routine will update the revenue.

I have put the desired solver % return in a separate cell and coloured the revenue cell in yellow to indicate that this cell will change as the VBA procedure runs.

If you do not already have the Solver Add-in then go to File - Options - Addins

Now at the bottom choose Manage Excel Add-Ins and click Go.

Then click on the Analysis Toolpack - VBA and click OK.   That should have Solver set up.

I have set up a simple Excel VBA procedure which tracks revenue and expenses and solves for required reveue based on a target percentage of expenses.

The Cell F10 is where you put the % you want to achieve and the code should do the rest by changing cell C10 (the revenue).

The solver does not only work for percentages.  You could specify a desired monetary outcome and solver will give the result for this also.

I have attached a sample Excel file to show the workings of this VBA procedure.