Читать книгу Using Excel for Business Analysis - Fairhurst Danielle Stein - Страница 8

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 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 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 and/or files (e.g., Access, SQL, MIS systems).

■ Regularly use, adapt, and modify PivotTables (see Chapter 8 for more on PivotTables).

■ Have created calculated fields in PivotTables.

■ Have created and/or modified an Excel Table (a structured reference table, not a data table) (see Chapter 8 for more on Excel Tables).

■ Have access to either Excel 2010 or Excel 2013 Professional Plus.

Although still quite new, Microsoft seems to be devoting a lot of resources to developing the Power Pivot product, so it is likely to gain even more popularity in the near future. It’s worth investing some time in learning it: Being skillful in Power Pivot may become similar to having advanced Excel skills and will be a valuable addition to your CV, and benefit your career as an analyst.

MS Access

Access is probably the closest alternative to Excel, and is worth a mention. There is often some resistance to using Access, and it is certainly less popular than it was a decade or so ago. Prior to the release of Excel 2007, Excel users were restricted to only 65,000 rows, and many analysts and finance staff used Access as a way to get around this limit. With now over 1.1 million rows (and purportedly up to a billion rows if you install Power Pivot), Excel is able to handle a lot more data, so there is less need for the additional row capacity of Access. If you’ve been using Access over the years, you might have noticed that not very much has changed in Access between versions. It seems that Microsoft is investing more of its efforts into the new Power Pivot rather than Access, and therefore we can expect more models in the future to be built using Power Pivot.

Advantages of Excel

■ Excel is included in most basic Microsoft packages (unlike Access, which often needs to be purchased separately) and therefore comes as standard on most PCs. Excel is much more flexible than Access and calculations are much easier to perform.

■ It is generally faster to build a solution in Excel than in Access.

■ Excel has a wider knowledge base among users, and many people find it to be more intuitive. This means it is quicker and easier to train staff in Excel.

■ It is very easy to create flexible reports and charts in Excel.

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

■ Excel easily performs calculations on more than one row of data at a time, which Access has difficulty with.

Advantages of Access

■ Access can handle much larger amounts of data: Excel 2003 was limited to 65,536 rows and 256 columns, and later versions of Excel are limited to around 1.1 million rows (1,048,576 rows, to be precise) and 16,384 columns. Access’s capability is much larger, and it also has a greater memory storage capacity.

■ Data is stored only once in Access, making it work more efficiently.

■ Data can be entered into Access by more than one user at a time.

■ Access is a good at crunching and manipulating large volumes of data.

■ Due to Access’s lack of flexibility, it is more difficult for users to make errors.

■ Access has user forms, which provide guidance to users and are an easy way for users to enter data.

In summary, Access is probably most commonly used for legacy software; databases that have been around for a long time. If it’s a brand-spanking-new solution that you need, consider Power Pivot instead.

MS Project

MS Project is specifically for creating project plans and associated component tasks, assigning resources to those tasks, tracking progress, managing budgets, and monitoring workloads. The user can also create critical path schedules and Gantt charts.

Because the program handles costs, budgets, and baselines quite well, Project could be considered a viable alternative to a financial model, if the purpose of the model were simply to create an actual-versus-budget tracking report. In fact, as with most purpose-built software, if your aim is to track and monitor a project, Project is a superior option to Excel. Of course, creating a project plan and even a Gantt chart is certainly possible in Excel, although it will take longer and be far more prone to error than Project. There are many reasons, however, why users will opt to use Excel rather than Project for a project plan:

■ Project is not included in any of the Office suites and therefore needs to be purchased separately.

■ The plan may need to be accessed, updated, and monitored by different users, who may not be able to use Project due to lack of skills.

■ For a reasonably small project, it’s probably not worth the trouble; it’s simpler to just handle it in Excel.

In summary, the choice between Excel and Project really depends on the size, scope, and complexity of the project plan model you are building. Bear in mind of course that there are many other pieces of project planning software besides Project on the market!

Excel Add-Ins

Add-ins are programs that add optional commands and features to Excel. Although Power Pivot is also an add-in to Excel, it has been discussed in a previous section. There are many add-ins on the market that have been developed specifically for the purpose of financial modelling. For more complex calculations or processing of input, it may be useful to activate or install one or more add-ins, especially tools such as Solver, which are included in your MS Excel licence. Bear in mind that other users will probably not have add-ins enabled, so they will not be able to see how your model has been created or calculated.

Excel add-ins can be categorised according to source:

