DES Chapter 9

1
DES Chapter 9 The Starting Point for Corporate
Valuation Historical Financial Statements
2
Overview of Valuation Process - Home Depot

• Input historical financial statements into the
  file Home Depot (for Ch 9-11, WACC, default
  inputs).xls. (This file will be called Home
  Depot.xls for short.)
• Provide projections for key performance drivers.
• Obtain preliminary intrinsic value estimate.
• Refine projections and perform sensitivity
  analysis to determine impact of plausible
  alternative performance scenarios.

3
Steps to Estimate Value Using the Corporate
Valuation Spreadsheet

• The valuation spreadsheet has seven interrelated
  worksheets, each of which performs an essential
  function
• (1) Proj Val
• (2) Inputs
• (3) WACC
• (4) Hist Analys
• (5) Condensed
• (6) Comprehensive
• (7) Actual
• (next slide)

4
Steps to Estimate Value (Continued)
5
Steps to Estimate Value (Continued)
Step 1 Find the actual historical financial
statements for a company, and insert them into
the Actual worksheet. (Chapt 9) Step 2 Put the
actual financial statements into a standardized
format using the Comprehensive worksheet. The
comprehensive format has just about all the
entries needed to capture variations in the
formats of most companies financial statements.
(Chapter 9 and appendix)
(continued)
6
Steps to Estimate Value (Continued)
Step 3 The spreadsheet will condense the
standardized format into statements on the
Condensed sheet. These sheets have enough detail
to accurately value a firm but do not have so
much detail that the analysis becomes overly
complicated. (Chapter 10 and appendix) Step 4
The Hist Analys worksheet begins the analysis by
calculating the historical free cash flows and
key ratios. (Chapter 10)
(continued)
7
Steps to Estimate Value (Continued)
Step 5 The WACC worksheet is structured to lead
you through the calculation of the firms cost of
capital. (Chapter 11) Step 6 Project the
financial statements by choosing key inputs, such
as the growth rate in sales, the ratio of
costs/sales, interest rates, etc., on the Inputs
worksheet. (Chapter 12 and appendix)
(continued)
8
Steps to Estimate Value (Continued)
Step 7 The Proj Val worksheet takes your
chosen inputs for the key ratios and projects the
financial statements, calculates free cash flows
and performs a valuation analysis. (Chapter 13)
9
The Actual Worksheet

• Get historical data for a company. The best
  sources are Thomson ONE - Business School
  Edition, or Thomson Research (formerly Global
  Access Disclosure) .
• Income Statements
• Balance Sheets
• Statement of Cash Flows
• Paste the data into the Actual worksheet
• This has already been done for Home Depot.xls.

10
The Comprehensive Sheet

• A fact - details of financial statement formats
  vary from one company to another, and even from
  one year to the next for the same company.
• The problem - to standardize financial
  information across firms and years so it can be
  used in analysis.
• The solution - mapping the financial data into
  a common format that contains only the essential
  level of detail necessary for valuing a company.

(continued)
11
The Comprehensive Sheet (continued)

• The Comprehensive sheet is long and has many
  items, but this enables it to capture Actual
  data in virtually any format (see next slide).
• Even though the Comprehensive sheet is long, it
  is in a standardized format.

(continued)
12
The Comprehensive Sheet (continued)

• To link Actual to Comprehensive, we map the
  data from the Actual Worksheet into the
  Comprehensive Worksheet.
• Find an entry on the Comprehensive sheet that
  matches an Actual entry.
• Enter a formula on the Comprehensive sheet that
  refers to the appropriate Actual cell.
• But weve already done this for you!

(continued)
13
The Comprehensive Sheet (continued)
Consider the NON-OPERATING INC item in cell B70
of Home Depots Actual income statement
(continued)
14
The Comprehensive Sheet (continued)
The item is mapped into cell B31 of the
Comprehensive worksheet by entering the formula
Actual!B70 in that cell (next slide)
(continued)
15
formula Actual!B70
(continued)
16
The Comprehensive Sheet (continued)

• Data from either Thomson ONE - Business School
  Edition, or Thomson Research has up to 10 years
  of annual data in a standardized format.
• The mapping in the valuation spreadsheet is
  already developed for this format.
• Should you use data from another source, you will
  adjust the formulas accordingly.
• Detailed guidelines for this task are provided in
  Exhibits 9-4 through 9-7 and elsewhere.