Guide to keeping simple accounting in Excel for a small warehouse

Keeping the accounts of a small store doesn't have to be complicated. Although there are specialized software, Excel is an excellent tool for those looking for a simple and economical solution. In this guide, I will show you how to create a basic accounting system using Excel, without being too technical, but ensuring that you keep control of your finances.

How many sheets do you need in Excel?

To properly manage the accounting of your store in Excel, we recommend organizing the information in several sheets within the same file. Below I explain the basic sheets you need:

1. Income Sheet

In this sheet you will register all sales and any other income generated by your store. Include the following columns:

• Date
• Income description (e.g.: Sale of product X)
• Income amount
• Payment method (cash, card, transfer)
• Client (optional)

With this sheet you will have a clear control of how much money enters your business daily.

2. Expenses Sheet

Here you will keep track of all expenses made by your store, from the purchase of products to service payments. Include the following columns:

• Date
• Expense description (e.g.: Inventory purchase)
• Expense amount
• Provider or payment recipient
• Category (inventory, services, rent, etc.)

This sheet will allow you to analyze where the money goes and adjust your costs if necessary.

3. Inventory Sheet

Although accounting does not always include inventory, it is useful to keep a record of it in Excel. Include the following columns:

• Product code
• Product description
• Available quantity
• Unit cost
• Total value (available quantity * unit cost)

This sheet will help you control what you have in stock and the value of the inventory.

4. Accounts Receivable Sheet

If you offer credit to your customers, this sheet is essential to know who owes you money. Include:

• Date
• Client
• Amount to collect
• Due date
• Status (pending, paid)

With this sheet you will always know how much you are owed and be able to track payments.

5. Accounts Payable Sheet

Like the previous sheet, this will allow you to know how much you owe your suppliers. Include:

• Date
• Provider
• Amount to pay
• Due date
• Status (pending, paid)

Keeping track of your debts will help you plan your payments better and avoid delays.

6. Balance Sheet

This sheet will be the summary of your accounting. Here you will see a general view of your financial situation. It is recommended to create a monthly or quarterly balance. Include:

• Assets (cash, accounts receivable, inventory)
• Liabilities (accounts payable, debts)
• Equity (difference between assets and liabilities)

The balance will indicate if your business is generating profits or losses.

Other useful records in Excel

• Employee Registry: If you have employees, you can keep a basic control of their salaries and payments in an additional sheet.
• Tax Registry: Create a sheet to keep track of the taxes you have to pay, such as VAT or income tax.

Is it useful to learn to use macros?

In Excel, macros can automate repetitive tasks, such as generating reports or sending notifications when certain goals are reached. Learning to use them can make your accounting system in Excel more efficient, although it is not mandatory for a basic system.

Keeping a simple accounting in Excel is completely possible for a small store. With a few organized sheets and dedication to register your financial movements, you can keep control of your business without complicating yourself. Although this is a basic solution, remember that as your business grows, you can consider more advanced or specialized options.