■ Add-ins such as Solver and the Analysis ToolPak that only need to be activated once Excel has been installed.

■ Add-ins that must be downloaded from Office.com and installed before they can be used (such as Power Pivot).

■ Custom add-ins created by third parties that must be installed before they can be used: Component Object Model (COM) add-ins, Visual Basic for Applications (VBA) add-ins, Automation add-ins, or DLL add-ins.

Excel add-ins from all sources can be used to perform a variety of tasks that assist in the financial modelling process. These add-ins can be broadly defined as:

■ Standard Excel add-ins such as the Analysis ToolPak and Solver.

■ Audit tools.

■ Integration links between Excel and the general ledger system.

The most commonly used add-ins are the Analysis ToolPak and Solver, which are standard add-in programs that are available when you install Microsoft Office or Excel. They are included in the program but are disabled by default, so if you want to use them, you need to enable them.

Prior to the release of Excel 2007, the only way to access certain functions (e.g., =EOMONTH and =SUMIFS) in Excel 2003 was to download the Analysis ToolPak. However, these functions are now standard in Excel 2007 and later, so the Analysis ToolPak is now less commonly used. Other features in the Analysis ToolPak are tools like the Data Analysis ToolPak, which has some powerful statistical and engineering functions not commonly used in financial modelling. Solver, however, is an extremely useful but quite advanced tool for calculating optimal values in financial modelling.

Audit add-ins for Excel are used to ensure the accuracy of data and calculations within a spreadsheet or workbook. They can very quickly identify formula errors by looking at inconsistent formulas, comparing versions, and getting to the bottom of complex named ranges. There are several custom add-ins available both from Microsoft and other parties that will facilitate accuracy by performing formula investigations, precedent/dependent analysis, worksheet analysis, and sensitivity reporting.

Whilst they can assist with checking for formula errors, there are many other types of errors that can be easily overlooked, and using these add-ins can provide a false sense of security. See the section “Error Avoidance Strategies” in Chapter 4 for greater detail.

Integration add-ins allow information from the financial reporting system to be transferred into Excel for further analysis, or data stored in Excel to be transferred into the financial reporting system. These are often used for the purpose of:

■ Transferring information from the general ledger system into Excel for the purposes of reporting and analysis. Many management reports are built in Excel, and extract up-to-date data directly from the general ledger system into the reports.

■ Loading information in the form of journal entries back into the general ledger system. Data is often manipulated in Excel, and then loaded into the general ledger as a journal. For example, if an invoice needs to be split among different departments based on headcount allocation, this calculation might be done in Excel, split to departments in the journal, and loaded into the general ledger system.

The Final Decision

The more sophisticated a financial model is, the more expensive it is to maintain. It is therefore best to use a model with the lowest possible level of sophistication needed to provide a specific solution. For this reason, purchasing a software package, provided it can deliver the desired solution, might be advisable.

Once the decision has been made to purchase a software package, it must be determined which package will provide the best solution as certain solutions may be better provided by particular software packages.

There are many forms of software and Excel add-ins on the market that can be used to create financial models. However, provided that it can deliver an adequate solution, we recommend using plain Excel, as it is easy to use and no extra licenses, training, or software downloads are required. If additional functionality is needed, Excel add-ins may be considered.

32-Bit versus 64-Bit Excel

Since the introduction of Excel 2010 several versions ago, Excel is now available in 64-bit; this has become a topic of discussion and interest for many Excel users. With all the buzz around the 64-bit version, many of us are wondering: Is 64-bit Excel better than 32-bit Excel? Should I make the switch? Is 64-bit MS Excel the solution to poor Excel performance?

First, let’s explore exactly what 32-bit and 64-bit really means. A 32-bit system can process the data in 32-bit pieces whereas 64-bit can process double that. Because more data is being processed at once, the system will operate more quickly and will use the physical memory more efficiently. Installing the 64-bit version of Excel will certainly make your Excel models run faster and more efficiently, but consider whether it’s really necessary before you take the plunge.

You need to consider three components: the software, the operating system, and the hardware. Just because you have 64-bit-capable hardware does not mean you have a 64-bit operating system, or software, but if you want to run the 64-bit, your machine and operating system need to be 64-bit. See below to check which hardware, operating system, or software you are running.

