ACCOUNTING & VALUATION: Understanding Net Operating Losses and Their Valuation

Background

Net operating loss (NOL) arises when a company’s tax deductible expenses exceed its total taxable revenues for a particular year. It allows a company to set off its positive pre-tax profits (calculated as per the taxation rules) with the existing NOL balance. This rule for adjusting the NOL balance varies across countries.

How to value NOLs?

NOLs are valued by aggregating the discounted value of the company’s tax savings arising due to the adjustment of the pre-tax profits with the existing NOL balance. The valuation of NOLs depends on whether a company would be able to generate sufficient profits in the coming years to set off its existing NOL balance. For instance, in Illustration 1 below, Company A generates profits in the future years, which are sufficient to set off its loss accrued during year 1. This allows the company to generate total tax savings of roughly USD 10 Mn over the next 3 years. Consequently, the company’s NOL is valued at USD 8.1 Mn, assuming a tax rate of 35% and a discount rate of 8%. Company B, on the other hand, is a loss making company, and assuming that the company doesn’t generate positive profits in the coming years, it doesn’t derive any tax saving. In fact, if there is any concern on the future profitability or the going concern basis of the company, the company’s auditor may suggest to reduce/remove the NOL build up in the balance sheet in the form of DTAs (Deferred Tax Assets).

NOLs per se do not impact the financials or income statement adjustments. The benefit from NOLs is reflected in the form of lesser cash taxes paid by the company, which gets recorded in the cash flow statement. The profitability of the company does not change due to the buildup or usage of NOLs.

Overall, NOLs represent a storehouse of value in the form of future tax savings for any company. Needless to say, the company should have future profitability to earn any benefit from them. An analyst should carefully consider these when valuing a company and adjust the NOPAT in their DCF calculation.

Carry-Back and Carry-Forward – What is the difference?

According to the guidelines provided by IRS regarding the corporate taxation rules in the United States, NOLs can be carried back and set-off 2 years prior to the company’s fiscal year, or can be carried forward for a period of up to 20 years (Method 1). Businesses can also elect to waive the carryback period and instead carry the loss forward for up to 20 years (Method 2). However, ideally, it is better to use Method 1 for adjusting NOLs rather than the latter, since it results in higher present value of savings, although the absolute amount of total savings remains same in both cases. A practical example clarifying this issue has been shown in Illustration 2 below:  

Assuming that year 5 is the current fiscal year in Illustration 1 above – If the company uses Method 1 for adjusting its NOL balance with the pre-tax profits, then it can carry back and set off its NOL with the pre-tax profits in year 3 and year 4 and the remaining balance can be carried forward and adjusted in future years like year 6 and year 7 and partially in year 8. However, if the company uses Method 2, then it can set off its NOL balance with the profits in future years. Although both the methods produce the same amount of gross savings of USD 10.5 MN, the net present value of these savings vary due to timing differences.

CHARTING: How to build a basic Waterfall Chart?

Waterfall charts are widely used in financial modeling to show how a value changes from one level to a final level through a series of intermediate changes. The graph allows the user to observe positive and negative variations in values. For example, an analyst can show how accretion/dilution in EBITDA takes place during the projection period.

Waterfall charts are also known as bridge charts as they show a bridge connecting the end points. A simple waterfall chart is shown below:

  

Making a simple Waterfall chart in Excel

Here we want to visually present how EBITDA changes during the 2014–2018 projection period

Part One: Waterfall Chart Data

1.        Populate the data set that results in a change in EBITDA, including the initial and final EBITDA levels, during the projection period in column C

2.        In column D, select the initial and final EBITDA levels for the chart end points. For example, link cell D7 & D13 with C7 & C13, respectively

3.        Column E captures the blank series that support the floaters, i.e., the intermediate values. To find this value, use formula in Cell E8: =D7+IF(F7="",0,F7)-IF(G8="",0,G8). Afterwards, in Cell E9, use this formula to find the value: =E8+IF(F8="",0,F8)-IF(G9="",0,G9). The formula used in E9 is to be filled down to row 12

4.        Column F captures the up intermediate values. To find the up values, use the formula in Cell F7: =IF(C7-D7>0,C7-D7,"")

5.        Column G captures the down intermediate values. To find the down values, use the formula in Cell G7: =IF(C7-D7<0,-C7+D7,"")

Part One: Create a Waterfall Chart

1.        Select the data B6:B13 & D6:G13 and create a stacked column chart

2.        Hide the blank columns by giving them no border and no fill

3.        After adjusting the chart formatting, the final output will look like the following chart:

 

                                               

Variants of Waterfall Chart

Apart from these basic Waterfall charts, there are other variants.

