ABCDEFGHIJKLM
1
Financial Model - Core Best Practices (mannhowie.com)
2
3
1. Color Coding Cells
4
5
Best Practices - Color Coding Cells
6
LegendNote
7
Col Headings (e.g. Metrics)
Bold black text with bottom border for headings, include unit value
8
Row fields (e.g. Ticker)
Black text for rows fields, include unit value and period
9
Assumptions (e.g. GOOGL)
Blue text and yellow background is for model drivers that require user input
10
Input cell (e.g. 2021-09-30)
Blue is for input cells, hard-coded historical data and other constants
11
Formula cell (e.g. $1,890)
Black text for cells, formulas, caluclations and links to other cells in the same spreadsheet. Negative values in brackets
12
Link cell (e.g. Alphabet Inc Class A)
Green is for links to other spreadsheets in the same file
13
14
Company Metrics
15
MetricsValue
16
TickerGOOGL
17
Company name
Alphabet Inc Class A
18
Share price ($US)
$185
19
Shares on issue (m)
12,168.3
20
Market cap ($USm)
$2,251,866
21
Net Debt ($USm)$(127,715)
22
Enterprise Value ($USm)
$2,124,151
23
24
Net Cash ($USm) - Latest 10Q Statements
25
TickerDateCash ($USm)
LT Debt ($USm)
Net Cash($USm)
Source
26
GOOGL2021-09-30$142,003$14,288$127,715
https://abc.xyz/investor/static/pdf/2021_Q3_alphabet_10Q.pdf?cache=cdb5740
27
AMZN2021-09-30$78,988$50,055$28,933
https://s2.q4cdn.com/299287126/files/doc_financials/2021/q3/06c52e44-38c4-42b0-90c4-14916435e83e.pdf
28
MSFT2021-09-30$130,615$50,039$80,576
https://www.microsoft.com/en-us/Investor/sec-filings.aspx
29
FB2021-09-30$58,075$0$58,075
https://investor.fb.com/financials/sec-filings-details/default.aspx?FilingId=15299621
30
BABA2021-09-30$68,819$20,627$48,192
https://www.alibabagroup.com/en/news/press_pdf/p211118.pdf
31
32
33
2. Financial Tables
34
35
Best Practices - Financial Tables
36
LegendNote
37
Year end (currency unit)
Financial year end of the period and currency and unit value
38
FY2020
Financial year end shorthand header (older periods should be to the left)
39
Headline metric
Bold entire row for headline metrics (e.g. Revenue, EBITDA, EBIT, NPAT)
40
Indent submetric
Left indent any sub-metrics used to calculate a headline metric (e.g. Cost of Revenue, Interest, Taxes)
41
Input cell
Blue text hard coded input cells (financial format, negative in brackets)
42
Formula field
Black text for calculated fields
43
44
Alphabet Income Statement
45
Dec year end ($USm)
FY2017FY2018FY2019FY2020
46
Revenue110,855,000136,819,000161,857,000182,527,000
47
Cost of Revenue
(45,583,000)(59,549,000)(71,896,000)(84,732,000)
48
Gross Profit65,272,00077,270,00089,961,00097,795,000
49
Operating Expense
(36,390,000)(45,878,000)(54,033,000)(56,571,000)
50
EBIT28,882,00031,392,00035,928,00041,224,000
51
 Interest & Other(1,689,000)3,521,0003,697,0006,858,000
52
Profit Before Tax
27,193,00034,913,00039,625,00048,082,000
53
 Tax(14,531,000)(4,177,000)(5,282,000)(7,813,000)
54
Net Profit After Tax
12,662,00030,736,00034,343,00040,269,000
55
56
Source: yahoo finance (https://finance.yahoo.com/quote/GOOG/financials?p=GOOG)
57
58
3. Key Metrics
59
60
Best Practices - Key Metrics
61
LegendNote
62
FY2020
Financial year end shorthand column align with the respective financial table
63
Revenue growth yoy
Italic black text for calculated key metrics, include comparison period (e.g. yoy, qoq)
64
23%
Italic black text for computed metrics, include relevant metric (%, x)
65
Grey out cell if computed metric not applicable
66
67
68
Alphabet Income Statement Key Metrics
69
Key MetricsFY2017FY2018FY2019FY2020
70
Revenue growth yoy
23%18%13%
71
Gross profit margin
59%56%56%54%
72
Operating expense growth yoy
26%18%5%
73
EBIT margin26%23%22%23%
74
Effective tax rate53%12%13%16%
75
NPAT margin11%22%21%22%
76
77