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 3 Scenario 1 Scenario 2 Cumulative Discounted Cash Flow (Round to the nearest integer as needed.) 3 Scenarios Scenario 1 3% 26% Scenario 2 5% 30% Scenario 3D 3% 27% Inflation Rate Cost of Merchandise (% of sales) Labor Cost Other Expenses First-year sales revenue Sales Growth $200000 $325000 $120000 $225000 $350000 $600000 $275000 $325000 $800000 25% 22% 20% year 2 Sales Growth 18% 15% 18% year 3 Sales Growth 14% 11% 16% year 4 Sales Growth 8% 5% 14% year 5 Print Done B D E F G A New Store Financial Model Data Year 2 20% Year 3 12% Year 4 9% Year 5 5% Store Size (square feet) 5000 Total Fixed Assets 300000 Depreciation period (straight line 5 3 Discount Rate 10% Tax Rate 34% 0 Inflation Rate 2% -1 Cost of Merchandise (% of sales) 30% 2 13. Operating Expenses 14 Labor Cost 200000 15 Rent Per Square Foot 28 16 Other Expenses 325000 17. 18 First Year Sales Revenue 800000 19 20 Annual Growth Rate of Sales 21 22 Model 23 Year 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 40 41 + 1 800000 240000 960000 1075200 1171968 1230566 288000 322560 351590 369170 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 15154 351790 141579 60000 81579 27737 53842 60000 113842 70687 150746 B D G 1 New Store Financial Model 2 3 Data 4 5 Store Size square feet) 5000 6 Total Fixed Assets 300000 7 Depreciation period straight line) 5 8 Discount Rate 10% 9 Tax Rate 3496 10 Inflation Rate 2% 11 Cost of Merchandise of sales) 30% 12 13 Operating Expenses 14 Labor Cost 200000 15 Rent Per Square Foot 28 16 Other Expenses 325000 17 18 First Year Sales Revenue 800000 19 20 Annual Growth Rate of Sale Year 2 Year 3 Year 4 Year 5 20% 12% 1996 21 SX 22 Model 23 Year 1 24 Sales Revenue B18 0241-020) 25 Cost of Merchandise 0241.520) -E241+F20) of 2011G20) $B$11C24 $B$11024 26 Operating Expenses $B$110E24 $$11F24 -$B$11.624 27 Labor Cost 2014 -C271 $B$10) 28 Rent Per Square Foot -0271-58510) E271 $B$10) F271 SB$10) ESB$15$B$5 C282456$10) 29 Other Expenses -02891 $B$10) -E284158$10) F281-58$10) -B16 30 Net Operating Income -C291 $B$10) -029" 1:$D$10) -E291 $B$ 10) F291 $B$10) C24 C25 C27-028-C29-02-025-027-028-6291 24-25E27-28-E29F24F2527-28-29 31 Depreciation Expense -624-625-627-628 629 $B$6/$$$ LS35675857 $A$6/5857 32 Net Income Before Tax SB$6/$$7 5056/5857 CIO-C31 030-031 33 Income Tax E30 E31 F30-F31 $$9 30 31 34 Net Alter Tax Income -0320$ LE32$0$9 F32SBC9 G3289 -C32-33 35 Du Depreciation Expeme 032 033 32-033 F32 F33 -682-633 LC31 081 36 Annual Cash Flow 031 F31 G31 C3401S 034.35 37 Discounted Cash Flow 14.35 34.135 634.035 C36/ CSS3 3 Cumulative Ow.counted Cash Flow -D3/ISBB/02 36/(1-$85821 F36/(1.50$8AF23 CZ -636 SB58"G22 C38037 01837 19 18:37 M.)2 40