/ Data Analysis / By William Rodriguez
The Discounted Cash Flow model is a business valuation model that quantifies enterprise value by discounting all future cashflows to a singular present value. Does that sound like Gobbledygook? Don't worry, we will break this down in more detail later. For now, know that we will be replicating this paradigm of valuation in Power BI!
Here is a preview of the output: A Discounted Cash Flow model and corresponding 'What-if' scenario:
As stated, Discounted Cash Flow (DCF) valuation is one of many valuation models that can be used to value a company. Some of the pros of DCF (generated by ChatGPT4):
The following breaks down the DCF model; we will use this paradigm in PowerBI. The corresponding image contains the respective items:
For more information on Business Valuation or Discounted Cash Flow, please reference the following links: Business Valuation, Investopedia and Discounted Cash Flow (DCF), Investopedia.
Creating what-if parameters in Power BI is straight forward and well documented (Power BI Parameters, Microsoft) (What If Parameters Power BI (2019), Guy in a Cube). However, creating your own customized parameter tables can add value through more robust logic and finer control. There is a downside though as the 'single value' option is not available for filters by default. SQLBI has documented a remedy here: Single Value option in Power BI slicer, SQLBI.
Here is a quick overview:
{"version":0}. Again, follow the SQLBI video for more information.Now you can create a calculated table with your own logic and values. Here is an example where I define the growth rate between 0 and 1,000% with intervals of 1%, and it has a -1 option for future expandability (e.g., if user selects negative one, I can find the average growth over a historical period):
_Growth Rate =
SELECTCOLUMNS(
UNION(
GENERATESERIES( 0 , 10 , .01 )
, {-1}
)
, "Rate" , ROUND ( [Value] , 2 )
)
When I add the data to a filter visual, I update the following settings for single value inputs: Format Pane > Slicer Settings > Options > Style = Single Value. Now you can add your 'what-if' scenario visuals to your report:
In total, seven 'what-if' scenario tables were procured for this scenario. One for each of the following: WACC: Equity Discount Rate, WACC: Debt Discount Rate, WACC: Equity Ratio, Growth Years, Avg Growth Rate, Terminal Growth Rate, and Tax Rate.
We are going to approach procuring Discounted Cash Flows in two varieties: (1) With Yearly Fiter Context (2) Without Year Filter Context. For both approaches, we will be following the five-step DCF model in the section Financial Overview: Discounted Cash Flows (DCF). As there are common elements between the two DCF approaches, we will first cover those in the DCF – Common Elements section below.
The common elements between the two DCF approaches are the General-use variables and Terminal Value calculation.
General-use variables: Inclusive of WACC, Growth Rates, Growth Years, and the Last FCFF calculation. WACC is a straightforward interpretation of the financial function (Weighted Average Cost of Capital (WACC), Investopedia). The last FCFF calculation (step 1, Financial Overview: Discounted Cash Flows (DCF)) simply finds the last FCFF value available in the date range; this metric can be procured differently depending on your data model. The important take-away is to find the last available FCFF value for the last available year or for the last trailing-12-month value. More information on the financial formula for FCFF can be found here: Free cash Flow to the Firm (FCFF), Investopedia.
[WACC] =
VAR __EquityRatio = SELECTEDVALUE('_Equity Ratio'[Rate])
VAR __EquityDiscount = SELECTEDVALUE('_Equity Discount Rate'[Rate] )
VAR __DebtDiscount = SELECTEDVALUE('_Debt Discount Rate'[Rate] )
VAR __TaxRate = SELECTEDVALUE('_Tax Rate'[Rate])
RETURN ( __EquityRatio * __EquityDiscount ) +( (1- __EquityRatio ) * __DebtDiscount * (1 - __TaxRate ) )
[FCFF - Last Available Year] =
CALCULATE( LASTNONBLANKVALUE( 'Company Metrics'[FCFF] , SUM ('Company Metrics'[FCFF]) ) , All ( 'Calendar' ) )
(Other General-Use Variables)
VAR __WACC = [Weighted Average Cost of Capital]
VAR __GrowthRate = SELECTEDVALUE( '_Growth Rate'[Rate] )
VAR __GrowthYears = SELECTEDVALUE( '_Growth Years'[Years] )
VAR __Last_FCFF = [FCFF - Last Available Year]
Terminal Value Calculation:
Calculating the terminal value (step 3, Financial Overview: Discounted Cash Flows (DCF)) is done without the date filter context. This means that the terminal value will render the same value for all dates if date is added to a visual. This is good for card visuals, or when transposing the value to a desired date location (e.g., filter date = "XYZ" will show the Terminal Date Value); this is similar to using the ALL() function in calculate.
To procure the Terminal Value, we will:
[Terminal Value] =
VAR __WACC = [Weighted Average Cost of Capital]
VAR __GrowthRate = SELECTEDVALUE( '_Growth Rate'[Rate] )
VAR __GrowthYears = SELECTEDVALUE( '_Growth Years'[Years] )
VAR __Last_FCFF = [FCFF - Last Available Year]
VAR __TerminalValue =
VAR __TV_FutureValue = CALCULATE( __Last_FCFF * ( ( 1 + __GrowthRate ) ^ __GrowthYears ) )
VAR __TV_TerminalValue = DIVIDE( ( __TV_FutureValue * ( 1 + SELECTEDVALUE( '_Terminal Rate'[Rate] ) ) ) , __WACC - SELECTEDVALUE( '_Terminal Rate'[Rate] ) )
VAR __TV_PresentValue= DIVIDE( __TV_TerminalValue , (1+__WACC) ^ __GrowthYears )
RETURN __TV_PresentValue
RETURN __TerminalValue
We are going to show the code and then break it down into the respective pieces:
[DCF with Yearly Filter Context] =
VAR __WACC = [Weighted Average Cost of Capital]
VAR __GrowthRate = SELECTEDVALUE( '_Growth Rate'[Rate] )
VAR __GrowthYears = SELECTEDVALUE( '_Growth Years'[Years] )
VAR __Last_FCFF = [FCFF - Last Available Year]
VAR __TerminalValue = [Terminal Value]
VAR __YearIndex =
VAR __LastAvailableYear = CALCULATE( MAX ( 'Company Metrics'[Year] ) , ALL ( 'Calendar'[Year] ) )
RETURN SELECTEDVALUE( 'Calendar'[Year] ) - __LastAvailableYear
VAR __DCF =
VAR __Indexed_FV = CALCULATE( __Last_FCFF * ( ( 1 + __GrowthRate ) ^ __YearIndex ) )
VAR __Indexed_PV = DIVIDE( __Indexed_FV , ( (1+__WACC) ^ __YearIndex ) )
RETURN __Indexed_PV
VAR __FinalDCF =
VAR __ShowDCF = __YearIndex >= 0 && __YearIndex <= __GrowthYears - 1
VAR __ShowTerminalValue = __YearIndex = __GrowthYears
RETURN DIVIDE( __DCF , __ShowDCF ) + DIVIDE( __TerminalValue, __ShowTerminalValue )
RETURN __FinalDCF
Like the terminal value calculation above, we need this metric to evaluate without a yearly context. This will allow us to place this metric in a card visual. However, by its nature, DCF requires years to discount in an iterative fashion. Good thing DAX has a full iteration library 😉. We will resolve the issue by materializing an index in memory and dynamically calculating the future and present values based on the materialized row context. Cool? Check out the code:
[DCF without Yearly Filter Context] =
VAR __WACC = [Weighted Average Cost of Capital]
VAR __GrowthRate = SELECTEDVALUE( '_Growth Rate'[Rate] )
VAR __GrowthYears = SELECTEDVALUE( '_Growth Years'[Years] )
VAR __Last_FCFF = [FCFF - Last Available Year]
VAR __TerminalValue = [Terminal Value]
VAR __DCF =
SUMX(
GENERATE(
GENERATESERIES(0,__GrowthYears-1,1)
, ROW( "DCF" ,
VAR __Index = [value]
VAR __FV = CALCULATE( __Last_FCFF * ( ( 1 + __GrowthRate ) ^ __Index ) )
VAR __PV = DIVIDE( __FV , ((1+__WACC) ^ __Index ) )
RETURN __PV
)
) , [DCF]
)
RETURN __DCF + __TerminalValue
Let's breakdown this code:
Materializing this table took a bit of ingenuity to figure if the table was materializing properly. I will need to revisit this later and blog on a helpful pattern.
UPDATE (11/23) The aforementioned 'helpful pattern' has been expanded upon here: DAX: Option for viewing Virtually Materialized Tables in Power BI (Fabric), Analytical Ants
To procure the report, we put different versions of the prior two metrics onto an 'inverted' matrix (i.e., we switched values to rows rather than columns).
For example, to show Terminal Value on the last available year, we simply constrain a measure to only evaluate the Terminal value if the YearIndex equals final growth year.
Lastly, we filter the visual with a DAX pattern that will only show years that are greater than or equal to the last available FCFF year.
This article has equipped you with the financial and practical Power BI knowledge to create your own Discounted Cash Flows models in Power BI! Feel free to download the corresponding report below, and make sure to comment on how this could be helpful in your next project!
Content not available for download currently, please check again when part four of this series is released!
About the Author:William Rodriguez is the founder and principal consultant of Analytical Ants, an analytics company focused on procuring insights via data engineering, data science, and data analysis. William has led multi-million-dollar construction projects, acted as financial controller managing million-dollar monthly check productions, and built and deployed BI infrastructures at companies large ($30B Revenue) and 'small' ($60M Revenue). Academically, he has his master's in business, two undergraduate business degrees, four active Microsoft certifications, and over 70 accredited SQLBI training hours. William loves spending time with his amazing family and knows that he would be nowhere without Christ.
Reach out to Analytical Ants LLC for all your data/analytical needs. We specialize in #DataEngineering #DataScience, and #DataAnalysis.