How to Build a Business Plan with Monte Carlo Analysis

How to Build a Business Plan with Monte Carlo Analysis

Every business has several levers that determine its financial performance.

For example,

• The business can increase its sales.

• It can improve its margin.

• It can improve the efficiency of its back office activities.

• It can use its assets more efficiently.

• It can do combinations of the above and more.

In corporations, business unit directors are held to account against performance targets set by the main board.

The business unit director should be asking, "How confident can I be that the business will achieve the performance I am being set?"

Suppose the sales team cannot meet the 10% pa improvement target you set them.

Suppose operations cannot meet the 5% pa margin improvement you require.

What will be the free cash flow you should expect?

Enter Monte Carlo Analysis.

This technique allows you to set a range of performance improvements instead of point targets.

For example, if you ask the sales director what is the maximum improvement the sales team can achieve and what must go right to make this, you have a clear idea of the most you can expect and what you have to manage to improve the likelihood of securing it.

If you also ask the sales director what is the minimum improvement the sales team can achieve and what must go wrong for it to be so low you can be confident that this is the worst you should plan for and know what you must mitigate to lower the likelihood of such a poor performance.

If you then ask the sales director what it is the most likely improvement in sales you will have the benefit of the salesperson's expertise and experience.

Instead of the traditional single point target you now have a range of plausible sales performance forecasts defined by these three points:

• there is no chance of performing below the minimum

• there is no chance of performing above the maximum

• there is a calculable chance of achieving the most likely

• there is some calculable chance of achieving every sales improvement performance between the minimum and the maximum.

Now let's ask the Operations Director the same regarding margin.

Let's ask the back office functions the same regarding selling and general administration.

Monte Carlo Analysis allows as many scenarios as you want to be constructed by randomly selecting values for all the levers in your business plan and forecasting the free cash flow for each scenario. By collecting the free cash flow from every scenario, the analysis builds up a picture of the range of possible outcomes and the probability of them occurring.

Not only does the course show you how to do this. It also includes an Excel spreadsheet that calculates one thousand such scenarios from the financial data you provide and the levers you set using three-point estimates.

This delivers five free cash flow forecasts for the next five years:

1. the minimum free cash flow for each year, meaning there is zero chance of achieving less than this

2. the 10% confidence line, which means that 10% of outcomes lie between the minimum and this line

3. the median line, which means that 50% of outcomes lie between the minimum and this line

4. the 90% confidence line, which means that 90% of outcomes lie between the minimum and this line

5. the maximum free cash flow for each year, meaning there is zero chance of exceeding this.

You can see immediately that 80% of outcomes lie between the 10% and 90% lines.

The spreadsheet also calculates the net present value of these forecast cash flows.

The most powerful feature is that the spreadsheet allows you to set a free cash flow or net present value target and responds with the confidence in exceeding it. Thus, if the main board want to set a target you can calculate how confident you can be in achieving it.

The spreadsheet uses all native Excel functions. That is, it requires no add-ins and no Visual Basic.

If you have completed my Forecast Business Outcomes with Monte Carlo Analysis then you will be well prepared to extend your knowledge and skills with this course.

If you have an appreciation of Monte Carlo Analysis or will take the theory behind it on trust, then you will be able to improve your business planning rightaway using this technique and the spreadsheet provided.

Forecasting Free Cash Flow For Ranges of Performance Improvements In Any Business

Url: View Details

What you will learn

• You will be able to forecast the free cash flow from a business by setting levers such as sales and margin improvement, etc
• You will be able to set improvement ranges - say between 5% and 15% year on year improvement in sales - and calculate the confidence in achieving any level of free cash flow in any year
• You will be able to calculate the net present value of the forecast cash flows and the confidence of achieving your target.

Rating: 4.55

Level: Intermediate Level

Duration: 1.5 hours

Instructor: Nicholas Whittall