PLEASE READ AND ANSWER COMPLELY. THUMBS UP FOR CORRECT ANSWERS ONLY

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
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

NEED SOMEETHING PLS COMMENT ASAP:)

THE LIST OF BUSINESSES.TXT

BusinessID Name StreetAddress City/State 1001 Computer Accessories Etc 362 SCHOON PLACE Mammoth-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 Movie House Posters 6033 BRIDGET PLACE Los Lunas-NM 1007 Printing Services Plus 2217 RAINBOW DRIVE Goodyear-AZ 1008 Barry's Pet Place 808 JAYME CIRCLE Santa Fe-AZ 1009 Mobile Computing Specialists 7770 LINDEN DRIVE Safford-AZ 1010 Golf Supplies Unlimited 5840 RICKY PLACE Los Alamos-AZ

A1 1 2 B 4 5 5 7 3 9 495AWN 050 000UAWNO 1000 GSA WN-O 0 1 2 3 4 5 6 7 8 6 21 22 23 24 25 26 27 8 9 30 #1 13 34 35 =6 18 19 -0 -1 Ready O A B fx E F G NewBusinesses LoanAnalysis RoomScheduling H I Documentation J K L Painted Paradise Resort & Spa A B D E F Painted Paradise Resort & Spa 2 Loan Analysis 3 Loan Amount $375,000 4 Term (Years) 5.00% 5 Interest Rate 6 6.50% $25,000 $ 20,000.00 6 Down Payment $ 25,000.00 7 Monthly Payment $5,883.48 30,000.00 8 $ 35,000.00 9 $ 40,000.00 $ 45,000.00 10 11 12 13 A1 3 4 5 6 14 15 16 17 18 97222222228 19 20 21 23 24 25 26 30 31 32 33 34 35 36 w w w w + w w w w 38 39 40 41 X Down Payment NewBusinesses LoanAnalysis G 5.50% RoomScheduling Documentation H Interest Rates 6.00% I 6.50% J 7.00% K 7.50% L A6 x A Painted Create Date By Whom mm/dd/yyyy Firstname Lastname Mod. Date By Whom 0 1 2 3 4 5 6 7 8 9 Create Date Sheet Name 0 1 2 1 2 B B 9 3 4 5 6 7 8 9 0 3 4 5 6 7 8 9 -0 1 Ready mm/dd/yyyy B Paradise RESORT & SPA NewBusinesses LoanAnalysis Description Mod. Description Creator RoomScheduling Documentation + D Workbook Name e05Hotel Expansion.xlsx Last Version Backup Name Purpose E F E9 1 2 3 Revenue 234 4 5 1560700 A X 8 9 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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 1 B Painted Paradise Resort & Spa Solver for Room Scheduling Capacity 500 $ 100 $ 25 $ 250 $ 125 $ $ Room The Muisca Room The Eldorado Room The Pueblo Room New Room 1 New Room 2 Utilities Room Maintenance Staff Salaries per event $ $ $ $ $ $ is 280.00 170.00 450.00 550.00 2,750.00 3,200.00 11,800.00 Price D 7,500.00 1,500.00 375.00 3,750.00 1,875.00 15,000.00 $ Saved Solver Models NewBusinesses LoanAnalysis RoomScheduling Documentation E Events per Month 1 1 1 1 1 + F G A1 1 2 B 4 5 5 7 3 9 495AWN 050 000UAWNO 1000 GSA WN-O 0 1 2 3 4 5 6 7 8 6 21 22 23 24 25 26 27 8 9 30 #1 13 34 35 =6 18 19 -0 -1 Ready O A B fx E F G NewBusinesses LoanAnalysis RoomScheduling H I Documentation J K L Painted Paradise Resort & Spa A B D E F Painted Paradise Resort & Spa 2 Loan Analysis 3 Loan Amount $375,000 4 Term (Years) 5.00% 5 Interest Rate 6 6.50% $25,000 $ 20,000.00 6 Down Payment $ 25,000.00 7 Monthly Payment $5,883.48 30,000.00 8 $ 35,000.00 9 $ 40,000.00 $ 45,000.00 10 11 12 13 A1 3 4 5 6 14 15 16 17 18 97222222228 19 20 21 23 24 25 26 30 31 32 33 34 35 36 w w w w + w w w w 38 39 40 41 X Down Payment NewBusinesses LoanAnalysis G 5.50% RoomScheduling Documentation H Interest Rates 6.00% I 6.50% J 7.00% K 7.50% L A6 x A Painted Create Date By Whom mm/dd/yyyy Firstname Lastname Mod. Date By Whom 0 1 2 3 4 5 6 7 8 9 Create Date Sheet Name 0 1 2 1 2 B B 9 3 4 5 6 7 8 9 0 3 4 5 6 7 8 9 -0 1 Ready mm/dd/yyyy B Paradise RESORT & SPA NewBusinesses LoanAnalysis Description Mod. Description Creator RoomScheduling Documentation + D Workbook Name e05Hotel Expansion.xlsx Last Version Backup Name Purpose E F E9 1 2 3 Revenue 234 4 5 1560700 A X 8 9 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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 1 B Painted Paradise Resort & Spa Solver for Room Scheduling Capacity 500 $ 100 $ 25 $ 250 $ 125 $ $ Room The Muisca Room The Eldorado Room The Pueblo Room New Room 1 New Room 2 Utilities Room Maintenance Staff Salaries per event $ $ $ $ $ $ is 280.00 170.00 450.00 550.00 2,750.00 3,200.00 11,800.00 Price D 7,500.00 1,500.00 375.00 3,750.00 1,875.00 15,000.00 $ Saved Solver Models NewBusinesses LoanAnalysis RoomScheduling Documentation E Events per Month 1 1 1 1 1 + F G