Skip to main content
Home
The Online Resource for Modern FP&A Professionals
Please register to receive the latest FP&A news, updates and tips Login

Main menu

  • Home
  • FP&A Insights
    • AI / ML FP&A Committee
    • FP&A Trends Digest
    • FP&A Talks Series
    • FP&A Trends E-Books
    • Our Authors
  • FP&A Board
    • Amsterdam
    • Boston
    • Brisbane
    • Brussels
    • Chicago
    • Copenhagen
    • Dubai
    • Frankfurt
    • Geneva
    • Hong Kong
    • Houston
    • Kuala Lumpur
    • London Board
    • London Circle
    • Melbourne
    • New York
    • Paris
    • Perth
    • San Francisco
    • Seattle
    • Shanghai
    • Singapore
    • Stockholm
    • Sydney
    • Tokyo
    • Toronto
    • Washington D.C.
    • Zurich
  • Webinars
    • FP&A Board Connect
  • FP&A Videos
  • About Us
    • Privacy Policy
    • Company Policy
    • Editorial Guidelines
    • Our Sponsors & Partners
  • Contact Us
This block is broken or missing. You may be missing content or you might need to enable the original module.
FP&A Empowerment Survey 2021

FP&A Empowerment Survey 2021

Click here to view details and register

 

Quantitative Modelling and Simulation for Strategic Financial Planning
November 26, 2020

By Jack Xu, Founder at Modtris Financial Modeling

Jack Xu has worked in the financial market for over 20 years starting as a quant in interest rate modelling and derivatives pricing. He worked as trader, portfolio manager, and head of securitized products, focusing on the trading, structuring and modelling of multi-tranche products such as CMO, CLO and CDO until 2008. His work then shifted into Debt Capital Market and credit rating and his modelling focus shifted to corporates as whole. The lack of integrated cash flow models for corporates prompted Jack’s creation of Modtris, a modelling software that provides integrated modelling and projections on corporate.

Jack Xu's LinkedIn profile: https://www.linkedin.com/in/jack-xu-35abb7184/

FP&A Tags
Strategic Finance
Strategic Planning
Modelling and Forecasting

Financial PlanningContrary to short-term budget planning or planning for a specific financial task, strategic financial planning plans for the ultimate goal of a company - avoiding the path to bankruptcy and striving for a sustainable rate of growth. 

Strategic planning is mostly done with qualitative analysis. In this article, we will explore the use of quantitative tools, how to narrow down the number of variables to focus on and what tools can help with managing multiple scenarios.  

The challenge of building action-based models for strategic planning

As I discussed in the previous article Why 3-Statement Forecasting Is Not Enough, the basic step (or unit) of a financial model should not be a time step (annual, quarter or month), but permissible actions that occur in real business life (borrowing and paying down debt, buying and selling inventory…). The model should be capable of computing deterministically the outcomes (balance sheet and income) given a specific input of the future actions.

The following two charts show an example of forward balance sheet and income statement under one specific set of future actions computed by an action-based dynamic model. Note that the end of a company (bankruptcy) can be modelled naturally as when cash level becomes negative.

Chart 1. Action model balance sheet projection (only showing major items. Bankruptcy occurs in 2026 in this projection)

model​

Chart 2. Action model forward income statement    

model​

The challenge in applying this deterministic model to strategic planning lies in the astronomical number of possible combinations of future actions. Even a simple action-based model may have over 30 actions with 50 to 100 variables, each being a time series over the planning horizon, leading to an unmanageably large number of inputs to the model. 

The impact spectrum helps to narrow down the number of variables to focus.

Impact spectrum: addressing the action-based modelling challenge

Impact spectrum is a tool that finance professionals can use to shift each action variables relative to a baseline and compare the change in a chosen outcome.

In a dynamic model, action variables are equivalent to input variables or impact variables. For models fully driven by company fundamentals, an impact variable can be any variable that specifies a financing or operating action – the amount of new debt in the action of borrowing, the percentage of current inventory sold in the action of selling, the tax rate in the action of paying income tax, etc. The outcome variables are what the models predict or simulate, which are essentially all the items on a balance sheet and income statement.

In the example below, there are two possible outcome variables 1) the time to bankruptcy and 2) the growth of cash level over the projection horizon (in case no bankruptcy happens). One can certainly choose other outcome variables such as total growth of sales etc.

“Bankruptcy” is defined as when a company’s cash position becomes negative. It is also referred to interchangeably as “default” in this article.

Chart 3. Sample impact spectrum    

model​

Chart 3 shows an example of a company’s impact spectrum that covers a 20-year horizon. There are in total over 160 impacts from over 80 variables (each variable giving two impacts – up and down in value). The impacts are sorted from left to right in the order of the most negative to the most positive impact.

The red curve shows how much cash has multiplied (right scale) over the projection period in the case without bankruptcy, and the blue curve shows the years to bankruptcy, or default (left scale) if the company does go bankrupt. The solid dot is the baseline case.

