Forecasting

Therefore we chose

Here’s why: linear, faster than linear, slower than linear, falling slowly, etc.

Here’s how we made it work.

Least squares fit of initial values 1.5, 1, .5, -.5, -1, 0 to get regression line.

Treating the regression line as a moving average, add seasonal effects to get model.

5. Show effect of changing exponents.

Full-history model: Choose some that don’t do much and some that result in dramatic changes.

Same for recent-history model.

Ask what we should do. We don’t know which FC is most likely and we don’t know which model is best, so we’ll simulate our financial future w/ inventory and see what we should do to adjust to any of the possible futures.

6. [Computer is set to EOQ, DDLT, “usual” values of parameters]

This what our design for the program looks like. Actually, it’s just some of what it looks like because we can’t fit it all on a small screen. We didn’t make it fancy because it’s just a working tool and doesn’t need it to be pretty.

This particular simulation is an Activity Scanning simulation. State of the system at t=0 (BOD), activity happens, state of the system at EOD plus summary of costs incurred and revenue generated. That creates the initial state of the system for the next day.

We can also build an Event-driven simulation. Initial state of the system, event happens, update state of the system.

Talk through layout w/o doing anything. Green input cells are for things that we can control or at least influence. These are the parameters. Talk about each one, where they come from (Accounting, Marketing, etc.) [Leave out Max, Avg, Min profit cells. That’s for next group. ]

7. And these col hdgs are the details of this particular activity-scanning simulation. These col hdgs (point to them) are the initial state of the system, next (point) are the day’s activities, next (point) are the updated state of the system. [Fill in the details of each as best as time allows and show & explain default graphs.]

And then there’s the flow of money that the day’s activities caused. Talk about each. This is repeated every day, for the 6-month FC horizon we’ve chosen. [Still haven’t touched the computer.]

8. This is something we didn’t do in this simulation, but need to mention.

We built this simulation based on the basic idea of buying and selling, and then we added costs and revenue. What we get out of this simulation can be more than inventory planning. We can track the flow of money related to this activity. We have sources (define) and sinks (define). A sink from one process might well be a source for another process, like filling and then emptying a storage tank. Since this is a time-based simulation, we can not only see where the money comes from and where it goes, we can see how much moves from here to there (volume) and how fast it moves (velocity).

By looking at sources, sinks, and velocity and volume, we can monitor the day-by-day financial condition of the department. We haven’t done that in this simulation but it can be added. If we did that, we’d see the interaction of demand, sales, orders, receipts, costs, and revenue. And we can see what changes and by how much when we alter the operational parameters of the department, which we’ll now do.

When we run the simulation, we get a profit figure based on the model and parameter values we’ve chosen, and whatever random variation happened this time. (Show cell U8) If we ran the program again, we’d get another, possibly quite different, result. (It can be different because one run might have randomly long lead times during a period of randomly large demand, for example.) So we ask Excel to run the simulation over and over – 100 times, actually – and report the Max, Min, and Avg profit. And we can get another set of 100 trials by pressing this button. (Press it)

This part of the program (show data table and frequency distributions) shows where the trials are generated (it’s called a data table) and where we arrange the data from the 100 trials for graphing.

9. Why is demand so flat when monthly FC was so up-and-down?

When we showed you the results of our forecasting models, the month-to-month sales looked quite variable. Part of that was due to limiting the y-axis to the sales range rather than setting the starting point to zero. More importantly, the time period of the 6-month forecast is for the winter months when our sales are slowest. The change in sales from September to October is only a change from 37,400 to 34,600 for the whole month, so the daily change will only be about 100 units, on average.

Exercise model-1, save parms

10. Exercise model-2, save parms

11. Exercise model-3, save parms

12. Show saved parms, introduce data analysis

Here you can see that we’ve saved all the parameters that produced a particular result. These even include the value of the exponents on each of the models. The subroutine that writes this data can easily be expanded to include any available data from anywhere on the spreadsheet.

The reason for writing down all this data is that those who are working in data analytics might be able to extract some generalizations from this data set, especially if it were to include actual results as they became available. (Otherwise, we’d simply be analyzing our simulation.)

Conclusion

Thanks for your att’n. What we’ve shown you today is…

A method for forecasting that works well if the business environment is stable (which means that we pretty much know what’s coming and don’t need to forecast in the first place) and works somewhere between OK and not-at-all for economic data.

So as most experienced managers know, forecasting is a necessary, but largely futile, exercise. It either doesn’t add any information because you already know what’s gonna happen, or it doesn’t add any information because nobody knows what’s gonna happen.

What does seem to work is a carefully designed parameterized simulation with accurate error estimates. By building a data set of parameters and results, it may be possible to learn not only what the KPIs are, but the really useful KPPs.

What we haven’t shown you are any kind of data visualizations of the interactions of all the parameters and the flow of money, as both a consequence of our operational decisions, and constraints on our future operational decisions. We’ll call that “Future Work.”

Any questions?

j

i

p

p

p

x

a

y

j

i

i

p

i

n

i

i

i

¹

¹

£

=

å

=

,

and

2

where

1