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.