• Model Setup

    Gather the information

    Begin by gathering the files required for historical information. For this tutorial, we’ll be forecasting using 10-K’s (annual reports), but there will be times where using 10-Q’s (quarterly reports) will be appropriate. You’ll usually need about four years of data, so the most current 10-K and one from two years back will provide the information required.

    Tips for Finding Company Financials

    When gathering information, try to be as efficient as possible. 10-Ks and 10-Q’s will include performance figures that cover periods from at least one period back with some companies offering several periods of information. This is done for comparison purposes, but for you, this helps save time.

    To get a copy of the reports, you can visit Edgar for American listed companies and Sedar for Canadian listed companies. The investor relations section of the company’s website is also a good place to get these reports along with  news releases that may shed light on operations.

    Enter the information

    Open Excel and set up three worksheets named IS, BS and CF. Using the company’s financial statements as a guide, add in the appropriate account lines for each of the statements. Enter the historical figures. As you go enter the numbers, be sure to double-check for accuracy. Checking totals and sums are a quick way to catch entry errors.

    Excel Tips

    • To quickly rename sheets:  Alt + O + H + R.
    • Ensure that the years match up on all sheets. If you have the year 2008 in column B on your income statement, column B on the balance sheet and cash flow statement should also be the year 2008
    • Use indents instead of columns to move text in as it makes spreadsheets easier to maintain. Ctrl + 1 to bring up formatting properties, a to the alignment tab, then tab twice to get to indent
    • Format your headings appropriately – Ctrl + B to bold, Alt + F + S to select a bigger font size
    • Do not enter numbers twice! Use sum() and subtotal() whenever possible. The fastest way to do this is to enter the formulas before you enter the historical information and then fill right Ctrl-R.
    • Blue for inputted data, black for formulas, green for linked data and red to highlight potential issues.
    • Get in the habit of using the accounting format with numbers. Select the area and Alt + H + K. By default, this will give you two decimal places. Use Alt + H + 9 to move the decimal place to match the historical financial statements.

    Example: Cisco

    Step 1: Download the historical information from Cisco’s investor relations website. The files you will need are the Printable 2010 Annual Report and the 2008 Annual Report.

    Step 2: Set up your spreadsheet based on the financial statements found on pages 39 to 41 of the 2010 AR (Pages 41 to 43 of the PDF).


    Step 3: Set up all the formulas on your sheet so that you don’t enter the figures twice. Doing so now allows you to fill across to save time.

    Step 4: Enter the historical data. As you reach each total, double check the numbers.

    About

    Leave a Reply

    Your email address will not be published. Required fields are marked *