What if...?

Financial Analysis in Power BI

Nick Edwards

Capital Markets Analyst

Scenario analysis

Scenario analysis is a type of financial modeling that evaluates the performance of a dependent variable given specific assumptions determined by the analyst.

A representation of scenario analysis. Independent variables are put into a financial model to determine the dependent variable. For example, sales growth, economy and workforce efficiency assumptions could be used in a financial model create a revenue forecast.

"What if" Analysis

Goal seeking and validation
i.e., what will revenue be if sales increase

Stress Testing

Input extreme scenarios to identify risks
i.e., financial crisis, break even analysis

¹ https://www.investopedia.com/terms/s/scenario_analysis.asp

Forecasting in scenario analysis

Forecasting is the process of predicting or estimating a future outcome based on assumptions.

Often used in scenario analysis
Based on historical data

Quantitative methodologies:

Straight Line
Moving average
Regression analysis

¹ https://online.hbs.edu/blog/post/financial-forecasting-methods

Straight line forecasting

Assumes the same growth rate
Analysts usually use the last period's growth rate

A straight-line forecast equals the previous period's value multiplied by one plus the growth rate.

Example: Forecast 2022's revenue if 2021 revenue was $100,000 and revenue growth in 2021 was 2%.

F = $100,000 * (1 + 2%)

F = $100,000 * (1.02)

F = $102,000

¹ https://online.hbs.edu/blog/post/financial-forecasting-methods

Straight line forecasting

Growth is a "straight line"

straigh_line_graph

Growth rate

Finds the change in x between two periods

growth_rate

Example: Find the growth rate if revenue in 2019 is $50mm and revenue in 2020 is $70mm.

g = (70,000,000 - 50,000,000)/50,000,000

g = (20,000,000)/50,000,000

g = 0.4 or 40%

Let's practice!

Financial Analysis in Power BI