First create a new Excel sheet and type in the following data. The total row contains all the component values and column 1 through 5 contain the value for each individual component.
Next count SUM of the values row "code" (row 2) to cell B3.
Repeat this for the whole column 1 and you should get the following results.
Then select the entire table and create a stacked column chart from Insert tab > Charts group > Column > Stacked column.
You should get the following chart as a result.
Select a Chart layout of your liking from Design tab > Chart layouts group.
Next we will hide the unneeded blocks from the column. Select the bottom block from the column "customization" and go Format tab > Shape fill > No fill. You should get the following result.
Repeat this for the rest of the columns (NOT for the last one) as well and you should have the following result.
Next we will create the lines bridging the columns together. For this type in the following to the table.
Next click on the chart and you see the blue frame surrounding our data area. Grab from the bottom right corner of the blue frame and drag the frame over the values in "line 1". You will then see two new blocks in your chart, as seen in the image below (orange blocks).
Click on either of the orange blocks and change the chart type to line from Design tab > Type > Change chart type.
You should now have the following result.
Next click on the chart and you see the blue frame surrounding our data area again. Grab from the bottom right corner of the blue frame and drag the frame over the values in "line 5". You will then see the rest of the connecting lines in your chart, as seen in the image below.
Format the new lines as you wish from Format tab > Shape styles group > shape outline. I have chosen to make the lines dashed and black, weighing 1/2 pt.
Now you should have the following result.
Next delete the legend on the right and hide the gridlines by right clicking on the chart area and select Format Gridlines > no line.
Click on the chart area and make the background color light grey from Format tab > Shape styles group > Shape fill.
Now all you need to do is to write down the title for the chart and you have the finished product.
Free Download Waterfall chart template Excel
Download link for the finished Excel Waterfall chart spreadsheet (file hosted by mediafire)