Consistency of headers and flags

A very useful concept in spreadsheet best practice is the use of common and consistent headers in a budgets and forecasts.

This is not the headers in your print settings but rather items that you always want to be able to see (and use) at the top of each sheet in your spreadsheet. It is sometimes referred to flags such as timing flags. As shown below, at the top of each sheet we typically have the following

Headers and flags

The whole point of these is to always have key information visible to you and it must be consistent across all the sheets (on the same lines, pulled from the same source etc ). It makes it easier to see where you are in the model but more importantly it helps simplify your formula.

Common Headers/ Flags we use

  • An example of some common headers we include:
    • A flag which shows a 1 if the company is operational or a 0 if it is not (or a % operational e.g. the company has ramped up to 50% production).
    • The cumulative months from the start of the model to the end of the model (e.g. month 1 to 120)
    • The month number of each column (1 to 12 and repeated)
    • Which year it falls into (or quarter, or semi annual period)
    • The period description of the columns (e.g. month or quarter or year)
    • The date that each column relates to
    • The month number as per the dates (e.g. a 3 if March etc)
  • In all cases once you have set this up, when you are creating a formula lower down, you can link to these headers instead of continuously creating flags all over the model or within each cell. You may have another sheet with flags, but in this case we are referring to the really common items.
  • The suggestioned items above are the standard ones. We generally add one or two more specific to the model. So if we are modeling a mine, we would include tons mined, or if it is a cell phone company then number of subscribers. This is useful because in each of these models, our formulas will regularly refer to these drivers of the business.

Use header rows to simplify formula

Watch the video clip below to see how using these headers can simplify your model.

To follow along go to 1.13

 

Back to: Budgeting and Forecasting with Excel Course > Spreadsheet Best Practice