How to Create Projected Balance Sheet Format for 3 Years in Excel

A company or organization often needs to create a projected balance sheet. It provides an idea of the company’s financial performance and also helps to forecast future conditions. In this tutorial, we will learn how to create a projected balance sheet format for 3 years in Excel. So, without further delay, let’s get started.

What Is a Projected Balance Sheet?

A projected balance sheet contains all the financial information (such as assets, liabilities and owner’s equity) of an organization. It is also called the pro forma balance sheet. A projected balance sheet always satisfies the following equation:

Total Assets = Total Liabilities + Total Equity

In the equation,

Total Assets: It refers to the summation of the resources that a company or organization owns. For example, cash, inventories, prepaid expenses, accounts receivable, licenses, etc.

Total Liabilities: It is the addition of all kinds of debt that any organization has. Such as accounts payable, unearned revenue, mortgage payable, etc.

Total Equity: Equity is the net worth of an owner’s investment in a company after paying all liabilities.

2 Handy Approaches to Create Projected Balance Sheet Format for 3 Years in Excel

This article will guide you with two handy approaches to creating a projected balance sheet format for three years in Excel. To demonstrate the methods, we will provide screenshots with explanations so that you can understand the process easily. The two methods are discussed below.

1. Create Excel Projected Balance Sheet Format for 3 Years Manually

In this method, we will make an Excel projected balance sheet format for 3 years manually. Here, Year 1 is 2019, Year 2 is 2020, and lastly, Year 3 denotes 2021. However, we will explain this approach in seven steps. After following the steps, we will finally get a projected balance sheet like the following picture. Let’s see the steps below.

Step 1: Creating Dataset of Current Assets

In the first step, we will create a table for the Current Assets that will be similar to the following image. To create a dataset format of current assets for 3 years in a projected balance sheet, follow the steps below:

In the beginning, open a worksheet in Excel.
Then, select the entire worksheet with a left-click on the triangle located at the top-left corner of the worksheet.
See the location of the triangle in the following picture.
Next, go to the Home tab.

After that, click on the Number Format drop-down in the Number group (see screenshot).

Later, select Accounting from the dropdown menu.
Now, you will find a dollar sign ($) after inserting any value in the worksheet.
It is needed as we need to deal with the financial condition of a company.

Afterward, type Assets, Year 1, Year 2 & Year 3 in the range B4:E4 of the worksheet.

Subsequently, select range B4:E4 > go to the Home tab > change the Font Size (12) > click on B (Bold).
See the screenshot below for a better understanding of the steps.

Therefore, change the Fill Color (Dark Blue).

Besides, select the font color (White) by clicking on the Font Color dropdown.

Similarly, create a heading (B5:E5) for the Current Assets table like the image below.

Accordingly, type the names of the Current Assets and ‘Total Current Assets’ in the B6:B10 range.
Thereupon, enter the asset values for the 3 years in the C6:E9 range.

At this time, select the B4:E10 range > go to the Home tab > find the Font group > click on the Border dropdown.

Momentarily, we will use the SUM function in Excel, to sum up the Current Assets for each year.
Forthwith, select All Borders from the dropdown menu.

In turn, the table (B4:E10) will look like the following image.

In order to calculate the Total Current Assets of Year 1, type the formula below in cell C10:

=SUM(C6:C9)

Press Enter.
Consequently, you will get the Total Current Assets for Year 1.

In the same way, add the Currents Assets of Year 2 by inserting the following formula in cell D10:

=SUM(D6:D9)

Press the Enter key to get the result in cell D10 (see screenshot).

Ultimately, find the Total Current Assets for Year 3 by entering the formula in cell E10:

=SUM(E6:E9)

Lastly, press the Enter button and you will get the result.
See the final result in the image below.

Step 2: Estimating Total Fixed Assets

In this step, we will estimate Total Fixed Assets in Excel for the projected balance sheet format of 3 years. Follow the steps below to do so:

Firstly, create a dataset (B12:E16) of Fixed Assets for the 3 years by following the process shown in the previous step.
See the dataset in the picture below.

Secondly, to sum up the values of Fixed Assets in Year 1, enter the formula below in cell C16:

=SUM(C13:C15)

Thirdly, press the Enter button on the keyboard.
Consequently, you will get the output in cell C16 (see screenshot).

Next, to add the Fixed Assets in Year 2, type the formula in cell D16:

=SUM(D13:D15)

After that, press Enter.
Thus, you will see the desired output in cell D16.

Finally, to find the Total Fixed Assets in Year 3, insert the formula below in cell E16:

=SUM(E13:E15)

Thereupon, press the Enter key.
In this way, you will get the result in cell E16.

Step 3: Finding Total Assets in Excel

This step will guide you to determine the Total Assets of the Excel projected balance sheet format for 3 years. We will do this by simply adding the Total Current & Fixed Assets. See the following steps:

To begin, create a row (B18:E18) for keeping the Total Assets values for the 3 years.
See the screenshot below.

Now, select cell C18.
After that, calculate the Total Assets for Year 1 by typing the following formula:

=SUM(C10,C16)

Therefore, press the Enter button to get the result.
See the result in cell C18 of the picture below.

At this time, activate cell D18.
Next, to calculate the Total Assets in Year 2, apply the formula in cell D18:

