Home MarketsOptions Using the Stock Valuation Excel Template – Step-by-Step guide

Using the Stock Valuation Excel Template – Step-by-Step guide

by admin
0 comment

With the addition of Excel integration to the Samurai platform, you can now create advanced tools in Excel to give you an edge while trading.For example, a blog or video How to get your own stock value estimate using the EPS-PE template.

This article shows how to use this Excel template with a more complex example so you can do it yourself.

The importance of self-estimating enterprise value is very high. Because this allows them to independently find opportunities in the market.Once you understand the company’s value, you can trade either long or short (our Scenario engine to find advanced optimal option trades). The Excel template automatically updates with updated values ​​each time you open the sheet, making it much more manageable to use.

In this example, we will analyze Match Group, Inc. It offers dating products all over the world. The company’s portfolio of brands includes Tinder, Match, Meetic, OkCupid, Hinge, Pairs and PlentyOfFish.

The company recently fired Tinder’s CEO and recorded slowing growth. I believe the company will continue to be profitable, so I think the sector will grow (and the company is a leader in this sector). When Current prices are around 2019 levels. It could be an interesting time to analyze and trade this.

This step is easy. Enter “MTCH” in the symbol cell and make sure the orange cell is correct. The rest will update automatically.

Step 1 - Import data
Step 1 – Import data

For stable companies, it is more likely that the model will update automatically with few changes required. However, for us, MTCH has seen a significant decline in EPS this year. So estimating based on his latest EPS gives you a low target price. However, the company has already shown that it can generate higher profits with lower sales, so we believe EPS will be even higher going forward.

Model before change:

Model before change

Changing EPS to be more realistic usually starts with reading management estimates or analyst estimates in financial reports. Analyst estimates can be found on financial portals such as Finviz and Yahoo Finance.

Finviz example:

Finviz EPS

Yahoo Finance example:

Yahoo Finance EPS

These estimates are usually good to start with. So enter an EPS of $2.6 in the blue cell for 2023. Analysts expect growth of 17.1%. I will use their estimate as it is lower than the company has done in the past.

The modified model looks like this:

We will also adjust the PE ratio from the current 180+ to something like 25, which seems conservative for a growth company. In the current market conditions, it looks good.

The final model looks like this:

final model

We find that the “fair price” under these assumptions is about 14.2% higher. This is usually not enough as there is no margin of safety if the assumption is wrong. But this is not the end. Read below for considerations when starting a trade.

Under Model Pricing you can see several scenarios. The most interesting is the analyst target price. You can see that it is 48% higher than the current price. This is a positive sign and could point to an upward trend.

In Part 2, you entered your assumptions, created a quote, and compared it to the market price. In this step, we assume the market price is correct and see how we can change our assumptions to reach that market price. Then see if the assumption makes sense to us.

The assumptions to change are:

  1. PE ratio
  2. EPS
  3. growth rate

In this case the PE ratio is reasonable and will not be changed (I believe). So let’s focus on the change in EPS and growth rate. In this step, slowly increase or decrease the value to see how much the final result changes and see if it makes sense.

  1. If you change the EPS and the EPS is 2.3 instead of the 2.6 I predicted, then you know the value is fair.
  2. By changing the growth rate, we can see that a 10% growth rate makes the company reasonably priced.
  3. Of course, a combination of both works as well.
Change in EPS
Change in EPS

If you compare the assumptions to what I think will happen in the market. If the company fixes their cost structure, I think the growth will be over 17%. So I don’t think a 10% growth rate is too dangerous (hypothesis #2). However, the company’s EPS could certainly be 2.3 instead of 2.6. In years when the macro environment is uncertain, it misses 12%. So it’s a possible scenario.

change in growth rate
change in growth rate

These scenarios will help you read and analyze news about your company. For example, once you read something that could hurt your income, you can assume it’s already priced. But a slowdown in growth could spell the worst for the company’s valuation.

In this step, you can see how different growth rates and PE ratios affect your company’s price. This is a very powerful tool for understanding your company’s “sweet spot”. However, it is not covered in this article and is for another part.

If available and the company is covered by analysts, I like to see trends in target price and analyst adjustments. I want to be more conservative).

In this instance, I’m bullish on the company and I think the growth rate (after the company adjusts its cost structure) will surprise analysts, so I look for entry signals. It usually integrates with a technical indicator (oversold) or a clear technical point (support or stop loss location). In this article, I would like to discuss some considerations for different strategies regardless of other trading styles.

Sell ​​Put/Put Spread – These are credit strategies and like to open at the current market price. Here we try to open with 50 or 45 strikes. This is about 25-40% off the “fair” price depending on the model. I would rather sell a put (if the portfolio can support it) because it’s easier to manage.

Long Call/Call Spread – Buy calls rather than spreads as these are debit strategies and profits are unlimited. But I would rather see the market on my side as well, as I want a very big upside when buying calls. This means bullish or oversold (see this article At the time of writing, the market is bearish). If not, I’d rather sell put/put spreads.

To Option Samurai

You may also like

Copyright ©️ All rights reserved. | Investors Radar