Modellgestützte Strategie- und Finanzplanung

Step 2: Uncertain prices and correlation

In Step 1 we introduced uncertainty into our financial statement simply by assuming the quantity sold to be uncertain – which is not an unrealistic assumption!

In reality, there are many uncertain variables. In Step 2 we’d like to show you how they are added to a financial statement.

In Step 0 and 1, we assumed prices to be fixed – clearly only a realistic assumption if a good is produced for a pre-determined contract. But even in this rare case, we might have conditions and external drivers that undermine our certain world. It we ignore those (for example inflation!), our projections could be quite far off.

Anyhow: Let’s see how we can add an uncertain market price into our financial statement.

This is the price curve from Step 0. We assumed a fixed price of 20 MU per unit of product A.

Step 0: Price assumed to be constant over the entire project life.

Let us be a bit more realistic! Let’s assume, that we expect the price to be around 20 MU, but depending on market conditions, we are able to sell at a higher or we must sell at a lower price. We expect being able to raise the price at the beginning (1% p.a. until year 5), keeping it stable for 5 years and expect it to go down by 1% p.a. after that. Let’s also assume that the price is clipped at 22 and 19 MU. What we get is the following price distribution (95%-case):

Step 2: Expected price distribution (95%-band) clipped at 22 and 19 MU.

Before we can see the impact of price assumptions on the P&L and the balance sheet, we have to specify the correlation between price uncertainty and quantity uncertainty. Let’s assume that the demand for the product is price sensitive: when we the price increases, the quantites sold have a tendency to decrease and vice versa:

Correlation between quantity and price of -0.45 assumed.

The balance sheet and the P&L now show the impact of the combined variables:

Step 2: Balance Sheet with uncertain prices and quantities

 

Step 2: Profit & Loss with uncertain prices and quantities sold

The combined impact is also visible if we look at details:

Step 2: EBT over time

Step 2: Cash over time

It goes without saying, that it is possible to specify the direct cost function in exactly the same manner. We will not do that in this example in order for it not become more complex than necessary. Against what might be expected, real world models contain normally not more than 5 to 10 key drivers that drive the overall uncertainty in the model.

Take-aways Step 2

  • It is possible to add and consistently integrate further uncertain/distributed values into the financial plan.
  • The charts show upper, mean and lower values for the level of probability selected. The underlying model is systematically calculating the impact of all combinations and cross-combinations of all uncertain variables in the model, independent of the probability selected. There is therefore no need to select and define specific scenarios!
  • In a balance sheet and P&L context, even such simple models are hard to do with conventional tools.
  • The quality of the assumptions must limit the quality of the estimate and not the tool.
  • One should be able to express and specify one’s assumptions openly to foster a sound discussion and to be able to explicitly integrate professional know-how.
  • The discussion should concentrate on the assumptions and not the calculations.

In Step 3, we are going to refine the assumptions by building a deeper market model which replaces our direct quantity estimate that we used until now.