Overview & Instructions
Business Analysis and Valuation Model
Overview
The purpose of the Business Analysis and Valuation Model (BAV Model) is to assist users in analysing and
valuing companies with atleast two years of historical financial statement data.
This program may be used in conjunction with Business Analysis and Valuation by Krishna G. Palepu, Paul M. Healy, Sue Wright,
Michael Bradbury and Jeff Coulton.
Instructions
1. Import financial statement data into the respective tabs for intended company using DatAnalysis premium /Morningstar DatAnalysis or by manually entering a minimum of 5 years of financial data using annual reports.
2. Conduct classification exercise across three sheets, using Classification Lookup tab for reference. Ensure
that all INDIVIDUAL line items (excluding totals and sub-totals) are appropriately classified to ensure
accuracy of the standardised financial statements and forecasts.
Classification Lookup
Classification Lookup
ERROR:#REF!
• Instructions
1. Select the financial statement (Income Statement, Balance Sheet or Statement of Cash Flows from the drop-down menu below.
2. Select the desired line-item classification heading(s) from the yellow shaded drop-down menu for examples of financial statement line-items typically classified under that heading.
• Classification
Financial Statement to Lookup Balance_Sheet
Classification Category to Lookup Sales
• Examples
Company-operated stores
Equipment sold
Finance participation income
Gain on sale of receivables
Insurance commissions
Licenses
Membership and service fees
Net interest margin
Net sales
Other non-interest income
Other revenue
Product sales
Rentals
Revenue
Royalties & franchise-related fees
Service, rental and maintenance revenues
Services
Services provided
Servicing fee income
Lookups
Lookups
ERROR:#REF!
Input Input Input Input Input Input Input Input Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement Cash Flow Statement
Ordering of Years Years Months Days Units Growth Cost of Equity FinancialStatements Sales Cost of Goods Sold SG&A D&A Other Operating Expenses Interest Income Interest Expense Investment Income Other Income Other Expenses Minority Interest Tax Expense Other Comprehensive Income Net Income Prefe
ed Dividends Cash and Short Term Investments Accounts Receivable Inventory Other Cu
ent Assets Long-Term Tangible Assets Long-Term Intangible Assets Defe
ed Tax Assets Other Long-Term Assets Accounts Payable Short-Term Debt Other Cu
ent Liabilities Other Non-WC Cu
ent Liabilities Long-Term Debt Defe
ed Tax Liabilities Other Long-Term Liabilities Minority Interest Prefe
ed Stock Shareholders' Equity Shares Outstanding Receipts from Customers Income Tax Received Interest Received Payments to Suppliers and Employees Income Tax Paid Interest Paid Other Operating Cash Flows Proceeds from sale of PPE Proceeds from Investment in other financial assets Purchase of PPE Purchase of Intangibles Acquisition of subsidiaries Sale of subsidiaries Investments in other financial assets Proceeds of loan advances Proceeds from share issues Dividends Paid Repayment of Loan Advances Other Financing Cash Flows Opening Cash Balance Effect of Foreign Exchange Rates Net increase/decrease in cash held Effect of Foreign Exchange Rates Closing Cash and Cash Equivalents
Ascending (Oldest First) 1990 January 1 $ Build-up growth CAPM Income_Statement Company-operated stores Cost of sales General and adminsitrative Depreciation Pre-opening expenses Interest income Interest on long-term debt Equity income from associates Gain on sale of Property, Plant and Equipment Loss on sale of Property, Plant and Equipment Minority interest Provision for taxes Extraordinary items Net Income Prefe
ed dividends Cash Accounts receivable, net Finished goods Company common shares acquired for employees' stock plans at cost Net assets of discontinued operation - non-cu
ent portion Computer software development costs Defe
ed Tax Assets Long-term investments Accounts payable Cu
ent portion of capital lease obligations Accrued expenses Other financial liabilities Capital lease obligations Defe
ed Taxes - Cu
ent Asset Defe
ed franchise revenue Minority interest Prefe
ed Stock Accumulated foreign cu
ency translation adjustments Number of common shares outstanding Receipts from Customers Income Tax Received Interest Received Payments to Suppliers and Employees Income Tax Paid Interest Paid Other Operating Cash Flows Proceeds from sale of PPE Proceeds from Investment in other financial assets Purchase of PPE Purchase of Intangibles Acquisition of subsidiaries Sale of subsidiaries Investments in other financial assets Proceeds of loan advances Proceeds from share issues Dividends Paid Repayment of Loan Advances Other Financing Cash Flows Opening Cash Balance Effect of Foreign Exchange Rates
Descending (Newest First) 1991 Fe
uary 2 $'000s Simple growth Simple cost of equity Balance_Sheet Equipment sold Cost of computer equipment Costs of building
ands Amortisation Product development Interest on short-term debt Dividend income Gain on sale of investments Loss on sale of investments Non-recu
ing charges Accretion of redeemable prefe
ed stock Cash and cash equivalents Due from securitisation trust Merchandise inventories Due from affiliates Property and equipment at cost, net Contract renewal rights Long-term receivables Trade accounts payable Cu
ent portion of long-term debt Accrued interest Convertible subordinated debentures Defe
ed Taxes - Cu
ent Liability Other LT liabilities Additional paid-in capital
1992 March 3 $m Cash_Flow_Statement Finance participation income Cost of equipment sold Marketing Salaries and benefits Provision for losses on credit sales Finance costs Rental income Foreign exchange gains Restructuring charges Effects of accounting changes Cash and temporary investments Finance income receivable Raw materials and component parts Due from employees Plant, property and equipment Cost in excess of the Fair Value of Net Assets Acquired Deposits and other non-cu
ent assets Floor plan notes payable Accrued liabilities Discounted lease rentals Defe
ed Taxes - LT Liability Capital in excess of pa
1993 April 4 $bn Gain on sale of receivables Cost of merchandise sold Sales and marketing Provision for relocation Gains from accounting changes the firm reports pre-tax Merger expenses Contract proceeds receivable Finance participation receivable, net Work in progress Net assets of discontinued operation Defe
ed financing costs Non-cu
ent ntoes receivable Notes payable Accrued personnel costs Long-term obligations Common stock
1994 May 5 Insurance commissions Cost of products sold Selling and store operating expenses Repositioning and other special charges Foreign exchange gains Short-term investments Other receivables Prepaid expenses Defe
ed subscriber acquisitions cost, net Investment in Leveraged Leases Customer advances Long-term notes payable Common stock of predecesso
1995 June 6 Licenses Cost of revenues Selling expense Research and development Losses from accounting changes the firm reports pre-tax Time deposits Trade account receivable prepaid income taxes Excess of costs over net assets of acquired companies less amortisation Investment in sales-type and direct financing leases Customer deposits Pos-retirement benefit cost Defe
ed translation adjustment
1996 July 7 Membership and service fees Cost of sales and occupancy costs Service, rental and maintenance Asset impairment Refundable income taxes Finance participation receivable, non-cu
ent portion Defe
ed franchise revenue Senior term notes Net unrealised investment gains
1997 August 8 Net interest margin Cost services Subscriber acquisition costs Restricted cash in spread accounts Goodwill Defe
ed revenue Subordinated notes Retained earnings
1998 September 9 Net sales Financial services costs Unbilled revenues License rights Treasury stock, at cost
1999 October 10 Other non-interest income Other LT assets and defe
ed charges Unrealised losses on marketable securities
2000 November 11 Other revenue
2001 December 12 Product sales
2002 13 Rentals
2003 14 Revenue
2004 15 Royalties & franchise-related fees
2005 16 Service, rental and maintenance revenues
2006 17 Services
2007 18 Services provided
2008 19 Servicing fee income
2009 20
2010 21
2011 22
2012 23
2013 24
2014 25
2015 26
2016 27
2017 28
2018 29
2019 30
2020 31
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
Legend
Style legend
Style Design Comment
Header1 Header1
Header2 Header2
Header3 Header3
Assumption 100 A user driven input
Technical_Input 100 A model input that should not be changed to protect the integrity of the model
Empty_Cell A cell that is left intentionally blank to avoid the risk of e
o
InSheet 100 A link within the worksheet or an interim calculation step
OffSheet 100 A link to another worksheet to minimise the number of inter-worksheet references
Line_SubTotal 100 The sum of elements in the table immediately above
Line_Total 100 The sum of elements above, including sub-totals
Unit / Info USD millions Explanatory text showing helpful information and the units/dimensions of the calculations
Line_Summary 100 The SUM() of everything to the right
Table_Header Qtr Header of a table or of an off-sheet reference
Flag 1 Binary flag - set up as a 'Style' and updated with conditional formatting
Line_ClosingBal 100 The closing balance of a control account
Names
Names and Constants
Item Unit Value
Constants
Days in Year Num# 365
Months per Year Num# 12
Quarters per Year Num# 4
Months per Quarter Num# 3
Thousand Num# 1,000
Million Num# 1,000,000
Very Small Number Num# XXXXXXXXXX
Tolerance Num# 10.00
Tolerance
$ 10 10 10
$'000s 1
$m 0.1
$bn 0.01
Imported Income Statement
Imported Income Statement
ERROR:#REF!
• Instructions
1. . Import financial statement data into the respective tabs for intended company using DatAnalysis premium /Morningstar DatAnalysis or by manually entering a minimum of 5 years of financial data using annual reports.
2.. Conduct classification exercise across three sheets, using Classification Lookup tab for reference. Ensure that all INDIVIDUAL line items (excluding totals and sub-totals) are appropriately classified to ensure accuracy of the standardised financial statements and forecasts.
NB 1: DO NOT classify totals and subtotals as line items. Doing so will result in misstated values throughout the model.
NB 2: Ensure that all units are consistent.
NB 3: Reference Classification Lookup as necessary to ensure co
ect classification.
• Classification
Classification Line Items 2017 2018 2019 2020 2021
Sales 5 Trading revenue 5,628.00 6,854.30 7,095.30 7,918.90 8,916.10
Cost of Goods Sold cost of sale -4,397.50 -5,384.10 -5,568.20 -6,224.80 -6,938.90
Gross Profit 1,230.50 1,470.20 1,527.10 1,694.10 1,977.20
Other Income Other income 2.00 1.10 2.40 3.60 2.90
SG&A Sales and marketing expenses -580.10 -695.10 -731.00 -786.40 -845.80
SG&A Administration expenses -36.20 -42.20 -44.50 -41.70 -41.30
Cost of Goods Sold Occupancy expense -248.60 -305.70 -306.40 -313.10 -293.60
Other Operating Expenses Other expense -75.30 -77.20 -74.00 -72.10 -54.70
Interest Expense 6 Finance costs -10.70 -16.60 -14.30 -36.40 -24.70
Profit before tax 259.20 334.50 359.30 448.00 720.00
Tax Expense 7 Income tax expense -86.80 -101.30 -109.50 -145.70 -213.90
Net profit 172.40 233.20 429.80 302.30 506.10
Other Operating Expenses Total Operation Expense 5,268.00 6,443.20 6,669.40 7,364.40 7,955.80
D&A 12 D+impairment -69.70 -61.10 -56.20 -59.90 -218.50
Other Comprehensive Income cu
ency exchange -1.20 0.60 0.30 -0.20 0.00
Other Comprehensive Income Abnormals -38.2 -0.7 0 -19.7 -7.60
Prefe
ed Dividends Diluted Shares 114.42 114.88 114.88 114.88 114.88
Prefe
ed Dividends Diluted Weighted Shares 112.72 115.96 116.04 115.96 115.59
Prefe
ed Dividends Ordinary Dividends -135.00 -151.60 -163.20 -217.10 0.00
Imported Balance Sheet
Imported Balance Sheet
ERROR:#REF!
• Instructions
1. Ensure "Model" section of Control tab is filled out at a minimum prior to importing financial data to ensure co
ect timing and classification.
2. Import financial statement data into the respective tabs for intended company using a financial database (i.e. FactSet, Capital IQ, Thompson Reuters) or by manually entering a minimum of 2 years of financial data using annual reports.
3. Conduct classification exercise across three sheets, using Classification Lookup tab for reference. Ensure that all INDIVIDUAL line items (excluding totals and sub-totals) are appropriately classified to ensure accuracy of the standardised financial statements and forecasts.
NB 1: DO NOT classify totals and subtotals as line items. Doing so will result in misstated values throughout the model.
NB 2: Reference Classification Lookup as necessary to ensure co
ect classification.
• Classification
Classification Line Items 2017 2018 2019 2020 2021
Cash and Short Term Investments 544.30 453.50 354.70 251.50 263.20
Accounts Receivable 366.40 411.00 344.00 220.30 211.60
Inventory 455.00 577.00 688.70 739.30 938.80
Other Cu
ent Assets 23.20 29.40 33.00 34.70 35.70
Long-Term Tangible Assets 12546 14875 17865 20457 19876
Long-Term Intangible Assets 765 876.5 675 1031.4 1031.4
Defe
ed Tax Assets 15.4 17.4 16.4 22.2 30.3
Other Long-Term Assets 3,319.00 4,456.00 5,049.00 5,876.00 7,688.00
Accounts Payable 521 433 756 854.1 777.4
Short-Term Debt 1222 1955 2012 2334 2543
Other Cu
ent Liabilities 54.3 44.3 72.30 92.20 32.30
Other Non-WC Cu
ent Liabilities
Long-Term Debt 12,045.00 12,777.00 13,098.00 13,546.00 13,876.00
Defe
ed Tax Liabilities 1,543.00 1,233.00 1,879.00 1,756.00 1,322.00
Other Long-Term Liabilities 2,503.00 1,766.00 1,965.00 1,766.00 2,654.00
Minority Interest 34 18 -10 -12 55
Prefe
ed Stock 23.00 12.00 0.00 4.00 0.00
Shareholders' Equity 16,786.00 12,355.00 15,987.00 17,876.00 12,098.00
Shares Outstanding
Imported Cash Flow Statement
Imported Cash Flow Statement
ERROR:#REF!
• Instructions
1. Ensure "Model" section of Control tab is filled out at a minimum prior to importing financial data to ensure co
ect timing and classification.
2. Import financial statement data into the respective tabs for intended company using a financial database (i.e. FactSet, Capital IQ, Thompson Reuters) or by manually entering a minimum of 2 years of financial data using annual reports.
3. Conduct classification exercise across three sheets, using Classification Lookup tab for reference. Ensure that all INDIVIDUAL line items (excluding totals and sub-totals) are appropriately classified to ensure accuracy of the standardised financial statements and forecasts.
NB 1: DO NOT classify totals and subtotals as line items. Doing so will result in misstated values throughout the model.
NB 2: Reference Classification Lookup as necessary to ensure co
ect classification.
• Classification
Classification Line Items 2017 2018 2019 2020 2021
Receipts from Customers 6,205.50 7,551.90 7,804.90 8,759.30 9,819.20
Payments to suppliers and employees -5,908.80 -7,130.50 -7,373.80 -7,624.50 -9,078.80
interest received 1.7 0.5 0.7 1.1 1.6
Interest and other finance costs paid on bo
owings -9.30 -15.00 -13.80 -11.50 -3.30