Maverick Lodge is a family-run lodge in Orlando, Florida, situated near Disney World. The lodge went bankrupt in 2009 and was purchased by Mickey and Minnie Mouse for $475,000. To finance the purchase, the Mouses took a loan of $150,000 from Small Business Administration at 3.25% annual interest with a payback period of 30 years. The Mouses also took out a loan of $200,000 from a bank at 4.25% annual interest rate to be paid back over a 30 year period. Since buying the lodge, the Mouses 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 Mouses 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 Mouses will not have enough money to pay all their bills due at the end of that month. The Mouses have determined the lodge'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 Florida to enjoy the beaches and the Disney World. At daily room rate of $85 per room, the estimated occupancy rate is given below. Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 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 40 kWhs a day for lighting the parking lot and the lodge signs, and providing lighting and heating/cooling to the hallway and the front lobby when the occupancy is below 55% and fixed amount of 50 kWhs a day when the occupancy is 55% or higher and (2) 10 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 $200 plus 9 cents per kWh for the first 2500 kWhs and 7 cents per kWh for units beyond 2500 kWhs. So, for example, if during the month of February the lodge consumed 4000 kWhs of electricity, then the electricity bill for February will be $200 + (2500*9+1500*7)/100 = $530. On the other hand if the consumption for February was only 1200 kWhs, then the electricity bill for February would be $200 + (1200*9)/100=$308 only. Use an IF function to estimate the electricity expenses. In your formulas use cell addresses only. DO NOT USE numbers other than constants (like dividing by 100 to convert cents into dollars) in your formulas. You will need an IF formula for each of the 12 months for Electricity usage and electricity payments. Monthly maintenance expenses (lawncare, changing light bulbs, repairs etc.) are $1000 if occupancy is 25% or less; $1250 if occupancy is between 25% and 35%; $1500 if occupancy is between 35% and 45%; $1800 if occupancy is between 45% and 55%; $2200 if occupancy is between 55% and 70%; and $2600 if occupancy exceeds 70%. Create a table to go with the lookup

function that you will need to estimate maintenance expenses for January through December. So, in the worksheet where it says create table for the vlookup function: 1. delete the sentence; 2. Undo merge and center; and then 3. create the table. You will complete the project by doing following Steps I through VII. STEP I. Open Maverick2021.xls from D2L. It is a template you will use to complete the project. STEP II. Data that is already known is usually 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, 10, 1, 40, 50, 9, 7, 2500 etc.). Type the labels starting in row 26. 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. Type formulas to compute electricity usage in kWhs (kilowatt hours), which is a unit of power just like gallons is a unit for volume of water. Type a formula for January making use of dollar signs in cell addresses so that you can later 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. Maintenance payments would require using a formula that has vlookup function. For house cleaning assume that if a room is occupied, then it is cleaned that day otherwise it is not (if the hotel had only one room and in January the occupancy rate was 60%, then the number of days that room would get cleaned in January would be 31*.60 where 31 is the number of days in January). Telephone bill should need a simple formula. Formula for water/sewage expenses should include IF function. 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 is 4% of the property value for the whole year. Half of it, which is 2% of the property value, is paid in June and an equal amount is paid in December. 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 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. Type formulas to complete the calculation section. 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 for the owner). The sheet with the input and the output sections should be named Cashflow. 2

STEP V. Mouses 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 = $90, the occupancy rates are: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 72% 31% 69% 33% 28% 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, display the values of the daily room rate and occupancy rates for each of the twelve months. Name your scenarios High room rate, Moderate room rate, and Low room rate. To create scenarios, first name the cells whose values you want to display in the scenarios. STEP VI. Assuming that the other input data does not change, which scenario is best for the lodge owners 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? 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. 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. 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. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one. Dont give more than one name to the same cell. Also, dont use the same name for two or more cells.