Lab Exercise Solutions¶
The solutions to all the lab exercises in the course.
Extending a Simple Retirement Model¶
Description¶
Now we want to see the effect of savings rate on time until retirement, in addition to interest rate
In both Excel and Python, calculate the years to retirement for savings rates of 10%, 25%, and 40%, and each of these cases with each of the interest rate cases, 4%, 5%, and 6%
Be sure that you drag formulas in Excel and use for loops in Python to accomplish this
In total you should have 9 calculated years to retirement numbers, in each of the two models.
Resources¶
Answers¶
Martha has 61.1 years to retirement if she earns a 4% return and saves 10%.
Martha has 41.0 years to retirement if she earns a 4% return and saves 25%.
Martha has 31.9 years to retirement if she earns a 4% return and saves 40%.
Martha has 53.3 years to retirement if she earns a 5% return and saves 10%.
Martha has 36.7 years to retirement if she earns a 5% return and saves 25%.
Martha has 29.0 years to retirement if she earns a 5% return and saves 40%.
Martha has 47.6 years to retirement if she earns a 6% return and saves 10%.
Martha has 33.4 years to retirement if she earns a 6% return and saves 25%.
Martha has 26.7 years to retirement if she earns a 6% return and saves 40%.
Determining Desired Cash in the Dynamic Salary Retirement Excel Model¶
Description¶
We want to relax the assumption that the amount needed in retirement is given by a fixed amount of desired cash
Add new inputs to the model, “Annual Cash Spend During Retirement” and “Years in Retirement”
Calculate desired cash based on interest, cash spend, and years in retirement
Use the calculated desired cash in the model to determine years to retirement
Answers¶
If annual spend is 40k for 25 years in retirement, $563,757.78 should be the retirement cash and there should be 18 years to retirement.
Python Basics - Conditionals¶
Description¶
The Jupyter notebook called Python Basics Lab contains all of the labs for today’s lecture
Please complete the exercises under “Conditionals”
Python Basics - Lists¶
Description¶
Keep working off of Python Basics Lab.ipynb
Please complete the exercises under “Working with Lists”
Python Basics - Functions¶
Description¶
Keep working off of Python Basics Lab.ipynb
Please complete the exercises under “Functions”
Python Basics - Data Types¶
Description¶
Keep working off of Python Basics Lab.ipynb
Please complete the exercises under “Data Types”
Python Basics - Classes¶
Description¶
Keep working off of Python Basics Lab.ipynb
Make sure you have car_example.py in the same folder
Please complete the exercises under “Working with Classes”
Determining Desired Cash in the Dynamic Salary Retirement Python Model¶
Description¶
We want to relax the assumption that the amount needed in retirement is given by a fixed amount of desired cash
Start from the completed retirement model Jupyter notebook Dynamic Salary Retirement Model.ipynb
Add new inputs to the model, “Annual Cash Spend During Retirement” and “Years in Retirement”
Calculate desired cash based on interest, cash spend, and years in retirement
Use the calculated desired cash in the model to determine years to retirement
Resources¶
Answers¶
If annual spend is 40k for 25 years in retirement, $563,757.78 should be the retirement cash and there should be 18 years to retirement.
Getting Started with Pandas¶
Description¶
Work off of the Jupyter notebook Pandas and Visualization Labs.ipynb
Complete the lab exercises in the first section entitled “Pandas”
Styling Pandas DataFrames¶
Description¶
Keep working with the same lab Jupyter Notebook
Complete the lab exercises in the second section entitled “Pandas Styling”
Introduction to Graphing with Pandas¶
Description¶
Keep working with the same lab Jupyter Notebook
Complete the lab exercises in the final section entitled “Graphics”
Adding Sensitivity Analysis to Project 1 - Excel¶
Description¶
Add sensitivity analysis to your Excel model from Project 1
See how the NPV changes when the number of machines and initial demand change
Do a one-way Data Table with a graph for each of the two inputs, then a two-way data table with conditional formatting
Resources¶
Learning How to Use Dictionaries¶
Description¶
For this Python section, lab exercises are in the Jupyter notebook Dicts and List Comprehensions Lab.ipynb
Complete the exercises in the dictionaries section for now
Learning How to Use List Comprehensions¶
Description¶
Continue working on the same Jupyter notebook from the previous lab exercise
Complete the exercises in the List Comprehensions section for now
Adding Sensitivity Analysis to Project 1 - Python¶
Description¶
Add sensitivity analysis to your Python model from Project 1
See how the NPV changes when the number of machines and initial demand change
Output both a hex-bin plot and a styled DataFrame
Resources¶
Adding Scenario Analysis to Project 1 - Excel¶
Description¶
Add external scenario analysis to your Excel model from Project 1
Create three cases, for a bad, normal, and good economy. Change the initial demand and price per phone in each of the cases. Both demand and price should be higher in better economic situations.
Resources¶
Adding Scenario Analysis to Project 1 - Python¶
Description¶
Add external scenario analysis to your Python model from Project 1
Create three cases, for a bad, normal, and good economy. Change the initial demand and price per phone in each of the cases. Both demand and price should be higher in better economic situations.
Resources¶
Generating and Visualizing Random Numbers - Excel¶
Description¶
Complete the following excercise in Excel for n=10 and n=1000
Generate n values between 0 and 1 with a uniform distribution
Generate n values from a normal distribution with a 0.5 mean and 10 standard deviation
Visualize each of the two outputs with a histogram
Calculate the mean and standard deviation of each of the two sets of generated numbers
Re-calculate it a few times, take note of how much the mean and standard deviation change
Resources¶
Generating and Visualizing Random Numbers - Python¶
Description¶
Complete the following excercise in Python for n=10 and n=1000
Generate n values between 0 and 1 with a uniform distribution
Generate n values from a normal distribution with a 0.5 mean and 10 standard deviation
Visualize each of the two outputs with a histogram
Calculate the mean and standard deviation of each of the two sets of generated numbers
Re-calculate it a few times, take note of how much the mean and standard deviation change
Resources¶
Building a Simple Model of Stock Returns¶
Description¶
Create the following model in both Excel and Python
A stock starts out priced at 100. Each period, it can either go up or down.
When it goes up, it will grow by 1%. When it goes down, it will decrease by 1%.
The likelihood of the stock going up is 60%, and down 40%.
Build a model which shows how the stock price changes throughout time. Visualize it up to 100 periods and show the final price.
Resources¶
Extending the Project 1 Model with Internal Randomness¶
Description¶
Add internal randomness to your Project 1 Excel and Python models
Now assume that the interest rate is drawn from a normal distribution
For baseline values of the inputs, you can use a 4% mean and 3% standard deviation
You should be able to run the model repeatedly and see a different NPV each time
Resources¶
Reading and Writing to Excel with Pandas¶
Description¶
Level 1¶
Download “MSFT Financials.xls” from the course site
Read the sheet “Income Statement” into a DataFrame
Write the DataFrame to a new workbook, “My Data.xlsx”, with the sheet name “Income Statement”
Level 2¶
Use the same “MSFT Financials.xls” from the first exercise
Output to five separate workbooks, named “My Data1.xlsx”, “My Data2.xlsx”, and so on.
Do this without writing the to_excel command multiple times
Level 3¶
Note: this exercise uses the Advanced material covered in the example Jupyter notebook Read Write Excel Pandas.ipynb
Use the same “MSFT Financials.xls” from the first exercise
Output to five separate sheets in the same workbook “My Data.xlsx”. The sheets should be named “Income Statement 1”, “Income Statement 2”, and so on.
Do this without writing the to_excel command multiple times
Reading and Writing to Excel with xlwings¶
Description¶
Level 1¶
For all of the xlwings lab exercises, work with “xlwings Lab.xlsx”.
Use xlwings to read the values in the column A and then write them beside the initial values in column B
Level 2¶
Get the value in C9 and multiply it by 2.5 in Python
Level 3¶
Read the table which starts in E4 into Python. Multiply the prices by 2.5, and then output back into Excel starting in cell H5.
Ensure that the outputted table appears in the same format as the original (pay attention to index and header)
Level 4¶
In column L, write 5, 10, 15 … 100 spaced two cells apart, so L1 would have 5, L4 would have 10, and so on.
Monte Carlo Simulation of DDM¶
Description¶
Level 1¶
You are trying to determine the value of a mature company. The company has had stable dividend growth for a long time so you select the dividend discount model (DDM).
\(P = \frac{d_1}{r_s - g}\)
The next dividend will be $1, and your baseline estimates of the cost of capital and growth are 9% and 4%, respectively
Write a function which is able to get the price based on values of the inputs
Then you are concerned about mis-estimation of the inputs and how it could affect the price. So then assume that the growth rate has a mean of 4% but a standard deviation of 1%
Visualize and summarize the resulting probability distribution of the price
Level 2¶
Continue from the first lab exercise
Now you are also concerned you have mis-estimated the cost of capital. So now use a mean of 9% and standard deviation of 2%, in addition to varying the growth
Visualize and summarize the resulting probability distribution of the price
Be careful as in some cases, the drawn cost of capital will be lower than the drawn growth rate, which breaks the DDM. You will need to modify your logic to throw out these cases.
Resources¶
Monte Carlo Simulation of Python Models¶
Description¶
Level 1¶
Work off of your existing Project 1 Python model
You are concerned the NPV could be heavily affected by changes in the interest rate. Instead of fixing it, draw it from a normal distribution with mean of 7% and standard deviation of 2%.
Run the model 10,000 times and collect the NPV results. Visualize the results. Create a table of probabilities and the minimum NPV we could expect with that probability. Output the chance that the NPV will be more than $400,000,000.
Level 2¶
Continue from the first lab exercise. Now you are also concerned that your assembly line will not be as efficient and so the number of phones per machine will be lower. So draw that from a normal distribution with mean 100,000 and standard deviation of 20,000.
As you run the model, also store what were the interest and number of phones corresponding to the NPV. You want to see which has a greater impact on the NPV: interest or number of phones. Visualize the relationship between interest and NPV, and the relationship between number of phones and NPV. Also run a regression to quantitatively determine which has a greater effect.
Resources¶
Monte Carlo Simulation of Excel Models¶
Description¶
Level 1¶
You will be running Monte Carlo simulations on your existing Excel model from Project 1
You are concerned that your estimate for the number of phones that will be sold is incorrect.
The number of phones should instead be drawn from a normal distribution with mean 100,000 and standard deviation of 20,000.
Estimate the model 1,000 times and output the results back to Excel
In Excel, visualize the results. Create a table of probabilities and the minimum NPV we could expect with that probability. Output the chance that the NPV will be more than $400,000,000.
Level 2¶
Continue from the first lab exercise. Now you are also concerned that there is varying quality in the machines, so they may have a different lifespan. Draw that from a normal distribution with mean 10 years and standard deviation of 2 years.
As you run the model, also store what were the number of phones and machine life corresponding to the NPV, all in Excel. You want to see which has a greater impact on the NPV: number of phones or machine life. Visualize the relationship between number of phones and NPV, and the relationship between beginning machine life and NPV. Try to determine which has a greater effect.
Resources¶
Finding Enterprise and Equity Value Given FCF and WACC¶
Description¶
Level 1¶
You are the CFO for a startup developing artificial intelligence technologies. There will be an initial research phase before making any money. Google is watching your development and will purchase the company after it is profitable.
For the first two years (years 0 and 1), the company loses $20 million. Then there is one breakeven year, after which the profit is $10 million for year 3. Finally in year 4, Google purchases the company for $70 million.
The WACC for the company is 15% and it has 1 million shares outstanding. The company has $5 million in debt and $1 million in cash.
What is the enterprise value of the stock at year 4 before Google acquires the company? What about the enterprise value today? What is the price of the stock today?
Level 2¶
A pharmaceutical company developed a new drug and has 4 years to sell it before the patent expires. It forms a new company to manufacture and sell the drug. After 4 years, the company will be sold to someone that wants to continue manufacturing at the lower price. The company is just about to pay a dividend.
The new company pays a dividend of $1 per share each year for years 0 to 3, before selling it for $30 million in year 4.
There are 10 million shares outstanding, $10 million of debt and $1 million of cash throughout the life of the company. The WACC is 10% today.
What is the enterprise value at year 4 and today? What is the price of the stock today?
Answers¶
Level 1¶
The enterprise value at year 4 is $70 million
The enterprise value at year 0 is $9.2 million
The equity value at year 0 is $5.21 million so the share price is $5.21
Level 2¶
The enterprise value at year 4 is $30 million
The equity value at year 0 is $48.5 million so the share price is $4.85
The enterprise value at year 0 is $57.5 million
Finding Cost of Equity Given Historical Prices¶
Description¶
Download “prices.xlsx” from the course site
Assume the risk free rate is 2%
What is the beta and the cost of equity for this company?
If you thought there was going to be a recession, such that the average market return would be 3% lower, then what would you expect the cost of equity to be?
Complete this exercise with the tool of your choice.
Answers¶
The beta is 0.848
The cost of equity is 6.53%
The cost of equity in the recession is 3.98%
Finding Cost of Debt Given Financial and Market Info¶
Description¶
Level 1¶
A chemical manufacturer has a 7.0% coupon, annual pay 1000 par value bond outstanding, priced at $1042.12 on 2021-06-09.
If the bond matures on 2024-06-09, what is the cost of debt for this company? The tax rate is 35%.
Level 2¶
Go to https://stockrow.com and search for WMT to get Walmart’s financials. Calculate the cost of debt for 2019-07-31 using the financial statements approach. Note that you will also need to determine the effective tax rate using actual tax paid and EBT.
Free Cash Flow Calculation¶
Description¶
Level 1¶
Calculate free cash flow from the following information:
Net income is 300, the total of non-cash expenditures is 100, the changes in accounts receivable, inventory, accounts payable, and PPE are 1000, 500, 800, and 2000, and depreciation & amortization is 200.
Level 2¶
Load in the income statement and balance sheet data associated with Project 3, “WMT Balance Sheet.xlsx” and “WMT Income Statement.xlsx”
Calculate the free cash flows from these data. Note that some items are missing in these data such as depreciation. You will just need to exclude any missing items from your calculation
Get the FCFs for 2019-04-30 and 2019-07-31.
Resources¶
Forecasting Simple Time-Series¶
Description¶
Go to the course site and download “Debt Interest.xlsx”
Forecast the next value of total debt using trend regression approach
Forecast the next value of interest using the four approaches (average, recent, trend, CAGR)
Forecast the next value of interest using the % of total debt method, with the percentages forecasted using the four approaches (average, recent, trend, CAGR)
Answers¶
The forecasted value of total debt should be $6,867
The directly forecasted values of interest should be $1,600, $1,900, $2,300, and $2,391, for average, recent, trend, CAGR, respectively
The % of debt forecasted values of interest should be $2,072, $2,139, $2,379, and $2,312, for average, recent, trend, CAGR, respectively
Forecasting Complex Time-Series¶
Description¶
Go to the course site and download “CAT Balance Sheet.xlsx” and “CAT Income Statement.xlsx”
Forecast the next four periods (one year) of cash using both the Quarterly Seasonal Trend Model and the automated software approach.
Plot both forecasts to see how they worked.
Resources¶
Answers¶
The forecasted values of cash using the Quarterly Seasonal Trend Model should be $8,454,920,455, $8,833,593,182, $8,869,693,182, and $10,251,393,182
The forecasted values of cash using the automated approach should be $8,071,641,657, $8,185,822,286, $9,132,093,865, and $9,502,395,879
DCF Stock Price using Terminal Values¶
Description¶
Calculate possible stock prices today for a hypothetical company. Use EV/EBITDA, EV/Sales, EV/FCF, and P/E and the perpetuity growth method to determine five different possible terminal values. You have already determined that the next 5 years FCFs will be $1,324M in each year.
EV/EBITDA is 18.58, EV/Sales is 1.92, EV/FCF is 11.82, and P/E is 39.30.
Final period forecasted financial statement values are as follows: EBITDA is $1,500M, sales is $7,898M, and net income is $232M
Total debt is $11,631M, and cash is $4,867M, both current and final period forecasted
Shares outstanding is $561M and WACC is 10.0% for the entire time period
The terminal growth rate is 3.0%
You can assume the next free cash flow is one year away.
Answers¶
The stock prices using the five methods are as follows:
EV/EBITDA: $27.74
EV/Sales: $13.67
EV/FCF: $14.21
P/E: $14.47
Perpetuity Growth: $18.45