For better visualization of Waterfall charts, connector lines are added to the basic Waterfall charts

Another variant of Waterfall charts is wherein an analyst wants to show intermediate sum along the way before showing the final cumulative sum. In the above example, the analyst wants to show 2018 EBITDA without investments and total EBITDA, including investments.

We will cover the advanced Waterfall charts in our future posts. Keep reading.

CHARTING: Pareto Analysis

A Pareto chart is a graphical representation that contains both bars and a line graph, where individual values are represented in a descending order by bars, and the cumulative total is represented by the line.

Pareto charts are based on the Pareto principle (also known as the 80/20 rule), which states that 80% of the effects come from 20% of the causes. In other words, 20% of the customers are likely produce 80% of revenue.

Use of Pareto Charts

In financial modeling, Pareto charts are primarily used to represent which customer segment or product line is the most profitable or contribute the most to a company’s revenue.

These charts are widely used in quality management to identify the most important causes of defects. They are used to optimize the processes, product mix or customer mix as they help identify the concentration of buyers, suppliers or processes.  

Making a Pareto Chart in Excel

For example, we have a company that sells its products in the US across 11 states. Thus, to analyze the state-wise revenue data, we use a Pareto chart.

Part One: Pareto Chart Data

1.        Populate the revenue data from each state in Column C

2.        In column D, create a cumulative revenue column. Start with the first amount, i.e., 1,538 or C6. Further on, each amount builds on one before it. For example, in D7, enter “=D6+C7”

3.        In Column E, create a cumulative percentage column. To get the cumulative percentage figure, divide the respective cumulative revenue in column D with the total revenue, i.e., 6,261 or D16

Part Two: Create a Pareto Chart

1.        Select the data from B5:E16 and go to the Insert tab and click on Recommended Charts. Click the All Charts Tab and select the Combo category in the dialogue box

2.        Select the secondary axis box for the cumulative % and click OK

3.        Click on the Chart Area and select Cumulative Revenue Bar and press the delete button

4.        After deleting and adjusting the chart formatting, the final output will be something like the following chart:

Interpretation of a Pareto Chart

·         As shown in the above chart, the company generates 80% of its total revenue by selling its products in 5 states, i.e., Massachusetts, Northern California, Texas, Colorado, and Illinois. Thus, any change in demand in these 5 states can impact the company’s revenue considerably and consequently its cash flow projections.

·         This analysis also provides insights on operational and marketing planning. For example, this could help the company focus on priority states to channelize more efforts by forgoing or reducing operations in the non-performing states. 

EXCEL: How to create switches in excel for building scenarios?

Financial modeling commonly requires to stress test the critical assumptions to sensitize the forecasts. This involves testing the financial model numbers under different scenarios. These different scenarios may range from stress testing simple revenue and cost-side variables such as annual escalation in prices to complex deal or funding-related variables such as debt / equity mix, which often have a significant impact on project cash flows and consequently, IRRs.    

A financial modeler has to build switches in a model to offer flexibility for the user to switch between the different sets of assumptions or model cases as we call them. The variables that have the greatest impact on the business and the ones with uncertain and contrasting future paths are identified as scenarios. As a good modeling practice, these scenarios should be realistic, descriptive, and should not outnumber 3–4 cases. Each scenario should clearly depict the modeler’s view on the macro or micro issues related to the business. While the names should be descriptive, we often resort to labels like upside case, management case, and downside case to capture the different scenarios.

In this post, we will cover a few simple, widely used and efficient methods available in Excel to build switches in financial models. We have used the example of a brick manufacturing company to build various cases. According to the analyst, the revenue drivers for the company — the number of bricks sold and price per brick — could take different paths in future. Accordingly, the model needs to be flexible enough to capture the three cases highlighted by the analyst.

In order to use switches to pull out values from the selected scenario, we need to first populate the assumptions pertaining to the revenue build-up under different scenarios.

Step 1: Data related to the number of bricks sold under different scenarios have been populated in cells B16:I18. Similarly, the prices per brick have been spread in cells B22:I24.

Step 2: Once the data is ready, the values can be pulled out using different switches from the selected scenario.

a.        If Switch: This switch uses the “nested if” statement to pull out values from the selected scenario in rows 19 & 25. For example, if the selected case is “Management Case” then the value from cell E17 is pulled out in E19. Otherwise, the respective value under the different scenarios mentioned in cell C11 is pulled out using the if function. Similarly, the values for the price per brick are pulled out in row 25.

Formula used: =IF ($C$11="Upside Case",E16,(IF($C$11="Management Case",E17,E18)))

