1. Activate the "Sensitivity" worksheet. Type a formula in cell "B7" that calculate the Profit. Use the formula in cell A21. 2. Create a Data Table that shows the sensitivity of the profit to ADR (top low) and Rooms Sold (left column). 3. What would Rooms Sold need to be in order to reach $0 in profit? Use Solver to determine the value in Rooms Sold. The final value in Profit (B7) should be 0. 4. Open the "Profit" worksheet. Please answer the following question What would Unit Sales need to be to reach $5 million in Profit? Use Solver to determine the value in Unit Sales. The final value in Profit (B12) should be $5,000,000. (Answer in cell location "B15"). 5. Open the NPV worksheet. Here are the problems. Suppose a firm is considering the following new project, where all of the dollar figures are in thousands of dollars. a. In year 0, the project requires $11,350 investment in hotel expansion is depreciated using the straight-line method over seven years, and there is a salvage value of $1,400 in year 7. 6. The project is forecast to generate sales of 2,100 unit in year 1 and grow at a sales growth rate of 55.0% in year c. The sales growth rate is forecast to decline by 15.0% in years 3 and 4, to decline by 20.0% in year 5. to decline by 25.0% in year 6. to decline by 30.0% in year 7. Inflation rate is forecast to be 2.0% in year 1, rising to 4.0% in year 5, and then leveling off The real cost of capital is forecast to be 11.0% in year 1. rising to 12.2% in year 5, and then leveling off. f. The tax rate is forecast to be a constant 35.0%. Sales revenue per unit is forecast to be $9.70 in year 1and then grow with inflation. h. Variable cost per unit is forecast to be $7.40 in year 1 and then grow with inflation. i Cash fixed cost is forecast to be $5,280 in year 1 and then grow with inflation. 6. Please answer the following questions on the "NPV worksheet a. What is the NPV Break-Even Point in the Year 1 Unit Sales and the Year 2 Sales Growth Rate, where NPV equals zero? (Answer in cell locations "C16" and "DS). Use Solver to determine the values. The final value in NPV (B41) should be 0. b. What is the NPV Break-Even Contour in the two-dimensional space of Year 1 Unit Sales and Year2 Sales Growth Rate? (Answer in cell locations B47:F52). Profit Table for a Hotel ADR Rooms Sold Variable Cost Fixed Cost $134.71 20,167 $100.00 $700,000 Profit $110.00 $120.00 $130.00 $140.00 $150.00 $160.00 $170.00 10,000 15,000 20,000 25.000 30,000 35,000 40,000 45,000 50,000 55,000 60,000 Profit = (ADR*Rooms Sold) - ((Variable cost*Rooms Sold) + Fixed Cost) Inputs Fixed Costs Sales Revenue / Unit Variable Costs / Unit $500,000 $200.00 $150.00 Profit analysis Unit Sales Sales Revenue Variable Costs Gross Margin Fixed Costs Profit 50,000 $10,000,000 $7,500,000 $2,500,000 $500,000 $2,000,000 Questin #8 Answer Unit Sales to reach $5 million profit A Based On NPV 3 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 BREAK-EVEN ANALYSIS 2 (in thousands of $) Year 0 4 Key Assumptions 5 Sales Growth Rate 6 Change in Sales Growth Rate 7 Inflation Rate 8 Real Cost of Capital 9 Tax Rate 10 11 Discounting 12 Discount Rate = Cost of Capital 13 Cumulative Discount Factor 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 14 0 $0.00 $0.00 0 $0.00 $0.00 $0 0 $0.00 $0.00 0 $0.00 $0.00 $0 0 $0.00 $0.00 0 $0.00 $0.00 $0 $0 $0 $0 $0 $0 15 Price or Cost / Unit 16 Unit Sales 17 Sales Revenue / Unit 18 Variable Cost / Unit 19 Cash Fixed Costs 20 21 22 Cash Flow Forecasts 23 Sales Revenue 24 Variable Costs 25 Gross Margin 26 27 Cash Fixed Costs 28 Depreciation 29 Total Fixed Costs 30 31 Operating Profit 32 Taxes 33 Net Profit 34 35 Add Back Depreciation 36 Operating Cash Flow 37 38 Investment in Hotel Expansion 39 Cash Flows 40 Present Value of Each Cash Flow 41 NPV $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 50 $0 $0 $0 $0 $0 $0 $0 $0 $0 SO $0 43 44 Data Table: Sensitivity of Net Present Value to Year 1 Unit Sales 45 and Year 2 Sales Growth Rate 46 Output Formula: Input Values for Year 1 Unit Sales 47 Net Present Value 400 800 1,600 3,200 48 10.0% 49 Input Values for Year 2 30.0% 50 Sales Growth Rate 45.0% 55.0% 60.0% Sensitivity Profit NPV