-----Please write down the formulas------

MAVERICK LODGE EXCEL PROJECT (20 POINTS) Maverick Lodge is a family-run lodge in Orlando, Florida, situated near Disney World. The lodge went bankrupt in 2014 and was purchased by Mickey and Minnie Mouse for $350,000. To finance the purchase, the Mouses took a loan of $180,000 from Small Business Administration at 4.25% annual interest with a payback period of 10 years. The Mouses also took out a loan of $120,000 from a bank at 3.05% annual interest rate to be paid back over a 15 year period. found themselves sho rt of cash for paying the bills Since buying the lodge, the Mouses have often 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 caslh 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 brealk, when visitors from all over the world come to Florida to enjoy the beaches and the Disney World. At daily room rate of $55 per roo m, the estimated occupancy rate is given below You will complete the project by doing following Steps I through VIII STEP I. Open Maverick.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 seven labels for the seven numbers (200, 15, 1, 45, 9,7, and 2500). Type the labels in cells A26 through A32. Type the corresponding seven numbers in cells B26 through B32. 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. You also have to create the table in the input section that will be used by the ylookup function. 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 kWs (kilowatts), which is a unit of power. Type a formula for January and then copy it across for February through December. 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 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 payment. 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 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. 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 -$60, 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 - $50, 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% 90% 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. STEP VIII. Create a pivot table displaying average net cashflow for each quarter. SUBMIT in the D2L dropbox A copy of the EXCEL workbook including the TITLE sheet, Cashflow sheet, scenario summary report, Cashflow graph, and recommendation sheet. Type the names of group members in the comment box of the drop box. Submit a hard copy of the above. Make right hand corner of the first page of your submission. 1. sure you have the names of the group members in the top 2. Note: By default, cellnames used in a fomula 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, Eirtuaeor Sales Tax A name can contain up to 255 characters. Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between then create another name called SALES in the same workbook, the second name will replace the first one. MAVERICK LODGE CASH FLOW 2019 5 INPUT SECTION 6 Loan amount from SBA 7 Interest rate for SBA 8 Payback period for SBA 9 Loan amount from bank 10 Interest rate for bank loan 11Payback period for bank loan 12 Daily room rate 13 No. of rooms 14 House cleaning 15 Fixed Telephone/Fax exp 16 Variable TeVFax exp 17 Property tax rate 18 Property value 19 Fixed water + sewage exp 20 Var. water + sewage exp 21 Insurance 22 Mkting exp 23 Mkting exp 24 Owner's salary $180,000 4.25% annual 10 S120,000 ears Create table for the vlookup function here 3.05% annual 15 S55.00 per day ears 20 S10 S150 1% each time the room is cleaned of revenue 4%of property value paid half in June and half in December S350,000 S100 1.50% of revenue if occupancy less than 50% per month 2.00% otherwise S500 if expected occupancy under S1,000otherwise S3,500 per month 40% 25 Beginning cash on-hand 26 The electricity data given to you goes here 27 You decide what labels to use. Delete these 28 sentences and type your data S2,500 30 31 32 JAN 0.75 31 FEB MAR APR 0.45 30 MAY JUNE JUL AUG SEP OCT NOV DEC 0.75 31 0.4 31 0.4 30 0.65 31 0.65 31 type the data for the remaining months from the assignment sheet. 34 Occupancy rate 35 No. of days in the month 36 Quarter 0.45 28 38 CALCULATION SECTION 39 Electricity usage in Kw 40 41 YEAR 2019 CASH FLOW PROJECTIONS FOR MAVERICK LODGE FEB MAR APR MAY JUNE JUL AUG SEP OCT NOV DEC JAN 44 Revenue 45 48 Payments 47 Maintenance 48 House cleaning 49 Telephone 50 Marketing 51 Electricity 52 Water + sewage 53 Insurance 54 Mortgage SBA 55 Mortgage Bank 56 Property tax 57 Owner's sala 58 Total payments 59 60 Beginning caslh 61 Net cashflow (Revenue-Payments) 62 Ending cash MAVERICK LODGE EXCEL PROJECT (20 POINTS) Maverick Lodge is a family-run lodge in Orlando, Florida, situated near Disney World. The lodge went bankrupt in 2014 and was purchased by Mickey and Minnie Mouse for $350,000. To finance the purchase, the Mouses took a loan of $180,000 from Small Business Administration at 4.25% annual interest with a payback period of 10 years. The Mouses also took out a loan of $120,000 from a bank at 3.05% annual interest rate to be paid back over a 15 year period. found themselves sho rt of cash for paying the bills Since buying the lodge, the Mouses have often 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 caslh 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 brealk, when visitors from all over the world come to Florida to enjoy the beaches and the Disney World. At daily room rate of $55 per roo m, the estimated occupancy rate is given below You will complete the project by doing following Steps I through VIII STEP I. Open Maverick.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 seven labels for the seven numbers (200, 15, 1, 45, 9,7, and 2500). Type the labels in cells A26 through A32. Type the corresponding seven numbers in cells B26 through B32. 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. You also have to create the table in the input section that will be used by the ylookup function. 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 kWs (kilowatts), which is a unit of power. Type a formula for January and then copy it across for February through December. 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 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 payment. 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 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. 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 -$60, 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 - $50, 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% 90% 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. STEP VIII. Create a pivot table displaying average net cashflow for each quarter. SUBMIT in the D2L dropbox A copy of the EXCEL workbook including the TITLE sheet, Cashflow sheet, scenario summary report, Cashflow graph, and recommendation sheet. Type the names of group members in the comment box of the drop box. Submit a hard copy of the above. Make right hand corner of the first page of your submission. 1. sure you have the names of the group members in the top 2. Note: By default, cellnames used in a fomula 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, Eirtuaeor Sales Tax A name can contain up to 255 characters. Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between then create another name called SALES in the same workbook, the second name will replace the first one. MAVERICK LODGE CASH FLOW 2019 5 INPUT SECTION 6 Loan amount from SBA 7 Interest rate for SBA 8 Payback period for SBA 9 Loan amount from bank 10 Interest rate for bank loan 11Payback period for bank loan 12 Daily room rate 13 No. of rooms 14 House cleaning 15 Fixed Telephone/Fax exp 16 Variable TeVFax exp 17 Property tax rate 18 Property value 19 Fixed water + sewage exp 20 Var. water + sewage exp 21 Insurance 22 Mkting exp 23 Mkting exp 24 Owner's salary $180,000 4.25% annual 10 S120,000 ears Create table for the vlookup function here 3.05% annual 15 S55.00 per day ears 20 S10 S150 1% each time the room is cleaned of revenue 4%of property value paid half in June and half in December S350,000 S100 1.50% of revenue if occupancy less than 50% per month 2.00% otherwise S500 if expected occupancy under S1,000otherwise S3,500 per month 40% 25 Beginning cash on-hand 26 The electricity data given to you goes here 27 You decide what labels to use. Delete these 28 sentences and type your data S2,500 30 31 32 JAN 0.75 31 FEB MAR APR 0.45 30 MAY JUNE JUL AUG SEP OCT NOV DEC 0.75 31 0.4 31 0.4 30 0.65 31 0.65 31 type the data for the remaining months from the assignment sheet. 34 Occupancy rate 35 No. of days in the month 36 Quarter 0.45 28 38 CALCULATION SECTION 39 Electricity usage in Kw 40 41 YEAR 2019 CASH FLOW PROJECTIONS FOR MAVERICK LODGE FEB MAR APR MAY JUNE JUL AUG SEP OCT NOV DEC JAN 44 Revenue 45 48 Payments 47 Maintenance 48 House cleaning 49 Telephone 50 Marketing 51 Electricity 52 Water + sewage 53 Insurance 54 Mortgage SBA 55 Mortgage Bank 56 Property tax 57 Owner's sala 58 Total payments 59 60 Beginning caslh 61 Net cashflow (Revenue-Payments) 62 Ending cash