Forecast Accuracy in Excel

this uh tutorial is on how to calculate or measure forecast accuracy and we're going to be looking at three measures the main absolute deviation nad the mean squared deviation or mean squared error mse and mean absolute percentage error m-a-p-e so let's start with mad i've got a forecast here on the screen i'm not going to tell you how i achieved the forecast but i do have my actual values we can label this a and my forecast values which i will label are f now in order to calculate m a d e m a d is the average of the absolute errors m a d mean absolute deviation so it is the average of the absolute errors first step is to calculate the errors absolute errors so again i'm going to label this absolute uh errors or i could have just used the pipe operators you know what uh let's do that let's make it cleaner with less type in absolute represented by the pipes excel has an absolute function just simplifies what you want to do so we're going to say equals absolute that's the function of the difference between the actual minus forecast the errors is calculated as actual value minus forecast value so the absolute of that is what we want for the first one it's correct so now i'm going to double click and copy the formula all the way down and get rid of some of the zeros to reduce the clutter on this on the screen so there we have it all of the errors in the forecast now that i have the all the errors the mad is just the average of all of those right so let's calculate mad it is equal to and that's the average of all of the absolute errors so we can say that on average the errors in our forecast is 64 per month sometimes it's over sometimes it's under it doesn't matter because mad weights all errors equally so on average our error is 64 per now to calculate mse that's the second thing mean absolute mean square error i need how about i format this i i have a formatting that i like so i'm going to click on the paintbrush and paint it on the other one so that it has the same format um so to calculate the mean squared error we need the square of the error so i could just use the square of this of course so we will say it is error squared let's do two and i'm going to format it by going to the formatting section clicking on the down arrow and making it a superscript so i need error squared so for my purpose i'm just going to calculate the errors again i could have used the one i've already calculated but you know what what if i didn't calculate md what would i do so let's calculate the errors again and use the i think it's called a carrot to represent the square so that is squared that's the formula four squared press enter and it's got too many zeros i'm going to get rid of some of them i knew it now i've already i formatted the first one so when i copy the data all the way down it's going to com copy up all the formatting so let's check make sense uh click on it move the arrow to the end double click okay so this is what is should be obvious about using the mean squared errors the large values are huge when you square them the mean square errors treats the large deviation as more significant as the small deviation look at this one the weight in the error is only 11. so uh let's calculate the mean squared error it's the average but it's it's sort of different right the mean squared error has a formula that looks like sample variance it is the squared the sum of the squared deviation divided by n minus 1. if it was divided by n we would just do average because that's average sum all numbers together divide by n that's just regular averaging but we're not doing that so we have to do this manually we have to sum all of the errors and then divide by n minus one excel has a function for calculating n and it's called a count so n will say is equal to count all of these observations here then i can use that to figure out what my n minus 1 is so i already i knew it was 59 right but i'm using the count function for those situations where i'm not sure how many observations are in my sample excel has another function called formula text and i'm going to use it to print out the mad and the mse formulas okay so when the formulas show up i'm going to use it to print it out uh let's say i also like the format here i'm going to paint it down here and this formula this format i'm going to paint it awesome so now i can do m-a-p-e okay so mse is equal to the sum of all of the errors divided by total number of errors which is n minus one okay so that is what the mse looks like and finally we're going to calculate m-a-p-e m-a-p-e is percent error so we're going to calculate the percent error first and that is we take the error again uh again the error is calculated as the actual minus the forecast so the error is 21 but we're going to divide it by the actual so we have a percentage error now excel can format a value for you as a percentage but we're not going to do that we're just going to multiply by a hundred by ourselves and report it in percentages so this is three percent i think i want to put percent error here makes my screen a little bit more compact so it's three percent uh error of 21 is three percent of seven one eight uh now error this works it looks good i'm going to copy it all the way down and the average of these errors gives me the average percentage error of the forecast i'm sorry i should have used absolute error because for calculating it the errors above and below if i didn't use absolute errors when i add them together they will cancel each other out the negatives will cancel the positive and make it seem as if we don't have errors so the under forecasting is just as important as over forecasting so we're using absolute errors so there we have it these are the absolute error percentages the largest is about 25 the smallest is about well zero percent so now that we have the uh percentage errors we can calculate mape as the average of those errors average of the percent errors so on average we are off by nine percent every single month done now can i just say to you that if we uh wanted these to be displayed as percentages in excel we wouldn't multiply the errors by 100. so if i delete the 100 here press enter and then ask uh excel to display it as a percent so i've selected all of my data and i'm going to go and click on the numbers formatting and just select percentage can you see you might prefer to do this instead of multiplying it by a hundred and this i'm going to format as a percentage as well with a couple of decimal points just because so on average uh map is eight point five seven percent per uh forecast so that's the average error in here now what do you do with this accuracy measures once you've calculated it well you would do this for all of your forecasting methods so if you have two or three methods to evaluate you would use this here to figure out which one works better in other words you would use it in a comparative way the end

Channel: Dr. Dami Kabiawu

Views: 2,877

Keywords:

Id: 16BLIEBVafY

Length: 11min 7sec (667 seconds)

Published: Thu Sep 10 2020