Excel for Small Business Owners
As a confirmed excel nerd, there is something about large amounts of data that I am inextricably drawn towards . I suppose it has something to do with an affinity for organization combined with a love of numbers and the innate desire to solve problems. As an accountant and financial consultant , I am often presented with the task of organizing and analysing data into a format that allows for greater insight into my clients businesses . And although good accounting software is important for most small business owners, especially once they reach a certain size, a great deal of analysis and reporting is done most effectively in excel.
A small smattering of the ways in which excel can help small business owners includes:
Accounting spreadsheets:
Many small business owners, self employed workers, freelancers etc who don’t have many transactions, are unincorporated and don’t require extensive reporting can simply use a spreadsheet to do their accounting. You can refer to my article on whether you should use a spreadsheet or accounting software. If you do decide to use a spreadsheet, you can set up your own template. Some best practices for an accounting spreadsheet are as follows:
Itemize each transactions
Have a separate spreadsheet for revenues and expenses
Headings should include date, name of supplier/customer, description of product/service, amount before tax, gst, hst, qst, total after tax, method of payment, currency if applicable and category of expense e.g. office supplies, subcontractors, subscriptions etc.
(you can use the categories indicated on the T2125 which is the form that unincorporated business owners complete as part of their tax return)
You can use pivot tables to summarize your data by category. While pivot tables may seem complex at first glance, they are actually quite simple. See below for more details.
financial CALCULATIONS:
Spreadsheets are great for basic calculations and a better alternative to a calculator in many cases, especially if you are doing them on your computer. This is because you enter the data, see what you have entered and ensure accuracy or change the inputs. You can then save the file for future reference. Basic calculations that you can do in excel (or Google Sheets) :
Add, multiply, subtract, divide etc.
Average, count or sum a range of data
Find minimum or maximum values using MIN or MAX.
Set criteria to sum or count a range of data only if it meets certain criteria using SUMIF and COUNTIF
Insert the date/time using NOW
Set up a condition using the IF function. For example you might have a range from which you only need the amounts that exceed a certain minimum. You can use the IF function to set this up.
Conditional formatting, among other things, allows you set up conditions which will highlight the cells that either meet or don’t meet the condition.
Tables are a very simple way to organize and then format your data. A table streamlines other functions including sort, adding new rows and columns, calculations etc.
Net present value (NPV) ,future values or annuities can be easily calculated if you are considering the profitability of an investment or to see how much you can expect to earn.
If you are purchasing a house, car or large equipment you can build your own amortization table and see how much principal vs interest you are paying down.
Analyzing/Querying Data:
Filters
Found under the Data menu, filters can be applied to a range of data that then allows you to select only the fields that you want to see. For example if you have a list of invoices and only want to see sales for August, the filter will allow you to select August (or a date range). Alternatively, you can further filter your data to see August sales for amounts greater than $10,000. Additionally, you can filter by specific text or numbers or dates. Once you have your filtered list you can then copy and paste into a new table, allowing you to see only the queried data.
Pivot Tables
One of my favourite excel functions, pivot tables are one of the best tools for analysis as it allow you to summarize and group your data in numerous ways. For example, depending on the data in your invoice table, you can see your sales by date, or state. Or sales rep. Or for the month of February. A short tutorial on creating pivot tables can be found here. Once you have started using pivot tables, it can take mere minutes to organize a data table with tens of thousands of rows and extract the information that you need. It is particularly useful, as mentioned above, for summarizing your accounting data by category that can then be entered on to the T2125.
VLookups
One of the more popular formulas in excel, a vlookup can be used to analyze large data sets and return results of a specific query. For example if you have a table where 10 customers are department stores, you can use a lookup function to retrieve data for only the customers you specify. Combined with other functions you can create another column which labels them as department stores. Once the formula has been entered, this can be fully automated.
Building Reports and Templates:
Financial Statements ,Budgets and Cash Flows can all be built, in an aesthetically pleasing and functional format, using excel. Building in formulas and linking schedules allows for interactive reports that can be updated with minimal effort. This is particularly useful with budgets, where you can build an assumptions page that feeds into the financial reports. Any changes to assumptions need only be changed in one place, and all reports are simultaneously updated. There are many formatting options as well including preset table formats, fonts, colours, borders etc. that can turn a drab financial report into a something far less boring. Good formatting and presentation can result in a much better understanding of the underlying material. Some functions/tools that are helpful for building reports and templates include:
Format which gives you access to a variety of different formats from colours of text and cells, size, fonts, margins etc.
Charts can be used on any data set and will great a visual presentation of the info in the type of chart you choose
Pre-built templates in excel including planners, budgets, cash flows, invoices, charts etc.
Import data from a PDF document which is often the starting point for building a template
Power BI is deeply powerful tool that allows you to build dashboards and reports. There is a free version which is often sufficient.
Excel is an amazing tool for any small business owner that is interested in understanding and/or manipulating their data. A better grasp of financial data ultimately contributes to your bottom line by allowing you to make informed decisions. There are a plethora of resources on YouTube and blogs that can help you navigate your way and while I recognize that not everyone is a numbers nerd like me, playing with excel can actually be a lot of fun.
Ronika Khanna is an accounting and finance professional who helps small businesses achieve their financial goals. She is the author of several books for small businesses and also provides financial consulting services.
Subscribe to our biweekly newsletter to receive articles, tips, tools and special offers for small businesses.
UPDATED FOR 2024
Mastering Canadian taxes as a small business or self-employed owner can often feel complex and overwhelming. Simple errors, oversights, and miscalculations can result in paying a higher tax bill than is necessary and result in costly audits and/or penalties.
But keeping your business on the right side of the tax authorities is easier than you think. Small Business Tax Facts: An Easy-to-Understand Guide for Canadian Small Business Owners teaches you the fundamentals and the facts about income and tax. It shows you how to navigate taxes, pay only what you owe, and on time, and save your hard earned money by claiming the deductions that you are entitled to.
Inside this comprehensive and helpful guide for unincorporated small business and self employed owners, you’ll discover:
a comprehensive breakdown of business expenses and deductions with examples to help you maximize your tax savings
detailed explanation of income tax implications for small business and self-employed individuals
expert guidance on how tax brackets and rates work so that you can understand exactly how you are taxed
the differences between being an employee and a small business/self employed owner allowing you to make informed decisions about your tax obligations
how to register for an online CRA Account to simplify your tax reporting and compliance
a list of the most common tax deductions and tax credits to help you reduce your tax bill
insights into how small business/self employed owners should do their accounting to simplify the tax preparation process
guidance on how to prepare and file your own business income tax return, allowing you to save on hiring an accountant
insights on sales taxes, including how to prepare your sales tax (GST/HST) returns accurately.
how Canada Pension Plan (CPP) contributions work ,how to calculate them and how they affect your taxes payable
and so much more!
Discover insider secrets, tools, techniques, and must-know information from author Ronika Khanna CPA, CA, & CFA, and learn to navigate your Canadian small business taxes with ease.
Get your free small business tax return checklist .