How to Make a Football Field Chart in Excel

Creating a football field chart is the standard way to compare different valuation methods for a company on a single, easy-to-read graphic. While it might look complex, building a professional one in Excel is entirely manageable once you understand the setup. This article will walk you through setting up your data and building a dynamic football field valuation chart step-by-step.

What Exactly is a Football Field Chart?

A football field chart, also known as a valuation matrix, is a type of floating bar chart used primarily in finance and investment banking. Its purpose is to present a summary of a company's valuation range derived from several different methodologies. By plotting these valuation ranges on top of each other, you can quickly see where they overlap and get a comprehensive view of what a company, business unit, or asset might be worth.

Common valuation methods you'll see included are:

• Comparable Company Analysis (Comps): Valuing a company based on the trading multiples of similar publicly traded companies.
• Precedent Transaction Analysis: Valuing a company based on what acquirers have recently paid for similar businesses.
• Discounted Cash Flow (DCF) Analysis: Valuing a company based on the present value of its projected future cash flows. This often includes multiple scenarios (e.g., low, base, and high cases).
• Leveraged Buyout (LBO) Analysis: Determining a valuation based on what a private equity firm might pay, considering debt and required returns.
• 52-Week Trading Range: The lowest and highest stock prices the company has traded at over the past year.

The chart gets its name because the floating horizontal bars against the gridlines resemble the yard markers on an American football field. It provides a visual guide to judge whether a company is overvalued, undervalued, or fairly priced based on various analytical perspectives.

When Should You Use a Football Field Chart?

This chart is most useful when you need to summarize complex financial analysis for a clear, high-level presentation. It’s a staple in deliverables where a story needs to be told quickly and visually. You’ll find them most often in:

• Mergers and Acquisitions (M&A) Pitch Books: Presenting a potential valuation to a client for buying or selling a business.
• Fairness Opinions: A detailed report prepared by a bank to confirm that the price of a transaction is fair from a financial point of view.
• Executive and Board Presentations: Simplifying the results of extensive valuation work for decision-makers who need to absorb the key takeaways quickly.
• Internal Strategy Discussions: Helping a company's leadership team understand its current market valuation relative to its intrinsic value.

In short, anytime you have multiple valuation ranges and need to convey the 'big picture,' the football field chart is the right tool for the job.

How to Organize Your Data for a Football Field Chart

The quality of your chart depends entirely on the data you feed it. Before you even think about building the chart in Excel, you must have your valuation analyses complete and the results neatly organized. The chart we’re going to build relies on creating a stacked bar chart and then making the first part of the stack invisible, which creates the "floating" bar effect.

To do this, structure your data in a table with four columns:

1. Valuation Method: The name of the analysis (e.g., 'Comparable Companies').
2. Low: The low end of your calculated valuation range for that method. This will form our invisible base.
3. Range: The difference between the high and low ends of the range (High - Low). This will be the visible floating bar.
4. High (Optional but helpful): Include the high end of your range to make the 'Range' calculation straightforward.

A few key points about the data structure:

• Metrics should be consistent: Ensure all your valuation metrics are the same type. For example, don’t mix Enterprise Value per share ($) from a DCF with an Equity Value from comps unless you make the appropriate adjustments. The most common metric used is an implied share price.
• Formula is simple: The "Range" column should be calculated as [High Value] - [Low Value].

Step-by-Step Guide to Making a Football Field Chart in Excel

Once your data is tidy and correctly structured, you're ready to build the chart. Follow these steps carefully.

Step 1: Insert a Stacked Bar Chart

First, highlight the data you need for the chart. Select the range that includes the names of your valuation methods, the 'Low' values, and the 'Range' values. Do not include the 'High' column in your selection. Go to the Insert tab on the Ribbon, click on the Chart icon for Bar Charts, and select the Stacked Bar option under the 2-D Bar section. You’ll get a basic stacked bar chart, which we'll now transform into our football field.

Step 2: Make the 'Low' Series Invisible

