PLEASE DO NOT OPEN TAB(s) ON YOUR BROWSER OTHE DO NOT SEND THE SOLUTION FILE TO YOUR EM DO NOT OPEN ANY COURSE MATERIAL SAVE YOUR FILE AS CIS623 FinalExam YourName Refresh your browser before submitting the solution file Comment Q1 Q2 Q3 Q4 Q5 Total 10 15 15 10 10 0 60 WSER OTHER THAN YOUR CANVAS MYGATE ACCOUNT O YOUR EMAIL CLOUD STORAGE CCOUNT or BINOM INV(n, p, RAND or BINOM INV(n, p, RAND()) Q1 10 Pts Monthly Sales between 2016 and 2018 Month 2016 2017 January 438 444 February 420 425 March 414 423 April 318 331 May 306 318 June 240 245 July 240 255 August 216 223 September 198 210 October 225 233 November 270 278 December 315 322 2018 450 438 434 338 331 254 264 231 224 243 289 335 For years The Glass Slipper restaurant has operated in a resort community n is busiest during the first 3 months of the year, when the ski slopes are crow shows a seasonal variability When James and Deena Weltee built The Glass Slipper, they had a vision of surrounding mountains was breathtaking, a high priority was placed on hav from anywhere inside the restaurant Special attention was also given to the truly magnificent experience for all who came to enjoy gourmet dining Sinc maintained a reputation as one of the must visit places in that region of N While James loves to ski and truly appreciates the mountains and all that th retiring to a tropical paradise and enjoying a more relaxed lifestyle on the be condition, they knew that retirement was many years away Nevertheless, t dream They decided to sell The Glass Slipper and open a bed and breakfast Mexico While this would mean that work was still in their future, they could blowing in the wind and the waves lapping at the shore They also knew tha Deena the time to begin a semi retirement in a corner of paradise To make this happen, James and Deena would have to sell The Glass Slipper based on the value of the property and equipment as well as projections of A forecast of sales for the next two year is needed to help in the determinati each of the past 3 years are provided in Table Anova Single Factor A Which forecasting model is the most appropriate for future sales Why SUMMARY Groups Month January February March April May June July August September October November December Count 3 3 3 3 3 3 3 3 3 3 3 3 3 Sum 6051 1332 1283 1271 987 955 739 759 670 632 701 837 972 Average 2017 444 427 6667 423 6667 329 318 3333 246 3333 253 223 3333 210 6667 233 6667 279 324 B Write the proposed model after the analysis and interpret the slope (2p Variance 1 the most appropriate forecasting model to these data to forecast s C Apply 36 86 33333 Please place the question number on your answers (i e , A, B, and C) 100 3333 103 156 3333 50 33333 147 56 33333 169 3333 81 33333 91 103 ANOVA Source of VariaSS df Between Group 8305335 Within Groups 2362 667 MS F P value F crit 12 692111 2 7616 348 6 812E 43 2 147926 26 90 87179 Total 38 8307697 d in a resort community near a popular ski area of New Mexico The restaurant en the ski slopes are crowded and tourists flock to the area Thus, the revenue pper, they had a vision of the ultimate dining experience As the view of priority was placed on having large windows and providing a spectacular view ntion was also given to the lighting, colors, and overall ambiance, resulting in a enjoy gourmet dining Since its opening, The Glass Slipper has developed and places in that region of New Mexico mountains and all that they have to offer, he also shares Deena's dream of relaxed lifestyle on the beach After some careful analysis of their financial ears away Nevertheless, they were hatching a plan to bring them closer to their open a bed and breakfast on a beautiful beach in l in their future, they could wake up in the morning to the sight of the palm trees shore They also knew that hiring the right manager would allow James and rner of paradise ve to sell The Glass Slipper for the right price The price of the business would be t as well as projections of future income to help in the determination of the value of the restaurant Monthly sales for ate for future sales Why (2pts) nd interpret the slope (2pts) to these data to forecast sales for each month of the 2019 and 2020 (6pts) ers (i e , A, B, and C) A Multiplicative decompositiong since there is a seasonal variation that grows proportionally with time Jan 16 Feb 16 Mar 16 Apr 16 May 16 Jun 16 Jul 16 Aug 16 Sep 16 Oct 16 Nov 16 Dec 16 Jan 17 Feb 17 Mar 17 Apr 17 May 17 Jun 17 Jul 17 Aug 17 Sep 17 Oct 17 Nov 17 Dec 17 Jan 18 Feb 18 Mar 18 Apr 18 May 18 Jun 18 Jul 18 Aug 18 Sep 18 Oct 18 Nov 18 Dec 18 438 420 414 318 306 240 240 216 198 225 270 315 444 425 423 331 318 245 255 223 210 233 278 322 450 438 434 338 331 254 264 231 224 243 289 335 Problem Title Forecasting 12 seasons Multiplicative decomposition Enter Enter the the actual actual values values in in the the cells cells shaded shaded YELLOW YELLOW Do Do not not change change the the time time period period numbers numbers Input Data Period Period 1 Period 2 Period 3 Seasonal Index Computation Actual value (Y) Seasonal Ratios Season 1 Average Time Centered period (X) average 1 2 3 Seasonal ratio Forecast Error analysis Unseasonal Seasonal Unseasonal ized Seasonalize index ized value Forecast d Forecast DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 Average Intercept DIV 0 Slope DIV 0 Season 2 Season 3 Season 4 Season 5 Season 6 Season 7 Season 8 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 Forecasts for future periods Unseasonal ized Seasonal Seasonalize Period forecast index d forecast 4 DIV 0 DIV 0 DIV 0 5 DIV 0 DIV 0 DIV 0 6 DIV 0 DIV 0 DIV 0 7 DIV 0 DIV 0 DIV 0 8 DIV 0 DIV 0 DIV 0 9 DIV 0 DIV 0 DIV 0 10 DIV 0 DIV 0 DIV 0 11 DIV 0 DIV 0 DIV 0 12 DIV 0 DIV 0 DIV 0 13 DIV 0 DIV 0 DIV 0 14 DIV 0 DIV 0 DIV 0 15 DIV 0 DIV 0 DIV 0 numbers numbers Error DIV 0 DIV 0 DIV 0 Absolute Squared Absolute error error error DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 DIV 0 MAD MSE MAPE Season 9 Season 10 Season 11 Season 12 DIV 0 DIV 0 DIV 0 DIV 0 Q2 15pts Table 1 Prob distribution of the weekly demand Demand 2 4 6 8 10 12 Probability 0 05 0 27 0 15 0 18 0 25 0 10 A factory produces office chairs According to the past data, the weekly demand has on Table 1 The selling price per chair is $120 In addition, the historical data showed manufacturing a chair showed a normal probability distribution with mean of $40 a A Simulate this production of chairs for a year (52 weeks 1 replication) to calculate Hint Net profit Revenu Cost where Revenue Demand Unit selling price Hint Demand and Cost are random variables based on the given probability distri B Calculate the average weekly profit and the percentage of generating $500 or mo (based on 1 replication) (2pts) C Usint Data Table, replicate the mean net profit and mean percentage of generati 200 times (2pts) D Determine the average weekly profit and the percentage of generating $500 or m replications (1pts) E Evaluate the average weekly profit and the percentage of generating $500 or mo chair is $100 and $110 (scenario manager) (3pts) F Determine range of true mean net profit based on 200 replication with 95 confi G Insert a histogram chart that shows the net profit based on 200 replications (1p Please place the question number on your answers (i e , A, B, , G) data, the weekly demand has the probability distribution given on, the historical data showed that the unit cost of tribution with mean of $40 and the standard deviation of $5 eks 1 replication) to calculate the weekly net profit (4pts) Demand Unit selling price n the given probability distributions age of generating $500 or more net profit based on 52 weeks mean percentage of generating $500 or more obtained in (B) ntage of generating $500 or more net profit based on 200 age of generating $500 or more net profit if the selling price of a 200 replication with 95 confidence level (2pts) based on 200 replications (1pt) e , A, B, , G) Demand Probability 2 0 05 4 0 27 6 0 15 8 0 18 10 0 25 12 0 10 Lower Limit Upper Limit 0 0 05 0 05 0 32 0 32 0 47 0 47 0 65 0 65 0 90 0 90 1 00 1 00 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 6 6 6 12 4 12 10 12 8 10 6 10 10 6 10 8 12 10 8 8 6 6 12 8 10 Net profit St dev 8 64 2 430363 Q3 15pts The decision variables of the given LP model determines how many necklaces (X1), bracelets(X2), rings (X3), and earrings objective function measures profit Constraint 1 measures display space in units, constraint 2 measures time to set up the constraint 4 are marketing restrictions Objectifive function MAX $100X1 $120X2 $150X3 $125X4 Constraints c1) X1 2X2 2X3 2X4 108 (Display space in units) c2) 3X1 5X2 X4 120 (Time to set up the display in minutes) c3) X1 X3 25 (Marketing restriction I) c4) X2 X3 X4 50 (Marketing restriction II) (Xi 0, consider them as real numbers, not as INTEGERs since the Sensitivity Analysis Report can not be generated) A) Generate the Sensitivity Analysis Report and the Answer Report(2pts) For the given questions below, use only the Sensitivity Analysis Answer Reports for your answers B) How many necklaces, bracelets, rings, and earrings should be stocked What is the total profit (2pt) C) How much space will be left unused How much time will be used By how much will the second marketing restrictio D) Find the total profit after increasing the unit profit of rings and earrings by $5 simultaneously (2pts) E) Find the new total profit after decreasing unit profit of necklace by $15 (1pt) F) Find the new total profit after increasing the display space and time to set up the display by 10 at the same time (2p G) Find the new total profit after decreasing the time to set up the display by 65min (1pt) H) You are offered the chance to obtain more space The offer is for 15 units and the cost of increasing the space is $150 Please place the question number on your answers (i e , A, B, , H) Place your answers on this Excel file only You can create a new worksheet with a title if you need it lets(X2), rings (X3), and earrings (X4) a jewelry store should stock The nt 2 measures time to set up the display in minutes Constraints 3 and eport can not be generated) r Reports for your answers tal profit (2pt) the second marketing restriction be exceeded (3pt) neously (2pts) lay by 10 at the same time (2pts) pt) st of increasing the space is $1500 Would you accept this offer Why (2pts) if you need it Q4 10 pts A hospital is planning an $8 million (up to $8,000,000) addition to its existing facility The architect has been asked to consider the following design parameters (1) There should be at least 10 and no more than 20 intensive care unit (ICU) rooms (2) there should be at least 10 and no more than 20 cardiac care unit (CCU) rooms (3) there should be no more than 50 double rooms (4) there should be at least 35 single rooms and (5) all patient rooms should fit inside the allotted 40,000square foot space ( no more than 40,000 sq f) Table1 summarizes the relevant room data How many rooms of each type should the architect include in the new hospital design a) which optimization method is the most appropriate to employ Linear, general integer, binary integer, mixed integer programming why (1 pt) b) Define the decision variables (1 pt ) c) Formulate the objective function (1 pt) d) Formulate the constraints (2pts) e) Solve it by the MS Excel Solver (5 pts) Please place the question number on your answers (i e , A, B, , E) Table 1 Summary of the relevant room data Cost per room to build and furnish square feet required Profit per room per month ($thousands) Single $45,000 300 $21 Room Types Decision Variables Optimal of Rooms Porfit per room x1 x2 47 7777778 21 Budget Constraint Space Constraint Min ICU Max ICU Min CCU Max CCU Max Dbl Rooms Min Single Rooms Double $54,000 360 $28 45000 300 ICU $110,000 320 $48 CCU $104,000 340 $41 x3 x4 40 28 15 48 10 41 54000 360 110000 320 1 1 104000 340 1 1 1 1 3253 33333333333 7000000 15 10 7000000 40000 15 20 10 20 40 35 Microsof Excel 16 0 Answer Report Worksheet CIS 623 R Gutierrez xlsx Q4 Report Created 5 6 2020 8 33 45 PM Result Solver found a solution All Constraints and optimality conditions are satisfied Solver Engine Engine Simplex LP Solution Time 0 047 Seconds Iterations 9 Subproblems 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0 000001, Use Automatic Scaling Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1 , Assume NonNegative Objective Cell (Max) Cell Name $J$12 Porfit per room Original Value Final Value 3307 3253 3333333 Variable Cells Cell Name $F$11 Optimal of Rooms x1 $G$11 Optimal of Rooms x2 $H$11 Optimal of Rooms x3 $I$11 Optimal of Rooms x4 Original Value Final Value Integer 35 47 777777778 Contin 40 40 Contin 20 15 Contin 12 10 Contin Constraints Cell Name $J$15 Budget Constraint $J$16 Space Constraint $J$17 Min ICU $J$18 Max ICU $J$19 Min CCU $J$20 Max CCU $J$21 Max Dbl Rooms $J$22 Min Single Rooms Cell Value Formula 7000000 $J$15 $L$17 15 $J$18 $L$19 10 $J$20 $L$22 Status Binding Not Binding Binding Not Binding Binding Not Binding Binding Not Binding Slack 0 3066 6666667 0 5 0 10 0 12 777777778 A Linear B Let, Xi number of rooms of type i to be included in the hospital design i 1, 2, 3, and 4 for Single, Double, ICU, and CCU C The objective function is to maximize the total profit per month of a combination of rooms D 1 Budget Constraint $48X1 $59X2 $95X3 $88X4 15 4 Maximum of ICUs X3 10 6 Maximum of CCUs X4 35 9 Nonnegative Constraint All Xi 0 E See answer report Q4 Q5 10 Pts Please place the question number on your answers (i e , A, B, and C) Microsof Excel 16 0 Answer Report Worksheet CIS 623 R Gutierrez xlsx Q5 Solution Report Created 5 6 2020 9 06 40 PM Result Solver found a solution All Constraints and optimality conditions are satisfied Solver Engine Engine Simplex LP Solution Time 0 063 Seconds Iterations 5 Subproblems 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0 000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1 , Assume NonNegative Objective Cell (Max) Cell Name $K$12 Objective Function Original Value 0 Final Value 22000 Variable Cells Cell Name $L$5 Municipal bonds $L$6 Electronic $L$7 Aerospace $L$8 Drugs $L$9 Nursing home Original Value 0 0 0 0 0 Final Value Integer 50000 Contin 0 Contin 0 Contin 125000 Contin 75000 Contin Constraints Cell Name $K$15 Constraints $K$16 Constraints $K$17 Constraints $K$18 Constraints Cell Value Formula 250000 $K$15 $M$15 50000 $K$16 $M$16 125000 $K$17 $M$17 75000 $K$18 125000 75000

Question

PLEASE  DO NOT OPEN TAB(s) ON YOUR BROWSER OTHE DO NOT SEND THE SOLUTION FILE TO YOUR EM DO NOT OPEN ANY COURSE MATERIAL   SAVE YOUR FILE AS  CIS623 FinalExam YourName Refresh your browser before submitting the solution file  Comment Q1 Q2 Q3 Q4 Q5 Total 10 15 15 10 10 0 60 WSER OTHER THAN YOUR CANVAS MYGATE ACCOUNT  O YOUR EMAIL CLOUD STORAGE   CCOUNT  or BINOM INV(n, p, RAND or BINOM INV(n, p, RAND()) Q1 10 Pts Monthly Sales between 2016 and 2018 Month 2016 2017 January 438 444 February 420 425 March 414 423 April 318 331 May 306 318 June 240 245 July 240 255 August 216 223 September 198 210 October 225 233 November 270 278 December 315 322 2018 450 438 434 338 331 254 264 231 224 243 289 335 For years The Glass Slipper restaurant has operated in a resort community n is busiest during the first 3 months of the year, when the ski slopes are crow shows a seasonal variability  When James and Deena Weltee built The Glass Slipper, they had a vision of surrounding mountains was breathtaking, a high priority was placed on hav from anywhere inside the restaurant  Special attention was also given to the truly magnificent experience for all who came to enjoy gourmet dining  Sinc maintained a reputation as one of the   must visit   places in that region of N While James loves to ski and truly appreciates the mountains and all that th retiring to a tropical paradise and enjoying a more relaxed lifestyle on the be condition, they knew that retirement was many years away  Nevertheless, t dream  They decided to sell The Glass Slipper and open a bed and breakfast Mexico  While this would mean that work was still in their future, they could blowing in the wind and the waves lapping at the shore  They also knew tha Deena the time to begin a semi retirement in a corner of paradise  To make this happen, James and Deena would have to sell The Glass Slipper based on the value of the property and equipment as well as projections of A forecast of sales for the next two year is needed to help in the determinati each of the past 3 years are provided in Table  Anova  Single Factor A  Which forecasting model is the most appropriate for future sales  Why  SUMMARY Groups Month January February March April May June July August September October November December Count 3 3 3 3 3 3 3 3 3 3 3 3 3 Sum 6051 1332 1283 1271 987 955 739 759 670 632 701 837 972 Average 2017 444 427 6667 423 6667 329 318 3333 246 3333 253 223 3333 210 6667 233 6667 279 324 B  Write the proposed model after the analysis and interpret the slope  (2p Variance 1 the most appropriate forecasting model to these data to forecast s C  Apply 36 86 33333 Please place the question number on your answers (i e , A, B, and C) 100 3333 103 156 3333 50 33333 147 56 33333 169 3333 81 33333 91 103 ANOVA Source of VariaSS df Between Group 8305335 Within Groups 2362 667 MS F P value F crit 12 692111 2 7616 348 6 812E 43 2 147926 26 90 87179 Total 38 8307697 d in a resort community near a popular ski area of New Mexico  The restaurant en the ski slopes are crowded and tourists flock to the area  Thus, the revenue pper, they had a vision of the ultimate dining experience  As the view of priority was placed on having large windows and providing a spectacular view ntion was also given to the lighting, colors, and overall ambiance, resulting in a enjoy gourmet dining  Since its opening, The Glass Slipper has developed and    places in that region of New Mexico  mountains and all that they have to offer, he also shares Deena's dream of relaxed lifestyle on the beach  After some careful analysis of their financial ears away  Nevertheless, they were hatching a plan to bring them closer to their open a bed and breakfast on a beautiful beach in l in their future, they could wake up in the morning to the sight of the palm trees shore  They also knew that hiring the right manager would allow James and rner of paradise  ve to sell The Glass Slipper for the right price  The price of the business would be t as well as projections of future income  to help in the determination of the value of the restaurant  Monthly sales for ate for future sales  Why  (2pts) nd interpret the slope  (2pts) to these data to forecast sales for each month of the 2019 and 2020  (6pts) ers (i e , A, B, and C) A  Multiplicative decompositiong since there is a seasonal variation that grows proportionally with time  Jan 16 Feb 16 Mar 16 Apr 16 May 16 Jun 16 Jul 16 Aug 16 Sep 16 Oct 16 Nov 16 Dec 16 Jan 17 Feb 17 Mar 17 Apr 17 May 17 Jun 17 Jul 17 Aug 17 Sep 17 Oct 17 Nov 17 Dec 17 Jan 18 Feb 18 Mar 18 Apr 18 May 18 Jun 18 Jul 18 Aug 18 Sep 18 Oct 18 Nov 18 Dec 18 438 420 414 318 306 240 240 216 198 225 270 315 444 425 423 331 318 245 255 223 210 233 278 322 450 438 434 338 331 254 264 231 224 243 289 335 Problem Title Forecasting 12 seasons Multiplicative decomposition Enter Enter the the actual actual values values in in the the cells cells shaded shaded YELLOW  YELLOW  Do Do not not change change the the time time period period numbers  numbers  Input Data Period Period 1 Period 2 Period 3 Seasonal Index Computation Actual value (Y) Seasonal Ratios Season 1 Average Time Centered period (X) average 1 2 3 Seasonal ratio Forecast Error analysis Unseasonal Seasonal Unseasonal ized Seasonalize index ized value Forecast d Forecast  DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0  Average Intercept  DIV 0  Slope  DIV 0  Season 2 Season 3 Season 4 Season 5 Season 6 Season 7 Season 8  DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0  Forecasts for future periods Unseasonal ized Seasonal Seasonalize Period forecast index d forecast 4  DIV 0   DIV 0   DIV 0  5  DIV 0   DIV 0   DIV 0  6  DIV 0   DIV 0   DIV 0  7  DIV 0   DIV 0   DIV 0  8  DIV 0   DIV 0   DIV 0  9  DIV 0   DIV 0   DIV 0  10  DIV 0   DIV 0   DIV 0  11  DIV 0   DIV 0   DIV 0  12  DIV 0   DIV 0   DIV 0  13  DIV 0   DIV 0   DIV 0  14  DIV 0   DIV 0   DIV 0  15  DIV 0   DIV 0   DIV 0  numbers  numbers  Error  DIV 0   DIV 0   DIV 0  Absolute Squared Absolute   error error error  DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0   DIV 0  MAD MSE MAPE Season 9 Season 10 Season 11 Season 12  DIV 0   DIV 0   DIV 0   DIV 0  Q2 15pts Table 1  Prob distribution of the weekly demand Demand 2 4 6 8 10 12 Probability 0 05 0 27 0 15 0 18 0 25 0 10 A factory produces office chairs  According to the past data, the weekly demand has on Table 1  The selling price per chair is $120  In addition, the historical data showed manufacturing a chair showed a normal probability distribution with mean of $40 a A  Simulate this production of chairs for a year (52 weeks  1 replication) to calculate Hint   Net profit   Revenu   Cost  where  Revenue   Demand   Unit selling price   Hint  Demand and Cost are random variables based on the given probability distri B  Calculate the average weekly profit and the percentage of generating $500 or mo (based on 1 replication) (2pts) C  Usint Data Table, replicate the mean net profit and mean percentage of generati 200 times  (2pts) D  Determine the average weekly profit and the percentage of generating $500 or m replications  (1pts) E  Evaluate the average weekly profit and the percentage of generating $500 or mo chair is $100 and $110 (scenario manager) (3pts) F  Determine range of true mean net profit based on 200 replication with 95  confi G  Insert a histogram chart that shows the net profit based on 200 replications  (1p Please place the question number on your answers (i e , A, B,   , G) data, the weekly demand has the probability distribution given on, the historical data showed that the unit cost of tribution with mean of $40 and the standard deviation of $5  eks  1 replication) to calculate the weekly net profit  (4pts) Demand   Unit selling price   n the given probability distributions  age of generating $500 or more net profit based on 52 weeks mean percentage of generating $500 or more obtained in (B) ntage of generating $500 or more net profit based on 200 age of generating $500 or more net profit if the selling price of a 200 replication with 95  confidence level (2pts) based on 200 replications  (1pt) e , A, B,   , G) Demand Probability 2 0 05 4 0 27 6 0 15 8 0 18 10 0 25 12 0 10 Lower Limit Upper Limit 0 0 05 0 05 0 32 0 32 0 47 0 47 0 65 0 65 0 90 0 90 1 00 1 00 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 6 6 6 12 4 12 10 12 8 10 6 10 10 6 10 8 12 10 8 8 6 6 12 8 10 Net profit St dev 8 64 2 430363 Q3 15pts The decision variables of the given LP model determines how many necklaces (X1), bracelets(X2), rings (X3), and earrings objective function measures profit  Constraint 1 measures display space in units, constraint 2 measures time to set up the constraint 4 are marketing restrictions  Objectifive function  MAX   $100X1 $120X2 $150X3 $125X4 Constraints  c1) X1 2X2 2X3 2X4 108 (Display space in units) c2) 3X1 5X2 X4 120 (Time to set up the display in minutes) c3) X1 X3 25 (Marketing restriction I) c4) X2 X3 X4 50 (Marketing restriction II) (Xi   0, consider them as real numbers, not as INTEGERs since the Sensitivity Analysis Report can not be generated) A)  Generate the Sensitivity Analysis Report and the Answer Report(2pts)    For the given questions below, use only the Sensitivity Analysis  Answer Reports for your answers    B)  How many necklaces, bracelets, rings, and earrings should be stocked  What is the total profit  (2pt) C)  How much space will be left unused  How much time will be used  By how much will the second marketing restrictio D)  Find the total profit after increasing the unit profit of rings and earrings by $5 simultaneously (2pts) E)  Find the new total profit after decreasing unit profit of necklace by $15  (1pt) F)  Find the new total profit after increasing the display space and time to set up the display by 10  at the same time  (2p G)  Find the new total profit after decreasing the time to set up the display by 65min  (1pt) H)  You are offered the chance to obtain more space  The offer is for 15 units and the cost of increasing the space is $150   Please place the question number on your answers (i e , A, B,   , H)    Place your answers on this Excel file only  You can create a new worksheet with a title if you need it  lets(X2), rings (X3), and earrings (X4) a jewelry store should stock  The nt 2 measures time to set up the display in minutes  Constraints 3 and eport can not be generated) r Reports for your answers     tal profit  (2pt) the second marketing restriction be exceeded  (3pt) neously (2pts) lay by 10  at the same time  (2pts) pt) st of increasing the space is $1500  Would you accept this offer  Why  (2pts) if you need it  Q4 10 pts A hospital is planning an $8 million (up to $8,000,000) addition to its existing facility  The architect has been asked to consider the following design parameters  (1) There should be at least 10 and no more than 20 intensive care unit (ICU) rooms  (2) there should be at least 10 and no more than 20 cardiac care unit (CCU) rooms  (3) there should be no more than 50 double rooms  (4) there should be at least 35 single rooms  and (5) all patient rooms should fit inside the allotted 40,000square foot space ( no more than 40,000 sq f)  Table1 summarizes the relevant room data  How many rooms of each type should the architect include in the new hospital design  a) which optimization method is the most appropriate to employ  Linear, general integer, binary integer, mixed integer programming  why  (1 pt) b) Define the decision variables (1 pt ) c) Formulate the objective function (1 pt) d) Formulate the constraints (2pts) e) Solve it by the MS Excel Solver (5 pts) Please place the question number on your answers (i e , A, B,   , E) Table 1  Summary of the relevant room data Cost per room to build and furnish square feet required Profit per room per month ($thousands) Single $45,000 300 $21 Room Types Decision Variables Optimal   of Rooms Porfit per room x1 x2 47 7777778 21 Budget Constraint Space Constraint Min   ICU Max   ICU Min   CCU Max   CCU Max Dbl Rooms Min Single Rooms Double $54,000 360 $28 45000 300 ICU $110,000 320 $48 CCU $104,000 340 $41 x3 x4 40 28 15 48 10 41 54000 360 110000 320 1 1 104000 340 1 1 1 1 3253 33333333333 7000000   15   10   7000000 40000 15 20 10 20 40 35 Microsof Excel 16 0 Answer Report Worksheet   CIS 623  R Gutierrez xlsx Q4 Report Created  5 6 2020 8 33 45 PM Result  Solver found a solution  All Constraints and optimality conditions are satisfied  Solver Engine Engine  Simplex LP Solution Time  0 047 Seconds  Iterations  9 Subproblems  0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0 000001, Use Automatic Scaling Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1 , Assume NonNegative Objective Cell (Max) Cell Name $J$12 Porfit per room Original Value Final Value 3307 3253 3333333 Variable Cells Cell Name $F$11 Optimal   of Rooms x1 $G$11 Optimal   of Rooms x2 $H$11 Optimal   of Rooms x3 $I$11 Optimal   of Rooms x4 Original Value Final Value Integer 35 47 777777778 Contin 40 40 Contin 20 15 Contin 12 10 Contin Constraints Cell Name $J$15 Budget Constraint $J$16 Space Constraint $J$17 Min   ICU $J$18 Max   ICU $J$19 Min   CCU $J$20 Max   CCU $J$21 Max Dbl Rooms $J$22 Min Single Rooms Cell Value Formula 7000000 $J$15 $L$17 15 $J$18 $L$19 10 $J$20 $L$22 Status Binding Not Binding Binding Not Binding Binding Not Binding Binding Not Binding Slack 0 3066 6666667 0 5 0 10 0 12 777777778 A  Linear B  Let, Xi   number of rooms of type i to be included in the hospital design i   1, 2, 3, and 4 for Single, Double, ICU, and CCU  C  The objective function is to maximize the total profit per month of a combination of rooms  D  1 Budget Constraint $48X1   $59X2   $95X3   $88X4   15 4 Maximum   of ICUs X3   10 6 Maximum   of CCUs X4   35 9 Nonnegative Constraint All Xi    0 E  See answer report Q4 Q5  10 Pts Please place the question number on your answers (i e , A, B, and C) Microsof Excel 16 0 Answer Report Worksheet   CIS 623  R Gutierrez xlsx Q5 Solution Report Created  5 6 2020 9 06 40 PM Result  Solver found a solution  All Constraints and optimality conditions are satisfied  Solver Engine Engine  Simplex LP Solution Time  0 063 Seconds  Iterations  5 Subproblems  0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0 000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1 , Assume NonNegative Objective Cell (Max) Cell Name $K$12 Objective Function Original Value 0 Final Value 22000 Variable Cells Cell Name $L$5 Municipal bonds $L$6 Electronic $L$7 Aerospace $L$8 Drugs $L$9 Nursing home Original Value 0 0 0 0 0 Final Value Integer 50000 Contin 0 Contin 0 Contin 125000 Contin 75000 Contin Constraints Cell Name $K$15 Constraints $K$16 Constraints $K$17 Constraints $K$18 Constraints Cell Value Formula 250000 $K$15 $M$15 50000 $K$16  $M$16 125000 $K$17  $M$17 75000 $K$18  125000    75000

Accepted Answer

The Answer is in the image, click to view ...

Question

PLEASE: DO NOT OPEN TAB(s) ON YOUR BROWSER OTHE DO NOT SEND THE SOLUTION FILE TO YOUR EM DO NOT OPEN ANY COURSE MATERIAL .

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Management Fundamentals

Students also viewed these General Management questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question