e05 grader a1 HotelExpansion

Anonymous
timer Asked: Apr 17th, 2019
account_balance_wallet $10

Question Description

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.

Unformatted Attachment Preview

"BusinessID,Name,StreetAddress,City,State" "1001,Barry's Pet Place,808 JAYME CIRCLE ,Santa Fe,AZ" "1002,Electronic Wholesalers,7538 QUEENS VIEW DRIVE ,Prescott Valley,NM" "1003,Online Auctions Inc.,1818 SCALERO STREET ,Rio Rancho,AZ" "1004,Eccletic Books,2920 MARINER AVENUE ,Clovis,AZ" "1005,Boxes and More,5629 OLSON CIRCLE ,Prescott Valley,NM" "1006,Golf Supplies Unlimited,5840 RICKY PLACE ,Los Alamos,AZ" "1007,Printing Services Plus,2217 RAINBOW DRIVE ,Goodyear,AZ" "1008,Computer Accessories Etc,362 SCHOON PLACE ,Mammoth,AZ" "1009,Mobile Computing Specialists,7770 LINDEN DRIVE ,Safford[b],AZ" "1010,Movie House Posters,6033 BRIDGET PLACE ,Los Lunas,NM" Office 2016 – myitlab:grader – Instructions Excel Project YO16_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-YellowGreen 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 Updated: 10/15/2017 1 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Step 9 10 11 12 Excel Project Points Possible Instructions Add a constraint that limits the number of events in each room per month to <=6. 8.000 Add a constraint that sets the minimum number of events in the Pueblo Room to greater than or equal to two. 8.000 Use Simplex LP as the Solving Method. 7.000 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 Updated: 10/15/2017 2 100.000 Current_Instruction.docx Painted Paradise Resort & Spa Loan Analysis $350,000 5 6.00% $20,000 $6,379.82 4.50% Down Payment Loan Amount Term (Years) Interest Rate Down Payment Monthly Payment $ $ $ $ $ $ 20,000.00 25,000.00 30,000.00 35,000.00 40,000.00 45,000.00 20190417175659alfarj_e05_grader_a1_hotelexpansion.xlsx 5.00% Interest Rates 5.50% 6.00% 6.50% 7.00% Painted Paradise Resort & Spa Solver for Room Scheduling Revenue Room The Muisca Room The Eldorado Room The Pueblo Room New Room 1 New Room 2 Capacity 500 100 25 250 125 Expenses Fixed Costs Utilities Room Maintenance Total Fixed Costs $ $ $ 250.00 150.00 400.00 Variable Costs Staff Salaries per event $ 500.00 Total Variable Costs $ 7,000.00 Total Expenses $ 7,400.00 Net Income ######### Saved Solver Models Price Events per Month ######### 3 $ 1,500.00 1 $ 1,125.00 3 ######### 5 $ 3,750.00 2 ######### Create Date By Whom mm/dd/yyyy Firstname Lastname Mod. Date By Whom Description Create Date Creator Sheet Name Mod. Description 20190417175659alfarj_e05_grader_a1_hotelexpansion.xlsx Workbook Name e05HotelExpansion.xlsx Last Version Backup Name Purpose 20190417175659alfarj_e05_grader_a1_hotelexpansion.xlsx 20190417175659alfarj_e05_grader_a1_hotelexpansion.xlsx ...
Purchase answer to see full attachment

Tutor Answer

andythewxman
School: UIUC

Here is the last one. As always, let me know if you have any questions.

BusinessID,Name,StreetAddress,City,State
BusinessID Name
1001,Barry's Pet Place,808 JAYME CIRCLE ,Santa Fe,AZ
1001 Barry's Pet Place
1002,Electronic Wholesalers,7538 QUEENS VIEW DRIVE ,Prescott Valley,NM 1002 Electronic Wholesalers
1003,Online Auctions Inc.,1818 SCALERO STREET ,Rio Rancho,AZ
1003 Online Auctions Inc.
1004,Eccletic Books,2920 MARINER AVENUE ,Clovis,AZ
1004 Eccletic Books
1005,Boxes and More,5629 OLSON CIRCLE ,Prescott Valley,NM
1005 Boxes and More
1006,Golf Supplies Unlimited,5840 RICKY PLACE ,Los Alamos,AZ
1006 Golf Supplies Unlimited
1007,Printing Services Plus,2217 RAINBOW DRI...

flag Report DMCA
Review

Anonymous
awesome work thanks

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors