For Project 2 Part 1 Complete And Submit The Second Careers

For Project 2 Part 1 Complete And Submit The Second Careers In Practi

Complete and submit the second Careers in Practice (Skills Review) Hotel Management Cost Analysis starting on page 180. Note that on page 181, the number of days in the month is in cell B5 (not C5), and on page 182, for items 9 and 10, you are to enter formulas to calculate the highest and lowest Cleaning Expenses (not the Actual Occupancy again). Submit your completed Excel file here. For cells E6:E16, use a paste method that does not remove the border at the bottom of cell E16.

In the Historical Costs worksheet, enter a SUM function in cell C17 that sums C5:C16. Copy and paste this formula into D17 and F17, preserving cell borders. Copy the formula in E16 and paste it into E17, maintaining the bottom border. Sort the data based on Actual Occupancy in descending order; for ties, sort by Cleaning Expenses in descending order.

On the Cost Analysis worksheet, insert formulas into B3 and B4 to find the highest and lowest values in range D5:D16 (Actual Occupancy on Historical Costs). Similarly, insert formulas into C3 and C4 for the highest and lowest in range F5:F16 (Cleaning Expenses). Format B3 and B4 with commas and no decimals; format C3 and C4 as US dollars with zero decimals.

In cell B5, subtract the lowest value from the highest Actual Occupancy; copy this formula to C5. In C6, calculate the variable cost portion for Cleaning Expenses as the difference between high and low Cleaning Expenses divided by the difference in Actual Occupancy (cells B5 and C5). Format this as US dollars with two decimals.

In C7, compute the fixed cost portion for Cleaning Expenses by subtracting the product of C6 and the high Actual Occupancy (B3) from the high Cleaning Expenses (C3). Format as US dollars with zero decimals. Enter 3500 in C2 on the Cleaning Cost Estimates worksheet, format with commas and zero decimals, and fill the cell with yellow to indicate input required.

In C3 of the Cleaning Cost Estimates worksheet, enter a formula to estimate cleaning expenses as fixed cost plus variable cost times the activity level in C2. Format this as US dollars with zero decimals. Save the workbook with your name prepended to the filename as instructed.

Paper For Above instruction

The objective of this project is to perform a comprehensive hotel management cost analysis by analyzing historical costs, calculating variable and fixed expenses, and estimating future cleaning expenses based on occupancy data. This exercise involves advanced Excel skills such as formulas, data sorting, and formatting, to derive meaningful insights for effective cost control and management.

Initially, the data in the Historical Costs worksheet is to be consolidated by summing monthly expenses from cells C5 through C16 into cell C17. This summation provides an overall view of costs over a given period. The same formula is then replicated across adjacent columns D17 and F17 to maintain consistency in cost analysis across different expense categories. Copying formulas while preserving cell borders ensures the worksheet remains clean and visually organized.

Following this, the dataset must be sorted by Actual Occupancy in descending order, highlighting the months with the highest usage. Handling ties by sorting on Cleaning Expenses provides a more nuanced view of operational costs relative to occupancy. This sorted data will inform subsequent calculations on the Cost Analysis worksheet, focusing on identifying cost extremes.

The next step involves determining the highest and lowest values of Actual Occupancy through the formulas in B3 and B4, referencing the relevant range on the Historical Costs worksheet. Similarly, the highest and lowest Cleaning Expenses are calculated in C3 and C4. Proper formatting, such as commas and currency symbols, enhances clarity when presenting these figures, facilitating quick interpretation.

The difference between the maximum and minimum Actual Occupancy is calculated in B5 and C5 to measure the range of occupancy fluctuations. This metric serves as a basis for estimating the variable component of cleaning costs. The variable cost per unit is derived by dividing the range of Cleaning Expenses by the range of occupancy, reflecting how costs increase with activity levels. This value is formatted for precision and clarity.

Subsequently, the fixed cost component is estimated by subtracting the total variable costs at maximum occupancy from the highest Cleaning Expenses, isolating the baseline expense committed regardless of occupancy. This fixed cost estimate helps in budgeting and cost control strategies.

The Cleaning Cost Estimates worksheet begins with a cell (C2) prefilled with an activity level, in this case, 3500, formatted appropriately and highlighted for user input. The total estimated cleaning expenses are then calculated in C3 using a formula that combines fixed and variable costs based on the activity level entered by the user. Proper formatting ensures the output aligns with standard financial reporting practices.

Finally, the complete workbook is saved with the user's name prefixed to the filename, ensuring proper version control and identification. This comprehensive analysis enables hotel management to understand cost behavior patterns and make informed decisions to optimize operational efficiency.

References

• Garrison, R. H., Noreen, E. W., & Brewer, P. C. (2021). Managerial Accounting (16th ed.). McGraw-Hill Education.
• Hilton, R. W., & Platt, D. (2019). Managerial Accounting: Creating Value in a Dynamic Business Environment (11th ed.). McGraw-Hill Education.
• Wild, J. J., Subramanyam, K. R., & Halsey, R. F. (2020). Financial Statement Analysis (12th ed.). McGraw-Hill Education.
• Horngren, C. T., Datar, S. M., & Rajan, M. (2022). Cost Accounting: A Managerial Emphasis (16th ed.). Pearson.
• Reeve, J., & Warren, C. S. (2020). Financial & Managerial Accounting (14th ed.). Cengage Learning.
• Fraser, L., & Simons, K. (2019). Leadership and Cost Control in Hospitality. Journal of Hospitality Financial Management, 27(3), 245-260.
• Burns, P., & Choi, Y. (2018). Cost Behavior and Cost Management in Service Industries. International Journal of Hospitality Management, 75, 122-130.
• Harrington, R. J., & Ottenbacher, M. (2018). Hospitality Financial Management: An Overview. Cornell Hospitality Quarterly, 59(4), 356-365.
• Williams, S., & Schaefer, S. (2021). Practical Cost Control Strategies in Hotel Operations. Tourism & Hospitality Research, 21(2), 163-175.
• Li, H., & Zhang, X. (2020). Impact of Cost Management on Hotel Profitability. Journal of Tourism Economics, 26(3), 345-359.