=SUM(D10,D16)

Eventually, press Enter.
Hence, you will see the output in cell D18.

Ultimately, determine the Total Assets in Year 3 by typing the following formula in cell E18:

=SUM(E10,E16)

After pressing the Enter key, you will get the result in cell E18.
Thus, you can calculate the Total Assets for the three years.

Step 4: Making Excel Dataset of Current Liabilities

Here, we will make a heading for the Liabilities and Equity and then create a dataset for the Current Liabilities. Follow the steps below:

First of all, insert a heading (G4:J4) of Liabilities and Equity of Year 1, Year 2, and Year 3 by following the process in Step 1.
The heading will be similar to the following picture.

Then, create a table for the Current Liabilities for the three years (discussed in Step 1).

At this time, go to cell H10 in the worksheet.
Therefore, to find the Total Current Liabilities of Year 1, use the formula:

=SUM(H6:H9)

Next, press the Enter button and see the result in cell H10.

Similarly, calculate Total Current Liabilities in Year 2 and Year 3 (see the screenshot below).
In order to find the Total Current Liabilities in Year 2, type the formula below in cell I10:

=SUM(I6:I9)

Again, for Year 3, insert the following formula in cell J10:

=SUM(J6:J9)

Step 5: Determining Total Long-Term Liabilities

In this step, we will create a dataset for the Long-Term or Fixed Liabilities. To do so, first of all, create a dataset (G12:J16) for the Long-Term Liabilities. The dataset will look like the following image. The steps are below:

In the first place, activate cell H16.
Now, to add the Fixed or Long-Term Liabilities for 2019, enter the formula:

=SUM(H13:H15)

Then, press Enter.
Hence, you will get the output ($ 148,635.00).

In the same way, estimate Total Fixed Liabilities in 2020 and 2021 respectively.
For the year 2020, use the formula below in cell I16:

=SUM(I13:I15)

Similarly, for 2021, type the following formula in cell J16:

=SUM(J13:J15)

Step 6: Calculating Total Owner’s Equity

This step will show you the process to calculate Total Owner’s Equity. For this, first, you need to prepare a table (G18:J21) by entering the names and values of the Owner’s Equity. A sample table is shown in the following picture. See the steps below to find the Total Owner’s Equity:

First, calculate the Total Owner’s Equity for Year 1 by applying the formula below in cell H21:

=SUM(H19:H20)

After that, press Enter.
Consequently, you will get the result ($133,676.00) in cell H21.
See the screenshot below.

In the same way, determine Total Owner’s Equity in Year 2 and Year 3.
For Year 2, enter the formula below in cell I21:

=SUM(I19:I20)

Again, For Year 3, insert the following formula in cell J21:

=SUM(J19:J20)

Step 7: Adding Total Current & Fixed Liabilities with Total Equity

In the last step, we need to add Total Current & Fixed Liabilities with Total Equity. However, we will perform this calculation in the H23:J23 range of the dataset below. Follow the steps below to do so:

First of all, calculate Total Liabilities & Equity in 2019 by typing the following formula in cell H23:

=SUM(H10,H16,H21)

Next, estimate Total Liabilities & Equity in 2020 by inserting the formula below in cell I23:

=SUM(I10,I16,I21)

Finally, add the Total Liabilities and Total Owner’s Equity by applying the following formula in cell J23:

=SUM(J10,J16,J21)

Final Output

After applying the process in the above steps, we will finally get our desired projected balance sheet format in Excel for 3 years. We can see the final look of the balance sheet in the following picture. In this balance sheet, the Total Assets (C18:E18) and Total Liabilities & Equity (H23:J23) are equal for the 3 years.

2. Utilize Microsoft Excel Templates to Make a Projected Balance Sheet Format for 3 Years

In this approach, we will see the process of loading Microsoft Excel templates for making a projected balance sheet format for 3 years. Microsoft Excel contains some built-in balance sheet templates that we can easily modify as per our requirements. See the steps below to create the template:

In order to open the template, first of all, go to the File tab.

Afterward, select the New option.
Eventually, type ‘Balance Sheet’ in the search box.
Therefore, click on the search icon (see screenshot).

In turn, some Balance Sheet templates will appear.
Now, select the Balance Sheet option that you need.
In our case, we selected the Balance Sheet (Simple) as it looks close to the one we require.

Next, click on the Create button.

Hence, a new Excel workbook will open containing 3 worksheets.
Thereupon, select Balance Sheet from the sheet tab section.

As a result, the Balance Sheet will appear (see screenshot).
At this time, you can modify this Balance Sheet as you want.
In our case, we need a balance sheet for three years.
But, we can see that this balance sheet is for two years.
So, we can add another column here and then use it.

Things to Keep in Mind

At the time of preparing a projected balance sheet in Excel, you should always remember some important things. They are below:

In the balance sheet, the Total Assets, Total Liabilities, and equity have to be equal in all years.
Before any depreciation value, always use a minus (–) symbol.

Download Practice Workbook

Download the practice workbook from here.

Making Projected Balance Sheet.xlsx

Conclusion

I hope the above tutorial will be helpful for you to prepare a projected balance sheet format for 3 years in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section.

<< Go Back to Balance Sheet | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!