Updated 07/24/01

A Note on the Preparation of Pro Forma Financial Statements

I. Introduction

Pro forma, or projected, financial statements help managers evaluate new ventures or new strategic initiatives, and a carefully designed spreadsheet allows managers to quickly produce alternative statements for different business scenarios or strategies.

This note describes the mechanics of creating projected financial statements so you can learn to quickly develop statements for various scenarios.

A. The Basic Financial Statements

Managers, potential investors, and CEO's focus on three financial statements: the Balance Sheet, the Income Statement, and the Cash Flow Statement. The balance sheet shows what the company owns, owes, and the net interest of the owners at a point in time, e.g., year end, quarter end, or month end. The income statement shows the inflows and outflows of resources for a given time period, e.g., year, quarter, or month. Finally, the cash flow statement shows the inflows and outflows of cash for a specific time period. The following example illustrates these three statements.

To read a description of how to set up your spreadsheet for any of the accounts in the following financial statements, just click on the account name.

Spreadsheet for Financial Statements--Here

If you want a copy of the Excel spreadsheet used to prepare these statement, just click here to get a copy.   I have protected the cells in this spreadsheet that contain formulas, but you can unprotect the spreadsheet whenever you feel comfortable with the way it works.

In building financial statements in a spreadsheet, there are certain fundamental relationships that always hold true.

Balance Sheets

  Information at Yearend
  Year 1 Year 2 Year 3
Assets      
Cash $ 445 $ 52,434 $ 35,704
Receivables 103,562 113,918 131,005
Inventory 136,500 150,150 172,673
  -------- -------- --------
Total Current Assets 240,506 316,502 339,382
  -------- -------- --------
Plant Assets 195,000 205,000 205,000
Accumulated Depreciation 60,000 75,000 90,000
  -------- -------- --------
Net Plant Assets 135,000 130,000 115,000
  -------- -------- --------
Total Assets $ 375,506 $ 446,502 $ 454,382
  ======== ======== ========
Liabilities      
Accounts Payable $ 52,356 $ 57,592 $ 66,231
Other Payables 5,000 5,000 5,000
  -------- -------- --------
Total Current Liabilities 57,356 62,592 71,231
  -------- -------- --------
Long Term Debt 130,000 130,000 50,000
  -------- -------- --------
Total Liabilities 187,356 192,592 121,231
  -------- -------- --------
Common Stock 100,000 120,000 120,000
Retained Earnings 88,150 133,910 213,152
  -------- -------- --------
Total Equity 188,150 253,910 333,152
  -------- -------- --------
Total Liabilities & Equity $ 375,506 $ 446,502 $ 454,382
  ======== ======== ========

The income statements for this same three year period are shown here.

Income Statements

  Data for Year Ended
  Year 1 Year 2 Year 3
Sales $ 840,000 $ 924,000 $1,062,600
       
Cost of Sales 546,000 600,600 690,690
  -------- -------- --------
Gross Profit 294,000 323,400 371,910
       
Wages Expense 45,000 55,000 60,000
Selling & Admin. Expenses 125,000 125,000 125,000
Depreciation 15,000 15,000 15,000
Other 45,000 45,000 45,000
  -------- -------- --------
Total Expenses 230,000 240,000 245,000
  -------- -------- --------
Net Income Before Interest & Taxes 64,000 83,400 126,910
Interest Expense 13,000 13,000 5,000
Taxes 17,850 24,640 42,669
  -------- -------- --------
Net Income After Taxes $ 33,150 $ 45,760 $ 79,242
  ======== ======== ========
Cumulative before tax earnings 51,000 121,400 243,310

The cash flow statements which report the inflows and outflows of cash for the firm are next.

Cash Flow Statements

  Data for Year Ended
  Year 1 Year 2 Year 3
Sales $ 840,000 $ 924,000 $1,062,600
Change in Receivables (23,562) (10,356) (17,088)
  -------- -------- --------
Cash From Sales 816,438 913,644 1,045,512
  -------- -------- --------
Cost of Sales 546,000 600,600 690,690
Change in Payables (12,356) (5,236) (8,639)
Change in Inventory 46,500 13,650 22,523
  -------- -------- --------
Cash Cost of Sales 580,144 609,014 704,574
  -------- -------- --------
Cash Margin 236,295 304,629 340,939
       
       
Cash Wages 45,000 55,000 60,000
Selling & Administrative 125,000 125,000 125,000
Other 45,000 45,000 45,000
Net change in other current items - - -
  -------- -------- --------
Total Cash Expenses 215,000 225,000 230,000
  -------- -------- --------
Net Cash From Operations 21,295 79,629 110,939
       
Income Taxes (17,850) (24,640) (42,669)
Interest Expense (13,000) (13,000) (5,000)
Investment and Financing Transactions      
Sale of (Purchase) Plant - (10,000) -
Issue (Retire) Long-term Debt - - (80,000)
Issue (Retire) Stock - 20,000 -
  -------- -------- --------
Total Nonoperating Cash Changes (30,850) (27,640) (127,669)
  -------- -------- --------
Net Cash Increase (Dec) $ (9,555) $ 51,989 $ (16,730)
  ======== ======== ========

Dividends

II: Basic Relationships Among the Statements

Certain relationships among these financial statements always hold true regardless of the case for which you prepare the statements.

A. Retained Earnings

Retained earnings for periods X+1 always equals retained earnings for period X plus the after tax income of period X+1 minus any dividends for period X+1. Example: In the above statements the retained earnings in Year 2 was calculated like this:

Retained earnings Year 1 $ 88,150
Net income Year 2 45,760
Retained earnings Year 2 $133,910

In this case no dividends were paid. However, if the company had paid a $20,000 dividend, the ending retained earnings for Year 2 would only be $113,410 instead of the $133,910.

B. Total Assets

Total assets always equal total liabilities plus equity. This relationship proves very useful when setting up a spreadsheet for creating projected financial statements. Set total assets equal to total liabilities and equity.

Go back to balance sheet

C. Net Cash Changes

The net cash increase or decrease from the cash flow statement must equal the change in the cash on the balance sheet.

Consider the previous financial statements:

Cash Year 1

$ 445

Cash Year 2

52,434

Change in cash from Year 1 to Year 2

$51,989

Net change in cash from cash flow statement

$51,989

1. Balance sheet--global structure