Please solve for Scenario 1,2,3. Thank you

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 Scenario 1 5% 23% Scenario 2 3% 26% Scenario 3 5% 30% $210000 $310000 $900000 $200000 $325000 $800000 $225000 $350000 $600000 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 year 5 16% 25% 22% 15% 18% 15% 11% 14% 11% 7% 8% 5% Print Done - New store financial model with numbers D E F G A 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 Year 2 Year 3 Year 4 Year 5 Analna. Data Print Done - New store financial model with numbers Teal z Teal Teal 4 Teal J 20% 12% 9% 5% Year 1 800000 240000 2 960000 288000 3 1075200 322560 4 1171968 351590 5 1230566 369170 Annual Growth Rate 20 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 Net Income Before 32 Tax 33 Income Tax 34 Net After Tax Income Plus Depreciation 35 Expense 36 Annual Cash Flow Discounted Cash 37 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 16242 60511 96085 113842 - 44455 13423 45463 65627 70687 -44455 - 31031 14431 80059 150746 Print Done New store financial model with excel functions B D E F G New Store 1 Financial Model 3 Data 4 Store Size 5000 (square feet) Total Fixed 300,000 Assets Depreciation period 7 5 (straight line) Discount 10% Rate 9 Tax Rate 34% Inflation 10 2% Rate Cost of 11 Merchandise 30% (% of sales) 12 Operating 13 Expenses Print Done - New store financial model with excel functions 13 Operating Expenses 14 Labor Cost 200,000 15 Rent Per Square Foot28 Year 2 Year 3 Year 4 Year 5 Other 16 325,000 Expenses 17 First Year 18 Sales 800,000 Revenue 19 Annual 20 Growth Rate of Sales 21 22 Model 23 Year 24 Sales Revenue Cost of 25 Merchandise 20% 12% 9% 5% 1 2 3 4 5 =B18 =C24"(1+D20) =D24*(1+E20) =E24*(1+F20) =F24*(1+G20) =$B$11 C24 =SBS11*D24 =$B$11*E24 =$B$11*F24 =$B$11*G24 26 Operating Expenses 27 Labor Cost Rent Per =B14 =C27*(1+$B$10) =D27*(1+$B$10) =E27*(1 +$B$10) =F27"(1+$BS10) -tntcm -nn/4.cn -nnnnn -no+14.Onun - n11.11 Print Done New store financial model with excel functions =B14 =E27*(1+$B$10) =C27*(1+$B$10) =C28*(1+$B$10) =D27*(1 +$B$10) =D28*(1+$B$10) =F27*(1+$B$10) =F28*(1+$B$10) =SBS15*$B$5 =E28*(1+$B$10) =B16 =C29*(1 +$B$10) =D29*(1+$B$10) =E29*(1+$B$10) =F29*(1 +$B$10) =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 Expenses 27 Labor Cost Rent Per 28 Square Foot Other 29 Expenses Net 30 Operating Income Depreciation 31 Expense Net Income 32 Before Tax 33 Income Tax Net After Tax 34 Income Plus 35 Depreciation Expense Annual 36 Cash Flow Discounted 37 Cash Flow Cumulative 38 Discounted Cash Flow =C32 SBS9 =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 36/(1+$B$8) C23 =D36/(1+$B$8) D23 =E36/(1+$B$ =F36/(1+$B$8)" =G36/(1+$B$8)"G23 =C37 =C38+D37 =D38+E37 =E38+F37 =F38+G37 Print Done 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 Scenario 1 5% 23% Scenario 2 3% 26% Scenario 3 5% 30% $210000 $310000 $900000 $200000 $325000 $800000 $225000 $350000 $600000 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 year 5 16% 25% 22% 15% 18% 15% 11% 14% 11% 7% 8% 5% Print Done - New store financial model with numbers D E F G A 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 Year 2 Year 3 Year 4 Year 5 Analna. Data Print Done - New store financial model with numbers Teal z Teal Teal 4 Teal J 20% 12% 9% 5% Year 1 800000 240000 2 960000 288000 3 1075200 322560 4 1171968 351590 5 1230566 369170 Annual Growth Rate 20 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 Net Income Before 32 Tax 33 Income Tax 34 Net After Tax Income Plus Depreciation 35 Expense 36 Annual Cash Flow Discounted Cash 37 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 16242 60511 96085 113842 - 44455 13423 45463 65627 70687 -44455 - 31031 14431 80059 150746 Print Done New store financial model with excel functions B D E F G New Store 1 Financial Model 3 Data 4 Store Size 5000 (square feet) Total Fixed 300,000 Assets Depreciation period 7 5 (straight line) Discount 10% Rate 9 Tax Rate 34% Inflation 10 2% Rate Cost of 11 Merchandise 30% (% of sales) 12 Operating 13 Expenses Print Done - New store financial model with excel functions 13 Operating Expenses 14 Labor Cost 200,000 15 Rent Per Square Foot28 Year 2 Year 3 Year 4 Year 5 Other 16 325,000 Expenses 17 First Year 18 Sales 800,000 Revenue 19 Annual 20 Growth Rate of Sales 21 22 Model 23 Year 24 Sales Revenue Cost of 25 Merchandise 20% 12% 9% 5% 1 2 3 4 5 =B18 =C24"(1+D20) =D24*(1+E20) =E24*(1+F20) =F24*(1+G20) =$B$11 C24 =SBS11*D24 =$B$11*E24 =$B$11*F24 =$B$11*G24 26 Operating Expenses 27 Labor Cost Rent Per =B14 =C27*(1+$B$10) =D27*(1+$B$10) =E27*(1 +$B$10) =F27"(1+$BS10) -tntcm -nn/4.cn -nnnnn -no+14.Onun - n11.11 Print Done New store financial model with excel functions =B14 =E27*(1+$B$10) =C27*(1+$B$10) =C28*(1+$B$10) =D27*(1 +$B$10) =D28*(1+$B$10) =F27*(1+$B$10) =F28*(1+$B$10) =SBS15*$B$5 =E28*(1+$B$10) =B16 =C29*(1 +$B$10) =D29*(1+$B$10) =E29*(1+$B$10) =F29*(1 +$B$10) =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 Expenses 27 Labor Cost Rent Per 28 Square Foot Other 29 Expenses Net 30 Operating Income Depreciation 31 Expense Net Income 32 Before Tax 33 Income Tax Net After Tax 34 Income Plus 35 Depreciation Expense Annual 36 Cash Flow Discounted 37 Cash Flow Cumulative 38 Discounted Cash Flow =C32 SBS9 =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 36/(1+$B$8) C23 =D36/(1+$B$8) D23 =E36/(1+$B$ =F36/(1+$B$8)" =G36/(1+$B$8)"G23 =C37 =C38+D37 =D38+E37 =E38+F37 =F38+G37 Print Done