Excel Accounting for Small Businesses: It’s Easier Than You Think

I’m a writer, not an accountant. The mention of double-entry bookkeeping used to put me to sleep — and then give me stress nightmares. Fortunately, I’ve evolved. I won’t pretend that I’ve transformed into a number-loving accounting superstar. But Excel accounting for small business doesn’t require you to be one either.

All you need is a brief orientation and some useful templates and formulas. This article will cover the following:

• cash basis accounting
• accrual-basis accounting and the double-entry method
• helpful formulas
• helpful templates
• additional tips

Whether you’re a full-time small-business owner or an employee with your own side business, you can find the tools you need in Excel. So take a deep breath, brace yourself, and let’s run the numbers.

Excel Accounting 101

You can perform near miracles using Excel spreadsheets. There’s even a world championship in Microsoft Office applications.

That’s not for us.

These are the most basic forms of accounting, and you can perform any of them by using Excel. I’ll discuss the difference between cash basis and accrual accounting. Along the way, I’ll also demystify double-entry (or dual-entry) accounting.

Cash basis accounting

Once upon a time, people used to balance their checkbooks by hand. They’d record income and expenses to their account. They would regularly look over these lists to ensure that they always knew how much money they had available.

That’s all there is to cash basis accounting.

Many small businesses prefer to use this system because of its simplicity. You can — and should — keep additional records of accounts payable (money you owe) and accounts receivable (money owed to you). But your fundamental accounting tracks money only when it changes hands.

Your spreadsheet should have at least the following columns:

• transaction number
• date
• description
• income
• expense
• balance

Let’s say that you’re a wedding planner. On July 8, 2021, you begin the day with $ 750 in your account. On that day, you need to pay the florist for the upcoming Johnson wedding. You also need to collect a payment from the Johnson family.

Your Excel accounting might look something like this:

This cash basis accounting example shows two transactions: one credit and one debit. For each transaction, the account balance is updated accordingly.

You record your payment to Rosa’s Roses first and decrease your account balance by $ 150 to $ 600. You then record the check you received from the Johnsons, increasing the balance to $ 1100.

Yes, it really is that easy.

Accrual accounting and the double-entry method

While cash basis accounting is simpler, many businesses prefer accrual accounting because it gives them a fuller, more accurate picture of their company’s finances.

Instead of simply recording cash flow, accrual accounting requires you to record all your assets and liabilities. The core principle of accrual accounting is the equation:

• Assets = Liabilities + Shareholders’ (or Owner’s) Equity

This is where double-entry bookkeeping comes in. Double-entry accounting helps you ensure that you keep this equation balanced. Every time you increase or decrease one side of the equation, you need to do the same to the other.

In accrual accounting, you need more than a single ledger. In addition to your balance sheet, you need to keep worksheets for each of the accounts included on it.

The first page of your Excel accounting workbook should be a chart of accounts. Create a list of all your accounts including what type of accounts they are and how you increase them.

This last part — how you increase your accounts — is the most confusing aspect of Excel for small businesses, and I’ll return to it momentarily.

There are five types of accounts:

• assets
• expenses
• liabilities
• equity
• revenue

Assets and expenses belong on the left side of a balance sheet (or balance equation), while liabilities, equity, and revenue belong on the right.

Why do expenses belong with assets? Remember that these are not accounts payable/money that is still owed to others. These are the things that you pay for.

The electric bill you need to pay is a liability and goes on the right side of the equation. In return, you get something — electricity — which goes on the left, balancing the equation.

So why aren’t expenses simply assets? Because they’re consumed in the ordinary operation of your business during an accounting period. You don’t have leftover electricity at the end of the month that you carry on as an asset going forward.

And now for something even more counterintuitive.

Debits and credits

Here’s the number one rule to remember in accrual accounting:

• Assets and expenses are increased with debits and decreased with credits.
• Liabilities, equity, and revenue are increased with credits and decreased with debits.

But why? If you receive a huge cash payment, shouldn’t that credit your cash account? No. You would debit your account.

