Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Forecasting Techniques

0

Looking for somebody with experience in forecasting techniques. My problem is that i would like to combine datas related to the previous demand (3 years before) with a forecast of 1 year. 

If anybody has experience on this i can provide an excel sheet sample with more details. 

Answer
Discuss

Discussion

Can't say that I have experience in forecasting techniques but I have done forecasts. The hardest part is usually reconcling the actual data with the forecast in a way that makes sense. Pehaps that is what you refer to when you speak of "combining". I would take a look at your problem once you compress it into the shape of a question that relates to Excel.
Variatus (rep: 4889) Nov 12, '18 at 9:19 am
Hi Variatus, 
Sorry for my late reply. Sure, i can send you a more detailed information. 
Do you have an email or do you prefer me to share on this chat? 
AlexPo (rep: 2) Dec 12, '18 at 10:26 am
Hi Variatus, 
Thanks a lot, 
At the moment no sensitive datas, as you can see attached it is a simple sequence of numbers, each one represent the quantity of certain product demanded each year. On the last row, there is an yearly forecast which is a rought estimation of what will be the demand for this year made by me. 
What i'm struggling with, is to find a way to combine the previous demand with this year forecast (for example considering 80% weight previous demand and 20% weight the forecast) to obtain a final number that combine the 2 in column H. 
If you have any idea or suggest some specific functions from Word, please feel free to let me know at any moment. 
BR
Alex
AlexPo (rep: 2) Dec 19, '18 at 4:37 am
Sorry, just found that i can not attach files here. I just summarized here below, kindly let me know if the data provided this way are enough to give you some ideas. Thanks in advance Variatus. 

use 2012: 1000
use 2013: 2800
use 2014: 3000
use 2015: 1500
use 2016: 2400
use 2017: 800
Forecast 2018: 3240
AlexPo (rep: 2) Dec 19, '18 at 4:39 am
Dear Variatus, 

Sorry for my late reply and Happy New Year! :-) 
Thanks a lot for your analysis and feedback,

You are absolutely right that there is no regular demand: this is what makes it difficult to track. 
1- We did try to make an average of the previous years as well as allocate different weights to the previous years, but it did not work very well. We ended up short most of the time. 
2- Yes, i also agree with you on this. The reason i put it there, is because the future forecast is not reliable as there are weekly changes on the demand due to the outsourcing of some of the projects: as such this forecast is just as general reference to understand whether demand will increase a lot or will decrease a lot: but i cant use it to know for example whether the next year there will be an increase of 15% or so. Unfortunately we don't have anything more reliable then this. 

For the other 2 points, i totally agree with you. 

Currently i'm also thinking about another thing: if the result of all of this is an a higher number compared to what will be the real demand, the effect will not be so negative. However, if the number resulting out of all these calculation is lower then the real demand, it will have some quite big negative effects. 

Do you think it would make sense to just take the number with the highest demand of the previous years? 

AlexPo (rep: 2) Jan 5, '19 at 4:26 am
Take the highest imaginable value: If your job is not to run out of stock that would be the commendable solution. Unfortunatley, that will run into major objections from the controllers who try to minimise capital outlay. I have been dealing with this problem for many, many years. Something resembling a solution has evolved by sharing the risks. Some stock can be kept by the supplier, on call. Delivery time can be reduced by stocking materials (with the supplier), based on the fact that materials require lower capital outlay than finished product - and it's the supplier's capital.
My own view has changed over the years to consider my own stock as only a part of a problem which can't be optimally solved - if at all - by me alone. However, that idea merely shifts the problem from putting up money to entering commitments to put up money in the future. The risks are lower, no doubt, but they arrive at the doorstep of decision makers who are ill equipped to deal with them. In my case, the company structure doesn't allow for the optimal solution. It's easier to allow stocks to run out, as they have always done, and blame the guy who made the forecast. He should get a better crystal ball.
Variatus (rep: 4889) Jan 5, '19 at 5:03 am
Add to Discussion

Answers

0
Selected Answer

You have a product that has no regular demand. Therefore whatever forecast you make is a guess. You can apply some lipstick to it but it's still only a guess. Here is the lipstick.

  1. You might weigh last year's demand with whatever percentage you like, or the average of the past 5 years, or both last year's and an average of past years, or allocate different weights to numbers from previous years (the younger, the higher).
  2. I would not recommend to allocate a minor weight to the forecast for political reasons. Somebody put some thought into that number (hopefully). To plan for 1288 pcs in your example (weighting 80:20) has no logical foundation.

The alternative to guessing is to try and guess intelligently. That isn't much of an improvement but that is what people do.

  • The forecast may (should) consider offers submitted to potential customers. Therefore it is useful to look at the percentage of offers which resulted in orders in the past and, perhaps, the delay with which this occurs (to possibly push some of the quantities into the following planning period). Then look at the current economic outlook in your branch of business and try to extract some logic from it that might affect the realisation rate.
  • Look at the relationship between past forecasts and actual use.

Putting the two above branches of thought together, some predictions about future development are possible based on the trajectory of a past performance curve. It would be moot to discuss the merits of that here because your product's past performance curve doesn't have a trajectory. There is nothing to be learned about the past that could be applied to the future, even if that were otherwise commendable. Therefore your only logical course of action is to look at the method of how the projection was made and apply other trajectories to the resulting numbers, such as whether your company and/or its market is growing, and try to find a measure of performance of the sales team.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login