whitepaper-pic-big

Are you looking for the best possible company valuation methodology? Football field graphs can be used to compare different methodologies quickly and clearly. This article details how to build a football field graph in Excel 2007 and 2010.

 

Steps to build a football field chart

Below are the valuation ranges that we need to build the football field for and the associated valuation methodologies.

 

  • 1. Select the data as shown below, excluding the first row.

 

Summary Table

Min

Max

EV / LTM EBITDA Transaction Comps

20,844.0

24,395.2

LBO

12,075.0

12,787.0

DCF with synergies

19,560.6

23,224.6

DCF

17,486.4

20,870.1

EV / 2015 EBITDA trading comps

17,774.3

19,883.2

Equity research

15,931.9

19,091.4

52 week range

16,927.1

19,187.7

 

  • 2. Insert bar chart – clustered bar (ALT N B) and hit enter. Then select the legend as circled below and hit delete:

 

 

  • 3. Now this is what the chart looks like:

 

 

  • 4. Select one of the series and right click (SHIFT F10), then select “Format Data Series” and under “Series options” change the “Series overlap” to 100%, as shown below:

 

 

  • 5. This is how the chart appears now (both series appear to be completely merged):

 

 

  • 6. Now select the series and right click (SHIFT F10). Then click “Select data”. Move the “Series 1” down by selecting and hitting the down arrow as shown below:

 

 

  • 7. This is what appears on the screen:

 

 

  • 8. And the chart will be updated as below:

 

 

  • 9. Now select the blue bars, right click (SHIFT F10) and select “Format Data Series”. Select “Fill” as “Solid fill” and choose the color as “White” (to match the chart background). Hit enter. While the series is still selected, right click (SHIFT F10) and select “Add Data Labels”. Now right click (SHIFT F10) and select “Format Data Labels” and within “Label Options”, choose “Inside End” for label position.
  • 10. Now select the red bar, right click (SHIFT F10) and select “Add Data Labels”. The data labels, by default, appear on the outside end.
  • 11. Once this is done, this is how the chart will look:

 

 

  • 12. Now we need to clean the X Axis. Select the labels of the X Axis and right click (SHIFT F10) and select “Format Axis”. I have changed some of the options, as shown below, to suit my current chart plot area:

 

 

  • 13. If the need be, extend the chart a little to the right, by dragging the right edge of the chart and remove the vertical gridlines by selecting and hitting delete. Now this is what the chart looks like:

 

 

  • 14. We can also add a dashed line representing the company’s current enterprise value. Click on insert, select shapes (ALT N SH) and then select a line. Using this draw option, add the line to the chart, where relevant. Once added, right click (SHIFT F10) on the line to change the format from a solid line to a dashed line. We can also add a text box in the same way and add the relevant content as below. If needed, change the text size within the box. The final chart will look like:

 

 

 Copyright© 2016 AMT Training

Related courses available for:

Related articles in the Knowledgebank