Accounting Templates 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.

#1 – Cashbook Template

Below are the various accounting worksheet templates in Excel.

Cashbook is one of the critical ledgers in accounting. Cashbook 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.

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.

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.

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.

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.

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(]-TODAY()>30,"Due in More Than 30 Days", IF(]-TODAY()>25,"Due in 25 to 30 Days", IF(]-TODAY()>20,"Due in 20 to 25 Days", IF(]-TODAY()>15,"Due in 15 to 20 Days", IF(]-TODAY()>10,"Due in 10 to 15 Days", IF(]-TODAY()>5,"Due in 5 to 10 Days", IF(]-TODAY()>0,"Due in 1 to 5 Days", IF(]-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(]-TODAY()>30, in this @]- cell H2.

Now, apply the Pivot table to view the summary.

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

• 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.