How to Create Pro Forma Financial Statements in Excel

In this article, we will show you how to create pro forma financial statements in Excel. We will project three years of financial statements for a startup business 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. They are the written documents or records that the company keeps throughout the year. Here, we will talk about the first three main parts of a financial statement report:

Income Statement

It reports the profit and loss of a company over a certain period. So, the income statement is made up of three parts: 1. Revenue 2. Expense 3. 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. These are: assets, liabilities, and owners’ equity. A balance sheet provides a glimpse of the company’s finances. The balance sheet consists of the company’s liabilities, assets, and owner’s equity. Balance sheets are organized according to the equation:

• Assets: These are the main resources owned by the company. Assets can be categorized into many types. Example- current and fixed assets, tangible and intangible assets, etc.
• Liabilities: They are things that the company owes to a person or another company, like cash, loans, etc.
• Owner’s Equity: It 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.  There are also three parts to it:

• Operating Activity: It is the main source of revenue for a company or organization. Cash flows regarding main operations will also be included here.
• 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 regarding any changes in equity earning or borrowing entities like bonds, stocks, or dividends.

Pro Forma Financial Statements

Pro forma is a Latin word. This phrase means “as a matter of form”. A pro forma financial statement means displaying projected income, expenses, assets, or 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 financial statements. This is mostly used internally as part of the management decision-making process. This cannot be presented to the potential investors for a publicly traded company, and they will need to maintain proper financial guidelines to prepare their financial statements. Moreover, there are three main benefits of using pro forma financial statements:

• We can simulate how different scenarios affect the business.
• We can prepare for the worst-case scenarios.
• Potential clients can be drawn to us.

We will prepare three pro forma financial statements in Excel. Firstly, we will create the income statement. Following that, we will create the pro forma balance sheet. Lastly, we will create the cash flow statements. In this process, we will use the SUM function to calculate the total amount. Everything is linked, so changing one cell will change the relevant linked values. Moreover, the assumed values will be in blue font color; therefore, you will easily know which values to change as per your requirements. Moreover, the statements are created on the same sheet. This will speed up our navigation time. Additionally, this practice will reduce the risk of linking to unintended cells.

1. Creating Pro Forma Income Statement

In this first example, we will create a pro forma income statement in Excel. We will assume several values for forecasting. These values will be easily recognizable by their blue font colors. The depreciation and interest expenses will be obtained from the second example. Without further ado, let us go through the steps to project the income statement.

Steps:

• To start with, type all the assumptions. We will reference these values in the income statement.

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

• After that, type this formula in cell C9 to calculate the gross revenue and drag this using the Fill Handle to the right side to AutoFill the formula.

• Then, type another formula in cell C10 to calculate the refunds. Remember to use the negative sign here.

• After that, type another formula in cell C11 to find discounts.

• Then, type this formula in cell C12 to find the net revenue.

• Afterward, type this formula in cell C14 and drag this down and then to the right side to fill the formula. We have fixed the row number using the dollar sign.

• Next, type another formula to calculate the cost of goods sold.

• Type this formula to find the gross margin.

• After that, type another formula in cell C20 and drag this down and then to the right side to fill the formula.

• Then, type this formula to calculate the total operating expense. We will find the values for the depreciation and interest expense later, and we will keep those blank for now.

• Type this formula in cell C25 to find the operating income.

• Type another formula to find the net income before tax.

• Then, type this formula in cell C28 to find the tax amount.

• Next, type this formula in cell C29 to find the net income without considering depreciation and interest.

• Now, from this part, we will find the values of the depreciation. So, we link to those values by typing this formula.

• Lastly, from this part, we will find the interest amount, and we reference it by typing this formula.

• Finally, this step will complete the income statement.

2. Making Pro Forma Balance Sheet

For the second example, we will create a format for the pro forma balance sheet. For this, we need historical data from last year, which is 2021 for this article. 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 need to refer to the income statement for other values.

Steps:

• Firstly, we have projected the capital expenditure and depreciation. Remember, the blue colored text indicates assumed values. We have the total depreciation values from this in our income statement.

• Secondly, list all the assumptions for the balance sheet. From these assumptions, we have found the interest expenses for the income statement.

• After that, type all the fields for the balance sheet. Additionally, we have used last year’s (2021) values. Notice there is a field “balance verification” that we will use to check if the balance sheet balances.

• Next, type this formula in cell D52 and drag it to the right side to fill out the formula. We will find the accounts receivable amounts from this.

• After that, type this formula to find the total current assets. For now, we will find the amount of “cash and cash equivalents” from the cash flow statement and keep it empty for now.

• Then, type another formula to find the fixed assets.

• Then, type this formula to find the accumulated depreciation.

• Next, type this formula in cell D56 to get the values.

• Then, type this formula to calculate the total assets.

• Similarly, we will type formulas to calculate the values for the liabilities and equity parts. Moreover, we will see that the balance verification is not zero yet (0 means the balance sheet balances). This is because we have kept the cash and cash equivalents empty.

• Now, from the cash flow statement, we will get the net cash flow values. Input those values by typing the following formula in cell D51 and filling in the formula on the right side.

• Finally, the pro forma balance sheet is complete.

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

3. Preparing Pro Forma Cash Flow Statement

In this last example, we will prepare a pro forma cash flow statement in Excel. 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” that we created in the second example.

Steps:

• First of all, type all the fields for the cash flow statement.
• Secondly, 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

• Thirdly, type this formula in cell C78 to find the change in accounts receivable and drag it to the right side to fill it in.

• Next, type another formula to calculate the change in accounts payable.

• Then, type this formula to find the change in unearned revenue.

• Afterward, type this formula in cell C81 to find the operating cash flow.

• Then, type this formula to find free cash flow.

• After that, type another formula to calculate the net cash flow from financing.

• Lastly, type this formula in cell C89 to find the net cash flow.

• Thus, we will complete the objective to create pro forma financial statements in Excel.

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

You can download the Excel file from the link below.

Conclusion

We have shown you three quick examples of how to create pro forma financial statements in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Thanks for reading, keep excelling!

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