In today’s corporate finance environment, there’s a lot of buzz regarding the relevance of Excel. There are many today who feel as if Excel is outdated, and that newer tools, such as Datarails and Vena must replace the application. But, if you are a finance professional, regardless of how you or your peers in your organization feel about the contemporary debate surrounding this subject, you likely have a baseline (if not advanced) level of proficiency with this application. You probably also inevitably use it on a regular basis to collect, analyze, and present data.
If you feel as if you consistently fall short in the realm of accuracy, whether that relates to your technical approach to your work, or the ineptitude of Excel, both of these ideas hold some truth. Many Excel users today aren’t aware of how to best ensure their data’s accuracy on a technical level, nor do they possess a logical understanding of what advantages and shortcomings of Excel are. There’s a lot to be said about the criticality of both these virtues in relation to the performance of a finance department; understanding them well can take the fruits of your labor to the next level.
Technical Improvements for Spreadsheet Accuracy
1. Improving Accuracy in Excel Spreadsheets with the SUMIF Formula
Improving accuracy in Excel spreadsheets over time becomes difficult as formulas tend to get longer and less accurate. Oftentimes, when we use Excel formulas, we do them as a one-time thing to solve the immediate need. You may copy and paste that same formula somewhere else and the inaccuracies continue. This quick tip for using SUMIF formulas will make your accounting spreadsheets easier to read, quicker to update, and more accurate.
Here’s an example of a spreadsheet for a business that is tracking multiple store locations:
Improving accuracy in Excel spreadsheets – Fresno CPA
If you’re looking for your in-store total across all columns, does your formula look like this? =B8+D8+F8+H8+J8+L8
The start of this formula is relatively simple. There were just a couple of additional store locations that needed to be added in, which initially doesn’t seem to be a big deal. But then we added more and more to our business and slowly it turned into this monster that you just can’t look away from.
If you’re adding or subtracting across multiple columns, there is a much easier way to handle the setup so that it doesn’t get out of hand as more items are tracked in your reports.
Here’s what you want it to look like in the editing field: =SUMIF(range, criteria, sum_range)
Range: these are the cells where you want the formula to check. Usually, they will be your headings, but there are other possible applications. Criteria is what you’re looking for in the range. So, if you want to add together all of the in-store columns, then your criteria would be “In-store”. Alternatively, your criteria could also point to the value of a cell.
- Sum_range: is the range of cells where the data that you want to add or subtract is located.
- So, a typical SUMIF formula on our multiple store example might look like:
=SUMIF($B$7:$N$7,“In-store”,B8:N8)
- And for the second column:
=SUMIF($B$4:$N$7,“Delivery”,B5:N5)
When you create a formula in Excel and want to reuse it somewhere else, Excel assumes that you don’t want to use the actual data from the original formula and that you’re copying the cell locations instead. If you actually do want to continue to use the numbers from the original locations, simply insert “dollar sign” around that part of the equation.
In the above example, we are using the dollar signs to ensure that we are always referencing the headings. We make the references to the criteria “absolute” with the dollar sign because we don’t want that part of the formula to change when we copy it down.
2. Keep Formulas Simple
You should keep your formulas simple instead of clumping lots of things together. While you can make things as complex as you like, breaking the calculation into easily recognizable steps helps you to identify errors. Work with several meaningful components, across a few more cells with labels so that the logic of your workings is obvious.
e.g. Total Sales: =(Price*Quantity)-(Price*Quantity)*Discount+(Price*Quantity)*Tax. Remember your algebra teacher nagging about “showing all your workings” and include the separate steps so your logic is transparent (and later you can come back and see what you did)
3. Organize your workbook by Function
Finance professionals (unfortunately) often make very large single spreadsheets with several reports and side calculations all cluttered together on a single sheet which creates confusion, and leads to errors.
Therefore, as a general rule, separate each major function or display to a new worksheet in the workbook. It’s better to split your work into worksheets for Data Input, calculations and workings, reports and notes etc, assign each worksheet a relevant name (i.e. rename it from “Sheet 1″) if you have a lot of similar worksheets such as 3-4 for data input, try to color code the tabs to group them well. If each worksheet contains one logical section, you can easily review the entire report or analysis. Wherever possible avoid hiding worksheets; this really can cause trouble if someone else has to use the sheet.
Additionally create a notes/instructions tab, and include a description of why each sheet exists and working instructions on how to use the workbook to produce the required reports.
4. Use line graphs to uncover unusual results
You can quickly and easily use a line graph to display outliers in a series of numbers. Chart formatting doesn’t matter. Chart placement doesn’t matter. What does matter is that outliers – unusually large or small numbers – stand out from the crowd. If there are no apparent issues simply delete the chart.
- First Click the Chart Wizard button in your toolbar, or choose Insert Chart
- Next Click the Line chart type
- Lastly Click Finish, accepting all defaults
Understanding The Positives of Excel & How to Fill Its Gaps
Excel possesses a great deal of versatility to complete a multitude of tasks, including financial modeling, visualization, data analysis, data storage, textual and quantitative data, and regression/statistical analysis. The primary reason for these specific uses of Excel is its flexibility. Excel allows practitioners to tackle a throng of different responsibilities in a relatively inexpensive, intuitive platform. And what makes this all the more more advantageous is its universal familiarity for so many people, as over a billion people use the application worldwide. In some regards, this can be a positive in regards to accuracy, as people will make less mistakes when they are more familiar with a certain tool.
However, more complex tasks often require tools more intricate than Excel. Data analytics and warehousing, among others, are better solved using tools other than Excel. Additionally, the excessive manual trudging through spreadsheet tasks is prone to human error, which can easily prove catastrophic for any organization. A spreadsheet mistake in October 2003 required Fannie Mae to restate its unrealized gains by an amazing $1.2 billion—shortly after it had announced third-quarter earnings. A software system can improve data accuracy by directly integrating the data source to the analysis, and via automating the workflow in order to have the data analyzed and collected in real time in one place.
