One of the most pressing questions in business modeling is how to handle inflation

One of the most pressing questions in business modeling is how to handle inflation. Should we use nominal values (with inflation) or should we work with real values (without inflation)? Personally, I prefer nominal values, for a series of reasons I will explain. However, you may configure your spreadsheet to use both.

Inflation is the generalized raise of prices of goods and services over time. As far as I know, there is nothing unaffected by inflation on the long run. This means that all variables of your business model will be impacted by inflation if your time frame is long enough. To be sure, you must decide upfront whether and how you will treat it.

A few good reasons to work with nominal values

1. Historical values are expressed with inflation. As you probably know, forecasts are often based on historical values (we could of course discuss if this is the proper way of making a forecast). But, in any case, working with nominal figures will allow you to compare historical and projected numbers;

2. In most developed countries, inflation is low. This means its corrosive effects over the value of money will not be that hard and the conclusions of the business model will not be distorted - unless you are forecasting for a very long period (more than 10 years, for example). As you can see on the chart below, with the exception of the United Kingdom, in some of the most industrialized countries around the world, the annual inflation rate is usually below 5%;

3. Interest rates are also expressed in nominal values, thus facilitating the forecasting process.

If you prefer, you may create a business model with both nominal and real values by using an inflator. One of the techniques I've used is quite straightforward and can be easily replicated in virtually any business model. This is what you can do:

In a specific tab for external variables create a row with the historical and projected inflation rates for the country in question. Something like this:

These are the input values for the calculation of the 'inflator'. The inflator is the factor that reflects the accumulated inflation for a certain period. For example, if the accumulated inflation from period A (the base) to period B was 5%, the inflator would be 1,05.

In our example above, if we assume the base year as 2011, the inflator for that period would be 1,00 - the starting point.

To calculate the inflator of the remaining periods, we have to compound the inflation over time, adding the inflation of the current period to the accumulated inflation of the previous:

For 2012: 1,00*(1+2,51%) = 1,0251

For 2013: 1,0251*(1+2,75%) = 1,05329

For 2014: 1,05329 *(1+2,90%) = 1,083836

You can do this by entering the following formula in the second period and dragging it all the way to the last:

Now, of course if you choose to work with real values, you will treat inflation as non-existent. In that case, the inflator would be equal to 1,00 for all the periods.

At this point, as you've probably guessed, it would be handy to have a selector on which the whole model depended. The user could choose to work with nominal or real currencies and simulate the conclusions. To do this we need to restate the formula above, making it dependent on the user's choice.

Let's first build the selector with the help of a form control:

You need to enable the developer tab to do this. You can do it, by clicking the 'Microsoft Office Button', and then 'Excel Options'. Search for the 'Popular' category under 'Top options for working with Excel' and select 'Show Developer tab in the Ribbon' check box. Click OK.

Now that you have the developer tab ready, click it and choose Insert Option Button (Form Control). Do this twice since you want to have two options.

You may then format these buttons by right-clicking on them and choosing Format Control. In the dialog box that will pop up you can define a cell to which link each of the option buttons. Let's say we link them to cell E2. Click OK.

Now you can see that cell E2 displays 1 if the first option button is selected or 2 if the second is. This will be the key input for our inflator so keep in mind this cell is important as the whole model will depend on it.

You can now edit the text of the option buttons by right-clicking on them and choosing Edit Text. The cursor will point to the first position of the text tag and you can write the text you want. The option buttons will now look like this:

This means the formulas above will have to be changed to accommodate this feature. We could rewrite the formula like:

=IF($E$2=1;C20*(1+D19);1)And that's it! Now your model can use both nominal and real values as you wish. All you have to do is to make sure all the relevant data is multiplied by the inflator of the respective period