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 qualified specialist to plan and develop appropriate modelling software.

Which package you choose depends on the solution you require. 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.”.²

32-Bit versus 64-Bit Microsoft Excel

Since the introduction of Microsoft Excel 2010 several versions ago, Excel has been available in 64-bit; this has been a topic of discussion and interest for many Excel users. With all the buzz around the 64-bit version, many of us wonder: 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. Anything over around 50MB does not work very efficiently on 32-bit hardware. 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 offer much better security features than the 32-bit versions.

Spreadsheet and Technical Excel Skills

It's very easy for financial modellers to get bogged down in the technical Excel aspects of their model, get carried away with complex formulas, and not focus on key high-level, best-practice procedures, such as error-checking strategies and model stress testing.

Excel is an incredibly powerful tool, and almost no single Excel user will have the need or desire to utilise most of the functionality this program offers. As with most software, the 80/20 rule applies: 80 percent of users use only 20 percent of the features, although some would argue that 95 percent of Excel users use only 5 percent of the features! Still, there are those select few who understand every in and out of Excel, every single function, and work out how to do practically anything in Excel. Do you need to have this level of Excel skill to become a good financial modeller? Unfortunately, having great software skills doesn't always help when it comes to applying them to a specific area of business. Realise that Excel is used in several capacities, so being an Excel super-user doesn't automatically mean you'll be a super financial modeller. The best financial models are clear, well structured, flexible, and dynamic; they are not always the biggest and most complicated models that use the most advanced tools and functions. Many of the best financial models use only Excel's core functionality.

Having said that, to be a good financial modeller, you do need to know Excel exceptionally well. Those people who maintain that you don't need good Excel skills to be a financial modeller are usually those with weak Excel skills. You should be building a superb model using simple and straightforward tools because you've chosen to make your model clear and easy to follow, not because that's all you know how to do. You don't have to be a super-user—the 99th percentile in Excel knowledge—but you must certainly be above average. A complex financial model might use features in Excel that the everyday user doesn't know. The best financial model will always use the solution that is the simplest tool to complete the task (as simple as possible and as complex as necessary, right?), so the more familiar you are with the tools available in Excel, the easier it will be. An array formula or a macro might be the only way to achieve what you need to achieve, but a simpler solution may well be—and often is—superior. You might also need to take apart someone else's model, which uses complex tools, and it's very difficult to manipulate an array formula or a macro if you've never seen one before. So, if you are considering a career as a financial modeller (as I assume you are), improving your Excel knowledge is an excellent place to start.

Industry Knowledge

One of the fantastic things about financial modelling is that it is applicable across so many different industries. Good financial modelling skills will always stand you in good stead, no matter which industry or country you are working in. Financial modelling consultants or generalists will probably work in many different industries during their careers and be able to build models for different products and services. They will probably not be experts in the intricacies of each industry, however, and that's why it's important for a financial modelling generalist to consult carefully with the subject matter expert for the inputs, assumptions, and logic of the financial model. Don't be afraid to ask lots and lots of questions if the details are not absolutely clear. It's quite likely that the person who has commissioned the model hasn't actually thought through the steps, inputs, assumptions, and even what the outputs should look like until you ask the right question.

Financial modelling consultants are very careful to transfer responsibility for the assumptions to the end user, which is a very sensible course of action. The person building the model is often not the one who has commissioned it or the person who is actually using it. Model builders are often not overly familiar with the product or even the organisation, and they cannot (and should not) take responsibility for the inputs. (See the section “Document Your Assumptions” in Chapter 3 for greater detail on the importance of documentation of assumptions.)

For example, when building a pricing model, the modeller needs to understand the product and how the costs and revenue work. Experience with regulatory constraints will help the modeller to understand the basis of regulation and its components (e.g., cost building blocks, cost index, revenue cap, weighted average price cap, maximum prices, etc.). Understanding of economic concepts, such as efficient cost calculation, return on and of a regulatory asset base, operating costs and working capital, long-run versus short-run marginal costs, and average costs, are other examples of industry knowledge that is useful for the financial modeller.

Accounting Knowledge

Elements such as financial statements, cash flow, and tax calculations can be an important aspect of many financial models. Professional accountants know every single accounting rule and law there is, but this certainly does not by definition make them good financial modellers. If a highly skilled accountant built a financial model, you would guess that the calculations, layout, and structure of the financial statements will be 100 percent correct, but will they be linked properly? If you change some of the inputs, does the balance sheet still balance? Sometimes not! A good accountant, or even someone qualified who has a Master's degree in applied finance, for example, might not be familiar with all the modelling technical tools, even if he or she is a competent Excel user. As with the other modelling skills, you don't need a top level of accounting knowledge to build a financial model. In fact, financial models are often relatively straightforward from an accounting standpoint. You certainly do not need to be a qualified accountant to become a financial modeller, although a good understanding of accounting and knowledge of finance certainly helps.

