![]() The result is shown in the screenshot below where there is now the increment and the high range shown. To make the right bars into no fill, you select the data series and then clicking on the no fill option. The first thing to do is just to make the bars at the left and the right into no fill bars. Now you can start cleaning up the graph with the bar chart and putting in the data labels. The graph in the screenshot above does not look too good. With the stacked bar, that has the low range, the increment and the high range. In the bar chart I select the second option that is a stacked bar. Then I have changed the chart type and made it a bar chart. In the example illustrated on the screenshot below, I have selected the second table from the above screenshot and pressed ALT, F1 to insert the graph on the same page. Once you have the table, the next step is to make a graph from the three columns that will not look very good but will be the outline and provide the basis for the final graph. The screen shot below demonstrates how to set up this key part of the process of making a little table. The big deal is to create the increment that is calculated as the difference between the high case and the low case. The low range is for the data label that goes on the left and the high range is for the data label that goes on the right. Once you have the data with the different valuation techniques and the ranges for each technique, the key step is create a table with the low range, the increment and the high range. To make the bars wider, go to the series and make the graph width less wide. The graph below shows how you can format the final graph. Step 6: Format the Graph – Thick Bars, Change the Scale, Remove the Legend Click the no-fill for both the low and the high label Format the data series and then go to the no-fill. Step 4: Do the Same thing with the High End Data Labelsĭo the same thing but you can use a different way to format the data labels to get them close to the middle bar Step 5: No Fill the Data Label BarsĬlick on the blue and green series. Then you can format the data labels so that the are close to the red bar. Select the blue area and add data labels. Note that the little red thing in the middle is what will be graphed Step 3: Add Labels You may have to right click and change the row and column. Step 2: Insert a Stacked Bar Graph Using Alt F1Īfter you create the table, select the data and press Alt F1. Note that the low and the high are now just for the labels and the increment is the real item that is graphed. Now, simply repeat the data and put the increment between the high and the low in a separate table as shown below. The data below is an illustrative starting point The example is attached to the button below. In this example I start with a high and a low for an assumed M&A transaction. To make a football field diagram use the following steps. The increment is what is graphed and the high an low are used to create the data labels. The tricks to make this work is to are to add an increment between the high and the low. The good news is that no macros are necessary. Putting labels on the football field involves adding a separate series to make spaces and it can be painful. The step by step description and the video below demonstrate how you can construct such a flexible football field diagram that includes the valuation amounts as labels on the graphs. But the problem comes when you put labels on the valuation bars in the chart from different methods. Making the bar chart with the range of values is pretty simple. Making a basic football field graph is can be accomplished with the bar chart selection in excel. While the name is irritating, the idea of presenting different valuation approaches is pretty good because a football field diagram shows that different valuation techniques can be used and that it is naive to believe a single valuation amount is reasonable. For example, the range in valuation from low and high comparable P/E ratio’s is compared with the range in value from a DCF method with different WACC assumptions. A football field graph is a pretty silly name used to present alternative valuation techniques with different ranges for M&A analysis. On this page I walk through how to create a football field diagram.
0 Comments
Leave a Reply. |