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 absolutely necessary for any small business owner, a significant amount of analysis and reporting is done most effectively in excel.
Excel, is so feature laden, that when you discover a function that does the same task in a fraction of the time, it can be somewhat magical. (Just to be clear, I have no connection to Microsoft. I will eventually write a post on how my feelings towards Word are the exact opposite). I will often manipulate data in different ways just so that I can discover new features and find more efficient ways of completing a given task. This, of course, often ends up taking twice as long as if I had just done it using the more manual, tried and tested methods. What, continues to impress me, however, is that there is an easier way to do almost everything in excel (involving data – unfortunately it will not cook you dinner). And this does not even include programming in excel.
Excel is especially helpful for small business owners and employees who do not have customized financial reporting or accounting software but are interested in disseminating their data. Some of the most useful featuers in excel are below:
Filters found under the Data menu, allows you to apply a filter to your table and select only the information you require. For example if you have a list of invoices and only want to see sales for August, the filter will allow you to select the only the month of August. Alternatively, if you want to see August sales for amounts greater than $10,000, you can do this as well. Or you can filter by specific text. Once you have your filtered list you can copy and paste into a new table, allowing you to see only the queried data.
Pivot Tables are an extremely flexible and useful tool for analysis as it allow you to summarize and group a table of data 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 query a data table with tens of thousands of rows.
VLookups and Hlookups can be used to 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. And 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.
Excel has hundreds of “functions” which allow for the calculation of just about any type of metric. You can sum up your sales, or count them. You can average them by month or year. You can calculate the net present value of a project that you are thinking of investing in to determine if it will be profitable. You can ask for a result based on a certain condition with “If “ functions. Loan repayments, interest rates, amortization and number of days remaining in the loan all take seconds to calculate if you have the right inputs. One of the many advantages of performing even the simplest calculations in excel, eg. Adding up purchases from receipts, is that you can save it for future reference.
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.
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. And, although I imagine this probably does not apply to everyone, playing with excel can actually be a lot of fun.
About the author: Ronika Khanna is an accounting and finance professional who provides excel consulting and report customization . Please sign up to receive articles pertaining to small business, accounting, tax and other occasional non business topics of interest. You can also follow her on Facebook or Twitter.