How to Create Deposit, Withdrawal & Balance Worksheets

Stockbyte/Stockbyte/Getty Images

Accounting for money is critical to operating a business. Monitoring the flow of cash in your company in the most simplest form means recording the basic transactions of deposits and withdrawals and preparing an account balance. One way is to create worksheet printouts that can be filled in and retained in your files. Here are a few steps to get this accomplished using Microsoft Office.

Prepare a Deposit Worksheet

Open a new Microsoft Excel worksheet and list the following information in three columns.

At the top of the page highlight the first two cells in the first row and click 'Merge & Center' from the 'Home' tab in the 'Alignment' sections. This will merge both cells in column one and two together. Type 'Organization Name.' Tab over to the next cell in the first row and type 'Account #'

Merge the first two cells in the second row and then type 'Person Responsible.' Tab over to the next cell in the second row and type 'Date.'

Place cursor in the first cell in the third row and type 'Checks.' Tab over to the next cell and type 'Amount.' Tab over to the next cell and type 'Total.' Then place cursor in the first cell of the next row and type 'Check #'. Then tab over to the next cell and third cell. In the cell type an equal sign however if you do Excel will think you are about to type in a formula so you will need to properly format the cell to interpret the entry as a general entry and not a formula. On the 'Home' tab under the 'Numbers' section locate the drop box that says 'General.' Click the drop down arrow and select 'Text.' Then type in the equal sign.

Copy the row to create room to list several checks on the worksheet. Highlight those three cells. Excel will form a border around the three cells plus a small box will show in the lower right corner of the third cell. Place your cursor on the little box and drag to expand the board down eight or ten rows.

Place the cursor in the first cell of the next blank row and type 'Dollar Bills.' Tab to the next cell and type 'Tally.' Tab to the next cell and type 'Total.'

Type the following in the first and third cell of the next six rows.

Dollar Bills
Ones ($1)
Fives ($5)
Tens ($10)
Twentys ($20)
Fiftys ($50)
Hundreds ($100)

Total x 1.00 = x 5.00 = x 10.00 = x 20.00 = x 50.00 = x 100.00 =

Place the cursor in the first cell of the next blank row and type 'Coins.' Tab to the next cell and type 'Tally.' Tab over to the next cell and type 'Total.'

Type the following in the first and third cell for the next four rows.

Coins
Penny ($0.01)
Nickels ($0.05)
Dimes ($0.10)
Quarters ($0.25)

Total x 0.01 = x 0.05 = x 0.10 = x 0.25 =

Merge the first two cells of the next blank row and type 'Total Deposit.' Leave next cell blank to fill in information. Use the 'Boarders' feature from the 'Home' tab to create grid lines around the text. Make any final edits then save and print. Make multiple copies of the form for future use or fill in the form on screen and print out for your records.

Prepare a Withdrawal Worksheet

Open a new Microsoft Excel worksheet and list the following information in four columns. The first column should list the required information titles and the second column should be left blank for filling in information. You may want to add or delete some titles to customize for your individual company needs.

Highlight the first two cells at the top of the page in the first row and click 'Merge & Center' from the 'Home' tab in the 'Alignment' sections. This will merge both cells in column one and two together. Type "Organization Name.' Tab over to the next cell in the first row and type 'Account #'

Merge the first two cells second row and then type 'Person Responsible.' Tab over to the next cell in the second row and type 'Date.'

Place cursor in the first cell in the third row and type 'Date.' Then tab over to the next cell and type 'Reason.' Then tab over to the next cell and type 'Amount.'

Leave several blank rows for multiple entries. Update this worksheet as withdrawals of cash are made. Decide whether you will total these withdrawals on a daily, weekly, monthly or even quarterly basis. When you are done with your entries for the time period you have selected, merge the first three cells of the next blank row and type 'Total Withdrawals.' Leave next cell blank to fill in information. Use the 'Boarders' feature from the 'Home' tab to create grid lines around the text.

Make any final edits then save. Fill in the form on screen and print out for your records.

Balance Worksheet

Open a new Microsoft Excel worksheet and list the following information in four columns.

At the top of the page highlight the first two cells in the first row and click 'Merge & Center' from the 'Home' tab in the 'Alignment' sections. This will merge both cells in column one and two together. Then type "Organization Name.' Tab over to the next cell in the first row and type 'Account #'

Merge the first two cells second row and then type 'Person Responsible.' Tab over to the next cell in the second row and type 'Date.'

Merge the first three cells of the next blank row and type 'Beginning Account Balance.' Leave the fourth cell blank to fill in amount.

Merge the first three cells of the next blank row and type 'Revenue.' Leave the fourth cell blank to fill in amount. In the second cell of the next several rows type all the Revenue source titles your company receives. Leave the third cell blank. Fill in amounts in the fourth cell.

Merge the first three cells of the next blank row and type 'Total Revenue.' Leave the fourth cell blank to fill in the total amount.

Merge the first three cells of the next blank row and type 'Liabilities.' Leave the cells blank. In the second cell of the next blank row type 'Expenses.' In the third cell of the next several rows list all the expenses for your company. This would include things like rent, phone and supplies. Leave the fourth cell blank to fill in amount.

Merge the first three cells of the next blank row and type 'Total 'Expenses.' Leave the fourth cell blank to fill in the total amount.

Type 'Payables' in the second cell of the next blank row. In the third cell of the next several rows list all the vendor or contractor names of which you pay for products or services. This would include things like fees for services from vendors like a CPA, Marketing Firm or maybe construction materials. Leave the fourth cell blank to fill in amount.

Merge the first three cells of the next blank row and type 'Total Payables.' Leave the fourth cell blank to fill in the total amount.

Type 'Withdrawals' in the second cell of the next blank row. In the third cell of the next several rows list all the reasons you have drawn cash from the account. Refer to your withdrawal worksheet for an accurate record. Leave the fourth cell blank to fill in amount.

Merge the first three cells of the next blank row and type 'Total Withdrawals.' Leave the fourth cell blank to fill in the total amount.

Merge the first three cells of the next blank row and type 'Total Payables.' Leave the fourth cell blank to fill in the total amount.

Remaining Balance: Add Remaining Balance and Total Revenue together. Then subtract the total expenses, payables and withdrawals. The result is your remaining balance.

Use the 'Boarders' feature from the 'Home' tab to create grid lines around the text. Make any final edits then save. Fill in the form on screen and print out for your records.

Tips

  • Use a 10-key adding machine to tally the total of all of the checks you receive. You should make a copy of the adding tape. Retain copy for the worksheet upon completion.

    Save the worksheets after you have input your information so that you can retain until you are ready to print and file. It might also be smart to retain a working spreadsheet for back up.

    Always record a reason for cash withdrawals so you can account for the reduction in cash on the account.

    It may be beneficial to purchase either a lock box or safe that can be kept in the office between deposits.

Warnings

  • Spreadsheets may result in multiple pages so you may have to adjust the format in order to achieve the appearance of the worksheet you want.

References

Resources

About the Author

Toria Renee has been writing professionally since 2001. She is a former managing editor of Black Youth In Action Quarterly newsletter. Renee's published works have appeared in the New Coalition News & Views Quarterly newsletter, for which she was a writer/junior editor. Renee has a Bachelor of Business Management from Robert Morris College.

Photo Credits

  • Stockbyte/Stockbyte/Getty Images