Petty cash book template excel

Just before you begin reading this guide, let me inform you that this Excel Petty Cashbook tutorial is the ultimate guide.

This means that I’ll try my best to exhaust every important detail you need to know about the analytical petty cash book and how to design and create one using Excel.

But as it stands now, some may want me to spare them the details and get straight to the point.

Others too may need the details because they don’t know much about the analytical petty cash books.

Meanwhile, someone may just need to download the template and then find their way.

I’ll make it easy for all of you. Use the table of content below to jump-start wherever you find fit for you.

Download the Template

If you just want to download the template, use the download links below:

What is an analytical Petty Cashbook?

In large firms, all receipts are banked and payments are made by cheque. All transactions are cashless.

But what about the numerous small expenses like stationery, refreshments to guests, cartage, bus fare, postage, and stamps, etc. which cannot be paid through cheque?

Moreover, can the main cashier stand the frustration of making all these small as well as frequent payments?

The petty Cashbook is a book that keeps records of quick payments for miscellaneous small expenses in the business concern.

Keeping a petty Cashbook is a very smart practice for a business. It is of great value to safeguard and control cash effectively.

Generally, all transactions related to cash are recorded primarily in a journal, which requires the cash aspect of the transaction to be posted separately to the cash account, in the ledger.

This approach consumes time and labor. That’s where maintaining a separate book for recording only cash transactions becomes imperative to larger organizations. It saves time and labor.

But then the procedure can turn clumsy if petty and repetitious payments are all shouldered by the main cashier and are recorded in the main cash book.

The Cashbook may become too bulky to manage and the cashier may be loaded down and may lose focus on high-value transactions.

Therefore, all small cash payments are separately recorded in the petty cash book.

Normally in big organizations, a Petty Cashier is assigned to handle petty expenses. It may also be assigned to an existing employee who in addition to his normal duties maintains a separate Cashbook to record these petty expenses.

Petty Cash expenses work under the Imprest System, where a fixed amount say $50 is given to the petty cashier for incurring petty expenses.

This amount is called the imprest money. The Petty Cashier makes all the payment for which he is authorized out of the impress money.

At any point in time the money assigned to him is exhausted, he gets reimbursed from the main Cashier for the actual amount spent.

All these petty payments and reimbursements are recorded in an analytical petty cash book.

This system of Cashbook reduces labor, controls irregular expenses, makes quick payments possible, and also reduces the chance of the main Cashbook becoming overburdened.

Petty cash book format in excel

A petty cash book can either be a simple petty cash book or an analytical petty cash book.

The simple petty cash book has only two amount columns: receipt and payment.

The receipt column is used to record the opening balance and the amount received from the head cashier. And the payment column of a simple petty cash book records petty expenses.

An analytical petty cash book, on the other hand, is a cash book that contains only one amount column on the receipt side and a number of different columns on the payment side for the head of expenses.

It is called an analytical cash book because it analyses the expenses into a number of grouped expenses. The analytical petty cash book is simply the extension of a simple petty cash book.

Below is the format or layout of an analytical petty cashbook:

Explanation of the format details

The Receipt column is used to record the imprest amount on the debit side of the Cashbook. However, for recording receipts and payments, the column for Date, Particulars, and Voucher Number (V.N) are common.

For recording petty expenses, there’s a column on the credit side which is the Total Amount column. The totals of various expenses paid by the same voucher and on the same day are recorded at one place.

Following the Total Amount, Columns are columns dedicated to recording the heads of items that are most common in the business.

Some Heads under which expenses may be classified include Postage, Cartage, T&T, Stationary, etc. Among the analytical column, there’s also a column allotted for recording miscellaneous expenses.

How to create Petty Cash Book Template

It is now time for me to walk you through the step-by-step guide to design and create your own petty Cashbook template using Excel. Without wasting much time, let’s get started.

Step #1: Creating the worksheets

This petty Cashbook requires two worksheets to work with. So, make sure there are two worksheets in your workbook. Rename the first worksheet as FILE and the second worksheet as ANALYTICAL PETTY CASHBOOK.

To rename a worksheet, right-clicking the worksheet tab and select Rename. Or just double-click the sheet name. Either way, Excel lets you type a new name directly in the tab. (See screenshot)

Step #2: Working on the FILE sheet

At this step, on the FILE sheet, select the whole of column A and name it as Analysis. Then type all your items that will serve as your Analytical Expenses in Column A. One item for one cell.

To name column A as analysis here’s what to do:

• Select the entire column by clicking the column letter A.
• In the name box just above column A, type in Analysis
• Press enter to apply the name.

