YO$22\_$Excel$\_$Ch$03\_$PS$1\_$Renovations

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.

Steps to Perform:

Step Instructions Points Possible

$1$ Start Excel. Download and open the file named Excel$\_$Ch$03\_$PS$1\_$Renovations.xlsx$.$ Grader has automatically added your last name to the beginning of the filename. Save the file to a location where you are storing your files. $0$

$2$ You have been asked to determine how long the renovation of each room will take in days. You have decided to use a DATEDIF function to perform this calculation.

On the Renovations worksheet, in cell D$6,$ enter a DATEDIF function based on the renovations started and renovations completed dates to determine the length in months of the expected renovation for the Capitol Ballroom.

Copy this formula through cell D$10.$

$10$

$3$ In cell G$6,$ enter a formula to determine the room capacity that the Capitol Ballroom will hold. The new room capacity is based on the original capacity in cell F$6$ multiplied by the increase in capacity percentage in cell B$13.$ Be sure to calculate the entire increase in capacity, not just the percentage increase. Use absolute references where necessary. Copy the formula though cell G$10.10$

$4$ Using the Name Box, select the named range IncreaseInRevenue. You notice the named range refers to a blank cell. Therefore, you want to correct the named range.

Modify the named range IncreaseInRevenue to reference cell B$14$ not cell B$15.2$

$5$ After renovations, each room will have more capacity. You want to determine the projected revenue after the renovations have been completed.

In cell I$6,$ using the named range in cell B$14,$ enter a formula to determine the projected increase in revenue for the Capitol Ballroom based on the revenue figure in cell H$6$ and the increase in revenue percentage in cell B$14.$ Be sure to calculate the entire increase in capacity, not just the percentage increase.

Copy this formula through cell I$10.10$

$6$ Next, Zophia has asked you to determine the monthly payments based on the renovations loan amount.

In cell B$20,$ enter a function that will calculate the monthly payment for the loan amount in cell B$17,$ based on the Interest Rate in B$18,$ and the number of years in the Term in cell B$19.$ Display the monthly payment as a positive value. $10$

$7$ Assign the named range RoomClassification to cell range A$23$:B$26.$

$5$

$8$ Based on the projected revenue in column I, you want to determine each room's projected room classification.

In cell J$6,$ using a lookup function, enter a formula to determine the projected room classification based on the projected quarterly revenue after renovations in cell I$6.$ Use the named range RoomClassification when entering this formula.

Copy this formula through cell J$10.$

$10$

$9$ Zophia has asked you to analyze the sales per banquet room.

On the RoomAnalysis worksheet, in cell G$7,$ determine the total charges for the Capitol Ballroom by using a SUMIF function. Use absolute references where appropriate. Copy this formula through cell G$11.10$

$10$ Zophia also wants to know the number of times each room was used in the month of January.

In cell G$16,$ determine the total count of the Capitol Ballroom usage by using a COUNTIF function. Use absolute references where appropriate. Copy this formula through cell G$20.10$

$11$ On the RoomCommissions worksheet, in cell C$4,$ determine the potential commission of the Capitol Ballroom based on the commission rates in cell range B$4$:B$8$ and the potential sales in cell range C$3$:G$3.$ Use mixed referencing.

Copy the formula through cell G$8.10$

$12$ In cell C$14,$ use an array formula to determine the potential commission of the Grand Ballroom based on the commission rates in cell range B$14$:B$18$ and the potential sales in C$13$:G$13.10$

$13$ Add the File Name code in the left footer of the Renovations and RoomAnalysis worksheets.

$3$

$14$ Save and close Excel$\_$Ch$03\_$PS$1\_$Renovations.xlsx$.$ Exit Excel. Submit the file as directed. $0$

Total Points $100$