Football Field Valuation Chart with Dynamic Share Price Line [Tutorial Video]

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.

Resources:

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

Lesson Outline:

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.

[edupa]

Dude, I really appreciate your work. Thank you.

[NewNorseman]

Great video. Thanks for sharing.
Could you please tell how to find example docs (i.e. valuation summary) you showed? Seems to be archives from sec edgar.
Thanks

[vivekvarshney]

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?
Thank you

[TheVintageCurls]

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