Welcome to our series of free financial modeling tutorials!

In this series, you’re going to get 3 free financial modeling tutorials on the Discounted Cash Flow (DCF) Analysis and how to use it to value Michael Hill, a jewelry retailer.

Plus, you’ll get even more free bonus material, quick reference guides, and additional videos… if you watch your inbox closely.

Check out the video above and you’ll learn more about what makes Breaking Into Wall Street different and what it will do for you, and you’ll see how our clients have won internships and full-time jobs at banks and investment firms with our courses.

Coming up next, you’ll receive Part 1 of the tutorial series, which explains how to calculate and project Unlevered Free Cash Flow in a DCF.

Also, please make sure you confirm your email address following the instructions in the video above, or you will not receive the rest of the free tutorials from us.

-Brian

• 0:49: What Terminal Value Means
• 4:00: Picking the Right Numbers for Terminal Value
• 9:54: Discounting Terminal Value and Calculating the Implied Share Price
• 13:43: Sensitivity Tables and Valuation Conclusions
• 21:38: Summary and Preview

What Terminal Value Means

As with the previous two lessons, everything here goes back to the big idea about valuation and the most important formula in finance:

Put simply, this “Company Value” is the Terminal Value!

But to calculate it, you need to get the company’s first Cash Flow in the Terminal Period, and its Cash Flow Growth Rate and Discount Rate in that Terminal Period as well.

So, it’s not quite as easy as just looking at a DCF and inputting all the numbers straight from there.

In an Unlevered DCF, this all-important formula becomes:

Terminal Value = Unlevered FCF in Year 1 of Terminal Period / (WACC – Terminal UFCF Growth Rate)

You rarely forecast the actual Terminal Period in a DCF, so you often project just the Unlevered FCF in Year 1 of the Terminal Period and use this tweaked formula instead:

Terminal Value = Final Year UFCF * (1 + Terminal UFCF Growth Rate) / (WACC – Terminal UFCF Growth Rate)

As shown in the slide above, this “Terminal Growth Rate” should be low – below the long-term GDP growth rate of the country, especially in developed countries such as Australia, the U.S., and the U.K. You might use numbers such as 1%, 2%, or 3%, depending on the region.

You can also calculate the Terminal Value with the Multiples Method and multiply the company’s final-year EBITDA, EBIT, or NOPAT by a valuation multiple, such as 5x or 10x:

You can find the appropriate multiples by searching for similar companies to Michael Hill, also called “comparable public companies” or “public comps.”

If similar companies trade at multiples of 10x their operating income or 10x their EBITDA, then it’s reasonable to assume that Michael Hill might trade in a similar range in the future.

You can use either method, or both methods, to estimate Terminal Value, but the important part is what comes next: once you have your initial estimate, you must cross-check it by entering the numbers in Excel and looking at the growth rate or multiple implied by your initial guess.

Then, you need to tweak the assumptions a bit to make sure the implied growth rates and multiples make sense.

Once you’ve tweaked your assumptions, you then discount this Terminal Value to its Present Value, add it to the Present Value of the Unlevered Free Cash Flows, and then back into the company’s Implied Share Price from there.

And then at the end, you can set up sensitivity tables to look at this number in different cases and see the full range of values the company might be worth.

How to Calculate Terminal Value in Excel: Picking the Right Numbers

You start by looking up data on the expected long-term GDP growth rate in the company’s country and the range of forward EBITDA multiples for the comparable public companies.

Then, you make initial guesses for the Terminal FCF Growth Rate and the Terminal Multiple that are slight discounts to these numbers.

For example, if long-term GDP growth is expected to be 2-3%, you might pick 1-2% for the Terminal FCF Growth Rate.

If the comparable companies trade at EBITDA multiples of 8-10x, you might pick 6-7x for the Terminal Multiple.

Here, it’s safe to say that Australia’s long-term GDP growth will be between 2% and 3%:

