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
to calculate the gross revenue:*C9*

`=H9*H10`

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

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

`=-C9*H11`

- Enter another formula in cell
to find the discounts:*C11*

`=-C9*H12`

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

`=SUM(C9:C11)`

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

`=C$12*H14`

The row number is fixed using the dollar sign.

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

`=SUM(C14:C16)`

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

`=C12-C17`

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

`=C$12*H18`

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

`=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
to find the operating income:*C25*

`=C18-C24`

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

`=C25-C26`

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

`=C27*H21`

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

`=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
to derive accounts receivable amounts, and drag it to the right to fill the rest of the cells:*D52*

`=I50*I51`

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

`=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
to find the fixed assets:*D54*

`=C54+C39`

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

`=C55-C44`

- Enter this formula in cell
to get the values:*D56*

`=SUM(D54:D55)`

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

`=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 celland filling in the formula on the right:*D51*

`=C51+C89`

Our * pro forma balance sheet* is complete.

**Read More: **How to Prepare Financial Statements from Trial Balance in Excel

## 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
to find the change in accounts receivable, then drag it to the right to fill the rest of the cells:*C78*

`=C52-D52`

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

`=D59-C59`

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

`=D60-C60`

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

`=SUM(C77:C80,C75)`

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

`=C81-C83`

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

`=SUM(C86:C87)`

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

`=C84+C88`

**Read More: **How to Create a Personal Financial Statement in Excel

## 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.

**Download Practice Workbook**

## Related Articles

- How to Automate Financial Statements in Excel
- How to Link 3 Financial Statements in Excel
- Consolidation of Financial Statements in Excel

**<< Go Back to How to Create Financial Statements in Excel | Excel for Finance | Learn Excel**