A Cash Book format in Excel is a straightforward layout created to keep track of all daily cash transactions carried out by a business. This Excel cash book functions as a ledger account for recording the inflow and outflow of cash from the business. Today, in this article, we’ll learn four quick and suitable steps to create a daily cash book format in Excel effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Introduction to Daily Cash Book
A daily Cash Book is an accounting journal used to track all cash payments and receipts, including bank deposits and withdrawals.
The Cash Book is a subsidiary accounting ledger used to track cash transactions, to put it simply. The general ledger is updated by the accountant with the cash book entries.
Transactions are recorded by the cashier or accountant in chronological order by date. This makes the period’s end reconciliation simple.
Disbursement Journal and Receipt Journal are the two distinct journals used by larger businesses with a higher volume of transactions to track their cash transactions.
In addition, the disbursement diary keeps track of all cash payments, while the receipt journal keeps track of all cash receipts. On the debit side, it keeps track of every receipt, and on the credit side, every payment.
A cash book will also always have a negative balance. You are unable to pay more than you have, which is the cause. The number of transactions will determine how long it takes to reconcile.
Small firms typically reconcile twice a month or every two weeks. While larger companies that handle more transactions reconcile daily.
There are four types of daily cash books. They are:
i. Single Column Cash Book
A single-column cash book is a record of a business’s inward and outward cash transactions that excludes discounts and taxes. There are two sides to it: credit and debit. A simple cash book is another name for it.
ii. Double Column Cash Book with Discounts
Some companies are required to provide customers discounts when paying with cash. As a result, this kind of firm keeps a Cash Book with Discounts. It has a separate column for discounts earned or paid.
iii. Double Column Cash Book with Tax
It has a debit and credit side as well as an additional column for tax, just like a normal cash book. Either GST (Goods and Services Tax) or VAT may apply to this (Value Added Tax). You can add any additional taxes that are legal in your location in accordance with your needs.
iv. Triple Column Cash Book with Tax and Discounts
For people whose everyday cash transactions involve providing and receiving discounts as well as collecting or paying taxes, Cash Book with Discounts and VAT is designed. For discounts and taxes, there are additional columns in this kind of book.
4 Easy Steps to Create Daily Cash Book Format in Excel
Let’s say, we have a dataset that contains information about the Daily Cash Book of the XYZ group. From our dataset, we will create a daily cash book format. We can easily create a daily cash book format in Excel by using the SUM function. Here’s an overview of the dataset for our today’s task.
Step 1: Create Daily Cash Book Header
In this portion, we will enter the heading of a cash book such as Daily Cash Book Format, Cash Inward, Cash Outward, Transaction date, Transaction Details, Transaction Amount, etc.
Step 2: Make Cash Inflow Format
Now, in this portion, we will input the cash inward details. For instance, Transaction date, Transaction Details, and Transaction Amount. The cash inward is the income of the XYZ group. We will enter several types of Transactions in the Transaction Details column.
Step 3: Create Cash Outflow Format
Now, in this portion, we will input the cash outward details. For instance, Transaction date, Transaction Details, and Transaction Amount. The cash outward is the expenses of the XYZ group. We will enter several types of Transactions in the Transaction Details column which has been given in the below screenshot.
Step 4: Make Summary of Daily Cash Book Format
Last but not least, we will create a summary of the daily cash book using the SUM function and Mathematical formula. Let’s follow the instructions below to learn!
- First of all, select cell After that, write down the SUM function in that cell. The SUM function is,
- Hence, simply press Enter on your keyboard. As a result, you will get the total Cash Inward of the XYZ group which is the return of the SUM function. The total Cash Inward is $95,000.00.
- Similarly, write down the SUM function in cell D17.
- Hence, simply press Enter on your keyboard. As a result, you will get the total Cash Outward of the XYZ group which is the return of the SUM function. The total Cash Outward is $80,000.00.
- Further, write down the Mathematical Subtraction formula in cell D18 to calculate the Closing Balance.
- Where D16 is the total Cash Inward and D17 is the total Cash Outward.
- After that, press Enter on your keyboard. As a result, you will get the Closing Balance. The Closing Balance is $15,000.00.
- Now, write down the Mathematical formula in cell H18 to calculate the Actual Balance.
- Where H16 is the Closing Balance and H17 is the Fixed Balance.
- After that, press Enter on your keyboard. As a result, you will get the Actual Balance. The Actual Balance is $14,500.00.
- Finally, you will be able to create a daily Cash Book format.
👉 #N/A! error arises when the formula or a function in the formula fails to find the referenced data.
👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.
I hope all of the suitable methods mentioned above to create a daily cash book format will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.