Definition
Creating financial analysis that projects the likely or probabilistic outcomes of recommendations.
- What it is: Financial Modeling is the process of creating a mathematical simulation that will allow a manager to understand what will be the outcome of decisions or variations in factors they can not control. By accurately simulating the income statement, balance sheet, cash flow, and other documents, it is possible to understand the financial implications of your organization's choices and actions by others in the marketplace. The model can be as simple or complex as needed to understand the implication of choices or forecasting future outcomes. It can be used to create a single point forecast, or be the basis of a probabilistic model that will measure the impact of uncertainty or variability in the inputs to the model. It is like a flight simulator used by pilots to determine the outcome of specific actions or decisions they may make. By simulating many different options and conditions the manager can play a virtual video game of his/her business where he/she can die (make poor decisions) many times in the simulation before finding the set of decisions or actions that will bring success, or maximize future profits.
- What does it do: The financial model is used by managers, business owners, financial managers, etc. to better understand their business decisions and the likely outcomes of decisions and marketplace actions. It is often used to make decisions by anticipating the outcomes before the choice is made or the decisions are implemented. Most business decisions should be modeled to choose between alternatives or provide evidence that the recommendation will provide the desired return on the investment required to implement the recommendation.
Uses:
- How is it used: Financial Models create a simulation that describes what will be the outcome of business decisions, options being considered, or how will future trends beyond management's control affect the company or its decisions. Financial Modeling helps business leaders make informed decisions, choose between alternative options, and develop plans to respond to possible scenarios.
- Where: Almost any business decision could be modeled to provide a more informed choice. Some examples of where modeling is often used includes:
- Projecting a company’s financial performance based on specific scenarios or decisions
- Historical analysis of a company and what has driven the profitability or growth
- Choose between alternative business options
- Analyze Project finances to determine if the project should be implemented, or choose between projects
- Deciding on investments like new plants, geographic expansion, or acquisitions and divestitures
- Launching new products or existing products in new markets
- Should you invest in a proposed marketing plan, or which marketing options should you consider
- Should you add people to your sales force (or other personnel in the company) to increase revenues or profits
- Why: Financial Modeling can highlight the best alternative option to be chosen. It helps clarify the risk, rewards, and variability of different options or situations.
Limitations:
- Where it shouldn't be used: Financial Models should not be purported to simulate things that they really can only partially describe. Using a model that only partially describes a situation will give answers that underestimate the impact of some of the variables. That type of model will give the wrong business signals to the managers, causing them to make ill-informed decisions that will not have the projected outcomes as indicated by the model.
- Any restrictions: none noted
- Warnings: The more complex the model is made, the more likely it is that someone will miss using the model to make the wrong decision. Make the model as simple as possible to support the decision to be made.
Step-by-step process:
- Define the Purpose and need of the Model
- What is the desired goal of the financial model?
- Will you be Launching a new product?
- Will this include Integrating an acquisition?
- Choosing between different forms of debt?
- Who will be using the model?
- What will be the form of the model (Complete Financial Statements are challenging and time-consuming to prepare, but at the same time also most instructive to users)
- Create the skeleton of the model calculations
- Determine which data will be set (or assumed), and which data will be variable within the model?
- Gathering data
- Gather the fixed data and build it into the calculation portion of the model
- Gather the most likely data for each variable and build a range around each variable (80% chance that the actual data will fall within the range)
- The range is often bracketed by the conservative estimate (only 10% chance it could be lower), most likely estimate, and the optimistic estimate (only 10% chance it could be higher)
- Consider the possibility that there will be interactions between the variable and try to determine the relationship of the interaction
- Analysis of data
- Build the calculation model (income statement, product cost model, project outcome model, etc.) with no variable data included
- Build an input Table with all variables listed (have all inputs in the same place for a specific calculation table like income statement)
- (optional: have the column that will be linked to from the calculation table be linked to the low, medium and high columns so that you can switch all variables at once)
- Build an output table that links to the calculation model and represents all of the key outputs in one table
- Place the Input table at the top of the spreadsheet on the left and the output table at the top of the spreadsheet on the right (above the calculation table, or on an input/output sheet of its own)
- Interpretation of results
- Run the full range of analysis (each of the three cases) to determine the range of outcomes and the most likely outcome (most likely estimate)
- If you are considering multiple projects, options, or approaches, you can compare the results and the risk and reward ranges (Low to High variables)
- Based on the risk tolerance of the manager they can consider the results and the Bang for the buck outputs (most % profit for what was invested)
- If the ranges of the low, medium, high and inconclusive, you may want to run a probabilistic Monte Carlo simulation (i.e. Crystal Ball or @Risk)
- The Tornado chart from these simulations should indicate the variable input sources which cause the greatest variability in the results
- The results of the tornado chart make cause you to do further research to narrow the input ranges of the most sensitive variables
- Presentation of results
- Flying Bar Charts can be the best way to show the risk, reward and uncertainty of the results (can be created by the Stock (high/Low/ close) charts)
- The Tornado chart shows the greatest sources of sensitivity
- Expected Value chart from the probabilistic Monte Carlo simulation shows the most likely outcomes.
- Bar charts and multiple line graphs can also be effective in conveying the results.
Options Financial Model.xlsx
Financial Model What-If Analysis.xls
