Best & Worst case chart in excel

B

Very often we use Best-case and Worst-case scenarios to explain uncertainty.

If you are into Sales or Revenue forecasting then you should be familiar with these terms.

To understand further you can read the scenario planning article here.

In this tutorial, I’ll show you how to create a nice little Best case and Worst case chart in excel to explain the underlying uncertainty.

Excel chart for best & worst case

If you know how to create a Line chart in excel then it will be very quick and easy.

Consider the below example where I have Sales data by quarter from Q1FY21 till Q2FY22. And please note, we have actuals till Q222 and we are forecasting for Q322 & Q422.

Best and Worst case data

If you notice, I have removed best & worst case numbers for all the actuals (all but Q222) since they are final hence no uncertainty.

So where do we start?

line Chart to Scenario chart

First, we need need to make a few changes to our data table.

Step1: Make sure to include Best & Worst-case data for the preceding quarter, i.e. quarter Q222 is already closed hence we can just fill with actuals for all three rows (refer above).

But why? well, this is our starting point.

Step2: Select the entire data range from the table

Step3: Click on Insert ==> Line Chart ==> Select Line with Markers

Line with markers

The bare bone chart should look like below…already looking good..?

Base chart (best & worst case)

Add a few bells and whistles

Let’s adjust the Y-axis starting range so that we can centre our graph in the chart area.

Step1: Right-click on Y-Axis ==> Format Axis ==> Bounds ==> Minimum should be around $3000 (note we do not have any data points below 3k)

Step2: Let’s change chart style, Click anywhere on your chart ==> Chart design (from top Menu) ==> Add Chart element ==> Gridlines ==> Primary Minor Vertical

Step3: let’s add a nice descriptive Title

That’s pretty much it.

Your chart should look like the one below…but with some tinkering, you can make it even more attractive but I’ll leave it to your creativity.

You can download my Best & Worst Case chart workbook from here.

Final chart best & wors case

Also read:
10 Reasons for Excel formulas not working
51 Best Excel Tips & Tricks

About the author

Sudheer Tammini

Hello,
I am an 'Excel enthusiast' and early adopter of technology. I’d like to learn new things and solve the most difficult business problems using technology (trust me, it's fun). Besides running this website, I’m a full-time employee and have a Master’s degree in Finance.

Subscribe to our: Newsletter
Know more: About Me