mob auto the cumulative > Scenarios Scenario 1 3% 27% Scenario 2 3% 26% Scenario 3 1% 31% Inflation Rate Cost of Merchandise (% of sales) Labor Cost Other Expenses First-year sales revenue Sales Growth $275000 $325000 $800000 $200000 $325000 $800000 $275000 $350000 $700000 20% 25% 22% year 2 Sales Growth 18% 18% 17% year 3 Sales Growth 16% 14% 12% year 4 Sales Growth 14% 8% 9% year 5 Print Done Clear All A1 New Store Financial Model x G I D E B 5000 300000 5 10% 34% 2% 30% 4 5 Store Size (square feet) 6 Total Fixed Assets 7 Depreciation period (straight line) 8 Discount Rate 9 Tax Rate 10 Inflation Rate 11 Cost of Merchandise (% of sales) 12 13 Operating Expenses 14 Labor Cost 15 Rent Per Square Foot 16 Other Expenses 17 18 First Year Sales Revenue 19 20 Annual Growth Rate of Sales 21 200000 28 325000 800000 Year 2 20% Year 3 12% Year 4 9% Year 5 5% 5 Year 1 800000 240000 2 960000 288000 3 1075200 322560 4 1171968 351590 1230566 369170 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 32 Net Income Before Tax 33 Income Tax 34 Net After Tax Income 35 Plus Depreciation Expense 36 Annual Cash Flow 37 Discounted Cash Flow 38 Cumulative Discounted Cash Flow 39 200000 140000 325000 -105000 60000 -165000 -56100 -108900 60000 -48900 -44455 44455 204000 142800 331500 -6300 60000 -66300 -22542 43758 60000 16242 13423 -31031 208080 145656 338130 60774 60000 774 263 511 60000 60511 45463 14431 212242 148569 344893 114674 60000 54674 18589 36085 60000 96085 65627 80059 216486 151541 351790 141579 60000 81579 27737 53842 60000 113842 70687 150746 A1 * fx New Store Financial Model G 3 C 5000 300000 3 20% 34% 2x 110 200000 28 325000 800000 Year Year 5 Year 2 20% Year 12% Store Sesuare feet 6 Totaled Assets 7 Depreciation period tracht line Discount Rate 9 Tax Rate 10 Inflation Rate 11 Cost of Merchandise of sales 12 13 Operating Expenses 16 Labor Cost 15 Rent Persuarefoot 16. Other Expenses 17 18 First Year Sales Revenue 19 20 An Growth of Sales 21 22 Model 23 26. Wes Revenue 25 Cost of Merchandise 26 Cerating Expenses 22 Laborcom 26 Rant. Per Souarefoot 19 Other penses 10 Net Operating income 31 Depreciation opens 32 Netcome Before Tax 3 Income Tax Net Aer Twincome 35 Plus Depreciation pense 36 Anna Cash Flow 17 Discounted Cash Flow 38 Cumulative Discounted Cathrow 30 Ver C24 1.020) $0$11.024 02414201 $0$1124 3 -2411-20) $B$15.124 4 -F2414620) SA$11624 $8511C24 585155855 .816 C24C25C2C2B-629 -585675857 C3003: -C125859 -C32.033 . CH4CS 36/113858723 017 C2758510) C284358510) C291058510) 024 025 027 028 029 5056/5852 010-03 0325859 012-033 031 034.035 036/1-5658702) C33012 102734585101 0289/1515101 0291-585101 -224-25-227-28-29 SBS6/5657 00 UI -3258$9 -D2-33 E221-585101 028911058510) 02991 $0$10) 24-F25-427428429 S856/5857 F30-F31 F325899 -F32 F33 31 34735 +36/1 $B$123 .B8F37 F222-58510) -F281585101 F29"(150$10) 24-625-627-628-629 $856/5057 G30 31 G325859 -632-633 1631 -634635 636155623 -F38+637 -4.35 06/14505623 18437 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 is 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 view the New Store Financial Model Spreadsheet with numbers. Click to view the New Store Financial Model Spreadsheet with Excel Functions. Complete the table below. Scenario 1 Scenario 2 Scenario 3 Cumulative Discounted Cash Flow (Round to the nearest Integer as needed.) mob auto the cumulative > Scenarios Scenario 1 3% 27% Scenario 2 3% 26% Scenario 3 1% 31% Inflation Rate Cost of Merchandise (% of sales) Labor Cost Other Expenses First-year sales revenue Sales Growth $275000 $325000 $800000 $200000 $325000 $800000 $275000 $350000 $700000 20% 25% 22% year 2 Sales Growth 18% 18% 17% year 3 Sales Growth 16% 14% 12% year 4 Sales Growth 14% 8% 9% year 5 Print Done Clear All A1 New Store Financial Model x G I D E B 5000 300000 5 10% 34% 2% 30% 4 5 Store Size (square feet) 6 Total Fixed Assets 7 Depreciation period (straight line) 8 Discount Rate 9 Tax Rate 10 Inflation Rate 11 Cost of Merchandise (% of sales) 12 13 Operating Expenses 14 Labor Cost 15 Rent Per Square Foot 16 Other Expenses 17 18 First Year Sales Revenue 19 20 Annual Growth Rate of Sales 21 200000 28 325000 800000 Year 2 20% Year 3 12% Year 4 9% Year 5 5% 5 Year 1 800000 240000 2 960000 288000 3 1075200 322560 4 1171968 351590 1230566 369170 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 32 Net Income Before Tax 33 Income Tax 34 Net After Tax Income 35 Plus Depreciation Expense 36 Annual Cash Flow 37 Discounted Cash Flow 38 Cumulative Discounted Cash Flow 39 200000 140000 325000 -105000 60000 -165000 -56100 -108900 60000 -48900 -44455 44455 204000 142800 331500 -6300 60000 -66300 -22542 43758 60000 16242 13423 -31031 208080 145656 338130 60774 60000 774 263 511 60000 60511 45463 14431 212242 148569 344893 114674 60000 54674 18589 36085 60000 96085 65627 80059 216486 151541 351790 141579 60000 81579 27737 53842 60000 113842 70687 150746 A1 * fx New Store Financial Model G 3 C 5000 300000 3 20% 34% 2x 110 200000 28 325000 800000 Year Year 5 Year 2 20% Year 12% Store Sesuare feet 6 Totaled Assets 7 Depreciation period tracht line Discount Rate 9 Tax Rate 10 Inflation Rate 11 Cost of Merchandise of sales 12 13 Operating Expenses 16 Labor Cost 15 Rent Persuarefoot 16. Other Expenses 17 18 First Year Sales Revenue 19 20 An Growth of Sales 21 22 Model 23 26. Wes Revenue 25 Cost of Merchandise 26 Cerating Expenses 22 Laborcom 26 Rant. Per Souarefoot 19 Other penses 10 Net Operating income 31 Depreciation opens 32 Netcome Before Tax 3 Income Tax Net Aer Twincome 35 Plus Depreciation pense 36 Anna Cash Flow 17 Discounted Cash Flow 38 Cumulative Discounted Cathrow 30 Ver C24 1.020) $0$11.024 02414201 $0$1124 3 -2411-20) $B$15.124 4 -F2414620) SA$11624 $8511C24 585155855 .816 C24C25C2C2B-629 -585675857 C3003: -C125859 -C32.033 . CH4CS 36/113858723 017 C2758510) C284358510) C291058510) 024 025 027 028 029 5056/5852 010-03 0325859 012-033 031 034.035 036/1-5658702) C33012 102734585101 0289/1515101 0291-585101 -224-25-227-28-29 SBS6/5657 00 UI -3258$9 -D2-33 E221-585101 028911058510) 02991 $0$10) 24-F25-427428429 S856/5857 F30-F31 F325899 -F32 F33 31 34735 +36/1 $B$123 .B8F37 F222-58510) -F281585101 F29"(150$10) 24-625-627-628-629 $856/5057 G30 31 G325859 -632-633 1631 -634635 636155623 -F38+637 -4.35 06/14505623 18437 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 is 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 view the New Store Financial Model Spreadsheet with numbers. Click to view the New Store Financial Model Spreadsheet with Excel Functions. Complete the table below. Scenario 1 Scenario 2 Scenario 3 Cumulative Discounted Cash Flow (Round to the nearest Integer as needed.)