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.
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.
Read More: How to Create Petty Cash Book Format in Excel
How to Create Daily Cash Book Format in Excel: with Easy Steps
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.
Read More: How to Create Three Column Cash Book in Excel
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,
=SUM(D6:D14)- 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.
=SUM(H6:H14)- 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.
=D16-D17- 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.
=H16+H17- 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.
Read More: How to Format Double Column Cash Book in Excel
Bottom Line
👉 #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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
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.
<< Go Back to Excel Cash Book Templates | Accounting Templates | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!
what is a faxed Balance
Hello, SAJNA!
Thanks for your query!
I think you want to know about the fixed balance instead of faxed balance.
In a daily cash book, a fixed balance refers to a predetermined amount of money that remains constant throughout a specific period, such as a day. This fixed balance represents the starting cash on hand at the beginning of the day and is not affected by daily transactions. It is typically used to track and reconcile cash inflows and outflows accurately.
For example, let’s say a company starts the day with $1,000 in its cash register. This initial amount of $1,000 would be recorded as the fixed balance in the daily cash book. Throughout the day, various transactions occur, such as cash sales, cash expenses, and withdrawals. These transactions are recorded in the cash book, and the fixed balance is adjusted accordingly.
At the end of the day, let’s assume the company made $700 in cash sales, paid $200 in cash expenses, and withdrew $300 for petty cash. Despite these transactions, the fixed balance in the cash book would still remain $1,000. The final balance in the cash book would be calculated by adding or subtracting the daily transactions from the fixed balance. In this example, the final balance would be $1,000 + $700 – $200 – $300 = $1,200.
The fixed balance in the daily cash book helps ensure that the cash position is accurately tracked, and any discrepancies can be easily identified during the reconciliation process.
Regards
Md. Abdur Rahim Rasel (Exceldemy Team)