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.) Scenario 1 1% 25% Scenario 2 4% 20% Scenario 3 5% 30% Inflation Rate Cost of Merchandise % of sales) Labor Cost Other Expenses First-year sales revenue Sales Growth $150000 $300000 $600000 $190000 $250000 $900000 $225000 $350000 $600000 15% 20% 22% year 2 Sales Growth 10% 16% 15% year 3 Sales Growth 6% 11% 11% year 4 Sales Growth 3% 7% 5% year 5 Now Store 1 Financial Modal 3 Data Store Size 5000 (square foot) Total Fixed Asants 300,000 Depreciation 7 period (straight ino) 8 Discount Rate 10% 9 Tax Rate 34% 10 inflation Rate 2% Cost of 11 Merchandise 30% (% of sales) 12 13 Operating Expenses 14 Labor Cost200,000 15 Square Foot 16 Other 325,000 Expenses Rent Per 26 First Year 18 Sales Revenue 800,000 Year 2 Year 3 Year 4 12% Year 5 994 20% 5% 19 20 Annual Growth Rate of Sales 21 22 Model 23 24 Sales Revenue 25 Cost of Merchandise Year 1 -B18 2 =C24*(1+020) =$B$11'D24 3 =D24"(1+E20) =SBS11E24 4 =E24"(1+F20) $B$11*F24 5 =F24"(1+G20) =$B$11"G24 $B$11C24 26 Operating Expenses 27 Labor Cost 28 Rent Per Square Foot Other 29 Expenses 30 Net Operating Income 31 Depreciation Expense Net Income 32 Before Tax 33 Income Tax 34 Net After Tax Income =B14 =C27*(1+$B$10) -D27"(1+$B$10) =E27"(1+$B$10) =F27"(1+$B$10) $B$150$B$5 =C28*(1+$B$10) =D28"(1+$B$10) =E28*(1+$B$10) =F28*(1+$B$10) -B16 -C29"(1+$B$10) =D29*(1+$8510) =E29*(1+SBS10) =F29*(1+$B$10) =C24-C25-C27-C28-C29-D24-D25-D27-D28-029-E24-25-E27-E28-E29-F24-F25-F27-F20-F29-624-G25-G27-G28-G29 =$B$6/$B$7 =SBS6/SBS7 -$B$6/$B$7 =$B$6/$B$7 $B$6/$B$7 030-031 -E30-E31 =C30-C31 -C32*$B$9 =C32-C33 =D32"$B$9 E32"$B$9 -E32-E33 -F30-F31 F32"$B$9 -F32 F33 =G30-G31 =G32"$B$9 =G32-G33 =D32-D33 Plus YTYWY =$B$11'C24 -$B$11D24 EETTEV -$B$11'F24 =$B$11'E24 -$B$11"G24 VECIN Cost of 25 Merchandise 26 Operating Expenses 27 Labor Cost 28 Rent Per Square Foot 29 Other Expenses 30 Net Operating =B14 =C27*(1+$B$10) =627"(1+$B$10) =E27"(1+$B$10) =F27"(1+$B$10) $B$16'$B$5 =C28*(1+$B$10) -D28*(1+$B$10) -E28"(1+$B$10) =F28"{1+$8510) =B16 -C29"(1+$B$10) =D29*(1+$B$10) -E29"(1+$B$10) =F29"(1+$B$10) -C24-C25 C27-C28-C29-024-D25-D27-028 029 -E24 E25-E27-E28-E29=F24-F25-F27-F20-F29-G24-G25-G27-G28-G29 =$B$6/SB$7 $B$6/$B$7 =$B$6/$B$7 $B$6/$B$7 $B$6/$B$7 Income 31 Depreciation =D30 31 C30-031 -C32"$B$9 C32 C33 -E30 E31 -E32'58$9 E32 E33 D32'$859 032 033 F30-F31 F32"$859 #F32-F33 +630-631 *G32"$B$9 G32-633 Expense Not Income 32 Before Tax 33 Income Tax Net Alter Tax 34 Income Plus 35 Depreciation Experts Annual Cash 36 Flow Discounted Cash Flow Cumulative 38 Discounted Cash Flow C31 031 -E31 -F31 G31 =C34+C35 -D34+035 -E34 E35 F34 F35 +234+G35 37 -C36/(1+5858) C23 =D36/(1+$B$8Y023 =E36/(1+$858E23 F36/(1+$858F23 -G36/(1+$B$8G23 -C37 -C38 037 -D38-E37 -E38+F37 -F38+G37 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.) Scenario 1 1% 25% Scenario 2 4% 20% Scenario 3 5% 30% Inflation Rate Cost of Merchandise % of sales) Labor Cost Other Expenses First-year sales revenue Sales Growth $150000 $300000 $600000 $190000 $250000 $900000 $225000 $350000 $600000 15% 20% 22% year 2 Sales Growth 10% 16% 15% year 3 Sales Growth 6% 11% 11% year 4 Sales Growth 3% 7% 5% year 5 Now Store 1 Financial Modal 3 Data Store Size 5000 (square foot) Total Fixed Asants 300,000 Depreciation 7 period (straight ino) 8 Discount Rate 10% 9 Tax Rate 34% 10 inflation Rate 2% Cost of 11 Merchandise 30% (% of sales) 12 13 Operating Expenses 14 Labor Cost200,000 15 Square Foot 16 Other 325,000 Expenses Rent Per 26 First Year 18 Sales Revenue 800,000 Year 2 Year 3 Year 4 12% Year 5 994 20% 5% 19 20 Annual Growth Rate of Sales 21 22 Model 23 24 Sales Revenue 25 Cost of Merchandise Year 1 -B18 2 =C24*(1+020) =$B$11'D24 3 =D24"(1+E20) =SBS11E24 4 =E24"(1+F20) $B$11*F24 5 =F24"(1+G20) =$B$11"G24 $B$11C24 26 Operating Expenses 27 Labor Cost 28 Rent Per Square Foot Other 29 Expenses 30 Net Operating Income 31 Depreciation Expense Net Income 32 Before Tax 33 Income Tax 34 Net After Tax Income =B14 =C27*(1+$B$10) -D27"(1+$B$10) =E27"(1+$B$10) =F27"(1+$B$10) $B$150$B$5 =C28*(1+$B$10) =D28"(1+$B$10) =E28*(1+$B$10) =F28*(1+$B$10) -B16 -C29"(1+$B$10) =D29*(1+$8510) =E29*(1+SBS10) =F29*(1+$B$10) =C24-C25-C27-C28-C29-D24-D25-D27-D28-029-E24-25-E27-E28-E29-F24-F25-F27-F20-F29-624-G25-G27-G28-G29 =$B$6/$B$7 =SBS6/SBS7 -$B$6/$B$7 =$B$6/$B$7 $B$6/$B$7 030-031 -E30-E31 =C30-C31 -C32*$B$9 =C32-C33 =D32"$B$9 E32"$B$9 -E32-E33 -F30-F31 F32"$B$9 -F32 F33 =G30-G31 =G32"$B$9 =G32-G33 =D32-D33 Plus YTYWY =$B$11'C24 -$B$11D24 EETTEV -$B$11'F24 =$B$11'E24 -$B$11"G24 VECIN Cost of 25 Merchandise 26 Operating Expenses 27 Labor Cost 28 Rent Per Square Foot 29 Other Expenses 30 Net Operating =B14 =C27*(1+$B$10) =627"(1+$B$10) =E27"(1+$B$10) =F27"(1+$B$10) $B$16'$B$5 =C28*(1+$B$10) -D28*(1+$B$10) -E28"(1+$B$10) =F28"{1+$8510) =B16 -C29"(1+$B$10) =D29*(1+$B$10) -E29"(1+$B$10) =F29"(1+$B$10) -C24-C25 C27-C28-C29-024-D25-D27-028 029 -E24 E25-E27-E28-E29=F24-F25-F27-F20-F29-G24-G25-G27-G28-G29 =$B$6/SB$7 $B$6/$B$7 =$B$6/$B$7 $B$6/$B$7 $B$6/$B$7 Income 31 Depreciation =D30 31 C30-031 -C32"$B$9 C32 C33 -E30 E31 -E32'58$9 E32 E33 D32'$859 032 033 F30-F31 F32"$859 #F32-F33 +630-631 *G32"$B$9 G32-633 Expense Not Income 32 Before Tax 33 Income Tax Net Alter Tax 34 Income Plus 35 Depreciation Experts Annual Cash 36 Flow Discounted Cash Flow Cumulative 38 Discounted Cash Flow C31 031 -E31 -F31 G31 =C34+C35 -D34+035 -E34 E35 F34 F35 +234+G35 37 -C36/(1+5858) C23 =D36/(1+$B$8Y023 =E36/(1+$858E23 F36/(1+$858F23 -G36/(1+$B$8G23 -C37 -C38 037 -D38-E37 -E38+F37 -F38+G37