Football Field Valuation Chart with Dynamic Share Price Line [Tutorial Video] (16:54)

A football field valuation template lets you quickly see a company’s valuation across different methodologies, such as Comparable Company Analysis, 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.

Here’s what a sample football field valuation graph looks like for Walmart:

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 Make a Football Field Valuation Chart, Part 1: The Basic Chart

Step 1.1: First, assemble the output of each valuation methodology across all the percentiles, which you can do with the TRANSPOSE function and a basic Equity Value to Enterprise Value bridge:

Then, you need to display these in reverse order for the graph, which you can do with direct links or with an INDEX/MATCH combination to reverse the order.

We normally do this on a separate “ValGraph” sheet.

Step 1.2: Next, calculate the “distance” between each point, starting with the 25^th Percentile Values minus the Minimum Values; these distances will be the segments in the chart:

Step 1.3: Then, select the data on the right (from “Min Point” to “Max Point”) and go to the Insert tab and then “Stacked Bar Chart”:

Step 1.4: Right-click this new graph, go to Select Data and then Edit Horizontal Axis Labels, and link to the labels for the methodologies on the left-hand side:

The graph will now look like this:

To get an extra space at the top of the graph, you can manually extend the data range to row 51 rather than row 50:

Step 1.5: Now, you can format the football field valuation, add labels, change the colors and fonts, add axis titles, and hide the bars you don’t want to see. For example, you often show only the 25^th percentile to 75^th percentile and hide the rest:

Here’s the graph after changing the fonts, borders, and legend, and adding labels for each set of methodologies:

How to Make a Football Field Valuation Chart, Part 2: The Dynamic Share-Price Line

Part 2 of the football field valuation chart involves adding the company’s Current Share Price as a vertical line, which is much harder than it sounds:

Step 2.1: Create a “dummy series” for the Current Share Price under the main data area, and link to the Current Share Price in the Min to Max columns and then a dummy number, such as 1,000, followed by 0’s:

Step 2.2: Right-click the graph, go to Select Data, and then go to “Add” under Legend Entries (Series) and use “Current Share Price” for the Series Name and the dummy series for the Series Values:

Step 2.3: Now, left-click this new bar on the graph and manually change the Series Formula in the formula bar so that it has both X and Y values. This is the trick:

The new formula should look like this:

=SERIES(ValGraph!$B$52, ValGraph!$I$52:$M$52, ValGraph!$N$52:$R$52,1)

Once you make this change, your graph will look completely wrong – temporarily:

Step 2.4: Right-click the Current Share Price bar in the football field valuation, go to Change Series Chart Type, and select “Scatter with Smooth Lines and Markers.” The Secondary Axis box will be checked automatically:

You’ll now see a vertical line at the position of the company’s Current Share Price in the graph.

Step 2.5: Right-click the graph, go to Select Data, and for the first “blank” series, change the Horizontal Axis Labels to the names on the left-hand side, and click OK:

Step 2.6: Right-click the Secondary Vertical Axis, go to Format Axis, and change the Min to 0 and Max to 1,000:

You can also remove the labels for this Secondary Vertical Axis.

Step 2.7: Now, you can change the color and any other formatting for the Share Price line:

Real-Life Graphing and Valuation

In real life, you don’t have to complete this series of steps to create and format football field charts: you take a template from an existing file and modify the graph as necessary.

We presented this set of steps to illustrate some of the problems that come up when you create complex graph types that are not built into Excel.