(See screenshot)

After naming column A as Analysis, type all the expenses you wish to display as your analysis expenses. (See screenshot below)

That’s all you need to do on this worksheet.

Thus, the FILE sheet is complete.

Step #3: Designing the Petty Cashbook

This is the design stage. Here, the procedure is very simple and straightforward. We rather not waste much time here. Below is a screenshot of how you should design the petty cash book.

You can save some time by downloading this petty cashbook format without formulas so that you can jump start from there. Click here to download this layout.

Let’s just say that the first five columns are constant. It’s fine ok if you don’t want some of the columns in your cashbook. And you can add as many analytical columns as you please to make room for more related expenses.

But always make sure to include the Miscellaneous/Sundry Expenses column to record payments that cannot be entered in any of the analysis columns.

I am sure everyone looking at this screenshot above will understand the layout. It has the company title marked, the receipt and payment side separated, all the analytical columns intact and it also marked the totals of each of the amount columns. It has a balance box which will be automated to give the update of how much money is left for the petty cashier to make payments with.

Always remember this:

• When creating the table, don’t worry about the totals row at the button of the table. The totals are supposed to be automatically inserted. I’ll show you how in the later part of this tutorial.
• At this point don’t also worry about the number of rows the petty cash book should have. New rows can always be added for entering new records.
• You can tweak your table to look as you please. But make sure it has all the important columns.

Step #4: Formatting & entering the formulas

Do we really need formulas in a petty cash book?

Well, if we want to automate the system to do certain tasks, then yes, formulas are needed to save the day. Things that we can automate in the petty Cashbook system include calculating the total amount of receipt, the total amount of payment, the totals of each analytical expense, and also the balance left after every transaction. With the help of formulas, we’ll also automate postings to the analytical expenses, so those columns are not going to be recorded manually.

Now let’s pick the columns one after the other to enter the formulas and formatting.

The Receipt Column (1^st column)

This is the first column of the petty cash book. It is one of the amount columns and hence needs to be formatted with currency or accounting number format. This column doesn’t need any formula.

To apply accounting number format:

• Select the receipt column by clicking the column header.
• Go to Home →Number →Number Format→Accounting.

See the screenshot below:

To learn more about the currency and accounting number format please refer to this page.

The Date column (2^nd column)

The date column also needs no formula. But it needs to be formatted with the date format.

To do so:

• Select the column header to select all cells under that column.
• Go to Home→Number→Number Format→Date

See screenshot below:

The Details column (3^rd column)

The details column need neither formatting nor formula. It is used to give a very brief description of each transaction.

The Group column (4^th column)

Under the Group column, we are going to create a dropdown list of the analysis items.

If you can remember in the FILE sheet, we created a list of analytical expenses under column A and name that entire column as Analysis.

The essence of that column in the FILE sheet is related to this one. It’ll help us automate the posting of the amount spent on each transaction to their respective expense groups.

For instance, if say the transaction is related to T&T, specifying it in the group column will automatically post its amount to the T&T column.

To make the dropdown for the Group column:

• Select the first cell under the Group column.
• Go to Data → Data Tools → Data Validation
• Under the settings tab, click Allow drop-down and select list.
• In the source Box type =Analysis then click OK.

See screenshot below:

The Total Payment column (5^th column)

This column is used to record all payments before they are posted on to their respective expense group.

The Total Payment column needs to be entered manually and does not require any formula.

However, since it is one of the amount columns, it needs to be formatted with the accounting or currency number format.

The Analytical expenses columns

These columns are dedicated for each head of expense on the credit side of the petty cash book. They are called analytical expenses. They enable transactions to be recorded and analyzed at the same time, according to the type of expenditure incurred.

Below are the formulas for each column. Enter them in the first cell under each column head and copy the formula by dragging or double-clicking the fill handle (see screenshot)

COLUMN	FORMULA
Postage	=IF(D5=$F$4,E5,””)
Cartage	=IF(D5=$G$4,E5,””)
T&T	=IF(D5=$H$4,E5,””)
Stationary	=IF(D5=$I$4,E5,””)
Misc. Exp.	=IF(D5=$J$4,E5,””)

After entering the above formulas, test for accuracy in the formulas by entering a sample transaction and switching through all the items in the drop-down list under the Group column. (See screenshot below)

Remember to format all the amount columns and cells with the accounting/currency number format.

Step #5: Finding the totals

Let’s get the total for the amount columns.

To generate the totals, click anywhere in the table and press Ctrl+Shift+T on your keyboard.

