{"text_format":"3","workbook":"xls","n_sheets":7,"sheets":["Quick Tour","Product Mix","Shipping Routes","Staff Scheduling","Maximizing Income","Portfolio of Securities","Engineering Design"],"not_rendered":{"macros":1,"external_links":1,"defined_names":160},"read":"whole","date_system":"1900","formulas":"cached_only"}

{"sheet":"Quick Tour","index":0,"n_rows":188,"n_cols":10,"rows_kept":188,"flags":["formulas_cached_only"]}
["Quick Tour of Microsoft Excel Solver"]
["Month","Q1","Q2","Q3","Q4","Total"]
["Seasonality",0.9,1.1,0.8,1.2]
[]
["Units Sold",{"v":3591.5525890622844,"d":"3,592 "},{"v":4389.675386631681,"d":"4,390 "},{"v":3192.491190277586,"d":"3,192 "},{"v":4788.7367854163795,"d":"4,789 "},{"v":15962.455951387932,"d":"15,962 "}]
["Sales Revenue",{"v":143662.10356249136,"d":"$143,662"},{"v":175587.01546526724,"d":"$175,587"},{"v":127699.64761110344,"d":"$127,700"},{"v":191549.47141665517,"d":"$191,549"},{"v":638498.2380555172,"d":"$638,498"},null,"Color Coding"]
["Cost of Sales",{"v":89788.8147265571,"d":"89,789 "},{"v":109741.88466579204,"d":"109,742 "},{"v":79812.27975693965,"d":"79,812 "},{"v":119718.41963540949,"d":"119,718 "},{"v":399061.3987846983,"d":"399,061 "}]
["Gross Margin",{"v":53873.28883593426,"d":"53,873 "},{"v":65845.1307994752,"d":"65,845 "},{"v":47887.36785416379,"d":"47,887 "},{"v":71831.05178124568,"d":"71,831 "},{"v":239436.83927081892,"d":"239,437 "},{"$nulls":3},"   Target cell"]
[]
["Salesforce",{"v":8000,"d":"8,000 "},{"v":8000,"d":"8,000 "},{"v":9000,"d":"9,000 "},{"v":9000,"d":"9,000 "},{"v":34000,"d":"34,000 "},{"$nulls":3},"   Changing cells"]
["Advertising",{"v":10000,"d":"10,000 "},{"v":10000,"d":"10,000 "},{"v":10000,"d":"10,000 "},{"v":10000,"d":"10,000 "},{"v":40000,"d":"40,000 "}]
["Corp Overhead",{"v":21549.315534373705,"d":"21,549 "},{"v":26338.052319790084,"d":"26,338 "},{"v":19154.947141665514,"d":"19,155 "},{"v":28732.420712498275,"d":"28,732 "},{"v":95774.73570832759,"d":"95,775 "},{"$nulls":3},"   Constraints"]
["Total Costs",{"v":39549.3155343737,"d":"39,549 "},{"v":44338.052319790084,"d":"44,338 "},{"v":38154.947141665514,"d":"38,155 "},{"v":47732.42071249828,"d":"47,732 "},{"v":169774.73570832756,"d":"169,775 "}]
[]
["Prod. Profit",{"v":14323.97330156056,"d":"$14,324"},{"v":21507.078479685115,"d":"$21,507"},{"v":9732.420712498279,"d":"$9,732"},{"v":24098.631068747403,"d":"$24,099"},{"v":69662.10356249136,"d":"$69,662"}]
["Profit Margin",{"v":0.09970599724185297,"d":"10%"},{"v":0.12248672501606143,"d":"12%"},{"v":0.07621337172470041,"d":"8%"},{"v":0.12580891448313355,"d":"13%"},{"v":0.10910304744871398,"d":"11%"}]
[]
["Product Price",{"v":40,"d":"$40.00"}]
["Product Cost",{"v":25,"d":"$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",null,"Explanation"]
[3,"Fixed values",null,"Seasonality factor:  sales are higher in quarters 2 and 4,"]
[{"$nulls":3},"and lower in quarters 1 and 3."]
[]
[5,"=35*B3*(B11+3000)^0.5",null,"Forecast for units sold each quarter:  row 3 contains"]
[{"$nulls":3},"the seasonality factor; row 11 contains the cost of"]
[{"$nulls":3},"advertising."]
[]
[6,"=B5*$B$18",null,"Sales revenue:  forecast for units sold (row 5) times"]
[{"$nulls":3},"price (cell B18)."]
[]
[7,"=B5*$B$19",null,"Cost of sales:  forecast for units sold (row 5) times"]
[{"$nulls":3},"product cost (cell B19)."]
[]
[8,"=B6-B7",null,"Gross margin:  sales revenues (row 6) minus cost of"]
[{"$nulls":3},"sales (row 7)."]
[]
[10,"Fixed values",null,"Sales personnel expenses."]
[]
[11,"Fixed values",null,"Advertising budget (about 6.3% of sales)."]
[]
[12,"=0.15*B6",null,"Corporate overhead expenses:  sales revenues (row 6)"]
[{"$nulls":3},"times 15%."]
[]
[13,"=SUM(B10:B12)",null,"Total costs:  sales personnel expenses (row 10) plus"]
[{"$nulls":3},"advertising (row 11) plus overhead (row 12)."]
[]
[15,"=B8-B13",null,"Product profit:  gross margin (row 8) minus total costs"]
[{"$nulls":3},"(row 13)."]
[]
[16,"=B15/B6",null,"Profit margin:  profit (row 15) divided by sales revenue"]
[{"$nulls":3},"(row 6)."]
[]
[18,"Fixed values",null,"Product price."]
[]
[19,"Fixed values",null,"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."]
[]
["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","On the Tools menu, click Solver.  In the Set target cell box, type b15 or "]
[null,"select cell B15 (first-quarter profits) on the worksheet.  Select the Max option."]
[null,"In the By changing cells box, type b11 or select cell B11 (first-quarter advertising)"]
[null,"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."]
[]
["n","After you examine the results, select Restore original values and click OK to"]
[null,"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."]
[]
["n","On the Tools menu, click Solver.  In the Set target cell box, type f15 or select"]
[null,"cell F15 (total profits for the year) on the worksheet.  Make sure the Max option is"]
[null,"selected.  In the By changing cells box, type b11:e11 or select cells B11:E11"]
[null,"(the advertising budget for each of the four quarters) on the worksheet.  Click Solve."]
[]
["n","After you examine the results, click Restore original values and click OK to"]
[null,"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."]
[]
["n","On the Tools menu, click Solver, and then click Add.  The Add Constraint "]
[null,"dialog box appears.  In the Cell reference box, type f11 or select cell F11"]
[null,"(advertising total) on the worksheet.  Cell F11 must be less than or equal to $40,000."]
[null,"The relationship in the Constraint box is <= (less than or equal to) by default, so "]
[null,"you don't have to change it.  In the box next to the relationship, type 40000. Click"]
[null,"OK, and then click Solve."]
[]
["n","After you examine the results, click Restore original values and then click OK"]
[null,"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."]
[]
["n","On the Tools menu, click Solver.  The constraint, $F$11<=40000, should "]
[null,"already be selected in the Subject to the constraints box.  Click Change.  In"]
[null,"the Constraint box, change 40000 to 50000.  Click OK, and then click Solve."]
[null,"Click Keep solver solution and then click OK to keep the results that are "]
[null,"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."]
[]
["n","On the Tools menu, click Solver, and then click Options.  Click Save Model."]
[null,"In the Select model area box, type h15:h18 or select cells H15:H18 on the"]
[null,"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."]

{"sheet":"Product Mix","index":1,"n_rows":39,"n_cols":11,"rows_kept":39,"flags":["formulas_cached_only"]}
["Example 1:  Product mix problem with diminishing profit margin."]
["Your company manufactures TVs, stereos and speakers, using a common parts inventory",{"$nulls":7},"Color Coding"]
["of power supplies, speaker cones, etc.  Parts are in limited supply and you must determine"]
["the most profitable mix of products to build. But your profit per unit built decreases with",{"$nulls":9},"   Target cell"]
["volume because extra price incentives are needed to load the distribution channel."]
[{"$nulls":10},"   Changing cells"]
[]
[{"$nulls":3},"TV set","Stereo","Speaker",{"$nulls":4},"   Constraints"]
[null,null,"Number to Build->",100,100,100]
["Part Name","Inventory","No. Used"]
["Chassis",450,200,1,1,0]
["Picture Tube",250,100,1,0,0,null,"Diminishing"]
["Speaker Cone",800,500,2,2,1,null,"Returns"]
["Power Supply",450,200,1,1,0,null,"Exponent:"]
["Electronics",600,400,2,1,1,null,0.9]
[{"$nulls":3},"Profits:"]
[null,null,"By Product",{"v":4732.180083601453,"d":"$4,732"},{"v":3154.7867224009683,"d":"$3,155"},{"v":2208.350705680678,"d":"$2,208"}]
[null,null,"Total ",{"v":10095.317511683099,"d":"$10,095"}]
[]
["This model provides data for several products using common parts, each with a different profit margin"]
["per unit.  Parts are limited, so your problem is to determine the number of each product to build from the"]
["inventory on hand in order to maximize profits."]
[]
["Problem Specifications"]
[]
["Target Cell",null,"D18",null,"Goal is to maximize profit."]
[]
["Changing cells",null,"D9:F9",null,"Units of each product to build."]
[]
["Constraints",null,"C11:C15<=B11:B15",null,"Number of parts used must be less than or "]
[{"$nulls":4},"equal to the number of parts in inventory."]
[]
[null,null,"D9:F9>=0",null,"Number to build value must be greater than or"]
[{"$nulls":4},"equal to 0."]
[]
["The formulas for profit per product in cells D17:F17 include the factor ^H15 to show that profit per unit"]
["diminishes with volume.  H15 contains 0.9, which makes the problem nonlinear.  If you change H15 to"]
["1.0 to indicate that profit per unit remains constant with volume, and then click Solve again, the"]
["optimal solution will change.  This change also makes the problem linear."]

{"sheet":"Shipping Routes","index":2,"n_rows":46,"n_cols":12,"rows_kept":46,"flags":["formulas_cached_only"]}
["Example 2:  Transportation Problem."]
["Minimize the costs of shipping goods from production plants to warehouses near metropolitan demand"]
["centers, while not exceeding the supply available from each plant and meeting the demand from each"]
["metropolitan area."]
[]
[null,null,"Number to ship from plant x to warehouse y (at intersection):",{"$nulls":6},"Color Coding"]
["Plants:","Total","San Fran","Denver","Chicago","Dallas","New York"]
["S. Carolina",5,1,1,1,1,1,{"$nulls":4},"   Target cell"]
["Tennessee",5,1,1,1,1,1]
["Arizona",5,1,1,1,1,1,{"$nulls":4},"   Changing cells"]
[null,null,"---","---","---","---","---"]
["Totals:",null,3,3,3,3,3,{"$nulls":4},"   Constraints"]
[]
[null,"Demands by Whse -->",180,80,200,160,220]
["Plants:","Supply","Shipping costs from plant x to warehouse y (at intersection):"]
["S. Carolina",310,10,8,6,5,4]
["Tennessee",260,6,5,4,3,6]
["Arizona",280,3,4,5,5,9]
[]
["Shipping:",{"v":83,"d":"$83"},{"v":19,"d":"$19"},{"v":17,"d":"$17"},{"v":15,"d":"$15"},{"v":13,"d":"$13"},{"v":19,"d":"$19"}]
[]
["The problem presented in this model involves the shipment of goods from three plants to five regional"]
["warehouses.  Goods can be shipped from any plant to any warehouse, but it obviously costs more to"]
["ship goods over long distances than over short distances.  The problem is to determine the amounts"]
["to ship from each plant to each warehouse at minimum shipping cost in order to meet the regional"]
["demand, while not exceeding the plant supplies."]
[]
["Problem Specifications"]
[]
["Target cell",null,"B20",null,"Goal is to minimize total shipping cost."]
[]
["Changing cells",null,"C8:G10",null,"Amount to ship from each plant to each "]
[{"$nulls":4},"warehouse."]
[]
["Constraints",null,"B8:B10<=B16:B18",null,"Total shipped must be less than or equal to "]
[{"$nulls":4},"supply at plant."]
[]
[null,null,"C12:G12>=C14:G14",null,"Totals shipped to warehouses must be greater"]
[{"$nulls":4},"than or equal to demand at warehouses."]
[]
[null,null,"C8:G10>=0",null,"Number to ship must be greater than or equal "]
[{"$nulls":4},"to 0."]
[]
["You can solve this problem faster by selecting the Assume linear model check box in the Solver "]
["Options dialog box before clicking Solve.  A problem of this type has an optimum solution at which"]
["amounts to ship are integers, if all of the supply and demand constraints are integers."]

{"sheet":"Staff Scheduling","index":3,"n_rows":45,"n_cols":16,"rows_kept":45,"flags":["formulas_cached_only"]}
["Example 3:  Personnel scheduling for an Amusement Park."]
["For employees working five consecutive days with two days off, find the schedule that meets demand"]
["from attendance levels while minimizing payroll costs."]
[]
[]
["Sch.","   Days off",null,"Employees",null,"Sun","Mon","Tue","Wed","Thu","Fri","Sat",null,"Color Coding"]
["  A","Sunday, Monday",null,4,null,0,0,1,1,1,1,1]
["  B","Monday, Tuesday",null,4,null,1,0,0,1,1,1,1,{"$nulls":3},"   Target cell"]
["  C","Tuesday, Wed.",null,4,null,1,1,0,0,1,1,1]
["  D","Wed., Thursday",null,6,null,1,1,1,0,0,1,1,{"$nulls":3},"   Changing cells"]
["  E","Thursday, Friday",null,6,null,1,1,1,1,0,0,1]
["  F","Friday, Saturday",null,4,null,1,1,1,1,1,0,1,{"$nulls":3},"   Constraints"]
["  G","Saturday, Sunday",null,4,null,0,1,1,1,1,1,0]
[]
[null,null,"Schedule Totals:",32,null,24,24,24,22,20,22,28]
[]
[null,null,"Total Demand:",null,null,22,17,13,14,15,18,24]
[]
[null,"Pay/Employee/Day:",null,{"v":40,"d":"$40"}]
[null,"Payroll/Week:",null,{"v":1280,"d":"$1,280"}]
[]
["The goal for this model is to schedule employees so that you have sufficient staff at the lowest cost.  In"]
["this example, all employees are paid at the same rate, so by minimizing the number of employees working"]
["each day, you also minimize costs.  Each employee works five consecutive days, followed by two days"]
["off."]
[]
["Problem Specifications"]
[]
["Target cell",null,"D20",null,"Goal is to minimize payroll cost."]
[]
["Changing cells",null,"D7:D13",null,"Employees on each schedule."]
[]
["Constraints",null,"D7:D13>=0",null,"Number of employees must be greater than or equal"]
[{"$nulls":4},"to 0."]
[]
[null,null,"D7:D13=Integer",null,"Number of employees must be an integer."]
[]
[null,null,"F15:L15>=F17:L17",null,"Employees working each day must be greater than or"]
[{"$nulls":4},"equal to the demand."]
[]
["Possible schedules",null,"Rows 7-13",null,"1 means employee on that schedule works that day."]
[]
["In this example, you use an integer constraint so that your solutions do not result in fractional numbers of"]
["employees on each schedule.  Selecting the Assume linear model check box in the Solver Options "]
["dialog box before you click Solve will greatly speed up the solution process."]

{"sheet":"Maximizing Income","index":4,"n_rows":66,"n_cols":12,"rows_kept":66,"flags":["formulas_cached_only"]}
["Example 4:  Working Capital Management."]
["Determine how to invest excess cash in 1-month, 3-month and 6-month CDs so as to"]
["maximize interest income while meeting company cash requirements (plus safety margin)."]
[]
[null,"Yield","Term",null,"Purchase CDs in months:"]
["1-mo CDs:",{"v":0.01,"d":"1.0%"},1,null,"1, 2, 3, 4, 5 and 6",null,null,"Interest"]
["3-mo CDs:",{"v":0.04,"d":"4.0%"},3,null,"1 and 4",null,null," Earned:"]
["6-mo CDs:",{"v":0.09,"d":"9.0%"},6,null,"1",null,"Total",{"v":7700,"d":"$7,700"}]
[]
["Month:","Month 1","Month 2","Month 3","Month 4","Month 5","Month 6","End",null,"Color Coding"]
["Init Cash:",{"v":400000,"d":"$400,000"},{"v":205000,"d":"$205,000"},{"v":216000,"d":"$216,000"},{"v":237000,"d":"$237,000"},{"v":158400,"d":"$158,400"},{"v":109400,"d":"$109,400"},{"v":125400,"d":"$125,400"}]
["Matur CDs:",null,{"v":100000,"d":"100,000 "},{"v":100000,"d":"100,000 "},{"v":110000,"d":"110,000 "},{"v":100000,"d":"100,000 "},{"v":100000,"d":"100,000 "},{"v":120000,"d":"120,000 "},{"$nulls":3},"   Target cell"]
["Interest:",null,{"v":1000,"d":"1,000 "},{"v":1000,"d":"1,000 "},{"v":1400,"d":"1,400 "},{"v":1000,"d":"1,000 "},{"v":1000,"d":"1,000 "},{"v":2300,"d":"2,300 "}]
["1-mo CDs:",{"v":100000,"d":"100,000 "},{"v":100000,"d":"100,000 "},{"v":100000,"d":"100,000 "},{"v":100000,"d":"100,000 "},{"v":100000,"d":"100,000 "},{"v":100000,"d":"100,000 "},{"$nulls":4},"   Changing cells"]
["3-mo CDs:",{"v":10000,"d":"10,000 "},null,null,{"v":10000,"d":"10,000 "}]
["6-mo CDs:",{"v":10000,"d":"10,000 "},{"$nulls":9},"   Constraints"]
["Cash Uses:",{"v":75000,"d":"75,000 "},{"v":-10000,"d":"(10,000)"},{"v":-20000,"d":"(20,000)"},{"v":80000,"d":"80,000 "},{"v":50000,"d":"50,000 "},{"v":-15000,"d":"(15,000)"},{"v":60000,"d":"60,000 "}]
["End Cash:",{"v":205000,"d":"$205,000"},{"v":216000,"d":"$216,000"},{"v":237000,"d":"$237,000"},{"v":158400,"d":"$158,400"},{"v":109400,"d":"$109,400"},{"v":125400,"d":"$125,400"},{"v":187700,"d":"$187,700"}]
[]
[null,-290000]
[]
["If you're a financial officer or a manager, one of your tasks is to manage cash and short-term investments in a"]
["way that maximizes interest income, while keeping funds available to meet expenditures.  You must trade off"]
["the higher interest rates available from longer-term investments against the flexibility provided by keeping funds"]
["in short-term investments."]
[]
["This model calculates ending cash based on initial cash (from the previous month), inflows from maturing"]
["certificates of deposit (CDs), outflows for new CDs, and cash needed for company operations for each month."]
[]
["You have a total of nine decisions to make:  the amounts to invest in one-month CDs in months 1 through 6;"]
["the amounts to invest in three-month CDs in months 1 and 4; and the amount to invest in six-month CDs in"]
["month 1."]
[]
["Problem Specifications"]
[]
["Target cell",null,"H8",null,"Goal is to maximize interest earned."]
[]
["Changing cells",null,"B14:G14",null,"Dollars invested in each type of CD."]
[null,null,"B15, E15, B16"]
[]
["Constraints",null,"B14:G14>=0",null,"Investment in each type of CD must be greater than"]
[null,null,"B15:B16>=0",null,"or equal to 0."]
[null,null,"E15>=0"]
[]
[null,null,"B18:H18>=100000",null,"Ending cash must be greater than or equal to"]
[{"$nulls":4},"$100,000."]
[]
["The optimal solution determined by Solver earns a total interest income of $16,531 by investing as much as"]
["possible in six-month and three-month CDs, and then turns to one-month CDs.  This solution satisfies all of the"]
["constraints."]
[]
["Suppose, however, that you want to guarantee that you have enough cash in month 5 for an equipment"]
["payment.  Add a constraint that the average maturity of the investments held in month 1 should not be more"]
["than four months."]
[]
["The formula in cell B20 computes a total of the amounts invested in month 1 (B14, B15, and B16), weighted"]
["by the maturities (1, 3, and 6 months), and then it subtracts from this amount the total investment, weighted by"]
["4.  If this quantity is zero or less, the average maturity will not exceed four months.  To add this constraint,"]
["restore the original values and then click Solver on the Tools menu.  Click Add.  Type b20 in the Cell "]
["Reference box, type 0 in the Constraint box, and then click OK.  To solve the problem, click Solve."]
[]
["To satisfy the four-month maturity constraint, Solver shifts funds from six-month CDs to three-month CDs.  The"]
["shifted funds now mature in month 4 and, according to the present plan, are reinvested in new three-month "]
["CDs.  If you need the funds, however, you can keep the cash instead of reinvesting.  The $56,896 turning"]
["over in month 4 is more than sufficient for the equipment payment in month 5.  You've traded about $460 in"]
["interest income to gain this flexibility."]

{"sheet":"Portfolio of Securities","index":5,"n_rows":76,"n_cols":11,"rows_kept":76,"flags":["formulas_cached_only"]}
["Example 5:  Efficient stock portfolio."]
["Find the weightings of stocks in an efficient portfolio that maximizes the portfolio rate of"]
["return for a given level of risk.  This worksheet uses the Sharpe single-index model; you"]
["can also use the Markowitz method if you have covariance terms available."]
[]
["Risk-free rate",null,{"v":0.06,"d":"6.0%"},null,"Market variance",null,{"v":0.03,"d":"3.0%"}]
["Market rate",null,{"v":0.15,"d":"15.0%"},null,"Maximum weight",null,{"v":1,"d":"100.0%"}]
[]
[null,"Beta","ResVar",null,"Weight","*Beta","*Var.",null,"Color Coding"]
["Stock A",{"v":0.8,"d":"0.80"},0.04,null,{"v":0.2,"d":"20.0%"},{"v":0.16000000000000003,"d":"0.160"},{"v":0.0016000000000000003,"d":"0.002"}]
["Stock B",{"v":1,"d":"1.00"},{"v":0.2,"d":"0.20"},null,{"v":0.2,"d":"20.0%"},{"v":0.2,"d":"0.200"},{"v":0.008000000000000002,"d":"0.008"},{"$nulls":3},"   Target cell"]
["Stock C",{"v":1.8,"d":"1.80"},0.12,null,{"v":0.2,"d":"20.0%"},{"v":0.36000000000000004,"d":"0.360"},{"v":0.0048000000000000004,"d":"0.005"}]
["Stock D",{"v":2.2,"d":"2.20"},{"v":0.4,"d":"0.40"},null,{"v":0.2,"d":"20.0%"},{"v":0.44000000000000006,"d":"0.440"},{"v":0.016000000000000004,"d":"0.016"},{"$nulls":3},"   Changing cells"]
["T-bills",{"v":0,"d":"0.00"},{"v":0,"d":"0.00"},null,{"v":0.2,"d":"20.0%"},{"v":0,"d":"0.000"},{"v":0,"d":"0.000"}]
[{"$nulls":10},"   Constraints"]
["Total",{"$nulls":3},{"v":1,"d":"100.0%"},{"v":1.1600000000000001,"d":"1.160"},{"v":0.030400000000000007,"d":"0.030"}]
[{"$nulls":4},"Return",null,"Variance"]
[null,null,"Portfolio Totals:  ",null,{"v":0.1644,"d":"16.4%"},null,{"v":0.07076800000000001,"d":"7.1%"}]
[]
["Maximize Return: A21:A29",null,null,"Minimize Risk: D21:D29"]
[0.1644,null,null,0.07076800000000001]
[5,null,null,5]
[true,null,null,true]
[true,null,null,true]
[true,null,null,true]
[true,null,null,true]
[true,null,null,true]
[true,null,null,true]
[true,null,null,true]
[]
["One of the basic principles of investment management is diversification.  By holding a portfolio of several"]
["stocks, for example, you can earn a rate of return that represents the average of the returns from the"]
["individual stocks, while reducing your risk that any one stock will perform poorly."]
[]
["Using this model, you can use Solver to find the allocation of funds to stocks that minimizes the portfolio"]
["risk for a given rate of return, or that maximizes the rate of return for a given level of risk."]
[]
["This worksheet contains figures for beta (market-related risk) and residual variance for four stocks.  In"]
["addition, your portfolio includes investments in Treasury bills (T-bills), assumed to have a risk-free rate of"]
["return and a variance of zero.  Initially equal amounts (20 percent of the portfolio) are invested in each"]
["security."]
[]
["Use Solver to try different allocations of funds to stocks and T-bills to either maximize the portfolio rate of"]
["return for a specified level of risk or minimize the risk for a given rate of return.  With the initial allocation"]
["of 20 percent across the board, the portfolio return is 16.4 percent and the variance is 7.1 percent."]
[]
["Problem Specifications"]
[]
["Target cell",null,null,"E18",null,"Goal is to maximize portfolio return."]
[]
["Changing cells",null,null,"E10:E14",null,"Weight of each stock."]
[]
["Constraints",null,null,"E10:E14>=0",null,"Weights must be greater than or equal to 0."]
[]
[{"$nulls":3},"E16=1",null,"Weights must equal 1."]
[]
[{"$nulls":3},"G18<=0.071",null,"Variance must be less than or equal to 0.071."]
[]
["Beta for each stock",null,null,"B10:B13"]
[]
["Variance for each stock",null,null,"C10:C13"]
[]
["Cells D21:D29 contain the problem specifications to minimize risk for a required rate of return of 16.4"]
["percent.  To load these problem specifications into Solver, click Solver on the Tools menu, click "]
["Options, click Load Model, select cells D21:D29 on the worksheet, and then click OK until the"]
["Solver Parameters dialog box is displayed.  Click Solve.  As you can see, Solver finds portfolio"]
["allocations in both cases that surpass the rule of 20 percent across the board."]
[]
["You can earn a higher rate of return (17.1 percent) for the same risk, or you can reduce your risk without"]
["giving up any return.  These two allocations both represent efficient portfolios."]
[]
["Cells A21:A29 contain the original problem model.  To reload this problem, click Solver on the Tools "]
["menu, click Options, click Load Model, select cells A21:A29 on the 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."]

{"sheet":"Engineering Design","index":6,"n_rows":44,"n_cols":12,"rows_kept":44,"flags":["formulas_cached_only"]}
["Example 6:  Value of a resistor in an electrical circuit."]
["Find the value of a resistor in an electrical circuit that will dissipate the charge to 1"]
["percent of its original value within one-twentieth of a second after the switch is closed."]
[]
[null,"Switch->"]
[{"$nulls":5},"q0 =",9,"volts",null,"Color Coding"]
[{"$nulls":5},"q[t] =",0.09,"volts"]
[{"$nulls":5},"t =",0.05,"seconds",{"$nulls":3},"   Target cell"]
["Battery","Capacitor (C)",null,"Inductor (L)",null,"L =",8,"henrys"]
[{"$nulls":5},"C =",0.0001,"farads",{"$nulls":3},"   Changing cells"]
[]
[null,null,"Resistor",null,null,"R =",300,"ohms",{"$nulls":3},"   Constraints"]
[null,null,"  (R)"]
[]
[null,"1/(L*C)",null,1250,null,"q[t] =",0.2538891840303239]
[null,"(R/(2*L))^2",null,351.5625]
[null,"SQRT(B15-B16)",null,29.973947020704497]
[null,"COS(T*B17)",null,0.07203652709511484]
[null,"-R*T/(2*L)",null,-0.9375]
[null,"Q0*EXP(B19)",null,3.524450640091191]
[]
["This model depicts an electrical circuit containing a battery, switch, capacitor, resistor, and "]
["inductor.  With the switch in the left position, the battery charges the capacitor.  When the switch"]
["is thrown to the right, the capacitor discharges through the inductor and the resistor, both of"]
["which dissipate electrical energy."]
[]
["Using Kirchhoff's second law, you can formulate and solve a differential equation to determine"]
["how the charge on the capacitor varies over time.  The formula relates the charge q[t] at time t"]
["to the inductance L, resistance R, and capacitance C of the circuit elements."]
[]
["Use Solver to pick an appropriate value for the resistor R (given values for the inductor L and"]
["the capacitor C) that will dissipate the charge to one percent of its initial value within "]
["one-twentieth of a second after the time the switch is thrown."]
[]
["Problem Specifications"]
[]
["Target cell",null,"G15",null,"Goal is to set to value of 0.09."]
[]
["Changing cell",null,"G12",null,"Resistor."]
[]
["Constraints",null,"D15:D20",null,"Algebraic solution to Kirchhoff's law."]
[]
["This problem and solution are appropriate for a narrow range of values; the function represented"]
["by the charge on the capacitor over time is actually a damped sine wave."]