Using Excel's Forecast Function in Inventory Operations

3 Views
What do you think about this article? Rate it using the stars above and let us know what you think in the comments below.
The first thing you need to know is that the Forecast function in Microsoft Excel isn't a complete system for forecasting inventory. Inventory management forecasting usually requires us to remove the noise from the demand, then calculate and incorporate seasonality, trends, and events. The Forecast function just can't do all of this for you. However, it's a useful function that's not too hard to get used to, and can be a worthwhile part of a larger forecasting system.

Microsoft Help tells us that the Forecast function returns the predicted value of the independent variable for the specific value of the independent variable. It uses a best fit linear regression to do this. That's great to know. But for people in operations management jobs, what does it really mean? After all, reciting a bit of technical sounding information won't get you a worthwhile forecast. Let's have a look. If you're in an operations career, you need to know.

First, we'll check out linear regression. It's a type of regression analysis that can be used to calculate a relationship between two or more data sets. This is used in forecasting when you think one set of data might be usable to predict another set of data. Someone who sells building supplies might notice that interest rate changes predict the sales rate for their products, for instance. This is a classic example of how regression can be used to calculate the relationship between the external variable (here it's interest rates) and the internal variable (which is sales). You can also use regression to calculate relationships inside just one set of data.



The usual approach towards regression analysis involves determining not only the mathematical relationship, but also the validity of that relationship. It's important to know that the Forecast Function won't do the second part - it skips the analysis and only calculates the relationship, automatically applying it to the output. This is easier for the user, but it doesn't make sure the relationship is valid. That part is up to you.

Of course, the classic method of applying regression isn't one size fits all. It might not be the right method for what you need. So don't apply the Forecast function blindly. Certain types of data just won't work correctly, and won't give you the right information about trend. If you just have one set of data, you'll need to look for your relationship differently. Usually, it's going to be time based. Use the demand for each period as the predictor variable for the demand in the following period.

If you remember that the Forecast function isn't a forecasting system, you'll be able to get the most use out of it. You'll need to remove the elements that aren't related to the base demand and trend (or other variables) as well as the effects of seasonality or promotion before you use it. Apply your seasonality index and event index to the output of the Forecast function. You may need to play with your input to get the result you want. However, for people in operations management jobs, the Forecast function can be very helpful.
If this article has helped you in some way, will you say thanks by sharing it through a share, like, a link, or an email to someone you think would appreciate the reference.

Popular tags:

 trends  relationships  methods  functions  operations managers  inventory  forecasts  inputs  promotions


What I liked about the service is that it had such a comprehensive collection of jobs! I was using a number of sites previously and this took up so much time, but in joining EmploymentCrossing, I was able to stop going from site to site and was able to find everything I needed on EmploymentCrossing.
John Elstner - Baltimore, MD
  • All we do is research jobs.
  • Our team of researchers, programmers, and analysts find you jobs from over 1,000 career pages and other sources
  • Our members get more interviews and jobs than people who use "public job boards"
Shoot for the moon. Even if you miss it, you will land among the stars.
OperationsCrossing - #1 Job Aggregation and Private Job-Opening Research Service — The Most Quality Jobs Anywhere
OperationsCrossing is the first job consolidation service in the employment industry to seek to include every job that exists in the world.
Copyright © 2024 OperationsCrossing - All rights reserved. 21