Transcribed Image Text
Walmart, America’s largest retailer had the market’s secondlargest debt issuance in the U.S. in 2019, selling $16 billion in30 year bonds. The funds were to help finance the purchase ofFlipkart, India’s largest online seller. Thanks to Walmart’s highcredit rating, the bonds were classified by Moody’s (one of the topbond rating agencies) as Aa2, or very highly rated. The bond salewas managed by a syndicate of investment banks, including BarclaysPlc, Citigroup Inc., JPMorgan Chase & Co., Bank of AmericaCorp., HSBC Holdings Plc and Wells Fargo & Co.In this problem, you will be provided with publicly availabledata on Walmart and other economic data. Certain assumptions havebeen made about flotation costs in order to expand the analysis.Using the given data and assumptions, answer each of the questionsbelow. If you prefer to do the analysis in Excel, that is fine. Youwill need to submit your Excel file along with this word file. Bothfiles must have your last name included in the file names (severalstudents incorrectly submitted only the original file, with no workshown). I strongly suggest you download this file and make a copywith your name and PA2 file name. Your Excel file must be clear andeasy to follow (also with your name and PA2 in the file name).Final answers must be clearly labeled, and all backup work shown.All solutions must be in the correct order.Data for Walmart as of April 2019Market Price$103.18# Shares (mm, or millions)2,897Long term debt ($mm from balance sheet)$45,396Tax rate (T)25%Walmart beta (?)0.66Current risk free rate (rf)2.59%Estimated market risk premium6.00%Estimated underwriter spread1.0%Estimated additional flotation costs0.5%Estimated total flotation cost (as a % of debt face value)1.50%WalMart data to use20152016201720182019Dividend payout ratio (dividends paid out as a % of netincome)38.02%42.89%45.59%62.04%91.68%Net income ($ millions)$16363$14694$13643$98626670Common equity $ (millions, book value)$85937$83611$80535$8082279634ROE (net income/common equity or NI/CE)19.04%17.57%16.94%12.20%8.38%20152016201720182019Dividend history ($/share)$1.91$1.96$2.00$2.04$2.082020202120222023Dividend estimates ($/share)$2.14$2.05$2.40$2.48Basic “starting point” data:You are to calculate each of the following based on the dataprovided above.1)Value of equity (market capitalization)2)Value of long term debt (use bookvalue)3)Weight of equity4)Weight of debtShow your inputs and/or calculations below (add space asnecessary, make it very clear how you arrived at your final answersabove)Calculate the cost of equity:The growth rate (assumed constant) fordividends can be calculated two different ways, 1) Calculate “g”using historic or projected trends in dividends per share or 2)Calculating “g” from ROE and reinvestment in the firm: g = ROE * (1-payout ratio). Read the following questions andclearly answer the question as asked, using the appropriateestimation method for the growth rate.What is the 3 year growth rate (2020-2023) of expecteddividends?Calculate and describe how you arrivedat your answer (what were the inputs).What is the 4 year growth rate (2015-2019) of actualdividends?Calculate and describe how you arrivedat your answer (what were the inputs).DISCUSS: Do either of these values seem appropriate? Ifso, which one and why?I expect a few sentences that discussissues you see with using the dividend discount model in thissituation. Your answer should reference what you see for the twodifferent values you have just calculated.Now, what about calculating g from ROEand reinvestment in the firm, where g = ROE*(1-payout ratio)? Youhave net income and book value of equity trends, from this, Icalculated ROE (NI/Book value of common equity) and that isprovided in the table above. The firm’s dividend payout ratiotrends are also provided above.What are the difficulties in estimating the "constant"growth rate for Walmart using the firm's ROE and payout ratio? [g =ROE*(1-payout ratio]I expect a discussion of specific input values and issueshere.[Discuss issues here, add more space as needed]Using the CAPM equation for the required cost ofcapital, r = rf + ?(market risk premium), what is the required costof equity (re)?Calculate anddescribe how you arrived at your answer (what werethe inputs).Based on the issues with using the dividend discount model toestimate “g”, we will proceed from here using the estimated CAPMcost of equity.Calculate the cost of debtThe cost of debt, on a pre-tax basis,is the yield on a firm's bonds. Walmart has at least 50 long-termbonds, the weighted average yield on these bonds is 3.2%. On theother hand, a simplified cost of debt can be calculated as theinterest expenses divided by two year average of book value ofdebt. For Walmart, the book and market value of debt is nearlyidentical. This estimated cost of debt is 4.49%.For our purposes, we will takethe average, and use 3.85% as the pre-tax cost of debt(preliminary)Now that we have the average pre-taxcost of debt, we need to account for the underwriter spread. We aresimplifying from flotation costs calculated for equity. Theadjusted pre-tax cost of debt should be estimated rate (fromabove)/(1-UW spread). Estimated pre-tax cost after accounting forunderwriter spread and other flotation costs (the total of 1.5%).We calculate this adjusted pre-tax cost of debt as: originalpre-tax estimate/(1-flotation cost %)What is the adjusted pre-tax cost of debt(rd)? [show your work below]Weighted average cost of capitalWeighted average cost of capital =Be sure to show your inputs in your WACC above and be sure toadjust for taxes as appropriate.Discuss: Is this weighted average cost of capital a “good”hurdle rate to use for all new Walmart projects?.