Need help With only Assignment 1. Everything is explained in the pictures i have posted. Read through it and you will see where assignment 1 is. It says to produce a spreadsheet that models Snow Top's Financial situation.

CASE THE SKI RESORT INVESTMENT DECISION Decision Support Using Microsoft Excel PREWEW 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. PREPARKHON a Review spreadsheet concepts discussed in class and in your textbook. - Complete any exercises that your instructor assigns. - Complete any part of Tutor-la] 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 filesaving 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, 3 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 prot 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 pcopie 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 ea st coast. of the United States has seen higher temperatures and fluctuations in the amount of snowfall recently. For ex ample, \\iermo at 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 income to Spend an lift tickets, rentals, food, tramportation, 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 conrpared 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 ights 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. A B C D E WN SKI RESORT INVESTMENT DECISION CONSTANTS 2020 2021 2022 4 TAX RATE NA 0.20 0.20 0.20 5 CASH NEEDED TO START NEXT YEAR NA 5 4,000,000 5 4,000,000 5 4,000,000 6 INTEREST RATE ON DEBT NA 0.03 0.03 0.03 7 DAILY LIFT TICKET PRICE NA 100 $ 100 $ 100 ANNUAL LIFT TICKET PRICE NA 700 $ 700 $ 700 AVERAGE DAILY MONEY SPENT ON FOOD NA 25 5 25 $ 25 10 AVERAGE SKI SCHOOL DAILY PRICE NA 45 5 45 5 45 11 |AVERAGE DAILY RENTAL PRICE NA 25 $ 27 5 30 12 NUMBER OF SKI DAYS PER YEAR 100 100 100 13 OTHER DAILY REVENUE NA 15 $ 15 5 15 14 SUMMER POTENTIAL REVENUE 1,000,000 1,000,000 15 OPERATING COSTS - SNOW MAKING 2,000,000 1,800.000 1,800,000 16 |OPERATING COSTS - SUMMER 350,000 350,000 400,000 17 DAILY OTHER WINTER OPERATING COSTS 15,000 15,000 15,000 8 FIXED COSTS 35,000,000 5 35,000,000 35,000,000 19 FIGURE 6-1 Constants sectionI lie am nubul |. u Ivublilrutll UUUIaIUI' ' Tax RateTim rate at Which Snow Top's net income is taxed is shown for each year. . Cash Needed to Start Next YearJl'be local Vermont bank wants Snow Top to have at least 154 million cash at the beginning of each year. Assume that the ski resort could borrow from the bank at the and of a year in order to begin the new year with the needed amount. - Interest Rate on DebtThis 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 l'iegi'nnirtgJ of the year. I Daily Lift Ticket PriceThis is the amount that Snow Top charges a customer to ski for an entire day at the resort. 0 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. 0 Average Daily Money Spent on FoodThis 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 Price1f skiers rent equipment, such as boots, poles, skis, and helmets, this is the average price they will patr for one day's rental. 0 Number of Ski Days per YearThis is the average number of days in Vermont's ski season. Snow Top is open for business for this number of days per year. I Other Daily RevenuewSkiers 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 \"Pep expects each day. 0 Summer Potential RevenueIf Snow Top decides to open for the summer and develop activities to attract visitors, it can expect this amount of revenue from the activities. 0 Operating Costs - Snow MakingSnow 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 efcient, so the costs are not expected to increase. 0 Operating Costs - SummerThese are the expected costs that Snow Top will incur if it operates over the summer. I Daily Other Winter Operating CostsThis is the additional daily cost of operating Snow Top, beyond the costs already noted. ' 9 Fixed CostsThis is the amount of xed 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. 19:! 20 @3115 21 RATE OF CHANGE IN SKIERS 2; iECONOMIC omtoos (Good. Poor} ZEJCLIMATE CHANGE moron 21: SUMMER Opesanons [Y on N} 25 ! FIGURE 6-2 Inputs section II Rate of Change in SkiersThis percentage could be positive or negative, depending on how the resort thinks the number of future skiers will change. 0 Economic OutlookIf 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 FactorThis percentage factor, ex pressed as a negative number, indicates the severity of climate change and its effect on the number of skiers. - Summer Operations1f Snow TOP decides to Operate In the summer, enter \"Y\" in this cell. If not, the input is \"N.\" I u Case 5 Summary of Key Results Section Your spreadsheet should include the results shown in Figure 6-3- rn D m fit. A 3 B _. L._..._ mm critter INcoNtE AFTER Taxes are lemme-meme CASH oN HAND glamourmews: neat owes 3e iF'ROFIT men .311 FIGURE ea 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 he 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. Ccll 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 gure. ..: . A 31 324W 3t_;PERt:ENTAGE MGE IN DAILY SKiERS __3-i NUMBER OF EMILY SKIERS as NUMBER or SEASON SKIERS 36_l_YEAFtLY TOTAL SKIER REVENUE 3ft imrt'r SKI SCHOOL nTi'ENDEES ss'IYEARtY SKI SCHOOL REVENUE as new EQUIPMENT RENI'AL USERS 43..er RENTAL REVENUE 41 items FOOD CONCESSION REVENUE 4; 'OTHER YEARLY REVENUE 43 FIGURE 6-4 Calculations section I 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 sitters 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. 0 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 Shier 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 price. "F \"P'm'h- - The Ski Resort investment Decision - Daily Ski School Attendeesif his 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. a Daily Equipment Rental UsersThis number is E'S percent of the daily number of skiers. 0 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. II Yearly Food Concession Revenue'i'his 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. 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 he computed by cell formula; hardcode 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 gure. -' '..- A . B i Qua...\" 2...... @Ec... m 451 \"HOME STATEMENT AND ts jggsa FLOW STATEHIEIIT gm: m 21,121 23;; _4?_|iBEGINNINGOF-THE-YEAR CASH ON HAND NA 46 as j REVENUE - SKIERS NA 59 IREVENUE - SKi SCHOOL NA 51 EREVENLJE EQUIPMENT RENTAL NA 5;} REVENUE - FOOD CDNCESSiDN NA 53; REVENUE - OTHER NA 54} REVENUE - POTENTIAL SUMMER Ma 55 |TorAL REVENUE NA 551 OPERATING COSTS - SNow WING NA srysumuea OPE RARNG COSTS NA sserARLY WINTER OPERATTNG cosrs H 593mm COSTS NA stTOTAL cosrs NA si__;INOOME BEFORE INTEREST AND TAxES ttA 62_!INTEREST EXPENSE to. asfmcome BEFORE TAXES NA 64 iINcOME TAX EXPE NSE NA 65"?NET INCOME AFTER TAXES mi FIGURE 6-5 Income and cash flow statements section - Beginning-of-the-Year Cash on HandThis value is the cash on hand at the end of the prior year. I Revenue - SkiersThis value was computed elsewhere in the spreadsheet and can be echoed here. 0 Revenue - Ski SchoolThis value was computed elsewhere in the spreadsheet and can he echoed here. 0 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 els ewhere in the spreadsheet and can be echoed here. Case 6 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 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 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 'I'axe sThis number is the difi'crcnce 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 formulae should not reference a cell with a value of \"NA." An explanation of each item follows the gure. ' NET CASH POSFTION {woe} BEFORE Jeonnmvmo AND REPAYMENT or near GEEI'BEG OF YR CASH + NEF INCOME} NA QJADDLBORROWlNG FROM BANK HA TC\" LESS: REPAYMENT TO BANK NA T1_;EQUAL5: END-OF-YEAR CASH {IN HAND 5 4.000.000 3'21 FIGURE 6-6 End-oiyear cash on hand section Net Cash Position (NC?) Before Borrowing and Repayment of DebtT his 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 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 HOP 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-ofYear Cash on HandThis amount is the NCP plus any bank borrowing minus any repayments. Debt Owed Section This section shows a calculation oi'dobt owed at the end of a year (see Figure 6-?) Year 2019 values are NA except for Endofyear debt owed, which is 52 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. : _.__ We EL, W _ ._C -, on D e E, T2. 73: DEBT D m M ' M M by BEGINNING-OF-YEAR DEBT oweo NA rs; ADD; aomowmc FROM BANK NA 201L555: REPAYMENT TO BANK NA rrgeoupts: END-0F~THE-YEAR DEBT oweo 5 2.000.000 r0. FlGURE 6-? Debt Owed section I Begllming-Df-Year Debt Owedeebt 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. 0 Equals: End-of-theYear Debt OwedT his is the amount owed at the beginning of a year, plus borroMng during the year, minus repayments during the year