from copy import deepcopy
import pytest
import pyexlatex as pl
import pandas as pd
from plufthesis.info_models import ThesisTypes
from plufthesis.thesis import UFThesis
from plufthesis.transformers import elevate_sections_by_one_level
from tests.config import INPUT_FILES_DIR
EXAMPLE_BODY = [
pl.Chapter([
'Some content',
pl.UnorderedList(['a', 'b', 'c']),
],
title='First'),
pl.Chapter([
'Chapter content',
pl.Section([
'Section content',
pl.SubSection([
'Subsection content',
pl.SubSubSection([
'Subsubsubsection content',
pl.CiteP('person'),
],
title='First sub sub')
],
title='First sub')
],
def get_content():
pd_mono = pl.Monospace('pandas')
dfs_mono = pl.Monospace('DataFrames')
df_mono = pl.Monospace('DataFrame')
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')
xw_arg_decorator = pl.Monospace('@xw.arg')
xw_ret_decorator = pl.Monospace('@xw.ret')
x_mono = pl.Monospace('x')
expand_table_mono = pl.Monospace("expand='table'")
random_choice_mono = pl.Monospace('random_choice')
random_choice_py = pl.Monospace('random.choices')
lecture = get_combining_excel_python_lecture()
pd_read_write_exercise = get_read_write_excel_pandas_lab_lecture(
).to_pyexlatex()
xlwings_exercise = get_read_write_xlwings_lab_lecture().to_pyexlatex()
read_from_excel_example = pl.Python("""
my_value = sht.range("G11").value # single value
# all values in cell range
my_value = sht.range("G11:F13").value
# expands cell range down and right getting all values
my_values = sht.range("G11").expand().value
""")
write_to_excel_example = pl.Python("""
sht.range("G11").value = 10
sht.range("G11").value = [10, 11] # horizontal
sht.range("G11:G12").value = [10, 11] # vertical
# table, DataFrame from elsewhere
sht.range("G11").value = df
""")
return [
pl.Section([
lp.DimRevealListFrame([
"We have learned how to use both Excel and Python to solve problems. Throughout this process, there "
"were advantages and disadvantages of each tool for each problem.",
"I wanted you to know both tools so you could pick whichever is best to tackle your problem",
"For larger problems, you'll likely find some parts are better with Excel and some with Python",
"After this lecture, you won't need to choose one anymore, you can use both at once."
],
title='Leveraging the Power of Both Tools'),
],
title='Introduction'),
pl.Section([
lp.DimRevealListFrame([
[pd_mono, 'has built-in tools for working with Excel'],
[
pd_mono, 'can read Excel workbooks into', dfs_mono,
'and it can write', dfs_mono, 'back to Excel workbooks'
],
'For simple uses, this may be enough. If you just need to get data from somewhere once and put it in your '
'workbook, or you have your data in Excel and want to analyze it in Python, this is sufficient',
[
"If you want to manipulate your workbook from Python, or you want to run Python code from your "
"workbook, look to", xlwings_mono
]
],
title=f'How Far does {pd_mono} Get Us?'),
lp.Frame([
lp.Block([
df_from_excel_example,
pl.VSpace(-0.3),
pl.UnorderedList([
"If you don't pass a sheet name, it will take the first sheet."
])
],
title='Reading Excel Files',
overlay=next_slide),
pl.VFill(),
lp.Block(
[
df_to_excel_example,
pl.VSpace(-0.3),
pl.UnorderedList(
[[
'We are passing', index_false_mono,
'because usually the 0, 1, 2 ... index is not useful'
],
[
"If you had set your index to something useful, then don't include",
index_false_mono
]])
],
title='Writing to Excel Files',
overlay=next_slide),
pl.VFill(),
lp.AlertBlock([[
'When', pd_mono,
'writes to a workbook, it replaces the file. Do not write over an existing '
'workbook that you want to keep!'
]],
title='Careful When Writing!',
overlay=next_slide),
],
title=f'Reading and Writing to Excel Files with {pd_mono}'
),
lp.Frame([
InClassExampleBlock([
pl.UnorderedList([
'Download the contents of the "Read Write Excel Pandas" folder in Examples',
'Ensure that you put the Excel file and notebook in the same folder for it to work',
'Follow along with the notebook'
])
],
title=
f'Read and Write to Excel using {pd_mono}')
],
title='Showcasing Reading and Writing to Excel Files'),
pd_read_write_exercise.presentation_frames(),
],
title=f'To and From Excel with {pd_mono}',
short_title=pd_mono),
pl.Section([
lp.TwoColumnGraphicDimRevealFrame([[
'The easiest way to use Python from in Excel, or Excel from in Python, is',
xlwings_mono
], "In Windows, it's based off the Microsoft COM API, which is some common tools they give for creating "
"plugins.",
"It's still in active development, but overall it works pretty well and is far beyond where we were "
"a few years ago"],
graphics=[
images_path(
'xlwings-logo.png')
],
title=
f'Introducing {xlwings_mono}'),
lp.DimRevealListFrame(
[['There are two main ways to use', xlwings_mono],
[
'You can',
pl.
Bold('manipulate Excel from Python,'),
'which gives you the full power of Excel from',
"within Python. In this class we'll focus on reading and writing values, but you can do anything",
"that you would normally be able to in Excel, but by executing Python code."
],
[
'Or you can',
pl.Bold('run Python from Excel'),
'using one of two approaches:',
pl.Underline('Python as a VBA replacement'), 'and',
pl.Underline('user-defined functions (UDFs)')
],
'We will focus on manipulating Excel from Python in this class. I encourage you to explore '
'the other two approaches on your own.'],
title=f'What are the Main Ways to use {xlwings_mono}?'),
lp.TwoColumnGraphicDimRevealFrame([
pl.TextSize(-1),
[
xlwings_mono,
'allows us to write Python values into Excel and fetch Excel values into Python'
],
'There is also a complete VBA API, meaning you can do everything that you could do with VBA '
'from within Python, which means you have the full capabilities of Excel within Python',
'There are also convenient features to work with entire tables at once rather than '
'a single value'
],
graphics=[
images_path(
'python-excel-logo.png')
],
title=
'Using Python to Drive Excel Models'
),
lp.Frame([
lp.Block([read_from_excel_example],
title='Read Values from Excel'),
lp.Block([write_to_excel_example],
title='Write Values to Excel')
],
title='Write and Read Values to and from Excel'),
InClassExampleFrame([
'Download the contents of the "xlwings" folder in Examples',
'Ensure that you put the Excel file and notebook in the same folder for it to work',
'Follow along with the notebook'
],
title=f'How to Use {xlwings_mono}',
block_title=f'Trying out {xlwings_mono}'),
xlwings_exercise.presentation_frames(),
],
title=
f'Introducing Full Python-Excel Connection with {xlwings_mono}',
short_title=f'{xlwings_mono}'),
pl.PresentationAppendix([
lecture.pyexlatex_resources_frame,
pd_read_write_exercise.appendix_frames(),
xlwings_exercise.appendix_frames(),
])
]
def get_content():
random.seed(1000)
lecture = get_visualization_lecture()
intro_pandas_lab = get_intro_to_pandas_lab_lecture().to_pyexlatex()
styling_pandas_lab = get_pandas_styling_lab_lecture().to_pyexlatex()
graphing_lab = get_intro_python_visualization_lab_lecture().to_pyexlatex()
appendix_frames = [
lecture.pyexlatex_resources_frame,
intro_pandas_lab.appendix_frames(),
styling_pandas_lab.appendix_frames(),
graphing_lab.appendix_frames()
]
ret_model = RetirementModel()
ret_df = ret_model.get_formatted_df(num_years=12)
ret_table = lt.Tabular.from_df(ret_df, extra_header=pl.Bold('Retirement Info'))
plt_mono = pl.Monospace('matplotlib')
df_mono = pl.Monospace('DataFrame')
df_basic_example = pl.Python(
"""
>>> import pandas as pd
>>> df = pd.DataFrame()
>>> df['Sales'] = [1052, 212, 346]
>>> df['Category'] = ['Aprons', 'Apples', 'Bowties']
df
"""
)
plot_example_code = pl.Python(
"""
>>> %matplotlib inline
>>> ret_df.plot.line(x='Time', y='Salaries')
"""
)
return [
pl.Section(
[
lp.DimRevealListFrame(
[
"So far we've had one main output from our model, number of years",
"Salaries and wealth over time have also been outputs, but we haven't had a good way of understanding "
"that output. It's a bunch of numbers.",
"This is where visualization comes in. We have some complex result, and want to make it easily "
"interpretable."
],
title='Why Visualize?'
),
lp.Frame(
[
pl.Center(ret_table)
],
title='What we Have so Far'
),
lp.GraphicFrame(
images_path('excel-insert-chart.png'),
title='Visualization in Excel'
),
lp.GraphicFrame(
lg.ModifiedPicture(
images_path('python-visualization-landscape.jpg'),
[
lg.Path('draw', [(0.52, 0.52), (0.85, 0.67)], options=['red'], draw_type='rectangle',
overlay=lp.Overlay([2]))
]
),
title='An Overwhelming Number of Options in Python'
),
lp.DimRevealListFrame(
[
["Ultimately, we will be creating graphs using", plt_mono, "but we won't use it directly."],
["Instead, we will use", pd_mono],
[pd_mono, "is actually creating its graphs using", plt_mono,
"for us, but it is simpler to use."]
],
title='Explaining Python Visualization in This Class'
),
InClassExampleFrame(
[
'I will now go back to the "Dynamic Salary Retirement Model.xlsx" Excel model to '
'add visualization',
'I have also uploaded the completed workbook from this exercise '
'as "Dynamic Salary Retirement Model Visualized.xlsx"',
'Follow along as I go through the example.',
],
title='Visualization in Excel',
block_title='Adding Graphs to the Dynamic Salary Retirement Excel Model'
),
],
title='Visualization Introduction',
short_title='Intro'
),
pl.Section(
[
lp.DimRevealListFrame(
[
[pd_mono, "does", pl.Bold('a lot'), 'more than just graphing. We will use it throughout the '
'rest of the class.'],
"Previously we've worked with lists, numbers, strings, and even our custom types (our model dataclasses)",
[pd_mono, "provides the", df_mono, "as a new type that we can use."],
f'Before we can get to graphing, we must learn how to use the {df_mono}.'
],
title='Some Setup Before we can Visualize in Python'
),
lp.Frame(
[
['A', df_mono, 'is essentially a table. It has rows and columns, just like in Excel.'],
pl.VFill(),
lp.Block(
[
pl.UnorderedList([
'Add or remove columns or rows',
'Group by and aggregate',
'Load in and output data from/to Excel and many other formats',
'Merge and join data sets',
'Reshape and pivot data',
'Time-series functionality',
'Slice and query your data',
'Handle duplicates and missing data'
])
],
title=f'Some Features of the {df_mono}'
)
],
title=f'What is a {df_mono}?'
),
lp.Frame(
[
df_basic_example,
pl.Graphic(images_path('df-basic-example.png'), width=0.3)
],
title=f'A Basic {df_mono} Example'
),
InClassExampleFrame(
[
'I will now go through the notebook in '
'"Intro to Pandas and Table Visualization.ipynb"',
'Follow along as I go through the example.',
'We will complete everything up until DataFrame Styling'
],
title='Introduction to Pandas',
block_title='Creating and Using Pandas DataFrames'
),
intro_pandas_lab.presentation_frames(),
lp.DimRevealListFrame(
[
['It is possible to add styling to our displayed tabular data by styling the', df_mono],
'The styling is very flexible and essentially allows you to do anything',
'Out of the box, it is easy to change colors, size, and positioning of text, add a caption, do '
'conditional formatting, and draw a bar graph over the cells.'
],
title='Styling Pandas DataFrames'
),
InClassExampleFrame(
[
'I will now go through the next section in '
'"Intro to Pandas and Table Visualization.ipynb"',
'Follow along as I go through the example.',
'This time we are covering the remainder of the notebook starting from "DataFrame Styling"'
],
title='Introduction to Pandas',
block_title='Creating and Using Pandas DataFrames'
),
styling_pandas_lab.presentation_frames(),
],
title='Tables with Pandas DataFrames',
short_title='Pandas'
),
pl.Section(
[
lp.Frame(
[
lp.Block(
[
plot_example_code,
pl.Graphic(images_path('python-salaries-line-graph.pdf'), width=0.5)
],
title=f'Line Graphs using {pd_mono}'
)
],
title='A Minimal Plotting Example'
),
lp.MultiGraphicFrame(
[
images_path('excel-salaries-line-graph.png'),
images_path('python-salaries-line-graph.pdf'),
],
vertical=False,
title='Basic Graph Types: Line Graphs'
),
lp.MultiGraphicFrame(
[
images_path('excel-salaries-bar-graph.png'),
images_path('python-salaries-bar-graph.pdf'),
],
vertical=False,
title='Basic Graph Types: Bar Graphs'
),
lp.MultiGraphicFrame(
[
images_path('excel-salaries-box-whisker-plot.png'),
images_path('python-salaries-box-graph.pdf'),
],
vertical=False,
title='Basic Graph Types: Box and Whisker Plots'
),
InClassExampleFrame(
[
'I will now go through '
'"Intro to Graphics.ipynb"',
'Follow along as I go through the entire example notebook.',
],
title='Introduction to Graphing',
block_title='Graphing Using Pandas'
),
graphing_lab.presentation_frames(),
],
title='Graphing using Pandas',
short_title='Graphs'
),
pl.PresentationAppendix(appendix_frames)
]
def _get_contents(self):
return lp.Frame([
InClassExampleBlock([pl.UnorderedList(self.block_contents)],
title=self.block_title)
],
title=self.title)
import pyexlatex as pl
import pyexlatex.table as lt
import pyexlatex.presentation as lp
import pyexlatex.graphics as lg
import pyexlatex.layouts as ll
salary_block_content = [
lp.adjust_to_full_size_and_center(pl.Equation(str_eq=r'S_t = S_0 (1 + r_l)^t (1 + r_p)^p')),
pl.UnorderedList([
f'{pl.Equation(str_eq="S_t")}: Salary at year {pl.Equation(str_eq="t")}',
f'{pl.Equation(str_eq="S_0")}: Starting wealth',
f'{pl.Equation(str_eq="r_l")}: Return for cost of living',
f'{pl.Equation(str_eq="r_p")}: Return for promotion',
f'{pl.Equation(str_eq="t")}: Number of years',
f'{pl.Equation(str_eq="p")}: Number of promotions',
])
]
def get_content():
random.seed(1000)
ev_bet = (999999 / 1000000) * 1 + (1 / 1000000) * (-750001)
xlwings_mono = pl.Monospace('xlwings')
pd_mono = pl.Monospace('pandas')
quickstart_mono = pl.Monospace('quickstart')
read_from_excel_example = pl.Python("""
my_value = xw.Range("G11").value # single value
# all values in cell range
my_value = xw.Range("G11:F13").value
# expands cell range down and left getting all values
my_values = xw.Range("G11").expand().value
""")
write_to_excel_example = pl.Python("""
xw.Range("G11").value = 10
xw.Range("G11").value = [10, 11] # horizontal
xw.Range("G11").value = [[10], [11]] # vertical
xw.Range("G11").value = [[10, 11], [12, 13]] # table
""")
ball_options = ['fill', 'circle', 'inner sep=8pt']
blue_ball_options = ball_options + ['blue']
red_ball_options = ball_options + ['red']
def rand_pos():
return random.randrange(-150, 150) / 100
blue_nodes = [
lg.Node(None, (rand_pos(), rand_pos()), options=blue_ball_options)
for _ in range(10)
]
red_nodes = [
lg.Node(None, (rand_pos(), rand_pos()), options=red_ball_options)
for _ in range(10)
]
red_blue_ball_graphic = lg.TikZPicture([
lg.Rectangle(5, 5, shape_options=['blue', 'thick']), *blue_nodes,
*red_nodes
])
lecture = get_monte_carlo_lecture()
intro_mc_python_lab = get_intro_monte_carlo_lab_lecture().to_pyexlatex()
mc_python_lab = get_python_retirement_monte_carlo_lab_lecture(
).to_pyexlatex()
mc_excel_lab = get_excel_retirement_monte_carlo_lab_lecture().to_pyexlatex(
)
return [
pl.Section([
lp.
TwoColumnGraphicDimRevealFrame([
[
pl.Bold('Monte Carlo Simulation'),
'is a technique which allows understanding the probability '
'of acheiving certain outputs from a model.'
],
'This gives the modeler a greater understanding of the likelihood of different outputs, rather '
'than relying on a single number',
],
graphics=[
images_path(
'random-numbers.jpg')
],
title=
'What is Monte Carlo Simulation?'),
lp.DimRevealListFrame([
r'Imagine you have a one-time opportunity to place a bet for \$1. ',
r'If you win the bet, you will receive \$2. If you lose the bet, you will lose \$750,000. '
r'You cannot avoid the payment by declaring bankruptcy.',
r'The odds of winning the bet are 999,999/1,000,000. In 1/1,000,000 you lose the \$750,000.',
fr'The expected profit from the bet is \${ev_bet:.2f}. Should you take it? Depends on your '
fr'risk tolerance.',
'Therefore not only the expected outcome matters, but also what other outcomes may occur and '
'their probabilities.'
],
title='Why Use Monte Carlo Simulation?'),
lp.GraphicFrame(explore_parameters_graphic(),
title='Monte Carlo Simulation in One Picture'),
lp.DimRevealListFrame([
'Monte Carlo simulation is carried out similarly to external scenario analysis.',
'The main difference is that we manually picked specific cases for the inputs with scenario '
'analysis.',
'In Monte Carlo simulation, we assign distributions to the inputs, and input values are drawn '
'from the distributions for each run of the model',
'Finally, we can fit a probability distribution to the outputs to be able to talk about the '
'chance of a certain outcome occurring'
],
title=
'Basic Process for Monte Carlo Simulation')
],
title='Introduction'),
pl.Section(
[
lp.DimRevealListFrame([
'Monte Carlo simulation can be applied to any model',
'It is generally easier to run them in Python than in Excel.',
"With pure Excel, you're either going to VBA or hacking something with data tables",
'In Python, just loop for N iterations, each time drawing inputs, running the model, and collecting '
'outputs.',
[
'We will start with a pure Python model, then move to using',
xlwings_mono, 'to add Monte Carlo '
'simulations to our Excel models.'
],
],
title=
'Running Monte Carlo Simulations - Python or Excel?'
),
lp.Frame([
lp.Block([
r'You have \$1,000 now and need to pay \$1,050 in one year. You have available to you '
r'two assets: a risk free asset that returns 3%, and a stock that returns 10% with a '
r'20% standard deviation. How much should you invest in the two assets to maximize '
r'your probability of having at least \$1,050 in one year?'
],
title='An Investment Problem'),
pl.VFill(),
pl.UnorderedList([
lp.DimAndRevealListItems([
'We must first construct the basic model which gets the portfolio value for given '
'returns',
'Then draw values of the stock return from a normal distribution, and run the model '
'many times and visualize the outputs. ',
'Then repeat this process with each weight to determine the best weight.'
])
])
],
title='An Example Application'),
InClassExampleFrame([
'Go to the course site and download the Jupyter notebook "MC Investment Returns.ipynb" from '
'Monte Carlo Examples',
'I will go through this example notebook to solve the problem from the prior slide.'
],
title='Simluating Portfolio Values',
block_title=
'Example for Simulating Portfolio Values'),
pl.TextSize(-2),
intro_mc_python_lab.presentation_frames(),
pl.TextSize(0),
],
title='Running a First Monte Carlo Simulation',
short_title='Run MC',
),
pl.Section(
[
lp.Frame([
pl.TextSize(-2), 'For the model given by:',
pl.Equation(str_eq='y = f(X)', inline=False),
pl.Equation(str_eq='X = [x_1, x_2, ..., x_n]',
inline=False),
pl.UnorderedList([[
pl.Equation(str_eq='y:'), 'Model output'
], [pl.Equation(str_eq='X:'), 'Model input matrix'],
[
pl.Equation(str_eq='x_i:'),
'Value of $i$th $x$ variable'
]]),
'To run $N$ Monte Carlo simulations, follow the following steps:',
pl.OrderedList(
[[
'Assign a probability distribution for each',
pl.Equation(str_eq='x_i')
],
[
'For each',
pl.Equation(str_eq='x_i'),
'randomly pick a value from its probability distribution. Store them as',
pl.Equation(str_eq='X_j')
],
[
'Repeat the previous step $N$ times, yielding',
pl.Equation(str_eq='[X_1, X_2, ..., X_N]')
],
[
'For each',
pl.Equation(str_eq='X_j'), 'calculate',
pl.Equation(str_eq='y_j = f(X_j)')
],
[
'Store the values of',
pl.Equation(str_eq='X_j'), 'mapped to',
pl.Equation(str_eq='y_j')
],
[
'Visualize and analyze',
pl.Equation(str_eq='y_j'), 'versus',
pl.Equation(str_eq='X_j')
]])
],
title='Monte Carlo Simulation Process'),
lp.DimRevealListFrame([
'There are a multitude of outputs we can get from a Monte Carlo simulation. We saw a few '
'already in the example.',
[
pl.Bold('Outcome probability distributions'),
'are the main output. We saw this with two '
'approaches in the example, a',
pl.Underline('histogram'), 'and a',
pl.Underline('probability table.')
],
[
'We also examined the',
pl.Bold('probability of a certain outcome'),
'in whether we reached '
'the desired cash.'
],
[
'The last main output is examining the',
pl.Bold('relationship between inputs and outputs.'),
'for which common approaches include',
pl.Underline('scatter plots'), 'and',
pl.Underline('regressions.')
]
],
title=
'Outputs from Monte Carlo Simulation'),
lp.TwoColumnGraphicDimRevealFrame(
[
pl.TextSize(-3),
'The outcome probability distribution represents the chance of receiving different '
'outcomes from your model.',
'There are two main ways to visualize a probability distribution: a plot and a table.',
[
'The plot, usually a',
pl.Underline('histogram'), 'or',
pl.Underline('KDE'),
'gives a high-level overview of the probabilities and can uncover any non-normal '
'features of the distribution.'
],
[
'The probability table represents the chance of receiving the given value or '
'lower.'
],
'The Value at Risk (VaR) is a common measure calculated in the industry, and it represents '
'the probability of losing at least a certain amount. This would be a subset of this analysis '
'and so this analysis can be used to calculate VaR',
],
graphics=[
images_path('outcome-probability-distribution.png'),
lt.Tabular([
pl.MultiColumnLabel('Probability Table', span=2),
lt.TopRule(),
lt.ValuesTable.from_list_of_lists(
[['Probability', 'Value']]),
lt.TableLineSegment(0, 1),
lt.ValuesTable.from_list_of_lists(
[['25%', '1020'], ['50%', '1039'],
['75%', '1053']]),
lt.BottomRule()
],
align='c|c')
],
title='Outcome Probability Distributions',
graphics_on_right=False,
),
lp.TwoColumnGraphicDimRevealFrame(
[
'Imagine a box which contains red and blue balls. You do not know in advance how many there '
'are of each color.',
'You want to estimate the probability of getting a blue ball when pulling a ball from the box.',
'To evaluate this, you grab a ball, write down its color, and put it back, 1,000 times.',
'You pull a blue ball in 350 out of the 1,000 trials. What is the probability of getting blue?'
],
graphics=[red_blue_ball_graphic
],
title='Probability of a Certain Outcome - A Simple Example'
),
lp.DimRevealListFrame([
'We followed the same logic when estimating the probability of receiving our desired cash '
'in the investment example.',
pl.Equation(
str_eq=
fr'p = \frac{{{pl.Text("Count of positive outcomes")}}}{{{pl.Text("Count of trials")}}}'
),
[
'For the balls example, this is simply',
pl.Equation(str_eq=r'p = \frac{350}{1000} = 0.35'),
],
[
'In the investment example, we used', pd_mono,
'to check for each trial, whether it was a '
'positive outcome (made it a 1) or not (made it a 0). Then the sum is the count of '
'positive outcomes and so the mean is the probability.'
],
],
title=
'Probability of a Certain Outcome, Formally'
),
lp.DimRevealListFrame([
'Monte Carlo simulation can also provide a more comprehensive look at the relationship between '
'inputs and outputs.',
'While sensitivity analysis can be used to estimate the relationship between an input and '
'output, it is usually done with other inputs at their base case',
'The values of inputs may affect how other inputs affect the output. E.g. for the retirement '
'model, an increase in interest rate increases wealth more if the initial salary was higher.',
'As all the inputs change each time, you can get a more realistic view of the relationship, e.g. '
'some trials with a higher interest rate will have high salary and some will have low salary.'
],
title=
'Why Monte Carlo Simulations Help Understand Inputs vs. Outputs'
),
lp.TwoColumnGraphicDimRevealFrame(
[
pl.TextSize(-1),
'A scatter plot is a simple way to visualize the relationship between two variables',
'If there is a relationship, you will see some defined pattern in the points. This may be '
'somewhat of an upward or downward line (linear relationship) or some other shape such '
'as a U (non-linear relationship).',
'If there is no relationship, then there will just be a random cloud of points (lower '
'plot) or a horizontal line.'
],
graphics=[
images_path('scatter-plot-line.png'),
images_path('scatter-plot-no-relationship.png')
],
graphics_on_right=False,
title=
'Visualizing the Relationship between Inputs and Outputs'),
lp.TwoColumnGraphicDimRevealFrame([
pl.TextSize(-2),
'The scatter plots help give a broad understanding of the relationship but do not answer the '
'question, how much will my output change if my input changes? E.g. if I earn 10,000 more '
'for a starting salary, how much sooner can I retire?',
'Simply increasing the input in your model and checking the output is not enough, because it '
'does not take into account how all the other variables may be changing.',
'Multivariate regression is a general tool which is good at answering these kinds of questions, '
'while taking into account all the changing inputs.'
],
graphics=[
images_path(
'excel-multivariate-reg.png'
)
],
title=
'Numerically Analyzing the Relationships'
),
lp.DimRevealListFrame([
pl.TextSize(-1),
'The coefficient in a multivariate regression represents how much the outcome variable '
'changes with a one unit change in the input variable.',
'E.g. a coefficient of -.0002 on starting salary in explaining years to retirement would mean '
r'that a \$1 increase in starting salary is associated with a decrease in years to retirement by .0002 years, or '
r'a \$10,000 increase in starting salary is associated with a decrease in years to retirement by 2 years.',
'All interpretations are "all else constant", meaning that it does not consider relationships '
'between the inputs. E.g. if starting salary is higher because of a good economy, and interest '
'rates are also higher due to the good economy, the starting salary coefficient is not taking '
'into account the increase in interest rates.',
'Be careful about units. If you use decimals for percentages, you will need to multiply or '
'divide by 100 to get the effect in percentages.'
],
title='How to use Multivariate Regression'
),
InClassExampleFrame(
[
'I will now go through adding a Monte Carlo simulation to the Dynamic Salary Retirement '
'Model in Python',
'The completed example is on the course site in '
'Monte Carlo Examples',
],
title='Adding Monte Carlo Simulation to a Formal Model',
block_title='Dynamic Salary Retirement with Monte Carlo'),
mc_python_lab.presentation_frames(),
],
title='A More Formal Treatment of Monte Carlo Simulation',
short_title='Formal MC',
),
pl.Section([
lp.DimRevealListFrame([
'In pure Excel, it is much more difficult to run a Monte Carlo Simulation',
'Without going to VBA, typically the only way is to use a data table',
'A data table can be used in situations where you only want to have one or two inputs '
'varying at once. Just generate the random inputs and use them as the axes of the data table',
'If you want to vary more than two inputs, VBA or Python would be required',
'There are also add-ons that accomplish this but they are usually not free'
],
title=
"How is it Different Running MC in Excel?"),
lp.DimRevealListFrame([
'The process for Monte Carlo Simulation which works for any number of variables is '
'very similar to what we were doing in Python.',
'We are still just changing the inputs, running the model, and storing the outputs from each run',
[
'Using', xlwings_mono,
'from Python code we can change and retrieve the values of cells'
],
'This allows us to change inputs, run the model, and store outputs, just as in Python, but running our Excel model.',
'We can either analyze the outputs in Python or output them back to Excel for analysis'
],
title=
'Monte Carlo in Excel with More than Two Variables'
),
InClassExampleFrame([
'Go to the course site and download the "Dynamic Salary Retirement Model.xlsx" and '
'"Excel Monte Carlo.ipynb" from the Monte Carlo Examples',
'Open up the Jupyter notebook and follow along with me',
'The completed Excel model is also there in case you lose track. Visualizations '
'were added after running the Jupyter notebook on the original Excel model.',
],
title='Monte Carlo Excel Retirement Model',
block_title=
f'Using {xlwings_mono} to Run Monte Carlo Simulations'
),
mc_excel_lab.presentation_frames(),
],
title='Monte Carlo Simulation in Excel',
short_title='Excel MC'),
pl.PresentationAppendix([
lecture.pyexlatex_resources_frame,
intro_mc_python_lab.appendix_frames(),
mc_python_lab.appendix_frames(),
mc_excel_lab.appendix_frames(),
])
]
def get_content():
random.seed(1000)
dcf_overview_graphic = get_dcf_graphic()
cc_graphic = get_dcf_graphic(include_output=False, include_fcf=False)
fcf_graphic = get_dcf_graphic(include_output=False, include_coc=False)
lecture = get_dcf_cost_capital_lecture()
enterprise_equity_value_excercise = get_enterprise_value_lab_lecture().to_pyexlatex()
cost_equity_exercise = get_dcf_cost_equity_lab_lecture().to_pyexlatex()
cost_debt_exercise = get_dcf_cost_debt_lab_lecture().to_pyexlatex()
wacc_graphics = get_wacc_graphics()
return [
pl.Section(
[
lp.DimRevealListFrame(
[
pl.JinjaTemplate('A {{ "discounted cash flow valuation (DCF)" | Bold }} is a method of '
'determining the value of a stock.').render(),
'Other ways include the dividend discount model and approaches based on comparables',
'The dividend discount model only works well for stable companies that pay dividends with '
'constant growth.',
'Comparable approaches can give a rough idea of a valuation but never take into account the '
'specifics of the company',
'DCF valuation can be applied to any company and is based on the particulars of the company'
],
title='What is a DCF?'
),
lp.GraphicFrame(
dcf_overview_graphic,
title='The DCF in One Picture'
),
lp.Frame(
[
lp.Block(
[
pl.Equation(str_eq=r'V = \sum_{t=0}^T \frac{CF^t}{(1 + r)^t}', inline=False)
],
title='Financial Asset Value'
),
pl.UnorderedList([lp.DimAndRevealListItems([
'The value of any financial asset is the present value of its future cash flows',
'The cash flows for a stock are dividends. The dividend discount model takes the present '
'value of future dividends.',
'To find the value of a business, find the present value of its future free cash flows'
], vertical_fill=True)]),
],
title='Motivating the DCF'
),
lp.TwoColumnGraphicDimRevealFrame(
[
pl.TextSize(-1),
['The goal of cost of capital estimation is to determine the',
pl.Bold('weighted average cost of capital (WACC)')],
['This can broadly be broken down into two components: estimating the',
pl.Underline('cost of equity'), 'and estimating the', pl.Underline('cost of debt')],
'Cost of equity is typically estimated using the Capital Asset Pricing Model (CAPM)',
'Cost of debt is usually estimated from the interest payments and book value of debt'
],
graphics=[lp.adjust_to_full_size_and_center(cc_graphic)],
title='Overview of Cost of Capital Estimation'
),
lp.TwoColumnGraphicDimRevealFrame(
[
pl.TextSize(-1),
'The goal of free cash flow estimation is to determine the historical and future '
'free cash flows (FCF) for the company.',
'Historical financial statements, including the income statement, balance sheet, and '
'statement of cash flows are used to determine historical FCF',
'It is the job of the analyst building the model to project those FCF into the future',
'This is usually done by projecting the financial statements into the future'
],
graphics=[lp.adjust_to_full_size_and_center(fcf_graphic)],
title='Overview of Free Cash Flow Estimation'
)
],
title='Introduction to Discounted Cash Flow (DCF) Valuation',
short_title='DCF Intro'
),
pl.Section(
[
lp.DimRevealListFrame(
[
'The enterprise value of the business is the asset value or the cost to purchase the '
'entire company',
pl.Equation(
str_eq=f'{pl.Text("Enterprise Value")} = {pl.Text("Equity Value")} + '
f'{pl.Text("Debt Value")} - {pl.Text("Cash")}'
),
'A stock represents only the equity value or market capitalization of a business',
'By determining the enterprise value, we can back into the equity value to get the stock price'
],
title='Enterprise Value vs. Equity Value'
),
pl.TextSize(-1),
enterprise_equity_value_excercise.presentation_frames(),
pl.TextSize(0),
],
title='Enterprise and Equity Value',
short_title='EV'
),
pl.Section(
[
lp.Frame(
[
pl.TextSize(-1),
lp.Block(
[
EquationWithVariableDefinitions(
r'r_i = r_f + \beta (r_m - r_f) + \epsilon',
[
'$r_i$: Return on stock $i$',
'$r_f$: Return on risk free asset',
'$r_m$: Return on market portfolio',
r'$\beta$: Covariance of stock returns with market risk premium',
r'$\epsilon$: Idiosyncratic return, mean 0',
]
)
],
title='Capital Asset Pricing Model (CAPM)'
),
pl.UnorderedList([lp.DimAndRevealListItems([
'We will use historical stock price data along with CAPM to produce an estimate of the '
'cost of equity.',
'Ultimately, $r_i$ is the estimate of the cost of equity',
], vertical_fill=True)])
],
title='How Can CAPM be used for Estimating the Cost of Equity?'
),
lp.Frame(
[
lp.Block(
[
pl.Equation(str_eq=r'r_i = r_f + \beta (r_m - r_f) + \epsilon')
],
title='Capital Asset Pricing Model (CAPM)'
),
pl.UnorderedList([lp.DimAndRevealListItems([
r'The three returns can all be estimated from historical data. Therefore $\beta$ and '
r'$\epsilon$ are the unknowns. But $\epsilon$ has mean zero so we can ignore it '
r'for estimation.',
'We will estimate the historical beta, then assume that the beta is still valid today to '
'come up with the current $r_i$ as the cost of equity.',
r'$\beta$ can be estimated by regressing the historical stock returns of the company on '
r'the historical market risk premiums. The $\beta$ is then the coefficient of the market '
r'risk premium in the regression.'
], vertical_fill=True)])
],
title='Overview of Cost of Equity Estimation'
),
InClassExampleFrame(
[
'Go to the course site and download "Determining the Cost of Equity.ipynb" '
'and "price data.xlsx" from '
'Cost of Equity Python Examples',
'Make sure that you place these two in the same folder',
'We are using historical prices to calculate the cost of equity using CAPM',
'We will use a risk free rate of 3% for the exercise',
],
title='Using Price Data to Estimate Cost of Equity in Python',
block_title='Python CAPM Estimation'
),
InClassExampleFrame(
[
'Go to the course site and download "DCF Cost of Equity.xlsx" from '
'Cost of Equity Excel Examples',
'We are using historical prices to calculate the cost of equity using CAPM',
'We will use a risk free rate of 3% for the exercise',
],
title='Using Price Data to Estimate Cost of Equity in Excel',
block_title='Excel CAPM Estimation'
),
cost_equity_exercise.presentation_frames(),
lp.DimRevealListFrame(
[
'As we will cover in more detail when we get to WACC, we need to have the market values of '
'both equity and debt along with the costs to be able to caluclate the WACC.',
'The market value of equity for a publicly traded company is straightforward. Just calculate '
f'the {pl.Bold("market capitalization")} as the number of shares outstanding multiplied by the current '
'share price.',
'The market capitalization can be used directly as the market value of equity.'
],
title='Market Value of Equity'
)
],
title='Cost of Equity Estimation',
short_title='Equity'
),
pl.Section(
[
lp.DimRevealListFrame(
[
'We want to estimate the cost of debt for the company, which more specifically should be '
'the marginal interest cost of raising one additional dollar via debt.',
['There are two general approaches to estimating this: the',
pl.Underline('financial statements approach'), 'and the',
pl.Underline('market rate of bonds approach')],
'The market rate of bonds approach is better able to capture the current rate when it has '
'changed substantially over time, but it requires price, coupon, and maturity information on '
'a bond.',
'The financial statements approach uses only the income statement and balance sheet, and '
'represents a weighted average historical cost of debt.'
],
title='Overview of Estimating the Cost of Debt'
),
lp.DimRevealListFrame(
[
'The financial statements approach uses interest expense from the income statement and '
'total debt from the balance sheet to estimate the cost of debt',
'With this approach, we can estimate the cost of debt by a very simple formula',
pl.Equation(
str_eq=rf'r_d = \frac{{{pl.Text("Interest Expense")}}}{{{pl.Text("Total Debt")}}}'
),
'Calculate this for the most recent data available and use this as the cost of debt'
],
title='The Financial Statements Approach to Cost of Debt'
),
lp.DimRevealListFrame(
[
'The cost of debt is about raising new debt, so it is more accurate to look at the market to '
'determine how much the company would have to pay for new debt.',
"The yield to maturity (YTM) of the company's bonds can be calculated. A weighted average of "
"the YTMs can be used as an estimate of the cost of debt.",
'The YTM is representing the required rate of return on the bond for the investor, which is '
'equivalent to the cost of the bond for the company',
'The YTM is simply the IRR of the bond, considering the current market price of the bond'
],
title='The Market Rate of Bonds Approach to Cost of Debt'
),
lp.DimRevealListFrame(
[
pl.TextSize(-1),
['Debt has an interesting feature in our tax system: debt is', pl.Underline('tax deductible.')],
'The amount a company has to pay in income tax is taken as a percentage of earnings before tax (EBT).',
'As interest is taken out while calculating EBT, it lowers the tax payment.',
'Think about two hypothetical companies with the exact same operations, revenues, costs, etc. '
'One is financed completely with equity and the other with 50% debt. They will both have the '
'same EBIT but the EBT will be lower for the debt firm and so the taxes will be lower for the '
'debt firm, likely giving the debt firm a higher value than the equity firm.',
'What this means for cost of capital estimation is that all our calculations will be based on '
f'pre-tax numbers, then we multiply by $(1 - {pl.Text("tax rate")})$ to get the after-tax cost '
f'of debt to use in the WACC.'
],
title='After-Tax Cost of Debt'
),
cost_debt_exercise.presentation_frames(),
lp.DimRevealListFrame(
[
"If you have taken a debt course, you should be familiar with the fact that bonds' values "
"change over time.",
'The value of a bond can be determined (just like any financial asset) by taking the present value of '
'future cash flows (here, interest and principal payments). ',
"If the discount rate for the company changes, the value of the bonds change, as the interest "
"payments are contracted and will remain the same",
"The discount rate will change when the riskiness of the firm's debt changes, e.g. taking on "
"additional debt, starting a new project, having a bad operating year, etc."
],
title='What is the Market Value of Debt?'
),
lp.DimRevealListFrame(
[
'Say a company issues a 3-year bond with a 10% coupon. When issued, the riskiness of the firm implies '
'it should have a 10% discount rate. In other words, the true cost of debt is 10%. '
'The bond is at par (value 1,000).',
'One year later, the firm has a bad year, and now lenders are requiring a 15% rate to lend to the company',
r'Due to this, the price of the existing bond has dropped to \$918.71.',
r'If we calculate the IRR on this \$918.71 bond, it comes to 15%, which is the true YTM or cost of debt',
'The coupon rate on the bond is still 10%, and the book value of debt on the balance sheet is '
'still 1,000 so based on the financial statements approach the cost of debt would still be 10%.'
],
title='Why Should we Care about the Market Value of Debt?'
),
lp.DimRevealListFrame(
[
'There are three main approaches to calculating the market value of debt for use in the '
'WACC calculation, depending on what data you have available',
['If all you have is financial statements, you must just',
pl.Underline('assume the book value of debt equals the market value of debt.')],
['If you also have an estimate of the current cost of debt obtained from the market as well '
'as an average maturity of debt, you can use the', pl.Underline('hypothetical bond approach.')],
['Finally, if you have all the individual debt instruments, you can',
pl.Underline('calculate the market value of individual instruments.')]
],
title='Approaches to Calculating the Market Value of Debt'
),
lp.DimRevealListFrame(
[
"For the purposes of this class, we won't deal with seniority. But you should keep it in mind "
"in the future when estimating the market value and cost of debt.",
'Seniority represents the payoff order during bankruptcy. The most senior loans will be paid '
'first, and if there is still money left over, then the more junior loans will be paid.',
'As there is a higher expected value in recovery, senior loans are less risky and so should '
'have a lower rate associated with them.',
'In the prior exercise, when valuing individual debt instruments, we assumed a single cost of '
'debt, when in reality, it should be adjusted for the seniority.',
],
title='Dealing with Seniority of Debt'
),
InClassExampleFrame(
[
'Go to the course site and download "Market Value of Debt.ipynb" and "debt data.xlsx" from '
'Cost of Debt Examples',
'Ensure you have the Jupyter notebook and the Excel spreadsheet in the same folder.',
'We will go through the Jupyter notebook to show the three approaches to estimating '
'the market value of debt.'
],
title='Calculating the Market Value of Debt',
block_title='MV Debt Example'
),
],
title='Cost of Debt Estimation',
short_title='Debt'
),
pl.Section(
[
lp.Frame(
[
lp.Block(
[
EquationWithVariableDefinitions(
f'{pl.Text("WACC")} = r_e w_e + r_d (1 - t) w_d',
[
'$r_e$: Cost of equity',
'$w_e$: Weight of equity',
'$r_d$: Pre-tax cost of debt',
'$t$: Tax rate',
'$w_d$: Weight of debt'
],
space_adjustment=-0.4
)
],
title='Weighted Average Cost of Capital (WACC)'
),
pl.UnorderedList([lp.DimAndRevealListItems([
'So now from the prior sections we have the cost of equity, market value of equity, '
'cost of debt, and market value of debt.',
'The weights of debt and equity are found by dividing that market value by the sum of '
'both market values.'
], vertical_fill=True)])
],
title='Calculating WACC'
),
lp.Frame(
[
pl.Center(adjust_to_size(wacc_graphics[0], 0.9, 0.35, keep_aspect_ratio=True)),
pl.Center(adjust_to_size(wacc_graphics[1], 0.9, 0.35, keep_aspect_ratio=True)),
],
title='What the Weighted Part of WACC Means'
)
],
title='Putting it All Together: Calculating the WACC',
short_title='WACC'
),
pl.PresentationAppendix(
[
pl.TextSize(-2),
lecture.pyexlatex_resources_frame,
pl.TextSize(-1),
enterprise_equity_value_excercise.appendix_frames(),
cost_equity_exercise.appendix_frames(),
cost_debt_exercise.appendix_frames(),
pl.TextSize(0),
]
)
]