def get_monte_carlo_python_exercise() -> LabExercise:
retirement_model = pl.Monospace('RetirementModel')
bullet_contents = [
[
pl.TextSize(-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 years to retirement 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.'
],
[
pl.TextSize(-1),
"Continue from the first lab exercise. Now you are also concerned that your assembly line will not be "
"as efficient amd 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 beginning salary and NPV. Also run a regression "
"to quantitatively determine which has a greater effect."
]
]
return LabExercise(bullet_contents,
'Monte Carlo Python',
f"Monte Carlo Simulation of Python Models",
label='lab:monte-carlo-python')
def get_monte_carlo_excel_exercise() -> LabExercise:
bullet_contents = [
[
'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 '
r'the chance that the NPV will be more than \$800,000,000.'
],
[
"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."
]
]
return LabExercise(bullet_contents,
'Monte Carlo Excel',
f"Monte Carlo Simulation of Excel Models",
label='lab:monte-carlo-excel')
def get_dcf_fcf_simple_forecast_exercise() -> LabExercise:
# NOTE: to get answers, ran Forecast Sales COGS simple but loading in these data instead
bullet_contents = [
[
'Go to Canvas and download "Debt Interest.xlsx" from Lab Exercises > Forecasting > Simple',
'Forecast the next value of total debt using trend regression approach',
'Forecast the next value of interest using the four approaches (average, recent, trend reg, 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 reg, trend CAGR)',
],
]
answer_contents = [
[
r'The forecasted value of total debt should be \$6,867',
r'The directly forecasted values of interest should be \$1,600, \$1,900, \$2,300, and \$2,391, '
r'for average, recent, trend reg, trend CAGR, respectively',
r'The % of debt forecasted values of interest should be \$2,072, \$2,139, \$2,379, and \$2,312, '
r'for average, recent, trend reg, trend CAGR, respectively',
],
]
return LabExercise(bullet_contents,
'Simple Forecast',
f"Forecasting Simple Time-Series",
label='lab:dcf-fcf-forecast-simple',
answers_content=answer_contents)
def get_intro_monte_carlo_python_exercise() -> LabExercise:
bullet_contents = [
[
pl.TextSize(-2),
'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).',
pl.Equation(str_eq=r'P = \frac{d_1}{r_s - g}'),
r'The next dividend will be \$1, and your baseline estimates of the cost of capital and growth are '
r'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'
],
[
pl.TextSize(-1), '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.'
]
]
return LabExercise(bullet_contents,
'Intro Monte Carlo',
f"Monte Carlo Simulation of DDM",
label='lab:intro-monte-carlo-python')
def to_pyexlatex(self) -> LabExercise:
bullet_contents = []
answer_contents = []
for exercise in self.exercises:
if exercise.exercise:
bullet_contents.append(
exercise.exercise.to_pyexlatex_content())
else:
bullet_contents.append([])
if exercise.answers:
answer_contents.append(exercise.answers.to_pyexlatex_content())
else:
answer_contents.append([])
resources = [res.to_pyexlatex() for res in self.resources]
frame_title = self.short_title or self.title
label = self.label or "labs:" + "-".join(
frame_title.casefold().split())
return LabExercise(
bullet_contents,
self.title,
frame_title,
label,
answers_content=answer_contents,
resources=resources,
)
def get_dcf_fcf_complex_forecast_exercise() -> LabExercise:
# NOTE: to get answers, ran Forecast Quarterly Financial Statements.ipynb but loading in these data instead
bullet_contents = [
[
'Go to Canvas and download "CAT Balance Sheet.xlsx" and "CAT Income Statement.xlsx" from '
'Lab Exercises > Forecasting > Complex',
'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.'
],
]
answer_contents = [
[
r'The forecasted values of cash using the Quarterly Seasonal Trend Model should be \$8,454,920,455, '
r'\$8,833,593,182, \$8,869,693,182, and \$10,251,393,182',
r'The forecasted values of cash using the automated approach should be \$8,071,641,657, \$8,185,822,286, '
r'\$9,132,093,865, and \$9,502,395,879'
],
]
return LabExercise(bullet_contents,
'Complex Forecast',
f"Forecasting Complex Time-Series",
label='lab:dcf-fcf-forecast-complex',
answers_content=answer_contents)
def get_xlwings_exercise() -> LabExercise:
xlwings_mono = pl.Monospace('xlwings')
bullet_contents = [
[[
'For all of the', xlwings_mono,
'lab exercises, work with "xlwings Lab.xlsx".'
],
[
'Use', xlwings_mono,
'to read the values in the column A and then write them beside',
'the initial values in column B'
]], [
'Get the value in C9 and multiply it by 2.5 in Python',
],
[
'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)'
],
[
'In column L, write 5, 10, 15 ... 100 spaced two cells apart, so L1 would have 5, L4 would have 10, '
'and so on.'
]
]
return LabExercise(bullet_contents,
'Dynamically Reading and Writing to Excel',
f"Reading and Writing to Excel with {xlwings_mono}",
label='lab:xlwings')
def get_lab_exercise() -> LabExercise:
pd_mono = pl.Monospace('pandas')
dfs_mono = pl.Monospace('DataFrames')
next_slide = lp.Overlay([lp.UntilEnd(lp.NextWithIncrement())])
df_to_excel_example = pl.Python(
"df.to_excel('data.xlsx', sheet_name='My Data', index=False)")
df_from_excel_example = pl.Python(
"df = pd.read_excel('data.xlsx', sheet_name='My Data')")
index_false_mono = pl.Monospace('index=False')
addin_install_mono = pl.Monospace('xlwings addin install')
addin_install_success = pl.Monospace(
'Successfuly installed the xlwings add-in! Please restart Excel.')
random_seed_py = pl.Monospace('random_seed.py')
random_seed_excel = pl.Monospace('random_seed.xlsm')
quickstart_mono = pl.Monospace('xlwings quickstart')
quickstart_project_mono = pl.Monospace(
'xlwings quickstart my_project_name')
cd_mono = pl.Monospace('cd')
xw_func_decorator = pl.Monospace('@xw.func')
random_choice_mono = pl.Monospace('random_choice')
random_choices_mono = pl.Monospace('random_choices')
random_choice_py = pl.Monospace('random.choices')
xlwings_mono = pl.Monospace('xlwings')
df_returns_example = pl.Monospace("df['Stock Price'].pct_change()")
bullet_contents = [
[[
'In your', xlwings_mono,
'Python file, add a function which will generate 1, 2, ... N in a column for',
'whatever N is passed.'
],
[
'Add a cell in your wookbook containing the number of rows to be generated, and call your UDF in',
'another cell.'
],
[
'Change the number of rows to be generated and see the number of rows in the output change.'
]],
[[
'In your', xlwings_mono,
'project workbook, add a column Stock Price which has five prices below it,',
'100, 110, 115, 108, and 105.'
],
[
'In the Python file, write a UDF, which accepts the top-left cell of this table (the one',
'with Stock Price), and returns the stock returns.'
],
[
'Call this UDF next to the stock price column so that you have the return from last period to this one',
'next to the stock prices.'
], ['You may find', df_returns_example, 'useful for this purpose.'],
['Answers should be 10%, 4.5%, -6%, -2.8%']]
]
return LabExercise(bullet_contents,
'Advanced UDFs',
f"Dynamic and Array UDFs with {xlwings_mono}",
label='lab:dynamic-array-udf')
def get_dcf_cost_debt_exercise() -> LabExercise:
today = datetime.datetime.today().date()
bond_price = 1042.12
coupon_rate = 0.07
maturity_date = datetime.date(today.year + 3, today.month, today.day)
par_value = 1000
tax_rate = 0.35
bullet_contents = [
[
rf'A chemical manufacturer has a {coupon_rate:.1%} coupon, annual pay {par_value} par value bond outstanding, priced '
rf'at \${bond_price} on {today}.',
f'If the bond matures on {maturity_date}, what is the '
rf'cost of debt for this company? The tax rate is {tax_rate:.0%}.',
],
[[
'Go to',
Hyperlink('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."
]],
]
# Levels 1 exercise
l1_pretax_cost_of_debt = np.irr(
[-bond_price] + [coupon_rate * par_value for _ in range(3 - 1)] +
[(1 + coupon_rate) * par_value])
l1_aftertax_cost_of_debt = l1_pretax_cost_of_debt * (1 - tax_rate)
# Level 2 exercise
wmt_int_exp = 641
wmt_total_debt = 74709
wmt_ebt = 4843
wmt_tax_paid = 1233
wmt_tax_rate = wmt_tax_paid / wmt_ebt
wmt_pre_tax_cd = wmt_int_exp / wmt_total_debt
wmt_after_tax_cd = wmt_pre_tax_cd * (1 - wmt_tax_rate)
answer_contents = [
[
f'The pre-tax cost of debt for the chemical manufacturer is {l1_pretax_cost_of_debt:.2%}',
f'The after-tax cost of debt for the chemical manufacturer is {l1_aftertax_cost_of_debt:.2%}',
],
[
f'The pre-tax cost of debt for Walmart in 2019-07-31 is {wmt_pre_tax_cd:.2%}',
f'The after-tax cost of debt for Walmart in 2019-07-31 is {wmt_after_tax_cd:.2%}',
],
]
return LabExercise(bullet_contents,
'DCF Cost of Debt',
f"Finding Cost of Debt Given Financial and Market Info",
label='lab:dcf-cost-debt',
answers_content=answer_contents)
def get_dcf_enterprise_equity_value_exercise() -> LabExercise:
bullet_contents = [
[
pl.TextSize(-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.',
r'For the first two years (years 0 and 1), the company loses \$20 million. Then there is one breakeven year, after which '
r'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 5? What about the enterprise value today? '
'What is the price of the stock today?'
],
[
pl.TextSize(-1),
'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.',
r'The new company pays a dividend of \$1 per share each year for years 0 to 3, before selling it for \$30 million in '
r'year 4.',
r'There are 10 million shares outstanding, \$10 million of debt and \$1 million of cash throughout the '
r'life of the company. The WACC is 10% today.',
'What is the enterprise value at year 5 and today? What is the price of the stock today?'
]
]
answer_contents = [
[
r'The enterprise value at year 5 is \$70 million',
r'The enterprise value at year 0 is \$9.2 million',
r'The equity value at year 0 is \$5.21 million so the share price is \$5.21'
],
[
r'The enterprise value at year 5 is \$30 million',
r'The equity value at year 0 is \$49.2 million so the share price is \$4.92',
r'The enterprise value at year 0 is \$58.2 million',
]
]
return LabExercise(
bullet_contents,
'DCF Value of a Firm',
f"Finding Enterprise and Equity Value Given FCF and WACC",
label='lab:dcf-enterprise-equity',
answers_content=answer_contents)
def get_dcf_cost_equity_exercise() -> LabExercise:
risk_free = 0.02
data_path = LAB_EXERCISES_PATH / 'DCF' / 'Cost of Equity' / 'prices.xlsx'
df = pd.read_excel(data_path)
returns = df.pct_change().dropna()
returns['MRP'] = returns['Market'] - risk_free
model = sm.OLS(returns['Asset'],
sm.add_constant(returns['MRP']),
hasconst=True)
results = model.fit()
beta = results.params['MRP']
market_return = returns['Market'].mean()
cost_of_equity = risk_free + beta * (market_return - risk_free)
recession_cost_of_equity = risk_free + beta * (
(market_return - 0.03) - risk_free)
bullet_contents = [
[
'Go to Canvas and download "prices.xlsx" from Lab Exercises > DCF > Cost of Equity',
f'Assume the risk free rate is {risk_free:.0%}',
'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.'
],
]
answer_contents = [
[
rf'The $\beta$ is {beta:.3f}',
rf'The cost of equity is {cost_of_equity:.2%}',
rf'The cost of equity in the recession is {recession_cost_of_equity:.2%}'
],
]
return LabExercise(bullet_contents,
'DCF Cost of Equity',
f"Finding Cost of Equity Given Historical Prices",
label='lab:dcf-cost-equity',
answers_content=answer_contents)
def get_lab_exercise() -> LabExercise:
pd_mono = pl.Monospace('pandas')
to_excel_mono = pl.Monospace('to_excel')
bullet_contents = [
[
'Download "MSFT Financials.xls" from Canvas in Lab Exercises -> Read Write Excel Pandas',
'Read the sheet "Income Statement" into a DataFrame',
'Write the DataFrame to a new workbook, "My Data.xlsx", with the sheet '
'name "Income Statement"'
],
[[
'Use the same "MSFT Financials.xls" from the first exercise on Slide',
pl.Ref('lab:read-write-excel-pandas-1')
],
'Output to five separate workbooks, named "My Data1.xlsx", "My Data2.xlsx", and so on.',
[
'Do this',
pl.Underline('without'), 'writing the', to_excel_mono,
'command multiple times'
]],
[
'Note: this exercise uses the Advanced material covered in Examples -> Read Write Excel Pandas',
[
'Use the same "MSFT Financials.xls" from the first exercise on Slide',
pl.Ref('lab:read-write-excel-pandas-1')
],
'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',
pl.Underline('without'), 'writing the', to_excel_mono,
'command multiple times'
]
]
]
return LabExercise(bullet_contents,
'Reading and Writing to Excel',
f"Reading and Writing to Excel with {pd_mono}",
label='lab:read-write-excel-pandas')
def get_content():
random.seed(1000)
next_until_end_ov = lp.Overlay([lp.UntilEnd(lp.NextWithIncrement())])
next_slide = lp.Overlay([lp.NextWithIncrement()])
numpy_mono = pl.Monospace('numpy')
lecture = get_dynamic_salary_python_lecture()
return [
pl.Section(
[
lp.DimRevealListFrame(
[
'We have seen how structure and organization can help the readability and maintainability of '
'an Excel model. The same concept exists for our Python models.',
['We already learned that we should use functions to organize logic and a',
pl.Monospace('dataclass'),
'for the model inputs'],
"Typically you'll have functions for each step, those may be wrapped up into other functions which "
"perform larger steps, and ultimately you'll have one function which does everything by calling the"
"other functions.",
"Those are good ideas with any Python model, but working in Jupyter allows us some additional "
"organization and presentation of the model"
],
title='How to Structure a Python Financial Model'
),
lp.DimRevealListFrame(
[
'In Jupyter, we can have code, nicely formatted text, equations, sections, hyperlinks, '
'and graphics, all in one document',
['For all you can do with these nicely formatted "Markdown" cells, see',
Hyperlink('https://www.markdownguide.org/basic-syntax/', 'here'), 'and',
Hyperlink('https://www.markdownguide.org/extended-syntax/', 'here.')],
'We can think of sections in Jupyter as analagous to Excel sheets/tabs. One section for each '
'logical part of your model. Then you can have smaller headings for subsections of the model.',
'Break your code up into small sections dealing with each step, with nicely formatted text '
'explaining it. Add comments where anything is unclear in the code.',
],
title='Using Jupyter for Structure of a Model'
),
lp.GraphicFrame(
[
get_model_structure_graphic()
],
title='Structuring a Python Model'
),
lp.DimRevealListFrame(
[
'When I develop in Jupyter, I have lots of cells going everywhere testing things out',
['When I finish a project in Jupyter, I remove these testing cells and make sure it runs and '
'logically flows from end to end', pl.Monospace('(restart kernel and run all cells)')],
"Run your model with different inputs, and make sure the outputs change in the expected fashion. This "
"is a good way to check your work.",
'There may be outputs in each section, but the final output should be at the end of the notebook',
],
title='Workflow and Final Output'
),
],
title='Structuring a Model in Python and Jupyter',
short_title='Model Structure'
),
pl.Section(
[
lp.DimRevealListFrame(
[
'The first project is aimed at approaching a new time value of money and cash flow model',
'It covers the same concepts as the retirement model, but in a capital budgeting setting',
'We need to introduce some economic equations to handle this model. You should have covered these in microeconomics.'
],
title='Introducing Project 1'
),
lp.GraphicFrame(
images_path('supply-demand-graph.png'),
title='A Quick Review of Supply and Demand'
),
lp.Frame(
[
lp.Block(
[
"There are a couple of basic economic equations we haven't talked about that "
"we'll need for this:",
pl.Equation(str_eq='R = PQ', inline=False),
pl.Equation(str_eq='Q = min(D, S)', inline=False),
pl.UnorderedList([
f'{pl.Equation(str_eq="R")}: Revenue',
f'{pl.Equation(str_eq="Q")}: Quantity Purchased',
f'{pl.Equation(str_eq="D")}: Quantity Demanded',
f'{pl.Equation(str_eq="S")}: Quantity Supplied',
])
],
title='New Required Equations'
)
],
title='Equations for Project 1'
),
lp.Frame(
[
pl.UnorderedList([
lp.DimAndRevealListItems([
'We need to cover one more Python concept and one gotcha before you can complete the first project.',
"On the next slide I'll introduce error handling, and show an example of how it's useful"
],
vertical_fill=True)
]),
lp.AlertBlock(
[
pl.UnorderedList([
f'The NPV function in {numpy_mono} works slightly differently than the NPV function in Excel.',
f'Excel treats the first cash flow as period 1, while {numpy_mono} treats the first cash flow as period 0.',
'If taking NPV where the first cash flow is period 1, pass directly to Excel, and for Python, pass 0 as the first cash flow, then the rest.',
'If taking NPV where the first cash flow is period 0, pass from period 1 to end to Excel and add period 0 separately, pass directly to Python.'
])
],
title='NPV Gotcha',
overlay=next_slide
)
],
title='A Couple More Things on the Python Side'
),
],
title='Project 1 Additional Material',
short_title='Project 1'
),
pl.Section(
[
get_retirement_model_overview_frame(),
lp.Frame(
[
lp.Block(
salary_block_content,
title='Salary with Promotions and Cost of Living Raises'
)
],
title='Revisiting the Model Salary Equation'
),
lp.Frame(
[
pl.UnorderedList([
'For wealths, we need to add the investment return and then the savings in each year',
], overlay=next_until_end_ov),
pl.VFill(),
lp.Block(
[
lp.adjust_to_full_size_and_center(
pl.Equation(str_eq=r'W_t = W_{t-1} (1 + r_i) + S_t v')),
pl.UnorderedList([
f'{pl.Equation(str_eq="S_t")}: Salary at year {pl.Equation(str_eq="t")}',
f'{pl.Equation(str_eq="W_t")}: Wealth at year {pl.Equation(str_eq="t")}',
f'{pl.Equation(str_eq="r_i")}: Investment return',
f'{pl.Equation(str_eq="t")}: Number of years',
f'{pl.Equation(str_eq="v")}: Savings rate',
])
],
title='Calculating Wealth',
overlay=next_until_end_ov,
)
],
title='Building the Wealth Model'
),
InClassExampleFrame(
[
'I will now show the process I use to create a full model.',
'I will be recreating the model "Dynamic Salary Retirement Model.ipynb"',
'Go ahead and download that to follow along as you will also extend it in a lab exercise',
],
title='Creating a Full Model in Python',
block_title='Dynamic Salary Retirement Model in Python',
),
get_extend_dynamic_retirement_python_lab_lecture().to_pyexlatex().presentation_frames(),
LabExercise(
[
[
"Usually I would try to have smaller labs but it didn't fit the format of this lecture. "
"Most will not be able to complete this during class.",
"For this lab, attempt the practice problem "
'"P1 Python Retirement Savings Rate Problem.pdf"',
'This is similar to how the projects will be assigned, so it is good preparation',
"I would encourage you to try it from scratch. If you are totally stuck, try working off "
"of the retirement model I completed today to have a lot of the structure already. If you "
"still are having trouble with that, check the solution and see me in office hours.",
'Note: this is not an official lab exercise you need to submit, it is practice only, but '
'I would highly encourage you to complete it.'
]
],
block_title='Practice Building A Model',
frame_title='Extending the Simple Retirement Model in a Different Way',
label='lab:retire-model'
),
],
title='Building the Dynamic Salary Retirement Model',
short_title='Build the Model'
),
pl.PresentationAppendix(
[
lecture.pyexlatex_resources_frame,
get_extend_dynamic_retirement_python_lab_lecture().to_pyexlatex().appendix_frames(),
]
)
]
def get_dcf_fcf_calculation_exercise() -> LabExercise:
lab_1_inputs = dict(adjustments=100,
change_ar=1000,
change_inventory=500,
change_ap=800,
change_ppe=2000,
dep_amort=200,
net_income=300)
lab_1_nwc = lab_1_inputs['change_ar'] + lab_1_inputs[
'change_inventory'] - lab_1_inputs['change_ap']
lab_1_capex = lab_1_inputs['change_ppe'] + lab_1_inputs['dep_amort']
lab_1_fcf = lab_1_inputs['net_income'] + lab_1_inputs[
'adjustments'] - lab_1_nwc - lab_1_capex
stmt_folder = LAB_EXERCISES_PATH / 'DCF' / 'FCF'
bs_path = os.path.join(stmt_folder, 'WMT Balance Sheet.xlsx')
inc_path = os.path.join(stmt_folder, 'WMT Income Statement.xlsx')
bs_df = pd.read_excel(bs_path, index_col=0)
inc_df = pd.read_excel(inc_path, index_col=0)
bs_data = BalanceSheets.from_df(bs_df)
inc_data = IncomeStatements.from_df(inc_df)
stmts = FinancialStatements(inc_data, bs_data)
lab_2_date_1 = '2019-04-30'
lab_2_date_2 = '2019-07-31'
bullet_contents = [
[
'Calculate free cash flow from the following information:',
f"Net income is {lab_1_inputs['net_income']}, the total of non-cash expenditures is "
f"{lab_1_inputs['adjustments']}, "
f"the changes in accounts receivable, inventory, accounts payable, and PPE are {lab_1_inputs['change_ar']}, "
f"{lab_1_inputs['change_inventory']}, {lab_1_inputs['change_ap']}, and {lab_1_inputs['change_ppe']}, "
f"and depreciation & amortization is {lab_1_inputs['dep_amort']}."
],
[
'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',
f'Get the FCFs for {lab_2_date_1} and {lab_2_date_2}.'
]
]
answer_contents = [
[
fr'The NWC is \${lab_1_nwc:,.0f}',
fr'The CapEx is \${lab_1_capex:,.0f}',
fr'The FCF is \${lab_1_fcf:,.0f}'
],
[
fr'The FCF for {lab_2_date_1} is \${stmts.fcf[lab_2_date_1]:,.0f}',
fr'The FCF for {lab_2_date_2} is \${stmts.fcf[lab_2_date_2]:,.0f}',
]
]
return LabExercise(bullet_contents,
'Calculate FCFs',
f"Free Cash Flow Calculation",
label='lab:dcf-fcf-calc',
answers_content=answer_contents)
def get_dcf_fcf_tv_exercise() -> LabExercise:
ev_ebitda = 18.58
ev_sales = 1.92
ev_fcf = 11.82
pe = 39.30
ebitda = 1500
sales = 7898
shrout = 561
fcf = 2.36 * shrout
earnings = 232
debt = 11631
cash = 4867
wacc = 0.1
growth = 0.03
def p_from_ev(ev):
current_ev = np.npv(wacc, [0] + [fcf] * 4 + [fcf + ev])
equity_value = current_ev - debt + cash
return equity_value / shrout
ev_from_ebitda = ev_ebitda * ebitda
p_from_ebitda = p_from_ev(ev_from_ebitda)
ev_from_sales = ev_sales * sales
p_from_sales = p_from_ev(ev_from_sales)
ev_from_fcf = ev_fcf * fcf
p_from_fcf = p_from_ev(ev_from_fcf)
eps = earnings / shrout
tv_p_from_pe = pe * eps
ev_from_pe = tv_p_from_pe * shrout
p_from_pe = p_from_ev(ev_from_pe)
ev_from_perp = (fcf * (1 + growth)) / (wacc - growth)
p_from_perp = p_from_ev(ev_from_perp)
bullet_contents = [
[
pl.TextSize(-1),
'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 '
fr'FCFs will be \${fcf:,.0f}M. ',
fr'EV/EBITDA is {ev_ebitda:.2f}, EV/Sales is {ev_sales:.2f}, EV/FCF is {ev_fcf:.2f}, and P/E is {pe:.2f}.',
fr'Final period forecasted financial statement values are as follows: EBITDA is \${ebitda:,.0f}M, '
fr'sales is \${sales:,.0f}M, and net income is \${earnings:,.0f}M',
fr'Current period financial statement values are as follows: total debt is \${debt:,.0f}M, and '
fr'cash is \${cash:,.0f}M',
fr'Shares outstanding is \${shrout:,.0f}M and WACC is {wacc:.1%} for the entire time period',
f'The terminal growth rate is {growth:.1%}',
'You can assume the next free cash flow is one year away.'
],
]
answer_contents = [
[
'The stock prices using the five methods are as follows:',
fr'EV/EBITDA: \${p_from_ebitda:.2f}',
fr'EV/Sales: \${p_from_sales:.2f}',
fr'EV/FCF: \${p_from_fcf:.2f}',
fr'P/E: \${p_from_pe:.2f}',
fr'Perpetuity Growth: \${p_from_perp:.2f}',
],
]
return LabExercise(bullet_contents,
'Terminal Values',
f"DCF Stock Price using Terminal Values",
label='lab:dcf-fcf-tv',
answers_content=answer_contents)
def get_lab_exercise() -> LabExercise:
pd_mono = pl.Monospace('pandas')
dfs_mono = pl.Monospace('DataFrames')
next_slide = lp.Overlay([lp.UntilEnd(lp.NextWithIncrement())])
df_to_excel_example = pl.Python(
"df.to_excel('data.xlsx', sheet_name='My Data', index=False)")
df_from_excel_example = pl.Python(
"df = pd.read_excel('data.xlsx', sheet_name='My Data')")
index_false_mono = pl.Monospace('index=False')
addin_install_mono = pl.Monospace('xlwings addin install')
addin_install_success = pl.Monospace(
'Successfuly installed the xlwings add-in! Please restart Excel.')
random_seed_py = pl.Monospace('random_seed.py')
random_seed_excel = pl.Monospace('random_seed.xlsm')
quickstart_mono = pl.Monospace('xlwings quickstart')
quickstart_project_mono = pl.Monospace(
'xlwings quickstart my_project_name')
cd_mono = pl.Monospace('cd')
xw_func_decorator = pl.Monospace('@xw.func')
random_choice_mono = pl.Monospace('random_choice')
random_choices_mono = pl.Monospace('random_choices')
random_choice_py = pl.Monospace('random.choices')
xlwings_mono = pl.Monospace('xlwings')
bullet_contents = [
[[
'If you have not already created your', xlwings_mono,
'project, go back two slides', 'and follow those steps.'
],
[
'Edit the .py file to add a function', random_choice_mono,
'which will call', random_choice_py
],
'The function should accept the items to choose from, and the probabilities.',
'Write out a few possible items to choose from in your workbook. Put probabilities next '
'to them.',
[
'Call your', random_choice_mono,
'function on these inputs, and see it pick a random item'
]],
[[
'Work off the existing', xlwings_mono,
'project from the Level 1 exercise'
],
[
'Now add an additional function', random_choices_mono,
'which will accept the items to',
'choose from, the probabilities, and the number of random choices to generate.'
],
[
'The function should return multiple random choices. In Excel, you should see multiple cells of output,'
'with each cell containing a random choice.'
],
[
'If you return a list from the Python function, it should create this behavior.'
]]
]
return LabExercise(bullet_contents,
'Write a Simple UDF',
f"Getting your Feet Wet with {xlwings_mono}",
label='lab:intro-udf')