Akib Bin Rashid
Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will show you how to create a personal financial statement in Excel.
Download Practice Workbook
Download this workbook and practice while going through the article.
Introduction to Personal Financial Statement
Personal Financial Statements are summary reports which contain the state of the financial situation of an individual. They include the reports of the balance sheet and income statement. They are the written documents or records that the company performs throughout the year.
Balance Sheet
A Balance sheet summarizes the financial position after a certain period. 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. Balance sheets for an individual are organized according to the equation:
Assets = Liabilities + Equity/Net WorthAssets: These are the main resources owned by individuals. They can be classified into many types. Like Current and Fixed assets, Tangible, and Intangible assets, etc.
Liabilities: They are things that the individual owes to a person or a company like cash, loans, etc.
Equity: For a personal balance sheet, this is the difference between assets and liabilities.
Income Statement
It reports the profit and loss of a company over a certain period. So, the income statement is made of 3 parts.
- Revenue
- Expense
- Net Income.
2 Steps to Create a Personal Financial Statement in Excel
In this article, I will create a personal financial statement in Excel. The entire process is divided into 2 main parts. They are
- Preparing Balance Sheet
- Preparing Income Statement
Let’s prepare them steps by steps.
Step 1: Prepare Balance Sheet
The first step is to prepare a balance sheet. For your convenience, I have divided the task into 3 parts.
Create Outline
The first task is to create an outline. There is no fixed rule for doing so. I have given a sample here.
Write Down Information
The next task is to put the correct information in the right account. This varies from person to person. I have the following information.
Calculate Assets, Liabilities, and Equity
The next part is the calculation. I will calculate total assets, total liabilities, and equity one by one.
To calculate total assets,
- Go to C15 and write down the formula
=SUM(C9:C14)
- Then, press ENTER to get the output.
- Similarly, calculate total liabilities.
Equity is the difference between total assets and total liabilities. To calculate it,
- Go to F15 and write down the following formula
=C15-F13
- Then, press ENTER to get the output.
Read More: How to Prepare Financial Statements from Trial Balance in Excel
Step 2: Create Income Statement
The next step is creating an income statement. For your convenience, I have divided this step into 3 parts. Let’s see what they are.
Create Outline
The first task is to create an outline. There is no fixed rule for doing so. I have given a sample here.
Write Down Information
The next task is to put the correct information in the right account. This varies from person to person. I have the following information.
Calculate Total Revenues, Expenses, and Net Income
The final part is to calculate the total revenue, expense, and net income.
To calculate the total revenue,
- Go to D10 and write down the formula
=SUM(D8:D9)
- Then, press ENTER to get the output.
- Similarly, calculate the total expense.
- Finally, I will calculate the net income. This is the difference between total revenue and expense. To calculate it
- Go to D21 and write down the formula
=D10-D19- After pressing ENTER, you will get the output.
Read More: How to Automate Financial Statements in Excel (with Easy Steps)
Things to Remember
- An income statement calculates the profit or loss of an individual/ a business over a period of time.
- A balance sheet illustrates the financial position of an individual/ a business at a specific point in time.
Conclusion
In this article, I have explained how to create a personal financial statement in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.
