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

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.

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:

Walmart - Football Field Valuation Graph

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:

Valuation Methodologies - Multiples

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 25th Percentile Values minus the Minimum Values; these distances will be the segments in the chart:

Valuation Points - Distances

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”:

Stacked Bar Chart - Football Field Valuation

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:

Football Field Valuation - Valuation Labels

The graph will now look like this:

Unformatted Football Field Valuation Chart

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:

Valuation Chart - Data Range

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 25th percentile to 75th percentile and hide the rest:

Football Field Valuation - Hiding Percentiles

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

Walmart - Partial Football Field Valuation Graph

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:

Valuation - Dummy Series

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:

Adding a Series to the Valuation Chart

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:

Valuation Chart - Changing the Series Formula

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:

Incorrect Valuation Graph

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:

Valuation Chart - Scatter Plot

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:

Valuation - Horizontal Axis Labels

Horizontal Axis Labels

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

Valuation Chart - Secondary Vertical Axis

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:

Share Price Line Formatting

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.

About Brian DeChesare

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.