Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please HELP ME ASAP! THank you Think of any retailer that operates many stores throughout the country. The retailer is often seeking to open new
Please HELP ME ASAP! 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 3 Scenario 1 Scenario 2 Cumulative Discounted Cash Flow (Round to the nearest integer as needed.) Scenario 1 3% 26% Scenario 2 1% 25% Scenario 3 5% 30% Inflation Rate Cost of Merchandise (% of sales) Labor Cost Other Expenses First-year sales revenue Sales Growth year 2 Sales Growth $200000 $325000 $800000 $150000 $300000 $600000 $225000 $350000 $600000 25% 15% 22% 18% 10% 15% year 3 Sales Growth 14% 6% 11% year 4 Sales Growth 8% 3% 5% year 5 New Store Financial Model Data 5000 300000 5 Store Size (square feet) Total Fixed Assets Depreciation period (straight line) Discount Rate Tax Rate Inflation Rate Cost of Merchandise % of sales) 10% 34% 2% 30% Operating Expenses Labor Cost Rent Per Square Foot Other Expenses 200000 28 325000 First Year Sales Revenue 800000 Year 2 20% Year 3 12% Year 4 9% Year 5 5% Annual Growth Rate of Sales Model Year 1 4 800000 1075200 960000 288000 1171968 351590 1230566 369170 240000 322560 216486 200000 140000 325000 -105000 Sales Revenue Cost of Merchandise Operating Expenses Labor Cost Rent Per Square Foot Other Expenses Net Operating Income Depreciation Expense Net Income Before Tax Income Tax Net After Tax Income Plus Depreciation Expense Annual Cash Flow Discounted Cash Flow Cumulative Discounted Cash Flow 60000 -165000 -56100 -108900 60000 204000 142800 331500 -6300 60000 -66300 -22542 -43758 60000 16242 13423 -31031 208080 145656 338130 60774 60000 774 263 511 60000 60511 45463 14431 212242 148569 344893 114674 60000 54674 18589 36085 60000 96085 65627 80059 151541 351790 141579 60000 81579 27737 53842 60000 113842 70687 150746 -48900 -44455 -44455 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 3% 26% Scenario 2 1% 25% Scenario 3 5% 30% Inflation Rate Cost of Merchandise (% of sales) Labor Cost Other Expenses First-year sales revenue Sales Growth year 2 Sales Growth $200000 $325000 $800000 $150000 $300000 $600000 $225000 $350000 $600000 25% 15% 22% 18% 10% 15% year 3 Sales Growth 14% 6% 11% year 4 Sales Growth 8% 3% 5% year 5 New Store Financial Model Data 5000 300000 5 Store Size (square feet) Total Fixed Assets Depreciation period (straight line) Discount Rate Tax Rate Inflation Rate Cost of Merchandise % of sales) 10% 34% 2% 30% Operating Expenses Labor Cost Rent Per Square Foot Other Expenses 200000 28 325000 First Year Sales Revenue 800000 Year 2 20% Year 3 12% Year 4 9% Year 5 5% Annual Growth Rate of Sales Model Year 1 4 800000 1075200 960000 288000 1171968 351590 1230566 369170 240000 322560 216486 200000 140000 325000 -105000 Sales Revenue Cost of Merchandise Operating Expenses Labor Cost Rent Per Square Foot Other Expenses Net Operating Income Depreciation Expense Net Income Before Tax Income Tax Net After Tax Income Plus Depreciation Expense Annual Cash Flow Discounted Cash Flow Cumulative Discounted Cash Flow 60000 -165000 -56100 -108900 60000 204000 142800 331500 -6300 60000 -66300 -22542 -43758 60000 16242 13423 -31031 208080 145656 338130 60774 60000 774 263 511 60000 60511 45463 14431 212242 148569 344893 114674 60000 54674 18589 36085 60000 96085 65627 80059 151541 351790 141579 60000 81579 27737 53842 60000 113842 70687 150746 -48900 -44455 -44455Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started