Question

1 Approved Answer

Posted on Sep 06, 2024

- X Scenarios Scenario 1 2% 20% Scenario 2 4% 31% Scenario 3 5% 23% to Inflation Rate Cost of Merchandise % of sales) Labor

- X Scenarios Scenario 1 2% 20% Scenario 2 4% 31% Scenario 3 5% 23% to Inflation Rate Cost of Merchandise % of sales) Labor Cost Other Expenses First-year sales revenue Sales Growth $200000 $325000 $800000 $350000 $280000 $700000 $210000 $310000 $900000 24% 17% 16% year 2 Sales Growth 20% 13% 15% year 3 14% 11% 11% Sales Growth year 4 Sales Growth 8% 5% 7% year 5 1 New Store Financial Model 2 3 Data 5000 300000 5 10% 34% 2% 30% 4 5 Store Size (square feet) 6 Total Fixed Assets 2 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 200000 28 325000 800000 Year 2 20% Year 3 12% Year 4 (9% Year 5 5% Year 1 800000 240000 2 960000 288000 3 1075200 322560 4 1171968 351590 5 1230566 369170 200000 140000 325000 204000 142800 331500 208080 145656 338130 212242 148569 344893 216486 151541 351790 - 105000 -6300 114674 60000 60000 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 141579 60000 81579 27737 53842 -165000 -56100 - 108900 60000 -48900 44455 44455 60774 60000 774 263 511 60000 60511 45463 14431 -66300 -22542 -43758 60000 16242 13423 -31031 60000 54674 18589 36085 60000 96085 65627 80059 60000 113842 70687 150746 New Store Financial Model 3 Data 5000 300000 5 10% 34% 254 30% 200000 28 325000 5 Store Suelquare feet) Total Fixed Assets Depreciation period straipht line) Discount Rate Tate 10 Inflation Rate 11 Cost of Merchandise % of sales) 12 13 Operating Expenses 1 Labor Cost 15 Rent Per Square Foot 16 Other Expenses 17 115 First Year Sales Revenue 19 20 Annual Growth state of Sales 21 22 Model 23 34 Sales Revenue 25 Cost of Merchandise 20 Operatiu Expenses 27 Labor Cost 2 Rent Per Square Foot Others 800000 Year 2 20% Year 3 12% Year 4 9% Year 5 SW Year -818 -S$11024 C24"(1020) S$11024 0241/141201 -$8$11 E24 3 -E24 (147201 $8511F24 4 -F2411202 SA$11-624 -314 $B$15.SBSS -316 -C27"(1 $B$10] -C281 $B$101 -C291 $B$101 -027" (190910) -028" (1258510) -02911-SS10) 27"{1SUISTO) -E28*11468510) E29-11_S$10) -F27 (1.S0510) -F281ASBS10 -F29 ( 110) 30 Net Operating income 31 Depreciation Exponse 12 Net Income before Tax 22 Income Tax 14 Not Alter Tax income 35 Plus Depreciation Experte 36 Annual Cash Flow 27 Discounted Cash Flow 38 Cumulative Discounted Cash Flow -024.C25 C27-28-020 SB5/ -CO-C1 -C32 SAS C32-C33 -31 -C4C35 06/150C2 -624-025 027 028-029 SBSG/SBSY -030-011 -032*$859 032-033 -031 D31D3S D36/140SY023 -C23.017 -24-25-27-028 29 -58$6/$0$7 -E30 E31 32"$0$9 -32-13 -E31 34-35 E301 SASSY23 -038137 F24-F25-127-F2 F29 -S$6/5A57 -F30-F31 -F32-5859 -F32-F33 21 -F34735 -36/11 SBSF23 F22 624 625 627.628 329 SBS SBS7 -630-631 -G2889 632 633 -631 34.635 636**SA$aA623 -138037 TU Good Think of any retailer that operates many stores throughout the country. The retailer is often seeking to open now 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 Lanager 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. do view line New Store Financial Moderne with Exconcions C Complete the table below Scenario 3 Scenario 1 Scenario 2 Cumulative Discounted Cash Flow (Round to the nearest Integer as needed) - X Scenarios Scenario 1 2% 20% Scenario 2 4% 31% Scenario 3 5% 23% to Inflation Rate Cost of Merchandise % of sales) Labor Cost Other Expenses First-year sales revenue Sales Growth $200000 $325000 $800000 $350000 $280000 $700000 $210000 $310000 $900000 24% 17% 16% year 2 Sales Growth 20% 13% 15% year 3 14% 11% 11% Sales Growth year 4 Sales Growth 8% 5% 7% year 5 1 New Store Financial Model 2 3 Data 5000 300000 5 10% 34% 2% 30% 4 5 Store Size (square feet) 6 Total Fixed Assets 2 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 200000 28 325000 800000 Year 2 20% Year 3 12% Year 4 (9% Year 5 5% Year 1 800000 240000 2 960000 288000 3 1075200 322560 4 1171968 351590 5 1230566 369170 200000 140000 325000 204000 142800 331500 208080 145656 338130 212242 148569 344893 216486 151541 351790 - 105000 -6300 114674 60000 60000 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 141579 60000 81579 27737 53842 -165000 -56100 - 108900 60000 -48900 44455 44455 60774 60000 774 263 511 60000 60511 45463 14431 -66300 -22542 -43758 60000 16242 13423 -31031 60000 54674 18589 36085 60000 96085 65627 80059 60000 113842 70687 150746 New Store Financial Model 3 Data 5000 300000 5 10% 34% 254 30% 200000 28 325000 5 Store Suelquare feet) Total Fixed Assets Depreciation period straipht line) Discount Rate Tate 10 Inflation Rate 11 Cost of Merchandise % of sales) 12 13 Operating Expenses 1 Labor Cost 15 Rent Per Square Foot 16 Other Expenses 17 115 First Year Sales Revenue 19 20 Annual Growth state of Sales 21 22 Model 23 34 Sales Revenue 25 Cost of Merchandise 20 Operatiu Expenses 27 Labor Cost 2 Rent Per Square Foot Others 800000 Year 2 20% Year 3 12% Year 4 9% Year 5 SW Year -818 -S$11024 C24"(1020) S$11024 0241/141201 -$8$11 E24 3 -E24 (147201 $8511F24 4 -F2411202 SA$11-624 -314 $B$15.SBSS -316 -C27"(1 $B$10] -C281 $B$101 -C291 $B$101 -027" (190910) -028" (1258510) -02911-SS10) 27"{1SUISTO) -E28*11468510) E29-11_S$10) -F27 (1.S0510) -F281ASBS10 -F29 ( 110) 30 Net Operating income 31 Depreciation Exponse 12 Net Income before Tax 22 Income Tax 14 Not Alter Tax income 35 Plus Depreciation Experte 36 Annual Cash Flow 27 Discounted Cash Flow 38 Cumulative Discounted Cash Flow -024.C25 C27-28-020 SB5/ -CO-C1 -C32 SAS C32-C33 -31 -C4C35 06/150C2 -624-025 027 028-029 SBSG/SBSY -030-011 -032*$859 032-033 -031 D31D3S D36/140SY023 -C23.017 -24-25-27-028 29 -58$6/$0$7 -E30 E31 32"$0$9 -32-13 -E31 34-35 E301 SASSY23 -038137 F24-F25-127-F2 F29 -S$6/5A57 -F30-F31 -F32-5859 -F32-F33 21 -F34735 -36/11 SBSF23 F22 624 625 627.628 329 SBS SBS7 -630-631 -G2889 632 633 -631 34.635 636**SA$aA623 -138037 TU Good Think of any retailer that operates many stores throughout the country. The retailer is often seeking to open now 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 Lanager 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. do view line New Store Financial Moderne with Exconcions C Complete the table below Scenario 3 Scenario 1 Scenario 2 Cumulative Discounted Cash Flow (Round to the nearest Integer as needed)