And then we look at the comparable public companies for Michael Hill and see that the median forward EV / EBITDA multiple is 7.7x:

Therefore, our initial guesses here are 7.5x for the Terminal Multiple and 1.5% for the Terminal Growth Rate:

The Implied Terminal EBITDA Multiple is easy – divide the Terminal Value from the Perpetuity Growth Method by the Final Year EBITDA.

The Implied Terminal FCF Growth Rate is more difficult because you must use algebraic manipulation to flip around the equation and solve for the growth rate if you have everything else. After rearranging the equation, it comes out to:

Implied Terminal FCF Growth Rate = (Terminal Value * Discount Rate – Final Year FCF) / (Terminal Value + Final Year FCF)

You can see the full derivation in these slides.

You tweak these assumptions until you get something reasonable for the Terminal FCF Growth Rate and the Terminal Multiple (or just one of them if you’re calculating Terminal Value using only one method).

In this case, our initial guesses are reasonable because the implied growth rate and multiple both make sense:

How to Calculate Terminal Value: Discounting Terminal Value and Calculating the Implied Share Price

Terminal Value represents Michael Hill’s implied value 10 years in the future, from that 10-year point into infinity – so, we need to discount that to what it’s worth today, i.e., the Present Value.

Then, we add the PV of the Terminal Value to the PV of the Unlevered Free Cash Flows to get the Implied Enterprise Value:

This represents the value of the company’s core-business Assets to all investors in the company, but since Michael Hill is public, we want to find its value on a per-share basis – which means that we need its Implied Equity Value as well.

Since Equity Value represents the value of all Assets but only to common shareholders, we move to Implied Equity Value by adding non-core-business Assets and subtracting items that represent other investor groups:

In this case, the DCF shows a premium of nearly 150%, which indicates that the company may have been dramatically undervalued by the public markets as of the time of this case study.

Sensitivity Tables and Valuation Conclusions

With WACC, we generally want our actual value of 9.16% to be in the middle of the range and to go up and down based on the range of values we found in the Discount Rate calculations. So, maybe 8.8% – 9.4%, but we want to make it a bit wider than that to span at least ~2%.

We start just below 8.0% and go up to just below 10.5%.

With the Multiples and Growth Rates, it’s a similar concept – our actual value should be in the middle of a range of plausible values. So, maybe 7-8x for the multiples and 1-2% for the growth rates; we go a bit smaller than those to fit the tables:

We then link to the Implied Share Price cells in the top left-hand corner of both tables, and create the sensitivity tables with Alt, D, T. If this doesn’t work for you, go into Options (Alt, T, O), Formulas, and set Workbook Calculations to “Automatic” or just press F9 to refresh the spreadsheet.

Is Michael Hill Dramatically Undervalued?

We can look at the football field valuation chart to get a quick answer to this one:

Yes, by any metric, this company seems extremely undervalued as of the time of this case study – we never even get close to its current share price of $0.68, let alone $1.00.

The other valuation methodologies confirm this finding.

However, we’re not certain that it’s undervalued by 150% because it’s not clear that we’ve handled the exit of its U.S. business correctly.

For example, are any of the Assets on MHJ’s Balance Sheet for “Discontinued Operations”? What are the remaining costs to shut down the U.S. segment? Even with all that, the company is still probably undervalued, but we don’t know by how much.

Our best guess is that it’s probably more like 30-50% undervalued – and we also haven’t looked at other cases/scenarios here, such as a prolonged restructuring or an economic downturn in the ANZ markets (and Canada!).

Given the current valuation, it’s not a great idea to pursue a sale of the company now since it’s quite undervalued, and public companies are sold based on premiums to their current share prices.

Getting a premium above 20-30%, or even up to 50%, is highly unlikely, so Michael Hill would be unlikely to receive anything close to what it’s worth.

Raising equity capital also doesn’t make sense due to the company’s low valuation – it’s best to raise equity at higher valuations to reduce dilution.

