Bean Counter

https://www.dwmbeancounter.com

How to Use The Spreadsheets

This spreadsheet is a comprehensive bookkeeping tool designed for students to practice entering transactions on a monthly basis.

It supports both Cash and Accrual accounting methods and provides automated financial statements.

It also supports the perpetual and periodic inventory systems but is designed mainly for use with the periodic system.

If using a perpetual inventory system you need an additional standalone system to manage your inventory and product costs.

Capacity: 100 Transactions per month

Below is an analysis and a step-by-step guide on how to use the spreadsheet effectively.

1. Initial Setup

Before entering transactions, you must configure your business information in the Bookkeeping tab:

Your Information: Enter your business name and the current fiscal year.

Profit Sharing: If the business has multiple owners (up to 3), enter their names and their respective Profit Share %. The total must equal 100%,

or the spreadsheet will flag an "Error."

Beginning Balances: If you are starting mid-year or moving from another system, enter your starting balances using Journal Entries

(e.g., current cash in bank, accounts receivable) as the first entries to ensure your Balance Sheet and Income Statement is accurate.

Review and modify your Chart Of Accounts-Use Account Types Tab.

Add Customers and Suppliers- Use Customer-Suppliers Tab

2. Understanding the Chart of Accounts

The Account TypesTab contains your "Chart of Accounts." These are the categories you will use to classify every transaction.

Standard Accounts: Includes Assets (Cash, AR, Inventory), Liabilities (AP, Loans, Taxes), Equity, Revenue (Sales),

and common Expenses (Rent, Utilities, Travel).

Customization: You can add up to 5 additional custom expense accounts (labeled New1–New5) if you have specific categories not listed.

Make sure all account names are unique.

3. Recording Transactions (Bookkeeping Tab)

This is the main data entry area. Each transaction typically requires two lines (Double-Entry Bookkeeping) to keep the books balanced.

Data Entry Fields:

Description & Date: Mandatory for every entry.

Account: Select the appropriate account from the dropdown menu.

Debit & Credit: Enter the amount in the correct column.

Rule of Thumb: Debits increase Assets and Expenses. Credits increase Liabilities and Revenue.

Example: If you buy $50 of supplies with cash, Line 1 is a Debit to "Office Expenses" and Line 2 is a Credit to "Cash-Bank-1."

Check/Document #: Use these columns to track invoice numbers or check references.

Customer/Supplier Name: Essential if you want to track how much specific people owe you or how much you owe them.

4. Managing Customers and Suppliers

Use the Customer-Suppliers tab to get a bird's-eye view of your relationships.

When you enter a name in the Bookkeeping Tab, this sheet will automatically transfer the amounts to the Billed and Paid Columns.

Make sure all account names are unique and do not change names after entering a transaction that uses that account.

It helps you track Owed amounts (Accounts Receivable) and Unpaid bills (Accounts Payable).

5. Specialized Tracking

Sales Tax: Use the Sales Tax Tab to monitor tax collected on sales vs. tax paid on purchases. This is vital for filing your periodic tax returns.

Home Office: If you work from home, fill out the blue cells in the Home Office tab. Enter your total home square footage

vs. office square footage, along with annual costs for Heat, Electricity, and Insurance. The sheet will calculate your.

"Eligible Home Office Expenses" for tax deduction purposes.

6. Reviewing Performance (Financial Statements)

The Financial Statements Tab is fully automated. It pulls data from your bookkeeping entries to generate:

Income Statement (P&L): Shows your Gross Profit and Net Income for each quarter and Year-to-Date (YTD).

Balance Sheet: Shows what you own (Assets) vs. what you owe (Liabilities) and your equity.

Accuracy Check: Always check the "Checks included" section at the bottom of the Financial Statements. If it does not say "Balanced,

" there is a typo in your bookkeeping (e.g., your debits don't equal your credits for a specific transaction).

7. Sample Transactions

Practice: Use the Sample Transactions Tab to record transactions and see examples of how common business transactions

(buying supplies, billing a client, paying a utility bill) are recorded.

8. Summary of Tips

Monthlyy Navigation: The Bookkeeping Tab is long. Use the "Jump to" links (e.g., "Jump to Cell A248" for Month 2) to move quickly between months.

Filtering: Use the Excel Filter feature (Data > Filter) on the headers in the Bookkeeping tab to view only specific accounts or customers.

Data Entry: After entering transactions click the Financial Statements Tab and check that the statements balance.

Data Entry: Make sure to use the Sample Transactions Tab for practice and the Bookkeeping Tab for recording actual transactions.

Account Drop Down Menus: Besides selecting the accounts from the dropdown menus you can also start typeing the name to speed up the selection.

Make Backups and keep a Master Copy

Navigation-Tabs

Tabs located at the bottom allow you

navigate to the different sections of the

Workbook. Arrow Keys < > allow you

to move back and redisplay Tabs that are

not displayed.

Workbook Links are also provided below.

Inventory Calculations

Tabs

Instructions Tab

This Tab explains the purpose of this

Workbook and what tasks the different

Tabs (sections) perform.

Bookkeeping Tab

This Tab is the "Heart" of the Workbook.

This is the section where you enter your

Transactions.

You enter your Transactions by Quarter.

You select the links provided to

"Jump To" the correct area of the worksheet

for entering your quarter transactions.

Deleting or Correcting Entries

Deleting Entries

Use your Delete Key to remove all the

information entered in the cells.

Correcting Entries

Use your Delete Key to remove the information

entered and enter the correct information.

When entering Transactions, Drop Down

Menus are used for entering Accounts

and Customers and Suppliers.

When you enter an Accounts Receivable

Transaction by selecting the Accounts Receivable

Account or enter an Accounts Payable Transaction

by selecting the Accounts Payable Account

you need to add the customer or supplier.

An Error Check is included for catching

when debits and credits don't balance.

After Entering Transactions, I recommend that

you go to the Fianancial Statements Tab to

check that your Financial Statements balance.

You can also Filter Transactions to display

specific information about your transactions

such as customers, suppliers, accounts,etc.

Business Information Sections

Enter Business Name and Year

Enter Owners % Ownership

Account Transactions Tab

This Tab displays your Account Balances

sorted by account name and also your customer

and supplier balances.

Financial Statements Tab

This Tab displays your Financial Statements.

An Error Check is included to make sure your

Statements Balance.

Home Office Tab-Optional

This Tab calculates your allowed Home Office

Expenses.

Account Types Tab

This Tab is used for changing Account

Names and adding up to 5 new expense accounts.

in your Chart Of Accounts.

Common business accounts have already

been included.

Warning !

Do Not delete or add any new rows to this

sheet. It will mess up the calcualtions used

for preparing your Financial Statements.

Customer-Suppliers Tab

This Tab is where you add your Customers

and Suppliers. You can have 50 Customers

and 30 Suppliers. A Not Assigned Customer

and a Not Siigned Supplier is included to use

for miscellaneous customer and suppliers.

Sample Transactions Tab

This Tab is used for getting familiar with how

to enter Transactions so you know how to

Enter Actual Transactions.

Inventory Tab

This Tab provides a worksheet for calculating

Cost Of Sales using a periodic inventory system