Spreadsheet Applications 2022 Milestone Activity Hotel Management

You have reached a Milestone Activity! Please be sure you complete the Personalized Lesson above before continuing.

Milestone Activities are important stops along your educational journey to check your progress towards mastering the competency and to get feedback and guidance from your tutorial faculty. Please note: You must complete this Milestone Activity before moving on in this competency.

Milestones ask questions that touch upon concepts covered in previous Learning Activities and provide you with the opportunity to see concepts from the perspective of experts in the field, your tutorial faculty. This Milestone Activity is not graded and will not impact your score on your Final Assessment; however, your response must score a “Proficient” or “Exemplary” on each criterion, listed in the rubric below, in order to access and submit your Final Assessment.

To complete this Milestone Activity, read the question below and then provide your response in the comment box. In order to score a “Proficient” or “Exemplary”, your response must be thoughtful and substantive, and you must reference or cite content from this competency. Your tutorial faculty will use the rubric to determine if your post shows the depth of thought required to successfully complete this Milestone. Let’s get started!

The hotel management industry presents a wide variety of career opportunities. These range from running a bed and breakfast to a management position at a large hotel. No matter what hotel management career you choose to pursue, understanding hotel occupancy and costs are critical to running a successful operation. This exercise examines the occupancy rate and expenses of a small hotel.

There are three worksheets in the workbook for this assignment.

·        Occupancy – calculates and displays the maximum hotel capacity for each month (based on the number of rooms, the capacity of each room, and the number of days in the specific month), the actual occupancy (how many actually stayed in the hotel that month), and the occupancy percentage (what percentage of capacity was the hotel each month).

·        Statistics – calculates and displays the highest, lowest, and average actual occupancy and occupancy percentages from the Occupancy worksheet.

·        Shuttle Purchase – calculates three different down payment options for a loan to purchase a shuttle for the hotel.

Occupancy Worksheet

1.     Open the file named SC2 Data and then Save As [name]SC2 Hotel. (Example: DobbsSC2 Hotel)

2.     Switch to the Occupancy worksheet if needed.

3.     Replace the [Insert Year] in A1 with the year number for last year.

4.     You need to calculate the January capacity for the hotel in C5. The capacity shows how many people the hotel can hold during the month. It is calculated by first multiplying the occupants per room by the number of rooms in the hotel. This result is then multiplied by the number of days in the month (cell B5 for January). Create this formula using absolute references so that the appropriate cells do not change when the formula is pasted throughout column C. Hint: two of the cells in the formula need to be absolute references.

5.     Copy the formula in cell C5 and paste it into the range C6:C16. Use a paste method that does not remove the border at the bottom of cell C16.

6.     Format the numbers in columns C and D for comma format with zero decimal places.

7.     In cell C17, enter a function that finds the sum of the monthly hotel capacity values.  Do the same in cell D17 to find the sum of the monthly actual occupancy values.

8.     Enter a formula in cell E5 to calculate the Percent Occupied of the hotel (this statistic shows what percentage of the hotel is full or occupied). Your formula should divide the Actual Occupancy by the Hotel Capacity. Then copy and paste the formula into the range E6:E17. Use a paste method that does not remove the borders at the bottom of cell E16 and E17. Format the results in E5:E17 as percentages with two decimal places.

9.     Format the Totals (C17:E17) as bold.

10.  Apply any number formatting that aids in the readability and professionalism of the worksheet.

11.  Check Print Preview and make any changes necessary for professional printing.

Statistics Worksheet

1.     Replace the [Insert Year] in A1 with the year number for last year.

2.     Enter a function in cell B3 that finds the highest value in the Actual Occupancy column from the Occupancy worksheet.

3.     Enter a function in cell B4 that finds the lowest value in the Actual Occupancy column from the Occupancy worksheet.

4.     Enter a function in cell B5 that shows the average value of the Actual Occupancy column on the Occupancy worksheet.

5.     Use the Auto Fill handle to copy the formulas in the range B3:B5 to the range C3:C5.

6.     Apply any number formatting that aids in the readability and professionalism of the worksheet. The numbers should be formatted similarly to the Occupancy worksheet.

7.     Check Print Preview and make any changes necessary for professional printing.

Shuttle Purchase Worksheet

The hotel is considering buying a car to shuttle customers to and from the airport.  You need to decide how much of a down payment to make, so you are going to calculate the monthly payment based on three different down payment percentages.  The number of years to pay off the loan will vary for each of the down payment percentage options. Remember, the down payment amount is found by multiplying the price of the car by the down payment percentage. This amount is then subtracted from the price of the car to find the amount of the loan.

1.     In cell B5 write a formula that will calculate the amount of the down payment. Be sure to use cell references as much as possible. Copy the formula to the other down payment options.

2.     In cell B6 write a formula to calculate the amount of the loan. Be sure to use cell references as much as possible. Copy the formula to the other down payment options.

3.     In cell B9 create a PMT function to calculate the monthly payment. Make sure the arguments in the PMT function are converted into months and that the monthly payment is a positive number. Be sure to use cell references as much as possible. Copy the function to the other down payment options.

4.     In cell B10 create a formula that calculates how much will be paid in total for the vehicle, including the down payment and the total amount paid on the loan in the given number of years.  Copy the formula to the other down payment options.

5.     In cell A12, write an explanation of which down payment option is the best and why.

6.     Apply any number formatting that aids in the readability and professionalism of the worksheet.

7.     Make the following page setup changes to the worksheet:

o   Center horizontally on the page

o   Create a footer with the date on the left and the file name on the right.  Make sure that both the date and the file name will update automatically.

8.     Check the spelling on all of the worksheets and make any necessary changes.

9.     Save the workbook and submit the workbook as directed by your instructor.

PLEASE SUBMIT THE SPEADSHEET BACK AS AN ATTACHMENT

• 1683200465-SC2-Data.xlsx

SmartExpert

(118)

Status NEW Posted 04 May 2023 11:05 AM My Price 15.00

Spr-----------ead-----------she-----------et -----------App-----------lic-----------ati-----------ons----------- 20-----------22 -----------Mil-----------est-----------one----------- Ac-----------tiv-----------ity----------- Ho-----------tel----------- Ma-----------nag-----------eme-----------nt-----------

Attachments

file 1683200736-Answer SC2 Milestone Activity Hotel Management.xlsx.xlsx preview (34 words )

Mo-----------nth-----------Day-----------s i-----------n M-----------ont-----------hJa-----------nua-----------ryF-----------ebr-----------uar-----------yMa-----------rch-----------Apr-----------ilM-----------ayJ-----------une-----------Jul-----------yAu-----------gus-----------tSe-----------pte-----------mbe-----------rOc-----------tob-----------erN-----------ove-----------mbe-----------rDe-----------cem-----------ber-----------Num-----------ber----------- of----------- Ro-----------oms-----------Hot-----------el -----------Cap-----------aci-----------tyA-----------ctu-----------al -----------Occ-----------upa-----------ncy-----------Tot-----------alO-----------ccu-----------pan-----------ts -----------per----------- Ro-----------omA-----------ver-----------age-----------Mon-----------thl-----------y P-----------aym-----------ent-----------Pri-----------ce -----------of -----------Car-----------Int-----------ere-----------st -----------Rat-----------e (-----------per----------- ye-----------ar)-----------Ter-----------ms -----------of -----------Loa-----------n (-----------yea-----------rs)-----------Per-----------cen-----------t O-----------ccu-----------pie-----------dLo-----------an -----------Amo-----------unt-----------Tot-----------al -----------Pai-----------dPe-----------rce-----------nt -----------Dow-----------n P-----------aym-----------ent-----------Hot-----------el -----------Shu-----------ttl-----------e P-----------urc-----------has-----------e O-----------pti-----------ons-----------Hig-----------hes-----------tLo-----------wes-----------tDo-----------wn -----------Pay-----------men-----------t A-----------mou-----------ntO-----------ccu-----------pan-----------cy -----------Sta-----------tis-----------tic-----------s f-----------or -----------[20-----------22]-----------Hot-----------el -----------Occ-----------upa-----------ncy----------- fo-----------r [-----------202-----------2] -----------

Not Rated(0)

• Buy Answer