Save Time & Cost by Learning How to Use Excel Properly

Microsoft Excel has been around since the mid eighties and is used globally by micro to multinational organisations. For micro to sole trader business’s Excel can be used to build Financial Models to cater for the entire Accounting and reporting function. For small and medium business’s Excel Models can often replace the need for additional expensive reporting and forecasting software.

Many Excel Spreadsheets are designed and managed by the end users. They often lack control and are limited to the users Excel abilities. Without adequate controls and checks they can be prone to error and somewhat unreliable. In July 2009 Shares in the Irish Drinks company C&C fell 15% after its said revenue for the previous four months had not risen by 3% but fell by 5%. The error occurred by incorrectly transferring data to a spreadsheet. How many times have you seen someone copy and paste figures to a report, which takes time and can be prone to errors. Using lookup and other reference formulas along with pivot tables can create the same reports, saving time and are less prone to errors.

Spreadsheets:
A Spreadsheet’ is a general term for any type of data saved in Excel, including a Financial Model. A Spreadsheet could really be anything – an item list, data output from accounting systems, a well presented management report – or a Financial Model used to ensure all control accounts balance against the Trail balance.

A Spreadsheet is usually static. Once a Spreadsheet is complete, it often becomes a standalone report with no further changes to be made. A Spreadsheet might simply be a report which aggregates information from other sources and assembles it in a useful presentation. It may contain a few formulas, such as a total at the bottom of a list of expenses, or average sales over 12 months, but the results will depend on direct inputs into those columns and rows. A Spreadsheet shows actual historical data.

Financial Models
A Financial Model is structured. A Model contains sets of variable assumptions, inputs, outputs, calculations and scenarios. A Financial Model will also often include a set of standard financial forecasts including a profit and loss, balance sheet, and cash flow which are based on those assumptions.
A Financial Model is dynamic. A Model will contain variable inputs which, when changed, will impact the results of the model. A Financial Model will use relationships between several variables to create the financial report, and changing any or all of them will affect the output. For example, Income for a Period could be a result of Cost Price X Quantity Sold X Mark up. In this example, three factors come into play, and the end user can explore different mixes of all three to see the results and which reflects their business Model best.

By learning how to build reliable and robust Financial Models can save you time. Once you have these skills, you can integrate Excel with outputs from your current Data capturing systems, giving you the ability to analyses data and produce reports in a time efficient manner. These reports and Financial Models can be as simple as a daily Purchases report, enabling the user to tightly control costs and make savings, or daily cash forecast and bank reconciliation ensuring the bank account is not overdrawn and unnecessary bank charges are not incurred. Here are some tips for creating reliable and robust Financial Models:

1) Keep a separate inputs and assumptions sheets. Any data that may be subject to change should be kept on these worksheets. This will include variables, criteria, conversions and assumptions.

2) Never hardcode. When building formula always link back to the inputs and assumption sheets. As workbooks grow it will become impossible to remember where you have hardcoded and will render the model impossible.

3) Only carry out a calculation once and link back to the original calculation. You can then use the keyboard shortcut Ctrl + [ to navigate back to the original calculation and then press F5 twice to return to the current cell.

4) Keep formula simple. Don't be afraid to use the results from one formula as a criteria or value for another formula. Understanding the built in functions, and selecting the correct function will make the Model more robust and will help keep formula small. Lower level formula also allows others to understand the workings of the Model better.

5) Keep time lines consistent and have all dates driven from one place in the workbook. If a number of the worksheets have timelines, keep these timelines in the same place. For example if you have Sales in the first workbook by month and January is in column D. The second worksheet are Overheads also by month, ensure that January is also in column D. Also ensure that the timeline can only be changed in one location and this will feed through to all of the worksheets.

6) Name and number your worksheets. For example 1_profit, 2_balancesheet. Or with a group of companies 1_profit, 2_profit, 3_profit, 4_balancesheet, 5_balancesheet, 6_balancesheet, 7_consprofit, 8_consbalancesheet.

7) Ensure the workbook has easy of navigation. Set up an index page and use hyperlinks to navigate through out the workbook.

8) Use control calculations. By using control calculations you can enhance the integrity of the Model for the users. All control calculations should be aggregated and summarised into one worksheet. It is also good practice to hyperlink each error on the summary sheet back to its originator.

9) Keep a log. When building a Financial Model it is useful to keep a log sheet. This will help other users to track and understand any changes that have been made to the workbook.

Another skill to saving time in Excel is to learn keyboard shortcuts. In my early years of using Excel I would often look over someone’s shoulder as they worked on a spreadsheet and watched in amazements as they quickly navigated around the spreadsheet using the keyboard. These take practice but will in time save you time. I recently carried out a survey among accountants regarding the use of Excel and 68% of Accountants surveyed use the mouse instead of Keyboard shortcuts.

About the Author

Paula Guilfoyle is a qualified CPA working across businesses in the SME sector for over ten years. Paula is also the owner of The Excel Club(www.theexcelclub.com), which provides online Excel training, Financial Modelling solutions and a spreadsheet auditing service