SWIFTIE HOTEL EXCEL PROJECT (20 POINTS) Swiftie Hotel is a family-run Hotel in Nashville, Tennessee, situated near Grand Ole Opry. The Hotel went bankrupt in 2020 and was purchased by Taylor and Travis (T&T) for $3,500,000. To finance the purchase, the T&T took a loan of $1,500,000 from Small Business Administration at 3.25% annual interest with a payback period of 15 years. T&T also took out a loan of $2,000,000 from a bank at 4.25% annual interest rate to be paid back over a 25 year period. Since buying the Hotel, the T&T have often found themselves short of cash for paying the bills. They have realized a need to develop a plan for managing their cash flow. Develop a spreadsheet that will help T&T forecast their monthly cash flow in a 12 month planning horizon. They will use this spreadsheet to identify the months when they will not have enough money to pay all their bills. Cash flow forecast for a period shows projected cash receipts and payments and subtracts payment from receipts. The resulting amount (could be positive or negative) for a given month is added to the beginning cash for that month to get the ending cash balance for the same month. The ending cash balance for a given period becomes the beginning cash on hand for the next period. Negative ending cash balance for a given month means that the T&T will not have enough money to pay all their bills due at the end of that month. The T&T have determined the Hotel's occupancy rate (percentage of the rooms occupied) for various months of the year. The occupancy rate is highest during the winter months and spring break, when visitors from all over the world come to Nashville to enjoy live music concerts. At daily room rate of $85 per room, the estimated occupancy rate is given below. Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Occupancy rate 75% 45% 75% 40% 35% 40% 65% 65% 45% 50% 65% 77% The electricity usage comprises of two parts: (1) a fixed amount of 200 kWhs a day for lighting the parking lot and the Hotel signs, and providing lighting and heating/cooling to the hallway and the front lobby when the occupancy is below 55% and fixed amount of 300 kWhs a day when the occupancy is 55% or higher and (2) 25 kWhs per room per day when the room is occupied and 1 kWh per room per day when it is unoccupied. Use an IF function to estimate the electricity consumption. The monthly charges for the electric bill are fixed amount of $500 plus 10 cents per kWh for the first 4500 kWhs and 8 cents per kWh for units beyond 4500 kWhs. So, if during the month of February the Hotel consumed 8000 kWhs of electricity, then the electricity bill for February will be $500 + (4500*10+3500*8)/100 = $1230. On the other hand if the consumption for February was only 4200 kWhs, then the electricity bill for February would be $500 + (4200*10)/100=$920 only. Use an IF function to estimate the electricity expenses. In your formulas use cell addresses only. DO NOT USE numbers other than constants in your formulas. Monthly maintenance expenses (landscaping, replacing light bulbs, repairing bathroom leaks, fixing internet access problems etc.) are $2000 if occupancy is 25% or less; $2500 if occupancy is between 25% and 35%; $3000 if occupancy is between 35% and 45%; $3500 if occupancy is between 45% and 55%; $4000 if occupancy is between 55% and 70%; and $5000 if occupancy exceeds 70%. Create a table to go with the lookup function. You will need to use lookup function to estimate maintenance expenses for January through December. The hotel provides free continental breakfast to its guests. Average number of guests staying in a room is 1.3 and the average cost of providing free breakfast to the guests is $3.25 per person. Part time staff is hired for providing breakfast. For breakfast 1 part time staff is needed if the occupancy is 35% or less; 2 part time staff if occupancy is between 35% and 55%; and 3 if occupancy rate exceeds 55%. These people work from 6 am to 11 am, 7 days a week, at the cost of $23/hr. You will have to type the data about the staff people given to you in the input section along with appropriate labels. To calculate the cost of breakfast staff, you will need a formula which has vlookup function in it. The hotel outsources the laundry service for linen, towels, etc. at an average cost of $2.50 per room. Linen and towels are changed everyday if the room is occupied. How often it happens can be figured out the way you figured out the house cleaning expenses. The hotel gets a 10% discount if the laundry expense for a month exceeds $1500. You will have to type the data for laundry service in the input section with appropriate labels. You will need a formula with an IF function in it for this. You will complete the project by doing following Steps I through VII. STEP I. Open Swiftie2024.xls from D2L. It is a template you will use to complete the project. STEP II. Data that is already known is typed in the Input section. I have done most of the input section. You will have to complete the input section by typing meaningful labels and numbers about the electricity usage given in the paragraph above. To do this, you will create additional labels for the electricity related numbers (200, 300, 55, 25, 1, 500, 10, 8, 4500 etc.). Type the labels starting in row 28. Type the corresponding numbers in the adjacent cells. Use meaningful labels. Also, type the occupancy rate and the number of days for the months of September through December. Display occupancy rate using percentage format. STEP III. Below the input section is a calculation section. You will type formulas in the calculation section to estimate the value of items listed in this section. The calculation section should contain formulas and labels only. All formulas should have cell addresses only. There should be no numbers in the formulas. However, numbers that are constants and are unlikely to change (like 12 for converting years into months) are acceptable. There should be NO formulas in the input section. Type formulas to compute electricity usage in kWhs (kilowatt hour), which is a unit of power just like gallons is a unit for volume of water. Type a formula for January and then copy it across for February through December. This formula should use an IF function. You also have to type a formula to estimate room rental revenue. It is a product of number of rooms, daily room rate, number of days in the months, and occupancy rate where occupancy rate is percentage of rooms that get rented during the month. Use IF function to compute water/sewage expenses. Marketing expense formula uses the IF function. You can save yourself lot of time if you type a formula for January that can be copied across for February through December. Similarly, type formulas to estimate other payments. Property tax for the whole year is 4% of the property value. Half of it, which is 2% of the property value, is paid in June and an equal amount is paid in December; no property tax is paid for other months. Use the =PMT function to calculate monthly mortgage payments. In the PMT function, the interest rate used should be the monthly interest rate and the number of payments would equal number of months in the loan period. Also, remember ending cash balance for any given month will be the beginning cash for the next month. Display negative monthly ending cash balances as a negative number. To change the formatting for negative currency values, right click on cell, select format cells, click the number tab, click on 2 currency, and then select the format you want for the negative number. The dollar values should be displayed in currency format and the percent values should be displayed in percentage format. Rooms in the hotel are cleaned if a guest is staying in the room. There are 30 days in April and if the occupancy rate in April is 40%, then a room is occupied for 12 (30*40/100) days. So, this room will be cleaned for a total of 12 days during the month of April. Common areas like the lobby, fitness center, elevators, restroom in the lobby etc. are cleaned twice everyday; once in the morning and once in the evening. STEP IV. The spreadsheet should also have a TITLE sheet. In the title sheet, type your name, title of the spreadsheet, date it was created, and a statement of purpose (what does the spreadsheet do). The sheet with the input and the output sections should be named Cashflow. STEP V. T&T would like to estimate ending monthly cash balances for different values of daily room rates. Using Scenario manager, create scenarios for the following business situations: a. When the daily room rate = $100, the occupancy rates are: Jan Feb Mar Apr May 72% 31% 69% 33% 28% Jun Jul Aug Sep Oct Nov Dec 39% 61% 61% 37% 48% 61% 74% b. When the daily room rate = $75, the occupancy rates are: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 79% 50% 82% 46% 43% 52% 70% 70% 54% 56% 70% 80% In each scenario summary, for changing cells display the values of the daily room rate and occupancy rates for each of the twelve months. You have to figure out what should be included in the result cells section. To create scenarios, first name the cells whose values you want to display in the scenario summary. Name your scenarios High room rate, Moderate room rate, and Low room rate. STEP VI. Assuming that the other input data does not change, which scenario is best for the T&T and why? Create a scenario summary report. Create a sheet called recommendation in your workbook and type your recommendation there about as to which scenario is best and why? Assume that profit is the motivation for T&T. Ending cash balance for December would be an indicator of profit for the whole year. STEP VII. Plot a column graph of Ending cash balance vs month. Your graph should have a graph title, x-axis title, and y-axis title. Along the x-axis display the months Jan, Feb, Mar etc. SUBMIT in the D2L dropbox: 1. A copy of the EXCEL workbook including the TITLE sheet, Cashflow sheet, scenario summary report, Cashflow graph, and recommendation sheet. 2. A hard copy of your EXEL workbook. Note: By default, names use absolute cell references. Guidelines for naming cells, formulas, and constants in Microsoft Excel · The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters. 3 · Names cannot be the same as a cell reference, such as Z$100 or R1C1. ·Spaces are not allowed. Underscore characters and periods may be used as word separators for example, First.Quarter or Sales_Tax. A name can contain up to 255 characters. Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names.