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.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. 1 Point: In cell B3, use the Excels built-in function named SUMPRODUCT to write a formula for the objective function. Be sure to reference the appropriate cells.b. 1 Point: In cells B9:D9, enter the profit coefficients for the objective function.c. 4 Points: In cells B12:D15, enter the constraint coefficients for each of the four constraints.d. 1 Point: In cells E12:E15, enter the appropriate constraint values.e. 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.f. 3 Points: In cells G12:G15, write the appropriate formulas to determine the amount of slack or surplus associated with each resource or limitation.g. 1 Point: In cells H12:H15, write the type of inequality associated with each constraint using either <=, >=, or =.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 doesnt 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)a. 4 Points: In cell B3, use the Excels built-in function named SUMPRODUCT to write a formula for the objective function. Be sure to reference the appropriate cells.b. 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.c. 4 Points: In cells B12:E19, enter the constraint coefficients for each of the eight constraints.d. 4 Points: In cells F12:F19, enter the appropriate constraint values.e. 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.f. 4 Points: In cells H12:H19, write the appropriate formulas to determine the amount of slack or surplus associated with each resource or limitation.g. 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 citys 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.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.M5 Group Assignment InstructionsComplete 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.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. 1 Point: In cell B3, use the Excels built-in function named SUMPRODUCT to write a formula for the objective function. Be sure to reference the appropriate cells.b. 1 Point: In cells B9:D9, enter the profit coefficients for the objective function.c. 4 Points: In cells B12:D15, enter the constraint coefficients for each of the four constraints.d. 1 Point: In cells E12:E15, enter the appropriate constraint values.e. 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.f. 3 Points: In cells G12:G15, write the appropriate formulas to determine the amount of slack or surplus associated with each resource or limitation.g. 1 Point: In cells H12:H15, write the type of inequality associated with each constraint using either <=, >=, or =.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 doesnt 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)a. 4 Points: In cell B3, use the Excels built-in function named SUMPRODUCT to write a formula for the objective function. Be sure to reference the appropriate cells.b. 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.c. 4 Points: In cells B12:E19, enter the constraint coefficients for each of the eight constraints.d. 4 Points: In cells F12:F19, enter the appropriate constraint values.e. 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.f. 4 Points: In cells H12:H19, write the appropriate formulas to determine the amount of slack or surplus associated with each resource or limitation.g. 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 citys 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.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.