Using Excel for Business Analysis. Fairhurst Danielle Stein

or processing of input required by the model. Financial models are built for the purpose of providing timely, accurate, and meaningful information to assist in the financial decision-making process. As a result, the overall objective of the model depends on the specific decisions that are to be made based on the model’s output.

      As different modelling tools lend themselves to different solutions or output, before selecting a modelling tool it is important to determine precisely what solution is required based on the identified model objective.

       Evaluating Modelling Tools

      Once the overall objective of the model has been established, a financial modelling tool that will best suit the business requirements can be chosen.

      To determine which financial modelling tool would best meet the identified objective, the following must be considered:

      ■ The output required from the model, based on who will use it and the particular decisions to be made.

      ■ The volume, complexity, type, and source of input data – particularly relating to the number of interdependent variables and the relationships between them.

      ■ The complexity of calculations or processing of input to be performed by the model.

      ■ The level of computer literacy of the users, as they should ideally be able to manipulate the model without the assistance of a specialist.

      ■ The cost versus benefit set off for each modelling tool.

      As with all software, financial modelling programs can either be purchased as a package or developed in-house. Whilst purchasing software as a package is a cheaper option, in a very complex industry, in-house development of specific modelling software may be necessary in order to provide adequate solutions. In this instance, one would need to engage a reputable specialist to plan and develop appropriate modelling software.

      Which package you choose depends on the solution you require. A database or customer relationship management (CRM) data lends itself very well to a database, whereas something that requires complex calculations, such as those in many financial models, is more appropriately dealt with in Excel.

      Excel is often described as a band-aid solution, because it is such a flexible tool that we can use to perform almost any process – albeit not as fast or as well as fully customised software, but it will get the job done until a long-term solution is found: “Spreadsheets will always fill the void between what a business needs today and the formal installed systems.”2

       Budgeting and Forecasting

      Many budgets and forecasts are built using Excel, but most major general ledger systems have additional modules available that are built specifically for budgeting and forecasting. These tools provide a much easier, quicker method of creating budgets and forecasts that is less error-prone than using templates. However, there are surprisingly few companies that have a properly integrated, fully functioning budgeting and forecasting system, and the fallback solution is almost always Excel.

      There are several reasons many companies use Excel templates over a full budgeting and forecasting solution, whether they are integrated with their general ledger system or not.

      ■ A full solution can be expensive and time consuming to implement properly.

      ■ Integration with the general ledger system means a large investment in a particular modelling system, which is difficult to change later.

      ■ Even if a system is not in place, invariably some analysis will need to be undertaken in Excel, necessitating that at least part of the process be built using Excel templates.

       Microsoft Office Tools: Power Pivot, Access, and Project

      Plain-vanilla Excel (and by this I mean no add-ins) is the most commonly used tool. See the next section for a review of some extra add-ins you might like to consider. However, there are other Microsoft (MS) tools that could also serve to create the solution.

       MS Power Pivot

      First introduced as a free add-in in Excel 2010, and slightly more diffcult to find in Excel 2013, Power Pivot replaces and improves the SQL Server Analysis Services for Microsoft’s Business Intelligence (BI) suite. Put simply, Power Pivot is PivotTables on steroids. It extends the capabilities of the PivotTable data summarisation and cross-tabulation feature by introducing the ability to import data from multiple sources. It will allow you to do things you couldn’t do before in plain Excel, like matching data from multiple sources and pulling them together into a single report. Because it is a relational database, Power Pivot makes it easy to link together data from various sources employing a simple-to-use “drag-and-drop” graphical user interface.

      Wonderful as it is, we know that plain-vanilla Excel stops being quite so wonderful when your data is more than 1,048,576 records long or if the data needs to be consolidated from multiple sources. When faced with this problem, Excel users find themselves migrating to a data warehouse or other, more powerful software. Microsoft has tried to retain these users by introducing Power Pivot, which addresses these problems with added capacity and speed yet retains the familiar Excel interface that we all know and love.

      As a self-service BI product, Power Pivot is intended to allow users with no specialised BI or analytics training to develop data models and calculations, sharing them either directly or through SharePoint document libraries. For more sophisticated users, Power Pivot can:

      ■ Create your own BI solutions without purchasing expensive software.

      ■ Manipulate large data sets quickly, even if they consist of millions of rows (Excel can’t do that!).

      ■ Construct complex what-if reporting systems with data modelling and data analysis expressions (DAX).

      ■ Link data from various sources quickly and easily.

      Power Pivot is one of the most exciting things to happen to Excel in a long time, and certainly worth some consideration when you are building an Excel solution. Although more appropriate for data analysis than pure dynamic financial models, Power Pivot is worth bearing in mind as a possible tool. If you find that your model has the following attributes, then you should consider using Power Pivot:

      ■ Your data contains many thousands of rows and your model is starting to slow down.

      ■ PivotTables or Tables are used extensively.

      ■ Data needs to be sourced from multiple locations.

      One of the great things about Power Pivot is that it is a free download that comes with the licence you have already if you’re using Excel 2010. Be careful, however, about which version you buy if you’re using Excel 2013, as Power Pivot is not included with every version (for some inexplicable reason). There are also a number of differences between the Excel 2010 and 2013 versions, and as this is an area of rapid change, I have no doubt that the availability of versions and features may have changed by the time this book goes to print, so be sure to research carefully before you purchase your license if you are specifically upgrading with the intention of using Power Pivot.

      The disadvantage of using Power Pivot is that although you don’t need to be a BI specialist to use it, learning how to use Power Pivot is not particularly straightforward even for advanced users. We offer a number of Power Pivot training courses at Plum Solutions through our partners, and there are many videos and online resources that can help you to get started if you decide that Power Pivot is the solution that you need.

      If you are trying to decide whether your Excel skills are advanced enough to consider tackling Power Pivot, here are some questions that will help you to determine whether you are ready to take on Power Pivot. You should:

      ■ Understand and have used Excel’s SUMIF function.

      ■ Have a working knowledge of filtering data in Excel (e.g., Auto or Advanced Filters).

      ■ Know how to deal with multiple criteria (e.g., SUMIFS, SUMPRODUCT, or DBASE functions).

      ■ Be able to import data from third-party databases

2

Mel Glass, David Ford, and Sebastian Dewhurst, “Reducing the Risk of Spreadsheet Usage – A Case Study” (presented at the annual conference of the European Spreadsheet Risks Interest Group, Paris, France, July 2–3, 2009). Available at arxiv.org/abs/0908.1584.