How to Make a Pro Forma Balance Sheet in Excel (6 Steps)

This article will show you how to make a pro forma balance sheet in Excel. We will project three years of financial statements for a startup business company.

Definition of Balance Sheet & Its Constituents

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 owner’s 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 = Liabilities + Owner’s Equity

Assets: These are the primary resources owned by the company. Assets consist of many types. Examples – are 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.

Step-by-Step Procedures to Make a Pro Forma Balance Sheet in Excel

We will demonstrate how to make a pro forma balance sheet in Excel. Here, we will need the income statement. Following that, we will create the pro forma balance sheet. Lastly, we will project the cash flow statements. To create the pro forma balance sheet, we will use the SUM function to calculate the total amount. Here, we have linked all the formulas in the sheet, 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, all the statements we have created are on the same sheet. This will speed up our navigation time. Additionally, this practice will reduce the risk of linking to unintended cells.

Step 1: Projecting Capital Expenditures and Depreciation

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.

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, we have shown all the formulas in the data set to know how we have calculated all depreciation.

Finally, you will get all the results.

Step 2: Calculating Interest Expenses

We will also need to refer to the income statement for other values. It reports the profit and loss of a company over a certain period. So, the income statement consists of three parts: They are Revenue, Expense, and Profits. So, it contains all income and expenses for a certain period and calculates net profit.

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

Step 3: Make a Pro Forma Balance Sheet with Proper Parameters

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 the balance sheet balances.

Step 4: Evaluating Specific Parameters

Next, type this formula in cell D8 and drag it to the right side to fill out the formula by using the Fill Handle tool. We will find the accounts receivable amounts from this.

=I6*I7

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.

=SUM(D7:D8)

Then, type another formula to find the fixed assets.

=C10+I28

Then, type this formula to find the accumulated depreciation.

=C11-I33

Next, type this formula in cell D11 to get net fixed assets.

=SUM(D10:D11)

Then, type this formula to calculate the total assets.

=SUM(D12,D9)

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.

Step 5: Calculating Cash Flow Values to Balance Pro Forma Sheet

We will need the “net cash flow” from the cash flow statement to calculate the “cash & cash equivalents” on the balance sheet.

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.
Now, from the cash flow statement, we will get the net cash flow values. Input those values by typing the following formula in cell D7 and filling in the formula on the right side.

=C7+C29

Step 6: Showing Final Results

Finally, you will find the complete pro forma balance sheet.
As a result, you will see the Balance verification is zero for all the years and total assets are equal to the sum of liabilities and the shareholder’s equity.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it yourself.

Pro Forma Balance Sheet.xlsx

Conclusion

In this article, we’ve covered step-by-step procedures to make a pro forma balance sheet in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, If you have any questions, comments, or recommendations, kindly leave them in the comment section below.