Solve
a problem using Solver
|
||||||||||
Month
|
Q1
|
Q2
|
Q3
|
Q4
|
Total
|
|||||
Seasonality
|
0.9
|
1.1
|
0.8
|
1.2
|
||||||
Units
Sold
|
3,592
|
4,390
|
3,192
|
4,789
|
15,962
|
|||||
Sales
|
$143,662
|
$175,587
|
$127,700
|
$191,549
|
$638,498
|
Color
Code
|
||||
Cost
|
89,789
|
109,742
|
79,812
|
119,718
|
399,061
|
|||||
Gross
|
53,873
|
65,845
|
47,887
|
71,831
|
239,437
|
Target
|
||||
Salesforce
|
8,000
|
8,000
|
9,000
|
9,000
|
34,000
|
Changing
|
||||
Advertising
|
10,000
|
10,000
|
10,000
|
10,000
|
40,000
|
|||||
Overhead
|
21,549
|
26,338
|
19,155
|
28,732
|
95,775
|
Constraints
|
||||
Total
Costs
|
39,549
|
44,338
|
38,155
|
47,732
|
169,775
|
|||||
Prod.
Profit
|
$14,324
|
$21,507
|
$9,732
|
$24,099
|
$69,662
|
|||||
Profit
Margin
|
10%
|
12%
|
8%
|
13%
|
11%
|
|||||
Product
Price
|
$40.00
|
|||||||||
Product
Cost
|
$25.00
|
|||||||||
Solve a problem using Solver
values to maximize or minimize another price, enter and change constraints, and save a difficulty model.
This examples show you how to work with the representation on top to solve for one value or several
Row Contains Details
3 Fixed values Seasonality factor: sales are higher in quarters 2 and 4,
and
lower in quarters 1 and 3.
5 =35*B3*(B11+3000)^0.5 Forecast for units sold each quarter: row 3 contains
the
seasonality factor; row 11 contains the cost of
advertising.
6 =B5*$B$18 Sales revenue: forecast for units sold (row 5) times
price
(cell B18).
7 =B5*$B$19 Cost of sales: forecast for units sold (row 5) times
product
cost (cell B19).
8 =B6-B7 Gross margin: sales revenues (row 6) minus cost of
sales
(row 7).
10 Fixed
values Sales
personnel expenses.
11 Fixed
values Advertising
budget (about 6.3% of sales).
12 =0.15*B6 Corporate overhead
expenses: sales revenues (row 6)
times
15%.
13 =SUM(B10:B12) Total costs: sales personnel expenses (row 10) plus
advertising
(row 11) plus overhead (row 12).
15 =B8-B13 Product profit: gross margin (row 8) minus total costs
(row
13).
16 =B15/B6 Profit margin: profit (row 15) divided by sales revenue
(row
6).
18 Fixed
values Product
price.
19 Fixed values Product cost.
This is a typical marketing model that displays sales rising from a bottom figure perhaps due to the sales
personnel along with increases in advertising, but with withdrawing returns. example, the initial
$5,000 of advertising in Q1 yields about 1,092 increment units sold, but the next $5,000 yields only
775 units.
You can use Solver to find out whether the marketing budget is too low, and or advertising
should be allocated in a different way over time to take advantage of the changing seasonality factor.
Solving for a Value to Maximize an
additional 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.
- On the Tools menu,
- click Solver.
- In the Set target cell box,
- type b15 or select cell B15 (first-quarter profits) on the worksheet.
- Select the Max option.
- In the By changing cells box,
- type b11 or select cell B11 (first-quarter advertising) on the worksheet.
- Click Solve.
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.
After
you examine the results, select Restore original values and
click OK
to discard the results and return cell
B11 to its former value.
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.
- On the Tools menu, click Solver.
- In the Set target cell box,
- type f15 or select cell F15 (total profits for the year) on the worksheet.
- Make sure the Max option is selected. In the By changing cells box,
- type b11:e11 or select cells B11:E11 (the advertising budget for each of the four quarters) on the worksheet.
- Click Solve.
- After you examine the results,
- click Restore original values and
- click OK to discard the results and return all cells to their former values.
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.
- On the Tools menu,
- click Solver, and then
- click Add.
- The Add Constraint dialog box appears.
- In the Cell reference box,
- type f11 or select cell F11 (advertising total) on the worksheet.
- Cell F11 must be less than or equal to $40,000.
- The relationship in the Constraint box is <= (less than or equal to) by default,
- so you don't have to change it. In the box next to the relationship, type 40000.
- Click OK, and then
- click Solve.
- After you examine the results,
- click Restore original values and then
- click OK to discard the results and return the cells to their former values.
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.
- On the Tools menu,
- click Solver.
- The constraint, $F$11<=40000, should already be selected in the Subject to the constraints box.
- Click Change.
- In the Constraint box,
- change 40000 to 50000.
- Click OK, and then
- click Solve.
- Click Keep solver solution and then
- click OK to keep the results that are displayed on the worksheet.
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.
- On the Tools menu,
- click Solver, and then
- click Options.
- Click Save Model.
- In the Select model area box,
- type h15:h18 or
- select cells H15:H18 on the worksheet.
- Click OK.
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.
No comments:
Post a Comment