Using Excel for Business Analysis. Fairhurst Danielle Stein

Чтение книги онлайн.

СКАЧАТЬ 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 of a financial model determines the output as well as the calculations 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 СКАЧАТЬ