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.
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
The bare bone chart should look like below…already looking good..?
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.
Also read:
10 Reasons for Excel formulas not working
51 Best Excel Tips & Tricks