Accounting Templates in Excel

Reviewed byDheeraj Vaidya, CFA, FRM

What Is Accounting Template In Excel?

Accounting template in excel is useful for creating simple accounting worksheets. It consists of tools for accounts payable, accounts receivable, cash book, and petty cash book.

Excel workbook itself is pre-designed to insert data and perform calculations based on users’ requirements. To make our work even easier, we can create templates in excel based on niche, such as accounting template in excel.

This article will show you how to create those accounting templates with Excel worksheets. If you are an entrepreneur and cannot buy sophisticated software to manage your business accounts, we will help you with simple templates to track your expenses and income.

Key Takeaways

  • Accounting templates in excel are helpful in performing calculations, maintaining accounts, and keeping track of expenses.
  • Templates in excel provide are pre-designed sheets with various formatted layouts. Using templates saves time because the sheets are already created with basic elements.
  • There are many available accounting templates in excel such as cashbook template, petty cashbook template, aging schedule of accounts receivable, etc.,
  • We can use the templates based on the requirements – for eg: small business owners can make use of petty cashbook template.

Top 5 Accounting Templates in Excel Worksheets

Below are the various accounting worksheet templates in Excel.

You can download this Excel Accounting Templates here – Excel Accounting Templates

#1 – Cashbook Template

Cashbook is one of the critical ledgers in accountingCashbook is used to record daily transactions in the company. Meanwhile, we can see two kinds of transactions here: debit transactions, i.e., an outflow of cash, and credit transactions, i.e., the cash inflow.

We can record all the debit transactions on one side of the account. We may record all the credit transactions on the other side of the ledger. Also, we should record all the transactions in chronological order.

Accounting Template Example 1

You are free to use this image o your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Accounting Templates in Excel (wallstreetmojo.com)

For both debit and credit transactions, we can see three standard columns.

First, we need to enter the date of the transaction. Then, we need to enter the transaction details, and finally, we should enter the transaction amount.

Then, we can get the debit total and credit total. So, cell D14 has a total balance available, i.e., Credit Total – Debit Total.

Similarly, we can use cashbook template in excel.

#2 – Petty Cashbook Template

Another simple cashbook template important for small businesses is Petty Cashbook. Petty cash is used to maintain all the daily expenses to fit daily business needs.

Daily expenses are printing and stationery, postage and courier, repair and maintenance, and office expenses.

We can see slightly different columns compared to the previous cash book ledger.

Accounting Template Example 1-1

You are free to use this image o your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Accounting Templates in Excel (wallstreetmojo.com)

Remember, we need to enter all outflow transaction amounts in the “Dr” column. In the “Cr” column, we need to enter all the inflow transactions.

This Excel template is unlike our cash book, where we had two parts for recording debit and credit transactions.

Likewise, we can use petty cashbook template in excel.

#3 – Accounts Payable Template

Accounts payable are all the payments companies require to payout to their vendors to receive goods and services. So, we need to enter the payee’s name, invoice date and amount, due date, and TDS percentage.

Every vendor requires different TDS percentages, so one must enter the TDS percentage based on the vendor category.

Accounting Template Example 1-2

You are free to use this image o your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Accounting Templates in Excel (wallstreetmojo.com)

Similarly, we can use accounts payable template in excel.

#4 – Accounts Receivable Template

Accounts receivable is just the opposite of accounts payable. To be precise, accounts receivable are the blood of the business because it needs money to run. Based on the funds available, the proprietor decides the accounts’ payable dates irrespective of the due date.

Meanwhile, if there is no money, how can we pay even if the due date is tomorrow?

In such cases, the accounts receivable team significantly pushes the clients to make the payment on time.

Accounting Template Example 1-3

You are free to use this image o your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Accounting Templates in Excel (wallstreetmojo.com)

However, the accounts receivable job does not stop there. First, they must create an aging schedule for their payments.

Similarly, we can use accounts receivable template in excel. Let us see the aging schedule in the below section.

#5 – Aging Schedule of Accounts Receivable

One of the thumb rules in accounts is, “The longer the accounts balance outstanding is pending, the chance of collecting them is less likely.”

Remember, we must create an aging schedule to break the total receivable amount into different time slabs.

For example, if the total receivable amount is ₹5 lakhs, then as an accountant, we need to ensure the amount one can receive in the next five days. For instance, what will be the expected amount in the next 10 days, 15 days, 20 days, 30 days? and so on.

This is aging schedule. Now, we must arrive at the aging schedule. So, consider the due date, and then, decide on the slab based on the due date.

Ageing schedule Example 1-4

You are free to use this image o your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Accounting Templates in Excel (wallstreetmojo.com)

First, to automatically arrive at the aging remarks, we need to put in a nested IF condition. Below is the formula we have put in.

=IF([@[Due Date]]-TODAY()>30,"Due in More Than 30 Days",
IF([@[Due Date]]-TODAY()>25,"Due in 25 to 30 Days",
IF([@[Due Date]]-TODAY()>20,"Due in 20 to 25 Days",
IF([@[Due Date]]-TODAY()>15,"Due in 15 to 20 Days",
IF([@[Due Date]]-TODAY()>10,"Due in 10 to 15 Days",
IF([@[Due Date]]-TODAY()>5,"Due in 5 to 10 Days",
IF([@[Due Date]]-TODAY()>0,"Due in 1 to 5 Days",
IF([@[Due Date]]-TODAY()=0,"Due Today","Beyond Due Date"))))))))

Since we have a table format, we cannot see cell references. Instead, it displays the due date column header. For example: –

=IF([@[Due Date]]-TODAY()>30, in this @[@[Due Date]]- cell H2.

Now, apply the Pivot table to view the summary.

Ageing pivot table Example 1-5

Like this, we can perform an aging analysis to anticipate payment inflows at different times.

Important Things To Note

  • Accounting Templates in excel is a type of pre-designed template which is used to perform accounting calculations and finance.
  • Accounts payable templates and accounts receivable templates are opposite with respect to usage.

Frequently Asked Questions (FAQs)

1. What are accounting templates in excel?

Accounting template is a pre-built template used to insert accounting or finance related data in excel. Similar to accounting template, there are other templates in excel which we can design based on the niche.

2. What are the common accounting templates in excel?

The commonly used accounting templates in excel are:

Accounts Payable Excel Template.
Accounts Receivable Excel Template.
Cash Book Excel Template.
Invoice Excel Templates.
Expense Report Excel Template.
Petty Cash Book Excel Template.
Inventory Management Excel Template.
Billing Statement Excel Template.

3. What is the use of accounting templates in excel?

Templates are pre-designed sheets that provides formatted layouts. It saves time as the sheets are already created with basic elements. In case of accounting templates, we can use it to do calculations in balance sheets or to keep track of expenses.

This article is a guide to Accounting Templates in Excel. We discuss the top 5 Accounting templates Excel worksheets, including cashbook, petty cashbook, etc. You may learn more about Excel from the following articles: –