# How to Automate Financial Statements in Excel (with Easy Steps)

## What is a Financial Statement?

Financial statements, also known as financial reports, are summary documentation of the financial condition of an organization, summarizing a company’s performance throughout the year.

## How to Automate Financial Statements in Excel: with Easy Steps

The following picture shows a Trial Financial Statement of ABC company. Using this Trial Financial Statement, we will demonstrate how to automate financial statements in Excel. We used Microsoft Office 365, but you can use any available Excel version.

### Step 1 – Calculating Total Current Assets

In a Financial Statement, Assets are a combination of Current Assets, Fixed ( Long Term Assets), and Other Assets.

To begin, we calculate the total Current Assets. From the Trial Financial Statement, the current assets are Cash, Account Receivable, Prepaid Insurance, and Inventory.

• In the Trial Financial Statement, add entries in cells C7:C10 for Cash, Account Receivable, Prepaid Insurance, and Inventory in the Current Assets group.
• If you have more current assets in your Trial Financial Statement, include them in the Current Assets group too.

• In cells D7:D10, insert the values of the Current Assets from the Trial Financial Statement.

Now we’ll use the SUM function to calculate the Total Current Assets.

• Enter the following formula in cell D11:
`=SUM(D7:D10)`

• Press ENTER.

Total Current Assets are returned in cell D11.

### Step 2 – Computing Total Fixed Assets

Next we calculate the Total Fixed Assets,Â also known as long-term assets. From the Trial Financial Statement, we identify Equipment and Long Term Investment as Fixed Assets, and calculate the Total Fixed Assets.

• Insert Equipment and Long Term Investment in the Fixed ( Long Term Assets) group in cells C13:C14.
• If you have more fixed assets in your trial Financial Statement, include them in this group.

• In cells D13:D14, insert the values of Fixed Assets from the Trial Financial Statement.

We again use the SUM function to calculate the Total Fixed Assets.

• Enter the following formula in cell D15:
`=SUM(D13:D14)`

• Press ENTER.

Total Fixed Assets are shown in cell D15.

### Step 3 – Finding Other Assets

Next, we calculate the Total Other Assets.

• From the Trial Financial Statement, insert Income Tax in the Other Assets group in cell C17.
• Enter Other in cell C18, in case in the future there more assets that need to be included underÂ Other Assets.

• In cell D17, insert the value of Income Tax from the Trial Financial Statement.

• Enter the following formula in cell D19 to calculate Total Other Assets:
`=SUM(D17:D18)`

• Press ENTER.

### Step 4 – Determining Total Assets

• Enter the following formula in cell D21:
`=D11+D15+D19`

This simply adds Total Current Assets, Total Fixed Assets, and Total Other Assets.

• Press ENTER.

### Step 5 – Calculating Total Current Liabilities

Liabilities and Owner’s Equity is a combination of Current Liabilities, Long Term Liabilities, and Owner’s Equity.

First we calculate the total Current Liabilities.

• From the Trial Financial Statement, insert Salary Payable, Unearned Revenue, and Tax Payable in the Current Liabilities group in cells C25:C27.
• If you have more Current Liabilities, include them in thisÂ group.

• In cells D25:D27, insert the values of Current Liabilities from the Trial Financial Statement.

• Enter the following formula in cell D28 to calculate the Total Current Liabilities:
`=SUM(D25:D27)`

• Press ENTER.

### Step 6 – Computing Total Long Term Liabilities

• From the Trial Financial Statement, insert Long Term Loan, Long Term Account Payable, and Income Tax in the Long Term Liabilities group.
• If you have more Long Term Liabilities, include them in thisÂ group.

• In cells D30:D32, insert the values of Long Term Liabilities from the Trial Financial Statement.

• Enter the following formula in cell D33 to calculate the Total Long Term Liabilities:
`=SUM(D30:D32)`

• Press ENTER.

### Step 7 – Finding Total Ownerâ€™s Equity

• From the Trial Financial Statement, insert the Ownerâ€™s Investment and Authorized Share in the Ownerâ€™s Equity group.
• If you have more Ownerâ€™s Equity categories, include them in thisÂ group.

• In cells D35:D36, insert the values of Ownerâ€™s Equity from the Trial Financial Statement.

• Enter the following formula in cell D37Â to calculate the Total Long Term Liabilities:
`=SUM(D35:D36)`

• Press ENTER.

### Step 8 – Determining Total Liabilities and Ownerâ€™s Equity

• Enter the following formula in cell D39:
`=D28+D33+D37`

This simply adds Total Current Liabilities, Total Long Term Liabilities, and Total Owner’s Equity.

• Â Press ENTER.

Our automated financial statement is complete.

## Calculating Common Financial Ratios in Financial Statements

Financial statements contain several financial ratios, which give an overall view of the financial statements. Letâ€™s determine the common financial ratios.

### Step 1 – Calculating the Debt Ratio

Debt Ratio = Total Liabilities/ Total Current Assets

• Enter the following formula in cell D42:
`=IF(D11=0,"",(D28+D33)/D11)`

Formula Breakdown

• IF(D11=0,””,(D28+D33)/D11) â†’ the IF function returns a blank cell when D11 is 0, otherwise it returns the value of the division.
• (D28+D33) â†’ are the Total Liabilities.
• D11 â†’ is the Total Current Assets.
• IF(D11=0,””,(D28+D33)/D11) â†’ becomes
• Output: 0.616004606
• Explanation: as D11 is not equal to 0, the IF function returns the value of the division. 616004606 is the debt ratio.
• Press ENTER.

The Debt Ratio is returned in cell D42.

### Step 2 – Computing the Current Ratio

Current Ratio = Current Assets/ Current Liabilities

• Enter the following formula in cell D43:
`=IF(D28=0,"",D11/D28)`

Here,Â the IF function returns a blank cell when D28 is 0, otherwise it returns the value of the division.

• Press ENTER.

The Current Ratio is returned in cell D43.

### Step 3 – Finding the Working Capital

Working Capital = Current Assets-Current Liabilities

• Enter the following formula in cell D44:
`=D11-D28`

This simply subtracts cell D28 from cell D11.

• Press ENTER.

Working Capital is returned in cell D44.

### Step 4 – Determining the Assets to Equity Ratio

Assets to Equity Ratio = Total Assets/Total Ownerâ€™s Equity

• Enter the following formula in cell D45:
`=IF(D37=0,"",D21/D37)`

Here,Â the IF function returns a blank cell when D37 is 0, otherwise it returns the value of the division.

• Press ENTER.

The Assets to Equity Ratio is returned in cell D45.

### Step 5 – Calculating Debts to Equity Ratio

Debts to Equity Ratio = Total Liabilities/Total Ownerâ€™s Equity

• Enter the following formula in cell D46:
`=IF(D37=0,"",(D28+D33)/D37)`

Here, Â the IF function returns a blank cell when D37 is 0, otherwise it returns the value of the division.

• Press ENTER.

The Debts to Equity Ratio is returned in cell D45.

The automated financial statement with common financial ratios is complete.

## Related Articles

<< Go Back to How to Create Financial Statements in Excel | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF