Part 2. New Stores Your next assignment is to determine if AAP should invest in a new stores so you need to determine net cash flows and NPV and determine if the project is viable or not. Capital expenditures to produce the new stores will initially require an investment of $1.8 billion. Other development costs that will be required to finish the stores project is $900 million this year. Any ongoing costs for upgrades will be covered in the margin calculation below. The store project is expected to have a life of five years. First-year revenues for the new ride are expected to be $3,400,000,000 ($3,400 million). The ride revenues are expected to grow by 37% for the second year, and then increase by 5% for the third, decrease by 15% for the 4th and finally decrease by 25% for the 5th (final) year of operation. Your job is to the rest of the cash flows associated with this project. Your boss has indicated that the operating costs and net working capital requirements are similar to the rest of the company's products. Since your boss hasn't been much help, here are some tips to guide your analysis: 1. You will need to use the Financial Statements that you downloaded in Part 1. 2. You are now ready to determine the free cash flow. Compute the free cash flow based on the information above for each year using : Free Cash Flow = (Revenues - Costs - Depreciation) X(1 - Tax Rate) + Depreciation - Capex - Change in NWC Set up the timeline and computation of the free cash flow in separate, contiguous columns for each year of the project life. Be sure to make outflows negative and inflows positive a. Assume that the project's profitability will be similar to AAP's existing projects and estimate costs for each year of your project by using the average ratio of non-depreciation costs to revenue for the last 2 fiscal years (in practice you really tend to use at least 4 years worth of data, but for this exercise 2 years will suffice): (Costs of Goods Sold* + SG&A)/Sales *AAP calls cost of goods sold, cost of sales words Foaus B F- 5 - Styles Editing Volee Paragraph Font *AAP calls cost of goods sold, cost of sales You should assume that this ratio will hold for this project as well. You do not need to break out the individual components of operating costs in your forecast Simply the forecast to the Total Cost of Goods Sold (or cost of revenue) + SG&A +R&D for each year. Determine AAP's tax rate as 1- (Income After Tax/income Before Tax) in the last fiscal year reported. Recalculate the WACC form Part 1 using this tax rate. What should you use if this number is negative? Calculate the networking capital required each year by assuming that the level of NWC will be a constant percentage of the project's sales. Use AAP's last 2 fiscal year average NWC/Sales to estimate the required percentage. (Use only accounts receivable, accounts payable, and inventory to measure working capital. Other components of current assets and liabilities are harder to interpret and are not necessarily reflective of the project's required NWC-e.g., AAP's cash holdings.) To determine the free cash flow, calculate the additional capital investment and the change in net working capital each year. Determine the NPV of the project with WACC calculated in Part 1 step 10 and the projects IRR For the NPV calculation remember to add the first CF when you are using the excel function =NPV(rate, CF1:CF5) + CFO For the IRR include all cash flows in your excel calculation. Should AAP invest in the project? 40 words03 AUSIVE U AAP Worksheet 2 - Excel Review Help File Home Insert Page Layout Formulas Data Calibri 12 - AA == BIU - - A . Z Clipboard Font Font C29 X for View General $ % - 8-98 Conditional Format as Formatting Tables Styles Alignment Number D 2 total revenue 3 cost of revenue 4 gross profit 5 operating expense 6 operating income 7 Net Non Operating interest income expense 8 other income expense 9 pretax income 10 tax provision 11 net income common stockholders 12 diluted Nl available to com Stock holders 13 basic EPS 14 diluted EPS 15 Basic Average shares 16 Diluted Average Shares 17 Total operating income as reported 18 total expense 19 net income from continuing & discontiuing operation 20 normalized income 21 interest expense 22 net interest income 23 ebit 24 ebitda 25 Reconciled Cost of Revenue 26 Reconciled Depreciation 27 Net income from continuing operation net minority interest 28 Normalized EBITDA 12/31/2019 12/31/2018 97090039580554 5454257 5361141 4254746 4219413 3577566 3615138 677180 604275 -39898 -56588 464 7577 637746 555264 150850 131417 486896 423847 486896 423847 0.0058 0.0057 73728 73991 677180 604275 9031823 8976279 486896 423847 486896 423847 39898 56588 -39898 -56588 677644 611852 545257 5361141 238371 238184 486896 423847 916015850036 c Bond Information Financial Statements New Stores Project Ready No action More Share Comments 2.44 Herte 57 ZY Cand Formats SOTT End m 1 Points AAP's New Stores Project Amounts in Millions Revenues Costs Depreciation Tax Rate Capital Expenditures ARNNNNNNAARS NWC Che NWC Recover NWC FCF (millions) Cost of Capital NPV = IRR - (For the IRR include all cash flows in your excel calculation.) Should AAP invest in the project? Bond Information Final Statements New Stores Project We're Updating Your Norton Protection! Get ready to enjoy more features at no extra cost. This update will occur automatically. No action is required from you. Learn More Part 2. New Stores Your next assignment is to determine if AAP should invest in a new stores so you need to determine net cash flows and NPV and determine if the project is viable or not. Capital expenditures to produce the new stores will initially require an investment of $1.8 billion. Other development costs that will be required to finish the stores project is $900 million this year. Any ongoing costs for upgrades will be covered in the margin calculation below. The store project is expected to have a life of five years. First-year revenues for the new ride are expected to be $3,400,000,000 ($3,400 million). The ride revenues are expected to grow by 37% for the second year, and then increase by 5% for the third, decrease by 15% for the 4th and finally decrease by 25% for the 5th (final) year of operation. Your job is to the rest of the cash flows associated with this project. Your boss has indicated that the operating costs and net working capital requirements are similar to the rest of the company's products. Since your boss hasn't been much help, here are some tips to guide your analysis: 1. You will need to use the Financial Statements that you downloaded in Part 1. 2. You are now ready to determine the free cash flow. Compute the free cash flow based on the information above for each year using : Free Cash Flow = (Revenues - Costs - Depreciation) X(1 - Tax Rate) + Depreciation - Capex - Change in NWC Set up the timeline and computation of the free cash flow in separate, contiguous columns for each year of the project life. Be sure to make outflows negative and inflows positive a. Assume that the project's profitability will be similar to AAP's existing projects and estimate costs for each year of your project by using the average ratio of non-depreciation costs to revenue for the last 2 fiscal years (in practice you really tend to use at least 4 years worth of data, but for this exercise 2 years will suffice): (Costs of Goods Sold* + SG&A)/Sales *AAP calls cost of goods sold, cost of sales words Foaus B F- 5 - Styles Editing Volee Paragraph Font *AAP calls cost of goods sold, cost of sales You should assume that this ratio will hold for this project as well. You do not need to break out the individual components of operating costs in your forecast Simply the forecast to the Total Cost of Goods Sold (or cost of revenue) + SG&A +R&D for each year. Determine AAP's tax rate as 1- (Income After Tax/income Before Tax) in the last fiscal year reported. Recalculate the WACC form Part 1 using this tax rate. What should you use if this number is negative? Calculate the networking capital required each year by assuming that the level of NWC will be a constant percentage of the project's sales. Use AAP's last 2 fiscal year average NWC/Sales to estimate the required percentage. (Use only accounts receivable, accounts payable, and inventory to measure working capital. Other components of current assets and liabilities are harder to interpret and are not necessarily reflective of the project's required NWC-e.g., AAP's cash holdings.) To determine the free cash flow, calculate the additional capital investment and the change in net working capital each year. Determine the NPV of the project with WACC calculated in Part 1 step 10 and the projects IRR For the NPV calculation remember to add the first CF when you are using the excel function =NPV(rate, CF1:CF5) + CFO For the IRR include all cash flows in your excel calculation. Should AAP invest in the project? 40 words03 AUSIVE U AAP Worksheet 2 - Excel Review Help File Home Insert Page Layout Formulas Data Calibri 12 - AA == BIU - - A . Z Clipboard Font Font C29 X for View General $ % - 8-98 Conditional Format as Formatting Tables Styles Alignment Number D 2 total revenue 3 cost of revenue 4 gross profit 5 operating expense 6 operating income 7 Net Non Operating interest income expense 8 other income expense 9 pretax income 10 tax provision 11 net income common stockholders 12 diluted Nl available to com Stock holders 13 basic EPS 14 diluted EPS 15 Basic Average shares 16 Diluted Average Shares 17 Total operating income as reported 18 total expense 19 net income from continuing & discontiuing operation 20 normalized income 21 interest expense 22 net interest income 23 ebit 24 ebitda 25 Reconciled Cost of Revenue 26 Reconciled Depreciation 27 Net income from continuing operation net minority interest 28 Normalized EBITDA 12/31/2019 12/31/2018 97090039580554 5454257 5361141 4254746 4219413 3577566 3615138 677180 604275 -39898 -56588 464 7577 637746 555264 150850 131417 486896 423847 486896 423847 0.0058 0.0057 73728 73991 677180 604275 9031823 8976279 486896 423847 486896 423847 39898 56588 -39898 -56588 677644 611852 545257 5361141 238371 238184 486896 423847 916015850036 c Bond Information Financial Statements New Stores Project Ready No action More Share Comments 2.44 Herte 57 ZY Cand Formats SOTT End m 1 Points AAP's New Stores Project Amounts in Millions Revenues Costs Depreciation Tax Rate Capital Expenditures ARNNNNNNAARS NWC Che NWC Recover NWC FCF (millions) Cost of Capital NPV = IRR - (For the IRR include all cash flows in your excel calculation.) Should AAP invest in the project? Bond Information Final Statements New Stores Project We're Updating Your Norton Protection! Get ready to enjoy more features at no extra cost. This update will occur automatically. No action is required from you. Learn More