Project Description: Zophia Symanski, general manager of The Governor's Hotel, has created a worksheet to keep track of the renovations being made to the three event rooms that are used for conferences and the two technology centers that are used for computer. based presentations and interactive sessions. The technology centers are equipped with workstations, are networked, and have Internet accessibility. This spreadsheet is simple and designed only for Zophia to keep a handle on the renovation progress and estimate the increase in capacity and revenue that might occur because of the renovations. Zophia also wants you to analyze the banquet room sales and usage for the month of January as well as potential commissions for the Capitol and Grand Ballrooms. 5 After renovations, each room will have more capacity. You want to determine the projected 10 revenue after the renovations have been completed In cell 16, using the named range in cell B14, enter a formala to delermine the projected increase in revenue for the Capitol Ballroom based on the revenue figure in celt H6 and the increase in revenue percentage in cell B14. Be sure to calculate the entire increase in capacity, not just the percentage increase. Copy this formula through cell I110. 6 Next, Zophia has asked you to determine the monthly payments based on the renovations: 10 loan amount. In cell B20, enter a function that will calculate the monthly payment for the loan amount in cell: 817, based on the Interest Rate in B18, and the number of years in the Term in cell 819 . Display the monthly payment as a poritive value. 7 Assign the named range RoomCiassification to cell range A23:B26. 5 Created On: 0323/2022 1 Y022.Excal_CH63_PS1 - Renovaticns 1.0 Grader-Instructions: Excel 2022 Projict. Step Instructions Points Possible 8 Based on the projected revenue in column I, you want to determine each room's projected 10 room classification. In cell J6, using a lookup function, enter a formula to determine the projectegd room classification based on the projected quarterly revenue after renovations in cell 16. Use the named range Room Classification when entering this formula. Copy this formiula through cell J10 9 Zophia has asked you to analyze the sales per banquet room. 10 On the RoomAnalysis worksheet, in cell G7, determine the total charges for the Capitol Baliroom by using a SUMIF function. Use absolute references where appropriate. Copy this formula through cell G11 10. Zophia also wants to know the number of times each room was used in the month of January. 10 In cell G16, determine the total count of the Capitol Ballroom usage by using a COUNTIF function. Use absolute references where appropriate. Copy this formula through cell G20. 11 On the RoomCommissions worksheet, in cell C4, determine the potential commission of the 10 Capitol Ballroom based on the commission rates in cell range B4:B8 and the potential sales in cell range C3.G3. Use mixed referencing Copy the formula through cell G8. 12. In cell C14, use an array formula to determine the potential commission of the Grand Ballroom 10 based on the commission rates in cell range B14.B18 and the potential sales in C13.G13 13 Add the File Name code in the left footer of the Renovations and RoomAnalysis worksheets. 14 Save and close Excel Ch03 PSt Renovations.xlsx. Exit Excel. Submit the file as directed. 0 Total Points 100 Canitol Ballroom Commissions Analysis Grand Ballroom Commissions Analysis Project Description: Zophia Symanski, general manager of The Governor's Hotel, has created a worksheet to keep track of the renovations being made to the three event rooms that are used for conferences and the two technology centers that are used for computer. based presentations and interactive sessions. The technology centers are equipped with workstations, are networked, and have Internet accessibility. This spreadsheet is simple and designed only for Zophia to keep a handle on the renovation progress and estimate the increase in capacity and revenue that might occur because of the renovations. Zophia also wants you to analyze the banquet room sales and usage for the month of January as well as potential commissions for the Capitol and Grand Ballrooms. 5 After renovations, each room will have more capacity. You want to determine the projected 10 revenue after the renovations have been completed In cell 16, using the named range in cell B14, enter a formala to delermine the projected increase in revenue for the Capitol Ballroom based on the revenue figure in celt H6 and the increase in revenue percentage in cell B14. Be sure to calculate the entire increase in capacity, not just the percentage increase. Copy this formula through cell I110. 6 Next, Zophia has asked you to determine the monthly payments based on the renovations: 10 loan amount. In cell B20, enter a function that will calculate the monthly payment for the loan amount in cell: 817, based on the Interest Rate in B18, and the number of years in the Term in cell 819 . Display the monthly payment as a poritive value. 7 Assign the named range RoomCiassification to cell range A23:B26. 5 Created On: 0323/2022 1 Y022.Excal_CH63_PS1 - Renovaticns 1.0 Grader-Instructions: Excel 2022 Projict. Step Instructions Points Possible 8 Based on the projected revenue in column I, you want to determine each room's projected 10 room classification. In cell J6, using a lookup function, enter a formula to determine the projectegd room classification based on the projected quarterly revenue after renovations in cell 16. Use the named range Room Classification when entering this formula. Copy this formiula through cell J10 9 Zophia has asked you to analyze the sales per banquet room. 10 On the RoomAnalysis worksheet, in cell G7, determine the total charges for the Capitol Baliroom by using a SUMIF function. Use absolute references where appropriate. Copy this formula through cell G11 10. Zophia also wants to know the number of times each room was used in the month of January. 10 In cell G16, determine the total count of the Capitol Ballroom usage by using a COUNTIF function. Use absolute references where appropriate. Copy this formula through cell G20. 11 On the RoomCommissions worksheet, in cell C4, determine the potential commission of the 10 Capitol Ballroom based on the commission rates in cell range B4:B8 and the potential sales in cell range C3.G3. Use mixed referencing Copy the formula through cell G8. 12. In cell C14, use an array formula to determine the potential commission of the Grand Ballroom 10 based on the commission rates in cell range B14.B18 and the potential sales in C13.G13 13 Add the File Name code in the left footer of the Renovations and RoomAnalysis worksheets. 14 Save and close Excel Ch03 PSt Renovations.xlsx. Exit Excel. Submit the file as directed. 0 Total Points 100 Canitol Ballroom Commissions Analysis Grand Ballroom Commissions Analysis
