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 Scenarios Scenario 1 Scenario 2 Cumulative Discounted Cash Flow (Round to the nearest integer as needed.) Scenario 1 5% 30% Scenario 2 3% 26% Scenario 3 3% 27% Inflation Rate Cost of Merchandise % of sales) Labor abor Cost Other Expenses First-year sales $225000 $350000 $600000 $200000 $325000 $800000 $275000 $325000 $800000 revenue Sales Growth 22% 25% 20% sales year 2 15% 18% 18% year 11% 14% 16% Sales Growth Sales ar 3 Sales Growth year 4 Sales Growth year 5 5% 8% 14% Print Done Enter your answer in each of the answer boxes. A1 fx New Store Financial Model A E F G 1 New Store Financial Model 2 3 Data 5000 300000 5 10% 34% 2% 30% 200000 28 325000 800000 Year 2 20% Year 3 12% Year 4 9% Year 5 5% 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 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 Year 3 4 5 1 800000 240000 2 960000 288000 1075200 322560 1171968 351590 1230566 369170 200000 140000 325000 -105000 60000 -165000 -56100 -108900 60000 48900 -44455 -44455 216486 151541 351790 141579 60000 204000 142800 331500 -6300 60000 -66300 -22542 -43758 60000 16242 13423 -31031 208080 145656 338130 60774 60000 774 263 212242 148569 344893 114674 60000 54674 18589 36085 60000 96085 65627 80059 511 60000 60511 45463 14431 81579 27737 53842 60000 113842 70687 150746 A1 fx New Store Financial Model A C D E G H 5000 3000001 5 10% 34% 2% 30% 200000 28 325000 800000 1 New Store Financial Model 2 3 Data 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 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 Year 2 20% Year 3 12% Year 4 9% Year 5 5% Year 1 2 3 5 =B18 =$B$11*C24 =C24*(1+D20) =$B$11D24 =D24*(1+E20) =$B$11*E24 =E24*(1+F20) $B$11*F24 =F24*(1+G20) =$B$11*G24 =B14 =$B$15*$B$5 =B16 =C24-C25-C27-C28-C29 $B$6/$B$ 7 =C30-C31 =C32*$B$9 =C32-C33 =C31 =C34+C35 =C36/(1+$B$8) C23 =C37 =C27*(1+$B$ 10) =C28*(1+$B$ 10) =C29*(1+$B$ 10) =D24-D25-D27-D28-D29 =$B$6/$B$7 =D30-D31 =D32*$B$9 =D32-D33 =D31 =D34+D35 =D36/(1+$B$8)^D23 =C38+D37 =D27*(1+$B$ 10) =D28*(1+$B$ 10) =D29*(1+$B$ 10) =E24-E25-E27-E28-E29 $B$6/$B$ 7 =E30-E31 =E32*$B$9 =E32-E33 =E31 =E34+E35 =E36/(1+$B$8)"E23 =D38+E37 =E27*(1+$B$10) =E28*(1+$B$10) =E29*(1+$B$10) |=F24-F25-F27-F28-F29 $B$6/$B$ 7 =F30-F31 =F32 $B$9 =F32-F33 =F31 =F34+F35 =F36/(1+$B$8)"F23 |=E38+F37 =F27*(1+$B$ 10) =F28*(1+$B$10) =F29*(1+$B$ 10) | =G24-625-627-G28-529 $B$6/$B$ 7 =G30-G31 =G32*$B$9 =G32-G33 =G31 =G34+G35 =G36/(1+$B$8)"G23 =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 Scenarios Scenario 1 Scenario 2 Cumulative Discounted Cash Flow (Round to the nearest integer as needed.) Scenario 1 5% 30% Scenario 2 3% 26% Scenario 3 3% 27% Inflation Rate Cost of Merchandise % of sales) Labor abor Cost Other Expenses First-year sales $225000 $350000 $600000 $200000 $325000 $800000 $275000 $325000 $800000 revenue Sales Growth 22% 25% 20% sales year 2 15% 18% 18% year 11% 14% 16% Sales Growth Sales ar 3 Sales Growth year 4 Sales Growth year 5 5% 8% 14% Print Done Enter your answer in each of the answer boxes. A1 fx New Store Financial Model A E F G 1 New Store Financial Model 2 3 Data 5000 300000 5 10% 34% 2% 30% 200000 28 325000 800000 Year 2 20% Year 3 12% Year 4 9% Year 5 5% 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 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 Year 3 4 5 1 800000 240000 2 960000 288000 1075200 322560 1171968 351590 1230566 369170 200000 140000 325000 -105000 60000 -165000 -56100 -108900 60000 48900 -44455 -44455 216486 151541 351790 141579 60000 204000 142800 331500 -6300 60000 -66300 -22542 -43758 60000 16242 13423 -31031 208080 145656 338130 60774 60000 774 263 212242 148569 344893 114674 60000 54674 18589 36085 60000 96085 65627 80059 511 60000 60511 45463 14431 81579 27737 53842 60000 113842 70687 150746 A1 fx New Store Financial Model A C D E G H 5000 3000001 5 10% 34% 2% 30% 200000 28 325000 800000 1 New Store Financial Model 2 3 Data 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 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 Year 2 20% Year 3 12% Year 4 9% Year 5 5% Year 1 2 3 5 =B18 =$B$11*C24 =C24*(1+D20) =$B$11D24 =D24*(1+E20) =$B$11*E24 =E24*(1+F20) $B$11*F24 =F24*(1+G20) =$B$11*G24 =B14 =$B$15*$B$5 =B16 =C24-C25-C27-C28-C29 $B$6/$B$ 7 =C30-C31 =C32*$B$9 =C32-C33 =C31 =C34+C35 =C36/(1+$B$8) C23 =C37 =C27*(1+$B$ 10) =C28*(1+$B$ 10) =C29*(1+$B$ 10) =D24-D25-D27-D28-D29 =$B$6/$B$7 =D30-D31 =D32*$B$9 =D32-D33 =D31 =D34+D35 =D36/(1+$B$8)^D23 =C38+D37 =D27*(1+$B$ 10) =D28*(1+$B$ 10) =D29*(1+$B$ 10) =E24-E25-E27-E28-E29 $B$6/$B$ 7 =E30-E31 =E32*$B$9 =E32-E33 =E31 =E34+E35 =E36/(1+$B$8)"E23 =D38+E37 =E27*(1+$B$10) =E28*(1+$B$10) =E29*(1+$B$10) |=F24-F25-F27-F28-F29 $B$6/$B$ 7 =F30-F31 =F32 $B$9 =F32-F33 =F31 =F34+F35 =F36/(1+$B$8)"F23 |=E38+F37 =F27*(1+$B$ 10) =F28*(1+$B$10) =F29*(1+$B$ 10) | =G24-625-627-G28-529 $B$6/$B$ 7 =G30-G31 =G32*$B$9 =G32-G33 =G31 =G34+G35 =G36/(1+$B$8)"G23 =F38+G37