Using Excel for Business Analysis. Fairhurst Danielle Stein

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