Just do Assignment 1. Thank you

6 CASE THE SKI RESORT INVESTMENT DECISION Decision Support Using Microsoft Excel PREVIEW In this case, you will use Microsoft Excel to help a ski resort establish a budget for the next three years and decide whether to expand activities into the summer months. PREPARATION Review spreadsheet concepts discussed in class and in your textbook. Complete any exercises that your instructor assigns. Complete any part of Tutorial C that your instructor assigns. You may need to review the use of If statements and the section called "Cash Flow Calculations: Borrowing and Repayments." Review file-saving procedures for Windows programs. Refer to Tutorial E as necessary. . . BACKGROUND Ski areas across the United States have seen external pressures on their profit margins recently. Profit margin, a key performance indicator, has been negative in the past few years due to high operating costs and diminish- ing revenue. A medium-sized ski area in Vermont is concerned about its profit margins and has hired you, a proficient Excel user, to help generate a spreadsheet budget and create a "what-if" analysis. The aging of baby boomers, who in the past have been active skiers, has caused the total number of ski- ers to drop in many areas across the country. Baby boomers represent an estimated 20 percent of all skiers. Younger people are skiing, but not as often as their parents and grandparents did. Younger people tend to have other interests; also, they work longer hours and have more debt, which means they have less cash to spend on an expensive sport. Another huge factor affecting the ski industry is climate change. The east coast of the United States has seen higher temperatures and fluctuations in the amount of snowfall recently. For example, Vermont has had less snowfall, especially over the last five years. Finally, the economy affects the ski industry. When potential skiers are out of work or underemployed, they do not have the disposable inco to spend on lift tickets, rentals, food, transportation, and lodging. Snow Top Ski Area is located in the Green Mountains of Vermont. The resort currently has capacity for about 5,000 skiers per day, and it averages about 3,000 daily skiers. Snow Top also sells about 500 season tickets per year to local enthusiasts. The season tickets are sold at a significant discount compared with the daily tickets, but the management at Snow Top realizes that season-ticket holders also spend money at the resort on food and other incidentals. Snow Top generates income not only from ticket sales but from concessions and ski rentals. Most daily skiers rent equipment, either because they do not own it themselves or they want to avoid the hassle of bring- ing equipment on flights or train rides to Vermont. There is also an "other" revenue source generated from the ski store, where skiers buy items they left at home: goggles, balaclavas (ski masks), gloves, and so on. Case 6 The owners of Snow Top are concerned about their negative profit margins, and are wondering if they should expand their offerings and keep the ski area open all year. The summer months could bring in revenue from hiking, mountain biking, and gondola riding. The owners could also install a zip line for added adventure to attract more visitors. The owners would like you to finish setting up their budget in an Excel spreadsheet and run different scenarios. These scenarios would indicate the effects of changes in the number of skiers, different economic outlooks, and climate change. Then, the owners would like you to add projected revenues from the summer activities to see how they might affect some of the numbers. ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT In this assignment, you will produce a spreadsheet that models Snow Top's financial situation. Then, in Assignment 2, you will run Scenario Manager twice to explore various financial scenarios and write a memo- randum that documents your analysis and findings. First, you will create the spreadsheet model of the decision. The model covers the three years from 2020 to 2022. This section helps you set up each of the following spreadsheet components before entering cell formulas: Constants Inputs Summary of Key Results Calculations Income and Cash Flow statements Debt Owed A discussion of each section follows. The spreadsheet skeleton for this case is available for you to use; it will save you time. To access the spreadsheet skeleton, go to your data files, select Case 6, and then select Ski Resort.xlsx Constants Section Your spreadsheet should include the constants shown in Figure 6-1. An explanation of the line items follows the figure. B D E 2020 2021 0.20 4,000,000 $ 0.03 5 1 SKI RESORT INVESTMENT DECISION 2 3 CONSTANTS 4 TAX RATE 5 CASH NEEDED TO START NEXT YEAR 6 INTEREST RATE ON DEBT 7 DAILY LIFT TICKET PRICE ANNUAL LIFT TICKET PRICE 9 AVERAGE DAILY MONEY SPENT ON FOOD 10 AVERAGE SKI SCHOOL DAILY PRICE 11 AVERAGE DAILY RENTAL PRICE 12 NUMBER OF SKI DAYS PER YEAR 13 OTHER DAILY REVENUE 14 SUMMER POTENTIAL REVENUE 15 OPERATING COSTS - SNOW MAKING 16 OPERATING COSTS - SUMMER 17 DAILY OTHER WINTER OPERATING COSTS 18 FIXED COSTS 19 2019 NA NA NA NA NA NA NA NA 100 5 $ $ $ $ 0.20 4,000,000 $ 0.03 100 $ 700 $ 25 S 45 $ 25 5 100 15$ 2022 0.20 4,000,000 0.03 100 700 25 45 30 100 TUU 15 1,000,000 1,800,000 400,000 15,000 35,000,000 700 $ 25 $ 45 $ 27 S 100 15 1,000,000 1.800.000 350,000 $ 15,000 $ 35,000,000 $ NA 2,000,000 $ 350,000 $ 15,000 $ 35,000,000 $ FIGURE 6-1 Constants section . . . Tax Rate-The rate at which Snow Top's net income is taxed is shown for each year. Cash Needed to Start Next Year--The local Vermont bank wants Snow Top to have at least 84 million cash at the beginning of each year. Assume that the ski resort could borrow from the bank at the end of a year in order to begin the new year with the needed amount. Interest Rate on Debt-This is the interest rate that the bank charges Snow Top on any outstanding debt. The interest is calculated for the year based on the debt owed at the beginning of the year. Daily Lift Ticket Price-This is the amount that Snow Top charges a customer to ski for an entire day at the resort. Annual Lift Ticket PriceThis is the yearly amount (a one-time fee) that a skier will pay for an annual pass to the ski area. Skiers can then ski as many times as they choose. Average Daily Money Spent on Food-This is the average amount of money that skiers will spend on food and drink in the concession stands and restaurants at Snow Top. Average Ski School Daily PriceIf skiers choose to take a lesson, this is the average price they will pay for the lesson. Average Daily Rental Price-If skiers rent equipment, such as boots, poles, skis, and helmets, this is the average price they will pay for one day's rental. Number of Ski Days per Year-This is the average number of days in Vermont's ski season. Snow Top is open for business for this number of days per year. Other Daily Revenue-Skiers often forget or lose gloves, goggles, hats, or other equipment required to make skiing pleasurable. The resort has a shop that supplies these items along with souvenirs. This amount of other revenue is what Snow Top expects each day, Summer Potential Revenue-If Snow Top decides to open for the summer and develop activities to attract visitors, it can expect this amount of revenue from the activities. Operating Costs - Snow Making-Snow making is required most of the season due to the recent lack of natural snow. Snow making is a huge expense for the ski industry. Luckily, snow-making equipment continues to become more efficient, so the costs are not expected to increase. Operating Costs - Summer-These are the expected costs that Snow Top will incur if it operates over the summer. Daily Other Winter Operating CostsThis is the additional daily cost of operating Snow Top, beyond the costs already noted. Fixed Costs-This is the amount of fixed costs per year; this number is not a function of how many days Snow Top is open each year. . . . Inputs Section Your spreadsheet should include the following inputs for the years 2020 to 2022, as shown in Figure 6-2. B D E ALL YRS 19 20 INPUTS 21 RATE OF CHANGE IN SKIERS 22 ECONOMIC OUTLOOK (Good, Poor) 23 CLIMATE CHANGE FACTOR 24 SUMMER OPERATIONS (Y OR N) 25 2020 NA NA 2021 NA NA 2022 NA NA NA NA FIGURE 6-2 Inputs section . Rate of Change in SkiersThis percentage could be positive or negative, depending on how the resort thinks the number of future skiers will change. Economic Outlook-If the economy is going well and is expected to continue to hum along, enter "Good" in the cell. If the economy is faltering and is expected to be lackluster, enter "Poor." Climate Change Factor-This percentage factor, expressed as a negative number, indicates the severity of climate change and its effect on the number of skiers. Summer Operations-If Snow Top decides to operate in the summer, enter "Y" in this cell. If not, the input is "N." Case 6 Summary of Key Results Section Your spreadsheet should include the results shown in Figure 6-3. B D E 25 26 SUMMARY OF KEY RESULTS 27 NET INCOME AFTER TAXES 28 END-OF-THE-YEAR CASH ON HAND 29 END-OF-THE-YEAR DEBT OWED 30 PROFIT MARGIN 311 FIGURE 6-3 Summary of Key Results section For each year, your spreadsheet should compute net income after taxes, cash on hand at the end of the year, debt owed at the end of the year, and profit margin. These values are computed elsewhere in the spread- sheet and should be echoed here for all years. Calculations Section You should calculate intermediate results that will be used in the income and cash flow statement that fol- lows. The Calculations section is shown in Figure 6-4. When called for, use absolute referencing properly. Val- ues must be computed by cell formula. Cell formulas should not reference a cell with a value of "NA," which stands for "not applicable." An explanation of each item in this section follows the figure. B D E 2019 2020 2021 2022 3000 500 31 32 CALCULATIONS 33 PERCENTAGE CHANGE IN DAILY SKIERS 34 NUMBER OF DAILY SKIERS 35 NUMBER OF SEASON SKIERS 36 YEARLY TOTAL SKIER REVENUE 37 DAILY SKI SCHOOL ATTENDEES 38 YEARLY SKI SCHOOL REVENUE 39 DAILY EQUIPMENT RENTAL USERS 40 YEARLY RENTAL REVENUE 41 YEARLY FOOD CONCESSION REVENUE 42 OTHER YEARLY REVENUE 43 FIGURE 6-4 Calculations section . Percentage Change in Daily Skiers--- This number is a function of the rate of change in skiers, the economic outlook, and the climate change factor. If the economic outlook is poor, then the rate of change in skiers will decrease by 2 percent. The climate change factor will also be applied to the rate of change in skiers. For example, if the economic outlook is poor and the climate change factor is minus 3 percent, then the total rate of change in skiers will be the rate of change minus 2 percent minus 3 percent. Number of Daily SkiersThis number is a function of the percentage change in daily skiers and the previous year's number of daily skiers. Number of Season Skiers--This number is a function the rate of change in skiers. The rate of change in skiers is applied to the prior year. Yearly Total Skier Revenue-This number is a function of the number of daily skiers, the average lift ticket price, and the number of ski days per year, plus a function of the number of annual- pass skiers and the annual lift ticket price. The Ski Resort Investment Decision . Daily Ski School Attendees-This number is a function of the number of daily skiers. Assume that 10 percent of the daily skiers will take ski school lessons. Yearly Ski School Revenue-This number is a function of the daily ski school attendees, the aver- age ski school daily price, and the number of ski days per year. Daily Equipment Rental Users-This number is 75 percent of the daily number of skiers. Yearly Rental RevenueThis number is a function of the daily equipment rental users, the aver- age daily rental price, and the number of ski days per year. Yearly Food Concession RevenueThis number is a function of the total number of skiers per year and the average daily money spent on food. Assume that 90 percent of skiers buy food. Other Yearly Revenue-This number is a function of the other daily revenue and the number of ski days per year. Income and Cash Flow Statements The forecast for net income and cash flow starts with the cash on hand at the beginning of the year. This value is followed by the income statement and the calculation of cash on hand at year's end. For readability, format cells in this section as currency with zero decimals. Values must be computed by cell formula; hard-code numbers in formulas only if you are told to do so. Cell formulas should not reference a cell with a value of "NA." Your spreadsheets should look like those in Figures 6-5 and 6-6. A discussion of each item in the section follows each figure. B D E 2020 2021 2022 2019 NA A 45 INCOME STATEMENT AND 46 CASH FLOW STATEMENT 47 BEGINNING-OF-THE-YEAR CASH ON HAND 48 49 REVENUE - SKIERS 50 REVENUE - SKI SCHOOL 51 REVENUE - EQUIPMENT RENTAL 52 REVENUE - FOOD CONCESSION 53 REVENUE - OTHER 54 REVENUE - POTENTIAL SUMMER 55 TOTAL REVENUE 56 OPERATING COSTS - SNOW MAKING 57 SUMMER OPERATING COSTS 58 YEARLY WINTER OPERATING COSTS 59 FIXED COSTS 60 TOTAL 61 INCOME BEFORE INTEREST AND TAXES 62 INTEREST EXPENSE 63 INCOME BEFORE TAXES 64 INCOME TAX EXPENSE 65 NET INCOME AFTER TAXES NA NA NA NA NA NA NA NA NA MA NA MA NA ma NA wa NA NA NA NA NA FIGURE 6-5 Income and cash flow statements section . Beginning-of-the-Year Cash on Hand-This value is the cash on hand at the end of the prior year. Revenue - SkiersThis value was computed elsewhere in the spreadsheet and can be echoed here. Revenue - Ski SchoolThis value was computed elsewhere in the spreadsheet and can be echoed here. Revenue - Equipment Rental-This value was computed elsewhere in the spreadsheet and can be echoed here. Revenue - Food Concession-This value was computed elsewhere in the spreadsheet and can be echoed here. Revenue - Other-This value was computed elsewhere in the spreadsheet and can be echoed here. Case 6 Revenue - Potential Summer-If Snow Top decides to include summer activities and therefore operate in the summer, then the value for the revenue is echoed here from the Constants section. If Snow Top chooses not to include summer activities, then the value here is zero. Total RevenueThis amount is the sum of the revenue from the skiers, the ski school, the equipment rental, the food concession, other revenue, and perhaps the summer revenue, if chosen Operating Costs - Snow Making-This value is recorded in the Constants section and echoed here. Summer Operating CostsIf Snow Top chooses to operate in the summer, then the value for the operating costs can be found in the Constants section and echoed here. If Snow Top chooses not to operate in the summer, then this value is zero. Yearly Winter Operating costs-This value is a function of the daily other winter operating costs and the number of ski days per year. Fixed Costs-This value is recorded in the Constants section and echoed here. Total Costs-This is the sum of the operating costs of snow making, the summer operating costs, if chosen, the yearly winter operating costs, and the fixed costs. Income Before Interest and TaxesThis value is the difference between the total revenue and the total costs. Interest ExpenseThis value can be calculated from the interest rate, which is a value in the Constants section, and the debt owed at the beginning of the year. Income Before TaxesThis value is the difference between Income Before Interest and Taxes and the Interest Expense. Income Tax Expense-This value is based on the tax rate, which is a value in the Constants section, and the Income Before Taxes. This value should be calculated only for income that is a positive number. In other words, no tax is applied if the income before taxes is less than zero. Net Income After TaxesThis number is the difference between the Income Before Taxes and the Income Tax Expense. The following values must be computed by cell formula; hard-code numbers in formulas only when you are told to do so. Cell formulas should not reference a cell with a value of "NA." An explanation of each item follows the figure. . . B D E 2019 2020 2021 2022 A 66 67 NET CASH POSITION (NCP) BEFORE BORROWING AND REPAYMENT OF DEBT 68 (BEG OF YR CASH + NET INCOME) 69 ADD:BORROWING FROM BANK 70 LESS: REPAYMENT TO BANK 71 EQUALS: END-OF-YEAR CASH ON HAND 72 NA NA NA 4,000,000 $ FIGURE 6-6 End-of-year cash on hand section Net Cash Position (NCP) Before Borrowing and Repayment of Debt-This amount equals cash at the beginning of the year plus net income after taxes. Add: Borrowing from Bank-Assume that Snow Top can borrow from bankers at the end of the year to reach the minimum cash needed to start the next year, this minimum is a value in the Constants section. If the NCP is less than this minimum, the company would borrow enough to start the next year with the minimum. Borrowing increases cash on hand, of course, Less: Repayment to Bank-The resort will use its excess cash at year's end to pay off as much debt as possible without going below the minimum cash threshold. Excess cash is the NCP minus the minimum cash required on hand. Amounts over the minimum are available to repay any debt. The resort must repay as much as it can if it has any money available. Equals: End-of-Year Cash on Hand-This amount is the NCP plus any bank borrowing minus any repayments. . Debt Owed Section This section shows a calculation of debt owed at the end of a year (see Figure 6-7). Year 2019 values are NA except for End-of-year debt owed, which is $2 million. Values must be computed by cell formula. Cell formulas should not reference a cell with a value of NA. An explanation of each item follows the figure. D E 2020 2021 2022 A 72 73 DEBT OWED 74 BEGINNING-OF-YEAR DEBT OWED 75 ADD: BORROWING FROM BANK 76 LESS: REPAYMENT TO BANK 77 EQUALS: END-OF-THE-YEAR DEBT OWED 78 2019 NA NA NA 2,000,000 $ FIGURE 6-7 Debt Owed section Beginning-of-Year Debt OwedDebt owed at the beginning of a year equals the debt owed at the end of the prior year. Add: Borrowing from BankThis amount has been calculated elsewhere and can be echoed to this section. Borrowing increases the amount of debt owed. Less: Repayment to Bank-This amount has been calculated elsewhere and can be echoed to this section. Repayments reduce the amount of debt owed. Equals: End-of-the-Year Debt OwedThis is the amount owed at the beginning of a year, plus borrowing during the year, minus repayments during the year. . 6 CASE THE SKI RESORT INVESTMENT DECISION Decision Support Using Microsoft Excel PREVIEW In this case, you will use Microsoft Excel to help a ski resort establish a budget for the next three years and decide whether to expand activities into the summer months. PREPARATION Review spreadsheet concepts discussed in class and in your textbook. Complete any exercises that your instructor assigns. Complete any part of Tutorial C that your instructor assigns. You may need to review the use of If statements and the section called "Cash Flow Calculations: Borrowing and Repayments." Review file-saving procedures for Windows programs. Refer to Tutorial E as necessary. . . BACKGROUND Ski areas across the United States have seen external pressures on their profit margins recently. Profit margin, a key performance indicator, has been negative in the past few years due to high operating costs and diminish- ing revenue. A medium-sized ski area in Vermont is concerned about its profit margins and has hired you, a proficient Excel user, to help generate a spreadsheet budget and create a "what-if" analysis. The aging of baby boomers, who in the past have been active skiers, has caused the total number of ski- ers to drop in many areas across the country. Baby boomers represent an estimated 20 percent of all skiers. Younger people are skiing, but not as often as their parents and grandparents did. Younger people tend to have other interests; also, they work longer hours and have more debt, which means they have less cash to spend on an expensive sport. Another huge factor affecting the ski industry is climate change. The east coast of the United States has seen higher temperatures and fluctuations in the amount of snowfall recently. For example, Vermont has had less snowfall, especially over the last five years. Finally, the economy affects the ski industry. When potential skiers are out of work or underemployed, they do not have the disposable inco to spend on lift tickets, rentals, food, transportation, and lodging. Snow Top Ski Area is located in the Green Mountains of Vermont. The resort currently has capacity for about 5,000 skiers per day, and it averages about 3,000 daily skiers. Snow Top also sells about 500 season tickets per year to local enthusiasts. The season tickets are sold at a significant discount compared with the daily tickets, but the management at Snow Top realizes that season-ticket holders also spend money at the resort on food and other incidentals. Snow Top generates income not only from ticket sales but from concessions and ski rentals. Most daily skiers rent equipment, either because they do not own it themselves or they want to avoid the hassle of bring- ing equipment on flights or train rides to Vermont. There is also an "other" revenue source generated from the ski store, where skiers buy items they left at home: goggles, balaclavas (ski masks), gloves, and so on. Case 6 The owners of Snow Top are concerned about their negative profit margins, and are wondering if they should expand their offerings and keep the ski area open all year. The summer months could bring in revenue from hiking, mountain biking, and gondola riding. The owners could also install a zip line for added adventure to attract more visitors. The owners would like you to finish setting up their budget in an Excel spreadsheet and run different scenarios. These scenarios would indicate the effects of changes in the number of skiers, different economic outlooks, and climate change. Then, the owners would like you to add projected revenues from the summer activities to see how they might affect some of the numbers. ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT In this assignment, you will produce a spreadsheet that models Snow Top's financial situation. Then, in Assignment 2, you will run Scenario Manager twice to explore various financial scenarios and write a memo- randum that documents your analysis and findings. First, you will create the spreadsheet model of the decision. The model covers the three years from 2020 to 2022. This section helps you set up each of the following spreadsheet components before entering cell formulas: Constants Inputs Summary of Key Results Calculations Income and Cash Flow statements Debt Owed A discussion of each section follows. The spreadsheet skeleton for this case is available for you to use; it will save you time. To access the spreadsheet skeleton, go to your data files, select Case 6, and then select Ski Resort.xlsx Constants Section Your spreadsheet should include the constants shown in Figure 6-1. An explanation of the line items follows the figure. B D E 2020 2021 0.20 4,000,000 $ 0.03 5 1 SKI RESORT INVESTMENT DECISION 2 3 CONSTANTS 4 TAX RATE 5 CASH NEEDED TO START NEXT YEAR 6 INTEREST RATE ON DEBT 7 DAILY LIFT TICKET PRICE ANNUAL LIFT TICKET PRICE 9 AVERAGE DAILY MONEY SPENT ON FOOD 10 AVERAGE SKI SCHOOL DAILY PRICE 11 AVERAGE DAILY RENTAL PRICE 12 NUMBER OF SKI DAYS PER YEAR 13 OTHER DAILY REVENUE 14 SUMMER POTENTIAL REVENUE 15 OPERATING COSTS - SNOW MAKING 16 OPERATING COSTS - SUMMER 17 DAILY OTHER WINTER OPERATING COSTS 18 FIXED COSTS 19 2019 NA NA NA NA NA NA NA NA 100 5 $ $ $ $ 0.20 4,000,000 $ 0.03 100 $ 700 $ 25 S 45 $ 25 5 100 15$ 2022 0.20 4,000,000 0.03 100 700 25 45 30 100 TUU 15 1,000,000 1,800,000 400,000 15,000 35,000,000 700 $ 25 $ 45 $ 27 S 100 15 1,000,000 1.800.000 350,000 $ 15,000 $ 35,000,000 $ NA 2,000,000 $ 350,000 $ 15,000 $ 35,000,000 $ FIGURE 6-1 Constants section . . . Tax Rate-The rate at which Snow Top's net income is taxed is shown for each year. Cash Needed to Start Next Year--The local Vermont bank wants Snow Top to have at least 84 million cash at the beginning of each year. Assume that the ski resort could borrow from the bank at the end of a year in order to begin the new year with the needed amount. Interest Rate on Debt-This is the interest rate that the bank charges Snow Top on any outstanding debt. The interest is calculated for the year based on the debt owed at the beginning of the year. Daily Lift Ticket Price-This is the amount that Snow Top charges a customer to ski for an entire day at the resort. Annual Lift Ticket PriceThis is the yearly amount (a one-time fee) that a skier will pay for an annual pass to the ski area. Skiers can then ski as many times as they choose. Average Daily Money Spent on Food-This is the average amount of money that skiers will spend on food and drink in the concession stands and restaurants at Snow Top. Average Ski School Daily PriceIf skiers choose to take a lesson, this is the average price they will pay for the lesson. Average Daily Rental Price-If skiers rent equipment, such as boots, poles, skis, and helmets, this is the average price they will pay for one day's rental. Number of Ski Days per Year-This is the average number of days in Vermont's ski season. Snow Top is open for business for this number of days per year. Other Daily Revenue-Skiers often forget or lose gloves, goggles, hats, or other equipment required to make skiing pleasurable. The resort has a shop that supplies these items along with souvenirs. This amount of other revenue is what Snow Top expects each day, Summer Potential Revenue-If Snow Top decides to open for the summer and develop activities to attract visitors, it can expect this amount of revenue from the activities. Operating Costs - Snow Making-Snow making is required most of the season due to the recent lack of natural snow. Snow making is a huge expense for the ski industry. Luckily, snow-making equipment continues to become more efficient, so the costs are not expected to increase. Operating Costs - Summer-These are the expected costs that Snow Top will incur if it operates over the summer. Daily Other Winter Operating CostsThis is the additional daily cost of operating Snow Top, beyond the costs already noted. Fixed Costs-This is the amount of fixed costs per year; this number is not a function of how many days Snow Top is open each year. . . . Inputs Section Your spreadsheet should include the following inputs for the years 2020 to 2022, as shown in Figure 6-2. B D E ALL YRS 19 20 INPUTS 21 RATE OF CHANGE IN SKIERS 22 ECONOMIC OUTLOOK (Good, Poor) 23 CLIMATE CHANGE FACTOR 24 SUMMER OPERATIONS (Y OR N) 25 2020 NA NA 2021 NA NA 2022 NA NA NA NA FIGURE 6-2 Inputs section . Rate of Change in SkiersThis percentage could be positive or negative, depending on how the resort thinks the number of future skiers will change. Economic Outlook-If the economy is going well and is expected to continue to hum along, enter "Good" in the cell. If the economy is faltering and is expected to be lackluster, enter "Poor." Climate Change Factor-This percentage factor, expressed as a negative number, indicates the severity of climate change and its effect on the number of skiers. Summer Operations-If Snow Top decides to operate in the summer, enter "Y" in this cell. If not, the input is "N." Case 6 Summary of Key Results Section Your spreadsheet should include the results shown in Figure 6-3. B D E 25 26 SUMMARY OF KEY RESULTS 27 NET INCOME AFTER TAXES 28 END-OF-THE-YEAR CASH ON HAND 29 END-OF-THE-YEAR DEBT OWED 30 PROFIT MARGIN 311 FIGURE 6-3 Summary of Key Results section For each year, your spreadsheet should compute net income after taxes, cash on hand at the end of the year, debt owed at the end of the year, and profit margin. These values are computed elsewhere in the spread- sheet and should be echoed here for all years. Calculations Section You should calculate intermediate results that will be used in the income and cash flow statement that fol- lows. The Calculations section is shown in Figure 6-4. When called for, use absolute referencing properly. Val- ues must be computed by cell formula. Cell formulas should not reference a cell with a value of "NA," which stands for "not applicable." An explanation of each item in this section follows the figure. B D E 2019 2020 2021 2022 3000 500 31 32 CALCULATIONS 33 PERCENTAGE CHANGE IN DAILY SKIERS 34 NUMBER OF DAILY SKIERS 35 NUMBER OF SEASON SKIERS 36 YEARLY TOTAL SKIER REVENUE 37 DAILY SKI SCHOOL ATTENDEES 38 YEARLY SKI SCHOOL REVENUE 39 DAILY EQUIPMENT RENTAL USERS 40 YEARLY RENTAL REVENUE 41 YEARLY FOOD CONCESSION REVENUE 42 OTHER YEARLY REVENUE 43 FIGURE 6-4 Calculations section . Percentage Change in Daily Skiers--- This number is a function of the rate of change in skiers, the economic outlook, and the climate change factor. If the economic outlook is poor, then the rate of change in skiers will decrease by 2 percent. The climate change factor will also be applied to the rate of change in skiers. For example, if the economic outlook is poor and the climate change factor is minus 3 percent, then the total rate of change in skiers will be the rate of change minus 2 percent minus 3 percent. Number of Daily SkiersThis number is a function of the percentage change in daily skiers and the previous year's number of daily skiers. Number of Season Skiers--This number is a function the rate of change in skiers. The rate of change in skiers is applied to the prior year. Yearly Total Skier Revenue-This number is a function of the number of daily skiers, the average lift ticket price, and the number of ski days per year, plus a function of the number of annual- pass skiers and the annual lift ticket price. The Ski Resort Investment Decision . Daily Ski School Attendees-This number is a function of the number of daily skiers. Assume that 10 percent of the daily skiers will take ski school lessons. Yearly Ski School Revenue-This number is a function of the daily ski school attendees, the aver- age ski school daily price, and the number of ski days per year. Daily Equipment Rental Users-This number is 75 percent of the daily number of skiers. Yearly Rental RevenueThis number is a function of the daily equipment rental users, the aver- age daily rental price, and the number of ski days per year. Yearly Food Concession RevenueThis number is a function of the total number of skiers per year and the average daily money spent on food. Assume that 90 percent of skiers buy food. Other Yearly Revenue-This number is a function of the other daily revenue and the number of ski days per year. Income and Cash Flow Statements The forecast for net income and cash flow starts with the cash on hand at the beginning of the year. This value is followed by the income statement and the calculation of cash on hand at year's end. For readability, format cells in this section as currency with zero decimals. Values must be computed by cell formula; hard-code numbers in formulas only if you are told to do so. Cell formulas should not reference a cell with a value of "NA." Your spreadsheets should look like those in Figures 6-5 and 6-6. A discussion of each item in the section follows each figure. B D E 2020 2021 2022 2019 NA A 45 INCOME STATEMENT AND 46 CASH FLOW STATEMENT 47 BEGINNING-OF-THE-YEAR CASH ON HAND 48 49 REVENUE - SKIERS 50 REVENUE - SKI SCHOOL 51 REVENUE - EQUIPMENT RENTAL 52 REVENUE - FOOD CONCESSION 53 REVENUE - OTHER 54 REVENUE - POTENTIAL SUMMER 55 TOTAL REVENUE 56 OPERATING COSTS - SNOW MAKING 57 SUMMER OPERATING COSTS 58 YEARLY WINTER OPERATING COSTS 59 FIXED COSTS 60 TOTAL 61 INCOME BEFORE INTEREST AND TAXES 62 INTEREST EXPENSE 63 INCOME BEFORE TAXES 64 INCOME TAX EXPENSE 65 NET INCOME AFTER TAXES NA NA NA NA NA NA NA NA NA MA NA MA NA ma NA wa NA NA NA NA NA FIGURE 6-5 Income and cash flow statements section . Beginning-of-the-Year Cash on Hand-This value is the cash on hand at the end of the prior year. Revenue - SkiersThis value was computed elsewhere in the spreadsheet and can be echoed here. Revenue - Ski SchoolThis value was computed elsewhere in the spreadsheet and can be echoed here. Revenue - Equipment Rental-This value was computed elsewhere in the spreadsheet and can be echoed here. Revenue - Food Concession-This value was computed elsewhere in the spreadsheet and can be echoed here. Revenue - Other-This value was computed elsewhere in the spreadsheet and can be echoed here. Case 6 Revenue - Potential Summer-If Snow Top decides to include summer activities and therefore operate in the summer, then the value for the revenue is echoed here from the Constants section. If Snow Top chooses not to include summer activities, then the value here is zero. Total RevenueThis amount is the sum of the revenue from the skiers, the ski school, the equipment rental, the food concession, other revenue, and perhaps the summer revenue, if chosen Operating Costs - Snow Making-This value is recorded in the Constants section and echoed here. Summer Operating CostsIf Snow Top chooses to operate in the summer, then the value for the operating costs can be found in the Constants section and echoed here. If Snow Top chooses not to operate in the summer, then this value is zero. Yearly Winter Operating costs-This value is a function of the daily other winter operating costs and the number of ski days per year. Fixed Costs-This value is recorded in the Constants section and echoed here. Total Costs-This is the sum of the operating costs of snow making, the summer operating costs, if chosen, the yearly winter operating costs, and the fixed costs. Income Before Interest and TaxesThis value is the difference between the total revenue and the total costs. Interest ExpenseThis value can be calculated from the interest rate, which is a value in the Constants section, and the debt owed at the beginning of the year. Income Before TaxesThis value is the difference between Income Before Interest and Taxes and the Interest Expense. Income Tax Expense-This value is based on the tax rate, which is a value in the Constants section, and the Income Before Taxes. This value should be calculated only for income that is a positive number. In other words, no tax is applied if the income before taxes is less than zero. Net Income After TaxesThis number is the difference between the Income Before Taxes and the Income Tax Expense. The following values must be computed by cell formula; hard-code numbers in formulas only when you are told to do so. Cell formulas should not reference a cell with a value of "NA." An explanation of each item follows the figure. . . B D E 2019 2020 2021 2022 A 66 67 NET CASH POSITION (NCP) BEFORE BORROWING AND REPAYMENT OF DEBT 68 (BEG OF YR CASH + NET INCOME) 69 ADD:BORROWING FROM BANK 70 LESS: REPAYMENT TO BANK 71 EQUALS: END-OF-YEAR CASH ON HAND 72 NA NA NA 4,000,000 $ FIGURE 6-6 End-of-year cash on hand section Net Cash Position (NCP) Before Borrowing and Repayment of Debt-This amount equals cash at the beginning of the year plus net income after taxes. Add: Borrowing from Bank-Assume that Snow Top can borrow from bankers at the end of the year to reach the minimum cash needed to start the next year, this minimum is a value in the Constants section. If the NCP is less than this minimum, the company would borrow enough to start the next year with the minimum. Borrowing increases cash on hand, of course, Less: Repayment to Bank-The resort will use its excess cash at year's end to pay off as much debt as possible without going below the minimum cash threshold. Excess cash is the NCP minus the minimum cash required on hand. Amounts over the minimum are available to repay any debt. The resort must repay as much as it can if it has any money available. Equals: End-of-Year Cash on Hand-This amount is the NCP plus any bank borrowing minus any repayments. . Debt Owed Section This section shows a calculation of debt owed at the end of a year (see Figure 6-7). Year 2019 values are NA except for End-of-year debt owed, which is $2 million. Values must be computed by cell formula. Cell formulas should not reference a cell with a value of NA. An explanation of each item follows the figure. D E 2020 2021 2022 A 72 73 DEBT OWED 74 BEGINNING-OF-YEAR DEBT OWED 75 ADD: BORROWING FROM BANK 76 LESS: REPAYMENT TO BANK 77 EQUALS: END-OF-THE-YEAR DEBT OWED 78 2019 NA NA NA 2,000,000 $ FIGURE 6-7 Debt Owed section Beginning-of-Year Debt OwedDebt owed at the beginning of a year equals the debt owed at the end of the prior year. Add: Borrowing from BankThis amount has been calculated elsewhere and can be echoed to this section. Borrowing increases the amount of debt owed. Less: Repayment to Bank-This amount has been calculated elsewhere and can be echoed to this section. Repayments reduce the amount of debt owed. Equals: End-of-the-Year Debt OwedThis is the amount owed at the beginning of a year, plus borrowing during the year, minus repayments during the year. .