This shortcut will add a new row with a drop-down of functions at the button of the table.

Use the drop-down to sum all the amount columns.

See screenshot:

Step #6: Computing the balance

We need to use a formula to do this task. And this formula is going to return the difference between the total amount received and the total amount spent after each and every transaction.

This will give the petty cashier the most recent update about the amount of cash supposed to be in hand.

To compute the balance, select the cell where you need the balance to be and key in the following formula:

=SUM(Table2[[#Totals],[Receipt]])-SUM(Table2[[#Totals],[Total Payment]])

Well, this formula looks too complicated but it doesn’t have to be. You don’t have to type every single character of the formula. It’ll waste much time. The easy way is to point and click in your formula. To do so:

• Type =SUM( in the cell you need the balance.
• Without pressing Enter or clicking anywhere in your worksheet, click the total of the receipt column and close the brackets.
• Press the minus sign and type the sum function again like this -SUM(
• Again, without clicking anywhere or pressing any key, click the total of the payment column and close the brackets.
• Now press the enter key to complete the formula.

See screenshot:

Step #7: Final touches

At this point, the petty cash book template is ready for use. If you are going to use it yourself, it may be fine like this. But if you want to share it with others, there’s the need for you to make it user-friendly. To make our Excel petty cashbook template user-friendly, just do the following.

Format the Cash Balance

You can use some conditional formatting to hint the petty cashier whenever she is running out of cash. To do so, select the Balance box and navigate to Home→Styles→Conditional Formatting→Highlight Cells Rules→ Less than.

In the less than dialog, type the minimum amount in the less than box and specify the format you want to apply to the balance cell when it is less than zero. That way, it’ll draw the petty cashier attention that she needs some reimbursement to continue the payments. (See screenshot)

Add some colors

Well, filling your worksheet with colors doesn’t make sense. But at times, it does. Like in this petty cashbook, we can give a particular color to the cells that do not require a user input (i.e. cells with formulas). And by doing this, we can easily indicate in the template instructions that cell with that particular color does not require any user input.

So if you buy my idea, here’s how to give color to our cashbook.

• Highlight the cells that contain formulas.

To highlight all of the formula cells at the same time, hold down the Ctrl key. Holding down the Ctrl will allow you to jump and add cells to your selection.

• Go to Home→Font→Color Fill

Here you’ll be given gazillions of colors to mess around with. Choose the color you like for the cells to have and they will surely have it.

See screenshot:

Protect the worksheet

This is necessary to prevent users from deleting or making unnecessary changes to formulas, mistakenly messing with formatting, and many more. To protect your worksheet against these dangers, you need to dabble in Excel’s worksheet protection features.

Here are the steps you need to protect your worksheet against such accidents:

• First, ensure that all cells which require data to be manually entered are unlocked.

By default, every cell in excel is locked. Our petty cashbook template contains about five columns that require a user input. All these columns need to be unlocked so that users would be able to enter data. To lock or unlock cells, go to Home→Cells→Format→Lock. (see screenshot)

Alternatively, if you want to lock or unlock cells, select the cell or cells you want to unlock, right-click the selection and choose Format Cells. Click the protection tab and turn off the Locked check-box and click Ok. (See screenshot)

NOTE: Turning the Locked feature on or off have no effect until you make the final steps of protecting your worksheet. See the next step.

• Right-click the ANALYTICAL PETTY CASHBOOK tab and click Protect Sheet.

In the Protect Sheet windows, select the checkbox labeled “Protect worksheet and contents of locked cells”

From the “Allow all users of this worksheet to” list, turn on or off the actions you want people to be able to do on the worksheet.

You can also specify a password in the “Password to unprotect sheet” text box to stop unauthorized people from unprotecting the worksheet.

After checking all your preferences, click Ok to protect your worksheet.

Remember: When the sheet is protected you cannot do certain things like adding more columns or rows. So always remember to unprotect the sheet if you are unable to do something after protecting the worksheet.

Include User Instruction Sheet

Every software requires a user manual, so is your petty cash book template. Creating a system for yourself is different from creating it for others to use. The user is not in your mind to know how to use the template right away. You need to teach her by dedicating an additional sheet in the workbook for user instructions.

To make users understand the instructions you craft, you must know who is going to use the template, then write it with the user in mind. Keep your instructions clear, precise and simple in order to ensure a problem-free user experience.

Never assume that the user already knows what you mean. You know what they say, that Assumption is the matter of mistakes. People are not mind-readers.

This is the end of creating our analytical petty cash book.