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.
Read More: How to Make Stock Balance Sheet in Excel
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.
Read More: How to Tally a Balance Sheet in Excel
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.
- Select cell G27 and enter the following formula:
=SUM(G11,G18,G24)
Next, Select cell H27 and enter the following formula:
=SUM(H11,H18,H24)
- Doing this will calculate the summation of both types of Liabilities (Current & Fixed) and Equity in cell H27.
- And this is how we make the Projected Balance Sheet in Excel.
Read More: Debit Credit Balance Sheet with Excel Formula
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the question “how to make Projected Balance Sheet in Excel” is answered here in 5 separate steps with elaborate explanations.
For this problem, a workbook is available for download where you can practice these steps.
Feel free to ask any questions or feedback through the comment section.
Related Articles
- How to Prepare Balance Sheet from Trial Balance in Excel
- How to Calculate Running Balance Using Excel Formula
- How to Keep a Running Balance in Excel
- Calculate Debit Credit Running Balance Using Excel Formula
- How to Make Profit and Loss Account and Balance Sheet in Excel
- How to Make Trial Balance in Excel