Advance Auto Parts Inc (AAP)
You can do this project on your own or work together in teams of 2
students. Please include students names in worksheet if working on a
team.
Part 1: AAP’s Weighted Cost of Capital
You work in AAP Inc´s corporate finance and treasury department and have
een assigned to the team estimating AAP´s WACC. You must estimate this
WACC in preparation for a team meeting later today. You quickly realize that the
information you need is readily available online.
1. You will first have to download into excel AAP’s Financial Statements
for the past 2 fiscal years. Go to SEC EDGAR Company Search and
use the option to the right using AAP’s Ticker “AAP” to look for the
information. Remember to use the 10Ks (these have the annual reports)
2. Go to http:
finance.yahoo.com. Under “Market Data” you will find the
yield to maturity for 10-year Treasury bonds listed as “10 Yr Bond(%).”
Collect this number as your risk-free rate.
http:
finance.yahoo.com
3. Still in Yahoo Finance, in the box next in the search bar, type AAP’s
ticker symbol AAP), and click “Search.” Once you see the basic
information for AAP, find the cu
ent stock price and click “Key
Statistics” on the left side of the screen. Collect AAP’s number of shares
outstanding and beta. (You can also find the number of shares
outstanding in the financial statements that you downloaded in step 1)
4. Calculate AAP’s cost of equity capital using the CAPM, the risk-free rate
you collected in step 2, and a market risk premium of 10%.
5. To get AAP’s cost of debt and the market value of its long-term debt, you
will need the price and yield to maturity on the firm’s existing long-term
onds. Go to
http:
finramarkets.morningstar.com/BondCente
Default.jsp?,
click “Search,” click “Corporate,” type AAP ticker “Show Results.” A list of
AAP’s outstanding bond issues will appear.
Assume that APP’s policy is to use the yield to maturity of its 10 year
obligations as its cost of debt (or closest maturity). Find the bond issue
that is as close to 10 years from maturity as possible. (Note: It is also best
not to select a Callable Bond….)
Find the yield to maturity for your chosen bond issue (it is in the column
titled “Yield”) and enter that yield as your pretax cost of debt into your
spreadsheet.
http:
finra-markets.morningstar.com/BondCente
Default.jsp?
Next, copy and paste the data in the entire table of info listed for AAP the
FINRA web pages into Excel . [For a quick way to do this, refer to
Appendix 1]
6. You now have the price for each bond issue, but you need to know the
size of the issue. Returning to the Web page, go to the row of the bond
you chose and click the Issuer Name in the first column. This
ings up a
Web page with all of the information about the bond issue. Scroll down
until you find “Amount Outstanding” on the right side. Noting that this
amount is quoted in thousands of dollars (e.g. $60,000 means 60 million)
ecord the issue amount in the appropriate row of your spreadsheet.
Repeat this step for all of the bond issues.
7. The price for each bond issue in your spreadsheet is reported as a
percentage of the bond’s par value. For example, XXXXXXXXXXmeans that the
ond issue is trading at 104.5% of its par value. You can calculate the
market value of each bond issue by multiplying the amount outstanding by
Price /100. Do so for each issue (listed on the first 2 pages of bonds) and
then calculate the total of all the bond issues. This is the market value of
AAP’s debt.
8. Compute the weights for AAP’s equity and debt based on the market
value of equity (Market Capitalization) and AAP’s market value of debt,
computed in step 5.
9. Assuming that AAP has a tax rate of 20%, calculate its effective cost of
debt capital.
10. Calculate AAP’s WACC.
11. Calculate AAP’s net debt by subtracting its cash (collected in step 2) from
its debt. Recalculate the weights for the WACC using the market value of
equity, net debt, and enterprise value. Recalculate AAP’s WACC using the
weights based on the net debt. How much does it change?
12. How confident are you of your estimate? Which implicit assumptions did
you make during your data collection efforts?
13. Looking at the yields of all the AAP bonds outstanding, How would have
the WACC changed if you had calculated the cost of debt using the
information for all the bonds outstanding instead of just using the 5 year
ond (Do an estimate. Don’t recalculate)
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.9 billion. Other development costs that will be required to finish the stores
project is $800 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 stores are expected to be $4,300,000,000
($4,300 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
determine 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
cashflow 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,
ut for this exercise 2 years will suffice):
(Costs of Goods Sold* + SG&A)/Sales
*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
eak 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 net working 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 cu
ent 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) + CF0
For the IRR include all cash flows in your excel calculation.
Should AAP invest in the project?
Appendix 1
Once you have obtained the whole list of bonds, your internet page will look like this:
To avoid copying bond by bond, in your
owser, export the page as a pdf
Once you have exported the page you can use a pdf converter into excel. If you have
a complete version of acrobat you can use that or you can use the free online
convertor such as I love pdf. https:
www.ilovepdf.com
Once you have the list as an excel file you will need to add the issue amount for each
ond. As explained on step 6… Copy your excel table onto the excel worksheet
provided
https:
www.ilovepdf.com
WACC Calculation
Points Points Obtained AAP Project Summary Page
Part 1
0.5 Yield for 10-year Treasury bonds:
AAP's Market Capitalization:
Number of Shares Outstanding
AAP's Cu
ent Stock Price
1.5 AAP's Market Capitalization
AAP's Cost of Captal
Market Risk Premium (Given in the instructions)
0.5 AAP Beta
3 AAP's cost of equity using CAPM E(Ri) = Rf + [E(RM) – Rf] × βi
0.5 AAP's Cash:
AAP's cost of debt:
4 Total market value of AAP's debt: Remember to use the information of all AAP Bonds
1 Weight of debt:
1 Weight of equity:
1 AAP's wiehted cost of equity:
4 AAP's WACC:
AAP's effective cost of debt:
Net debt:
1 Weight of debt:
1 Weight of equity:
3 AAP's WACC with net debt:
2 a. How much does the WACC change if you use net debt versus the first method of calculation?
3 b. How confident are you of your estimate? Which implicit assumptions did you make during your data collection efforts?
3 XXXXXXXXXXc . Looking at the yields of all the AAP bonds outstanding, How would have the WACC changed if you had calculated the cost of debt using the information
for all the bonds outstanding instead of just using the 10 year bond (Do an estiamte)
30
ç Bond Information
Issuer Name Bond Symbol Callable Coupon Maturity Yield Amount Outstanding Market Value of