WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Accounting Templates in Excel

Accounting Templates in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Top 5 Accounting Templates in Excel Worksheets

Accounts Payable, Accounts Receivable, Cash Book, Petty Cashbook these are the simple accounting tools you need. In this article, we will show you how to create those accounting templates with excel worksheets. If you are an entrepreneur and not able to buy sophisticated software to manage your business accounts, then we will help you out with simple templates to track your expenses and income.

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 important ledgers in accounting. Cashbook is used to record daily transactions in the company. We can see two kinds to transactions here one is debit transactions i.e., an outflow of cash, and another one is credit transactions i.e., the inflow of cash.

On one side of the account, we will record all the debit transactions, and on the other side of the ledger, we will record all the credit transactions. All the transactions should be recorded in chronological order.

Accounting Template Example 1

For both debit & credit transactions, we can see three common columns. First, we need to enter the date of the transaction, then we need to enter the detail of the transaction, and the final part is what the amount of the transaction is.

Then we will get the Debit total and Credit total. So, in cell D14, we have a total balance available i.e., Credit Total – Debit Total.

#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 in daily business needs.

Daily expenses are like “Printing & Stationery, Postage & Courier, Repair & Maintenance, and Office Expenses.”

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

For this, we will see slightly different columns compared to the previous Cash Book Ledger.

Accounting Template Example 1-1

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

This excel template is unlike our Cash Book, where we had two different half to record debit and credit transactions.

#3 – Accounts Payable Template

Accounts Payable is nothing but all the payments company required to payout to their vendors for receiving goods and services. For this, we need to enter Payee Name, Invoice Date, Invoice Amount, Due Date, and TDS Percentage.

Every vendor requires different TDS percentages, so you need to enter the TDS percentage based on the vendor category.

Accounting Template Example 1-2

#4 – Accounts Receivable Template

Accounts Receivable is just the opposite of Accounts Payable. AR is the blood of the business because you need money to run your business, and based on the funds available; the proprietor decides the Accounts Payable dates irrespective of the due date.

If there is money, then how you do pay even if the due date is tomorrow, and that is where the Accounts Receivable team plays a major role in pushing the clients to make the payment on time.

Accounting Template Example 1-3

The accounts receivable job doesn’t stop there; they need to create the aging schedule of their payments; we will 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.”

Keeping that in mind, we need to create an aging schedule breakup the total receivable amount into different time slabs.

For an if the total receivable amount is 5 Lakh, then as an accountant, you need to be sure what is the amount is going to come in the next five days, what is the amount is going to come in next 10 days, 15 days, 20 days, 30 days, and so on.

It is called an aging schedule. We need to arrive on the aging schedule; we need to consider the due date; based on the due date, we need to decide the slab.

Ageing schedule Example 1-4

To automatically arrive, the Ageing remarks, we need to put in nested if condition. Below is the formula I 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 I have a table format, we cannot see cell references; instead, it says that due date column header. For example

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

Apply the Pivot table to see the summary.

Ageing pivot table Example 1-5

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

Recommended Articles

This has been a guide to Accounting Templates in Excel. Here we discuss the top 5 examples of Accounting templates in excel, which include 1) Cashbook Template, 2)Petty Cashbook Template, etc. You may learn more about excel from the following articles –

  • Inventory Excel Template
  • Create Income Statement Template
  • Create Profit and Loss Statement Template
  • Personal Budget Excel Template
6 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Excel Accounting Templates

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More