Fin Modeling
Fin Modeling
Fin Modeling
Copyright 2008 by Wall Street Prep, Inc. | All rights reserved Inc.
Table of contents
SECTION 1: PREPARING FOR MODELING Welcome letter Introduction Gathering historical documents/information Understanding projections Modeling techniques in Excel Debt schedule Interest expense SECTION 3: ENHANCEMENTS TO A COMPLETE MODEL Balancing the model and circularity Ratio analysis Form toggles, sensitivity and scenario analysis
SECTION 2: BUILDING A FINANCIAL STATEMENT MODEL Income statement historicals Income statement projections Balance sheet historicals Balance sheet projections Working capital schedule Deferred taxes Intangible assets Property, plant, & equipment Treasury stock and shares outstanding Minority interests and equity in affiliates Goodwill and other balance sheet items Retained earnings ***************************** Cash flow statement projections
Understanding projections
Example of a driver (simple sales driver)
Take the last reported years sales growth rate, and project that growth rate to future years (called straight-lining projections)
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
3
Copyright 2007 by Wall Street Prep, Inc.
$ sign only shown on first row of worksheet and highlighted financial results
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
4
Copyright 2007 by Wall Street Prep, Inc.
Golden Rule
Understand what you are modeling
Assumptions should be placed together on one page and be explicit and driven off clearly identified drivers when possible. Save often and keep many versions of your model. Believe us Excel does crash sometimes! When appropriate, explain your work via footnotes or comments Footnotes Written in the bottom of the model Shows up when model is printed Clients will see these Comments Embedded within a specific cell (Shift F2)
***************************** Do not show up when model is printed SAMPLE PAGES For internal purposes FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
5
Copyright 2007 by Wall Street Prep, Inc.
When inputting historical financial statements, calculate where appropriate Total assets although disclosed should be calculated in the financial model as the summation of all assets. *****************************
SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
6
Copyright 2007 by Wall Street Prep, Inc.
If you must hide information, group the rows or columns so the hidden information shows up on the margin. Shortcut in Excel to group / ungroup rows: Shift + Alt + Right arrow key / Left arrow key
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
7
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
8
Copyright 2007 by Wall Street Prep, Inc.
Also remember to change EBIT, EBT, and net income calculations to reflect ***************************** pro forma (vs. reported) FROM TUTORIAL GUIDE SAMPLE PAGES results.
For illustrative Purposes Only ***************************** 9
Copyright 2007 by Wall Street Prep, Inc.
Remember minority expense and equity income! Recall that we just excluded minority interest expense and equity income from the Other expense category. As such, we will explicitly identify them (on an after-tax basis) by ***************************** multiplying the pre-tax figures by CLs effective tax rate.
SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
10
Copyright 2007 by Wall Street Prep, Inc.
Confirm models historical EPS matches consensus EPS Since our EPS matches the consensus EPS, we can be fairly certain that we have caught all the nonrecurring items and made all the adjustments that The Street has made.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
11
Copyright 2007 by Wall Street Prep, Inc.
Accounts receivable 3 ways to project: 1. By default, the model grows accounts receivable balances at the revenue growth rate. Assumption is that sales that generate accounts receivable will grow at the same rate as cash sales. ***************************** 1. User can override this projection by projecting days sales outstanding 2. User can override both the default and theFROM TUTORIAL GUIDE SAMPLE PAGES DSO projection by inputting an absolute projection for accounts receivable. 12 For illustrative Purposes Only
*****************************
Modeling asset dispositions/sales Only model these if the company continually sells assets and the practice is expected to continue. Sale leasebacks Some companies continually sell assets and lease them right back as a financing tool or to alter their capital structure. If the company you are analyzing does this consistently, you should also project a reduction in PP&E as a result of the sale-leaseback.
+ -
SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
Modeling write-downs Similarly to intangible assets, unless we have specific information to the contrary, we do not typically have a basis for expecting any asset write-downs, and do *****************************not incorporate any projections for this in our model.
13
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
14
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
15
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
16
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
17
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
18
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
19
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
20
Copyright 2007 by Wall Street Prep, Inc.
Debt schedule
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
21
Copyright 2007 by Wall Street Prep, Inc.
Debt schedule
11. Reference cash from prior years balance sheet balance
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
22
Copyright 2007 by Wall Street Prep, Inc.
Debt levels
Interest expense
levels
Net Income
Reduced net income because of higher Reduced free cash flows ***************************** interest expense because of lower net income SAMPLE PAGES FROM TUTORIAL GUIDE
For illustrative Purposes Only ***************************** 23
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
24
Copyright 2007 by Wall Street Prep, Inc.
Excel 2007 Office Button > Excel Options > Formulas tab
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
25
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
26
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
27
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
28
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
29
Copyright 2007 by Wall Street Prep, Inc.
Balance-check should read 0 for every projected year. If not, you will need to locate the source of the error. Turn to the next page for guidance.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
30
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE Input the following ranges of assumptions in a schedule below the ratio analysis. For illustrative Purposes Only *****************************
32
Your toggle is now ready to use! ***************************** Toggle between scenarios and see how the projections in the model change SAMPLE PAGES FROM TUTORIAL GUIDE
For illustrative Purposes Only ***************************** 33
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
34
Copyright 2007 by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
35
Copyright 2007 by Wall Street Prep, Inc.
Highlight the matrix Excel 2003: Type Alt d t (Data>Table...) Excel 2007: Type Alt a w t (Data>What-if analysis>Data Table) Reference the row input variable from the model above (2007 gross profit margin is the row input variable) Reference the column input variable from the model above (2007 revenue growth is the column input variable) Hit Enter
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
36
Copyright 2007 by Wall Street Prep, Inc.
Terms of Use All materials included in Wall StreetPreps Self Study Program including Step-by-step Tutorial Guide, Wall StreetPreps proprietary portfolio of financial models, supplementary notes, are not to be duplicated, copied, disseminated or distributed without the expressed, written permission of Wall StreetPrep, Inc. The Self Study Program as well as case studies used during live classes are designed for illustrative purposes only and does not, in any way, constitute and investment thesis or recommendation. Copyright 2008 Wall StreetPrep, Inc. All rights reserved. "Wall StreetPrep", "Wall Street Prep", "The EDGE Self Study Program", and various marks are trademarks of Wall Street Prep, Inc. *****************************