b.        Vlookup Switch: This switch uses the “vlookup” function to pull out values from the selected scenario in rows 14 & 20. For example, in cell E14, the vlookup function search values for the selected case, i.e., in cell C6 in column 1 of the selected array, i.e. B11:I13. Once the function finds the value in the selected array, it returns value based on the column number mentioned to retrieve value, i.e. column 4. Similarly, the values for the price per brick are pulled out in row 20.

Formula used: =VLOOKUP($C$6,$B$11:$I$13,COLUMN(E8)-1,)

c.        Choose Switch: This switch uses the “choose” function to return value from a list of values based on a given position. For example, in cell F21, the choose function returns value from the list of selected values, i.e., F18, F19 & F20 based on the given position selected in cell C10. Similarly, the values for the price per brick are pulled out in row 27.

Formula used: =CHOOSE($C$10,F18,F19,F20)

d.        Offset Switch: This switch uses the “offset” function to return value based on the specified row and/or column from the reference cell. For example, in cell F21, the offset function returns value from the reference cell, i.e., F17 based on the given row position selected in cell C10.

Formula used: =OFFSET(F17,$C$10,0)

Step 3: Once the data is pulled out for the selected scenario, the revenue model can be populated by multiplying the number of bricks sold and the price per brick for the given year. 

RESEARCH: What is market pricing in compared to my DCF price? Case study – Wipro Limited

Introduction

The intrinsic value of a stock is the sum of its future cash flows discounted by the cost of capital. Analysts and investors frequently use the Discounted Cash Flow (DCF) valuation to ascertain potential investment opportunities (undervalued stocks). However, the DCF method involves a fair bit of guesstimates, which make the forecast unreliable. In fact, it is next to impossible to accurately forecast the performance of any business. This is one of the reasons why analysts prefer a market-based approach (relative valuation) compared to an income-based approach (intrinsic valuation) due to its simplicity and real time data (in the form of comparables).

Typical DCF inputs include revenue growth, profit margins, reinvestment rate, target capital structure, cost of capital, steady state cash flows, and steady state growth rate. The multiplicity of variables makes the estimated price extremely sensitive to estimates, especially the perpetual growth rate.

While an analyst’s view about a stock is colored with his own outlook on the stock and the sector, stock prices provide a ready reference to check the expectations of the entire market. The trading price reflects the market sentiments and any news related to the company or the sector. By using the techniques of reverse DCF, we can back calculate the variables priced by the market. Often there have been cases where stocks enter into a momentum phase where the market begins to price in a perpetual decline or an irrational high growth rate for its future cash flows. These market estimates are embedded in the trading price of any stock. By reviewing the market embedded variables, one can gauge the divergence between the trading price and an analyst’s estimates about the fair price.

We used the technique of 3-variable sensitivity tables (which is a separate topic in itself and will be covered in future posts) to back calculate market expectations.

Case study – Wipro Ltd.

Step 1: The first step in determining the implied assumptions embedded in the market price is to build an abbreviated DCF model. We pulled the company actuals using third party databases like Bloomberg and applied conservative estimates to project growth. In this case, we have projected the financials using the historical trends. As Wipro is an exporter of IT services, its earnings remain exposed to forex movement. While the company’s recent growth in revenue could be partly due to the weakening of INR against USD, we believe the cycle is upward looking and the company will continue to post healthy results. Hence, we have resorted to the historical trends to project the cash flows for Wipro. The historical trends imply a constant revenue growth of 12% and a constant EBITDA margin of roughly 21%.

All figures in INR million, except per unit data

Source: Bloomberg

Step 2: The next step is to make assumptions regarding the WACC and the terminal value to arrive at a guesstimate price. For this example, we have assumed a constant WACC of 9.3% and an EV/EBITDA exit multiple of 11.5x, which implies an inherent growth rate of roughly 5.5%. The assumptions for WACC and EBITDA margin have been kept in line with the street.

·         WACC = 9.3%

·         Exit multiple = 11.5x

Step 3: The next step is to run the DCF analysis and find out the intrinsic value of the stock. The estimated price comes out to be INR 678.

All figures in INR million, except per unit data

Step 4: As shown in the above table, the upside to the current market price using conservative estimates shows the current price to be undervalued. We understand that the implied share price reflects the weakness of the DCF method in forecasting the cost of capital and growth rates. To cross check our assumptions, we will break down the current market price of Wipro to compare our estimates with the market.

In the following table, we have sensitized the numbers using a 3-D sensitivity table to determine the assumptions factored in by the market. As shown, the current stock price of INR 560 assumes a perpetual growth rate of 6–10%, which reflects the broader GDP movement.

 Note: The share prices in red are in the range of INR 550–570.

Step 5 : Interpretation of 3-variable sensitivity analysis:

