Saturday, September 19, 2015

Part 2 - How to Fill / Shade between lines in Excel Graph

Thank you everyone for the great comments!

This is a brief follow up to the original article found here: http://www.illuminateandenumerate.com/2013/11/how-to-fill-shade-between-lines-in.html

This follow up clarifies how to set up the data set, and attempts to answer the question about having three data lines.


How to Set Up the Data

In the first post I used the following data set where Bottom was a copy of Hidden Base:


From the first article:
  • The second column, “Hidden Base” and the last column, “Bottom” are the same data.  I actually used =C2, =C3, etc… for column F.

It works better the other way around!

  • Make the Hidden Base a copy of the Bottom as in, =F2, =F3, etc..
This way it's obvious that the Bottom column is your bottom data set.

You can download my example workbook here: Example Excel Workbook



Three Data Lines

Someone asked about having three data lines.  It works as long as any data line only intersects one adjacent data line.  This is because the gap is simply the difference between two adjacent lines.


This is an example where the top data line dips below the middle data line.


When the top line dips below the middle and the bottom, then we see this:

Gap 2 is showing the distance between the top and middle lines.

This is also in the example workbook above.

Thanks again for all the great comments!

Scott


Saturday, March 29, 2014

DevOps and Goldratt's "The Goal"

I have been invited to write a series of articles on the subject of DevOps for a new website.  My first article was about Devops and Goldratt's, "The Goal".

"The Goal" is business novel where Goldratt explains his Theory of Constraints.  If you have anything to do with business or process, it is a great read.

See my article here: http://devops.com/blogs/devops-and-the-goal-2/

Thanks,


Scott

Friday, February 7, 2014

Charter and Time Warner Cable (TWC) - How Can David Buy Goliath?

Charter – How Can David Buy Goliath?

(Note: I am not an analyst so this post should probably be ignored)

Two words: Tax assets. What does this mean to Charter? To find out, let’s begin by diving into their 2013 10-k.

In the Income Tax notes in Charter’s 2013 10-k, we find that Charter has “…$7.7 billion of federal tax net operating loss carryforwards, capital loss carryforwards and suspended losses resulting in a gross deferred tax asset of approximately $2.7 billion.”

This means Charter's $7.7 billion of debt has resulted in almost $3 billion in deferred tax assets. Over a period of several years, according to FASB (Finanical Accounting Standards Board) rules, Charter can use these tax assets to offset income.

This is very simiiliar to the mortgage interest deduction that allows homeowners to reduce their taxable income by the amount of their mortgage interest. The interesting point in this case is that Charter doesn't have to actually spend the money to get the tax break: They have already accumulated the tax asset and now get to use it to defer accounting income.

So, unlike the mortgage interest deduction, these funds are not flowing out of Charter. They are still available to Charter for things like reinvestment in PP&E, such as cable companies or Sirius, or pay down debt.

So Charter can show a loss on their Income Statement, which means they pay no additional income tax, and then use the resulting cash savings for other things.


In the next post, I'll do some analysis of Charter before the merger and more importantly what the NewCo will look like if Charter is successful in their bid for TWC.

Monday, January 13, 2014

Time Warner Cable Valuation

Disclaimer: I am not an analyst and so everything here should really be ignored.  I'm just trying to put some of my Executive MBA knowledge to work.

In our previous posts we determined TWC's CAPM and WACC and now we are ready to determine their valuation based upon discounted cash flows or DCF.

DCF is an accepted method for valuing established companies and even though this incarnation of TWC is only a few years old, the actual business is an established enterprise.

There are a host of assumptions required to arrive at projected cash flows.  Given my inexperience in valuation, I expect my assumptions will differ considerably from professional analysts.

I have used the following assumptions to arrive at a cash flow estimate:
  • Sales grow at 3% (some analysts are predicting 7% growth)
  • COGS constant at 47% of sales
  • SGA constant at 17% of sales
  • Depreciation + Amortization constant at 18.2% of PP&E + intangibles including goodwill
  • Intangible assets constant at 4.1% of sales
  • Goodwill constant at 12.5% of sales
  • PP&E remains at 2012 numbers 
  • CapEx spending remains flat at 2012 levels (note: I can't get the calculated capital spend to equal the stated amount.  It should be new PP&E - old PP&E + depreciation expense, right? But that doesn't balance with the given numbers).
  • The tax rate is fixed at 40%, reflecting their current rate.  

Now we are ready to review the cash flows.  Below is my estimation of cash flows. 



The key takeaways here include the growth rate of 3% over time.  Some analysts are predicting 7% growth in sales over next year or two, but I am being more conservative.  The industry is expected to be stagnant or shrink so 3% seems optimistic in my opinion.

The terminal value is the standard formula of the final cash flow multiplied by 1 + the growth rate and  all of that divided by the difference between the growth rate and the discount rate... Terminal Value  = CF * (1 + tax rate) / (growth rate - discount rate).

With these numbers, TWC is valued at $85 billion, which is about 11x EBITDA.  

TWC's board has indicated they will not settle for anything less than 8x EBITDA.  The interesting thing to me is how the analysts are valuing the company... I would like to see their cash flow estimates.

I hope you found this interesting.









Thursday, January 9, 2014

Histogram in Excel