Raising debt might make more sense if the company needs it to open more stores or buy smaller companies, but we think the best option, for now, is to focus on organic growth and prove that it has exited the U.S. successfully and turned itself around.

Once it has done that, management should think about expanding the store count or pursuing debt-funded add-on acquisitions.

• 0:39: Intuitive Explanation of the Discount Rate and WACC
• 5:53: Discount Rate Assumptions
• 11:43: How to Calculate the Cost of Equity
• 21:05: How to Calculate and Use WACC
• 24:55: Summary and Preview

Discount Rate Meaning and Explanation

The Discount Rate goes back to that big idea about valuation and the most important finance formula:

The Discount Rate represents risk and potential returns, so a higher rate means more risk but also higher potential returns.

The Discount Rate also represents your opportunity cost as an investor: if you were to invest in a company like Michael Hill, what might you earn by investing in other, similar companies in this market?

Normally, you use something called WACC, or the “Weighted Average Cost of Capital,” to calculate the Discount Rate.

The name means what it sounds like: you find the “cost” of each form of capital the company has, weight them by their percentages, and then add them up.

“Capital” just means “a source of funds.” So, if a company borrows money in the form of Debt to fund its operations, that Debt is a form of capital.

And if it goes public in an IPO, the shares it issues, also called “Equity,” are a form of capital.

How to Calculate Discount Rate: WACC Formula

The formula for WACC looks like this:

WACC = Cost of Equity * % Equity + Cost of Debt * (1 – Tax Rate) * % Debt + Cost of Preferred Stock * % Preferred Stock

Finding the percentages is basic arithmetic – the hard part is estimating the “cost” of each one, especially the Cost of Equity.

The Cost of Equity represents potential returns from the company’s stock price and dividends, and how much it “costs” the company to issue shares.

For example, if the company’s dividends are 3% of its current share price, and its stock price has increased by 6-8% each year historically, then its Cost of Equity might be between 9% and 11%.

The Cost of Debt represents returns on the company’s Debt, mostly from interest, but also from the market value of the Debt changing – just like share prices can change, the value of Debt can also change.

For example, if the company is paying a 6% interest rate on its Debt, and similar companies are as well, meaning the market value of Debt is close to its value on the Balance Sheet, then the Cost of Debt might be around 6%.

Then, you also need to multiply that by (1 – Tax Rate) because Interest paid on Debt is tax-deductible. So, if the Tax Rate is 25%, the After-Tax Cost of Debt would be 6% * (1 – 25%) = 4.5%.

The Cost of Preferred Stock is similar because Preferred Stock works similarly to Debt, but Preferred Stock Dividends are not tax-deductible and overall rates tend to be higher, making it more expensive.

So, if the Preferred Stock Coupon Rate is 8%, and its market value is close to its book value because market rates are also around 8%, then the Cost of Preferred Stock should be around 8%.

Discount Rate Meaning: WACC in One Sentence

WACC represents what you would earn each year, over the long term, if you invested proportionally in the company’s entire capital structure.

So, let’s say this company uses 80% Equity and 20% Debt to fund its operations, and that it has a 25% effective tax rate.

You decide to invest $1,000 in the company proportionally, so you put $800 into its Equity, or its shares, and $200 into its Debt.

We said before that the Cost of Equity was between 9% and 11%, so let’s call it 10%.

We know the After-Tax Cost of Debt is 4.5% as well.

So, WACC = 10% * 80% + 4.5% * 20% = 8.9%, or $89 per year.

That does not mean we will earn $89 in cash per year from this investment; it just means that if we count everything – interest, dividends, and eventually selling the shares at a higher price in the future – the annualized average might be around $89.

WACC is more about being “roughly correct” than “precisely wrong,” so the rough range, such as 10% to 12% vs. 5% to 7%, matters a lot more than the exact number.

How to Calculate Discount Rate in Excel: Starting Assumptions

To calculate the Discount Rate in Excel, we need a few starting assumptions:

