1 Part a Max Profit Grader X1 Sofa X2 Table X3 Chair Grader Dec Vars Part b Profit Coefficients Part c Constraint Coefficients Wood Upholstery Labor Warehouse Capacity Solver Solution X1 Sofa X2 Table X3 Chair X1 Sofa Part d Constraint X2 Table X3 Chair Value Grader Grader Grader Part e Amount Used Grader Part f Part g Slack or Type Surplus Inequality Grader Grader Question 2 Adjustable Cells Cell Name $B$6 Dec Vars X1 Sofa $C$6 Dec Vars X2 Table $D$6 Dec Vars X2 Chair Grader Final Value 11 0 67 Reduced Cost Objective Coefficient 0 300 -25 275 0 225 Final Value 345 601 401 78 Shadow Price Allowable Allowable Increase Decrease 7.14 30 25 1.0000E+030 25 4.17 Part a b c d Constraints Cell $F$12 $F$13 $F$14 $F$15 Name Wood Amount Used Upholstery Amount Used Labor Amount Used Warehouse Capacity Amount Used 0 8 33 0 Constraint Allowable R.H. Side Increase 2250 1.0000E+030 601 201 401 28.29 650 1.0000E+030 Allowable Decrease 1905 39.6 100.5 572 Part e f g h Grader Question 3 Part a Max Return Grader X1 RE X2 CD X3 MF X4 Bonds Grader X1 RE X2 CD X3 MF X4 Bonds Grader Dec Vars Part b Return Coefficients Part c Constraint Coefficients RE to CD Ratio MF + Bonds Combined Bonds <=$2000 RE Alone CD Alone MF Alone B Alone All Funds Invested Part d X1 RE Grader X2 CD X3 MF X4 Bonds Part e Constraint Amount Value Used Grader Grader Part f Part g Slack or Surplus Type Inequality Grader Grader Question 4 Adjustable Cells Cell $B$6 $C$6 $D$6 $E$6 Name Dec Vars X1 RE Dec Vars X2 CD Dec Vars X3 MF Dec Vars X4 Bonds Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 4750 0 0.08 1.0000E+030 0.02 250 0 0.04 0.0400000005 1.000E+030 3000 0 0.06 0.01 1.000E+030 2000 0 0.07 1.0000E+030 0.01 Constraints Cell $G$12 $G$13 $G$15 $G$16 $G$17 $G$18 $G$14 $G$19 Name RE to CD Ratio Amount Used MF + Bonds Combined Amount Used RE Alone Amount Used CD Alone Amount Used MF Alone Amount Used B Alone Amount Used Bonds <=$2000 Amount Used All Funds Invested Amount Used Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 4,250 0 0 4250 1.000E+030 5,000 0 5000 4250 2750 4,750 0 250 4500 1.000E+030 250 0 250 1416.6666667 250 3,000 0 250 2750 1.000E+030 2,000 0 250 1750 1.000E+030 2,000 0 2000 2750 1750 10,000 0 10000 1.0000E+030 4250 Part a b c d Grader Response times (minutes) for a fire in tract 1 2 3 4 5 6 7 8 Cost of station ($thousands) Part a Grader 1 2 9 17 10 21 25 14 30 350 2 8 3 8 13 12 15 22 24 250 3 18 10 4 19 16 7 18 15 450 Fire Station in Tract 4 5 9 23 12 16 20 21 2 18 13 5 21 15 7 13 14 17 300 50 Part b 6 22 14 8 21 11 3 15 9 400 7 16 21 22 6 9 14 2 8 300 8 28 25 17 12 12 8 9 3 200 Grader Response times (minutes) for a fire in tract 1 2 3 4 5 6 7 8 Cost of station ($thousands) Part c Grader 1 2 9 17 10 21 25 14 30 350 2 8 3 8 13 12 15 22 24 250 3 18 10 4 19 16 7 18 15 450 Fire Station in Tract 4 5 9 23 12 16 20 21 2 18 13 5 21 15 7 13 14 17 300 50 6 22 14 8 21 11 3 15 9 400 7 16 21 22 6 9 14 2 8 300 8 28 25 17 12 12 8 9 3 200 M5 Group Assignment Instructions Complete the Assignment, name it as GroupXX_Assign5.xls (where XX is your Group Name), and upload and submit to the instructor through Dropbox. Do not enter anything in the spreadsheet cells that are black, labeled \"Grader\". You must complete this assignment without the assistance of persons other than the members of your Group. You may use any other resources you deem necessary. Answer the questions below by placing the appropriate graph and/or answers in the designated cells of the spreadsheet. DO NOT CHANGE THE APPEARANCE OR FUNCTIONALITY OF THE SPREADSHEET UNLESS INSTRUCTED TO DO SO. QUESTION 1 (18 points): The Hickory Cabinet and Furniture Company produces sofas, tables, and chairs. The plant uses three main resources to make furniture - wood, upholstery, and labor. The resource requirements for each piece of furniture and the total resources available weekly are as follows. Sofa Table Chair Total Available Resources Resource Requirements Wood (lb) Upholstery (yd) Labor (hr) 7 12 6 5 NA 9 4 7 5 2250 1000 240 The furniture is produced on a weekly basis and stored in the warehouse until the end of the week, when it is shipped out. The warehouse has a total capacity of 650 pieces of furniture. Each sofa earns $300 in profit, each table $200, and each chair $150. The company wants to know how many pieces of each type of furniture to make per week to maximize profit. The worksheet named \"Hickory 1\" contains a template for solving this LP problem. Answer the parts below, and then use the LP template to solve for the maximum profit. (4 points) a. b. c. d. e. f. g. 1 Point: In cell B3, use the Excel's built-in function named \"SUMPRODUCT\" to write a formula for the objective function. Be sure to reference the appropriate cells. 1 Point: In cells B9:D9, enter the profit coefficients for the objective function. 4 Points: In cells B12:D15, enter the constraint coefficients for each of the four constraints. 1 Point: In cells E12:E15, enter the appropriate constraint values. 3 Points: In cells F12:F15, use the \"SUMPRODUCT\" function to write a formula for the amount of each resource used or limitation imposed. Be sure to reference the appropriate cells. 3 Points: In cells G12:G15, write the appropriate formulas to determine the amount of slack or surplus associated with each resource or limitation. 1 Point: In cells H12:H15, write the type of inequality associated with each constraint using either \"<=,\" \">=,\" or \"=.\" 1 QUESTION 2 (8 points): The Hickory Cabinet and Furniture Company had some changes in supplier and buyer contracts, as well as labor opportunities. The sofa retailer left the profit at $300 per sofa, while a contract with another retailer increased the profit on chairs to $225 and on tables to $275. Additionally, the upholstery vendor decided to sell in lots of size 601, meaning the available upholstery is now only 601 yards since the company doesn't want to buy 1202 yards at a time (current is 1000 yds available at a time). As a result of another plant going out of business, the labor supply increased to 401 hours. Based on changes in the profit amounts and available resources, the sensitivity output for this formulation is provided in the worksheet named \"Hickory 2.\" Use the output the answer the following questions. a. 1 Point: In cells J5:L5 input the values of the decision variables that result from the new model solution. b. 1 Point: In cells J6:K6 input the lower and upper range of sofa profit for which the optimal decision variables would remain the same. c. 1 Point: In J7 input the value that represents how much table profit would have to increase by to have a solution that includes making tables. d. 1 Point: In cell J8, input either the word(s) \"change\" or \"no change\" to indicate what would happen to the optimal solution if chair profit could be increased by $30. e. 1 Point: In cell J13, input the value that indicates the amount of the wood resource remaining. f. 1 Point: In cell J14, input the value indicating the maximum amount the company would be willing to pay for an additional yard of upholstery. g. 1 Point: In cell J15, input the value representing how much more profit could be obtained by obtaining 10 additional hours of labor. h. 1 Point: In cell J165, input the value representing how much profit would be lost if warehouse capacity dropped by 500 pieces of furniture. QUESTION 3 (30 points): As a 401K retirement plan participant you have to make personal decisions regarding your own investment objectives. Suppose you have $10,000 to divide among four investment alternatives in your 401K retirement plan. The investment alternatives and historical returns are as follows; Real Estate (RE) = 8% CD = 4% Mutual Fund (MF) = 6% Bonds = 7% You will follow the investment allocation guidelines below. 1. More money should be in real estate than in CDs, at a rate of at least 2 to 1. [Hint: That means that RE > 2*CD] 2. At least 50% ($5,000) must be in mutual funds and bonds combined. 3. No more than 20% ($2,000) can be in bonds alone. 4. At least 2.5% ($250) must be invested in each alternative. 5. All of the $10,000 must be invested. Use the spreadsheet template in the worksheet named \"Investment 1\" to formulate and solve the LP problem for the maximum return. Note that the percentages must be expressed as proportions in the solution. Answer the parts below, and then use the LP template to solve for the maximum profit. (4 points) 2 a. b. c. d. e. f. g. 4 Points: In cell B3, use the Excel's built-in function named \"SUMPRODUCT\" to write a formula for the objective function. Be sure to reference the appropriate cells. 4 Points: In cells B9:E9, enter the investment return coefficients for the objective function. Make sure the coefficients are input as proportions, not percent. 4 Points: In cells B12:E19, enter the constraint coefficients for each of the eight constraints. 4 Points: In cells F12:F19, enter the appropriate constraint values. 4 Points: In cells G12:G19, use the \"SUMPRODUCT\" function to write a formula for the amount of each resource used or limitation imposed. Be sure to reference the appropriate cells. 4 Points: In cells H12:H19, write the appropriate formulas to determine the amount of slack or surplus associated with each resource or limitation. 2 Points: In cells I12:I19, write the type of inequality associated with each constraint using either \"<=,\" \">=,\" or \"=.\" Question 4 (8 points): The sensitivity report for the investment problem in question 3 is in the worksheet named \"Investment 2.\" Use the report to answer the following questions. a. 2 Points: In cell J6, input the word \"yes\" or \"no\" to indicate if a greater return on RE could change the optimal solution. b. 2 Points: In cell J7, input the word \"yes\" or \"no\" to indicate if a return on CD of 7% would change the optimal solution. c. 2 Points: In cell J8, input the value that represents the maximum amount the return on MF (in percent terms) could increase without changing the optimal solution. d. 2 Points: In cell J9, input the value that represents the maximum decrease in return on Bonds (in percent terms) that would result in the optimal solution remaining unchanged. Question 5 (36 points): Carlie City is located in a particular warm and dry part of the United States, so it is especially prone to the occurrence of fires. The city has become a popular place for senior to move to after retirement, so it has been growing rapidly and spreading well beyond its original borders. However, the city still has only one fire station, located in the congested center of the original town site. The result has been some long delays in fire trucks reaching fires in the outer parts of the city, causing much more damage than would have occurred with a prompt response. The city's residents are very unhappy about this, so the city council has directed the city manager to develop a plan for locating multiple fire stations throughout city (including perhaps moving the current fire station) that would greatly reduce the response time to any fire. In particular, the city council has adopted the policy about the maximum acceptable response time of 10 minutes for fire trucks to reach a fire anywhere in the city after being notified about the fire. The city manager contacted your team for analyzing the problem. To get started, she divides the city into eight tracts and then gathers data on the estimated response time for a fire in each tract from a potential fire station in each of the eight tracts. These data are shown in worksheet Fire. For example, if a decision were to be made to locate a fire station in tract 1 and if that fire station were to be used to respond to a fire in any of the tracts, the second column shows what the estimated response time would be. The bottom row shows what the cost would be of acquiring the land and constructing a fire station in any of the eight tracts. Note that the cost is far less for tract 5 because the current fire station already is there. 3 The objective is to determine which tract should receive a fire station to minimize the total cost of the stations while ensuring that each tract has at least one station close enough to respond to a fire in no more than 10 minutes. a. 20 Points: Set up your model in worksheet Fire including decision variables, objective function, and constraints. Solve the model to optimality. [Hint: Construct a 0/1 table for the response time table (1 indicates that response time is below 10 minutes and 0 otherwise)]. b. 4 Points: Under the optimal location plan reported in part a, how many tracts will have more than one fire station that is within 10 minute response time? c. 12 Points: What would be the increase in the total cost if the maximum acceptable response time is now 8 minutes? Conduct your analysis in worksheet Fire8Min. 4