Hotel Conventions
The midterm elections
November 19, 2018Sorption data analysis
November 19, 2018YO16_XL_BU05_GRADER_PS3_AS – Hotel Conventions 1.3
Project Description:
The hotel at the Painted Paradise Resort and Spa is looking to expand its ability to hold business conventions. You have been asked to help in a variety of ways. First, a list of businesses has been provided to you as a comma-delimited text file. You will need to import this data into and Excel workbook so that these businesses can be contacted later. Second, Painted Paradise is considering taking out a loan to pay for the construction of new meeting spaces for business conventions. You have been asked to help develop a loan analysis for this project. Lastly, the new meeting spaces will add to the three rooms already available to the hotel. You have been asked to develop a Solver model to help determine the optimal number of events that should be scheduled in each room in order to maximize profit. You have been told that the Eldorado Room currently has two regularly occurring meetings per month.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
| Step | Instructions | Points Possible |
| 1 | Start Excel. Open the downloaded workbook named e05_grader_a1_HotelExpansion.xlsx. Save the file with the name e05_grader_a1_HotelExpansion_LastFirst, using your last and first name. If necessary, enable the content. | 0.000 |
| 2 | Import the data from the comma-delimited text file e05Businesses.txt into cell A1 of the Sheet1 worksheet. Rename the sheet NewBusinesses. | 10.000 |
| 3 | Use the Convert Text to Columns Wizard to separate the data into columns. Use cell B1 for the destination of the data. AutoFit the column widths. | 10.000 |
| 4 | On the LoanAnalysis worksheet, in cell E4, reference the monthly payment calculation in cell B7. | 8.000 |
| 5 | Create a two-variable data table using the range E4:K10. Use the interest rate as the row input for the data table. Use the down payment for the column input for the data table. | 10.000 |
| 6 | Format the data in cells F5:K10 as Currency. Use conditional formatting to apply a Red-Yellow-Green color scale to the data. | 10.000 |
| 7 | The Painted Paradise Resort and Spa is considering adding two new meeting rooms, which have yet to be named. One room will hold 250 people, while the other will hold 125. The RoomScheduling worksheet contains a model that describes the pricing model for scheduling conference meeting rooms. Begin to create a Solver model that will help Painted Paradise maximize their profit by scheduling events. Use the Net Income in cell C24 as the Set Objective cell. | 7.000 |
| 8 | Use the Events per Month in cells E5:E9 as the By Changing Variable Cells box. | 7.000 |
| 9 | Add a constraint that limits the number of events in each room per month to <=6. | 8.000 |
| 10 | Add a constraint that sets the minimum number of events in the Pueblo Room to greater than or equal to two. | 8.000 |
| 11 | Use Simplex LP as the Solving Method. | 7.000 |
| 12 | Save a copy of the Solver model to cell A27. | 7.000 |
| 13 | Run Solver and create an Answer report. | 8.000 |
| 14 | Close the workbook and then exit Excel. Submit the workbook as directed. | 0.000 |
| Total Points | 100.000 |