If you receive errors when attempting to view this white paper, please install the latest version of
Adobe Reader.
"Identifying market trends is essential to good demand planning and
peak performance in the manufacturing and retail industries. Consumer Driven Planning for Microsoft
Dynamics AX provides insight into demand trends, helps you align your internal processes and policies around accurate
demand forecasts,
and enables you to shape customer demand with effective promotions and campaigns. "
Source : Microsoft
How to Improve the Accuracy of your Forecasts
Forecasts is also known as :
Demand Forecasting,
Demand Forecasting & Inventory,
Multi-dimensional Forecasting,
Demand Works,
Improve Demand Planning,
Forecast Plan Cuts Costs,
Forecasting Software,
Predict Customer Demand,
Calculating Demand Forecast,
Realized Demand Quantity,
Demand Forecasting Activity,
Demand Forecasting Methods,
Demand Forecasting Models,
Toolkit Demand Forecasting,
Methods to Forecast Demand,
Planning Forecasting,
Demand Forecasting Data,
Demand Forecast Accuracy,
Demand Forecasting Data Cleansing,
Demand Forecast Prediction,
Determine Demand Forecast,
Forecasting Needs,
Retail Demand Forecasting,
Time Series Forecasting,
Define Demand Forecasting,
Demand Forecasting Excel,
Demand Planning,
Article on Demand Forecasting,
Demand Forecasting Activity,
Increase Forecast Accuracy.
Helping Distributors Maximize Business Potential
Through Education and World-Class Technology
From Microsoft Business Solutions
Our goal is to help distributors reach their maximum business potential by delivering connected solutions designed to meet unique business
processes through trusted partnerships and ongoing service.
This report is the third in a series of white papers designed to help forward-thinking distributors increase efficiency, customer service
and profitability with smart inventory management strategies based on tried and proven methods and best practices.
The author, Jon Schreibfeder, draws from decades of experience helping more than 1,000 distributors achieve better inventory
management. A popular speaker at distribution conferences, Mr. Schreibfeder has literally "written the book" on this topic, with Achieving
Effective Inventory Management now in its second edition.
As a leading provider of specialized distribution and business management systems, Microsoft Business Solutions is pleased to sponsor
this series.We are committed to serving the success of companies in the distribution industry through education and world-class technology.
Improving the Accuracy of Your Forecasts
It's Tough Being A Distributor In Today's Market
- Increased Competition.
Competition continues to increase as new distribution channels evolve and existing distribution channels expand. Twenty years ago most
distributors existed on "market islands". They may have had a few competitors but they knew how these other firms conducted business.
A number of developments including the Internet, dynamic data processing capabilities, and faster, more reliable transportation have
drastically changed the distribution environment. Customers have more options to choose from when looking for sources of supply.
- Lower Margins.
This "buyers market" has forced many distributors to lower their profit margins in order to remain competitive.
- More Customer Demands.
Lower margins are not the only result of this increased competition. Customers are in a position to demand more value added
services and greater product availability.
The result: Distributors have to provide better material availability and more services with fewer profit dollars. They have to do more with
less. In order to accomplish this goal the estimates of future usage of stocked items must be as accurate as possible. In this document
we will explore some ideas we have found to be effective in developing accurate demand forecasts for your stock products.
Traditional Forecasting Methods
One of the most common methods distributors utilize to forecast future demand of products is to average the usage recorded over the
previous several months. Consider the usage history of this product:
|
December |
January |
February |
March |
April |
May |
June |
July |
| Usage |
78 |
100 |
133 |
145 |
90 |
154 |
80 |
? |
To forecast demand for July, we might average the usage recorded over the previous six months:
(100 + 133 + 145 + 90 + 154 + 80) ÷ 6 = 117 pieces
As the following graph shows, a forecast of 117 pieces seems to be a reasonable estimate of July's usage (the green line reflects the
forecast for July):
The Effect of Unusual Usage
But averaging past usage does not always result in an accurate forecast of future demand. If the distributor experienced unusually large
sales of a product, averaging the usage in the past six months would result in an inaccurate forecast. For example, suppose the distributor
experienced an unusual 1,000 piece sale of the product we examined before (i.e. usage in June is 1,080 pieces instead of 80 pieces):
|
December |
January |
February |
March |
April |
May |
June |
July |
| Usage |
78 |
100 |
133 |
145 |
90 |
154 |
1080 |
? |
Averaging the previous six months results in a forecast of 284 pieces:
(100 + 133 + 145 + 90 + 154 + 1080) ÷ 6 284 pieces
But is 284 pieces a good estimate of July's demand? Probably not. To help ensure forecast accuracy, we must adjust usage history for any
unusual activity that will probably not reoccur. It is a good idea to examine all instances where the demand forecast differs significantly from
actual usage. Abnormally large sales are just one type of unusual activity. Consider a situation where there was no usage of the product in the
month just completed:
|
December |
January |
February |
March |
April |
May |
June |
July |
| Usage |
78 |
100 |
133 |
145 |
90 |
154 |
0 |
? |
A forecast based on the usage recorded in the previous six months equals 104 pieces:
(100 + 133 + 145 + 90 + 154 + 0) ÷ 6 104 pieces
Neither 284 nor 104 pieces appears to be a good forecast for July. To ensure the accuracy of demand forecasts, it is critical that buyers or
salespeople examine possible unusual usage. A report or inquiry should list products whose usage in the month just completed is greater
than "x" percent, or less than "y" percent, of the forecast. For example, some distributors will scrutinize any item whose usage is greater
than 300% or less than 20% of the predicted demand. These percentages are not "cast in stone" and should be modified to meet each
distributor's specific situation. There are three reasons why an item would be included on this possible unusual activity list:
- Activity that will not reoccur. This includes abnormally large sales as well as unusually low usage that was caused by stock outs,
temporary customer shutdowns or some other reason.
- The start of a new sales trend. There is a dramatic increase or decrease in usage that is representative of probable future
usage of the product.
- The wrong formula is being used to forecast future demand of the item.
If the possible unusual usage was caused by activity that will not reoccur, usage should be adjusted to equal what usage would have been
under "normal" circumstances. If a new sales trend is detected, you might want to either adjust past usage to reflect current market conditions
or override the actual forecast until adequate history that reflects the new trend has been accumulated.
Different Patterns of Usage Require Different Forecast Formulas
It would be wonderful if we could forecast future demand of every product by averaging the usage (or adjusted usage) over the previous
six months. But we have found that different patterns of usage require different forecast formulas. We've also discovered that an average
of past usage is just one element of a good forecast formula. In fact comprehensive forecasting considers four elements:
- A weighted average of past usage
- An optional trend factor
- Possible collaborative information from customers and/or salespeople
- Identification of the proper time frame for the forecast, also known as the forecast horizon
Weighted Average of Past Usage
Look at this product's usage history:
|
December |
January |
February |
March |
April |
May |
June |
July |
| Usage |
78 |
80 |
90 |
100 |
133 |
144 |
156 |
? |
A forecast for July calculated by averaging the previous six months usage is again 117 pieces
[(80 + 90 + 100 + 133 + 145 + 156) ÷ 6 117]. But notice how usage has increased over the past several months:
It is logical that June's usage of 156 pieces should have more of an effect on July's demand than January's usage of 80 pieces. We need to be
able to emphasize the history of certain months in our forecast demand calculations. This can be accomplished by utilizing a set of weights with
the average usage calculation. For this item we will place the greatest emphasis or weight on June's usage and gradually decrease the weight
over the previous four months:
| Month |
Usage |
Weight |
Extension |
| June |
156 |
3.0 |
468.0 |
| May |
145 |
2.5 |
362.5 |
| April |
133 |
2.0 |
266.0 |
| March |
100 |
1.5 |
150.0 |
| February |
90 |
1.0 |
90.0 |
| Total |
|
10.0 |
1336.5 |
The total extension of 1336.5 is divided by the total weight of 10 to equal a weighted average of about 134 pieces. A forecast of
134 pieces appears to be better than the previous estimate of 117 pieces, but it will probably still fall short of July's actual usage.
But remember that past usage is just one element of a comprehensive forecast.
Trends
No average of past usage can result in a forecast that is greater (or less) than the largest (or smallest) usage quantity included in the
calculation. If usage is either consistently increasing or decreasing over time, a "trend factor" should be applied to the results of the weighted
average formula. Going back to our example, let's look at how usage has increased, month to month, over the past several months:
|
March |
April |
May |
June |
July |
| Demand |
100 |
133 |
145 |
156 |
33.0% +9.0% 7.6%
This item has experienced an average increase of 16.5% [(33.0 + 9.0 + 7.6) ÷ 3] over the previous four months. If we increase the
weighted average of 134 pieces (calculated above) by 16.5% the result is a forecast of 156 pieces. This is probably a fairly good
forecast considering that the percentage increase in usage, month to month, is gradually getting smaller.
It is important to note that a trend percentage should not be applied to every forecast calculation. After all, not every item has a
consistent increase or decrease in usage over time. Applied trend percentages also may have to be adjusted to reflect changes in
interest rates, general business activity, housing starts or other economic factors.
Finding the Best Forecast Formula
Because various weights can be applied to any previous month's usage, there are literally thousands of sets of weights that can be used to
forecast the future demand of products. So how do you determine what set of weights to use with each item? Though this may seem like a
formidable task, it is actually not that difficult.We typically start by selecting the eight most common sets of weights. In this example we are
forecasting demand for March, 2003:
|
Feb '03 |
Jan'03 |
Dec'02 |
Nov '02 |
Oct '02 |
Sep '02 |
Aug '02 |
Jul '02 |
Jun '02 |
May '02 |
Apr '02 |
Mar '02 |
| A |
3.0 |
2.5 |
2.0 |
1.5 |
1.0 |
|
|
|
|
|
|
|
| B |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
|
|
|
|
|
| C |
1.0 |
1.0 |
1.0 |
|
|
|
|
|
|
|
|
|
| D |
5.0 |
2.0 |
1.0 |
|
|
|
|
|
|
|
|
|
| E |
1.0 |
|
1.0 |
|
1.0 |
|
|
|
|
|
|
|
| F |
|
|
|
|
|
|
|
|
|
|
1.0 |
2.0 |
| G |
|
|
|
|
|
|
|
|
|
1.0 |
1.0 |
1.0 |
| H |
|
|
|
|
|
|
|
|
|
|
1.0 |
4.0 |
A spreadsheet is created to calculate the forecast for each item, using each formula (with and without a trend factor), for each of the past six
to twelve months. Each forecast is compared to the actual usage (or adjusted usage) for that month. The formula that has the lowest average
forecast error (the difference between the forecast and actual usage) for an item will be assigned to that product to forecast demand in the
future. If none of these formulas generate an acceptably low forecast error, other sets of weights will be tested.
Note that formulas F, G and H use history for the upcoming months, last year. These formulas are appropriate for season items. These are
products whose usage normally fluctuates in a normal pattern throughout the year (e.g., beach umbrellas, snow shovels, etc.). Trend
percentages applied to seasonal forecast formulas compare usage in the last several months this year to usage in the same months, last year.
If your computer system does not allow you to place weights on the usage history in forecast calculations, try averaging the usage in the
last three, four and six months to determine what average usage calculation is most appropriate for each item.
Collaborative Information
Sometimes a customer's or salesperson's estimate of future usage provides a better forecast than an average of past history. If you
can obtain reliable estimates of what will be needed, this information can be included as a component in your forecast calculation:
Results of the Weighted Average Formula
+ Effects of a Trend Percentage
+ Collaborative Forecast Information
Total Forecast
If a customer regularly supplies collaborative information for your forecast, make sure that you do not include shipments to that customer
in your usage history. If the same customer demand is reflected in both usage history and collaborative information, the resulting forecast
will reflect twice the customer's actual needs.
Forecast Horizon
Forecasting is a lot like going to a rifle range. You have to be sure to aim at the right target. That is you have to be sure that you are forecasting
demand for the correct inventory period. For example suppose a product has a 90 day lead time. you are forecasting demand at the end of
January, your forecast needs to reflect your anticipated usage in May:
After all, an order placed with the vendor will not arrive until late April or early May. Forecasts for February, March and April should have
no effect on your current replenishment plans.
Identify Products Whose Future Demand Cannot be Forecast
We've spent a lot of time discussing how to accurately forecast future demand of products. Unfortunately, like lottery winners, future
usage of some products cannot be accurately predicted. These items tend to have "sporadic" or irregular usage. Here is the usage
history of one of these products:
|
January |
February |
March |
April |
May |
June |
| Usage |
1 |
0 |
0 |
0 |
1 |
0 |
There was usage of one piece in January and one piece in May. Any average of past monthly usage will result in a forecast of less than
one piece. What are the chances a customer will ask for one-sixth of a unit?
Here is the history of another item with sporadic usage:
|
January |
February |
March |
April |
May |
June |
| Usage |
50 |
0 |
0 |
50 |
0 |
50 |
It appears that customers buy 50 pieces of the product at a time. Any average of past monthly usage will result in a forecast demand of
less than 50 pieces (e.g., 150 pieces divided by six months equals 25 pieces per month). But even a forecast of 50 pieces per month
will not be accurate. After all, the item has sporadic usage. Fifty pieces are not sold every single month!
Products with sporadic usage can usually be identified as those whose average quantity sold or used in one transaction is greater than the
average monthly usage. Instead of a forecast of future demand, these products should be maintained with a target stock level. The target stock
level is a multiple of the average or normal quantity used in one transaction. For example, you might want to maintain a target inventory of one
normal usage quantity of the product sold fifty pieces at a time:
Minimum = 50 pieces
Maximum = 50 pieces
When the stock level drops below the minimum of fifty pieces, enough of the product will be ordered to bring the stock level back up to fifty
pieces. If the item has a long lead time or requires a very high level of customer service, you might consider maintaining a target stock level
of two normal use quantities:
Minimum = 100 pieces
Maximum = 100 pieces
Accurate demand forecasts are a critical factor in achieving effective inventory management. If you do not have good estimates of future usage,
you are forced to overstock in order to maintain a high level of customer service. This is the equivalent of adding "fat" to your warehouse. It costs
a lot of money to maintain this excess inventory, money that probably could be put to better use. In today's competitive environment you must be
"lean and mean" to prosper and maximize your company's profitability. You need to develop the most accurate forecast of future demand possible
for every stocked product in your inventory!
Leap Ahead with Microsoft Business Solutions for the Distribution Industry!
Microsoft Business Solutions offers an integrated set of specialized distribution and business management systems that are specifically
designed to meet the needs of the distribution industry. You'll find deep functionality in our solutions such as inventory, order and purchasing
management, sales forecasting, e-commerce and warehouse management. These distribution-focused modules integrate smoothly with dozens
of business management systems to meet the diverse needs of your business, including accounting, customer relationship management (CRM),
human resources/payroll, supply chain management, distribution and more.
It's all designed to help you improve profitability by streamlining and connecting every step of your operations - from inventory and sales
order management through forecasting and financial reporting. And it comes packed with tools to help dramatically reduce costs, eliminate
time-consuming processes and allow 24/7 access to information across your entire organization.
With Microsoft Business Solutions for the distribution industry, you can build a total enterprise solution that's simple and affordable. It will
empower you to:
- Make smarter, faster business decisions
- Improve employee and business productivity
- Gain a competitive advantage
It's a Safe Choice - It's Microsoft.
You can rely on Microsoft to provide the foundation and
resources to support your company's important goals.
With more than 200,000 customers worldwide, Microsoft
Business Solutions is a proven performer in thousands of
distribution-focused companies.
With Microsoft Business Solutions, you no longer have
to be a large organization to enjoy the big-league
advantages of powerful business-driving applications.
From small, networked systems to large client/server
solutions, Microsoft Business Solutions can be
customized to your needs. We can do this by
leveraging a range of proven, world-class distribution
management systems.
Backed by the finest support services in the industry,
these products are based on industry-standard Microsoft
technology. They share a common code set and connect
easily into the Microsoft development platform, so
leveraging information across applications and the Internet is now simple and effective. And they're fully compatible with familiar
Microsoft productivity tools such as Microsoft Office. Easy-to-use and fully customizable, Microsoft Business Solutions products deliver
superior integration capabilities with other systems to help you achieve a truly interconnected experience.
Microsoft Business Solutions for the distribution industry connect easily with the Microsoft
platform - a highly versatile environment that scales to meet nearly any business software
protocol. From Windows applications to specific industry programs, Microsoft integrates seamlessly
with your existing and future enterprise systems, providing solid and reliable performance
You don't have to wait to get a head start!
With the Microsoft Capital program, you may already qualify to have your complete distribution solution financed. There's a certified
Microsoft Business Solutions partner near you - ready to customize your solution to meet your unique requirements.
Call toll-free for more information on how Microsoft Business Solutions can move your business to the head
of the pack: 888-477-7989, option 1.