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.