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. The third column shows the discount received or paid.

How to Create a Three-Column Cash Book in Excel: Step-by-Step Procedures

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 the Three-Column Cash Book Table

Make two sections named Debit and Credit in the table.
Below that, write down the column headers Date, Particulars, Discount Allowed, LF (Ledger Folio), Cash, and Bank in each section.

Insert multiple rows for transactions of different dates.

Step 2 – Writing the Transactions

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).

The first transaction is the company’s previous year’s leftover balance and bank overdraft.

Enter the residue balance as Balance Brought Down from the previous year in the Debit section and the bank overdraft 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. Company X returned the rest of the money in check with a 2.5% discount from company Y.

We will credit the money under the Bank column with a discount of $125.

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 Cash Transactions

To calculate the total cash amount, select the F13 cell and insert the following formula:

=SUM(F6:F12)

We will get the total debit cash balance. This will be the same for the credit cash balance as well.

Step 4 – Calculating the 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). 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 order to calculate the Balance Carried Down, select the L12 cell and insert the following formula:

=F13-L8

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

Select the M13 cell and insert the following formula:

=SUM(M6:M12)

We will get the total credit bank balance. This will be the same for the debit bank balance as well.

Step 6 – Calculating the 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.

To calculate the bank Balance Carried Down, select the L12 cell and insert the following formula:

=M13-G9-G10

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.