Year 2 Year 3 Year 4 Year 5 20% 12% 9% 5% Year 1 =B18 2 =C24*(1+D20) 3 =D24*(1+E20) 4 =E24*(1+F20) =$B$11*F24 5 =F24*(1+G20) =$B$11*G24 =$B$11*C24 =$B$11*D24 =$B$11*E24 =B14 =$B$15*$B$5 19 Annual Growth 20 Rate of Sales 21 22 Model 23 24 Sales Revenue Cost of 25 Merchandise Operating 26 Expenses 27 Labor Cost Rent Per 28 Square Foot 29 Other Expenses Net Operating 30 Income Depreciation 31 Expense Net Income 32 Before Tax 33 Income Tax Net After Tax 34 Income Plus 35 Depreciation Expense Annual Cash 361 Flow =C27*(1+$B$ 10) =C28*(1+$B$10) =C29*(1+$B$10) =D27*(1+$B$10) =D28*(1+$B$10) =D29*(1+$B$10) =E27*(1+$B$10) =E28*(1+$B$10) =E29*(1+$B$10) =F27*(1+$B$10) =F28*(1+$B$10) =F29*(1+$B$10) =B16 =C24-C25-C27-C28-C29=D24-D25-D27-D28-D29=E24-E25-E27-E28-E29=F24-F25-F27-F28-F29=G24-G25-G27-G28-G29 =$B$6/$B$7 =$B$6/$B$7 =$B$6/$B$7 =$B$6/$B$7 =$B$6/$B$7 =C30-C31 =D30-D31 =E30-E31 =F30-F31 =G30-G31 =C32*$B$9 =D32*$B$9 =E32*$B$9 =F32*$B$9 =G32*$B$9 =C32-C33 =D32-D33 =E32-E33 =F32-F33 =G32-G33 =C31 =D31 =E31 =F31 =G31 =C34+C35 =D34+D35 =E34+E35 =F34+F35 =G34+G35 =C36/(1+$B$8)^C23 =D36/(1+$B$8)^D23 =E36/(1+$B$8)^E23 =F36/(1+$B$8)^F23 =G36/(1+$B$8)^G23 Discounted 37 Cash Flow Cumulative 38 Discounted Cash Flow =C37 =C38+D37 =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 1 Scenario 2 Scenario 3 Cumulative Discounted Cash Flow (Round to the nearest integer as needed.) Scenarios X Scenario 1 5% 30% Scenario 2 4% 20% Scenario 3 5% 23% $225000 $350000 $600000 $190000 $250000 $900000 $210000 $310000 $900000 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 22% 20% 16% 15% 16% 15% 11% 11% 11% 5% 7% 7% year 5 New Store Financial Model with numbers C D E F G B New Store Financial 1 Model 2 3 Data 4 Store Size (square 5 5000 feet) 6 Total Fixed Assets 300,000 Depreciation period 7 5 (straight line) 8 Discount Rate 10% 9 Tax Rate 34% 10 Inflation Rate 2% Cost of Merchandise 11 30% (% of sales) 12 13 Operating Expenses 14 Labor Cost 200,000 15 Rent Per Square Foot28 16 Other Expenses 325,000 17 First Year Sales 18 800,000 Revenue 19 Annual Growth Rate 20 of Sales 21 Year 2 Year 3 Year 4 Year 5 20% 12% 9% 5% Year 1 800000 240000 2 960000 288000 3 1075200 322560 4 1171968 351590 5 1230566 369170 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 New Store Financial Model with Excel functions B D E F G New Store Financial Model Data Store Size 5000 (square feet) Total Fixed 300,000 Assets Depreciation 7 period (straight 5 line) 8 Discount Rate 10% 9 Tax Rate 34% 10 Inflation Rate 2% Cost of 11 Merchandise %30% of sales) 12 Operating 13 Expenses 14 Labor Cost200,000 Rent Per 28 15 Square Foot 16 Other Expenses 325,000 17 First Year Sales 18 800,000 Revenue Year 2 Year 3 Year 4 Year 5 20% 12% 9% 5% Year 1 =B18 2 =C24*(1+D20) 3 =D24*(1+E20) 4 =E24*(1+F20) =$B$11*F24 5 =F24*(1+G20) =$B$11*G24 =$B$11*C24 =$B$11*D24 =$B$11*E24 =B14 =$B$15*$B$5 19 Annual Growth 20 Rate of Sales 21 22 Model 23 24 Sales Revenue Cost of 25 Merchandise Operating 26 Expenses 27 Labor Cost Rent Per 28 Square Foot 29 Other Expenses Net Operating 30 Income Depreciation 31 Expense Net Income 32 Before Tax 33 Income Tax Net After Tax 34 Income Plus 35 Depreciation Expense Annual Cash 361 Flow =C27*(1+$B$ 10) =C28*(1+$B$10) =C29*(1+$B$10) =D27*(1+$B$10) =D28*(1+$B$10) =D29*(1+$B$10) =E27*(1+$B$10) =E28*(1+$B$10) =E29*(1+$B$10) =F27*(1+$B$10) =F28*(1+$B$10) =F29*(1+$B$10) =B16 =C24-C25-C27-C28-C29=D24-D25-D27-D28-D29=E24-E25-E27-E28-E29=F24-F25-F27-F28-F29=G24-G25-G27-G28-G29 =$B$6/$B$7 =$B$6/$B$7 =$B$6/$B$7 =$B$6/$B$7 =$B$6/$B$7 =C30-C31 =D30-D31 =E30-E31 =F30-F31 =G30-G31 =C32*$B$9 =D32*$B$9 =E32*$B$9 =F32*$B$9 =G32*$B$9 =C32-C33 =D32-D33 =E32-E33 =F32-F33 =G32-G33 =C31 =D31 =E31 =F31 =G31 =C34+C35 =D34+D35 =E34+E35 =F34+F35 =G34+G35 =C36/(1+$B$8)^C23 =D36/(1+$B$8)^D23 =E36/(1+$B$8)^E23 =F36/(1+$B$8)^F23 =G36/(1+$B$8)^G23 Discounted 37 Cash Flow Cumulative 38 Discounted Cash Flow =C37 =C38+D37 =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 1 Scenario 2 Scenario 3 Cumulative Discounted Cash Flow (Round to the nearest integer as needed.) Scenarios X Scenario 1 5% 30% Scenario 2 4% 20% Scenario 3 5% 23% $225000 $350000 $600000 $190000 $250000 $900000 $210000 $310000 $900000 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 22% 20% 16% 15% 16% 15% 11% 11% 11% 5% 7% 7% year 5 New Store Financial Model with numbers C D E F G B New Store Financial 1 Model 2 3 Data 4 Store Size (square 5 5000 feet) 6 Total Fixed Assets 300,000 Depreciation period 7 5 (straight line) 8 Discount Rate 10% 9 Tax Rate 34% 10 Inflation Rate 2% Cost of Merchandise 11 30% (% of sales) 12 13 Operating Expenses 14 Labor Cost 200,000 15 Rent Per Square Foot28 16 Other Expenses 325,000 17 First Year Sales 18 800,000 Revenue 19 Annual Growth Rate 20 of Sales 21 Year 2 Year 3 Year 4 Year 5 20% 12% 9% 5% Year 1 800000 240000 2 960000 288000 3 1075200 322560 4 1171968 351590 5 1230566 369170 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 New Store Financial Model with Excel functions B D E F G New Store Financial Model Data Store Size 5000 (square feet) Total Fixed 300,000 Assets Depreciation 7 period (straight 5 line) 8 Discount Rate 10% 9 Tax Rate 34% 10 Inflation Rate 2% Cost of 11 Merchandise %30% of sales) 12 Operating 13 Expenses 14 Labor Cost200,000 Rent Per 28 15 Square Foot 16 Other Expenses 325,000 17 First Year Sales 18 800,000 Revenue