Solver Samplessubject1 subject2Microsoft Corp.Sub oneExample models illustrating applications of Microsoft Excel's Solver.Guang Gavin Lu1996-10-03T21:59:12Z2003-05-11T13:21:13ZCategory1ManagerMS & Sunhttp://www.sun.com11.492016#8080FF17#80206018#FFFFC019#A0E0E020#60008022#0080C023#C0C0FF33#69FFFF36#A6CAF037#CC9CCC39#E3E3E342#33993343#99993344#99663345#99666648#3333CC49#33666652#66330055#424242552011970-15-15854FalseFalseQuick Tour of Microsoft Excel SolverMonthQ1Q2Q3Q4TotalSeasonality0.91.10.81.2Units Sold3591.55258906228444389.67538663168123192.49119027758624788.736785416379515962.455951387932Sales Revenue143662.10356249136175587.01546526724127699.64761110344191549.47141665517638498.2380555172Color CodingCost of Sales89788.8147265571109741.8846657920479812.27975693965119718.41963540949399061.39878469828Gross Margin53873.28883593426165845.130799475247887.36785416379371831.051781245682239436.83927081892 Target cellSalesforce800080009000900034000 Changing cellsAdvertising1000010000100001000040000Corp Overhead21549.31553437370526338.05231979008419154.94714166551428732.42071249827595774.735708327586 ConstraintsTotal Costs39549.315534373744338.05231979008438154.94714166551447732.420712498279169774.73570832756Prod. Profit14323.9733015605621507.0784796851159732.420712498278624098.63106874740369662.103562491364Profit Margin9.9705997241852973E-20.122486725016061437.6213371724700413E-20.125808914483133550.10910304744871398Product Price40Product Cost25The following examples show you how to work with the model above to solve for one value or severalvalues to maximize or minimize another value, enter and change constraints, and save a problem model.RowContainsExplanation3Fixed valuesSeasonality factor: sales are higher in quarters 2 and 4,and lower in quarters 1 and 3.5=35*B3*(B11+3000)^0.5Forecast for units sold each quarter: row 3 containsthe seasonality factor; row 11 contains the cost ofadvertising.6=B5*$B$18Sales revenue: forecast for units sold (row 5) timesprice (cell B18).7=B5*$B$19Cost of sales: forecast for units sold (row 5) timesproduct cost (cell B19).8=B6-B7Gross margin: sales revenues (row 6) minus cost ofsales (row 7).10Fixed valuesSales personnel expenses.11Fixed valuesAdvertising budget (about 6.3% of sales).12=0.15*B6Corporate overhead expenses: sales revenues (row 6)times 15%.13=SUM(B10:B12)Total costs: sales personnel expenses (row 10) plusadvertising (row 11) plus overhead (row 12).15=B8-B13Product profit: gross margin (row 8) minus total costs(row 13).16=B15/B6Profit margin: profit (row 15) divided by sales revenue(row 6).18Fixed valuesProduct price.19Fixed valuesProduct cost.This is a typical marketing model that shows sales rising from a base figure (perhaps due to the salespersonnel) 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 onlyabout 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 ValueOne way you can use Solver is to determine the maximum value of a cell by changing another cell. Thetwo cells must be related through the formulas on the worksheet. If they are not, changing the value inone 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 advertisingto generate the maximum profit for the first quarter. You are interested in maximizing profit by changingadvertising expenditures.nOn 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 amoment, 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.nAfter you examine the results, select Restore original values and click OK todiscard the results and return cell B11 to its former value.Resetting the Solver OptionsIf you want to return the options in the Solver Parameters dialog box to their original settings so thatyou can start a new problem, you can click Reset All.Solving for a Value by Changing Several ValuesYou can also use Solver to solve for several values at once to maximize or minimize another value. Forexample, you can solve for the advertising budget for each quarter that will result in the best profits forthe entire year. Because the seasonality factor in row 3 enters into the calculation of unit sales in row 5as 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 determinethe best quarterly allocation.nOn the Tools menu, click Solver. In the Set target cell box, type f15 or selectcell F15 (total profits for the year) on the worksheet. Make sure the Max option isselected. 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.nAfter you examine the results, click Restore original values and click OK todiscard 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 findvalues for the four unknowns in cells B11 through E11 that will maximize profits. (This is a nonlinearproblem because of the exponentiation that occurs in the formulas in row 5). The results of thisunconstrained 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 certainvalues. These constraints may be applied to the target cell, the changing cells, or any other value thatis related to the formulas in these cells.Adding a ConstraintSo far, the budget recovers the advertising cost and generates additional profit, but you're reaching apoint of diminishing returns. Because you can never be sure that your model of sales response toadvertising will be valid next year (especially at greatly increased spending levels), it doesn't seemprudent 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.nOn the Tools menu, click Solver, and then click Add. The Add Constraintdialog 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. ClickOK, and then click Solve.nAfter you examine the results, click Restore original values and then click OKto 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 theadvertising budget.Changing a ConstraintWhen you use Microsoft Excel Solver, you can experiment with slightly different parameters to decidethe best solution to a problem. For example, you can change a constraint to see whether the resultsare better or worse than before. In the sample worksheet, try changing the constraint on advertisingdollars to $50,000 to see what that does to total profits.nOn the Tools menu, click Solver. The constraint, $F$11<=40000, should already be selected in the Subject to the constraints box. Click Change. Inthe 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,370over 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 alsoresults in profits of $4,889 less than the unconstrained result, but you spend $39,706 less to get there.Saving a Problem ModelWhen you click Save on the File menu, the last selections you made in the Solver Parametersdialog box are attached to the worksheet and retained when you save the workbook. However, youcan 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.nOn 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 theworksheet. Click OK.Note You can also enter a reference to a single cell in the Select model area box. Solver will usethis 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 thenclick OK. Solver displays a message asking if you want to reset the current Solver option settings withthe settings for the model you are loading. Click OK to proceed.