In this tutorial, you’ll
In this tutorial, you’ll learn how to create a flexible football field valuation template in Excel, including a line for the company’s current share price that updates automatically when the share price changes – which is missing from most templates you’ll find.
Table of Contents:
1:01 Football Field Valuation Description and Typical Problems
3:42 How to Set Up the Basic Chart
10:33 How to Add the Dynamic Share-Price Line
15:49 Recap and Summary
A football field valuation template lets you quickly see a company’s valuation across different methodologies, such as Public Comps, Precedent Transactions, and the DCF.
The overall results matter more than any single number or method, so it’s useful to get a view of everything at once.
If the company looks overvalued across all ranges across all methodologies, it probably is… and vice versa if it looks undervalued.
The basic setup is not complicated and requires just a Stacked Bar Chart or a High-Low-Close Stock Chart.
You can find many templates online, but they all tend to have a few problems:
First, most templates show the bars vertically rather than horizontally – that’s easier to set up, but less flexible.
Second, most templates do not support percentile ranges from different methodologies, such as the 25th, median, and 75th percentiles – but that’s what all banks do in real life.
Third, most templates do not include support for a dynamic share price line that updates on the graph when the company’s current share price range.
Our template fixes all these issues and makes the graph more dynamic so you don’t have to update lines or bars manually.
How to Set Up the Basic Chart
Step 1.1: First, we need the output of all the valuation methodologies across the different percentiles first – this step is not part of this lesson. See the other valuation tutorials.
Step 1.2: Then, we calculate the “distance” between each point, starting with the 25th Percentile minus Minimum; these will be segments in the bar chart.
Step 1.3: We create the Stacked Bar Chart by highlighting the data in Excel and going to “Insert Chart.”
Step 1.4: Right click the chart, go to Select Data, and Edit Horizontal Axis Labels and link to the labels for the methodology names.
Step 1.5: Apply some formatting to hide the bars you don’t want to see (we often show the 25th to 75th percentile and hide the rest), add labels, change the colors and fonts, add axis titles, etc.
How to Add the Dynamic Share-Price Line
Step 2.1: Create a “dummy series” for the Current Share Price under the main data area – link in the Share Price in the Min to Max columns, and then enter “1,000” or some other number in the column right after that, followed by 0’s.
Step 2.2: Right click the graph, go to Select Data, and go to “Add” under Legend Entries (Series) and select Name (Current Share Price) and Values (Min to Max range).
Step 2.3: Then, manually change the Series formula so that it has both X and Y values (the tricky part)!
Step 2.4: Right click the Current Share Price bar on the graph, go to “Change Series Chart Type,” select Scatter with Smooth Markers (Secondary Axis will be checked automatically), and click OK.
Step 2.5: Right click the graph, go to Select Data, and for the first “blank” series, change the Horizontal Axis Labels to the methodology names on the left-hand side, and click OK. This will restore the correct labels on the graph.
Step 2.6: Fix the Secondary Axis by right clicking it, going to Format Axis, and changing the Min to 0 and Max to 1000. This will make the share price line extend all the way to the top and bottom of the graph.
Step 2.7: Then, change the color of the line and remove the Secondary Axis.
great stuff! Very useful!
Thank you for this! If you’re given a football field in an interview and told to analyze it and tell them if there are mistakes, what are some things we should be looking for? For example, incorrect Ev/ebitda vs ev/ebit margins or copy and paste mistakes
Thanks! Really appreciate.
Great tutorial, thanks a lot!!
Hi, can you tell us about how IFRS 16 would affect the valuation in financial model, like ROU stuff. and how would it change our FCFF calculations in financial models?
RATIO CHART STOCKS In
RATIO CHART STOCKS In PRICE ACTION Trading - In This Price Action Trading Strategies video I have explained Ratio Chart and How to Trade this in Price Action Trading. I have covered Price Action Trading in depth. Link to all Price Action Trading strategies videos is given below.
?? VWAP Trading ONLINE Course For Day / Short Term Trading
?? JOIN this channel to get access to EXCLUSIVE Trading Videos:
(Price Volume Analysis) - 2 Videos Every Month
?? Price Action Trading For Beginners Playlist
?? Intraday Trading Strategies - Beginners Guide To Intraday Trading
?? VWAP Trading Strategy
Follow Me On Twitter
JOIN Telegram Channel
Technical Analysis Video For Stock Market is released every Saturday 1030 Am IST
Title - RATIO CHART In PRICE ACTION Trading (How To Trade)
Playlist - Price Action Trading
Thank You for Visiting Trade With Trend Channel (TradeWithTrend)
Disclaimer - This video is for Educational Purpose Only. Not Registered With SEBI And No Investment Or Trading Related advise is given.
Hi Mentor... Thanks for this video... Kindly share the code for Relative Strength....
Sir code is not Running , showing multiple errors
Does ratio chart available in zerodha
I am highly influenced by your knowledge and the way you present all topics so simple but highly effective way..will never miss your topics and learnt a lot from you..great job..
Plz guide for period setting
Very well explained thanks again for teaching valuable information God bless you sir
In today's video, we took
In today's video, we took a look at the similarities/differences between a line chart and candlestick chart. We also discuss which one I, as well as many traders prefer to use.
Thank you so much for choosing to watch my video. I encourage you to like and subscribe if you enjoyed, I would mean a lot to me.