This is the key trick. We need to hide the first part of each bar (the orange part in Excel's default colors, which represents our 'Low' values) to make the 'Range' bars appear to float.

1. Right-click on any of the 'Low' series bars (the first segment from the left).
2. From the context menu, select Format Data Series...
3. In the Format Data Series pane that appears, go to the Fill & Line tab (the paint bucket icon).
4. Under Fill, select No fill.
5. Under Border, select No line.

The first part of your bars will disappear, leaving you with bars that appear to be floating. Your chart is already starting to take shape!

Step 3: Reverse the Category Order

You’ll notice that Excel has plotted your valuation methods upside down from how they appear in your table. To fix this:

1. Right-click on the vertical axis (your Y-axis with the valuation method names).
2. Select Format Axis... from the menu.
3. In the Format Axis pane, under Axis Options, scroll down and check the box that says Categories in reverse order.

This will flip your chart vertically, so it correctly matches the order of your source data table.

Step 4: Adjust the Horizontal (X-Axis) Limits

To give your chart proper context and spacing, you need to set the bounds of the horizontal axis. Don't let Excel decide for you, as it might crop your view too tightly.

1. Right-click on a number in the horizontal axis (X-axis).
2. Select Format Axis...
3. In the Format Axis pane, under Axis Options, you can manually set the Minimum and Maximum bounds. Choose values that are slightly outside the full range of your data to give it some breathing room. For example, if your lowest value is $50 and your highest is $185, you might set the minimum to $40 and the maximum to $200.

Step 5: Add a Vertical Line for a Reference Price

A football field chart is most impactful when it includes a reference point, such as the company's current stock price or a proposed offer price. We can add this using an error bar on a dummy scatter plot point.

1. First, add a new data point for your reference line. Create a small table somewhere on your sheet with your desired price (e.g., "Current Price" in one cell, "$118" in the cell next to it).
2. Right-click on your chart and choose Select Data...
3. Click Add to create a new series. Give it a name (e.g., "Current Price") and select the cell with your price for the Series values. Click OK. Excel will add it as another small bar.
4. Right-click your chart and select Change Chart Type...
5. In the Change Chart Type dialog, go to the Combo section at the bottom. For your new "Current Price" series, change its chart type from Stacked Bar to X Y (Scatter). Make sure it's plotted on the Primary Axis. Click OK.
6. Now, select the single dot that has appeared on your chart. Go to the Chart Design tab and click Add Chart Element > Error Bars > More Error Bars Options...
7. In the Format Error Bars pane, select the Vertical Error Bar. Choose Both for direction and Fixed value for the amount, entering a number large enough to span the whole chart (e.g., 1000). To remove the small horizontal tick mark, select the Horizontal Error Bar on the chart and press delete.

This creates a clean, dynamic vertical line that will automatically update if you change the reference price in your cell.

Step 6: Final Formatting and Cleanup

Now for the professional touches that make your chart clear and presentable:

• Give Your Chart a Title: Click on "Chart Title" and give it a descriptive name like "Valuation Summary" and include the units (e.g., "Implied Share Price ($US)").
• Remove the Legend: The legend is now redundant and confusing because of our hidden series. Click on the legend box and press delete.
• Adjust Bar Width: To make the bars thicker, right-click a bar, select Format Data Series..., and decrease the Gap Width percentage (e.g., to 50%).
• Data Labels: To show the actual low and high values on the bars, you can add data labels. This can get cluttered, but if needed, you can add them and manually position them inside the bar at the low end and outside at the high end. Many analysts prefer to leave the axis numbers as the primary reference.
• Color and Font: Adjust the colors of your bars and the fonts throughout the chart to match your company's branding or your presentation's style guide.

After these steps, you will have a clean, insightful, and professional-looking football field chart ready to be included in your reports and presentations.

Final Thoughts

The football field chart is an excellent tool for summarizing several different financial valuation analyses in one clear, concise visualization. Once you’re comfortable with the data setup and the "invisible series" technique in Excel, you can create them quickly and efficiently to communicate complex financial information.

While Excel is fantastic for detailed financial models, often the biggest challenge analysts face is simply gathering and centralizing the necessary data from different sources to even start these analyses. For marketing and sales data, we built Graphed to solve this by connecting all your data sources automatically. You can use simple, natural language to get the numbers you need in seconds, making the pre-analysis part of your job much faster.