SKI Resort Scenario and Formula help

Percentage Change in Daily SkiersThis 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 years number of daily skiers. Number of Season SkiersThis number is a function of the rate of change in skiers. The rate of change in skiers is applied to the prior year. Yearly Total Skier RevenueThis 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 pr

Daily Ski School AttendeesThis 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 RevenueThis 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 UsersThis 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 RevenueThis number is a function of the other daily revenue and the number of ski days per year

Beginning-of-the-Year Cash on HandThis 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 RentalThis value was computed elsewhere in the spreadsheet and can be echoed here. Revenue Food ConcessionThis value was computed elsewhere in the spreadsheet and can be echoed here. Revenue OtherThis value was computed elsewhere in the spreadsheet and can be echoed her

Revenue Potential SummerIf 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 MakingThis 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 CostsThis value is a function of the daily other winter operating costs and the number of ski days per year. Fixed CostsThis value is recorded in the Constants section and echoed here. Total CostsThis 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 ExpenseThis 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.

Net Cash Position (NCP) Before Borrowing and Repayment of DebtThis amount equals cash at the beginning of the year plus net income after taxes. Add: Borrowing from BankAssume 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 BankThe resort will use its excess cash at years 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 HandThis amount is the NCP plus any bank borrowing minus any repayments

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 BankThis 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.

