A floating share price line adds a lot of value to the football field. It shows your valuation in context to the current share price and gives perspective on your valuation relative to the market.
But how to add the line to the graph? First, we need a simple football field. You can download our file here.
Next, we need to do 2 things:
- Add a new data series
- Change the new data series into a vertical line
1. Add a New Data Series
First, we add a new data series. We need X and Y values.
The X values are the share price. It will give us a line between 2 places; hence we need the number 19.8 written twice.
The Y values could be any two numbers separated by a set interval. We’ve chosen 0.0 and 7.0 as there are 7.0 valuation techniques on our football field, but you could just as easily use 0.0 and 1.0.
Now we need to add the share price to the graph.
NB: it won’t look correct until we’ve finished all the steps! Don’t worry if it looks strange until then.
Right click the chart and click on Select Data.
Type in the series name (here it is Share Price) and in the Series Values, select the cells with the 19.8 X values we input earlier.
We’ll have to change this later, so we’ll be back to this box soon.
Select OK. Then select OK again.
2. Change the New Data Series into a Vertical Line
The share price has been added to the graph but not as a vertical line (it may look different on your graph).
To change it, right click on one of the bars and select Change Series Chart Type.
Choose the Combo menu on the left, then your new Share Price series name should appear with Stacked Bar (or perhaps Clustered Bar) next to it.
For the Share Price series, click the drop-down next to Stacked Bar, scroll down to the X Y (Scatter) group, and click on the picture of the Scatter With Straight Lines graph.
The share price has probably disappeared or looks completely wrong! Excel isn’t clever enough to know what we want to do. So we need to tell Excel how to display the new line as a vertical line.
Right click the chart and choose Select Data.
We now need to tell Excel the X and Y values (at the moment they will be blank or linking to the wrong cells). For the X values select the cells with the 19.8 numbers, and for the Y values select the cells with the 0.0 and 7.0.
Click OK, and OK again, and the vertical line has appeared. We are 95.0% there. The vertical line probably doesn’t go all the way to the top of the chart though.
To change this, select the secondary axis on the right-hand side of the chart and then right click. Click on Format Axis.
And your floating share price line has now been added.
We can see some of the values are below the current share price line (PE CY2 Multiple valuation), while others imply a value above (% Premiums Paid valuation and the DCF With Synergies valuation). You could now change the two X values of 19.8 to whatever you like, even linking those cells to real share prices on information providers such as Factset, Bloomberg, and CapIQ, and the share price line will move automatically. Awesome.
Download our simple football field template below.
Have you read “Business Valuation Overview” yet?