Updated 07/24/01
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.
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.
| 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.
| 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.
| 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
Certain relationships among these financial statements always hold true regardless of the case for which you prepare the statements.
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.
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.
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 |
These steps allow you to make changes to individual elements in the balance sheet without destroying any fundamental relationships. Furthermore, you can instantly see the cash impact of any changes you make by examining the change in the cash balance.
The following sections describe how to create a spreadsheet for pro forma financial statements.
Make the cash balance equal total current assets minus the sum of all current assets excluding cash. In this way the balance sheet will always balance, and the cash impact of any changes in balance sheet items will be reflected in the cash balance.
Enter the number of days sales outstanding (average collection period) you expect in the input section of your spreadsheet. Put this value in each column so you have the option of changing the collection length for the different statement periods.
In the cell for accounts receivable put a formula that multiplies the average collection period by the average daily sales. This formula calculates the receivables balance. The financial statements on the first two pages used 45 days as the average collection period, so the receivables balance for Year 2 is $113,918 (($924,000 / 365) x 45).
Note: If you use quarterly periods be sure to change the 365 to 90, and change it to 30 if you use months.
Include a value for inventory turnover in the input section of your spreadsheet (put a turnover value in each column), and use it to compute the inventory balance. Put a formula (Cost of Goods Sold / Inventory Turnover) in the inventory cell on the balance sheet. Any changes you make in the turnover value immediately changes the inventory value on the balance sheet.
Total current assets equal total assets minus net plant assets.
Input the plant asset values directly into the individual cells. If the company buys or sells assets, just type the new value in the appropriate cell.
Each period the balance in this account increases by the amount of depreciation in the income statement. So the formula for accumulated depreciation adds the previous period accumulated depreciation to the current period depreciation expense.
Compute the accounts payable balance with the same approach used for accounts receivable, i.e., multiply average daily purchases by the accounts payable days outstanding. Assume cost of goods sold equals purchases. The formula for this calculation is ((cost of sales / 365) x payables days outstanding).
Sometimes you have a variety of payables you need to consider in your statements, so you may estimate them directly or compute them as a percentage of some other value, like sales or cost of sales.
You can use this row in the balance sheet to estimate how much financing a company needs, or the amount of cash required for a new venture. Just keep increasing the value for long term debt until the cash balance turns positive. Usually you use debt in multiples of $1,000, $10,000, or $100,000 depending on the size of the venture.
Instead of changing the value of long term debt to make cash positive, you can change the value of common stock until the cash balance becomes positive. By varying the amounts of debt and common stock you can try different financial structures, and evaluate their impact on the overall financial statements.
The current period balance in retained earnings equals the prior period balance plus the current period income, minus any dividends. If you know the company will pay dividends, put a row in the input section of the spreadsheet for the dividend amounts; deduct these amounts from the current period income before adding it to the prior balance in retained earnings.
Enter a sales value in the first period (or you might use a base period). Multiply this by the sales increase percentage you put in your input section to derive the sales in the current period. For example, sales in Period 2 equal Period 1 sales x the percentage increase in sales from the input section of the spreadsheet for Period 2.
In your input section include a value for the gross profit percentage. Place a formula in the cost of goods sold cell that deducts this from one and multiplies the result by sales. For example, if you specified a 35% gross profit percentage, the formula is (1 - .35) x sales.
Computations for this value depend on the specific case for which you are developing statements. The example in this note shows constant wages for the entire three years. Occasionally wages may be a percentage of sales; if so, put a percentage in the input section, and multiply it by sales to generate the wages expense.
In some cases wages may be a step function, so you have to develop a formula that increases wages by, say, $50,000 every time sales increase another $1,000,000. You have to use your creativity on this one.
Note: Never take wages as a flat percentage of sales unless you know the company actually makes labor cost rise and fall with sales.
Usually you must estimate this value directly. Sometimes you can estimate a base value that you increase by some percentage each period. If you use this approach, put a row in your input section with the percentage cost increase each period.
Start with some base amount you estimate directly. If you will be purchasing equipment, put rows in your input section in which you compute the value of the added depreciation. Add these depreciation amounts form the input section to the base value you entered to get the new amounts.
Enter this expense directly into the income statement, or make it a percentage of some other expense or of revenue.
Include a row in your input section for an interest rate. Put a formula in the interest expense cell on the income statement that multiplies this rate times the debt amount in the balance sheet.
Use a 35% tax rate for corporations. Include this rate in your input section, and multiply that rate times the taxable income.
Note: Watch out for losses. If the income statement shows a loss before taxes, include zero for taxes. Carry losses forward until the firm has a net income on which to pay tax.
The cash flow statement follows an income statement format for operating items, i.e., subtract cash operating costs from cash from sales to get net cash from operations.
The second part of the statement shows investment and financing transactions; that is, investments or sales of long term assets, and the issuance or retirement of stock or equity.
Since the cash flow statement presents a cash only picture of the same information in the other two financial statements, your spreadsheet uses information from these statements to compute cash flows.
Go back to cash flow statement
Create a link to the sales value in the income statement
Go back to cash flow statement
Deduct the current period receivables from the prior period receivables.
Go back to cash flow statement
If a company has sales of $100,000 for January and its receivables are $100,000 greater on January 31 than they were on January 1, the company collected no cash from January sales. If instead of increasing, receivables were $50,000 less on January 31 than on January 1, the company collected all $100,000 for January sales plus $50,000 from previous months' sales for a total January cash inflow of $150,000. In your spreadsheet you add decreases in accounts receivable to sales, and you deduct increases in receivables from sales to compute the cash generated by sales in the current period.
Go back to cash flow statement
In your cash flow spreadsheet, link cost of goods sold to the cost of goods sold in the income statement.
Go back to cash flow statement
Deduct current period payables from the previous period payables.
Go back to cash flow statement
Deduct previous period inventory from the current period inventory.
Go back to cash flow statement
Unlike cash from sales, cash paid out for cost of goods sold is impacted by two factors: changes in accounts payable and changes in inventory levels.
Changes in accounts payable impact cash payments in a way similar to the way changes in receivables affect cash collections. Increases in accounts payable mean cash paid out for cost of goods sold is less than the reported cost of sales because the company still owes cash for some of the cost of sales; a reduction in payables means the company not only paid for all of this period cost of sales, but it also paid for some past periods' cost of sales.
If inventory increase it means the company has more cash tied up in inventory, so any inventory increase is added to cost of goods sold to estimate the cash spend on cost of goods sold. Changes in inventory and accounts payable can have a double whammy on cash flow if inventory increases and payables decrease in a period.
Summary:
Go back to cash flow statement
Create a link to each cash operating expense in the income statement. This normally means you create a link to every operating expense except depreciation. Since depreciation uses no cash, you omit it from the cash flow statement.
Go back to cash flow statement
Create links to the income statement for these expenses.
Go back to cash flow statement
Put a formula in the cash flow statement cell that subtracts the current period balance sheet plant account balance from the previous period balance.
Go back to cash flow statement
Put a formula in each row that takes the balance sheet values for the previous period and deducts it from the current period balance sheet value.
Go back to cash flow statement
If you know how much the company plans to pay in dividends, include a row in your input section with these amounts. If the company will make dividends a percentage of income, include a row with these percentage in the input section. Use these inputs to derive the dividends that appear in the dividends section of the cash flow statement.