Friends
I believe I am correct in saying that computers used in forecasting trends make
extensive
use of Poisson Tables? I am currently involved in developing a VERY simple but
extensive
spreadsheet (in MS Excel) with the objective of determining likely demand for
very high
cost but low sales volume parts issued in units of one. There are no more than
about 90
different parts in focus.
The stocking and acquisition cost is high but availability is crucial because
these are
expensive components.
The 'easy out' is to always ensure one part is available at all times and as
each part is
issued it is immediately replaced in time for the next demand. The high overall
unit cost
precludes this.
My principals are prepared to forego occasional unit sales through lack of
availability
and would ideally like to forward plan a stock unit inflow next period or the
period after
that if at all possible. This just saves them money and having seen the costs
they have to
face, I can only support their wishes.
Unit demand is fairly erratic and time periods are conveniently broken in
fortnightly
sections. Several fortnights can pass without a supply demand for any one
particular part
being made and then one unit can be issued in each of the next three or so
consecutive
periods - and then nothing more for a while. I know Henry Ford is once alleged
to have
said that "history is bunk" but TTBOMK forecasting future requirement places
heavy
emphasis on historical demand and I believe this is something that Monsieur
Poisson
realised when he developed his tables so long ago.
I have got some way in tracking these issues from sales history provided and am
presently
looking at about 4 years past unit sales. What I have done is to create a
spreadsheet
where each product has its own column and as each issue occurs, this is
signalled by an
'x' in text. I could easily change it to a number. I can obviously determine
the total
number of issues using the =COUNTA(:) function but this only gives me a total
number of
units issued. Equally, using a straight line average would probably be
inappropriate and
an exponentially weighted one would be better?
I need someone to (please) give me an idea as to what formula I need, how to
write it, and
for the answer in the spreadsheet cell to provide an indicator of when the next
issue for
that part is likely to raise its head in terms of the number of issue periods
likely to
elapse from the last one until the next one, based on previous demand
volatility and
vagaries. You get the picture?
In a nutshell, I think I'm looking at what are essentially the Laws of
Probability - and
my Principals are prepared to run with an alternative form of forward
forecasting if one
can be worked out. I believe it can and I don't think the formula to calculate
that likely
figure will be too difficult to work out. That said, their current forecasting
methods are
rudimentary in the extreme and have already proved costly to use. A degree of
refinement
is needed, I'm no mathematician and I'm stuck ! Can anyone lighten my darkness?
FWIW,
I've already studied what I can find in various 'Dummies' guides - and I'm no
nearer the
truth.
Jonmac
Current Book: IN THE SHADOW OF MY FATHER
http://www.toolbox.ndirect.co.uk/triumphbook
The SLOW progress with 'Canley Girl': http://www.toolbox.ndirect.co.uk/
Triumph Charity Run in 2000 (in UK): http://www.toolbox.ndirect.co.uk/stories/
|