Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals, + Website

CHAPTER 2

Building a Model

Before launching in to build a model, it’s important to think about model design, structure, layout, and planning. A thought-out design and clear project plan can make a huge difference to the quality and success of the modelling project.

MODEL DESIGN

Design can sometimes be the most difficult part of building a financial model and, in my experience, one of the most difficult to teach and learn. The best way to develop design skills is to critically assess other people’s models, taking note of what works and doesn’t, and then applying it to your own models.

Even the simplest models can become complex if poorly designed, and a well-designed model will be so straightforwardly logical that it will simply speak for itself. It’s pretty easy to just dive in and start building a model without thinking about the implications of the design. It’s not a bad idea to spend some time thinking about the layout before you get started. The layout and structure of the model relate to the look and feel of the model, and how users navigate through the model.

The following examples outline a couple of different challenges you might come across when designing the layout of a model.

Practical Example 1—Assumptions Layout

Let’s say you are creating a high-level five-year forecast. We’ve got 15,065 customers in 2013, and we are expecting that number to increase by 5 percent every year. If you set up your model as shown in Layout Option 1 in Figure 2.1, with only one growth assumption, ...