Thanks for checking out a trial of the Advanced Spreadsheet Design course!
Here's a sample lesson to start us off.

Outputs and inputs

Every flexible Excel spreadsheet or model has three primary components: outputs, baselines, and assumptions. Let's examine all three of these pieces and how they work together.

Outputs and inputs

When preparing to build a model, there are three important steps:

The first is to identify the outputs. Without knowing what information you want the model to calculate, it’s impossible to structure your thinking on where to begin development.

In this set of lessons, we’ll follow along as a SnackWorld analyst develops a model to predict company profitability over time. The output of this model will be simple: a projection of SnackWorld profits by year for the next five years.

Now that we know our desired outputs, we can move on to the second step of developing a model: identifying a baseline. We set baselines because it’s difficult to predict the future without having a known starting point sometime in the past. In the model we’re developing, our baseline will be historical SnackWorld financials by year.

By starting with this known information, we’ll make our lives easier in two distinct ways:

First, we’ll establish a frame of reference to help us decide whether our estimates are realistic. After all, if our future profit estimates are off from historical numbers by several orders of magnitude, we’ll know that they’re probably incorrect.

Second, we’ll get a sense for historical growth trends, which will help guide our thinking on what to expect in the future. For example, if historical revenue has grown consistently at 20% year over year for the past five years, we can more easily justify the assumption that it will do so into the future.

Finally, we’ll transition to the third step of developing a model: identifying the assumptions. Assumptions are the inputs that drive the models’ numbers. They define our best estimates of how our baselines will change over time. By applying our assumptions year over year to our baseline, we’ll be able to arrive at a solid estimated output that represents our ‘best guess’ about what the future will bring.

When developing a model, it’s really important to make assumptions ‘flexible’ rather than ‘fixed’. It should be very easy for the end user of the model to modify assumptions on the fly and get a sense for how these modifications impact the output. In practice, this usually means outlining assumptions on their own worksheet, and linking them to your calculations using cell references rather than hard-coding them into formulas. Generally, it should be as easy as possible to change your assumptions after the model is complete.

Advanced Spreadsheet Design: 0 of 23 lessons completed