Using Excel for Business Analysis. Fairhurst Danielle Stein

A financial model is more structured. A model contains a set of variable assumptions, inputs, outputs, calculations, scenarios, and often includes a set of standard financial forecasts such as a profit and loss, balance sheet, and cash flow, which are based on those assumptions.

      3. A financial model is dynamic. A model contains variable inputs, which, when changed, impact the output results. A spreadsheet might be simply a report that aggregates information from other sources and assembles it into a useful presentation. It may contain a few formulas, such as a total at the bottom of a list of expenses or average cash spent over 12 months, but the results will depend on direct inputs into those columns and rows. A financial model will always have built-in flexibility to explore different outcomes in all financial reports based on changing a few key inputs.

      4. A spreadsheet is usually static. Once a spreadsheet is complete, it often becomes a stand-alone report, and no further changes are made. A financial model, on the other hand, will always allow a user to change input variables and see the impact of these assumptions on the output.

      5. 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, Revenue in Month 4 could be a result of Sales Price × Quantity Sold Prior Month × Monthly Growth in Quantities Sold. In this example, three factors come into play, and the end user can explore different mixes of all three to see the results and decide which reflects his or her business model best.

      6. A spreadsheet shows actual historical data, whereas a financial model contains hypothetical outcomes. A by-product of a well-built financial model is that we can easily use it to perform scenario and sensitivity analysis. This is an important outcome of a financial model. What would happen if interest rates increase by half a basis point? How much can we discount before we start making a loss?

      In conclusion, a financial model is a complex type of spreadsheet, whilst a spreadsheet is a tool that can fulfill a variety of purposes – financial models being one. The list of attributes above can identify the spreadsheet as a financial model, but in some cases, we really are talking about the same thing. Take a look at the Excel files you are using. Are they dynamic, structured, and flexible, or have you simply created a static, direct-input spreadsheet?

      TYPES AND PURPOSES OF FINANCIAL MODELS

      Models in Excel can be built for virtually any purpose – financial and nonfinancial, business-related or non-business-related – although the majority of models will be financial and business-related. The following are some examples of models that do not capture financial information:

      ■ Risk management: A model that captures, tracks, and reports on project risks, status, likelihood, impact, and mitigation. Conditional formatting is often integrated to make a colorful, interactive report.

      ■ Project planning: Models may be built to monitor progress on projects, including critical path schedules and even Gantt charts. (See the next section in this chapter, “Tool Selection,” for an analysis of whether Microsoft Project or Excel should be used for building this type of project plan.)

      ■ Key performance indicators (KPIs) and benchmarking: Excel is the best tool for pulling together KPI and metrics reporting. These sorts of statistics are often pulled from many different systems and sources, and Excel is often the common denominator between different systems.

      ■ Dashboards: Popularity in dashboards has increased in recent years. The dashboard is a conglomeration of different measures (sometimes financial but often not), which are also often conveniently collated and displayed as charts and tables using Excel.

      ■ Balanced scorecards: These help provide a more comprehensive view of a business by focusing on the operational, marketing, and developmental performance of the organisation as well as financial measures. A scorecard will display measures such as process performance, market share or penetration, and learning and skills development, all of which are easily collated and displayed in Excel.

      As with many Excel models, most of these could be more accurately created and maintained in a purpose-built piece of software, but quite often the data for these kinds of reports is stored in different systems, and the most practical tool for pulling the data together and displaying it in a dynamic monthly report is Excel.

      Although purists would not classify these as financial models, the way that they have been built should still follow the fundamentals of financial modelling best practices, such as linking and assumptions documentation. How we classify these models is therefore simply a matter of semantics, and quite frankly I don’t think what we call them is particularly important! Going back to our original definition of financial modelling, it is a structure (usually in Excel) that contains inputs and outputs, and is flexible and dynamic.

      TOOL SELECTION

      In this book we will use Excel exclusively, as that is most appropriate for the kind of financial analysis we are performing when creating financial models. We often hear it said that Excel is the “second-best solution” to a problem. There is usually a better, more efficient piece of software that will also provide a solution, but we often default to the “Swiss army knife” of software, Excel, to get the job done. Why do many financial modelling analysts use Excel almost exclusively, when they know that better solutions exist? At Plum Solutions, our philosophy is also one of using only “plain-vanilla” Excel, without relying on any other third-party software, for several reasons:

      ■ No extra licences, costly implementation, or software download is required.

      ■ The software can be installed on almost any computer.

      ■ Little training is needed, as most users have some familiarity with the product – which means other people will be able to drive and understand your model.

      ■ It is a very flexible tool. If you can imagine it, you can probably do it in Excel (within reason, of course).

      ■ Excel can report, model, and contrast virtually any data, from any source, all in one report.

      ■ But most important, Excel is commonly used across all industries, countries, and organisations, meaning that the Excel skills you have are highly transferable.

      What this last point means to you is that if you have good financial modelling skills in Excel, these skills are going to make you more in demand – especially if you are considering changing industries or roles or getting a job in another country. In fact, one of the best things you can do for your career is to improve your Excel skills. Becoming an expert developer on a proprietary piece of software is useful, but becoming a highly skilled Excel expert will stand you in good stead throughout your career.

      Excel has its limitations, of course, and Excel’s main downfall is the ease with which users can make errors in their models. Therefore, a large part of financial modelling best practice relates to reducing the possibility for errors. See Chapter 3, “Best Practice Principles of Modelling,” and “Error Avoidance Strategies” in Chapter 4 for details on errors and how to avoid them.

      The other issue with using Excel is capacity; we simply run out of rows, especially in this “Age of Big Data.” Microsoft is trying to keep Excel relevant by introducing Power Pivot, which is a free add-in that is part of your Excel licence if you are using Excel 2010 or above. Power Pivot can handle much bigger data than plain Excel, which gets around Excel’s capacity limitations.

       Is Excel Really the Best Option?

      Before jumping straight in and creating your solution in Excel, it is worth considering that some solutions may be better built in other software, so take a moment to contemplate your choice of software before designing a solution. There are many other forms of modelling software on the market, and it might be worth considering other options besides Excel. There are also a number of Excel add-ins provided by third parties that can be used to create financial models and perform financial analysis. The best choice depends on the solution you require.

      The overall objective