Pro Forma Financial Analysis

Pro Forma Financial Analysis

Pro Forma

Financial Analysis

As we discussed in our introduction, we can obtain the financials of any publicly traded company via:

• Yahoo Finance
  
• The Wall Street Journal
  
• The investor relations section of the corporation’s website

Piecing together a pro forma from the investor relations section of the company’s website can prove to be quite cumbersome.

• Some companies don’t have exportable excel files
  
• Makes the workload more tedious and time consuming
  
• It confuses certain line items that are available in other reliable sources

Income Statement

• A company’s performance measured by revenues and expenses
  
• In Economics:
  Profit: \(\pi\) = TR - TC
  
• In Finance/ Accounting:
  Profit = Revenue – Cost
  
• Operating Income (or Loss) = Total Revenue – Total Operating Expenses
  
• EBIT = Earnings Before Interest and Taxes
  
• Net Income from Continuing Operations = Total Other Income (and/or Expenses) Net + EBIT – Income Tax Expense

Balance Sheet

• Assets = Liabilities + Shareholder’s Equity

Cash Flow Statement

• Cash from Operating Activities
  
• Cash from Investing Activities
  
• Cash from Financing Activities
  
• We are going to compute Free Cash Flow (FCF) and use the discounted cash flow model (DCF) to valuate a company.
  
• Before we compute the FCF, let’s remember that financing activities of the cash flow statement can be disregarded.
  
• FCF = Operating activities – Capital Expenditures (CAPEX)

Recording Our First Macro

We will record a macro (automated process) that will create a script on the back end (in VBA).

1. In Excel, click on “View,” then “Macros,” and click on “Record Macro.”

2. Make sure that you have your steps mapped out such that you do not make any mistakes in the process (i.e., accidentally click or type somewhere that was not intended).

3. Ensure that you name your macro such that it DOES NOT contain any spaces.
   

4. Write-up an optional description.

5. Click “OK.”

6. Once you are finished going through the process of recording a macro, go back to “View,” “Macros,” and ensure to click on “Stop Recording.”

7. To view the resulting script, go back to the process in the diagram above.
   
8. The resulting dialog box pops up. Click “Edit.”

9. Let’s name our macro: “WSJProForma.”
   
10. Let’s add the following description:
    “ProForma Macro – useful for sites like WSJ
    re-arranges columns (years) in ascending order”
    
11. Select all data in range: (column A – column K).
    
12. Unselect “Wrap Text.”
    
13. With the data still selected, click on “Unmerge Cells.”

14. Get rid of the border surrounding the data set (you want to ensure to remove all borders in columns A-K ).

15. Insert a blank column in front of column B (where the header is marked as 2020).
    
16. Repeat step 7 (above) 4 times until 4 blank columns are created in front of 2020. You can do this by pressing down ctrl + y on your keyboard 3 more times as a shortcut. The reason for creating 4 more columns to the front of 2020 is so that we can proceed to rearrange the years in chronological order.
    
17. Highlight column J (where the header is marked as year 2016), copy the data, and paste the data into column A. Do the same for columns I – F , until all the dates are rearranged in chronological order.
    
18. If there is any data in column K: such as “5 year-trend, etc.,” ensure to delete it. You can do this by deleting column K in its entirety. Data in column K is what was left over from the copying and pasting of original data.
    
19. Go back to column A , highlight it, and align it to the left.
    
20. This concludes the steps for this macro. Ensure to go back to the View tab on top of the Excel menu, go to “Macros,” and click on “Stop Recording.”

This takes us to the VBA Editor. On the back end, a screenshot of the script for the macro is shown below:

Wall Street Journal Pro Forma Macro (Script)

Let’s save the file as a macro enabled workbook and copy and paste the remaining pro forma statements into our excel file into separate sheets. So, in effect, we will have 3 sheets:

1. Income Statement
   
2. Balance Sheet
   
3. Cash Flow

Below is the pro forma for IMAX Corporation from 2013 - 2020.

IMAX Corporation Pro Forma from Wall Street Journal (2013-2020)