Make your own free website on Tripod.com
Quick Tour of Microsoft Excel Solver
Month
Seasonality
 
Units Sold
Sales Revenue
Cost of Sales
Gross Margin
 
Salesforce
Advertising
Corp Overhead
Total Costs
 
Prod. Profit
Profit Margin
Product Price
Product Cost
The following examples show you how to work with the model above to solve for one value or several
values to maximize or minimize another value, enter and change constraints, and save a problem model.
 
Row
3
 
 
5
 
 
 
6
 
 
7
 
 
8
 
 
10
 
11
 
12
 
 
13
 
 
15
 
 
16
 
 
18
 
19
 
This is a typical marketing model that shows sales rising from a base figure (perhaps due to the sales
personnel) along with increases in advertising, but with diminishing returns.  For example, the first
$5,000 of advertising in Q1 yields about 1,092 incremental units sold, but the next $5,000 yields only
about 775 units more.
 
You can use Solver to find out whether the advertising budget is too low, and whether advertising 
should be allocated differently over time to take advantage of the changing seasonality factor.
 
Solving for a Value to Maximize Another Value
One way you can use Solver is to determine the maximum value of a cell by changing another cell.  The
two cells must be related through the formulas on the worksheet.  If they are not, changing the value in
one cell will not change the value in the other cell.
 
For example, in the sample worksheet, you want to know how much you need to spend on advertising
to generate the maximum profit for the first quarter.  You are interested in maximizing profit by changing
advertising expenditures.
 
n
 
 
 
 
You will see messages in the status bar as the problem is set up and Solver starts working.  After a
moment, you'll see a message that Solver has found a solution.  Solver finds that Q1 advertising of
$17,093 yields the maximum profit $15,093.
 
n
 
 
Resetting the Solver Options
 
If you want to return the options in the Solver Parameters dialog box to their original settings so that
you can start a new problem, you can click Reset All.
 
Solving for a Value by Changing Several Values
 
You can also use Solver to solve for several values at once to maximize or minimize another value.  For
example, you can solve for the advertising budget for each quarter that will result in the best profits for
the entire year.  Because the seasonality factor in row 3 enters into the calculation of unit sales in row 5
as a multiplier, it seems logical that you should spend more of your advertising budget in Q4 when the 
sales response is highest, and less in Q3 when the sales response is lowest.  Use Solver to determine
the best quarterly allocation.
 
n
 
 
 
 
n
 
 
You've just asked Solver to solve a moderately complex nonlinear optimization problem; that is, to find
values for the four unknowns in cells B11 through E11 that will maximize profits.  (This is a nonlinear
problem because of the exponentiation that occurs in the formulas in row 5).  The results of this
unconstrained optimization show that you can increase profits for the year to $79,706 if you spend
$89,706 in advertising for the full year.
 
However, most realistic modeling problems have limiting factors that you will want to apply to certain
values.  These constraints may be applied to the target cell, the changing cells, or any other value that
is related to the formulas in these cells.
 
Adding a Constraint
 
So far, the budget recovers the advertising cost and generates additional profit, but you're reaching a
point of diminishing returns.  Because you can never be sure that your model of sales response to
advertising will be valid next year (especially at greatly increased spending levels), it doesn't seem
prudent to allow unrestricted spending on advertising.
 
Suppose you want to maintain your original advertising budget of $40,000.  Add the constraint to the 
problem that limits the sum of advertising during the four quarters to $40,000.
 
n
 
 
 
 
 
 
n
 
 
The solution found by Solver allocates amounts ranging from $5,117 in Q3 to $15,263 in Q4.  Total 
Profit has increased from $69,662 in the original budget to $71,447, without any increase in the
advertising budget.
 
Changing a Constraint
 
When you use Microsoft Excel Solver, you can experiment with slightly different parameters to decide
the best solution to a problem.  For example, you can change a constraint to see whether the results
are better or worse than before.  In the sample worksheet, try changing the constraint on advertising
dollars to $50,000 to see what that does to total profits.
 
n
 
 
 
 
 
Solver finds an optimal solution that yields a total profit of $74,817.  That's an improvement of $3,370
over the last figure of $71,447.  In most firms, it's not too difficult to justify an incremental investment of
$10,000 that yields an additional $3,370 in profit, or a 33.7% return on investment.  This solution also
results in profits of $4,889 less than the unconstrained result, but you spend $39,706 less to get there.
 
Saving a Problem Model
 
When you click Save on the File menu, the last selections you made in the Solver Parameters 
dialog box are attached to the worksheet and retained when you save the workbook.  However, you
can define more than one problem for a worksheet by saving them individually using Save Model in 
the Solver Options dialog box.  Each problem model consists of cells and constraints that you 
entered in the Solver Parameters dialog box.
 
When you click Save Model, the Save Model dialog box appears with a default selection, based 
on the active cell, as the area for saving the model.  The suggested range includes a cell for each 
constraint plus three additional cells.  Make sure that this cell range is an empty range on the 
worksheet.
 
n
 
 
 
Note  You can also enter a reference to a single cell in the Select model area box.  Solver will use
this reference as the upper-left corner of the range into which it will copy the problem specifications.
 
 
To load these problem specifications later, click Load Model on the Solver Options dialog box,
type h15:h18 in the Model area box or select cells H15:H18 on the sample worksheet, and then
click OK.  Solver displays a message asking if you want to reset the current Solver option settings with
the settings for the model you are loading.  Click OK to proceed.