Discounted Cash Flows in Power BI (part 1)

/ Data Analysis / By

Table of Contents

Overview

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:  

Financial Overview: Discounted Cash Flows (DCF)

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):

  • Captures Fundamental Business Drivers: DCF valuation excels in capturing the essential drivers of a business, such as cost of equity, weighted average cost of capital, growth rate, and reinvestment rate. This approach is particularly effective in estimating the intrinsic value of an asset or business, making it a comprehensive tool for understanding a company’s financial underpinnings​​. (DCF Business Valuation Advantages & Pitfalls, Firmex)
  • Reliance on Free Cash Flows: Unlike other valuation methods, DCF relies on Free Cash Flows (FCF), which are considered a more reliable measure. FCF eliminates the influence of subjective accounting policies and financial “window dressing” often found in reported earnings. This means that the DCF model provides a truer measure of the money available for investors, as it remains unaffected by how expenses are categorized in financial statements​​. (DCF Business Valuation Advantages & Pitfalls, Firmex)
  • Flexibility and Detail: The DCF model is lauded for its extreme detail, incorporating major assumptions about a business, such as cash flow projections and growth rates. This results in a comprehensive analysis that determines the intrinsic value of a business. DCF’s flexibility allows investors to incorporate key changes in business strategy directly into the valuation model, a feature often not present in other valuation approaches​​​​. (DCF Analysis Pros & Cons, CorporateFinanceInstitute.com) (The Advantages and Limitations of Discounted Cash Flow Analysis, SmartSheet.com)
  • Objectivity and Independence from Comparables: DCF’s calculation of a business’s value is independent of market sentiments and does not require comparisons to similar companies. This makes it a more objective method compared to other valuation approaches that might rely on subjective market perceptions or comparables. ​(The Advantages and Limitations of Discounted Cash Flow Analysis, SmartSheet.com)

The following breaks down the DCF model; we will use this paradigm in PowerBI. The corresponding image contains the respective items:

  1. Procure Free Cash Flows to the Firm (FCFF) (Free cash Flow to the Firm (FCFF), Investopedia).
  2. Forecast FCFF future values using anticipated growth rate and duration (Future Value, Investopedia).
  3. Calculate the Weighted Average Cost of Capital (WACC); (Weighted Average Cost of Capital (WACC), Investopedia)
  4. Procure the terminal value of FCFF; this will represent the value of all future cashflows with a sustainable growth rate (Terminal Value, Investopedia)
  5. Calculate the present value of all future cashflows, discounted at the WACC (Present Value, Investopedia)

For more information on Business Valuation or Discounted Cash Flow, please reference the following links: Business Valuation, Investopedia and Discounted Cash Flow (DCF), Investopedia.

Procuring Custom ‘What-if’ scenarios in Power BI

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:

  1. Connect Tabular Editor to your Power BI model, go to file > preferences > features > “Allow unsupported Power BI features”.
  2. Now in tabular editor, change the following calculated table column property: Metadata > Extended Properties > (add) Name: “ParameterMetadata” and Value: “{“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.

Calculating Discounted Cash Flows in Power BI

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.

DCF – Common Elements

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:

  1. Growth the last FCFF value based on the growth rate & growth years using the future value equation (Future Value, Investopedia)
  2. Procure the terminal value using the terminal growth rate & WACC values (Terminal Value, Investopedia),
  3. Discount the terminal value into today’s value using the present value equation (Present Value, Investopedia).
[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

DCF with Yearly Filter Context

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
  • __YearIndex: This procures an index that will be used for future value calculations, present value calculations, and determining when to show the DCF versus terminal value. See column ‘_Year Index’ in the image above.
  • __DCF: This procures the FCFF future value and FCFF present value calculations (i.e., steps 2 and 5, Financial Overview: Discounted Cash Flows (DCF)). You can see them in the columns ‘Step2: FCFF Future Value’ and ‘Step5: FCFF Present Value’ in the image above.
  • __FinalDCF: This will show the present value of all discounted cashflows, replacing the last year DCF with the Terminal Value (i.e., step 5, Financial Overview: Discounted Cash Flows (DCF)). Please see column ‘Final DCF with Yearly Filter Context’ in the image above.

DCF without Yearly Filter Context

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:

  • __DCF generates a series from zero to the growth year minus one (see ‘value’ column below)
  • __DCF then creates virtual column “DCF” which performs both Future and Present Value analysis. The table below breaks down the steps.
  • __DCF then uses the SUMX function to sum all rows in the “DCF” column (see below)
  • Lastly, __DCF will add together the present values for both the Discounted Cashflows and the Terminal Value.

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

Reporting Discounted Cash Flows in Power BI

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.

Conclusion

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!

Downloadable Content

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.

Book Consultation
Book Consultation
Shopping Cart