Using Excel for Small Business Accounting – A Simple Guide

In this article, you’ll learn how to:

• Use Microsoft Excel to manage your small business finances

• Set up a basic bookkeeping system in Excel

• Understand the difference between cash basis and accrual accounting

• Create your own financial sheets and reports

• Get personal help with Excel bookkeeping training in Perth

---

Why Excel Is Perfect for Small Business Accounting

If you run a small business, keeping track of money coming in and going out is essential — and Microsoft Excel is one of the easiest ways to do it.

Excel lets you plan budgets, record expenses, track sales, and calculate profits all in one place. It’s flexible, affordable, and ideal for small business owners who want control over their accounts without paying for complex accounting software.

Many business owners find Excel confusing at first, but once you understand the basics, it becomes second nature. And if you’d like a helping hand, you can always join a 1-on-1 Excel course in Perth — contact us at PerthSoftware.com for a friendly chat.

---

1. Cash Basis Accounting – The Simple Starting Point

Most small businesses use cash basis accounting. It’s a straightforward method where you record income and expenses only when money actually moves.

Here’s how to set it up in Excel:

1. Create columns for Transaction Number, Date, Description, Income, Expense, and Balance.

2. When you spend money, record it in the Expense column.

3. When you receive money, record it in the Income column.

4. After each entry, update your Balance.

It’s just like keeping a digital checkbook.

💡 Tip: Create a new Excel sheet each month, or use one big sheet for the whole year — whatever keeps things simple for you.

---

2. Accrual Basis Accounting – A Complete Financial Picture

If you want a deeper look at how your business is performing, you can use accrual basis accounting. This method records transactions when they happen — not just when cash is exchanged.

Here’s the basic accounting rule:

Assets = Liabilities + Equity

That means your total assets must always equal your liabilities plus your equity.

---

Create Your Chart of Accounts

In Excel, set up a chart of accounts — a list of all your financial categories.
Here are some examples:

• Assets: Cash, equipment, inventory, accounts receivable

• Liabilities: Accounts payable, wages payable, loans

• Equity: Owner’s funds, retained earnings

• Revenue: Sales, service income

• Expenses: Rent, supplies, utilities

Give each account a number and group them by type. This keeps everything tidy and easy to track.

---

Set Up Your Transaction Sheets

Now create another worksheet for your transactions.
Add columns for Cash, Debit, and Credit.

Each time you record a debit, another account changes — same for a credit.

For example:

• If you sell $300 of goods, increase your Cash account (debit) and decrease your Inventory (credit).

• If you buy materials on credit, increase your Asset (materials) and increase your Liability (accounts payable).

Excel makes this simple to calculate once your columns are set up correctly.

---

Quick Guide to Debits and Credits

Here’s an easy way to remember how it works:

• Accounts Receivable (money owed to you): A debit increases it.

• Accounts Payable (money you owe): A debit decreases it.

• When you pay off a supplier, you debit both your Cash and the Liability account.

Example:
You sell 200 items at $1 each on credit.

• Record a $200 debit to Accounts Receivable (Asset).

• Record a $200 credit to Inventory (reducing stock).

If you buy raw materials worth $200 on credit:

• Record a $200 debit to Inventory (Asset).

• Record a $200 credit to Accounts Payable (Liability).

---

Bringing It All Together

With these simple setups, Excel becomes your best accounting assistant.
You can:

• Track every sale and expense

• Generate monthly or yearly reports

• Forecast future expenses

• Automate repetitive tasks with macros

The more you learn, the more Excel can do for you — and it’s all within your control.

---

Learn Excel Accounting the Easy Way

If you’d like to learn how to set up your own Excel bookkeeping system — or just want to feel confident using Excel for business — we can help.

At PerthSoftware.com, we offer personalised Excel training in Perth for individuals and small business owners.
You’ll learn practical, real-world skills that help you save time, reduce errors, and take full control of your business finances.

👉 Get in touch today to book your 1-on-1 Excel session and start mastering small business accounting with confidence.