Speadsheet Modeling & Decision Analysis 5e: Chapter 3 Solutions

Chapter 3 Modeling & Solving LP Problems In A Spreadsheet 1. In general, it does not matter what is placed in a variable (changing) cell. Ultimately, Solver will determine the optimal values for these cells. If the model builder places formulas in changing cells, Solver will replace the formulas with numeric constants representing the optimal values of the decision variables. An exception to this general principle is found in Chapter 8 where, when solving nonlinear programming problems, the values placed in the changing cells represent the initial starting point for the optimizer. 2. Communication - once the user understands the first formula in a range of copied cells, he or she should understand all the formulas in the range. …show more content…

X1= 1000, X2= 500, X3= 80, X4= 200, Maximum profit = $2,913.2 15. a. N1 = number of Newspaper ads to run at $1,000 each N2 = number of Newspaper ads to run at $900 each N3 = number of Newspaper ads to run at $800 each T1 = number of Television ads to run at $12,000 each T2 = number of Television ads to run at $10,000 each T3 = number of Television ads to run at $8,000 each MAX 900 N1 + 700 N2 + 400 N3 + 10,000 T1 + 7,500 T2 + 5,000 T3 ST 1,000 N1 + 900 N2 + 800 N3 + 12,000 T1 + 10,000 T2 + 8,000 T3 ( 145,000 Ni ( 10 Ti ( 5 Ti, Ni > 0 b. See file: Prb3_12.xls c. N1 = 10, N2 = 10, N3 = 0, T1 = 5 , T2 = 5 , T3 = 2 New Customers = 113,500 d. N3 is used before N2. 16. a. Xij = Square feet of space rented in month i (i=1, 2, 3, 4, 5) through month j (j=i, i+1, …, 5) MIN 55X11 + 95X12 + 130X13 + 155X14 + 185X15 + 55X22 + 95X23 + 130X24 + 155X25 + 55X33 + 95X34 + 130X35 + 55X44 + 95X45 + 55X55 ST X11 + X12 + X13 + X14 + X15 > 20000 X12 + X13 + X14 + X15 + X22 + X23 + X24 + X25> 30000 X13 + X14 + X15 + X23 + X24 + X25 + X33 + X34 + X35 > 40000 X14 + X15 + X24 + X25 + X34 + X35 + X44 + X45 > 35000 X15 + X25 + X35 + X45 + X55 > 50000 Xij > 0 b. See file: Prb3_16.xls c. X15 = 20000, X25 = 10000, X33 = 5000, X35 = 5000, X55 = 15000 Total leasing cost = $7 million d.