There are some situations where industry knowledge and accounting are required for financial modelling. For example, in manufacturing or, particularly, in the oil and gas industry, the modeller needs to know whether FIFO (first in, first out) or LIFO (last in, first out) accounting is being used, as this has a big impact on the way that inventory is being modelled. A financial modeller who has never worked in these industries may not have ever heard of FIFO and LIFO, and would probably have no idea how to model them.

Business Knowledge

A modeller with wide-ranging business experience is well equipped to probe for the facts and assumptions that are critical for building a financial model. This is probably the most difficult skill to teach, as it's most easily picked up by working in a management role.

Business acumen is particularly important when commissioning, designing, and interpreting a financial model. When creating the model, the modeller needs to consider the purpose of the model. What does the model need to tell us? Knowing the desired outcome will assist with the model's build, design, and inputs. If, for example, we are building a pricing model, we need to consider the desired outcome—normally, the price we need to charge in order to achieve a certain profit margin. What is an acceptable margin? What costs should we include? What cost will the market bear? Modellers should also have an understanding of economic concepts, such as efficient costs and how these are calculated, an expected return on an asset base, operating costs and working capital, or long-run versus short-run marginal costs.

Of course, the answers to these questions can be obtained from other people, but a modeller with good business sense will have an innate sense of how a model should be built, and what is the most logical design and layout to achieve the necessary results.

Aesthetic Design Skills

This is an area that many modellers and analysts struggle with, as aesthetics simply do not come naturally to left-brain thinkers like us. We are mostly so concerned with accuracy and functionality that we fail to realise that the model looks—and I'm not going to mince words here—ugly! Although it's just a simple matter of taking our time when formatting, most of us could not be bothered with such trivial details as making models pretty, and consequently most models I see use the standard gridlines, font, and black and white colouring that are Excel defaults. I'm certainly not suggesting that you embellish your models with garish colours, but you should take some pride in your model. See the section “Bulletproofing Your Model” in Chapter 7 for some ideas on how to remove gridlines and change some of the standard settings so that your model looks less like a clunky spreadsheet and more like a reliable, well-crafted model you've taken your time over. Research shows that users place greater faith on models with aesthetic formatting than those without, so one of the fastest and easiest ways to give your model credibility is to simply spend a few minutes on the colours, font, layout, and design.

Some aesthetic formatting is critical for the functionality and to avoid error (see the section “Error Avoidance Strategies” in Chapter 4), but mostly it adds credibility and makes your model easier to work with.

Models can become complex very quickly and without a well-planned design, they can be unintelligible. Some basic components of a model should be a cover sheet, instructions, and clearly labelled inputs, outputs, workings, and results. For extremely long and complex models with many sheets, a hyperlinked table of contents is also a valuable addition to help the user navigate the model.

Communication and Language Skills

This is also an area that we analytical, financially orientated people are not always good at. Some analysts like to lock themselves away, working on spreadsheets without communicating with other people. If this is your tendency, then you might need to consider whether financial modelling is a good career choice for you, because there is a surprising amount of human interaction required for most financial modellers.

