Overview

Problem Definition

You are an analyst at an investment fund.  The fund is considering an investment in Walmart.  As part of the decision-making process, your team is building a DCF model to value the stock.  Another analyst is working on getting the free cash flows. Your job is to determine the cost of capital for Walmart.

Main Question

Determine the WACC for Walmart. Ensure that you estimate the market value of debt by valuing the individual debt instruments.   Once you have a baseline estimate,  you want to figure o ut t he v ariability o f t hat estimate.  Given the standard deviations in the Monte Carlo Inputs section, and assuming these variables all follow normal distributions, visualize the probability distribution of the WACC via a histogram and a probability table.  Which of the variables has the greatest contribution to the WACC? Use at least 10,000 simulations.

Notes

•  Be careful that the returns are daily.  You will need to divide the risk free rate by 252 to get a daily rate. After you get an estimate from CAPM, you will need to multiply it by 252.

• You can feel free to modify any of the input files to make them easier to load. But keep in mind that if you did this on the job, and you needed to keep updating the model, you would have to do this every time.  If you cleaned it up with code then it would be automated. At the end I will show my model which automates the cleanup process.

• If there is not a specific day associated with a debt maturity, only a year, assume it is December 31st.

• If there is a range of coupons given, you can assume the midpoint of the range is the coupon.

• If there is not enough information to calculate a bond price, or the bond already expired, just use the principal as the market price of the bond.

Inputs

Download the following files from the course site under Project 3:

•  SP500 Prices.xlsx

• WMT Debt Details.xls

• WMT Prices.xlsx

• WMT Income Statement.xlsx

• WMT Balance Sheet.xlsx

In these files you will find the historical stock prices for Walmart, the historical index values for the S&P 500, the individual debt instruments for Walmart, and the income statement and balance sheet for Walmart.

In addition to the information in the spreadsheets, also consider that Walmart currently has a coupon bond trading in the market (details below). The risk-free rate and LIBOR rates are also given in the inputs table. Also reference Walmart’s stock information in the inputs table.

Bonus

The bonus for this problem is to complete it without doing any manual data cleanup.  That is to say that your code should be doing all of the clean up of the data, and your model should run end-to-end from the source files as given. If you submit without adding any cleaned up data files, then I will know you are attempting the bonus.

Please note that in a regular workflow, you would do this part first, then have the cleaned up data to work with for the main model.  But because this is probably new to most people, I would recommend saving this for last. Clean the data up manually, complete the project, then come back and make it work with the original data files by automating the clean up. To make this a bit easier, I would recommend taking notes of everything that you’re doing to manually clean up the data, so that when you go to write the code, you know exactly what it needs to do.

Also note that we have not covered everything you will need for the bonus. Here are some additional resources to help:

• Advanced Read Excel (look at skiprows)

• Dropping missing values

• Marking Existing Values as Missing

• Intro to Regular Expressions (not necessarily required, but I used them)

• Regular Expression Reference

Baseline Model Inputs

Baseline Inputs

Variable                                                  Baseline Value

Monte Carlo Inputs

Standard Deviations

Variable                                         Standard Deviation

Submission & Grading

Submission

Work off of the "montecarlo.xlsx" and "montecarlo.ipynb" files on Canvas and our Google Classroom.

It is up to you whether you submit a pure Python, pure Excel, or combination model. For whichever tools you use, you should use the templates, so those submitting a combination model will submit both a Jupyter notebook and an Excel workbook, both based off the templates. With the combination model, your ultimate answer should be in the Jupyter notebook.

Regardless of the tools you use, running the model should run the full Monte Carlo simulation based off the model and simulation inputs. It is not acceptable to hard-code the simulations.

If you modify any of the data files, then submit them along with your model. Ensure that the names of the files are the same as the original names. If you do not submit a data file, then I will assume your model works off the original and copy in the original file. Your bonus score will be based off of which files you submit. If you do not submit any data files (you’reusing the original for all), then you will get the full bonus points, assuming the information from the data files was correctly incorporated into the model.

Solutions

Selected solutions with the baseline inputs:

• WACC: 5.19%

•  MV Debt: $82.7 billion

•  Cost of Equity: 5.96%

•  Pre-Tax Cost of Debt: 2.74%

Grading

Grading Breakdown

Category                              Percentage