Using Excel for Business Analysis. Fairhurst Danielle Stein
Читать онлайн книгу.
you decide your financial models are not as good as they should be, should you immediately take an advanced Excel course? Whilst this is helpful, there’s a great deal more to financial modelling than being good at Excel!
When considering the skills that make up a good financial modeller, we need to differentiate between conceptual modelling, which is to have an understanding of the transaction, business, or product being modelled, and spreadsheet engineering, which is the representation of that conceptual model in a spreadsheet. Spreadsheet skills are reasonably easy to find, but a modeller who can understand the concept of the purpose of the model and translate it into a clear, concise, and well-structured model is much rarer.
People who need to build a financial model sometimes think they need to become either an Excel super-user or an accounting pro who knows every in and out of accounting rules. I’d argue you need a blend of both, as well as a number of other skills, including some business common sense!
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.
EXAMPLES OF TECHNICAL EXCEL SKILLS QUESTIONS
■ How do I use the appropriate formula? For example, should I use a VLOOKUP or a SUMIF?
■ How do I hide a sheet and then protect it so that the user can’t access it?
■ How do I construct a complex but concise formula?
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.
EXAMPLES OF INDUSTRY KNOWLEDGE
■ Regulatory constraints.
■ Industry standards.
■ Maximum price that can be charged for a certain item.
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 does not necessarily make them good financial modellers. If a highly skilled accountant built a financial model, you would guess that the 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 of 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.
EXAMPLES OF ACCOUNTING KNOWLEDGE
■ How is a profit-and-loss statement structured?
■ How do I construct a cash-flow forecast from my model?
■ How do I turn capital expenditure into a depreciation expense?
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