Again you ask, but why?

Because business accounting is viewed through the lens of banking institutions, and they calculate debits and credits differently than you’re used to doing.

Still confused? Honestly, I only kind of get it, myself. You don’t really need to understand how it works that way, just that it works that way. Similarly, I don’t understand any of the coding behind Excel accounting. I don’t need to (fortunately).

Try not to think about credits as good and debits as bad but as value-neutral accounting terms. You also want to avoid associating credits with increases and debits with decreases. Instead of attaching an imaginary +/- sign to the words, attach a location to them.

• debits = left
• credits = right

You record debits on the left-hand side of individual accounts and use debits to increase values on the left side of the equation. You record credits on the right-hand side of accounts and use them to increase values on the right of the equation.

It’s easier to see in practice. Let’s work through the above wedding-planner example with some simplified accrual accounting.

You start with a cash balance of $ 750. You also have $ 600 in accounts receivable (including the Johnsons’ payment, which is due today) and $ 200 in accounts payable (which includes the invoice for the flowers).

When you receive the Johnsons’ check and the flowers, money moves around. You would record each transaction twice, in the two accounts that it affects.

In accrual and double-entry accounting, you record each transaction twice, adjusting the two accounts that it affects

While this particular example pairs one debit with one credit, you could also have transactions that required you to debit two accounts or to credit two accounts.

For example, let’s say that, while you were at the florist’s, you decided to pick up a vase to add to your inventory. You brought a check to cover the roses you ordered but then used a credit card to buy the vase, which cost $ 50.

In doing so, you’ve increased your inventory, which is an asset. So you would debit your inventory by $ 50 to mark the increase. You have also increased your liabilities with a new outstanding purchase. You would debit your accounts payable by $ 50.

All of these transactions affect your bottom line, and you should balance your accounts on a regular basis. The Excel balance sheet template below will do the work for you.

Excel accounting comes with ready-made templates such as this annual balance sheet.

You might want a simpler monthly balance sheet, but this annual one shows how your various accounts balance in the end.

Double-entry and cash basis accounting

It is possible to use double-entry accounting with the cash method. For example, if you maintain accounts payable and receivable, you would record a customer payment as income and simultaneously deduct the amount from your account receivable. That sum would no longer be recorded as money to be collected at a later date.

The important distinction is that money is recorded only in your balance sheet when it changes hands.

Helpful formulas

One of the great things about using Excel for small business accounting is that you can leave your calculator in the drawer — where it has probably stayed since they first put a calculator on phones.

There are a few formulas you’ll likely find yourself using often. Here are a few to remember:

SUM

Perhaps the most frequently used, this formula allows you to quickly add up long strings of numbers.

Need to add up all the numbers in column A from rows 1 to 50? Just use the SUM function:

• =SUM(A1:A50)

Or just go to the first empty cell at the end of a column (in this case, A51), and click on AutoSum. You can find the button on the Home and Formulas toolbars.

SUMIF

Your business has been a loyal customer of Rosa’s Roses for years. Need to see exactly how much you’ve spent there so far this year? No problem.

SUMIF allows you to add up only cells that meet certain criteria. If you want to see how much money a certain product has brought in — or how much of your income was generated by purchases less than $ 20 — you can do it.

The basic syntax is

• =SUMIF(range, criteria, [sum_range])

Let’s say your accounts payable for the year run from rows 2 to 152. You record the description of the transaction in column B and all credits to that account in column D. Your formula would read as follows:

• =SUMIF(B2:B152, “Rosa’s”, D2:D152)

This would add all values in cells D2 to D152 if the corresponding B cell showed the word “Rosa’s.”

Note the quotation marks around the word “Rosa’s.” If you’re using text, you need to put it in quotation marks.

Other common functions

The most commonly used functions are easily accessible next to the sigma (or sum symbol) on the Home and Formulas toolbars. They are:

• average
• count
• max
• min

Financial formulas

