Using Excel for Business Analysis. Fairhurst Danielle Stein
Читать онлайн книгу.
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.
WHAT