• Assumptions validation. In order to gain buy-in from stakeholders, the key assumptions and inputs often need to be communicated verbally or in writing. People in various parts of the business should be involved in order to check the accuracy and appropriateness of inputs for inclusion in a model. Stakeholders will often query the assumptions or the way they have been used and provide extremely valuable insight for modellers (particularly for a consultant or modeller with little industry or product knowledge). Performing this task well is a critical step in the modelling process.
• Data gathering. There are some modelling projects where more time is spent gathering and collating data than actually building the model. Holders of information can be guarded about giving access to data, sometimes irrationally, but often it's because they've had bad experiences in the past. This can occur when someone provides estimates off the record and later discovers that those numbers have been used in budgets or other documents to which they are held accountable. So, people can be understandably reticent about providing data when requested for an ad hoc project such as a financial model. A modeller with good communication skills will be able to dig, delve, and coax the information out of them!
• Presentation skills. Senior management, when approving a project, often want to hear about the financial implications of the project from the person who actually built the model, and so modellers are sometimes required to present the key outcomes to a board or executive committee. Being able to distil a 30MB, extremely complex financial model that contains 20 tabs and took you six weeks to build, into three PowerPoint slides and a six-minute summary presentation, can be quite a challenge!
• Client skills. Whether you are a consultant or an in-house employee, working well with clients is a useful skill. Even in-house modellers have clients; every person you work with or for should be considered a client and treated with the same respect and consideration as though they were paying your bill.

  In all of these interactions with other people, financial modellers must show confidence in their model. Build the model to the best of your ability. Use best practices, check for errors, and follow a good and logical thought process, so that when you present or discuss your model, you can do so in a way that exudes absolute confidence. Doing so reduces questions about the accuracy, usefulness, and validity of your model. Be honest about the fallibility of your model and its known shortcomings (let's face it, no model is perfect), but be confident that you have built it to best-practice standards within the limitations of time, data, or scope. This will serve to increase your model's credibility, building your reputation within your company, and, of course, enhancing your career!

Numeracy Skills

Financial models, of course, have a significant mathematical component, and people with good numeracy skills are best suited to them. Solid mathematical skills can be particularly useful in error-checking and sense-checking. The ability to make rough estimates quickly means they will be able to spot errors more easily. If we sell 450 units at $800 each, will our sales revenue be $3.6 million, or $360,000? If we've made a calculation error, the numerate modeller will pick up the mistake much more quickly.

The modeller with numerical aptitude will also have a gut feel for differentiating between critical assumptions that need further verification, and input that is insignificant or immaterial to the model. The less numerate modeller will have to test it manually, and will probably end up with the same result, but it will simply take longer.

General numeracy is a skill that is difficult to teach, and one that can easily be tested for in the recruitment process. Experience working with models over time can drastically improve these skills, as the modeller who is less numerate will learn ways to compensate through error-testing, and these techniques will become acquired, innate habits.

Ability to Think Logically

Modelling is often like programming, and complex logic needs to be interpreted into the language of Excel so that the program can understand and create the modeller's expected results. For example, if we want to show a value, but only if the cell being tested is in the future, we would vocalise it by saying: “Show me the value, but only if the test cell is greater than or equal to today's date”. The way that we need to translate this into Excel's language is to use the formula

Logic is also critical for model layout, design, and the use of assumptions in calculations. The issue of timing in annual models is one that I use often when demonstrating logic in my training workshops. If we are estimating the revenue for a new insurance product, and the assumption is that we acquire 30,000 customers every year, we can't assume revenue for the full 30,000 customers, as not all of them will begin on the very first day of Year 1. Customers will be acquired gradually throughout the year, so we need to take an average in order to calculate revenue. This is an example of how it is very easy to get logic wrong and overestimate revenue by a substantial amount.

Logic is one of those analytical skills that is very difficult to teach, but modellers who have made a logic error (like the one illustrated above) learn quickly from their mistakes and are quite careful to use clear, well-documented logic for others to follow and check.

What's the Typical Background for a Financial Modeller?

A key problem in the financial modelling industry is that modelling is often incorrectly considered a junior task and is often given to inexperienced graduate analysts who undoubtedly have good Excel skills but simply do not have the depth of business experience to create good financial models. If a modeller is able to check off all of the above boxes (i.e., good financial skills, good communicator, able to resolve ambiguity), he or she is likely to quickly move into a more senior position to execute more management responsibilities and spend less time building financial models.

Most financial modellers come from a finance background and have gradually been exposed to industry knowledge. They often become specialists in a field, picking up business acumen, communication skills, and logic along the way. Quite often engineers, project managers, construction specialists, or even scientists who are required to build their own financial models end up pursuing a career in financial modelling. They have the industry and product knowledge and are able to learn the Excel skills but may struggle with the finance side of things. Some would argue that it is easier to teach an engineer or scientist financial skills than it is to teach a financial accountant about the industry, but I think both have a tough job. Every modeller will have strengths and weaknesses, but a good financial modeller will span different skill sets and will have a little bit of everything.

Training Courses

As a specialist financial modelling consultant and trainer, I spend a lot of my time running training courses and, consequently, many people expect me to be a firm advocate of the face-to-face training workshop, right? Not necessarily. I've come under fire from my training partners in the past for publicising this opinion, but I'm not entirely convinced that going on a training course is always the best option for someone keen to improve their financial modelling skills.

Excel is the backbone of any custom-built financial model, and as discussed in the previous section, one of the core attributes of a financial modeller is to have good technical Excel skills. When struggling with financial models, some managers' first reaction is to send their staff on an advanced Excel course to improve their modelling skills. However, with training budgets under constant scrutiny, you really need to make sure that you get the best value out of your training options. Is a training course really what you need?

When considering an advanced Excel course, there are a few points you should consider:

• As financial modellers, our use of Excel is quite narrow. I know it's hard for us to conceive, but there is a whole world of Excel outside the finance industry. Statisticians, database programmers, and engineers, to name a few, are able to use Excel's advanced functions to create non-financial spreadsheets. Most advanced Excel courses are very broad and will cover functions and capabilities that do not apply to your needs as a financial modeller. You may learn a few tricks, but the time and money spent could be invested elsewhere.
• Research shows that a large percentage of the skills learned in training courses are not retained. Will you really remember everything that you learn? A program of continuous, applied learning is often more effective than an intensive training course.
• Are your Excel skills really the problem? Following best practices and mastering the logic behind a financial model is more important than building complex formulas and, for the most part, you will want to keep formulas as simple as possible.