Ski Resort Investment Decision 2019 NA NA 2022 0.20 4,000,000 0.03 100 700 NA NA Constants TAX RATE CASH NEEDED TO START NEW YEAR INTEREST RATE ON DEBT DAILY LIFT TICKET PRICE ANNUAL LIFT TICKET PRICE AVERAGE DAILY MONEY SPENT ON FOOD AVERAGE SKI SCHOOL DAILY PRICE AVERAGE DAILY RENTAL PRICE NUMBER OF SKI DAYS PER YEAR OTHER DAILY REVENUE SUMMER POTENTIAL REVENUE OPERATING COSTS - SNOW MAKING OPERATIONG COSTS - SUMMER DAILY OTHERWINTER OPERATING COSTS FIXED COSTS 2020 0.20 4,000,000 $ 0.03 100 $ 700 $ 25 $ 45 $ 25 $ 100 15 $ 25 NA NA NA NA 2021 0.20 4,000,000 $ 0.03 100 $ 700 $ 25 $ 45 $ 27 $ 100 15 $ 1,000,000 $ 1,800,000 $ 350,000 $ 15,000 $ 35,000,000 $ 45 30 100 15 2,000,000 $ 350,000 $ 15,000 $ 35,000,000 $ 1,000,000 2,000,000 400,000 15,000 35,000,000 ALL YRS 2019 2020 2022 NA NA Inputs RATE OF CHANGE IN SKIERS ECONOMIC OUTLOOK (Good, Poor) CLIMATE CHANGE FACTOR SUMMER OPERATIONS (Y OR N) 2021 NA NA NA NA NA NA Poor NA NA Y ALL YRS 2019 2020 2021 2022 SUMMARY OF KEY RESULTS NET INCOME AFTER TAXES END-OF-THE-YEAR CASH ON HAND END-OF-THE-YEAR DEBT OWED PROFIT MARGIN 2020 2021 2022 DEBT OWED BEGINNING-OF-YEAR DEBT OWED ADD: BORROWING FROM BANK LESS: REPAYMENT TO BANK EQUALS: END-OF-THE-YEAR DEBT OWED 2019 NA NA NA 2,000,000 $ 2019 2020 2021 2022 3000 500 Calculations PERCENTAGE CHANGE IN DAILY SKIERS NUMBER OF DAILY SKIERS NUMBER OF SEASON SKIERS YEARLY TOTAL SKIER REVENYE DAILY SKI SCHOOL ATTENDEES YEARLY SKI SCHOOL REVENUE DAILY EQUIPMENT RENTAL USERS YEARLY RENTAL REVENUE YEARLY FOOD CONCESSION REVENUE OTHER YEARLY REVENUE NA NA NA Income Statement and Cash Flow Statement BEGINNING OF THE YEAR CASH ON HAND REVENUE-SKIERS REVENUE SKI SCHOOL REVENUE-EQUIPMENT RENTAL REVENUE-FOOD CONCESSION REVENUE-OTHER REVENUE-POTENTIAL SUMMER TOTAL REVENUE OPERATING COSTS - SNOW MAKING SUMMER OPERATING COSTS YEARLY WINTER OPERATING COSTS FIXED COSTS TOTAL COSTS INCOME BEFORE INTEREST AND TAXES INTEREST EXPENSE INCOME BEFORE TAXES INCOME TAX EXPENSE NET INCOME AFTER TAXES NA NA NA NA NA NA NA NA NA NA NA NA NA NA Ski Resort Investment Decision 2019 NA NA 2022 0.20 4,000,000 0.03 100 700 NA NA Constants TAX RATE CASH NEEDED TO START NEW YEAR INTEREST RATE ON DEBT DAILY LIFT TICKET PRICE ANNUAL LIFT TICKET PRICE AVERAGE DAILY MONEY SPENT ON FOOD AVERAGE SKI SCHOOL DAILY PRICE AVERAGE DAILY RENTAL PRICE NUMBER OF SKI DAYS PER YEAR OTHER DAILY REVENUE SUMMER POTENTIAL REVENUE OPERATING COSTS - SNOW MAKING OPERATIONG COSTS - SUMMER DAILY OTHERWINTER OPERATING COSTS FIXED COSTS 2020 0.20 4,000,000 $ 0.03 100 $ 700 $ 25 $ 45 $ 25 $ 100 15 $ 25 NA NA NA NA 2021 0.20 4,000,000 $ 0.03 100 $ 700 $ 25 $ 45 $ 27 $ 100 15 $ 1,000,000 $ 1,800,000 $ 350,000 $ 15,000 $ 35,000,000 $ 45 30 100 15 2,000,000 $ 350,000 $ 15,000 $ 35,000,000 $ 1,000,000 2,000,000 400,000 15,000 35,000,000 ALL YRS 2019 2020 2022 NA NA Inputs RATE OF CHANGE IN SKIERS ECONOMIC OUTLOOK (Good, Poor) CLIMATE CHANGE FACTOR SUMMER OPERATIONS (Y OR N) 2021 NA NA NA NA NA NA Poor NA NA Y ALL YRS 2019 2020 2021 2022 SUMMARY OF KEY RESULTS NET INCOME AFTER TAXES END-OF-THE-YEAR CASH ON HAND END-OF-THE-YEAR DEBT OWED PROFIT MARGIN 2020 2021 2022 DEBT OWED BEGINNING-OF-YEAR DEBT OWED ADD: BORROWING FROM BANK LESS: REPAYMENT TO BANK EQUALS: END-OF-THE-YEAR DEBT OWED 2019 NA NA NA 2,000,000 $ 2019 2020 2021 2022 3000 500 Calculations PERCENTAGE CHANGE IN DAILY SKIERS NUMBER OF DAILY SKIERS NUMBER OF SEASON SKIERS YEARLY TOTAL SKIER REVENYE DAILY SKI SCHOOL ATTENDEES YEARLY SKI SCHOOL REVENUE DAILY EQUIPMENT RENTAL USERS YEARLY RENTAL REVENUE YEARLY FOOD CONCESSION REVENUE OTHER YEARLY REVENUE NA NA NA Income Statement and Cash Flow Statement BEGINNING OF THE YEAR CASH ON HAND REVENUE-SKIERS REVENUE SKI SCHOOL REVENUE-EQUIPMENT RENTAL REVENUE-FOOD CONCESSION REVENUE-OTHER REVENUE-POTENTIAL SUMMER TOTAL REVENUE OPERATING COSTS - SNOW MAKING SUMMER OPERATING COSTS YEARLY WINTER OPERATING COSTS FIXED COSTS TOTAL COSTS INCOME BEFORE INTEREST AND TAXES INTEREST EXPENSE INCOME BEFORE TAXES INCOME TAX EXPENSE NET INCOME AFTER TAXES NA NA NA NA NA NA NA NA NA NA NA NA NA NA