Have you ever been asked to design a Cashbook system in Excel? Back at school, this was a group assignment of which I was part. More interestingly, but unfortunately, I was asked to lead the group. Since I could type, they thought they were lucky to have me in that group. By the way, that was a foolish thought.
The assignment demanded that we convert a manual Cashbook system into a computerized system using Excel. It was a total failure!
As the one leading the group, I knew very well what was expected of us. But I couldn’t think of where to begin. The whole group got lost. And instead of creating a system that could recalculate as new information was added, we created a very dumb looking static system that has values only.
Well, we did exhibit our design skills by polishing our Cashbook with table styles and colors. But still, the system was useless.
I wish I had found this information back then. I could have made my group proud.
Unfortunately, it’s too late for my group. But not too late for me. And for you, it’s just the beginning. I want you to make your friends proud with this Excel Cashbook tutorial.
Let’s start from the ground up
You may know this already. But for the purpose of those who may lack the knowledge, or might have forgotten. I’ll just throw some words about what a Cashbook is before we get started with creating our Cashbook system with Excel.
A Cashbook is a Book of original entry (which means that it is the first place cash transactions are listed) and it is used to record all cash receipt and cash payments. At every beginning of the period, the Cashbook starts with cash or bank balance. But in case of a new business, of course, there won’t be any cash or bank balance to start with. A Cashbook is prepared by all organizations and is therefore very important in the accounting cycle.
How do you create a Cashbook in Excel?
Now the question is: How do you create a Cashbook system with Excel spreadsheet? That’s the main goal of this tutorials. This is a step by step guide to help you design your own Cashbook using Microsoft Excel.
There are different types of Cashbooks in Accounting. In this modern era, banking habit is now widespread and is so convenient and safe that a large number of payments are made and received through cheques. This made the Cashbook more commodious and flexible when the bank column in addition to the cash and discount columns are all present.
Thus, I am going to teach you how to design a triple column Cashbook as it is the most exhaustive form of Cashbook. It takes three money columns on both the receipt (Debit) and payment (Credit) sides to record transactions involving cash, bank, and discount.
Remember: You can decide to remove the columns that don’t fit for you or your business.
Cashbook format in Excel
We know what a Cashbook is and the type of Cashbook we want to create. Now let’s understand the format of our Cashbook.
Without wasting much time, below is the Cashbook format in excel worksheet that I am going to use for this tutorials.
As already mentioned, a Cashbook is made up of receipt and payment sides. And here, I am going to separate both the receipt and the payment sides in separate sheets for the purpose of clarity. (See screenshots)
Receipt side of the Cashbook:
Payment side of the Cashbook:
Above are screenshots of the finished and complete version of the Cashbook with transactions recorded. As I said already, the debit and credit sides are in a separate worksheet. Below is how the Cashbook format looks like on a manual Cashbook with both the Dr. and Cr. sides on one sheet.
Cashbook format interpretation
It very important for me to explain the column headers of the Cashbook, this is to ensure that you and I understand what we are doing.
The Date column is used to record year, month and date that a transaction took place. Dates are recorded in chronological order.
The GCS column is used to specify the type of account. It is safe to say that the G is for General Ledger, C for Customers and the S for Suppliers. You can label it as Type instead of GCB. Don’t worry if you don’t understand this now, I promise, you’ll get it soon when we start creating the system.
Particulars Column is the column where the name of the account in respect of which cash has been received or payment has been made is recorded. Thus, accounts pertaining to the receipts of cash is debited in the receipt worksheet, and the account pertaining to cash payments is credited in the payment worksheet.
Contra Column is used to record entries in which the same account can be debited and credited in a contrary situation. That’s when a transaction relates to both cash and bank, it will be recorded on one side of the Cash column and on the other side of the Bank column.
For e.g. Cash was withdrawn from the bank for business use: the amount is recorded on the debit side of the Cash column, and at the same time on the credit side of the Bank column. Contra entry is recorded in the journal by marking C in the Ledger Folio (L.F) column on both sides against the entries in question.
Gross Amount: This column is used to record the total amount received or paid on debit or credit side respectively. It is gross because it is the amount after any deduction of discount if any.
Discount Rate is an amount or percentage deducted from the selling price of something.
Receipt Method is the method of payment used for a transaction. Whether the transaction was done by cash or bank (check).
The Bank Column records transactions relating to the bank. Such transactions are common among organizations, deeming it necessary to have a separate book to record bank transactions. But instead of dedicating a separate book for only bank transactions, a column is added to each side of the Cashbook.
There are certain business transactions which need special treatment in the bank column. Such transactions include Opening balance, receipt of Banks, contra entries, endorsement of Banks and also bank charges.
The Cash Column records transaction related to receipt and payments in terms of cash.
Discount allowed records the discount granted to customers. This is calculated by multiplying the discount rate by the gross amount. The Discount allowed column is on the receipt side of the Cashbook.
Discount Received is the reverse situation as discount allowed. It records the discount granted by suppliers and it is on the payment side of the Cashbook.
Setting Up the Cashbook in Excel
Enough with the explanation. Let’s get started with the real job.
This Cashbook will require three worksheets. So make sure you have three sheets in your workbook. Rename the first sheet as PARTICULARS, the second sheet as RECEIPT, and the last sheet as PAYMENT. Just to increase understanding, I want us to work out each sheet one after the other until the end. (See screenshot below)
Part 1 of 4: Working on PARTICULARS
The PARTICULARS worksheet is going to contain the list of items to be entered under the Particulars column of the Cashbook. These items may include Customers, Suppliers, General Ledger, and Opening Balance.
Let’s assume that you are designing this Cashbook for your own business. This means that you know who your suppliers are, who your customers are and you also know what products or services you offer. This particulars worksheet is going to contain every single detail of this information about your business. This is an approach to making an easy to use Cashbook that is free of errors.
Below is a screenshot of the PARTICULARS worksheet:
Below table also shows the items in the particulars worksheet:
|TYPE||CUSTOMERS||SUPPLIERS||GENERAL LEDGER||OPENING BALANCE|
|CS||A. Anthony||M. Samantha||Capital||Balance b/d|
|SS||J. Natalie||K. Sofia||Motor Van|
|GL||D. Elijah||A. Ryan||Rent|
Just enter the above information into the PARTICULARS worksheet. You can use your own data though, but for now, I’ll advise you not to. Work with this data to get the concept first before doing anything of your own. After the system is complete, you can change the customers, suppliers, and even items in the general ledger that will update itself automatically in the receipt and payment worksheet. So the particulars details shouldn’t be an issue at all.
NOTE: The column named as TYPE is there to mean “Type of Account” where CS stands for customers, SS stands for suppliers, GL for General Ledger, and OB for Opening Balance. We are going to use this TYPE column to set up a conditional drop-down list so that when SS is selected, only suppliers’ accounts would be displayed under the particulars side of our Cashbook. And the same goes for customers’ accounts and the general ledger accounts.
Also, make sure the arrangement of the items are in order because that will determine the accuracy of your Cashbook system. For e.g. CS is the first item under the TYPE column which is a unique name for customers. This means that the next column after TYPE should be customers (See screenshot)
Defining cell names
This idea of defining cell names will help us avoid typing cell references. So instead of typing a cell reference like A2:A20, we give it a name as Type. As you can easily forget or mistype a cell reference, it’s hard for you to forget or mistype a name, especially if it is a good name for the range.
The named range is required to easily set a conditional drop down validation list. Therefore, from the Particulars sheet, create the following named ranges.
- Name cells A2:A10 as type.
- Name Cells B2:B20 as CS.
- Name Cells D2:D20 as SS.
- Name Cells F2:F20 as GL
- Name Cell H2 as OB
Remember: You can set the name ranges to as many rows as your particulars can accommodate. Though, it’ll be a good practice to give more room for new data.
If you don’t know how to name a cell or range of cells, here’s how:
- Select the range of cells you want to name (say A2:A10). You can name a single cell or an entire range of cells.
- Go to FORMULAS→Defined Names→Define name
- In the Name box, enter a descriptive name, e.g. Type.
- In the Scope box select workbook
- Then click OK (see screenshot)
QUICK TIP: There’s a quick and smart way to use the Name Box. The name box is just above the A column – it shows the address of the active cell or cells. So to name a cell or range of cells using the name box, just select the range you want to name, click and type the name in the Name Box and press Enter. You can also use the Name Box to see all the named ranges in your workbook. (See screenshot)
After this stage of naming cells, we are done tweaking and glossing the particulars worksheet. Let’s move on to the next: the receipt sheet.
Step 2 of 4: The receipt side of the Cashbook
Design the receipt side of your Cashbook using a table. To do this, please lay out the sheet as shown below. Type the headings as seen in row 3 and those in row 17. (See screenshot)
Bonus: You can also download this workbook that contains no formulas so that you can continue from there. Click Here to Download.
After laying out your receipt sheet as this one, let’s then format and insert our functions in the columns where necessary.
NOTE: when laying out your receipt sheet, you can stick to only two or few rows including the header row. The table would automatically be extended to the next row(s) when you start recording entries.
The GCS column
Under this column, we will create a dropdown list, which when clicked, items under the TYPE column in the particulars worksheet will show.
To create a dropdown list for such effect, just follow these steps:
- Select the first cell under the GCS column (i.e. cell B4)
- Go to DATA→Data Tools→Data Validation
- From the settings tab, click the Allow drop-down and select list. Leave the Ignore blank and the In-Cell drop-down options checked.
- Enter =TYPE in the Source Box and click OK.
That is all for the GCS column. On to the next.
The particulars column
We are going to create an automatic dropdown list under the particulars column, and this list will depend on the type of accounts selected under the GCS column.
This will ensure that before an entry is processed (e.g. for a supplier), the SS item under the GCS column have to be selected before the list of suppliers could be found under the particulars column drop-down, to be able to select any supplier in question.
To create such a list, the following steps would guide you through:
- Select cell C4, the first empty cell below the Particulars column.
- Navigate to DATA→Data Tools→Data Validation
- From the settings tab, in the Allow drop-down, select List. Leave the Ignore blank and the In-Cell drop-down options checked.
- In the Source Box, enter =Indirect(B4). Note that cell B4 is not a constant cell, so you’ll want to make it relative. The B refers to the column where we have GCS as heading, and the 4 refers to the row we are working on.
- Click OK to complete the list. You may get an error message that says “The source currently evaluates to an error. Do you want to continue?” Ignore the error and just click Yes.
- Now if you have several rows under your headings, you’ll want to copy this drop-down to the rest of the cells by selecting the first cell under Particulars and dragging down the Fill Handle until the end of the table. This will copy the formula into all the cells you drag to cover.
The Contra Entry Column
As mentioned already, contra Entry arises when a transaction needs to be recorded into the cash and the bank accounts simultaneously. I think this concept has been fully understood when I explained the Cashbook format. For e.g. recording a transaction that says Cash paid into the bank and Cash withdrew from the bank for business use, both transactions relates to cash and bank.
You already know that for this kind of transactions, C is usually written under the folio column in the manual Cashbook. Well, not anymore, because this is now a computerized Cashbook and such transactions need a formula that can automate the entry. Such that when a transaction is a contra entry, the system will automatically enter the C letter in the Contra column of that transaction.
To achieve this kind of result, enter the following formula in the first cell of the Contra column (D4).
To check the result, select Cash or Bank in the dropdown list under the particulars column. You’ll notice that C is displayed for that record under the Contra column (see screenshot).
TIP: If you also want people to call you Excel Guru, this is your call. Make sure you study this nested IF statement and understand how and why it is so. It’ll add a great deal to your understanding of Excel logical functions.
The Gross Amount Column
In this Excel Cashbook System, the Gross Amount column is there to take records of all receipt and payment. There’s no need for any formula in the Gross Amount column because this column is going to be entered manually.
However, there will be formulas in the Cash and Bank column that will decide whether to place the amount into the Cash or Bank column base on the receipt method selected. You’ll understand this well when get to creating the formulas for the Cash and Bank columns.
Just like the Gross Amount column, there’s no need for a formula in this column too. Excel doesn’t know the discount rate your suppliers offered you or the rate you offered to your customers. This means that the discount rate must be entered manually.
Meanwhile, there’s a need to format this column to contain figures in percentages format. Any discount the business allowed to a customer is entered herein and left to the system to compute the discount amount and with the aid of a formula, the amount is automatically recorded into the Discount Allowed column.
To format the Discount Rate column as percentage figures, follow below steps:
- Select the first cell under the Discount Rate
- Go to HOME→Number→Percentage
The Receipt Method Column
In this column is going to contain a dropdown list of Cash and Bank. This dropdown will be used to specify whether the customer made payment by Cash or by Cheque. It is a very important part of the whole system. It depends on its accuracy to give you accurate figures of Bank and Cash balances.
To create a drop-down for the Receipt Method:
- Select the first cell in the Receipt Method
- Go to DataData→Tools→Data Validation.
- Click the Allow drop-down and select List.
- In the Source box, type Cash, Cheque (Must be separated with a comma)
- Click OK
The Cash Column
The allocation of the amount to the cash column is to be fully automated with a formula such that, the amount will be recorded to the cash column if the transaction is a cash transaction.
The formula for Cash Column:
It is imperative to know that the cash amount is allocated base on two solid conditions:
- When a transaction is a contra entry: this means that the particulars column of the receipt side of the Cashbook should record Bank (Remember your double entry principles). And when that happens, the whole amount (Gross Amount) received must be automatically recorded at the Cash column. In simple terms, this means that with the contra entry, we are receiving cash from our own bank account. So the effects will be that we credit Cash and debit Bank. You’ll agree with me that the receipt side of the Cashbook is the credit side right? And we are on the receipt worksheet, hence the amount must be recorded in the CASH column in the name of Bank under the particulars column. This is shown in the formula as =IF(C4=”BANK”,E4
- When the Receipt Method is Cash: Obviously! This means that if the user of the system selects Cash as the method of payment under the Receipt Method, then the amount must be recorded under the cash column of the Cashbook. Meanwhile, any available discount shall be deducted. So if there’s a discount on cash payment don’t expect an equal amount in the Gross Amount and the Cash column. This is the part of the formula that makes this happens: IF(G4=”CASH”,E4-J4,””).
The Bank Column
The formula for the bank column is almost the same as that of the cash column, except that we need to make some switching. Just like the Cash column, the allocation of the amount to the bank column is to be fully automated with a formula such that, the amount will be recorded to the Bank column if the transaction is a Bank transaction.
The formula for Bank Column:
Compare this formula to the formula in the cash account column and you’ll notice that they are almost the same.
It is imperative to know that the bank amount is also allocated base on two solid conditions:
- When a transaction is a contra entry: this means that the particulars column of the receipt side of the Cashbook should record Cash (once again, remember your double entry rules). And when that happens, the whole amount (Gross Amount) received must be automatically recorded at the Bank column. In simple terms, this means that with this contra entry, money is going into our bank account right from our own cash. Therefore, the effects will be that we credit Bank and debit Cash. This is shown in the formula as =IF(C4=”CASH”,E4
- When the Receipt Method is Cash: Obviously! This means that if the user of the system selects Bank as the method of payment under the Receipt Method column, then the amount must be recorded under the Bank column of the Cashbook. Meanwhile, any available discount shall be deducted. Thus, if there’s a discount on a bank payment, don’t expect an equal amount in the Gross Amount and the Cash column. This is the part of the formula that makes this happens: IF(G4=”BANK”,E4-J4,””).
Remember: Discount does not affect the contra entry. Also, in a situation where there is no discount, then the Discount Allowed column would be nil or zero and Zero from the Gross Amount have no effect.
The Discount Allowed Column
Guess what formula we are putting in this column. Just a formula to compute the available discount on the amount received or paid. As indicated in the Cash and Bank columns formulas, the result of the discount allowed is then deducted from the Gross Amount.
The formula for Discount Allowed column:
=IF(F4>0,F4*E4,0) Or simply use this: =F4*E4
And well, that’s all for the receipt worksheet.
Step 3 of 4: The Payment side of the Cashbook
We have almost exhausted everything about this Excel Cashbook system. All there is to do under the payment worksheet of the Cashbook system have been done already under the receipt worksheet. So we are going to copy and paste everything from the receipt worksheet to the payment worksheet and make some tinny adjustments.
As shown below, select and copy the entire receipt sheet and past onto the payment sheet (see screenshot).
After you copy and paste the receipt sheet onto the payment sheet, make sure to change the receipt method to payment method, and discount allowed to discount received. The payment side of the Cashbook is complete after completing this step.
Step 4 of 4: Calculating the Closing Balances
Finally, we are going to generate formulas that will return the closing balances of our Cashbook. You know, at the end of each accounting period, after closing the books, there always remain some closing balances. These balances will then serve as the opening balance for the next account period. Our Excel Cashbook too needs those balances, but then, a fully automated one, as always.
Below are the formulas to calculate the closing balances.
The formula to calculate the closing balance for Cash:
The formula to calculate the closing balance for Bank:
Remember: The Cash and Bank balances on the receipt side of the Cashbook are the same as the one on the payment side. Therefore, if you want to display these balances on both sides of your Cashbook, just use the same formulas.
The formula to calculate the closing balance for Discount Allowed:
The formula to calculate the closing balance for Discount Received:
Remember: Unlike the Cash and Bank Balances, where we have to deduct the balances on the payment side from that on the receipt side, no such gesture is needed for the discount column since discounts are completely different on both sides.
Time to record transactions
At this point, the system is now ready for use. You can download the complete Cashbook here so that you can compare.
Use the Cashbook you just created or downloaded and enter the following transactions. If a transaction needs to be entered on the receipt side, use the receipt worksheet. And if a transaction needs to be entered on the payment side, use the payment worksheet.
|1st||Started a business with bank||700,000|
|2nd||Bought Motor Van and issued a cheque for payment||230,000|
|3rd||Withdrew cash from the bank for business use||100,000|
|4th||Bought goods with cash||50,000|
|6th||We paid the following their accounts by cheque less 10% discount:
|8th||The following paid us their accounts by cash, less 5% discount:
|12th||Paid rent by cash||6,000|
|14th||Withdrawals from the bank for personal use||10,000|
|17th||Cash sales paid directly into bank||75,000|
|18th||Withdrew cash from bank for business use||30,000|
|24th||Paid for insurance||8,500|
|25th||Cash deposited at bank||90,000|
|30th||Bought furniture and issued a cheque for payment||15,000|
After entering the above transactions, your closing balances should tally with this:
|$ 60,000.00||$ 381,500.00||$ 4,000.00|
Should you have any questions, just drop them in the comments below.