Choose a Sign-in Option
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 forecast 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 managers can play a video game of their business where they 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 it does: 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.
How is it used: Financial models create a simulation that describes the potential outcome of business decisions, options being considered, or how future trends beyond management's control will 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 include the following:
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
Choosing between alternative business options
Analyzing project finances to determine if the project should be implemented, or choosing between projects
Deciding on investments like new plants, geographic expansion, or acquisitions and divestitures
Launching new products or existing products in new markets
Determining whether to invest in a proposed marketing plan, or which marketing options should you consider
Determining whether to add people to your sales force (or other personnel in the company) to increase revenues or profits
Why: Financial modeling can highlight the best option. It helps clarify the risk, rewards, and variability of different options or situations.
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 , the more likely it is that someone will misuse the model to make the wrong decision. Make the model as simple as possible to support the decision.
[Video: How to build a financial model in Excel - Tutorial Course]
[Video: BYU Business Plan Competition Financial Model]
[Video: New York School of Finance Core Financial Modeling Part 1]
Defining 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 also most instructive to users)?
What is the skeleton of the model calculations?
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 variables 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 from the calculation table be linked to the low, medium, and high columns so 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 are inconclusive, you may want to run a probabilistic Monte Carlo simulation (e.g., Crystal Ball or @Risk).
The tornado chart from these simulations should indicate which variable input sources cause the greatest variability in the results.
The results of the tornado chart may 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.
Showing the output table can often be a form of showing the completion of the financial model. A variety of charts, tables, efficient frontiers, and graphs will display the results of the model.
This content is provided to you freely by Ensign College.
Access it online or download it at https://ensign.edtechbooks.org/projectbased_internships/financial_modeling.
