Using Excel for Business Analysis. Fairhurst Danielle Stein

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

СКАЧАТЬ 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, СКАЧАТЬ