How to Prepare Financial Statements in Excel (with Easy Steps)
It reports the profit and loss of a company over a certain period. So, the income statement is made of 3 parts:
So, it contains all incomes and expenses in a certain period and calculates net profit.
Part 3 – 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 interest on loans, etc., are included here.
- Financing Activity : Cash flows regarding any changes in equity earning or borrowing entities like bonds, stocks, or dividends.
How to Prepare Financial Statements in Excel: with Easy Steps
Step 1 – Create a Balance Sheet
Part 1 – Create a Layout
- Create 3 individual sections for Assets, Liabilities, and Owner’s equity and assign a cell to calculate the balance value.
- In the assets section, you can create 2 parts for current assets, fixed assets, and other assets.
- In the liabilities section, there will be two sections for Current and long-term liabilities.
Balance = Assets – Total Liabilities – Owner’s Equity
Part 2 – Calculate Total Assets
- Use the SUM function to calculate the Total Current Assets, Total Fixed Assets, and Total Other Assets.
Part 3 – Calculate Total Liabilities and Owner’s Equity:
- Insert the values of current and long-term liabilities and calculate their totals.
- Insert the values of the owner’s equity and sum them to calculate the total.
- Calculate the total liabilities and owner equity value.
Part 4 – Calculate the Balance Amount:
- Subtract the total liabilities and owner’s equity from the value of the total assets.
- Here’s a sample balance sheet for a company.
of Financial Statements in Excel" width="1288" height="651" />
Step 2 – Create the Income Statement Sheet
Part 1 – Calculate Total Revenue:
- Calculate the total sales value and subtract the value of the fewer quality products from it.
- Add initial inventory, purchased goods and materials, and labor costs.
- Subtract the value of ending inventory from the total cost of goods.
- Calculate the gross revenue by subtracting the total cost of goods from the net sales.
Part 2 – Calculate Total Operating Expenses
- Add all relevant items here and sum them to calculate the total operating expenses.
Part 3 – Calculate Net Profit
- Calculate interest expenses or profit, tax expenses, or anything else that affects your profit.
- Add some items as Non–recurringitems.
- Sum them to get the Net Profit value.
- You will get a complete Income Statement Sheet.
of Financial Statements in Excel" width="1318" height="625" />
Part 1 – Create a Layout:
- Create a layout to allocate the cash flow items into 3 different categories for operations, investment activity, and financial activity.
Flow Statement Sheet" width="1203" height="691" />
Part 2 – Calculate Net Cash Flow of Operations:
- You can receive cash from clients or many other sources and cash be paid for many reasons like inventory, salary, administrative expense, interest, expense, etc.
- Subtract the cash-paid value from the cash received value to get the net cash flow of operations.
Flow Statement Sheet" width="638" height="516" />
Part 3 – Calculate Net Cash Flow of Investments
- The items relevant to the assets of the company include properties, machinery, loans, investment securities, etc.
Flow Statement Sheet" width="653" height="482" />
Part 4 – Calculate Net Cash Flow of Financial Activities
- Include items like stock issuance, borrowings, dividends, etc.
Flow Statement Sheet" width="668" height="442" />
- You will get the Cash flow statement of the company.
Download the Practice Workbook
Financial Statements.xlsx
Related Articles
