Grade = 100 Question 1 2 3 4 Total Points Available 30 20 40 10 100 Points Lost 0 0 0 0 0 Question1 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 X1 Sofa Grader X2 Table X3 Chair X2 Table X3 Chair Grader Part d Part e Constraint Value Amount Used Grader Grader Part f Part g Part h Slack or Type Slack or Surplus Inequality Surplus Grader 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 g h I f 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 Slack or Surplus Grader Part g Part h Type Slack or Inequality Surplus 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.0200000004 250 0 0.04 0.0400000005 1.0000E+030 3000 0 0.06 0.01 1.0000E+030 2000 0 0.07 1.0000E+030 0.01 Part a b c d Grader Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 4,250 0 0 4250 1.0000E+030 5,000 0 5000 4250 2750 4,750 0 250 4500 1.0000E+030 250 0 250 1416.6666667 250 3,000 0 250 2750 1.0000E+030 2,000 0 250 1750 1.0000E+030 2,000 0 2000 2750 1750 10,000 0 10000 1.0000E+030 4250 Part e Grader 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 Group Graded Assignment 4 Instructions Complete the Assignment, name it as GA or GAG GroupXX_Assign4.xlsx (where XX is your Group Number), and upload to the instructor through GeorgiaVIEW, using the link named \"Group Graded Assignment 4.\" 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. You are encouraged to use Excel's Help feature found on the right most, top menu option. 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: 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. (6 points) Be sure to choose \"assume Linear Model\" and \"Assume Non-negative\" from Solver's Options dialogue box. a. b. c. d. e. 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. 3 Points: In cells B9:D9, enter the profit coefficients for the objective function. 6 Points: In cells B12:D15, enter the constraint coefficients for each of the four constraints. 2 Points: In cells E12:E15, enter the appropriate constraint values. 4 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. 1 f. g. h. 4 Points: In cells G12:G15, write the appropriate formulas to determine the amount of slack or surplus associated with each resource or limitation. 2 Points: In cells H12:H15, write the type of inequality associated with each constraint using either \"<=,\" \">=,\" or \"=.\" 2 Points: In cells I12:I15, write the appropriate word (either \"Slack\" or \"Surplus\") to indicate the nature of the leftover resources or limitations. QUESTION 2: 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. 3 Points: In cells J5:L5 input the values of the decision variables that result from the new model solution. b. 3 Points: In cells J6:K6 input the lower and upper range of sofa profit for which the optimal solution would remain the same. c. 2 Points: 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. 2 Points: 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. 2 Points: In cell J13, input the value that indicates the amount of the wood resource remaining. f. 2 Points: In cell K13, input either the word \"slack\" or \"surplus\"\" to describe the unused amount of wood. g. 2 Points: In cell J14, input the value indicating the maximum amount the company would be willing to pay for an additional yard of upholstery. h. 2 Points: In cell J15, input the value representing how much more profit could be obtained by obtaining 10 additional hours of labor. i. 2 Points: In cell J165, input the value representing how much profit would be lost if warehouse capacity dropped by 500 pieces of furniture. QUESTION 3: 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 2. 3. 4. 5. At least 50% ($5,000) must be in mutual funds and bonds combined. No more than 20% ($2,000) can be in bonds alone. At least 2.5% ($250) must be invested in each alternative. 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) Be sure to choose \"assume Linear Model\" and \"Assume Non-negative\" from Solver's Options dialogue box. a. b. c. d. e. f. g. h. 2 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. 2 Points: In cells B9:E9, enter the investment return coefficients for the objective function. Make sure the coefficients are input as proportions, not percents. 8 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. 8 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. 4 Points: In cells I12:I19, write the type of inequality associated with each constraint using either \"<=,\" \">=,\" or \"=.\" 4 Points: In cells J12:J18, write the appropriate word (either \"Slack\" or \"Surplus\") to indicate the nature of the leftover resources or limitations. Question 4: 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. e. 2 Points: The requirement that \"at least $250 be allocated to each investment\" serves as a set of constraints. In cell J21, enter either the word(s) \"Slack,\