Locked lesson.
About this lesson
Generating forecast charts in Excel is only a few clicks away, resulting in a chart that you can customize and update later.
Exercise files
Download this lesson’s related exercise files.
Forecast Sheets (Excel 2016).xlsx94.6 KB Forecast Sheets (Excel 2016) - Completed.xlsx
111.4 KB
Quick reference
Forecast Sheets (Excel 2016)
Building dynamic forecasts in Excel 2016.
When to use
When you want to create a dynamic forecast chart that shows confidence levels.
Instructions
Data setup
- Ensure that your data is stored in a two column tabular format with a header row
- Ideally, keep your dates in the first column and values in the second
Creating the Forecast sheet and chart
- Select any cell in the data
- Go to Data à Forecast Sheet
- Set the Forecast End date
Forecast Options
- Can be modified by expanding the Options arrow during the creation phase
- Adjust the Forecast Start date
- Hide or adjust confidence levels (higher confidence yields a wider confidence spread)
- Adjust seasonality to reflect your data set
- Include additional forecast statistics
Hints & tips
- If your source data was just hard coded values, you can delete that worksheet as the values are copied into the forecast worksheet
- If your source data is dynamic, you can link it to the Forecast sheet to make that dynamic as well
- To create dynamic Month End dates, select cell A3, change the formula to =EOMONTH(A2,1) and copy down the table
- 00:04 The next visual that I'd like to add to my dashboard is a forecast of revenue,
- 00:10 I'm gonna go right down here.
- 00:13 Now you can build these manually in other versions of Excel if you've got
- 00:16 enough data and wizardry behind it's work out what you're forecasts are.
- 00:20 But in Excel 2016 it actually becomes really easy
- 00:23 because they've given us a button to insert one of these things.
- 00:26 Now this works for all versions of Excel 2016,
- 00:29 you don't have to be on subscription.
- 00:31 And what you'll need is some data that looks like this.
- 00:33 Notice that I've got a monthly trend here that's showing me my historical revenues.
- 00:38 From 2015 all the way through to the end of December 31st 2017.
- 00:41 So I've got three years of data here all grouped up on a monthly basis.
- 00:46 In order to insert this revenue forecast chart,
- 00:49 what I'm gonna do is I'm actually not going to the Insert tab.
- 00:52 There's no revenue forecast chart on here, it's on the Data tab.
- 00:55 And if I go here it's called Forecast Sheet, and
- 00:58 when I click on it it gives me something that looks like this.
- 01:03 Now this actually doesn't look too bad,
- 01:05 it gives me a nice little run that says here's what my historical revenues were.
- 01:09 And this is what we predict your future ones will be.
- 01:10 And then it's got these confidence boundaries that say would you like to
- 01:14 have, or it will give me something that says.
- 01:17 If all things remain equal we're pretty sure that with
- 01:19 a 95% confidence ratio that your data will fall between the top and
- 01:23 bottom line, but ideally around the middle.
- 01:26 It gives me the ability to control how far I want my forecast to go, so one year,
- 01:31 I can go even further if I want.
- 01:32 But you'll see that as cool as this looks, there are some challenges here.
- 01:36 When I open this up, you'll notice my forecast starts at this particular area.
- 01:41 It's got the Confidence Interval,
- 01:42 if I turn it off, those extra lines will go away.
- 01:46 If I put it back on, would bring it back and I can dial this up or down.
- 01:50 The challenge that I have though is that it has automatically detected that
- 01:54 the Seasonality of my business runs in 6 months cycles.
- 01:58 And it doesn't, for this particular data set, it actually runs with a year.
- 02:01 So I need to change this, now I can type this, but I want you to show or see how as
- 02:06 I dial this up and down, it will actually change the way the chart looks.
- 02:12 Once we get to 12 months, you'll notice that this pattern in orange here.
- 02:16 Is actually very similar to the pattern that I have for the first 12 month period,
- 02:19 which goes to about here.
- 02:21 And then the next 12 month period, which goes to about here.
- 02:23 And then our third year is very jagged, but it actually takes that and says.
- 02:26 Well, let's smooth it out and see what actually ends up happening here.
- 02:30 It looks like we've got a pretty good prediction of what might happen.
- 02:34 As well as confidence interval that we can play with.
- 02:37 So I could go and I could dial this down,
- 02:39 and you'll see that at this point the lines become closer together.
- 02:43 If I go and dial it back up, the lines come further apart because it wants us to
- 02:46 actually fit, or say that the data's gonna be within this range most likely.
- 02:52 When I build this, I have the ability also to fill in missing points.
- 02:55 So if there's something missing, we can interpolate the data.
- 02:57 We can do some different things in this particular area, put in Zeroes instead.
- 03:01 We can aggregate duplicates with some different functions as well.
- 03:04 We can also, when we create our chart, include some Forecast Statistics.
- 03:10 Let's click Create,
- 03:11 and what you'll see is that we actually get a new worksheet all together.
- 03:16 So our forecast data here is not linked into this new worksheet,
- 03:19 although we could certainly link it here if we wanted to do that.
- 03:24 Behind the chart, here are our summary statistics, and we can modify those and
- 03:28 they will feed into the chart as well.
- 03:31 This chart is obviously missing something important, a title.
- 03:34 So let's go Add a Chart Element, a Chart Title, Above the Chart, and
- 03:38 we'll call this guy here something like Revenue Forecast.
- 03:44 There we are, so its just a chart like any other, we can modify it.
- 03:48 Change things, take things away, add things to it, and what not.
- 03:52 The key part around this chart thought is really kind of nice.
- 03:55 Is that we actually have the ability to take a look back at the raw data that
- 03:59 made this up.
- 04:00 And as we start looking through it,
- 04:02 you'll see that the table that feeds this actually uses a bunch of formulas.
- 04:06 It's hard coded data, and my Revenue areas, the very first data point for
- 04:11 our Forecast is hard-coded.
- 04:13 But after this it all goes into used FORECAST.ETS, and
- 04:16 confidence interval functions and what not.
- 04:19 In order to actually provide some statistical validity to what's
- 04:22 going on here.
- 04:23 Why is this so important?
- 04:24 Well, when you're building something, you should always be looking at your data,
- 04:29 of course.
- 04:30 And maybe we figure out with this thing as we look through it and we say,
- 04:33 February's sales that are being used in this forecast, these were abnormally high.
- 04:38 If I look at February last year,
- 04:40 it was around $8,600, I'm pretty sure that this one isn't gonna be that big.
- 04:44 So I don't really wanna influencing the system in that way, so
- 04:47 why don't I go back and modify that, and say let's soften this.
- 04:52 And it'll change the chart, maybe you don't wanna do it there, maybe say,
- 04:55 well you know what, that's not ideal.
- 04:57 Maybe I want to go down and
- 04:59 I actually want to play around with these values that are down here.
- 05:03 And I'm saying, how about this March value here?
- 05:06 I don't want it to be in this range, for whatever reason I've got some good intel
- 05:10 that says this one should end up in this particular area.
- 05:13 I can override this value, and it will change the chart as well.
- 05:16 So this is kind of a nice thing, is that we can play with this forecast.
- 05:19 We're not stuck with what we have, we can link formulas into it.
- 05:22 We can drive it dynamically from other pieces within our workbook which is
- 05:26 kind of cool.
- 05:27 Once we're happy with it, we can grab it, Control+C.
- 05:32 We can go over to our Dashboard worksheet.
- 05:38 We can Paste it, and then, of course like usual, hold down our Alt key.
- 05:42 And resize it to make sure everything is in the top left hand corner of the range
- 05:45 we wanna put it in.
- 05:48 And it fits quite nicely, so
- 05:50 that's how we can use Excel 2016 to add a Forecast chart into our data set as well.
Lesson notes are only available for subscribers.