The Cost of Debt here is based on Michael Hill’s Interest Expense / Average Debt Balance over the past fiscal year. That’s 2.69 / AVERAGE(35.213,45.034), so it’s 6.70%. here.

This is a “rough estimate,” and there are some problems with it (e.g., What if the market value of Debt changes? What if that doesn’t represent the cost to issue *new* Debt?) but we’ll go with it for now in this quick analysis.

To calculate the Cost of Equity, we’ll need the Risk-Free Rate, the Equity Risk Premium, and Levered Beta.

Cost of Equity = Risk-Free Rate + Equity Risk Premium * Levered Beta

The Risk-Free Rate (RFR) is what you might earn on “safe” government bonds in the same currency as the company’s cash flows – Michael Hill earns in CAD, NZD, and AUD, but reports everything in AUD, so we’ll use the yield on 10-Year Australian government bonds, which was 2.10% at the time of this case study.

You can find up-to-date data on Australian government bond yields here, and you can do simple Google searches to find them for other countries.

The Equity Risk Premium (ERP) is the amount the stock market is expected to return each year, on average, above the yield on “safe” government bonds. We link it to the stock market of the country the company operates in (mostly Australia here).

You can find estimates for this number in different countries online; Damodaran’s data on the ERP is the best free resource for this.

At the time of this case study, the Australian ERP was 5.96% based on this data.

Levered Beta tells us how volatile this stock is relative to the market as a whole, factoring in intrinsic business risk and risk from leverage (Debt).

If it’s 1.0, then the stock follows the market perfectly and goes up by 10% when the market goes up by 10%; if it’s 2.0, the stock goes up by 20% when the market goes up by 10%.

How to Calculate the Cost of Equity

We could use the company’s historical “Levered Beta” for this input, but we usually like to look at peer companies to see what the overall risks and potential returns in this market, across different companies, are like.

We could look up “Beta” for each company and take the median, but Beta on sites like Google Finance, Capital IQ, Bloomberg, etc. reflects both inherent business risk and risk from leverage.

So, we must “un-lever Beta” for each company to determine the “average” inherent business risk for these types of companies:

Unlevered Beta = Levered Beta / (1 + Debt/Equity Ratio * (1 – Tax Rate) + Preferred/Equity Ratio)

This formula ensures that Unlevered Beta is always less than or equal to Levered Beta since we’re removing the risk from leverage.

We use VLOOKUP in Excel to find the Debt, Equity, and Preferred Stock for each company in the “Public Comps” tab, but you could find these figures on Google Finance and other sources if you don’t have the time/resources to extract them manually.

You can see the Unlevered Beta formula and output for each company below:

Michael Hill, like most companies, has more than just “inherent business risk” since it also carries Debt, so now we need to “re-lever” this median Unlevered Beta based on the company’s current or targeted capital structure to reflect that additional risk from leverage.

To do that, we can reverse the formula for Unlevered Beta:

Unlevered Beta = Levered Beta / (1 + Debt/Equity Ratio * (1 – Tax Rate) + Preferred/Equity Ratio)

We multiply both sides by the denominator to isolate Levered Beta on the right side:

Unlevered Beta * (1 + Debt/Equity Ratio * (1 – Tax Rate) + Preferred/Equity Ratio) = Levered Beta

Levered Beta = Unlevered Beta * (1 + Debt/Equity Ratio * (1 – Tax Rate) + Preferred/Equity Ratio)

When re-levering Beta, we like to use both the company’s current capital structure and the median capital structure of the peer companies, to get different estimates and see the range of potential values.

Once we have that, we can then plug this Levered Beta number into the formula for Cost of Equity to calculate that:

Cost of Equity = Risk-Free Rate + Equity Risk Premium * Levered Beta

You can see the results of these slightly different Cost of Equity calculations below:

Here, the Cost of Equity is always between 9% and 10% regardless of the exact number we use for Levered Beta, which is good since we want a range – but a relatively narrow range.

How to Calculate Discount Rate: Putting Together the Pieces for WACC

