What Is a 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.
Income Statement
It reports the profit and loss of a company over a period of time. It has 3 parts: 1. Revenue 2. Expense 3. Profits. It contains income and expenses in a period of time and calculates net profit.
Balance Sheet
It summarises the financial position of the company and is also known as a Statement of Financial Position/condition. A balance sheet contains Assets, Liabilities, and Owners’ equity:
Assets = Liabilities + Shareholder’s Equity
- Assets: the main resources owned by the company. Assets can be classified into: Current and Fixed assets, Tangible and Intangible assets, etc.
- Liabilities: values the company owes
- Owners Equity: the value for shareholders after all the company’s assets are sold and company liabilities are paid.
Cash Flow Statement
Cash flow statements are the bridge between the Income statement and the Balance sheet:
- Operations: the main source of revenue
- Investment Activity: Cash received or paid due to buying or selling any assets, taking loans, paying interests of loans, etc
- Financing Activity: Cash flows regarding any changes in equity earning or borrowing entities like bonds, stocks, or dividends.
How to Link 3 Financial Statements
Income statement, balance sheet, and cash flow statements are connected to each other through the following:
Net Earnings and Retained Earnings:
Net earnings from the income statement are connected to the balance sheet retained earnings. These net earnings are used in creating a cash flow statement.
Working Capital:
Inventory, property & equipment, and accounts payable in the balance sheet are linked to working capital in the cash flow statement. Adding inventory and property & equipment to the balance sheet, and subtracting accounts payable from it, returns the working capital for a year.
Property & Equipment and Depreciation & Amortization:
Property & equipment from the balance sheet are lined with the depreciation & amortization in the cash flow statement. Subtracting depreciation & amortization from property & equipment in the balance sheet returns the investments in property & equipment in the cash flow statements.
How to Link 3 Financial Statements in Excel
Step 1 – Create the Income Statement Sheet
Prepare an income statement for the financial statement. The income statements have the records of income, expenses, and tax records. Enter and calculate gross profits. Use the SUM function to calculate total expenses.
- To calculate the gross profit, enter the following formula:
=C5-C6 - Press Enter.
You will get the gross profit for 2019.
- Drag the Fill Handle to the right to fill the other cells with the formula.
You will get the gross profit for the other years.
Calculate the total expenses based on salaries and benefits, rent, depreciation & amortization, and interest.
- To calculate the total expenses, use the following formula:
=SUM(C9:C12) - Press Enter.
You will get the total expenses for 2019.
- Drag the Fill Handle to the right to fill the other cells with the formula.
You will get the Total Expenses for the other years.
Calculate earnings before tax using the gross profit and total expenses:
- Use the following formula:
=C7-C13 - Press Enter.
You will get the earnings before tax for 2019.
- Drag the Fill Handle to the right to fill the other cells with the formula.
You will get the earnings before tax for the other years.
Calculate the net earnings using earnings before tax and taxes:
- Use the following formula:
=C14-C15 - Press Enter.
You will see the net earnings for 2019.
- Drag the Fill Handle to the right to fill the other cells with the formula.
You will get the net earnings for the other years.
Read More: How to Create a Personal Financial Statement in Excel
Step 2 – Create a Balance Sheet
Calculate the total assets.
- Enter the following formula:
=SUM(C6:C9) - Press Enter.
You will get the total assets for 2019.
- Drag the Fill Handle to the right to fill the other cells with the formula.
You will get the total assets for the other years.
Calculate total liabilities based on accounts payable and debt.
- Enter the following formula:
=C12+C13 - Press Enter.
You will get the total liabilities for 2019.
- Drag the Fill Handle to the right to fill the other cells with the formula.
You will get the total liabilities for the other years.
Calculate the shareholder’s equity based on the equity capital and retained earnings.
- Enter the following formula:
=C16+C17 - Press Enter.
You will get the shareholder’s equity for 2019.
- Drag the Fill Handle to the right to fill the other cells with the formula.
You will get the shareholder’s equity for the other years.
Calculate total liabilities and shareholder’s equity based on total liabilities and shareholder’s equity:
- Use the following formula:
=C14+C18 - Press Enter.
You will get the total liabilities and shareholder’s equity for 2019.
- Drag the Fill Handle to the right to fill the other cells with the formula.
You will get the total liabilities and shareholder’s equity for the other years.
Read More: How to Prepare Financial Statements from Trial Balance in Excel
Step 3 – Create the Cash Flow Statement Sheet
To calculate cash from operations:
- Enter the following formula:
=C6+C7-C8 - Press Enter.
This is the output.
- Drag the Fill Handle to the right to fill the other cells with the formula.
You will get cash from operations for the other years.
Enter the amount of cash from investing and from financing and calculate the net increase or decrease in cash:
- Enter the following formula:
=C9-C12+C16 - Press Enter.
- You will get the value of the net increase or decrease in cash for 2019.
- Drag the Fill Handle to the right to fill the other cells with the formula.
You will get the value of the net increase or decrease in cash for the other years.
Calculate the closing cash balance in 2019:
- Enter the following formula:
=C17+C18
- Press Enter.
You will get the closing cash balance in 2020 and 2021 by dragging the Fill Handle to the right.
Read More: Consolidation of Financial Statements in Excel
Step 4 – Link the Financial Statements
Net Earnings and Retained Earnings:
The net earnings in the income statement are connected to the balance sheet retained earnings as shown below.
Use the net earnings to create a cash flow statement.
Working Capital:
Inventory, accounts receivable, and accounts payable in the balance sheet are linked to working capital in the cash flow statement.
Add inventory and accounts receivable in the balance sheet, and subtract accounts payable.
The working capital for 2019 is calculated.
Determine the value of working capital changes for 2020 by subtracting 2019 from 2020. Get working capital for 2021 after subtracting working capital in 2020 from working capital in 2021.
Property & Equipment with Depreciation & Amortization:
Property & Equipment in the balance sheet is lined with the Depreciation & Amortization in the cash flow statement. Subtracting depreciation & amortization from Property & Equipment in the balance sheet returns the investments in Property & Equipment in the cash flow statements.
Download the Practice Workbook
Related Articles
<< Go Back to How to Create Financial Statements in Excel | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Thanks for this! Can you post the solution?
Thank you for your question. This article already demonstrates the solution. To exercise while you read this article, you can download the practice workbook from the Download Practice Workbook section.
Thank you for this demonstration. This helps me experiencing the application of other functions available and you paid you’re for in this software.
Dear Tommy Fuifui,
You are most welcome.
Regards
ExcelDemy
Hi I’d like to ask why assets is not equal to the sum of the liabilities & equity?Thank you.
Hello MILTONKZ,
Thanks for reaching out to us and sharing such a fundamental issue.
Certainly, you pointed out the right fact. A fundamental concept of the Double Entry Accounting System is that the Total Assets must equal Total Liabilities and Equities. A balanced accounting equation (A=L+P) is a must condition because the dual aspect of accounting for income and expenses will result in equal increases to or decreases in assets or liabilities. Thanks for pointing out such a mistake of imbalanced accounting equation.
Thanks again, for pointing out such a mistake. The correction of our dataset and related images is made accordingly. Stay connected with us.
Regards,
Abdullah Al Masud
ExcelDemy Team