Question

Round Tree Manor is a hotel that provides two types of rooms with three rental classes: Super Saver, Deluxe, and Business. The profit per night for each type of room and rental class is as follows: Rental Class Super Saver Deluxe Business Type I (Mountain View) $35 $40 - Room Type II (Street View) $25 $35 $45 Round Tree's management makes a forecast of the demand by rental class for each night in the future. A linear programming model developed to maximize profit is used to determine how many reservations to accept for each rental class. The demand forecast for a particular night is 120 rentals in the Super Saver class, 65 in the Deluxe class, and 50 in the Business class. Since these are the forecasted demands, Round Tree will take no more than these amounts of each reservation for each rental class. Round Tree has a limited number of each type of room. There are 100 Type I rooms and 110 Type II rooms. (a) Formulate and solve a linear program to determine how many reservations to accept in each rental class and how the reservations should be allocated to room types. If an amount is zero, enter "O". Rental Class with room type No of Reservations Super Saver rentals allocated to room type I Super Saver rentals allocated to room type II Deluxe rentals allocated to room type I Deluxe rentals allocated to room type II Business rentals allocated to room type II (b) For the solution in part (a), how many reservations can be accommodated in each rental class? Rental No of Class Reservations Super Saver Deluxe Business Demand for - Select your answer - rental class was not satisfied. (c) With a little work, an unused office area could be converted to a rental room. If the conversion cost is the same for both types of rooms, would you recommend converting the office to a Type I or a Type II room? Type I Type II Shadow Prices Convert an unused office area to - Select your answer - room. Explain. The input in the box below will not be graded, but may be reviewed and considered by your instructor. blank

Answer 1

Answer: a) LP model formulation Date: 11/11/2019 Decision variables: S1, S2 Number of rooms reserved of Super Saver rental class of type 1 and 2 respectively D1, D2 Number of rooms reserved of Deluxe rental class of type 1 and 2 respectively B1 Number of rooms reserved of Business rental class of type 1 Objective: Maiximize Z 35S1+25S2+40D1+35D2+45B2 Constraints: S1+S2 120 D1+D2 <= 65 B2 <=50 S1+D1 <= 100 S2+D2+B2 < 110 S1, D1, S2, D2, B2> 0 Solution using Excel Solver is following

Clear -Consolidate Show Queries Flash Fill Group Connections A-ARemove Duplicates Relationships Reanply Filter Unaroup From Table Properties Get External New Refresh Sort Solver Parameters Data Edit Links Query L Recent Sources All Get & Transtorm Connections Sort & Filter le Set Objective SGS2 SUMPRODUCT(B2:F2,$B$9: $F$9) G2 To: 0 Value Of Маx Min A C D E F G н J 1 S1 S2 D1 D2 B2 By Changing Variable Cells: 2 Profit 35 25 40 35 45 7875 SBS9:SFS9 3 1 1 95 120 4 1 1 65 65 Subject to the Constraints: 5 1 50 50 <= SG$3:SGS7 < SI$3:SI$7 Add 100 100 6 1 1 7 1 1 1 110 = 110 Change 8 9 Optimal Solution: 95 0 60 50 Delete 10 11 Beset All 12 13 Load/Save 14 Make Unconstrained Variables Non-Negative 15 Select a Solving Method Simplex LP Options 16 18 Solving Method 19 20 Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth 21. 22 23 24 Help Solve Clgse 25

Formula: G2 SUMPRODUCT(B2:F2,$B$9:$F$9) copy to G2:G7 Rental Class with room type No. of Reservations Super Saver rentals allocated to room 95 type Super Saver rentals allocated to room O type I Deluxe rentals allocated to room type l Deluxe rentals allocated to room type II 60 Business rentals allocated to room type lIl 50 b) No. of Reservations Rental Class Super Saver 95 Deluxe 65 Business 50 Demand for Super Saver class was not satisfied c) Refer sensitivity report below

1 Microsoft Excel 16.0 Sensitivity Report 2Worksheet: [Book1] Sheet9 3 Report Created: 30-10-2017 11:29:49 4 5 6 Variable Cells Final Reduced Objective Allowable Allowable 7 Cell Value Coefficient Increase Decrease 8 Name Cost $B$9 Optimal Solution: S1 $C$9 Optimal Solution: S2 $D$9 Optimal Solution: D1 $E$9 Optimal Solution: D2 $F$9 Optimal Solution: B2 95 0 35 5 5 1E+30 0 -5 25 5 11 5 0 40 5 5 12. 60 0 35 15 5 13 50 0 45 1E+30 15 14 15 Constraints Final Shadow Constraint Allowable Allowable 16 Cell Value Price R.H. Side 17 Name Increase Decrease $G$3 Super Saver 18 95 0 120 1E+30 25 $G$4 Deluxe 19 65 5 65 95 5 $G$5 Business 50 15 50 60 $G$6 Type 1 $G$7 Type 2 21 100 35 100 25 95 22 110 30 110 60 23 Shadow price of Type I 35 Shadow price of Type II 30 Shadow price of Type I is higher. Therefore, convert unused office area to Typel