Note that a large number of impact variables (impact #40 to #140) make practically no impact to the chosen outcome. Some impact variables (#140 to 160) make positive impacts while some others (#1 to #40) make a negative impact. 

Monte-Carlo simulation as a way to manage various scenarios

The impact spectrum helps to narrow down the number of impact variables to focus on. But even with this help, there are still too many possible future scenarios to go through one by one. This is where Monte-Carlo simulation can be of great help.

In my modelling approach, there are three levels of Monte-Carlo simulation, each becoming more realistic than the last but also requiring much longer computing time.

1. Correlated statistical Monte-Carlo

The most basic Monte-Carlo method treats each selected impact variable as a stochastic variable following certain random process. The random processes, not necessarily always Gaussian, can be derived either from the company’s historical data or from a larger set of industry’s data. Different impact variables may be assumed to be correlated to one another.

In the example below, a Monte-Carlo simulation is made over a 20-year horizon. Chart 4 shows the annual percentage of bankruptcy scenarios (cumulatively, the company has bankrupted in 90% of the cases) and Chart 5 shows the histogram of sales growth in the cases without bankruptcy. 

The company can drill down each case of outcome and find the actual values of the underlying impact variables leading to either the most undesirable or the most desirable outcome such as in Chart 6 and 7. 

Chart 4. Annual percentage of bankruptcy

model​

           
Chart 5. Histogram of sales growth within non-bankrupt cases

model​

Chart 6. A set of impact variables (new debt, new CAPEX, and sales ratio) leading to bankruptcy at year 7 

model​

Chart 7. The same set of impact variables leading to growth without bankruptcy.

model​


2. Self-optimizing Monte-Carlo

The standard statistical Monte-Carlo is not the most realistic simulation because not every impact variable is stochastic. For example, the level of new capital expenditure each year. In reality, the level is decided based on strategic goals and financial projections made at the time. Therefore, in a more realistic simulation, the values of certain impact variables are determined by the values of the realized values of the stochastic variables as well as a set of goals and optimization rules. In other words, they are self-optimizing.

In this case, the company from the last example is simulated again with two self-optimizing variables (new debt borrowing amount and new CAPEX amount), each trying to achieve two goals at each forward time step: 

  1. avoid bankruptcy within a 5-year projection period, and 
  2. given that (1) is true, increase sales growth rate to a prescribed level over the same period. 

To achieve these goals, the simulation must perform another mini-simulation at each time step.

In a self-optimizing Monte-Carlo model, the number of bankruptcy cases are significantly reduced (chart 8). The cumulative bankruptcy percentage is reduced from 90% to 18%. Interestingly, the distribution of sales growth realized in the surviving cases (chart 9) is similar to ones in a standard statistical Monte-Carlo model. Importantly, the manager can now see how the self-optimizing variables respond to stochastic variables in order to achieve the preset goals (chart 10), in what scenarios the responses did and did not work, what improvements may be applied to the goals, etc.

Chart 8. Annual percentages of bankruptcy cases are significantly reduced. Only 18% cumulative bankruptcy over the projection period.

model​


Chart 9. Similar distribution of sales growth among cases without bankruptcy as in the statistical Monte-Carlo

model​

Chart 10. Examples of self-optimizing impact variables that avoid bankruptcy and optimize growth. The level of CAPEX (orange, left scale) and the level of new debt (blue, left scale) cease to be random, but self-adjust in response to the random sales ratio (grey, right scale). 

model​

3. Checked self-planning Monte Carlo

The self-optimizing Monte Carlo model seems to be smarter than purely statistical Monte Carlo. Can it be too smart? Yes. In reality, a company does not have full control in executing its planning. For example, a company may come up with a planned level of new borrowing, but it has to be accepted by the bond investors whose appetite for debt is uncertain. 

In this case, the resulting value of an impact variable is the minimum of the planned level and the market acceptable level. In the interest of not going too long in my article, I won’t discuss further. If anyone is interested, please contact me for more details.

Summary

  • A dynamic (causal) model that is fully driven by fundamental actions is the basis for quantitative strategic planning.
  • A causal model normally comes with an enormous number of input (impact) variables that must be narrowed down using static scenario analysis (sensitivity or impact spectrum).
  • Monte Carlo simulation is a standard technique in finance, but for strategic planning, Monte-Carlo implemented with self-optimizing variables are essential in producing realistic results. 
The full text is available for registered users. Please register to view the rest of the article.
  • Log In
  • or
  • Register

Related articles

Financial Models
6 Best Practice Tips for Building Great Financial Models
November 4, 2020

Financial models are crucial to the business but building them in Excel can be both complicated...

Read more
forecast
Five Methods for Unbiased Finance Forecasting
October 7, 2020

As long as there are humans involved in making a forecast, the forecast will be biased...

Read more
budgeting
How to Improve Sales Budget with Statistical Forecasting
September 2, 2020

This article will discuss how financial planning and analysis (FP&A) professionals can determine the general market...

Read more
Financial Modelling: What is Common Between Football and Finance?
March 27, 2020

This article will focus on is the modeling of a company as a whole, its consolidated...

Read more
Financial Modeling: Types of Models and How to Design Them
March 12, 2020

Starting with the end in mind is one of the simplest ideas that is frequently ignored...

Read more
Sensitivities, Scenarios, What-if Analysis – What’s the Difference?
November 22, 2019

Scenario analysis, sensitivity analysis and what-if analysis are very similar concepts and are really only slight...

Read more
+

Subscribe to
FP&A Trends Digest

We will regularly update you on the latest trends and developments in FP&A. Take the opportunity to have articles written by finance thought leaders delivered directly to your inbox; watch compelling webinars; connect with like-minded professionals; and become a part of our global community.

Create new account

The FP&A Trends Webinar: Magnifying Revenue Planning for Better FP&A Insights

The FP&A Trends Webinar: Magnifying Revenue Planning for Better FP&A Insights

Click here to view details and register

Pagination

  • ‹‹ Previous
  • April 2021
  • Next ››
Su Mo Tu We Th Fr Sa
28
29
30
31
1
2
3
 
 
 
 
4
5
6
7
8
9
10
The Digital Pan-Asian FP&A Board: The Art and Science of xP&A Business Partnering
 
11
12
13
14
15
16
17
The Digital North American FP&A Board: From Traditional to Better and Beyond Budgeting
 
18
19
20
21
22
23
24
The FP&A Trends Webinar: Embracing FP&A Transformation. Key Steps to Consider
 
The Digital London FP&A Circle: Empowering FP&A to Close the "Strategy - Execution" Gap
 
25
26
27
28
29
30
1
The FP&A Trends Webinar: The Combined Power of Human and Artificial Intelligence in FP&A: Roche Case Study
 
The FP&A Trends Webinar. Dynamic and Immediate Revenue Planning in Uncertain Environment: Insights from Microsoft and Emirates Airline
 
 
All events for the year

Future Meetings

FP&A Transformation
The FP&A Trends Webinar: Embracing FP&A Transformation. Key Steps to Consider
April 20, 2021
Digital London FP&A Circle
The Digital London FP&A Circle: Empowering FP&A to Close the "Strategy - Execution" Gap
April 22, 2021
The Combined Power of Human Intelligence and AI in FP&A
The FP&A Trends Webinar: The Combined Power of Human and Artificial Intelligence in FP&A: Roche Case Study
April 27, 2021
Revenue Planning
The FP&A Trends Webinar. Dynamic and Immediate Revenue Planning in Uncertain Environment: Insights from Microsoft and Emirates Airline
April 28, 2021
The Digital Swiss FP&A Board
The Digital Swiss FP&A Board: Building Winning FP&A Teams
May 6, 2021
The Digital Benelux FP&A Board
The Digital Benelux FP&A Board: The Art and Science of FP&A Storytelling
May 11, 2021
FP&A Predictive Analytics
The FP&A Trends Webinar: How to Manage Uncertainty with FP&A Predictive Analytics
May 12, 2021
xP&A Business Partnering
The Digital Pan-Australian FP&A Board: The Art and Science of xP&A Business Partnering
May 13, 2021
FP&A Scenario Planning
The Digital Middle Eastern FP&A Board: The Power of FP&A Scenario Planning
May 24, 2021
The Digital Nordic FP&A Board
The Digital Nordic FP&A Board: The Art and Science of Digitised FP&A Business Partnering
May 25, 2021
Advanced AnalyticsArtificial Intelligence (AI)Behavioural FinanceBetter BudgetingBeyond BudgetingBig DataBrexitCash PlanningCollaborative PlanningCorporate Performance Management (CPM)Cost PlanningCOVID-19DashboardsData ManagementData QualityData VisualisationDriver Based PlanningExtended Planning & Analysis (xP&A)Financial Planning and AnalysisForecasting QualityFP&A AnalyticsFP&A Business EnvironmentFP&A Business PartneringFP&A Case StudiesFP&A DigitalisationFP&A Maturity ModelsFP&A PeopleFP&A SkillsFP&A StorytellingFP&A Talks SeriesFP&A TechnologyFP&A TransformationFP&A Trends E-BooksFP&A Trends InitiativesIntegrated FP&AKey Performance Indictators (KPI)Machine Learning (ML)Management AccountingModelling and ForecastingPlanning and BudgetingPredictive AnalyticsProfitability AnalysisRisk ManagementRobotic Process Automation (RPA)Rolling ForecastScenario PlanningStrategic FinanceStrategic PlanningTeam BuildingWhat is FP&AZero-Based Budgeting (ZBB)

Please register to receive the latest FP&A news, updates and tips.

info@fpa-trends.com​

              

Foot menu

  • FP&A Insights
  • FP&A Board
  • Webinars
  • FP&A Videos

Footer countries

  • Amsterdam
  • Boston
  • Brisbane
  • Brussels
  • Chicago
  • Copenhagen
  • Dubai
  • Frankfurt
  • Geneva
  • Hong Kong
  • Houston
  • Kuala Lumpur
  • London Board
  • London(Circle)
  • Melbourne
  • New-York
  • Paris
  • Perth
  • San Francisco
  • Seattle
  • Shanghai
  • Singapore
  • Stockholm
  • Sydney
  • Tokyo
  • Toronto
  • Washington D.C.
  • Zurich

Copyright © 2021 fpa-trends.com. All rights reserved.

0