This is pretty geeky, but there is something about large amounts of data, in a neatly organized table, that I find exciting . I suppose it has something to do with an affinity for organization combined with a love of numbers. As an accountant and financial consultant, I am constantly faced with the task of organizing and analysing data. And although good accounting software is absolutely necessary for any small business owner, almost all of my financial reporting and analysis is done using excel.
Excel, for my purposes, is somewhat magical. (Just to be clear, I have no connection to Microsoft. I will eventually write a post on how much I despise Word). I actually like manipulating data in excel, just so that I can discover new features and find more efficient ways of doing thngs. 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 everythingin excel (involving data – unfortunately it will not cook you dinner). And this does not even include macros.
If you are planning to updgrade to excel 2007 or 2010, keep in mind that the interface is quite different. This takes a while to get used to, and can be quite frustrating in the beginning. However once you have become familiar with the new structure, excel 2007/10 is vastly superior to 2003.
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. Below are some ways in which excel can add value:
Excel has an almost overwhelming number of features to help analyze data including. The two I use most often are:
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 a August, the filter will allow you to select the whole month. Alternatively, if you want to see August sales for amounts greater than $10,000, you can do this as well. 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.
Excel has hundreds of “functions” which allow for the calculation of just about anything. 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 extremely 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 work of art. 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 helps small businesses achieve their financial goals. 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.