Using Someone Else’s Financial Model
s a consultant, my favorite scenario is when a client comes to me and asks me to build a new model from scratch. This situation does happen every now and then, but it’s very rare. Normally, clients have a financial model
that isn’t working properly, or they need to update their model from last year, or they need to change the layout or design of their model. Being able to create my own models from scratch every time would be nice, but it’s rarely practical. Instead, most of the time, like most modelers, I’m working with inherited models — models I’ve had to take over from someone else.
When you inherit a model, not only do you inherit the inputs, assumptions, and calculations the original modeler has entered, but you also inherit their mistakes. Using someone else’s model involves taking responsibility for someone else’s work, sometimes from people you’ve never even met! If you’re going to be taking responsibility for someone else’s model, you need to take it over and make it your own. You need to take responsibility for the workings of this model, and you have to be confident that the model is working correctly.
In this chapter, you explore some ways of checking, auditing, validating, and, if necessary, correcting other people’s models so that you can be confident in the models’ results.
Considering Templates for Building a Financial Model
Models are often inherited in the form of templates. Dealing with a model built from a template is quite a different prospect than using a model that has been purpose-built for the job. In this section, I start by taking a look at why a modeler would use templates in the first place. Then I steer you in another direction and tell you why.
Why templates can be appealing
If you describe yourself as a “casual” financial modeler, your usual job might be something else entirely, but part of your professional and personal life means that you need to create a budget or financial statements, or maybe just do some pricing calculations. If this is you, you’re probably looking for an easy way to create a quick financial model that gives you the results you need. Starting to build a full financial model entirely from the ground up, especially when you have no idea where to begin, can be rather daunting. Using a template is a very appealing option because it requires a much lower initial investment of time and money than building a model yourself.
If the business or situation you’re trying to model is extremely simple and/or your business is exactly the same as every other business, you’ll be fine with a template. However, most templates are really just a nicely formatted spreadsheet. There is a bit more to building a robust, responsive, and accurate financial model than plugging a few numbers into a spreadsheet.
If you’re looking for a shortcut to building a financial model, keep in mind what a fully functional, dynamic model does that a basic spreadsheet does not. For more information about differentiating a model from a spreadsheet, see the section in chapter 1 on “Defining Financial Modeling”.
What’s wrong with using templates
When you’re first starting out, a template may be a good way to get going. But think of a template as a car with no engine — it looks great on the surface, but there’s no performance! Here are a few important features you won’t have when you use a template:
» Financial models need drivers: What makes a really good fi model is its ability to take the business model and represent it fi lly. Revenues and expenses don’t just happen — something occurs that makes that revenue
or expense become a reality. Drivers are absolutely critical in creating a fi model that is fl and scalable. For example, if you were to
achieve 10 percent market penetration, and your product was priced at $5, your revenue would be, say, $100,000 per month. Many templates simply show a hard-coded value of $100,000 for revenue, but in your model, you need to know exactly what had to happen in order for revenue to be calcu- lated at $100,000.
Of course, the beauty of this method not only means that investors or other users can trace back to see how the revenue is calculated, but you can also run scenarios and sensitivity analyses on these inputs. What if penetration were 12 percent? What if you decreased the pricing by 10 percent? This sort of analysis is virtually impossible with a simple input of $100,000 for revenue.
» Customized inputs: A fi template has to suit everyone, so in order to meet the requirements of virtually any business model, the inputs must be kept generic (Revenue Item 1, Revenue Item 2, and so on). Of course,
you can change the titles of these line items, but what if you have diff lines of businesses that need to be separated?
Here’s another example: “Offi Rent” — a line item often found in a template — may not apply to your company. Maybe you bought your building, have a mortgage (a liability, not an expense), and need a way to factor in the mortgage pay down and interest portion of each payment. An experienced financial modeler would have no problem working this into a customized forecast. If you’re using a template, you’ll have a hard time getting the template to meet your needs. Plus, you’ll probably spend more time manipulating the template
to meet your needs than you would’ve spent just building it from scratch.
» Scalability: Just like that cheap one-size-fi shirt you bought from the market, your model will probably never fi properly. It’s pretty much guaran-
teed that whatever number of inputs the template designer has chosen won’t be exactly what you need. Inserting or deleting rows may seem simple, but any Excel modeler knows how deadly that can be. Before you know it, you’ve ended up with a model full of dreaded #REF! errors. To avoid this, the template designer likely crated a large number of unnecessary rows and columns just in case you need them. Most templates contain a huge amount of redundant information and unnecessary complexity, which is confusing, takes up memory, and is simply poor modeling practice.
» Specialized functionality: The standard fi reports have always been the balance sheet, cash fl statement, and profi and loss, but there are many additional reports that might be useful to your business but not
necessarily to others. Unfortunately, you won’t fi anything beyond stan- dard, minimum functionality in a template.
You’re not very likely to have much more than very basic scenario analysis functionality built into a template. For example, it would be nice to be able to
change a few inputs and do a scenario analysis to fi out how increasing marketing by 10 percent aff the bottom line. A good scenario analysis tool built into a fi model is really what makes a model useful, because you can easily see what changing not just one variable but multiple variables does to the company.
Templates are great for very surface-level projecting, or “back of the envelope” calculations where a high level of precision is not required. But if you’re serious about your modeling, you’ll want it done correctly and as accurately as possible. Finding a template that will meet your specific needs is nearly impossible. Between the work you’ll do adjusting it and the frustration you’ll experience using it, you’ll wish you’d just built the model from scratch!
Why you should build your own model
Imagine you are working on the due diligence for a potential acquisition by your company of a smaller one. Someone else created a model to project the financials but has since left, and you’re responsible for the financial model now. Your inves- tor asks why your sales projections increase so sharply when the expenses do not. The answer — “because that’s what the financial model says” — is simply not good enough. If you’re responsible for the model, you need to be familiar enough to able to answer a question like that — perhaps not off the top of your head, but you should be able to understand the drivers of the model to provide a timely and insightful answer to these kinds of questions. Blindly accepting the output of a model is foolish and extremely dangerous.
Learning from other people’s models is often helpful, but it’s rarely effi to build a model using their templates. Trying to change things becomes diffi when a formula doesn’t change in the way you expect it to, and a nuance will come back to haunt you because you didn’t understand the fi ncial model to begin with. You may think that a template will help you save time, but in the long run, it will end up costing you more time and lead to potential error. Although building your own model can be time-consuming, you’ll no doubt be far more comfortable with the results. Not only will you be able to vouch for the accuracy of the calculations, but during the model-building process you’ll improve your modeling and Excel skills.
Never trust someone else’s work, or take the outputs of his model at face value. When inheriting a model, your choices are to start over and build your own model from the ground up or validate and verify the existing model to the extent that you’re comfortable to take responsibility for the calculations. Starting over to build your own model from scratch is inefficient and a waste of resources. Unless the model is in extremely poor shape, it’s usually far more efficient to use what you already have — but leave no cell untouched during the process of validation and verification.
HAVING SOMEONE ELSE BUILD
A MODEL FOR YOU
You can always hire a consultant to build a find model for you. If you decide to take
this route, my advice, as a modeling consultant, is to work closely with the modeler and
make sure she understands the problem properly before she begins to build the model.
As she’s building, ask lots of questions and make sure you understand exactly what she’s done and why she’s done it. Don’t just blindly accept any of the outputs. Test the model rigorously throughout the process. Remember: You’re going to the be one using the model and taking responsibility for its outputs in the future, and it’s your job on the line if the model doesn’t refl the situation accurately. Make sure you can make changes to the model yourself so that you don’t have to call the consultant back every time you need a simple edit.
