How to Link 3 Financial Statements in Excel (with Easy Steps)

The financial statements of an organization must provide a detailed overview of its financial situation at any given point in time. There are three main types of financial statements: balance sheet, income statement, and cash flow statement. If you are looking for some special tricks to link the 3 financial statements in Excel, you’ve come to the right place. There is one way to link the 3 financial statements in Excel. This article will discuss every step of this method to link the 3 financial statements in Excel. Let’s follow the complete guide to learn all of this.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.

What Is Financial Statement?

There are three main types of financial statements; balance sheet, income statement, and cash flow statement. These statements summarize the financial situation of an organization or company. They are the written documents or records that the company performs throughout the year. There are 3 main parts of a financial statement report:

Income Statement

It reports the profit and loss of a company over a certain period. So, the income statement is made of 3 parts: 1. Revenue 2. Expense 3. Profits. So, it contains all incomes and expenses in a certain period and calculates net profit.

Balance Sheet

It summarises the financial position of the company after a certain period and it is also known as a Statement of Financial Position/condition. In general, a balance sheet contains 3 portions. And, these are Assets, Liabilities, and Owners’ equity  A balance sheet provides a glimpse of the company’s finances. The sheet consists of the company’s liabilities, assets, and shareholder’s equities. Balance sheets are organized according to the equation:

• Assets: These are the main resources owned by the company. Assets can be classified into many types. Like Current and Fixed assets, Tangible and Intangible assets, etc.
• Liabilities: They are things that the company owes to a person or a company like cash, loans, etc.
• Owners Equity: It represents the value for a company’s shareholders after all the company’s assets were sold off and all company liabilities were paid off.

Cash Flow Statement

Cash flow statements are the bridge between the Income statement and the Balance sheet.  There are also 3 parts to it:

• Operations: It is the main source of revenue for a company or organization. Cash flows regarding main operations will be included here.
• Investment Activity: Cash received or paid due to buying or selling any assets, taking loans, paying interests of loans, etc are included here.
• Financing Activity: Cash flows regarding any changes in equity earning or borrowing entities like bonds, stocks, or dividends.

How 3 Financial Statements Can Be Linked?

Three financial statements: income statement, balance sheet, and cash flow statements are connected to each other through the following particulars:

Net Earnings and Retained Earnings:

Net earnings from the income statement are connected with the balance sheet retained earnings. We also use these net earnings in creating a cash flow statement.

Working Capital:

Here, inventory, property & equipment, and accounts payable from the balance sheet are linked with working capital from the cash flow statement. Adding inventory and property & equipment to the balance sheet, then subtracting accounts payable from it, gives us working capital for a particular year.

Property & Equipment and Depreciation & Amortization:

Property & equipment from the balance sheet is lined with the depreciation & amortization in the cash flow statement. Subtracting depreciation & amortization from property & equipment from the balance sheet returns us the investments in property & equipment in the cash flow statements.

Step-by-Step Procedure to Link 3 Financial Statements in Excel

In the following section, we will use one effective and tricky method to link the 3 financial statements in Excel. We will create three financial statements in this article: an income statement, a balance sheet, and a cash flow statement. We will then link these three statements together. This section provides extensive details on this method. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

Step 1: Create Income Statement Sheet

Our first task would be to prepare an income statement for the financial statement. The income statements have the records of incomes, expenses, and tax records. So, first, let’s record and calculate gross profits made by the company. Here, we will use the SUM function to calculate total expenses.

You have to follow the following steps to create an income statement sheet.

• First of all, to calculate the gross profit, we have to type the following formula.

• Then, press Enter.
• Therefore, you will get gross profit for the year 2019.

• Then, drag the Fill Handle icon to the right to fill other cells with the formula.
• Consequently, you will get the other year’s gross profit.

• Now, we will show how to calculate the total expenses based on salaries and benefits, rent, depreciation & amortization, and interest.
• Next, to calculate the total expenses, we have to type the following formula.

• Then, press Enter.
• Therefore, you will get the total expenses for the year 2019.

• Then, drag the Fill Handle icon to the right to fill other cells with the formula.
• Consequently, you will get the other year’s total expenses.

• This step illustrates how to calculate earnings before tax using gross profit and total expenses.
• Next, to calculate the earnings before tax, we have to type the following formula.

• Then, press Enter.
• Therefore, you will get the earnings before tax for the year 2019.

• Then, drag the Fill Handle icon to the right to fill other cells with the formula.
• Consequently, you will get the other year’s earnings before tax.

• This step illustrates how to calculate net earnings using earnings before tax and taxes.
• Next, to calculate the net earnings, we have to type the following formula.

• Then, press Enter.
• Therefore, you will get the net earnings for the year 2019.

• Then, drag the Fill Handle icon to the right to fill other cells with the formula.
• Consequently, you will get the other year’s net earnings.