I’ve had the need to create a histogram in Excel on occasion and every time I’m forced to relearn the steps.  I recently came across a question on superuser.com regarding a histogram and decided to create this guide to remind myself and perhaps help others.

The original question on superuser.com asked how to create a histogram in Excel with the x-axis as the percent distribution.

I'm sure there are many ways accomplish this task, and this is one approach that uses the frequency function and an array-table.

Let’s assume the data is already a percentage and we want the x-axis to represent 10% increments from 10 to 100.



The data could represent anything.  The next step is to create our bins.




Notice that the label for Bins is to the left and not above; this is to let Excel know that Bins is an axis and not a data set when we create the graph later.

My spreadsheet now looks like this:




Now that we have our data and our table, we will use the Frequency function to create the data for our histogram chart.

In cell C:26 enter, =FREQUENCY(B2:B21,B26:B35) and press enter.





It should look like this:



**Magic happens here!**

Now we use the Excel magic known as an array formula.  The first step is to select cells C26 through C35 and type F2 on Windows or CTRL-U on a Mac to enter edit mode:



Now simply press CTRL+SHIFT+ENTER to make this an array formula and Excel does its magic!



Let’s add a label above the data and we are ready to create our histogram.




Now select cells A:25 through C35 (everything you see here) and create a clustered column chart.





And there you have it.  You can dress the chart up in a number of ways.  For example, I like to remove the y-axis.




You can also add percentages to the bars by creating a % of total column and charting that instead of the count:







And the chart looks like this:




There you have it: a simple way to create a histogram in Excel.


Sunday, January 5, 2014

Time Warner Cable (TWC) WACC

In this post we will calculate the weighted average cost of capital, or WACC, for Time Warner Cable.  As mentioned in my previous post, we calculated TWC's CAPM using data from ValueLine and Damodaran.

In this post we will also use TWC's financial documents, specifically their 10-K, retrieved from the SEC's Edgar site.  I'm using their 2013 filings which means our data is a year old.  More current filings could be used to get more accurate picture, but would require some amount of extrapolation.

A company's WACC is their realized, after-tax cost of financing activities.  A company's WACC will vary depending upon their access to the debt and equity markets and the mix of debt to equity.

Generally speaking, debt is cheaper than equity.  This can lead to companies with seemingly high debt ratios having a lower WACC.  It's important to note that WACC, while used in many measurements, is the marginal cost for a firm to borrow the next dollar.  This is one of the reasons that CAPM uses the current Treasury rate rather than the historical numbers.

At the end of 2012, TWC was carrying $24 billion in debt on their balance sheet.  Using ValueLine's numbers for outstanding shares of 283 million (vs. the 315 or so on the 2013 10-K), with a current stock price of $134.17  their market cap is ~$38 billion.

A company's WACC is calculated by adding the debt and equity (market cap in this case) and then dividing both the debt and and the equity by that sum.  This provides the percentage amount that debt and equity constitute of total capital.  This percentage amount is then multiplied by the cost of that segment and then added.

Using round numbers for TWC, we have the following:

Debt           $24 billion (from the balance sheet)
Equity        $38 billion (used ValueLine for outstanding shares and stock price)
Total:         $62 billion in total capital

% of debt:    24 / 62 = 39%
% of equity: 38 / 62 = 61%

Cost of equity is CAPM = 7.54%
Cost of debt = 5.887% (taken from the 10-K... could be calculated as well from 10-K information)
We want the after tax cost of debt to account for the tax savings so the cost of debt equals the After Tax Rate * (1 - Tax Rate) or, 5.887% * (1 - 40% ) = 3.53%.

Debt: 3.5% * 39% = 1.38%
Equity: 7.45% * 61% = 4.6%

WACC = 5.98%

This is Time Warner Cables marginal cost for every additional dollar financed.

In the next post we'll use the WACC to help determine the NPV of future cash flows.


Friday, January 3, 2014

Time Warner Cable's CAPM

As 2014 begins, Charter communications is still looking to buy Time Warner Cable.  This is a David and Goliath story with the smaller Charter trying to take over the larger Time Warner.

Having recently completed an Executive MBA program with a focus on corporate financing, I thought it would be interesting to value Time Warner and Charter to understand the deal.

There are a few steps to value a company and in this post, we'll start with figuring the CAPM for Time Warner (TWC).

The Capital Asset Pricing Model, or CAPM, is a way to value a security or investment.  Said another way, CAPM is the expected or needed return on an investment given it's risk.  CAPM can also be used to derive the Weighted Average Cost of Capital, or WACC, which is used in subsequent valuations of the company.

The formula for CAPM is simple... it is: Risk Free Rate + Beta x Market Risk Premium.  It is usually written as Rf + B (Rm).   The market risk premium has two components, the risk free rate and market required return.  In this case, professor Damodaran's 2013 numbers for the market risk premium (he is considered a credible source).

For Beta I used the Value Line number (beta differs depending on a lot of factors).

Beta = 1
Risk Free Rate = 1.76%
Market Risk Premium = 5.78%

CAPM = 1.76% + 1 x ( 5.78%)  = 7.54%.

This is the expected return on the TWC stock and also TWC's cost of equity which will be used in the next post to determine WACC.

Resources:
Motley Fool article on the Time Warner deal

Damodaran's Website at NYU

CAPM explanation at Investopedia