How to Create Pro Forma Financial Statements in Excel

In this article, we will create pro forma financial statements in Excel. We will project three years of financial statements for a startup company.

Financial Statements

There are four main types of financial statements: balance sheet, income statement, cash flow statement, and owner’s equity statement. These statements summarize the financial situation of an organization or company.

Income Statement

It reports the profit and loss of a company over a certain period, and comprises three parts: Revenue, Expense and Profits. So, it contains all income and expenses for a certain period and calculates net profit.

Balance Sheet

It summarizes the financial position of the company after a certain period and is also known as a Statement of Financial Position/Condition. In general, a balance sheet consists of three parts: Assets, Liabilities, and Owners’ Equity, and provides a glimpse of the company’s finances. Balance sheets are organized according to the equation:

Assets = Liabilities + Owner’s Equity

Assets: These are the main resources owned by the company, such as current and fixed assets, tangible and intangible assets, etc.
Liabilities: These are things that the company owes, like cash, loans, etc.
Owner’s Equity: This represents the value for a company’s shareholders after all the company’s assets have been sold off and all company liabilities have been paid off.

Cash Flow Statement

Cash flow statements are the bridge between the income statement and the balance sheet. They have three parts:

Operating Activity: This is the main source of revenue for a company or organization, including cash flows from main operations.
Investment Activity: Cash received or paid due to buying or selling any assets, taking loans, paying interest on loans, etc. is included here.
Financing Activity: Cash flows resulting from any changes in equity earning or borrowing via bonds, stocks, or dividends.

Pro Forma Financial Statements

Pro forma is a Latin word meaning “as a matter of form”. A pro forma financial statement displays projected income, expenses, assets and liabilities, particularly related to a planned activity. A company is not required to adhere to “generally accepted accounting principles”(GAAP) and is free to exclude anything from its pro-forma financial statements, which are mostly used internally as part of the management decision-making process. There are three main benefits to using pro forma financial statements:

To simulate how different scenarios affect the business.
To prepare for worst-case scenarios.
To attract potential clients.

We will prepare three pro forma financial statements in Excel. Firstly, we will create the income statement, then the pro forma balance sheet, followed by the cash flow statement. In this process, we will use the SUM function to calculate the total amounts. Everything is linked, so changing one cell will change the relevant linked values. Moreover, for improved visibility, the assumed values will be in blue font color. The statements are also created on the same sheet to reduce navigation time and the risk of linking to unintended cells.

Example 1 – Creating Pro Forma Income Statement

We will assume several values for our forecasting. The depreciation and interest expenses will be obtained from the second example.

Steps:

Enter all the assumptions (the values in blue font below).

Enter all the fields for the income statement. We will forecast for the years 2022, 2023, and 2024.

Enter the following formula in cell C9 to calculate the gross revenue:

=H9*H10

Drag the Fill Handle to the right to AutoFill the formula.

Enter the following formula in cell C10 to calculate the refunds (note the negative sign!):

=-C9*H11

Enter another formula in cell C11 to find the discounts:

=-C9*H12

Enter this formula in cell C12 to find the net revenue:

=SUM(C9:C11)

Enter this formula in cell C14 and drag it down and then to the right side to fill the rest of the cells:

=C$12*H14

The row number is fixed using the dollar sign.

Enter another formula in cell C17 to calculate the cost of goods sold:

=SUM(C14:C16)

Enter this formula in cell C18 to find the gross margin:

=C12-C17

And enter the following formula in cell C20 and drag this down and then to the right to fill the rest of the cells:

=C$12*H18

Enter this formula in cell C24 to calculate the total operating expense:

=SUM(C20:C23)

We will find the values for the depreciation and interest expense later, so keep those blank for now.

Enter the following formula in cell C25 to find the operating income:

=C18-C24

Enter the following formula in cell C27 to find the net income before tax:

=C25-C26

Enter this formula in cell C28 to find the tax amount:

=C27*H21

Enter this formula in cell C29 to find the net income without considering depreciation and interest:

=C27-C28

Now, from this part, we will find the values of the depreciation. Link to those values by entering this formula in cell C23:

=C44

Lastly, from this part, we will find the interest amount, and we reference it by entering this formula in cell C26:

=I57

This step completes the income statement.

Example 2 – Making Pro Forma Balance Sheet

Now we will create a format for the pro forma balance sheet. We need historical data from last year, 2021 in this case. We will need the “net cash flow” from the cash flow statement to calculate the “cash & cash equivalents” on the balance sheet. We will also refer to the income statement just prepared for other values.

Steps:

Project the capital expenditure and depreciation. We have the total depreciation values in our income statement.

List all the assumptions for the balance sheet as in the blue text below. From these assumptions, we derive the interest expenses for the income statement.

Enter all the fields for the balance sheet, including last year’s (2021) values.
Notice the field “balance verification” that we will use to check if the balance sheet balances.

Enter the followings formula in cell D52 to derive accounts receivable amounts, and drag it to the right to fill the rest of the cells:

=I50*I51

Enter the following formula in cell D53 to find the total current assets.

=SUM(D51:D52)

We will derive the amount of “cash and cash equivalents” from the cash flow statement, so keep it empty for now.

Enter the following formula in cell D54 to find the fixed assets:

=C54+C39

Enter this formula in cell D55 to find the accumulated depreciation:

=C55-C44

Enter this formula in cell D56 to get the values:

=SUM(D54:D55)

Enter this formula in cell D57 to calculate the total assets:

=SUM(D56,D53)

Similarly, enter formulas to calculate the values for the liabilities and equity parts.

Note that the balance verification is not zero yet (0 means the balance sheet balances), because the cash and cash equivalents value is still empty.

From the cash flow statement, we will get the net cash flow values. Input those values by entering the following formula in cell D51 and filling in the formula on the right:

=C51+C89

Our pro forma balance sheet is complete.

Example 3 – Preparing Pro Forma Cash Flow Statement

We will take most of the items from the balance sheet. Net income will come from the income statement, and depreciation will come from the “capital expenditure and depreciation” we just derived.

Steps:

Enter all the fields for the cash flow statement.
Link the known values:
- Net Income → Income Statement.
- Depreciation → Projected Capex and Depreciation.
- Capital Expenditure → Projected Capex and Depreciation.
- Debt Repayment → Balance Sheet Assumption (negative value).
- Net Borrowings → Balance Sheet Assumption.

Enter this formula in cell C78 to find the change in accounts receivable, then drag it to the right to fill the rest of the cells:

=C52-D52

Enter the following formula in cell C79 to calculate the change in accounts payable:

=D59-C59

Enter this formula in cell C80 to find the change in unearned revenue:

=D60-C60

Enter this formula in cell C81 to find the operating cash flow:

=SUM(C77:C80,C75)

Enter this formula in cell C84 to find free cash flow:

=C81-C83

Enter this formula in cell C88 to calculate the net cash flow from financing:

=SUM(C86:C87)

Enter the following formula in cell C89 to find the net cash flow:

=C84+C88

Our pro forma financial statements in Excel are complete.

Pro Forma Financial Statements Template

We have included a template for the pro forma financial statements where you can input values and the statements will automatically update. Additionally, you can add new rows and type new items to the financial statements as per your requirements.