How to Build a Business Plan with Monte Carlo Analysis
4.2 (10 ratings)
51 students enrolled

# How to Build a Business Plan with Monte Carlo Analysis

Forecasting Free Cash Flow For Ranges of Performance Improvements In Any Business
4.2 (10 ratings)
51 students enrolled
Created by Nicholas Whittall
Last updated 12/2018
English
Current price: \$27.99 Original price: \$39.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
• 1.5 hours on-demand video
• Access on mobile and TV
• Certificate of Completion
Training 5 or more people?

What you'll 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.
• You will be able to do all this rightaway with the Excel spreadsheet provided which performs Monte Carlo analysis with native Excel functions.
Requirements
• To use the Excel spreadsheet you need no more expertise than being able to enter data and rescale the axes on charts.
Description

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 occuring.

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.

Who this course is for:
• Business owners who want to calculate how confident they should be in the future performance of their businesses.
• Strategy managers and directors tasked with forecasting the performance of their businesses.
• Business managers encountering business planning for the first time and who want to know how to do it.
• Students of business and finance.
Course content
Expand all 7 lectures 01:35:27
+ Introduction
1 lecture 17:50

We introduce Monte Carlos analysis by posing the challenge of forecasting the profit from flipping a property; that is, buying a property, remodelling it, and selling it at a profit. The purchase price, selling price, and remodelling costs can only be estimated by ranges of values rather than single values. We contrast the "simple" approach of manually selecting combinations of values to assess the range of profit outcomes with the "sophisticated" approach of using Monte Carlo analysis to calculate the probability of all outcomes and the confidence we may have in any particular level of profit we require.

This summarises our course Forecast Business Outcomes with Monte Carlo Analysis.

We then show how this course extends the concept to forecasting the free cash flow from a business plan. The "simple" approach forecasts the free cash flow as a single value for each year from improvement targets for sales, gross margin, and other "levers". The "sophisticated" approach allows the planner to set target ranges (e.g., sales improvement between 5% and 15% per year) and deduce the range of values the free cash flow that results each year, and the confidence of achieving any outcome of interest.

Preview 17:50
3 lectures 29:36

We show how to:

1. build a cash flow statement to deduce the free cash flow from a business

2. set targets for improvements in the key levers

3. forecast the free cash flow for five years ahead

4. calculate the net present value of these free cash flows.

We provide the spreadsheet to perform these calculations.

11:46

We present the mathematics for the calculation of:

1. The weighted average cost of capital to a business

2. The value of the free cash flows achieved in perpetuity after the last year of the forecast

3. The Net Present Value of the free cash flow forecast by the business plan.

These calculations are embedded in the spreadsheet.

Discounted Cash Flow (DCF) Calculations
08:27

We extend the "simple" business plan we built in lecture 2 to set improvement target ranges and thereby forecast the range of free cash flows the business will generate. We set the ranges using three-point estimates (e.g., minimum, maximum, and most likely sales improvement) and generate triangular and normal distributions from which the Monte Carlo analysis randomly selects values for one thousand trials. Each trial is a forecast for the business plan under one set of improvement levers selected from the target ranges. In this way the spreadsheet calculates the confidence the planner can have in any outcomes (s)he requires.

09:23
2 lectures 39:51

We explain the main components in the spreadheet and their function.

Overview
13:13

We work through the worksheets showing how to use the spreadsheet.