Question

Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? F27 fx A B с D E F Painted Paradise Resort & Spa Solver for Room Scheduling 2 3 Revenue 4 Capacity 5 6 7 Events per Month 1 1 Room The Muisca Room The Eldorado Room The Pueblo Room New Room 1 New Room 2 Price 500 $ 100 $ 25 $ 250 $ 125 $ 1 7,500.00 1,500.00 375.00 3,750.00 1,875.00 15,000.00 8 1 1 9 $ Utilities Room Maintenance $ $ $ 280.00 170.00 450.00 10 11 12 Expenses 13 Fixed Costs 14 15 16 Total Fixed Costs 17 18 Variable Costs 19 20 Total Variable Costs 21 22 Total Expenses 23 24 Net Income 25 Staff Salaries per event $ $ 550.00 2,750.00 $ 3,200.00 $ 11,800.00 Saved Solver Models 26 27 28 29 30 31 32 33 34 35 ac

Answer 1

Some Assumptions:

1. Fixed Cost does not change as per event. It is fixed for the entire month.

Solution:

Solver can be found in Data Ribbion> Analysis Group. Click on Solver.

Step 1

The above image is the same as the question. Number of event per month per room is one.

А B E F 1 D Painted Paradise Resort and SPA Solver for Room Scheduling 2 3 Revenue 4 Capacity 1 5 6 Room The Muisca Room The Eldorado Room The Pueblo Room New Room 1 New Room 2 Price in s 500 100 25 1 Event per month 7500 1500 375 3750 1875 15000 7 8 9 1 1 250 125 1 10 11 Expenses 12 Fixed Costs 13 14 15 Total Fixed Cost Utilities Room Maintenance 280 170 450 Staff Salaries per event 550 2750 17 Variable Costs 18 19 Total Variable costs 20 21 Total Expenses 22 23 Net Income 24 25 3200 11800 Saved Solver Models 26 27 Sheet1

Step 2:

The values are entered in Excel Solver.

En 1 国 LE Data An LA From Access Te From Web From Other Lì From Text Sources 1x Clear Reapply Advanced Solver Parameters 9 Connections Properties Refresh All Edit Links Connections Existing Connections A Sort Filter Text to Flash Columns Fill Get External Data Sort & Filter Set Objective: SD$23 EN . To: O Max O Min Value Of: 0 A X ✓ fx с D Painted Paradise Resort and SPA Solver for Room Scheduling B E F 1 By Changing Variable Cells: SF$5:SF59 ES 2 3 Revenue 4 Capacity 5 Add 1 1 Room The Muisca Room The Eldorado Room The Pueblo Room New Room 1 New Room 2 Price in $ 500 100 25 250 125 6 7 8 9 Event per month 7500 1500 375 3750 1875 15000 Subject to the Constraints: SFS5 <= 6 SFS6 <= 6 SFS7 <= 6 SFS7 >= 2 SFS8 <= 6 SFS9 <= 6 1 Change 1 1 Delete Reset All 10 11 Expenses 12 Fixed Costs 13 14 15 Total Fixed Cost Utilities Room Maintenance Load/Save 280 170 450 ✓ Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options 17 Variable costs 18 Staff Salaries per event 550 2750 Solving Method 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. 3200 19 Total Variable costs 20 21 Total Expenses 22 23 Net Income 24 25 11800 Help Solve Close 26 Saved Solver Models

Some points to note:

1. Objective Function is set to Net Income value (in above picture cell D23 i.e 11800).

2. Our objective is to maximise the net income.

3. Variable cells are set to Number of event per month in the given rooms.

4. The Constraints were set to as given in the question (Step 9, 10, 11)

Which are basically Number of event in all events is set to less than or equal to 6 per month (Step 9, 10). Also for The Pueblo Room it is also set to more than or equal to 2 (Step 11).

5. Solving method is set to Simplex LP.

6. The solution is saved to A27.

Step 3:

Click on Solve.

The following Window pops up.

Click on OK.

X ✓ fx =MAX($D$23) A B C D E G Н J K L M N Solver for Room Scheduling Solver Results 2 3 Revenue 4 Capacity Price in s 5 500 6 6 100 Room The Muisca Room The Eldorado Room The Pueblo Room New Room 1 New Room 2 Event per month 7500 1500 375 3750 1875 6 2 7 Solver found a solution. All Constraints and optimality conditions are satisfied. Reports Answer O Keep Solver Solution Sensitivity Limits O Restore Original Values 25 250 8 6 6 9 125 88500 Return to Solver Parameters Dialog Outline Reports 10 11 Expenses 12 Fixed Costs 13 14 15 Total Fixed Cost 280 Utilities Room Maintenance OK Cancel Save Scenario.. 170 450 Staff Salaries per event 550 14300 Solver found a solution. All constraints and optimality conditions are satisfied. When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution. 17 Variable Costs 18 19 Total Variable costs 20 21 Total Expenses 22 23 Net Income 24 25 14750 73750 26 Saved Solver Models

Step 4:

Solution found.

A B с D E F 1 Painted Paradise Resort and SPA Solver for Room Scheduling Capacity 6 2 3 Revenue 4 5 6 7 8 9 10 11 Expenses 12 Fixed Costs 13 14 15 Total Fixed Cost Room The Muisca Room The Eldorado Room The Pueblo Room New Room 1 New Room 2 Price in $ 500 100 25 250 125 Event per month 7500 1500 6 375 2 3750 6 1875 6 88500 Utilities Room Maintenance 280 170 450 Staff Salaries per event 550 14300 14750 73750 Saved Solver Models 17 Variable Costs 18 19 Total Variable costs 20 21 Total Expenses 22 23 Net Income 26 27 73750 28 5 29 TRUE 30 TRUE 31 TRUE 32 TRUE 33 TRUE 34 TRUE 35 32767 36 0 37

Solution is:

Net Income: $73,750

All the conditions are satisfied as we can see "TRUE" for all the constraints.

Number of event:

Room			Event per month
The Muisca Room			6
The Eldorado Room			6
The Pueblo Room			2
New Room 1			6
New Room 2			6

If you found the answer helpful, please upvote/ give a thumbs up. It motivates me to write such great answers. Thanks