• At the present share price of INR 560, the stock price implies a lower revenue growth of 6–10%, with a perpetual growth rate of 5.5%, EBITDA margin of 19–23%, and cost of equity of 9.3%.
• In our analysis, we had assumed a near-term growth in revenue of around 12%, with the perpetual growth rate aligned more with the broader economy (long-term potential of India’s GDP growth), EBITDA margin of 21% (historical average), and cost of equity of 9.3% based on which our DCF value for Wipro is INR 678.
• The primary difference between our estimated price and the trading price remains the expectation on the revenue growth trends between 2014 and 2020. We believe the company has a higher potential and the recent correction in stock relative to the Sensex looks unwarranted as the market is pricing in a very low revenue growth for a technology stock like Wipro

DISCLAIMER: This  example is used to explain how we can deduce the market assumptions for perpetual growth priced-in in the share price of any company and see if it is fairly priced compared to an anlyst’s assumptions. We don’t recommend any trading in the stock based on this analysis and advise to use your own bottom-up model to validate your assumptions.

EXCEL: Sensitivity Analysis

What is Sensitivity Analysis?

Sensitivity analysis is a frequently used analysis tool in financial modeling that enables an analyst to gauge the impact of any changes in critical variables on the output. 

Sensitivity Analysis in Excel

Excel contains various inbuilt features, such as data table, scenario manager, and solver, which allow us to perform sensitivity analysis. 

Data table is the most widely used tool while performing sensitivity analysis.

One-Variable Data Table (1-dimensional) to calculate the NPV value

For evaluating the NPV of any project, we are generally faced with multi-period cash flows and a single discount rate. To better analyze the NPV of a project under different discount rate scenarios, we can use data tables. This single-variable analysis will help us understand the delta on the NPV with marginal changes in the discount rate.  

For example:

Step 1: Calculate the NPV of a project in cell #D10 using the XNPV function assuming a discount rate in cell #D4

Figure 1.

Step 2: After calculating the NPV, let’s assume the analyst wants to see how the NPV gets impacted by changing the discount rate. To get the desired output, the analyst can select data tools from the ‘What if’ analysis and use the one-variable data table to perform sensitivity analysis.

• Enter the list of discount rates that the analyst wants to evaluate in the input cells #D16:D20
• Enter the formula of the NPV in cell #E15
• Select the range of cells that contain the formula and the desired input values; i.e., select the range #D15:E20
• Insert the data table by inputting the column input cell as #D4. As a result, the NPV values at different discount rates would automatically appear in cells #E16:E20

Figure 2

Interpretation of one-variable sensitivity analysis:

• The NPV calculation drastically changes with the change in the discount rate. Consider the data table showing the sensitivity of the net present value with a change in the discount rate.
• As shown in the above analysis, the net present value of the project cash flows at a discount rate of 10% is $654.1. As the discount rate increases, the NPV of the project declines; however, the decline is not material in this case and this adds credibility to the analyst’s projections. Thus, a 50-bps increase in the discount rate decreases the NPV of a project by roughly 1%.

Two-Variable Data Table (2-dimensional) to calculate the impact on IRR

This type of sensitivity analysis is used when an analyst wants to analyze the impact of changes in two different variables simultaneously.  

Let’s consider an example where we need to calculate the impact of changes in the exit year and the exit multiple on the IRR. To analyze the impact of the above two variables, the analyst would use a two-variable data table to perform sensitivity analysis.

Step 1: Calculate the total cash flows available to equity holders in row #22. Calulate the IRR in cell #C24 based on the cash flows available to equity holders.

Figure 3

Step 2: After calculating the IRR, let’s assume an analyst wants to see how the IRR gets impacted by changing the exit multiple and the exit year.

• Enter the list of exit years that the analyst wants to evaluate in row #D30:G30 and the exit multiples in column #C31:C35

• Type the IRR formula exactly above the column variable and beside the row variable, i.e., in cell #C30 (note that the output cell is at the intersection of the variables in the table)
• Select the range of cells that contain the formulas and the desired input variables. Select the range #C30:G35
• Insert the data table by inputting the row input as #C4 and the column input as #C5. The result — i.e., the IRR — would automatically update in row #D31:G35

Figure 4

Interpretation of two-variable sensitivity analysis:

• The calculation of the IRR is quite sensitive to the exit multiple and the exit year. Consider the data table showing the sensitivity of the IRR with changes in the exit multiple and the exit year.
• An investor seeking to exit investments in Dec 2018 at an exit multiple of 6.5x can expect an IRR of 20.1% 
• As shown in the above analysis, the returns from the investment increases by ~5%,  with an increase of 0.5x in the exit multiple for an investor with an exit in Dec 2018