Read More: How to Create a Personal Financial Statement in Excel

Step 2: Create Balance Sheet

Here, we are going to demonstrate how to prepare a balance sheet for the financial statement. A balance sheet has three parts- records of assets, liabilities, and stakeholders’ equity. To prepare a balance sheet we first need to enter the values of all the current and non-current assets. We are taking two data points for three years. This help better comparison of the organization’s financial status.  We can prepare the sheets for one entry too. Either way, you can add all the assets in a separate category or in one- depending on how you want to present it. We have added cash, accounts receivable, inventory, and property and equipment as the assets. The asset chart of the balance sheet will look like this. Here, we will use the SUM function to calculate total assets.

• First of all, to calculate the total assets, we have to type the following formula.

• Then, press Enter.
• Therefore, you will get the total assets for the year 2019.

• Then, drag the Fill Handle icon to the right to fill other cells with the formula.
• Consequently, you will get the other year’s total assets.

• Now, we are going to calculate total liabilities based on accounts payable and debt.
• Next, to calculate the total liabilities, we have to type the following formula.

• Then, press Enter.
• Therefore, you will get the total liabilities for the year 2019.

• Then, drag the Fill Handle icon to the right to fill other cells with the formula.
• Consequently, you will get the other year’s total liabilities.

• Now, we are going to calculate shareholder’s equity based on equity capital and retained earnings.
• Next, to calculate the shareholder’s equity, we have to type the following formula.

• Then, press Enter.
• Therefore, you will get the shareholder’s equity for the year 2019.

• Then, drag the Fill Handle icon to the right to fill other cells with the formula.
• Consequently, you will get the other year’s shareholder’s equity.

• Now, we are going to calculate total liabilities and shareholder’s equity based on total liabilities and shareholder’s equity.
• Next, to calculate the total liabilities and shareholder’s equity, we have to type the following formula.

• Then, press Enter.
• Therefore, you will get the total liabilities and shareholder’s equity for the year 2019.

• Then, drag the Fill Handle icon to the right to fill other cells with the formula.
• Consequently, you will get the other year’s total liabilities and shareholder’s equity.

Read More: How to Prepare Financial Statements from Trial Balance in Excel

Step 3: Create Cash Flow Statement Sheet

In this step, we are going to demonstrate how to create a cash flow statement based on cash from the operation, sash from financing, and cash from investing. Let’s walk through the following steps to create a cash flow statement. Using net earnings, depreciation & amortization, and changes in working capital, we will calculate cash from operations.

• First of all, to calculate the cash from operations, we have to type the following formula.

• Then, press Enter.
• Therefore, you will get the cash from operations for the year 2019.

• Then, drag the Fill Handle icon to the right to fill other cells with the formula.
• Consequently, you will get the other year’s cash from operations.

• Next, you have to enter the amount of cash from investing and cash from financing.
• Afterward, to calculate the net increase or decrease in cash, we have to type the following formula.

• Then, press Enter.
• Therefore, you will get the value of the net increase or decrease in cash for the year 2019.

• Then, drag the Fill Handle icon to the right to fill other cells with the formula.
• Consequently, you will get the other year’s value of the net increase or decrease in cash.

• Afterward, to calculate the closing cash balance in 2020, we have to type the following formula.

• Then, press Enter.
• Therefore, you will get the closing cash balance in 2020.

• Next, to calculate the closing cash balance in 2021, we have to type the following formula.

• Then, press Enter.
• Therefore, you will get the closing cash balance in 2021.

Read More: How to Create Format for Projected Financial Statements in Excel

Step 4: Link 3 Financial Statements

Here, we are going to demonstrate how the three statements are connected with each other.

Net Earnings and Retained Earnings:

Here net earnings from the income statement are connected with the balance sheet retained earnings as shown below. We also use these net earnings in creating a cash flow statement.

Working Capital:

Here, inventory, property & equipment, and accounts payable from the balance sheet are linked with working capital from the cash flow statement. Adding inventory and property & equipment to the balance sheet, then subtracting accounts payable from it, gives us working capital for 2019.

Adding inventory and property & equipment to the balance sheet, then subtracting accounts payable from it, gives us working capital. Finally, we will be able to determine the value of working capital changes for 2020 by subtracting 2019 from 2020. We also get 2021’s working capital changes after subtracting 2020’s working capital from 2021’s working capital.

Property & Equipment with Depreciation & Amortization:

Property & equipment from the balance sheet is lined with the depreciation & amortization in the cash flow statement. Subtracting depreciation & amortization from property & equipment from the balance sheet returns us the investments in property & equipment in the cash flow statements.

Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to link the 3 financial statements in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!

Related Articles

• How to Create Pro Forma Financial Statements in Excel
• How to Automate Financial Statements in Excel (with Easy Steps)
• Construction Company Financial Statements in Excel