L12 & L13 needs formula

Complete the Cost Analytics exercise (Chapter 4 DA Exercise 2) before answering this question. Change the Plan Occupancy Days in cell K12 to 2500. In the space below, enter the Estimated Cleaning Expenses that appears in cell L13. Meats ef Bata 12 Cost analytics is a critical activity that helps businesses understand and estimate costs that are associated with day-to-day operations. This exercise examines the relationship between cleaning expenses and the occupancy rate of a small hotel. Cleaning expenses are mostly influenced by the occupancy rate of the hotel. As more rooms need to be cleaned, cleaning expenses will increase. To accurately estimate these expenses, the behavior of the cost must be identified in terms of fixed, variable, or mixed. A fixed cost does not change no matter how many rooms need to be cleaned. For example, if the cleaning staff is paid a fixed salary, it does not matter if they clean 1 room or 100 rooms, their salary will remain the same. A variable cost is when the cost per unit does not change no matter how many rooms are cleaned. In order to calculate the total variable cost, the cost per unit must be multiplied by the total activity, which in this case is the number of rooms that are cleaned. For example, assume that when a room is cleaned, a new bottle of shampoo is placed in the bathroom. If the shampoo cost is $1.00 per bottle, it does not matter if 1 room is cleaned or 100 rooms are cleaned, the cost per bottle will still be $1.00. However, the total cost will be $100.00 if 100 rooms are cleaned versus $1.00 if 1 room is cleaned. A mixed cost is where the total cost is comprised of both a fixed and a variable cost. This exercise will demonstrate how mixed costs can be estimated through a technique called the high-low method. The project will use one year of monthly occupancy days and cleaning expenses. Additional years of data will be added to continue the process of analyzing costs in order to estimate what the cleaning expenses will be at various levels of room occupancy days. Therefore, additional rows of data will be added to the workbook, but there will be no additional columns added. The workbook contains cost data from 2019, when the total cleaning expense for the year was $88,295 and the total occupancy days were 45,925 . Begin the exercise by opening the file named Chapter 4 DA Exercise 2. Questions that are preceded with the letters KO indicate you must only use your keyboard and not your mouse to execute the required skill. 1. Enter a function in cell E3 that shows the total of adding the values in the range E5:E16. Use the output of this function to audit the data versus what was provided in the description of this exercise. 2. Enter a function in cell G3 that shows the total of adding the values in the range G5:G16. Use the output of this function to audit the data versus what was stated in the description of this exerclse. 3. Enter a function in cell K17 that will count the number of months in the range B5:B17. Since the data in the worksheet should be for one year, verify that the output of the function makes sense. Also note that one blank cell, B17, is included in this range. 4. Enter a formula in cell D5 to calculate the January capacity for the hotel. The capacity is calculated by multiplying the occupants per room (cell C3) by the number of rooms in the hotel (cell C2). This result is then multiplied by the number of days in the month (cell C5). Construct this formula so that relative referencing does not change cells C3 and C2 when the formula is pasted into other cell locations. 5. Copy the formula in cell D5 and paste it into the range D6:D16. Use a paste method that does not change the border line style in column D. 6. Enter a formula in cell F5 to calculate the occupancy rate of the hotel. The formula should divide the Actual Occupancy by the Hotel Capacity. Format your result to a percentage with two decimal places. 7. Copy cell F5 and paste it into the range F6:F16. Use a paste method that uses the number format applied to cell F5 but does not change the border style in column F. 8. Enter a function in cell K5 that shows the highest value for the Cleaning Expenses in the range G5:G16. 9. KO Copy the function in cell K5 and paste it into cell K6 so the range used in the function does not change. Then modify the function so the output shows the lowest value for the Cleaning Expenses in the range G5:G16. 10. Enter a function in cell L5 that shows the highest value for the Actual Occupancy in the range E5:E16. 11. Copy the function in cell L5 and paste it into cell L6 so the range used in the function does not change. Then modify the function so the output shows the lowest value in the Cleaning Expenses column. 12. Enter a formula in cell K7 that subtracts the lowest Cleaning Expenses value from the highest Cleaning Expenses value. 13. Copy cell K7 and paste it into cell L7 such that only the formula is pasted without any of the formats from celi K7. 14. Enter a formula in cell L9 that divides the Cleaning Expenses High Low Difference (cell KT) by the Actual Occupancy High Low Ditference (cell L7). The output of this formula shows the variable cost per occupancy day portion of the cleaning expenses por month. As mentioned in the description for this exercise, the cleaning expense is a mixed cost, which means it is comprised of both a variable and fixnd cost. 15. Enter a formula in cell L10 that calculates the fixed cost portion for the cleaning expenses per month. Since we have calculated the variable cost per occupancy day, we can use it to calculate the fixed expense. First calculate the total variable expense by multiplying the variable expense per occupancy day in cell L9 by the High Actual Occupancy days in cell L5. Then, subtract this amount from the High Cleaning Expense in cell K5. Hint: You will have to put the calculation for the total variable expense in parentheses. 16. Enter the number 3500 in cell K12. Notice this cell is shaded yellow. Shading a cell is sometimes used to indicate a value that needs to be entered in order to produce an output. In this case, the value of 3,500 will be used to estimate the cleaning expenses. 17. Enter a formula in cell L13 that calculates the estimated cleaning expenses given the number that was entered into cell K12. Since the variable cost per occupancy day and the fixed cost is calculated in cells L and L10, these results can be used to estimate the cleaning expenses. The formula to estimate this mixed cost can be expressed as: Y=A+Bx where Y=C leaning Expense, A is the fixed cost, B is the variable cost per occupancy day, and x is the number of occupancy days. The result of multiplying B times x is the total variable cost. 18. The output of the formula used to calculate the estimated cleaning expenses in cell L13 should be $6,900. Type the value 4800 in cell K12. You should see the cleaning estimate change to $8,720. 19. Type the following into the merged cell beginning with cell I16: "Data added after row 16 is not included in this analysis". This alert will be used as part of a data internal control feature that will appear if additional data is added to the worksheet. 20. Change the font color in the merged cell beginning with I16 to white. It will appear as if the text has disappeared. 21. With the merged cell beginning with I16 still activated, click the Conditional Formatting button in the Home tab of the Ribbon. 22. Click the New Rule option from the dropdown menu. Set a conditional format rule based on the results of the following formula: =K17>12. If the value in cell K17 is greater than 12 , then additional data has been added to the worksheet that will not be included in the outputs of the formulas and functions. 23. Select the bold and red font color for the conditional formats. 24. Type the word "January" in cell B17. This should trigger the conditional format and the alert should now be seen. This serves as a data internal control so the person using this worksheet will know that the formulas and functions need to be adjusted to account for the new data. 25. Save and close the workbook. Complete the Cost Analytics exercise (Chapter 4 DA Exercise 2) before answering this question. Change the Plan Occupancy Days in cell K12 to 2500. In the space below, enter the Estimated Cleaning Expenses that appears in cell L13. Meats ef Bata 12 Cost analytics is a critical activity that helps businesses understand and estimate costs that are associated with day-to-day operations. This exercise examines the relationship between cleaning expenses and the occupancy rate of a small hotel. Cleaning expenses are mostly influenced by the occupancy rate of the hotel. As more rooms need to be cleaned, cleaning expenses will increase. To accurately estimate these expenses, the behavior of the cost must be identified in terms of fixed, variable, or mixed. A fixed cost does not change no matter how many rooms need to be cleaned. For example, if the cleaning staff is paid a fixed salary, it does not matter if they clean 1 room or 100 rooms, their salary will remain the same. A variable cost is when the cost per unit does not change no matter how many rooms are cleaned. In order to calculate the total variable cost, the cost per unit must be multiplied by the total activity, which in this case is the number of rooms that are cleaned. For example, assume that when a room is cleaned, a new bottle of shampoo is placed in the bathroom. If the shampoo cost is $1.00 per bottle, it does not matter if 1 room is cleaned or 100 rooms are cleaned, the cost per bottle will still be $1.00. However, the total cost will be $100.00 if 100 rooms are cleaned versus $1.00 if 1 room is cleaned. A mixed cost is where the total cost is comprised of both a fixed and a variable cost. This exercise will demonstrate how mixed costs can be estimated through a technique called the high-low method. The project will use one year of monthly occupancy days and cleaning expenses. Additional years of data will be added to continue the process of analyzing costs in order to estimate what the cleaning expenses will be at various levels of room occupancy days. Therefore, additional rows of data will be added to the workbook, but there will be no additional columns added. The workbook contains cost data from 2019, when the total cleaning expense for the year was $88,295 and the total occupancy days were 45,925 . Begin the exercise by opening the file named Chapter 4 DA Exercise 2. Questions that are preceded with the letters KO indicate you must only use your keyboard and not your mouse to execute the required skill. 1. Enter a function in cell E3 that shows the total of adding the values in the range E5:E16. Use the output of this function to audit the data versus what was provided in the description of this exercise. 2. Enter a function in cell G3 that shows the total of adding the values in the range G5:G16. Use the output of this function to audit the data versus what was stated in the description of this exerclse. 3. Enter a function in cell K17 that will count the number of months in the range B5:B17. Since the data in the worksheet should be for one year, verify that the output of the function makes sense. Also note that one blank cell, B17, is included in this range. 4. Enter a formula in cell D5 to calculate the January capacity for the hotel. The capacity is calculated by multiplying the occupants per room (cell C3) by the number of rooms in the hotel (cell C2). This result is then multiplied by the number of days in the month (cell C5). Construct this formula so that relative referencing does not change cells C3 and C2 when the formula is pasted into other cell locations. 5. Copy the formula in cell D5 and paste it into the range D6:D16. Use a paste method that does not change the border line style in column D. 6. Enter a formula in cell F5 to calculate the occupancy rate of the hotel. The formula should divide the Actual Occupancy by the Hotel Capacity. Format your result to a percentage with two decimal places. 7. Copy cell F5 and paste it into the range F6:F16. Use a paste method that uses the number format applied to cell F5 but does not change the border style in column F. 8. Enter a function in cell K5 that shows the highest value for the Cleaning Expenses in the range G5:G16. 9. KO Copy the function in cell K5 and paste it into cell K6 so the range used in the function does not change. Then modify the function so the output shows the lowest value for the Cleaning Expenses in the range G5:G16. 10. Enter a function in cell L5 that shows the highest value for the Actual Occupancy in the range E5:E16. 11. Copy the function in cell L5 and paste it into cell L6 so the range used in the function does not change. Then modify the function so the output shows the lowest value in the Cleaning Expenses column. 12. Enter a formula in cell K7 that subtracts the lowest Cleaning Expenses value from the highest Cleaning Expenses value. 13. Copy cell K7 and paste it into cell L7 such that only the formula is pasted without any of the formats from celi K7. 14. Enter a formula in cell L9 that divides the Cleaning Expenses High Low Difference (cell KT) by the Actual Occupancy High Low Ditference (cell L7). The output of this formula shows the variable cost per occupancy day portion of the cleaning expenses por month. As mentioned in the description for this exercise, the cleaning expense is a mixed cost, which means it is comprised of both a variable and fixnd cost. 15. Enter a formula in cell L10 that calculates the fixed cost portion for the cleaning expenses per month. Since we have calculated the variable cost per occupancy day, we can use it to calculate the fixed expense. First calculate the total variable expense by multiplying the variable expense per occupancy day in cell L9 by the High Actual Occupancy days in cell L5. Then, subtract this amount from the High Cleaning Expense in cell K5. Hint: You will have to put the calculation for the total variable expense in parentheses. 16. Enter the number 3500 in cell K12. Notice this cell is shaded yellow. Shading a cell is sometimes used to indicate a value that needs to be entered in order to produce an output. In this case, the value of 3,500 will be used to estimate the cleaning expenses. 17. Enter a formula in cell L13 that calculates the estimated cleaning expenses given the number that was entered into cell K12. Since the variable cost per occupancy day and the fixed cost is calculated in cells L and L10, these results can be used to estimate the cleaning expenses. The formula to estimate this mixed cost can be expressed as: Y=A+Bx where Y=C leaning Expense, A is the fixed cost, B is the variable cost per occupancy day, and x is the number of occupancy days. The result of multiplying B times x is the total variable cost. 18. The output of the formula used to calculate the estimated cleaning expenses in cell L13 should be $6,900. Type the value 4800 in cell K12. You should see the cleaning estimate change to $8,720. 19. Type the following into the merged cell beginning with cell I16: "Data added after row 16 is not included in this analysis". This alert will be used as part of a data internal control feature that will appear if additional data is added to the worksheet. 20. Change the font color in the merged cell beginning with I16 to white. It will appear as if the text has disappeared. 21. With the merged cell beginning with I16 still activated, click the Conditional Formatting button in the Home tab of the Ribbon. 22. Click the New Rule option from the dropdown menu. Set a conditional format rule based on the results of the following formula: =K17>12. If the value in cell K17 is greater than 12 , then additional data has been added to the worksheet that will not be included in the outputs of the formulas and functions. 23. Select the bold and red font color for the conditional formats. 24. Type the word "January" in cell B17. This should trigger the conditional format and the alert should now be seen. This serves as a data internal control so the person using this worksheet will know that the formulas and functions need to be adjusted to account for the new data. 25. Save and close the workbook