The Most Un-Likely Case Scenario: an Introduction to Risk Analysis for Businesses

Customer

JB Scherer Consulting Group

Industry

Manufacturing & Consumer Goods

Product(s)

@RISK & TopRank
Z

Application

Future Net Income Forecasting

Jerry Scherer, President of JB Scherer Consulting Group LLC and Palisade software consultant, has extensive knowledge of Palisade software products. He uses @RISK to provide risk modeling services to companies in the Midwest, identifying the key drivers in their plans, budgets, or forecasts. As part of his work in helping companies measure and model risk, he wrote an introductory case study that takes readers step-by-step through quantitative risk analysis, and how different stakeholders will have a different approach to risk. This case study is accompanied by an example model that covers the data and calculations referenced in the article. This is an informative introduction to risk modelling.

Review this case study and accompanying example model for an introduction to risk modelling using @RISK. A trial version of @RISK can be downloaded free of charge to examine the model. Jerry Scherer, President of JB Scherer Consulting Group LLC, prepared the materials to take readers step-by-step through a quantitative risk analysis model.

The Most Un-Likely Case Scenario

It’s Monday, August 15 of some year in the near future at the offices of The Osborne Company, a manufacturer of widgets. The company purchases partially assembled product, adds some manufactured components, and sells nationally.

The management of The Osborne Company is having their weekly staff meeting. It is attended by the Chief Executive Officer (CEO); the Chief Marketing Officer (CMO); the Chief Financial Officer (CFO); and the General Manager (GM).

The CEO kicks off the discussion. “It’s time to start putting some projections together for next year. The market has established that we can’t raise our selling price from its present level of $1.00 per widget. Let me hear some thoughts about unit volume that we can start with.”

The CMO says, “With the new production line operating as well as it has over the last six weeks and the positive reaction we have had to the newly-designed widget that we just introduced, I’ve already given some thought to that and I’m guessing that we ought to be able to sell about 989,300 units.”

The GM, looking a little flustered, says, “Hold on there. I think you’re about 40,000 units too high. My foreman and I have looked at this and believe that 950,000 units is about all that we’re going to be able to do.”

The CEO interjects. “You know, I think that we ought to reach for the moon. I’d say that we strive for almost 1,000,000 units, say 995,000.”

The CFO, sitting quietly to the right of the CEO, is deep in thought. He wonders how he is going to be able to create a financial projection with those disparate projections of unit volume. And, he is perplexed that they haven’t even mentioned that to achieve any of those volume levels, while maintaining inventory, they are going to have to purchase anywhere from 685,000 to 700,000 (most likely case, 690,000) assemblies. These assemblies are sourced from several different suppliers whose prices could be anywhere between 37.5 cents to 48 cents each depending upon supplier availability.

The GM interrupts his train of thought and says, “There’s something else that we have to consider. There is a possibility that there will be an increase in the minimum wage. Of the 9 people that I have out in the shop, 6 six of them earn at the minimum wage of $8.25 per hour. I have 1,896 production hours for each of them, without overtime, that I need to put an hourly rate on. What are the odds that there will be an increase and to what amount?”

There is a lot of nodding of heads and a collective “Hmmmm.”

The group decides that there is a 30% likelihood of an increase in the minimum wage and, if it occurs, $10.50 is the likely level.

The CFO is rolling his eyes when the CEO turns to him and says, “Why don’t you take these figures that we have discussed and put together our most likely case scenario. We’ll meet again next Monday and go over what you have come up with. Oh, and by the way, don’t forget about the fact that we have that mandatory $20,000 bank loan repayment that we have to come up with. There should be no problem meeting that requirement based on the numbers that we have discussed today. Right?”

The CFO nods and says, “It appears that way, but let me see what I can come up with based on today’s discussion.”

The meeting breaks up and the CFO heads back to his office. He has a glimmer of an idea that he would like to try. First, he sets out to create the “most likely case scenario.”

He comes up with the following:

Summary

Jerry Scherer presents an introductory case study that takes readers step-by-step through quantitative risk analysis, and how different stakeholders will have a different approach to risk. This case study is accompanied by an example model that covers the data and calculations referenced in the article. This is an informative introduction to risk modelling.

While he was satisfied that the most likely case scenario suggested that the Mandatory Debt Repayment would be adequately covered, he was dissatisfied with the fact that this scenario did not address (a) the wide discrepancy in unit volume forecasts expressed by the CEO and the CMO; (b) the wide discrepancy in raw material unit purchases; (c) the wide range of prices for the raw materials; and (d) the 30% possibility that there would be an increase in the minimum wage.

He remembered learning about a technique called Monte Carlo simulation in grad school. Also known as the Monte Carlo Method, Monte Carlo simulation lets you see all the possible outcomes of your decisions and assess the impact of risk, allowing for better decision making under uncertainty.

Starting with that most likely case scenario, he gives effect to the discrepancies missing from that scenario by assigning probability distributions to the key model drivers. Using commercially available software (@RISK™, the flagship product from Palisade Corporation that operates as an add-in to Excel), he assigns probability distributions. With better historical data, it would be possible to allow the software to determine the most appropriate probability distribution methods to use. In this case, he is using his own judgement.

(1) The Uniform Distribution is expressed as a minimum and a maximum. Any value between may be utilized in the simulation.
(2) The Discrete Distribution is expressed as a 0 or 1, with odds assigned to each. An IF statement will direct the appropriate wage rate to be utilized.

The probabilities are entered in the Excel spreadsheet for each of the foregoing drivers as follows:

=RiskTriang(950000,989300,995000) =RiskUniform(685000,700000) =RiskUniform(.375,.48) =RiskDiscrete({0,1},{0.3,0.7})

Desired outputs from the simulation are identified. In this case, Net Income and Net Change In Cash are the desired outputs. He decides to run 100,000 iterations of the model. Thus, each such iteration is effectively answering the question – what-if this is the result that occurs. All of those answers are collected and put into buckets, allowing the simulation to quantify the probability of any of those results occurring.

Insofar as Net Income is concerned, the analysis revealed that 90% of the results fall between a Net Loss of $21,656 and a Net Income of $50,414. 10% of the results are outside of these values. Further, the analysis showed that the single most damaging impact to Net Income was the Unit Price of Raw Material Purchases.

Insofar as Net Change In Cash is concerned, the model suggested that the company could really lose its shirt cash-wise, with 90% of the results falling between a cash shortfall of $60,064 and a cash gain of only $4,516. The $20,000 mandatory debt repayment would be in serious jeopardy.

At the meeting on the following Monday, the CFO presented his results and management turned its attention to ways in which to mitigate the risks portrayed in his analysis.

Single-point most likely case presentations do not provide you with this in-depth picture of risk. It could turn out that the most likely case scenario is the most unlikely case.

Join decision-makers around the world who

RELY ON PALISADE.