Increasing to the 64-bit version of Excel will increase the speed, capacity, and efficiency of working in Excel significantly. For those working in Office, what this means is that you are no longer limited to 2GB file sizes. This is quite revolutionary for Excel users as, at the moment, Excel file sizes are nowhere near 2GB, simply because anything over around 50MB does not work very efficiently on 32-bit. Most Excel files rarely exceed 20MB, unless you are working in Power Pivot. So if you’re a heavy-duty file-size Excel user, you’ll notice a big difference, but otherwise consider whether you are really going to gain much advantage with the upgrade. If you’re having trouble with your memory, see the section on “Improving Model Performance” in Chapter 10.

The file size supported by 64-bit Excel is limited only by the system capacity (hard drive) and memory (RAM) available for storage and computation, respectively. Also, the 64-bit solutions also offer much better security features than the 32-bit versions.

What Are You Using at the Moment?

To figure out what is on your machine, there are three different things that you need to consider here: first, is your machine 64-bit capable, is the operating system 64-bit, and is the version of Office you’ve installed 32-bit or 64-bit? Whilst you can’t install 64-bit Office on a 32-bit machine, it is entirely possible (and very common) to have 32-bit Office installed on a 64-bit-capable machine. In fact, this is the default option when you install Office 2010, even if you are running the 64-bit edition of Windows. It is also important to note that 64-bit computers can still use 32-bit-based software programs, even when the Windows operating system is a 64-bit version.

1. To check whether your computer is 32-bit or 64-bit capable, go to the Start button, and right-hand click on Computer or My Computer, select Properties, and look for system type. If you’re using Windows 8 and you don’t have a Start button, go to the Control Panel and search for System. On the system tab you’ll be able to see either “x32-based processor” or “x64-based processor.”

2. If you’re running Windows XP, you’re probably on a 32-bit operating system, but on other versions, it’s anyone’s guess. Go to the Control Panel, and choose “System.” In the section where it explains basic information about your computer, it should say “System Type” and you’ll be able to see whether it is 32-bit or 64-bit. By the way, it’s important to know which version you’re running when you install device drivers for your hardware.

3. Last, to check if your version of Excel is 32-bit or 64-bit, if you’re using the latest, Excel 2013, click on the File button, go to Account, and then About Excel. A dialog box will appear with either 32-bit or 64-bit at the top of the screen.

If you’re using Excel 2010, click on the File button, and then Help. About Excel will appear on the right-hand side and underneath it will state the version and whether it is 32-bit or 64-bit.

What to Beware of before Installing the 64-Bit Version

You have established that you have a 64-bit-capable computer. This doesn’t mean you necessarily should instantly install the 64-bit operating system and Office software! Bigger is better, right? Hold on a minute. While 64-bit does improve the capacity of the file size, there are some limitations with the 64-bit, mainly due to its nascence. Despite the fact that it has been around for at least two versions (Office 2010 and 2013), it’s still a relatively new introduction, and as such many add-ins and other pieces of software don’t work well with the 64-bit version of Office.

The 64-bit Excel is a little more stable, but if you are sticking to fairly standard Excel functionality, the switch from 32-bit to 64-bit will probably not impact you; in fact, you probably won’t even notice the increased capacity. For Power Pivot users, though, the additional amount of RAM that 64-bit can access might well come in handy, especially if you are regularly working with data models that contain over a million rows.

However, if you need to use advanced features with add-ons like ActiveX, VBA codes from an older Excel version or other third-party add-ons, you could encounter all sorts of problems. This is because many Excel add-ons are 32-bit versions that are not fully compatible with the 64-bit Excel. Of course, if you upgrade to 64-bit Excel, then you’ll need to upgrade for the rest of Office, and you may encounter similar problems with add-ins for these products as well. Users have particularly complained about add-ins for Outlook, such as not syncing with mobile phones or other devices.

Microsoft has a newer version of VBA called VBA 7, which comes in 32-bit and 64-bit formats that are compatible with both Excel versions. For ActiveX controls and other third-party add-ons, you need to either edit the source code (if you can access it) for 64-bit compatibility or look for an alternative or upgrade.

Last, if you are planning to build a solution or a tool using 64-bit Excel, you need to ensure that your solution will work on both 32-bit and 64-bit Excel. Given that 64-bit Excel is still not as prevalent as 32-bit, building a 64-bit compatible solution could be detrimental to its popular adoption or usage.

In summary, if you are looking at moving to 64-bit Excel, you need to evaluate how you use Excel. Unless you are a data-hungry Power Pivot user with the need to generate Excel files bigger than 2GB, there is no real value in making the switch. The 32-bit Excel versions can and will continue to meet your needs until the 64-bit solution becomes the norm.