Excel’s financial functions save you the trouble of performing finicky calculations by hand. There’s an entire list of financial formulas that you can simply click on to use. For example, the two formula builders to the right of the screenshot below give you two different ways to calculate depreciation.

Excel formulas do the hard financial math for you. Here are two different ways of calculating depreciation, with the SUMIF function on the left.

Helpful templates

While Excel accounting formulas are incredibly useful, they do require precision. An extra space or period could throw off your equations.

Templates come with all the heavy-lifting done for you. The designers label all the information you need to provide and input the correct formulas to calculate your business’s profits and losses.

I’m going to run through a couple of useful options, but I should point out that you can also download Excel accounting templates from third parties. You might find something that better suits your particular style and finances better.

Business expenses

This template breaks down your costs into different areas:

• employee costs
• office costs
• marketing costs
• training and travel costs

This business expenses template allows you to visualize discrepancies between planned and actual spending

The template’s best feature helps you to see when you overrun your budget. It shows you the difference between planned and actual expenses and keeps a visual record of how closely the two align across the year.

Monthly company budget

You might prefer a more detailed breakdown of your costs that focuses only on the present month. If so, there’s also an Excel accounting template with detailed itemization in the major areas of expenditure.

The monthly company budget template will help you identify where you spend the most.

The monthly company budget template also shows you your top five expenses and allows you to see what large-scale reductions would look like.

General ledger

The most all-encompassing template for small-business accounting is this general ledger. It both tracks planned vs. actual spending and breaks down the monthly budget into greater detail.

The general ledger template is an all-in-one option that covers all of the bases that most small businesses need.

You can even track your charitable giving and sponsorships to make life easier come tax season.

Invoice

Professional invoices are easy with Excel accounting. As the screenshot below demonstrates, you can pick from a variety of layouts, giving you flexibility in style as well as format.

Excel comes preloaded with 24 different invoice templates. A simple search for “invoice” in templates produces all of these options.

And more

There are a lot of different worksheets that you can create with Excel. There are templates available for

• inventory lists
• time sheets
• sales tracking
• contact sheets

Excel templates can save you time with much of your business paperwork.

There are even cash flow projection templates that can help you determine when to consolidate and when to grow your business.

Whenever you need to create a spreadsheet, it’s worth checking to see if there’s a built-in template available. Why reinvent the wheel when you can take a taxi?

Further tips

Before you get to work straightening out your accounts, there are two more things you should know.

When to pivot

Microsoft truly excels — pardon the pun — when it comes to flexible data analysis. And pivot tables make for one of the strongest tools in their office suite.

Pivot tables enable you to focus and refocus data in order to identify and display multiple areas of significance. They are small tables that summarize larger groups of data. You don’t even need to enter your functions — the software will do aggregation and analysis for you .

While you can manually create pivot tables by entering a range of cells, Excel comes with recommended pivot tables that make the process easier. You’ll find them at the leftmost side of the Insert toolbar.

When to move on

Excel for business is a powerful tool. That doesn’t mean it’s a one-size-fits-all solution. There are good reasons why you might want to try something else.

While you can download sets of data from many online platforms, there are software options with better ecommerce integrations.

Programs such as Intuit Quickbooks also make it easier to calculate certain statistics such as profit per sale. You can perform these calculations with Excel, but it’ll take a little more work. Because Excel does so much, it can be easier to master more targeted software.

You also might not want to pay for Microsoft Excel. There are many free applications that will give you most — but not all — of what Excel gives you. For example, Google Sheets is free for personal use.

Now hit the books

Excel accounting may take years to master, but you can learn how to use it in 15 minutes (or however long it took you to read this article).

Be patient with yourself as you get started. Familiarity takes time. And most of us don’t find accrual accounting intuitive. That’s one reason there are so many templates you can try to help you keep numbers in their proper places.

So go ahead, and get started with Excel for business. And, always remember that in accrual accounting, you increase assets with debits, not credits.

Business & Finance Articles on Business 2 Community

(78)