How to Make Projected Balance Sheet in Excel (with Quick Steps)

A Projected Balance Sheet can provide an inside look at the company or the organization. They can ease the process of evaluating a company and assist in making future predictions. If you are curious to know how you can make a projected balance sheet in Excel, this article may come in handy for you. Here we discuss how you can make a Projected Balance Sheet in Excel in detail and in a self-explanatory way.

Overview of Projected Balance Sheet

Definition

Projected Balance Sheet also known as Proforma balance sheets, is a record which keeps the change of liability, asset, equity of a organization/company over a certain period of time.

Components of Projected Balance Sheet

Normally you would need a Projected Balance Sheet to have an idea of how your organization will operate in the future. It is a tabulation for future performance or projections. It has some integral components, whose inclusion is a must in order to make a Projected Balance Sheet.

Assets: Simply put, Assets are those properties that the company owns. Those Assets can be Fixed Assets like land, or some Assets could be current property, like software. They can also be categorized as tangible and nontangible.
Liabilities: Liabilities can be explained as the things that the company owes. They could be mortgages, external debt, accrued taxes, etc
Equity: Equity represents the money that would return to the shareholders if all of the Assets (current & Fixed) are liquidated and all debts are paid.

How to Make Projected Balance Sheet in Excel: Step-by-Step Procedure

We will prepare and make a projected balance sheet in Excel using the basic financial data of a company. The whole process below is discussed in detailed steps. those steps involve the collection of sensitive data. These data must be carefully collected and input into the sheets in order to prevent any kind of unwanted results

Step 1: Prepare Current Assets Dataset

Before we delve into making the Projected Balance Sheet, we need to gather the necessary information first. we need to collect all kinds of Current Asset information with their exact amount. Only after then, we can prepare the Current Asset dataset, using the SUM function.

We have Cash, Inventory, Prepaid, Land, and Account Receivable values in the range of cells B6:B10.
And the value of them in the October 20, 2021, fiscal year, and June 5, 2022, fiscal year is presented. We can observe a steady increase in all of them over time.

Now select the cell C11 and enter the following formula:

=SUM(C6:C10)

Entering this formula will calculate the total of the current Assets mentioned in the range of cell C6:C10.in the time period October 20, 2021.

Now select the cell D11 and enter the following formula:

=SUM(D6:D10)

Doing this will calculate the SUM of the total of the Current Assets mentioned in the range of cells D6:D10. On the date June 5, 2022 time period.

Step 2: Estimate Total Non-Current Assets

Right after we estimated the Current Assets, we now calculate the non-current or the Fixed Assets. After that, we will be able to determine the total asset amount, using the SUM function.

We put the Non-Current costs of the organization in a list format in the range of cells B14:B17.
We then put the values of those non-current Assets values for October 20, 2021, in the range of cells C14:C17. And the values of non-current Assets for June 5, 2022, in the range of cells D14:D17.

After then, select the cell C18 and enter the following formula:

=SUM(C14:C17)

Doing this will calculate the sum of Non-Current Assets mentioned in the range of cell C14:C17 for the time period of October 20, 2021.

Next, select the cell D18, and enter the following formula:

=SUM(D14:D17)

Doing this will calculate the sum of Non-Current Assets mentioned in the range of cell D14:D17 for the time period of June 5, 2022.

After this, select the cell C20 and enter the following formula:

=SUM(C11,C18)

And then select the cell D20 and enter the following formula:

=SUM(D11,D18)

This will calculate the sum of two types of Assets, Current and Non-Current Assets.
For two different periods of time. We can get an idea of how the Assets value increased over time.

Step 3: Evaluate Liabilities

Like the Assets, Liabilities are divided into Fixed and Current Liabilities. The things that the company owes permanently are Fix Liabilities. And monthly payments like Salary, insurance installments, and taxes are known as Current Liabilities. the total liabilities will be evaluated using the SUM function.

We put the Current Liabilities of the organization in a list format in the range of cells F6:F10.
We then put the values of those Current Liabilities values for October 20, 2021, in the range of cells G6:G10. And the values of Current Liabilities for June 5, 2022, in the range of cells H6:H10.

Next, select the cell G11 and enter the following formula:

=SUM(G6:G10)

Doing this will calculate the sum of Current Liabilities mentioned in the range of cell G6:G10 for the time period of October 20, 2021.

Next, select the cell H11 and enter the following formula:

=SUM(H6:H10)

Doing this will calculate the sum of Current Liabilities mentioned in the range of cell H6:H10 for the time period of June 5, 2022.

We put the Fixed Liabilities of the organization in a list format in the range of cells F14:F17.
We then put the values of those Fixed Liabilities values for October 20, 2021, in the range of cells G14:G17. And the values of Fixed Liabilities for June 5, 2022, in the range of cells H14:H17.

Next, select the cell G18 and enter the following formula:

=SUM(G14:G17)

This will calculate the sum of Fixed Liabilities mentioned in the range of cell G14:G17 for the time period of October 5th, 2021.

Then, select the cell H18 and enter the following formula:

=SUM(H14:H17)

This will calculate the sum of Fixed Liabilities mentioned in the range of cell H14:H17 for the time period of June 5, 2021.

Step 4: Calculate Total Equity

After the calculation of the Assets and the liability, it is high time we calculate the Total Equity of the organizations, using the SUM function.

We put the Equity of the organization in a list format in the range of cells F21:F23.
We then put the values of those Equity values for October 20, 2021, in the range of cells G21:G23. And the values of Current Liabilities for June 5, 2022, in the range of cells H21:H23.

Next, select the cell G24 and enter the following formula:

=SUM(G21:G23)

Doing this will calculate the sum of Equity values mentioned in the range of cell G21:G23 for the time period of October 20, 2021.

Next. select the cell H24 and enter the following formula:

=SUM(H21:H23)

Doing this will calculate the sum of Equity values mentioned in the range of cell H21:H23 for the time period of June 5, 2022.

Step 5: Calculate Total Liabilities and Equity

Both the Liabilities and the Equity count as the outgoing flow of the money of an organization. A summation of them can provide a much clearer insight into the stream of money. The SUM function will be used here for this purpose.