Valuing companies with Excel & Python — Part 2 : Historical Assessment and Model Building

“All models are wrong, but some are useful” – George Box

1. Context is Key — Historical Assessment

#visual inspection of assumptions
ws = wb.sheets['3 Statement Page']
d = {}
for row in it.chain(range(5,16), range(18,25),range(28,39), range(41,45), range(48,54),range(56,61), range(63,67),range(69,72)):
rw = []
for col in range(5,20):
char = get_column_letter(col)
rw.append(ws[char + str(row)].value)
d[ws['C'+str(row)].value] = rw
df = pd.DataFrame(d).transpose()

df.columns = group.loc['fillingDate'][::-1][-15:]
df.loc['fillingDate'] = pd.to_datetime(group.loc['fillingDate'][::-1][-15:]).values
for i in range(len(df)-1):
ts = pd.DataFrame([df.loc[list(d.keys())[i]], df.loc['fillingDate']]).transpose().dropna()
ts = ts.set_index('fillingDate')

fig, ax = plt.subplots()
ax.plot(ts)
ax.set_ylabel(list(d.keys())[i])
plt.title(f'{ticker} {list(d.keys())[i]}')
  1. Gross or Operating Margin trend — is the margin improving or worsening over time?
  2. D&A vs Capital Expenditure — is the business under or over depreciating its assets?
  3. Cash Conversion Cycle — is the business getting more or less favourable terms against its customers and suppliers?
  4. Goodwill — is the company acquiring to grow, and is it possibly overpaying?
  5. Debt — what kind of debt is the company using? Is it increasing or decreasing leverage? What terms is it getting on that debt?
  6. Cash flows — are operating cash flows growing? Is the business investing consistently? Is it a net payer or receiver of financing?
DuPont Analysis of Alphabet since 2017
Returns on Capital Metrics, Cash Conversion Metrics and Liquidity Metrics

2. Looking Through a Fogged Windshield — Modelling the Financial Statements

Modelling Income Statement and Balance Sheet Assumptions
lb_five = df.iloc[:,-5:].mean(axis = 1, skipna = True)
lb_five_to_ten = df.iloc[:,-10:-5].mean(axis = 1, skipna = True)
lb_ten_to_fifteen = df.iloc[:,-15:-10].mean(axis = 1, skipna = True)
alpha = 0.4
ema_estimates = alpha * lb_five + (1 - alpha) * (alpha * lb_five_to_ten + (1 - alpha) * lb_ten_to_fifteen)
variance = df.std(axis = 1, skipna = True)
revenue_growth  =    [0.20,0.17,0.15,0.12,0.10,0.08,0.06]
cost_of_goods = [0.4,0.4,0.4,0.4,0.4,0.4,0.4]
sg_a = [0.12,0.12,0.12,0.12,0.12,0.12,0.12]
r_d_expense = [0.14,0.14,0.14,0.14,0.14,0.14,0.14]
d_a_rate = [0.15,0.15,0.15,0.15,0.15,0.15,0.15]
amort_rate = [0.3,0.3,0.3,0.3,0.3,0.3,0.3,0.3]
beta = [0.95]
exit_multiple = [12]
end_growth = [0.02]
ws['T5:Z5'].value = revenue_growth
ws['T6:Z6'].value = cost_of_goods
ws['T7:Z7'].value = sg_a
ws['T8:Z8'].value = r_d_expense
ws['T9:Z9'].value = d_a_rate
ws['T10:Z10'].value = amort_rate
ws['R309'].value = beta
ws['V306'].value = exit_multiple
ws['V308'].value = end_growth
base_valuation_em = ws.range('S347').value
base_valuation_ggm = ws.range('T347').value
Projected Free Cash Flow for Alphabet 2022–2028
Weighted Average Cost of Capital Calculation for Alphabet
Discount Factors
Base Case Valuation of Alphabet Using an EV/EBITDA and GGM Approach
Alphabet Projected Share Count
Alphabet Projected Share Price and Valuation

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Quantamental Value

Quantamental Value

Fundamental investing, with a statistical overlay