Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Almost all business organizations keep their monetary transaction records in excel worksheets. But creating the excel template with the required formats can be a complicated task for someone who isn’t used to excel. This article will show you the step-by-step procedures to create a Petty Cash Book Format in Excel.
What Is Petty Cash Book?
Petty means something which is of little value. Big organizations have cash books where they record their revenues and expenditures. The major transactions occur through cheques. However, there are sundry expenses that need to be paid via cash only. The little amounts are not ideal to go with cheques. We also need to track those dealings in cash books. But, recording them in the main cash book is a burden and requires time and labor. That’s when the petty cash book comes to the rescue. We can keep the frequent, quick, and small payments recorded in the petty cash book.
How to Create Petty Cash Book Format in Excel: With Easy Steps
In this article, we’ll create an analytical petty cash book format in an excel worksheet. For this purpose, we’ll prepare an excel template. We can create a simple petty cash book where we’ll only insert the received and payment amounts. And we can also build an analytical cash book to elaborate on the expenditures. Therefore, go through the steps below carefully to carry out the operation.
STEP 1: Design Petty Cash Book
- First, we need to input the required headers for our cash book.
- In the Received column, we’ll insert the initial amount the petty cashier will get to expend on small expenses.
- Then, we have a Date and Group.
- The Group is about the payment type, which are Postage, Cartage, and Miscellaneous. They are also column headers.
- After that, another header is Total Payment on the specific date.
- In this way, you can create other desired headers as per requirement.
Read More: How to Create Daily Cash Book Format in Excel
STEP 2: Input Received Amount & Date
- Now, we’ll input the received amount and the date associated with it.
- See the below picture to understand clearly.
STEP 3: Fill up Group
Here, we have 3 types of expenses. In this regard, we’ll create a drop-down list from the Group section from which you can choose instead of typing. So, follow the below process to perform the task.
- Firstly, select the range D5:D12.
- Then, go to Data > Data Tools > Data Validation.
- As a result, the Data Validation dialog box will pop out.
- There, choose Allow > List.
- Next, select the range F4:H4 in Source.
- Press OK.
- Thus, you’ll get a drop-down symbol beside every cell in the Group column.
- Hence, you can click your desired type from there.
- Look at the following figure to have a better understanding.
STEP 4: Insert Total Payment
- In this step, we’ll have to input the total payment amount manually.
- For instance, see the below dataset where we have placed our payments.
STEP 5: Create Formula for Postage
- First of all, click cell F5.
- Afterward, type the formula:
=IF(D5=$F$4,E5,"-")- Subsequently, press Enter and apply AutoFill.
- The IF function tests whether the D5 cell value is the same as the F4 cell value.
- In such a way, the total payment amount in E5 for the postage group will be placed in this column automatically.
- And otherwise, it’ll be a blank cell.
STEP 6: Apply Formula to Get Cartage
- Choose cell G5 at first.
- Input the formula:
=IF(D5=$G$4,E5,"-")- Next, hit Enter.
- Accordingly, use AutoFill to complete the series.
- Thus, it’ll return the payment amounts for cartage.
STEP 7: Generate Formula for Miscellaneous
- In cell H5, insert the formula:
=IF(D5=$H$4,E5,"-")- Then, press Enter and apply AutoFill to get other outcomes.
- Hence, you’ll get expenses made for miscellaneous.
STEP 8: Calculate Total
- Now, select cell B13 and the range E13:H13 at the same time by pressing the Ctrl key.
- Next, use the AutoSum feature in excel to get the total of Received, Total Payment, Postage, Cartage, and Miscellaneous.
- See the following picture for the result.
STEP 9: Find Present Balance
- Accordingly, we’ll need to determine the balance.
- For that purpose, select cell F15.
- Type the formula:
=B13-E13- Click Enter.
- Thus, you’ll get the net balance.
STEP 10: Apply Conditional Formatting
Moreover, it’s practical to highlight the net balance whenever we’re running low. It’ll alert us and we can take necessary actions. In this step, we’ll fill the net balance in red color whenever it’s below $500. Therefore, learn the following process.
- First, select cell F15.
- Then, click Home > Conditional Formatting > Highlight Cells Rules > Less than.
- In the pop-out dialog box, input 500 and choose Light Red Fill or any other options according to your preference.
- Consequently, press OK.
- Thus, whenever the balance falls below $500, it’ll be filled in red as demonstrated below.
- Finally, the petty cash book format is ready for demonstration in excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
Conclusion
Henceforth, you will be able to create a Petty Cash Book Format in Excel following the above-described steps. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
