Problem Solving Cases in microsoft access and excel Case 6 the ski resort investment decision. I am having problems with all of it. Could someone please help me out.

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 rever from hiking, mountain biking, and gondola riding. The owners could also install a zip line for added advent to attract more visitors. The owners would like you to finish setting up their budget in an Excel spreadsheet and run diferent outlooks, and climate change. Then, the owners would like you to add projected revenues from the summer scenarios. These scenarios would indicate the effects of changes in the number of skiers, different economie 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 randum that documents your analysis and findings. to 2022. This section helps you set up each of the following spreadsheet components before entering cell Panser you will create the spreadsheet model of the decision. The model covers the three years from 2020 Assignment 2, you will run Scenario Manager twice to explore various financial scenarios and write a memo- 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 E 2022 0.20 S 4,000,000 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 8 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 0.03 100 700 $ $ $ $ 45 2020 0.20 4,000,000 $ 0.03 100 $ 700 s 25 $ 45 $ 25 $ s 200 100 15 $ S 2,000,000 $ 350,000 $ 15.000 $ 35,000,000 $ 2021 0.20 4,000,000 $ 0.03 100 $ 700 $ 25 5 45 $ 2 27 $ 100 15 $ 1,000,000 $ 1,800,000 $ 350,000 $ 15,000 $ 35,000,000 $ NA S $ $ $ $ 100 15 1,000,000 1,800,000 400,000 15.000 35,000,000 FIGURE 6-1 Constants section 115 . day at the resort. day . annual pass Average . will pay for the lesson The Ski Resort Investment Decision Tax Rate The rate at which Snow Top's net income is taxed is shown for each year. million cash at the beginning of each year. Assume that the ski resort could borrow from the Cash Needed to Start Next Year-The local Vermont bank wants Snow Top to have at least 84 bunk 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 sks for an entire Annual Lift Ticket Price-This is the yearly amount (a one-time fee) that a skier will pay for an the ski area. Skiers can then ski as many times as they choose. 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 price they 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 what Snow 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 Daily Other Winter Operating costs-This is the additional daily cost of operating Snow Top, beyond the costs already noted. Fixed CostsThis is the amount of fixed costs per year, this number is not a function of how many days Snow Top is open each year. . . Snow Top expects each day. . . over the summer. Inputs Section Tour spreadsheet should include the following inputs for the years 2020 to 2022, as shown in Figure 6-2. D E B A ALL YRS 2020 NA NA 2021 NA NA 2022 NA NA 20 INPUTS 2 RATE OF CHANGE IN SKIERS 2 ECONOMIC OUTLOOK (Good, Poor) 2 CLIMATE CHANGE FACTOR 2 SUMMER OPERATIONS (Y OR N) NA NA AGURE 6-2 Inputs section . Rate of Change in Skiers-This 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 OperationsIf Snow Top decides to operate in the summer, enter "Y" in this cell. If not, the input is "N." 116 Case 6 Summary of Key Results Section Your spreadsheet should include the results shown in Figure 6-3. D B 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 31 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 spre 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. I. 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. D E B 2020 2021 2022 2019 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 Skiers-This 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 of the rate of change 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 avernee 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. skiers. The rate of Daily Ski School Attendees-This mumber is a function of the number of daily skiers. Assume 117 The Ski Reson Investment Decision 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 nge ski school daily price, and the number of ski days per year. . age daily rental 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 Revenue-This number is a function of the daily equipment rental users, the aver Yearly Food Concession Revenue-This 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 the income and cash flow starts with the cash on hand at the beginning of the year. This value sallowed by the income statement and the calculation of cash on hand at year's end. For readability, format cals in this section as currency with zero decimals. Values must be computed by cell formula; hard-code santhers in fomulas only if you are told to do so. Cell formulas should not reference a cell with a value of A." Jour spreadsheets should look like those in Figures 6-5 and 6-6. A discussion of each item in the section 1- llows each figure B D E 2020 2021 2019 NA 2022 INCOME STATEMENT AND CASH FLOW STATEMENT BEGINNING-OF-THE-YEAR CASH ON HAND REVENUE - SKIERS 51 REVENUE SKI SCHOOL F1 REVENUE - EQUIPMENT RENTAL 32 REVENUE - FOOD CONCESSION REVENUE - OTHER 4 REVENUE - POTENTIAL SUMMER TOTAL REVENUE 58 OPERATING COSTS - SNOW MAKING SUMMER OPERATING COSTS 58 YEARLY WINTER OPERATING COSTS 29 FXED COSTS 60 TOTAL COSTS 8. INCOME BEFORE INTEREST AND TAXES E INTEREST EXPENSE INCOME BEFORE TAXES ANCOME TAX EXPENSE 65 NET INCOME AFTER TAXES NA NA NA NA NA NA NA NA NA NA NA NA 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 - Skiers-This value was computed elsewhere in the spreadsheet and can be echoed here. Revenue - Ski School-This 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 Revenue - Food Concession-This value was computed elsewhere in the spreadsheet and can be be echoed here. echoed here. Revenue - Other-This value was computed elsewhere in the spreadsheet and can be echoed here. 118 . Debt This s exceps V An ex . 72 730 74 76 77 78 Case 6 Revenue - Potential Summer-If Snow Top decides to include summer activities and the rulote 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 Revenue-This amount is the sum of the revenue from the skiers, the ski school, the expect 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 Costs If 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 cose if chosen, the yearly winter operating costs, and the fixed costs. Income Before Interest and Taxes-This 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 Taxes-This 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 Taxes-- This 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. FIGL . . . . A OD (2 B c D E n2 2019 2020 2021 2022 66 67 NET CASH POSMION (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 lland-This amount is the NCP plus any bank borrowing minus ry . repayments. 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 rever from hiking, mountain biking, and gondola riding. The owners could also install a zip line for added advent to attract more visitors. The owners would like you to finish setting up their budget in an Excel spreadsheet and run diferent outlooks, and climate change. Then, the owners would like you to add projected revenues from the summer scenarios. These scenarios would indicate the effects of changes in the number of skiers, different economie 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 randum that documents your analysis and findings. to 2022. This section helps you set up each of the following spreadsheet components before entering cell Panser you will create the spreadsheet model of the decision. The model covers the three years from 2020 Assignment 2, you will run Scenario Manager twice to explore various financial scenarios and write a memo- 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 E 2022 0.20 S 4,000,000 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 8 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 0.03 100 700 $ $ $ $ 45 2020 0.20 4,000,000 $ 0.03 100 $ 700 s 25 $ 45 $ 25 $ s 200 100 15 $ S 2,000,000 $ 350,000 $ 15.000 $ 35,000,000 $ 2021 0.20 4,000,000 $ 0.03 100 $ 700 $ 25 5 45 $ 2 27 $ 100 15 $ 1,000,000 $ 1,800,000 $ 350,000 $ 15,000 $ 35,000,000 $ NA S $ $ $ $ 100 15 1,000,000 1,800,000 400,000 15.000 35,000,000 FIGURE 6-1 Constants section 115 . day at the resort. day . annual pass Average . will pay for the lesson The Ski Resort Investment Decision Tax Rate The rate at which Snow Top's net income is taxed is shown for each year. million cash at the beginning of each year. Assume that the ski resort could borrow from the Cash Needed to Start Next Year-The local Vermont bank wants Snow Top to have at least 84 bunk 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 sks for an entire Annual Lift Ticket Price-This is the yearly amount (a one-time fee) that a skier will pay for an the ski area. Skiers can then ski as many times as they choose. 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 price they 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 what Snow 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 Daily Other Winter Operating costs-This is the additional daily cost of operating Snow Top, beyond the costs already noted. Fixed CostsThis is the amount of fixed costs per year, this number is not a function of how many days Snow Top is open each year. . . Snow Top expects each day. . . over the summer. Inputs Section Tour spreadsheet should include the following inputs for the years 2020 to 2022, as shown in Figure 6-2. D E B A ALL YRS 2020 NA NA 2021 NA NA 2022 NA NA 20 INPUTS 2 RATE OF CHANGE IN SKIERS 2 ECONOMIC OUTLOOK (Good, Poor) 2 CLIMATE CHANGE FACTOR 2 SUMMER OPERATIONS (Y OR N) NA NA AGURE 6-2 Inputs section . Rate of Change in Skiers-This 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 OperationsIf Snow Top decides to operate in the summer, enter "Y" in this cell. If not, the input is "N." 116 Case 6 Summary of Key Results Section Your spreadsheet should include the results shown in Figure 6-3. D B 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 31 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 spre 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. I. 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. D E B 2020 2021 2022 2019 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 Skiers-This 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 of the rate of change 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 avernee 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. skiers. The rate of Daily Ski School Attendees-This mumber is a function of the number of daily skiers. Assume 117 The Ski Reson Investment Decision 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 nge ski school daily price, and the number of ski days per year. . age daily rental 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 Revenue-This number is a function of the daily equipment rental users, the aver Yearly Food Concession Revenue-This 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 the income and cash flow starts with the cash on hand at the beginning of the year. This value sallowed by the income statement and the calculation of cash on hand at year's end. For readability, format cals in this section as currency with zero decimals. Values must be computed by cell formula; hard-code santhers in fomulas only if you are told to do so. Cell formulas should not reference a cell with a value of A." Jour spreadsheets should look like those in Figures 6-5 and 6-6. A discussion of each item in the section 1- llows each figure B D E 2020 2021 2019 NA 2022 INCOME STATEMENT AND CASH FLOW STATEMENT BEGINNING-OF-THE-YEAR CASH ON HAND REVENUE - SKIERS 51 REVENUE SKI SCHOOL F1 REVENUE - EQUIPMENT RENTAL 32 REVENUE - FOOD CONCESSION REVENUE - OTHER 4 REVENUE - POTENTIAL SUMMER TOTAL REVENUE 58 OPERATING COSTS - SNOW MAKING SUMMER OPERATING COSTS 58 YEARLY WINTER OPERATING COSTS 29 FXED COSTS 60 TOTAL COSTS 8. INCOME BEFORE INTEREST AND TAXES E INTEREST EXPENSE INCOME BEFORE TAXES ANCOME TAX EXPENSE 65 NET INCOME AFTER TAXES NA NA NA NA NA NA NA NA NA NA NA NA 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 - Skiers-This value was computed elsewhere in the spreadsheet and can be echoed here. Revenue - Ski School-This 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 Revenue - Food Concession-This value was computed elsewhere in the spreadsheet and can be be echoed here. echoed here. Revenue - Other-This value was computed elsewhere in the spreadsheet and can be echoed here. 118 . Debt This s exceps V An ex . 72 730 74 76 77 78 Case 6 Revenue - Potential Summer-If Snow Top decides to include summer activities and the rulote 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 Revenue-This amount is the sum of the revenue from the skiers, the ski school, the expect 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 Costs If 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 cose if chosen, the yearly winter operating costs, and the fixed costs. Income Before Interest and Taxes-This 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 Taxes-This 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 Taxes-- This 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. FIGL . . . . A OD (2 B c D E n2 2019 2020 2021 2022 66 67 NET CASH POSMION (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 lland-This amount is the NCP plus any bank borrowing minus ry . repayments