Financial Modeling in Excel For Dummies
Book image
Explore Book Buy On Amazon
Financial 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.

Why templates can be appealing in financial modeling

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.

What’s wrong with using templates for financial models

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 financial model is its ability to take the business model and represent it financially. 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 financial model that is flexible 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 calculated 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 fill-in-the-blanks 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 different lines of businesses that need to be separated?

Here’s another example: “Office 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-fits-all shirt you bought from the market, your model will probably never fit properly. It’s pretty much guaranteed 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 financial reports have always been the balance sheet, cash flow statement, and profit and loss, but there are many additional reports that might be useful to your business but not necessarily to others. Unfortunately, you won’t find anything beyond standard, 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 find out how increasing marketing by 10 percent affects the bottom line. A good scenario analysis tool built into a financial 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 financial 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 investor 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 efficient to build a model using their templates. Trying to change things becomes difficult 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 financial 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.

About This Article

This article is from the book:

About the book author:

Danielle Stein Fairhurstis a Sydney-based financial modeling consultant who helps her clients create meaningful financial models for business analysis. She is regularly engaged around Australia and globally as a speaker and course facilitator. She received the Microsoft MVP Award in 2021 in recognition of her technical expertise and contributions to the community.

This article can be found in the category: