Financial Statement Modeling by Wall Street Prep, Inc.
Financial Statement Modeling
Copyright 2008 © by Wall Street Prep, Inc. | All rights reserved Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE *****************************
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
SAMPLE PAGES FROM TUTORIAL GUIDE *****************************
Understanding projections
Example of a driver (simple sales driver)
• Take the last reported year’s sales growth rate, and project that growth rate to future years (called “straight-lining projections”)
Calculation (black cells) Formula in Excel =E3*(1+F4)
Historical inputs (blue cells)
Calculations (black cells)
Simple driver (blue cells)
We’ll discuss formatting rules extensively in the next section.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
3
Copyright © 2007 by Wall Street Prep, Inc.
Modeling techniques in Excel
Formatting (cont’d)
• Maintain standard formatting of numbers throughout your model • Maintain standard formatting of worksheets throughout your model • Maintain standard column and row headings across multiple sheets
$ sign only shown on first row of worksheet and highlighted
Copyright 2008 © by Wall Street Prep, Inc. | All rights reserved Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE *****************************
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
SAMPLE PAGES FROM TUTORIAL GUIDE *****************************
Understanding projections
Example of a driver (simple sales driver)
• Take the last reported year’s sales growth rate, and project that growth rate to future years (called “straight-lining projections”)
Calculation (black cells) Formula in Excel =E3*(1+F4)
Historical inputs (blue cells)
Calculations (black cells)
Simple driver (blue cells)
We’ll discuss formatting rules extensively in the next section.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
3
Copyright © 2007 by Wall Street Prep, Inc.
Modeling techniques in Excel
Formatting (cont’d)
• Maintain standard formatting of numbers throughout your model • Maintain standard formatting of worksheets throughout your model • Maintain standard column and row headings across multiple sheets
$ sign only shown on first row of worksheet and highlighted