The three-column cash book is a very powerful financial tool. It allows a company to keep track of its transaction for a certain period efficiently. It is a handy way to represent the transactions as well. In this article, we will show how to create three column cash book in Excel.
What Is a Three Column Cash Book?
A three-column cash book is a table with Debit and Credit on each side and each side has three columns. The first column represents the cash transaction (received/paid). The second column contains the transactions into or from the bank account. Finally, the third column shows how much discount is received or paid.
How to Create Three Column Cash Book in Excel: Step-by-Step Procedures
In this article, we will discuss how to create a three-column cash book in Excel with detailed steps. We will use a transaction table to enter data into the columns of the cash book. Then we will use the data to calculate the cash or bank transactions. This is the table of transactions for Company X for the month of January 2022.
Step 1: Creating Three Column Cash Book Table
In the first step, we will create the structure of the cash book. We will include the necessary elements one by one to make a whole cash book. Follow the steps below to do so.
- To begin with, make two sections named Debit and Credit in the table.
- Then, below that write down the column header named Date, Particulars, Discount Allowed, LF (Ledger Folio), Cash, and Bank in each section.
- After that, expand the table by inserting multiple rows for transactions of different dates.
Read More: How to Format Double Column Cash Book in Excel
Step 2: Writing Transactions
In this second step, we will transfer the transaction outlined in the Transaction Details table to the cashbook one by one according to their type (debit/credit or cash/bank). Follow the ensuing steps.
- The first transaction is the company’s previous year’s leftover balance and bank overdraft.
- So, we will enter the residue balance as Balance Brought Down from the previous year in the Debit section and the bank overdraft will be added in the Credit section under the Bank column.
- The next transaction is a sale of $680.
- We will add that in the Cash column of the Debit section.
- On 18th January, the company paid cash of $650 bill to the bank account.
- We will enter it under Cash in the Credit section.
- Company Z bought goods worth $770 from company X on 11th January on credit. They paid it in check on 21 January.
- We will include it under the Credit in the Bank column.
- On 3 January Company X bought goods worth $5200 from company Y on credit. Then, company X returned $200 on 11 January. Finally, company X returned the rest of the money in check with a 2.5% discount from company Y.
- So, we will credit the money under the Bank column with a discount of $125.
- Finally, company X received a cash amount of $4900 from Mr. Adams. Although he had to pay $5000, he got a $100 discount from company X. This is also the last transaction of the month.
- This will be debited under the Cash column with a $100 discount.
Step 3: Adding Cashes
In this stage, we will make the sum of all the cash transactions both on the debit side as well as credit side using the SUM function. Follow the incoming steps.
- In order to calculate the total cash amount, select the F13 cell and type the following formula,
=SUM(F6:F12)
- As a result, we will get the total debit cash balance. This will be the same for the credit cash balance as well. Since debit and credit needed to be equal.
Step 4: Calculating Cash Balance Carried Down and Balance Brought Down
The financial balance of a real estate or personal ledger account that was carried over to the following accounting period is known as the Balance Carried Down (Bal c/d). In other words, this represents the ledger account’s closing balance. The surplus money obtained by deducting the smaller CR totals from the larger DR totals of a certain ledger account is known as the Balance Brought Down (Bal b/d). In this step, we will calculate the balance brought down and the balance carried down for the month.
- In order to calculate the Balance Carried Down select the L12 cell and write down the following formula,
=F13-L8
- This is simply the difference between debited cash and credited cash.
- As a result, we will get the cash Balance Carried Down for the month. This will be the Balance Brought Down for the month of February as no other transaction is done after that.
Step 5: Adding Bank Amounts
In this step, we will add the amounts that are stored in bank accounts in that period. We will sum up bank transactions both from the credit and debit sides. Follow the steps outlined below.
- In order to calculate the total bank amount, select the M13 cell and type the following formula,
=SUM(M6:M12)
- Consequently, we will get the total credit bank balance. This will be the same for the debit bank balance as well. Since debit and credit needed to be equal.
Step 6: Calculating Bank Balance Carried Down and Balance Brought Down
The Balance Brought Down and Balance Carried Down for bank accounts are the same as the cash amounts. Here, we will calculate the balance brought down and the balance carried down for the bank transactions.
- In order to calculate the bank Balance Carried Down select the L12 cell and write down the following formula,
=M13-G9-G10
- This is simply the difference between the credited bank balance and debited bank balance.
- As a result, we will get the bank Balance Credit Down for the month. This will be the bank Balance Brought Down for the month of February as no other transaction is done after that.
Download Practice Workbook
You can download the practice book here.
Three Column Cashbook.xlsx
Conclusion
In this article, we have discussed how to create a three-column cash book in Excel in an exhaustive way. This is a simple cash book. However, new users can use it as a basic building block to create a sophisticated three-column cash book.
Related Articles
- How to Create Daily Cash Book Format in Excel
<< Go Back toExcel Cash Book Templates|Accounting Templates|Excel Templates
Get FREE Advanced Excel Exercises with Solutions!