Once again, the main question here is “Which values do we for the percentages Equity, Debt, and Preferred Stock? The company’s current percentages, or those of peer companies?”

There’s no definitive answer, so we use different approaches here – one based on peer companies and two based on the company’s current percentages – and average them:

This result tells us that WACC for Michael Hill is most likely between 8.50% and 9.50%.

How to Discount the Cash Flows and Use the Discount Rate in Real Life

Finally, we can return to the DCF spreadsheet, link in this number, and use it to discount the company’s Unlevered FCFs to their Present Values using this formula:

Present Value of Unlevered FCF in Year N = Unlevered FCF in Year N /((1+Discount_Rate)^N)

The denominator gets bigger each year, so the Present Value is a lower and lower percentage of the Future Value as time goes by.

You can see that illustrated in the screenshot below:

• 0:42: The Big Idea Behind Valuation and DCF Analysis
• 4:48: Case Study Description
• 6:24: Revenue Projections
• 13:44: Expense and Cash Flow Projections
• 20:23: Unlevered FCF Projections
• 29:43: Summary and Preview

The Big Idea Behind Valuation and DCF Analysis

You can use this formula to value any company or asset:

The “Cash Flow” parts are intuitive because they’re similar to earning income from a job in real life and then paying for your expenses – they represent how much you earn in cash after paying for expenses and taxes.

But the Discount Rate is specific to finance. It represents risk and potential returns – a higher rate means more risk, but also higher potential returns.

A company is worth more when its cash flows or cash flow growth rate are higher, and it’s worth less when those are lower; the company is also worth less when it is riskier or when expectations for it are higher. That last part directly represents the Discount Rate.

If a company’s Discount Rate and Cash Flow Growth Rate were to stay the same forever, then investment analysis would be very simple: just plug the numbers into this formula, see how much the company is worth, and compare that to the company’s current value.

But that never happens!

Companies grow and change over time, and often they are riskier and have higher growth potential in earlier years, and then they become less risky later.

Valuation is more than this simple formula because we must project changes in the Discount Rate and Cash Flow Growth Rate.

And there are two ways you can do that by extending this simple formula into “real” valuation.

First, you can project a company’s cash flows until it reaches maturity over 5, 10, 15, or even 20 years, and then keep its Discount Rate and Cash Flow Growth Rate the same after that in the “Terminal Period.”

Then, you add up the values in each period to get the company’s total implied value.

This is known as “Intrinsic Valuation,” and the Discounted Cash Flow Analysis is the best example.

A second approach is to use “valuation multiples” as shorthand, skip these long-term projections, and value a company based on what other, similar companies in the market are worth.

These tutorials focus on the first approach because it’s more interesting to demonstrate, and it’s more important in finance interviews.

The first step of this approach – the DCF Analysis – is to project the company’s Cash Flows.

There are many types of “Cash Flow,” but in a DCF, you almost always use something called Unlevered Free Cash Flow:

Unlevered FCF should reflect only items on the financial statements that are “available” to all investors in the company, and that recur on a consistent, predictable basis for the core business.

In practice, that means that Unlevered FCF should include only Revenue, Cost of Goods Sold, Operating Expenses, Taxes, Depreciation & Amortization and sometimes a few other non-cash adjustments, the Change in Working Capital, and Capital Expenditures.

You ignore the Net Interest Expense, Other Income / (Expense), most non-cash adjustments, most of the Cash Flow from Investing section of the Cash Flow Statement, and the entire Cash Flow from Financing section.

Case Study Description

The scenario here is that Michael Hill’s share price has fallen by a huge amount over the past year (~50%) as it has announced plans to exit the U.S. market and its Emma & Roe stores.

Management believes the company is now significantly undervalued, and has called in your firm to value the company and advise on their best options.

How to Calculate Unlevered Free Cash Flow: Revenue Projections

Revenue for a retailer depends on the # of stores and sales per store, and contributions from other channels such as e-commerce.

