Problem 1. Make sure that formulas, Solver parameters, Sensitivity Reports, and Solver Table Results are saved in this worksheet. Refer to the Facility Location Problem in California Manufacturing Co. case from Section 7.1 where management is interested in determining the most profitable combination of investments using an available budget of $10 million. NPV (millions $) LA SF 4 Warehouse Factory 000 5 Decision Number Yes-or-No Question Decision Variable Net Present Value (Millions) Capital Required (millions $) 1 2 3 X1 Capital Required (Millions) $6 3 5 2 Build a factory in Los Angeles? Build a factory in San Francisco? Build a warehouse in Los Angeles? Build a warehouse in San Francisco? $8 5 6 LA SF X2 X3 X4 5 2 Capital spent 3 #VALUE! Warehouse Factory Capital available 10 4 4 6 = 4 Larry would like to determine how many full-time and part-time consultants should work each shift to meet the above requirements at the minimum possible cost. 8>= 0 8-12 12-4 4-8 8-12 8>= 8 (a) Which category of linear programming problem does this problem fit? Why? 4>= 4 (b) Formulate and solve a linear programming model for this problem on a spreadsheet. (c) Summarize the model in algebraic form. Problem 3. Make sure that formulas, Solver parameters, Sensitivity Reports, and Solver Table Results are saved in this worksheet. The Fly-Right Airplane Company builds small jet airplanes to sell to corporations for use by their executives. To meet the needs of these executives, the company's customers sometimes order a custom design of the airplanes being purchased. When this occurs, a substantial start-up cost is incurred to initiate the production of these airplanes. Fly-Right has recently received purchase requests from three customers with short deadlines. However, because the company's production facilities already are almost completely tied up filling previous orders, it will not be able to accept all three orders. Therefore, a decision now needs to be made on the number of airplanes the company will agree to produce (if any) for each of the three customers. The relevant data are given in the table below. The first row gives the start-up cost required to initiate the production of the airplanes for each customer. Once production is under way, the marginal net revenue (which is the purchase price minus the marginal production cost) from each airplane produced is shown in the second row. The third row gives the percentage of the available production capacity that would be used for each airplane produced. The last row indicates the maximum number of airplanes requested by each customer (but less will be accepted). Customer 3 0 Startup Cost Marginal Net Revenue Capacity Used per Plane Maximum Order Customer 1 $3 million $2 million 20% 3 planes Customer 2 $2 million $3 million 40% 2 planes $0.8 million 20% 5 planes Fly-Right now wants to determine how many airplanes to produce for each customer (if any) to maximize the company's total profit (total net revenue minus start-up costs). Formulate and solve a spreadsheet model with both integer variables and binary variables for this problem. Problem Make sure that formulas, Solver parameters, Sensitivity Reports and Solver Table Results are saved in this worksheet. Fixed Cost Warehous arehouse Northwest Southwest Midlavet Southeast Northeast per week) e Capacity (books/we S, WA $2.40 $8.50 $4.80 $6.80 $5.75 $40,000 20,000 R. NV $3.25 $2.30 $3.40 $5.25 $6.00 $30,000 O, NE $4.05 $3.23 $2.85 $4.30 $4.75 $25,000 15,000 H, PA $5.25 S6.05 $4.30 $3.20 $2.75 $40,000 25,000 J. FL $6.95 $5.85 $4.30 $2.10 $3.50 $30,000 15,000 Demand (perw 8,000 12,000 9,000 14,000 17,000 20.000 Warehouse Site Spokane, WA Reno, NV Omaha, NE Harrisburg, PA Northwest $2.40 $ $3.25 $4.05 $5.25 Average Shipping Cost ($/book) Southwest Midwest Southeast $3.50 $4.00 $6.80 $2.30 $3.40 $5.25 $3.25 $2.85 $4.30 S6.05 $4.30 $3.20 Fixed Cost Northeast (per week] $5.75 $40,000 $5.00 $30,000 $4.75 $25,000 $2.75 $40.000 Warehouse Capacity (books/week) 20,000 23.000 15,000 25.000 Lacksonville FL $6.95 $5.85 54.80 $2.10 $ $3.50 $30,000 15.000 arehouse S Northwest southwest Michwest Southeast Northeast local shipped Warehous e Capacity books/ cki Weckdy Demand 8,000 12,000 9,00 14,000 17,000 o 0 0 0 12000 D 0 8000 0 IS, WA R NV O, NE HPA 1, FL 0 0 1 0 20000 0 2500cl 15000 15,000 0 15.000 0 0 ol 8000 1 a a 1000 S000 0 14000 14000 17000 a 17000) 8000 12000 Demand (perw 0 0 Z 205,224,300.00 So at the end, we can clearly see that opening a warehouse in the followire places is the optimal decision for the company R-NV HPA and J FL with minimur cost of $ $268,950 00 Problem 1. Make sure that formulas, Solver parameters, Sensitivity Reports, and Solver Table Results are saved in this worksheet. Refer to the Facility Location Problem in California Manufacturing Co. case from Section 7.1 where management is interested in determining the most profitable combination of investments using an available budget of $10 million. NPV (millions $) LA SF 4 Warehouse Factory 000 5 Decision Number Yes-or-No Question Decision Variable Net Present Value (Millions) Capital Required (millions $) 1 2 3 X1 Capital Required (Millions) $6 3 5 2 Build a factory in Los Angeles? Build a factory in San Francisco? Build a warehouse in Los Angeles? Build a warehouse in San Francisco? $8 5 6 LA SF X2 X3 X4 5 2 Capital spent 3 #VALUE! Warehouse Factory Capital available 10 4 4 6 = 4 Larry would like to determine how many full-time and part-time consultants should work each shift to meet the above requirements at the minimum possible cost. 8>= 0 8-12 12-4 4-8 8-12 8>= 8 (a) Which category of linear programming problem does this problem fit? Why? 4>= 4 (b) Formulate and solve a linear programming model for this problem on a spreadsheet. (c) Summarize the model in algebraic form. Problem 3. Make sure that formulas, Solver parameters, Sensitivity Reports, and Solver Table Results are saved in this worksheet. The Fly-Right Airplane Company builds small jet airplanes to sell to corporations for use by their executives. To meet the needs of these executives, the company's customers sometimes order a custom design of the airplanes being purchased. When this occurs, a substantial start-up cost is incurred to initiate the production of these airplanes. Fly-Right has recently received purchase requests from three customers with short deadlines. However, because the company's production facilities already are almost completely tied up filling previous orders, it will not be able to accept all three orders. Therefore, a decision now needs to be made on the number of airplanes the company will agree to produce (if any) for each of the three customers. The relevant data are given in the table below. The first row gives the start-up cost required to initiate the production of the airplanes for each customer. Once production is under way, the marginal net revenue (which is the purchase price minus the marginal production cost) from each airplane produced is shown in the second row. The third row gives the percentage of the available production capacity that would be used for each airplane produced. The last row indicates the maximum number of airplanes requested by each customer (but less will be accepted). Customer 3 0 Startup Cost Marginal Net Revenue Capacity Used per Plane Maximum Order Customer 1 $3 million $2 million 20% 3 planes Customer 2 $2 million $3 million 40% 2 planes $0.8 million 20% 5 planes Fly-Right now wants to determine how many airplanes to produce for each customer (if any) to maximize the company's total profit (total net revenue minus start-up costs). Formulate and solve a spreadsheet model with both integer variables and binary variables for this problem. Problem Make sure that formulas, Solver parameters, Sensitivity Reports and Solver Table Results are saved in this worksheet. Fixed Cost Warehous arehouse Northwest Southwest Midlavet Southeast Northeast per week) e Capacity (books/we S, WA $2.40 $8.50 $4.80 $6.80 $5.75 $40,000 20,000 R. NV $3.25 $2.30 $3.40 $5.25 $6.00 $30,000 O, NE $4.05 $3.23 $2.85 $4.30 $4.75 $25,000 15,000 H, PA $5.25 S6.05 $4.30 $3.20 $2.75 $40,000 25,000 J. FL $6.95 $5.85 $4.30 $2.10 $3.50 $30,000 15,000 Demand (perw 8,000 12,000 9,000 14,000 17,000 20.000 Warehouse Site Spokane, WA Reno, NV Omaha, NE Harrisburg, PA Northwest $2.40 $ $3.25 $4.05 $5.25 Average Shipping Cost ($/book) Southwest Midwest Southeast $3.50 $4.00 $6.80 $2.30 $3.40 $5.25 $3.25 $2.85 $4.30 S6.05 $4.30 $3.20 Fixed Cost Northeast (per week] $5.75 $40,000 $5.00 $30,000 $4.75 $25,000 $2.75 $40.000 Warehouse Capacity (books/week) 20,000 23.000 15,000 25.000 Lacksonville FL $6.95 $5.85 54.80 $2.10 $ $3.50 $30,000 15.000 arehouse S Northwest southwest Michwest Southeast Northeast local shipped Warehous e Capacity books/ cki Weckdy Demand 8,000 12,000 9,00 14,000 17,000 o 0 0 0 12000 D 0 8000 0 IS, WA R NV O, NE HPA 1, FL 0 0 1 0 20000 0 2500cl 15000 15,000 0 15.000 0 0 ol 8000 1 a a 1000 S000 0 14000 14000 17000 a 17000) 8000 12000 Demand (perw 0 0 Z 205,224,300.00 So at the end, we can clearly see that opening a warehouse in the followire places is the optimal decision for the company R-NV HPA and J FL with minimur cost of $ $268,950 00