Forecast Sheets (Excel 2016)

Quick reference

• 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.