i newd to cumulative discounted cash flow for scenario 1,2, and 3

Think of any retailer that operates many stores throughout the country. The retailer is often seeking to open new stores and needs to evaluate the profitability of a proposed location that would be leased for five years. An Excel model in provided in the New Store Financial Model spreadsheet. Use the Scenario Manager to evaluate the cumulative discounted cash flow for the fifth year under the scenarios. Click to view the scenarios Click to viw the New Store Finance Model Spreadsheet with numbers Click to view the New Stor Financial Model Somadsheet with Excel Functions Complete the table below Scenario 1 Scenario 2 Scenario 3 Cumulative Discounted Cash Flow (Round to the nearest Integer as needed) Scenario 1 3% 27% Scenario 2 5% 23% Scenario 3D 1% 25% $275000 $325000 $800000 $210000 $310000 $900000 $150000 $300000 $600000 nel Inflation Rate Cost of Merchandise % of sales) Labor Cost Other Expenses First-year sales revenue Sales Growth year 2 Sales Growth year 3 Sales Growth year 4 Sales Growth year 5 20% 16% 15% 18% 15% 10% 16% 11% 6% 14% 7% 3% E F G o A B New Store Financial Model 2 3 Data 4 Store Size (square 5 feet) 5000 6 Total Fixed Assets 300,000 Depreciation period 7 (straight line) 5 8 Discount Rate 10% 9 Tax Rate 34% 10 Inflation Rate Cost of Merchandise 11 (% of sales) 30% 12 13 Operating Expenses 14 Labor Cost200,000 15 Rent Per Square Foot28 16 Other Expenses 325,000 17 First Year Sales 2% AAAAA 5% First Year Sales 18 800,000 Revenue 19 Year 2 Year 3 Year 4 Year 5 20 Annual Growth Rate of Sales 20% 12% 9% 21 22 Model 23 Year 2 3 4 5 24 Sales Revenue 800000 960000 1075200 1171968 1230566 25 Cost of Merchandise 240000 288000 322560 351590 369170 26 Operating Expenses 27 Labor Cost 200000 204000 208080 212242 216486 28 Rent Per Square Foot 140000 142800 145656 148569 151541 29 Other Expenses 325000 331500 338130 344893 351790 30 Net Operating Income - 105000 -6300 60774 114674 141579 31 Depreciation Expense 60000 60000 60000 60000 60000 Net Income Before 32 165000 - 66300 774 54674 Tax 81579 33 Income Tax -56100 - 22542 263 18589 27737 34 Net After Tax Income -- 108900 - 43758 511 36085 53842 35 Plus Depreciation 60000 60000 60000 60000 60000 Expense 36 Annual Cash Flow 48900 16242 60511 96085 113842 20% 12% 9% 5% e rel Year 1 800000 240000 2 960000 288000 3 1075200 322560 ari 1171968 351590 5 1230566 369170 ceg Annual Growth Rate 20 of Sales 21 22 Model 23 24 Sales Revenue 25 Cost of Merchandise 26 Operating Expenses 27 Labor Cost 28 Rent Per Square Foot 29 Other Expenses 30 Net Operating Income 31 Depreciation Expense Net Income Before 32 Tax 33 Income Tax 34 Net After Tax Income Plus Depreciation 35 Expense 36 Annual Cash Flow 37 Discounted Cash Flow Cumulative 38 Discounted Cash Flow 200000 140000 325000 - 105000 60000 204000 142800 331500 -6300 60000 208080 145656 338130 60774 60000 212242 148569 344893 114674 60000 216486 151541 351790 141579 60000 165000 - 66300 774 54674 81579 56100 108900 - 22542 - 43758 263 511 18589 36085 27737 53842 60000 60000 60000 60000 60000 48900 44455 16242 13423 60511 45463 96085 65627 113842 70687 -44455 - 31031 14431 80059 150746