We break Michael Hill’s revenue into regions: Australia, New Zealand, and Canada, because Australia and New Zealand (ANZ) are more mature and have lower growth potential.

The store count expands slightly in both regions, but it expands far more in Canada, going from 83 to 100 over the period shown here.

Sales per Store initially increase at 6.5% per year in Canada but fall to 3.0% by the end; ANZ start off with negative growth, but recover and eventually slow down to ~2-3% annual growth.

Revenue for each segment equals the average number of stores in the segment times the Sales per Store for the year:

How to Calculate Unlevered Free Cash Flow: Expense and Cash Flow Projections

Expenses differ in different region, and we’re just projecting the Operating Income or EBIT (Earnings Before Interest & Taxes).

The margins for ANZ here dip slightly in the first two years and then recover and rise to levels consistent with their historical figures (15.5% for Australia and 22.5% for New Zealand).

For Canada, the margins gradually rise from ~11% to the ~15% level as growth slows down far in the future.

“Corporate Overhead” is for everything outside the individual stores, such as the headquarters, CEO, accountants, marketing team, etc., and it’s a simple % of revenue here.

Capital Expenditures (CapEx) represent purchases of long-term items that will last for more than 1 year and benefit the business for many years to come.

It’s split into Growth CapEx for new stores here, based on the # of new stores and an annual cost to open each new store, and Maintenance CapEx for maintaining and upgrading existing stores. Spending in both categories increases over time at low single-digit rates.

You can see a summary of the expense projections here:

How to Calculate Unlevered Free Cash Flow: Putting Together the Full Projections

Unlevered FCF = NOPAT + D&A +/- Deferred Income Taxes +/- Net Change in Working Capital – CapEx

And we know that NOPAT = EBIT * (1 – Tax Rate). This represents the company’s earnings from core business after taxes, ignoring capital structure.

First, we need to sum up Revenue and EBIT by looking at all the segments and converting the Canadian Dollars (CAD) and New Zealand Dollars (NZD) back into Australian Dollars (AUD) based on the assumed exchange rates.

Then, we do the same thing for EBIT and convert each region’s EBIT into AUD and subtract the Corporate Overhead expense.

Depreciation & Amortization represents the recognition of previous CapEx spending over many years; we make sure it stays slightly under CapEx since the company is still growing, even near the end of the period.

We add this to NOPAT since it’s a non-cash expense that reduced EBIT before and saved the company on taxes.

Deferred Income Taxes represent differences between taxes on the Income Statement and what the company actually pays in cash.

It fluctuates a lot here, so we just make it a low percentage of Taxes and record a modest Cash Tax Benefit for the company. This one could increase or reduce UFCF, but here, it increases it.

The Net Change in Working Capital relates to timing differences between recording revenue and receiving it in cash, and recording expenses and paying for them in cash.

For example, if a customer pays, but not in cash right away, but still gets the product, the company lists it as “revenue,” even though its cash balance has not gone up.

The Change in WC tends to reduce cash flow for retailers that must order products before selling them (Inventory), but it often increases cash flow for companies that collect cash in advance.

It fluctuates a lot here, so we just use simple, low percentages (25-30% of the Change in Revenue) to project it.

It’s also unusual that this is positive for a retailer like Michael Hill, meaning that Working Capital boosts its cash flow, but aspects of its business model might explain that.

Finally, we subtract Capital Expenditures (CapEx) since these also reduce the company’s cash flow; we calculated these in a previous step.

Summing up all these items, we calculate and project Unlevered FCF across the 10-year period:

Unlevered FCF = NOPAT + D&A +/- Deferred Income Taxes +/- Net Change in Working Capital – CapEx

You can see the entire formula in Excel below:

Unlevered FCF growth should slow down over time, and by the end of 10 years, it should be around the GDP growth rate or inflation rate (1-3%), which it is here.

Next, we have to calculate the Discount Rate and use it to discount these UFCFs to their Present Value.