Create Projected Balance Sheet Format for Bank Loan in Excel

Get FREE Advanced Excel Exercises with Solutions!

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes organizations need to show a projected balance sheet to get loans from the bank. In this article, I will show you how to create a projected balance sheet for a bank loan in Excel format.


Introduction to Projected Balance Sheet

Projected balance sheets, also known as pro forma balance sheets, are statements that show estimated changes in a company’s financial status, such as investments, other assets, liabilities, and equity financing. Company owners or accounting professionals use projections to learn more about their business and forecast income and expenses for the future.

In general, a balance sheet contains 3 portions. And, these are Assets, Liabilities, and Owners’ equity. Balance sheets for an individual are organized according to the equation:

Assets = Liabilities + Equity

Assets: These are the main resources owned by the organization. They can be classified into many types. Like Current and Fixed assets, Tangible, and Intangible assets, etc.

Liabilities: They are things that the organization owes to a person or a company like cash, loans, etc.

Equity: Equity is the portion of a business that belongs to the owner. This is the difference between assets and liabilities.

Read More: How to Create Daily Bank Balance Report Format in Excel


3 Steps to Create Projected Balance Sheet Format for Bank Loan in Excel

In this article, I have divided the process of creating a projected balance sheet into 3 easy steps. However, there is no fixed rule for this.

Step 1: Create Format Outline

The first step is to create an outline. The balance sheet should start with a heading followed by the company’s name and the date it is being created. This is what it looks like.

Outline


Asset

These are the main resources an organization owns. There are many types of assets like Current and Fixed assets, Tangible, and Intangible assets, etc.

The balance sheet includes assets in the following way.

Outline for asset projected balance sheet for bank loan in excel format


Liabilities

Liabilities are mainly of two types. Long-term liabilities and current liabilities. Long-term liabilities are the liabilities that are payable beyond 12 months. The current liabilities must be paid within 12 months.


Owner’s Equity

Next comes the Owner’s Equity. The outline for this might look like this.

outline for equity projected balance sheet for bank loan in excel format

Read More: Income and Expenditure Account and Balance Sheet Format in Excel


Step 2: Write Down Information about the Asset

Now, you have to give the inputs correctly. Write down the information correctly.

Asset

First comes the assets. Here is a sample of all the asset accounts with their values.


Liabilities

Next comes the liabilities. You must categorize the accounts into long-term and current liabilities.

information projected balance sheet for bank loan in excel format


Owner’s Equity

Finally, put the information correctly for the Owner’s Equity accounts.

Read More: Balance Sheet Format of a Company in Excel


Step 3: Calculate Total Assets, Liabilities, and Owner’s Equity

Now, I will calculate total assets, liabilities, and owner’s equity. I will use the SUM function to calculate these values.

First of all, I will calculate the total current assets. To do so,

  • Go to C15 and write down the following formula
=SUM(C10:C14)

Asset Calculation information projected balance sheet for bank loan in excel format

  • Then, press ENTER to get the output.

  • Similarly, calculate the total current asset for the projected period and the total fixed asset for both the current and projected period.

Asset Calculation information projected balance sheet for bank loan in excel format

Now, we will calculate the total assets for the current period. To do so,

  • Go to C25 and write down the following formula
=C15+C22

Liabilities Calculation information projected balance sheet for bank loan in excel format

  • Then, press ENTER to get the output.

  • Similarly, calculate the total assets for the projected period.

Asset Calculation information projected balance sheet for bank loan in excel format

  • Next, we will calculate the liabilities just like the assets.

Liabilities Calculation information projected balance sheet for bank loan in excel format

  • Equity calculation will follow the same way.

  • The balance sheet includes total liabilities and owner’s equity. So we will now calculate that.
  • Go to C53 and write down the following formula
=C42+C51

  • Then, press ENTER to get the output.

Liabilities and Equity Calculation information projected balance sheet for bank loan in excel format

  • Do the same for the projected period.

Liabilities and Equity Calculation information projected balance sheet for bank loan in excel format

Read More: Create a Balance Sheet Format for Trading Company in Excel


Things to Remember

  • The total asset will equal the sum of total liabilities and equity.
  • Projected values are based on forecasts.

Download Practice Workbook

Download this projected balance sheet format.


Conclusion

In this article, I have explained how to create a projected balance sheet for bank loans in Excel format. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.



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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo