Top 5 Accounting Templates in Excel Worksheets

You need simple accounting tools for accounts payable, accounts receivable, cash book, and petty cash book. 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.

Below are the various accounting worksheet templates in Excel.

#1 – Cashbook Template

Cashbook is one of the critical ledgers in accountingLedgers In AccountingLedger in accounting records and processes a firm’s financial data, taken from journal entries. This becomes an important financial record for future reference. It is used for creating financial statements. It is also known as the second book of entry.read more. CashbookCashbookThe Cash Book is the book that records all cash receipts and payments, including funds deposited in the bank and funds withdrawn from the bank according to the transaction date. All the transaction which is recorded in the cash book has the two sides i.e., debit and credit.read more is used to record daily transactions in the company. 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 Debit Debit represents either an increase in a company’s expenses or a decline in its revenue. read more transactions on one side of the account. We may record all the credit transactions on the other side of the ledger. All the transactions should be recorded in chronological order.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor 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 are required to enter the transaction details, and the final part is to 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.

#2 – Petty Cashbook Template

Another simple cashbook template important for small businesses is “Petty CashbookPetty CashbookA petty cash book is a ledger book used to record small amounts of daily cash expenses in chronological order. It is a manual record-keeping of the monetary payments for everyday expenses and facilitates tracking in-hand cash.read more.” Petty cashPetty CashPetty cash means the small amount that is allocated for the purpose of day to day operations. It is unreasonable to issue a check for such small expenses and for managing the same custodians are appointed by the company.read more 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.

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.

#3 – Accounts Payable Template

Accounts payableAccounts PayableAccounts payable is the amount due by a business to its suppliers or vendors for the purchase of products or services. It is categorized as current liabilities on the balance sheet and must be satisfied within an accounting period.read more is all the payments companies require to payout to their vendors to receive goods and services. 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.

#4 – Accounts Receivable Template

Accounts receivableAccounts ReceivableAccounts receivables is the money owed to a business by clients for which the business has given services or delivered a product but has not yet collected payment. They are categorized as current assets on the balance sheet as the payments expected within a year. read more is just the opposite of accounts payable. 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.

If there is no money, how to 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.

The accounts receivable job does not stop there. First, they must create an aging schedule for their payments. Let us see the aging schedule in the below section.

#5 – Aging Schedule of Accounts Receivable

One of the thumb rules in accountsRules In AccountsAccounting rules are guidelines to follow for registering daily transactions in the entity book through the double-entry system. Here, every transaction must have at least 2 accounts (same amount), with one being debited & the other being credited. read more 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, ensure how much one can receive in the next five days. What amount can be obtained in the next 10 days, 15 days, 20 days, 30 days? and so on?

It is called an aging schedule. We must arrive at the aging schedule, consider the due date, and decide on the slab based on the due date.

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 referencesCell ReferencesCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more. Instead, it displays the due date column header. For example: –

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

Apply the Pivot tablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more to view the summary.

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

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: –

  • Inventory Excel TemplateCreate Income Statement TemplateCreate Profit and Loss Statement TemplatePersonal Budget Excel Template