Showing Page:
1/2
Grader - Instructions Excel 2019 Project
Created On: 12/13/2019 1 YO19_Excel_BU05_Assessment2 - Hotel Conventions 1.0
YO19_Excel_BU05_Assessment2_Hotel_Conventions
Project Description:
The hotel at the Painted Paradise Resort &amp; Spa wants 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 an 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.
Steps to Perform:
Step
Instructions
Points
Possible
1
to the location where you are storing your files.
0
2
A list of businesses has been provided in a text file. These businesses need to be contacted
but first the data needs to be imported into Excel and transformed.
Establish a connection to the text file Excel_BU05_A2_Businesses.txt.
0
3
The City/State data is organized with the city name, then the state abbreviation. For example,
Prescott Valley NM where Prescott Valley is the city and NM is the state.
Split the City/State column into two individual columns, City and State. Remove the City/State
field, if necessary.
6.4
4
Close & Load the data to cell A1 of the NewBusinesses worksheet.
1.6
5
You have been asked to create a two-variable data table that analyzes the changes in monthly
payments as the hotel considers different interest rates and down payments.
On the LoanAnalysis worksheet, in cell E4, reference the monthly payment calculation in cell
B7.
3.2
6
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.
4
7
Format the data in cells F5:K10 as Currency. Use conditional formatting to apply a Red-
Yellow-Green color scale to the data.
4
8
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.
2.8
9
Use the Events per Month in cells E5:E9 as the By Changing Variable Cells box.
2.8
10
Add a constraint that limits the number of events in each room per month to <=6.
3.2
Showing Page:
2/2
Grader - Instructions Excel 2019 Project
Created On: 12/13/2019 2 YO19_Excel_BU05_Assessment2 - Hotel Conventions 1.0
Step
Instructions
Points
Possible
11
Add a constraint that sets the minimum number of events in the Pueblo Room to greater than
or equal to two.
3.2
12
Use Simplex LP as the Solving Method.
2.8
13
Save a copy of the Solver model to cell A27.
2.8
14
Run Solver and create an Answer report.
3.2
15
Save and close Excel_BU05_A2_HotelConventions.xlsx. Exit Excel. Submit your files as
directed.
0
Total Points
40

### Unformatted Attachment Preview

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.
Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4