435414292.
xls
Vendas anuais de produtos com sazonalidade
Mês Quadrim.1 Quadrim. 2 Quadrim. 3 Quadrim. 4 Total
Sazonalidade 0.9 1.1 0.8 1.2
Unidades Vendidas 3,193 4,769 2,523 5,676 16,161
Valor das Vendas $127,710 $190,776 $100,906 $227,038 $646,430 Color Coding
Custo das Vendas 79,819 119,235 63,066 141,899 404,019
Lucro Bruto 47,891 71,541 37,840 85,139 242,411 Target cell
Custos Vendedores 8,000 8,000 9,000 9,000 34,000 Changing cells
Publicidade 7,273 12,346 5,117 15,263 40,000
Custos Administração 19,157 28,616 15,136 34,056 96,965 Constraints
Total Custos 34,430 48,963 29,253 58,319 170,965
Lucro Líquido $13,462 $22,578 $8,587 $26,820 $71,447
Margem Lucro 11% 12% 9% 12% 11%
Preço do Produto $40.00 40000
Custo do Produto $25.00
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 Contains Explanation
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
Page 1
435414292.xls
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 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.
Page 2
435414292.xls
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
Page 3
435414292.xls
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
Page 4
435414292.xls
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
Page 5
435414292.xls
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.
Page 6
