How to Manage Seasonal Fluctuations in Sales

As the holiday season approaches, many small and mid-size retailers are looking to better manage seasonal sales by studying their supply chains, buying the right inventory, and limiting their exposure to leftover merchandise.

A lackluster 2009 holiday sales season came on the heels of a ruinous 2008, when retail sales plummeted 3.9 percent. This year, the National Retail Federation (NRF) is hopeful that retail sales will increase a moderate 2.3 percent this year to $447.1 billion.

As retailers prepare for seasonal sales and how to accommodate fluctuations between Thanksgiving and Christmas, some things are different this year. Businesses are tech-savvy and trying to leverage new ways to drive sales, such as reaching consumers on their mobile phones and providing ways to accommodate the growing numbers who want to be able to shop 24/7.

But some aspects to managing seasonal sales fluctuations always manage to stay the same, year in and year out. "For most retailers, that fourth quarter is extremely important for their overall profitability," says Dan Butler, NRF's vice president for merchandising and retail operations. "For retailers, if you have a lackluster February or August, or back-to-school season, you can usually make up for some of that shortfall at other times of the year." That is often not true for the winter holiday season, however.

The following article will go over ways to plan for seasonal fluctuations in sales, how to best manage inventories, and when to start marking down merchandise.

Managing Seasonal Sales: Plan for Fluctuations

The best way to manage seasonal fluctuations, and maintain positive cash flows throughout the year, is to develop detailed sales and inventory plans before the season begins, use those plans to guide your merchandise purchases, and as benchmarks in-season to guide your progress, according to Ted Hurlbut, the principal of Hurlbut & Associates, a merchandising and inventory management consulting firm based in Foxboro, Massachusetts.

"Planning takes time -- time you may not think you have -- but invariably those independent retailers that take the time to carefully plan their sales and inventory are far more profitable than those that don't," Hurlbut says.

Before you begin the planning process, you will need to know what you've sold in the past, and how much inventory you had on-hand to generate those sales. That means using your point-of-sale (POS) system as a resource. "While effective planning goes far beyond merely what you did last year, this information is an important reference point," Hurlbut says. "You will need to extract that data from your POS system, by category and month. Unfortunately, many POS systems do not maintain a history of monthly inventory levels, so all you may be able to extract is sales data."

Second, you will need to determine the unit of measure that you will plan with. The two primary options are to plan in units or in retail dollar value. "In almost all instances, I recommend planning in retail dollars," Hurlbut says. "If you are going to do your planning in units, be clear in your own mind why units are the way to go in your particular business, and planning in retail dollars is inappropriate."

Managing Seasonal Sales: Building a Plan

To start, you need to develop a sales plan. For independent retailers, most sales plans are broken out by category and month. In some cases, especially highly seasonal businesses or categories, it may be more appropriate to plan sales by the week. "The question to ask is a very basic one: 'What is the most likely level of sales from stock (excluding special orders) by month (or week)?'" Hurlbut advises. Note that the question is what the most likely level of sales is, not what's the most you could possibly sell, he says. A business could easily get in trouble by planning on the latter.

The following are considerations in developing your seasonal sales plan:

• Review the prior year's sales histories. Make allowances and adjustments for unusual events, such as weather, out of stocks, one-time promotions, etc., Hurlbut says.

• Factor in the appropriate increase or decrease. Based on your current sales trend and your reading of the sales potential of the category for the upcoming season, you may forecast for higher or lower seasonal sales. For larger categories, it may make sense to break the sales plan down further, by sub-categories, styles or vendors.

• Marketing plan. How are you going to reach potential customers? In addition to advertising and direct mail, the Internet has opened up a wealth of new ways to reach customers. Hopefully, all year long you have been collecting customer e-mails so that you have a manageable list of contacts. Some companies are also toying with location-based advertising to reach customers when they are near by a store and lure them in with promotions.

• How to differentiate your business. One common issue for many independent retailers is how to remain competitive with national chains in your market and other retailers, particularly if they offer customers low prices. Butler suggests thinking along the lines of these questions: "What makes my store unique and different to customers? What makes me distinctive? What do I offer that they don't?"

• Hours and staffing levels. Determine whether you need to add seasonal staff to help make this a banner year, or work with staff to stagger schedules so that your business can be open when people are available to shop -- which may involve more nights and weekends. "Many retailers are adjusting their hours. Maybe they close on Mondays or are only open Monday afternoon," Butler says. "For many retailers, Sunday has becomes the second busiest day of the week."

Managing Seasonal Sales: Buying Inventory

For many independent retailers, the largest asset on the balance sheet is inventory. "Inventory is the 'active' asset, which generates the business's sales and profits," Hurlbut says. "But without careful planning, inventory can easily get out of line, resulting in heavy markdowns due to overstocks and, ultimately, serious cash flow problems."

For retailers whose businesses are subject to seasonal fluctuations, the challenge of managing inventory levels is magnified. Seasonal fluctuations in sales levels require that inventory levels anticipate both the seasonal peaks in sales as well as the seasonal ebbs.

Plan inventories
Once a sales plan has been developed, the next step is to build an inventory plan. "The question to ask is this: 'How much inventory do I need at the end of each month to support the next month's sales, as well as maintain effective merchandise displays?'" Hurlbut says. In some cases, the ending inventory may need to support more than just one month of future sales.

It makes little sense to bring in more inventory at any given time than you need to set your displays, support your planned sales until the next vendor delivery, and provide a safety stock in the event of an unexpected sales spike or a late delivery, Hurlbut says. Committing to inventory too far in advance, and then bringing it in all in one shot is one of the surest ways to find yourself over-stocked down the road.

Plan discounts ahead of time
There are two primary types of discounts a retailer might take:

• Promotional discounts during the season
• And clearance markdowns as the season winds down.

Planning these discounts goes hand in hand with planning sales and inventories if you are using retail value as your unit of measure. "A discount, just like a sale, decreases the retail value of your inventory on hand," Hurlbut says.

Keep in mind that any retailer needs to protect gross margins and cash flow when planning clearance markdowns. "If you plan the date of the first seasonal markdown before the season even begins," Hurlbut says, "you can plan the inventory you want to have on hand at that point in time, and thus your markdown percentage.

If you've planned sales by month, ending inventories by month, and discounts by month, it's easy to calculate how much inventory to bring in each month, by category. Hurlbut says retailers need to bring in enough to cover that month's planned sales, planned discounts, and planned ending inventory, less the prior month's planned ending inventory. "In this way, for example, a buyer can know before a season begins how much inventory to plan on bringing in each month of the season," he says.

Once inventory receipts have been planned, the next step is to plan how to execute those receipt plans. Ask yourself: How much of my receipt plan do I want to commit to buying now, before the season begins?

"The pre-season commit percentage is the percentage of the season's receipt plan that you commit to before the season begins," Hurlbut says. "It's the bets you place before the season has even opened up." Every seasonal retailer has to place these bets. A seasonal retailer has to commit to enough inventory to set displays and cover early sales, sales which are a critical early indicator of the season to come. Similarly, a retailer frequently has to commit up front to merchandise scheduled for delivery later in the season to assure they'll have core stocks of key items and categories at that critical time.

Managing Seasonal Sales: Take Markdowns Expeditiously

The process doesn't end once the holidays are upon us. It's a continuous process. In-season planning is even more important. "As each week goes by, and sales trends begin to develop, adjust your sales plans accordingly, and adjust inventory plans for those updated sales plans," Hurlbut says.

When sales start to fall behind plan, it's very tempting to think that you'll make up the sales later in the season. But when sales fall behind plan, inventories begin to back up as well. When inventories back up, pressure builds on prices, which, if not addressed, can lead to steep markdowns that decimate margins. The first thing to do is adjust future receipts to get inventories back in line, but it usually doesn't end there.

"When sales are soft, the weakest of your items or categories will usually suffer disproportionately," Hurlbut says. "They simply aren't as desirable at their full retail price." As soon as you identify these items, mark them down. A 25 percent markdown, for instance, taken immediately, will accelerate their rate of sale and get you out of that inventory, he adds. But if you wait until clearance time, when everything is marked down, it may take 50 percent to 75 percent to clear the inventory.

Look behind the sales to understand why you are taking markdowns. "You have to be aware and smart. Don't just say, 'It's not moving fast enough,'" Butler says. "Why is it not selling? Do customers not like it? Or are they not seeing it?" Make sure that you are pricing the items right to begin with, particularly in this economic climate. "You want to be very sensitive about how you price merchandise," Butler says. "The customer is not looking for everything to be marked down. They're looking for good values, and there is a balance between value and price."

Managing Seasonal Sales: Recommended Resources

National Retail Federation's 2010 Holiday Sales Headquarters - Retail association's website for seasonal sales statistics and holiday survival guide.

RetailSales.net - A collection of resources aimed at helping independent retailers develop the skills required to thrive in today's intensely competitive business environment.

The Retail Owners Institute - Self-help resources for retail owners.

http://www.inc.com/guides/2010/10/how-to-manage-seasonal-fluctuations.html

To Check or Not to Check: Understanding Landed Cost's Invoice Match and Revalue Inventory Options in Microsoft Dynamics GP

Dynamics GP presents two selection options in the Landed Cost Maintenance screen: Invoice Match and Revalue Inventory. These two options have profound accounting effects in how inventory and purchases are tracked in GP. The following is provided as is and was posted by Marge Swanson, Senior Software Development Engineer at Microsoft in response to a user's question on the Dynamics GP community board, but I felt it was important to rescue as it contains valuable information not found in the manuals or elsewhere:

You should determine whether or not to mark the Invoice Match checkbox based on how you want your distributions to be tracked for the Landed Cost. If you do not mark Invoice Match, the distributions will be reversed from the accrued purchases account used on the Shipment – which defaults from the Landed Cost card. If you mark Invoice Match, the distributions will be created with the Purchase Price Variance account on the Landed Cost Maintenance window.

This helps you track the variances to a separate account if you want to. If you also mark Revalue IV when you mark Invoice Match, the distributions will assigned to the inventory account associated with the item the landed cost is applied to. The cost basis for the item will also be updated for any cost variance on the Landed Cost.

Example: Invoice matching and distributions for landed costs

Marking the Invoice Match option for a landed cost record will affect account distributions. For example, suppose that a shipment is recorded for 10 items at $1 each. The landed cost uses the Flat Amount cost calculation method, and the flat amount is $0.50.

The distributions for the shipment would look like this:

Inventory                                  $10.50
Accrued Purchases - Landed Cost            ($0.50)
Accrued Purchases – Inventory             ($10.00)

Suppose that when the invoice is received, the cost of the goods is unchanged, but the landed cost has increased to $0.75. If Invoice Match is not marked, the account distributions would be as follows.

Accrued Purchases - Landed Cost             $0.75
Accrued Purchases – Inventory              $10.00
Accounts Payable                          ($10.75)

If Invoice Match is marked and the Revalue Inventory option is marked for the cost variance, the account distributions would be as follows:

Accrued Purchases - Landed Cost             $0.50
Accrued Purchases - Inventory              $10.00
Inventory                                   $0.25
Accounts Payable                          ($10.75)

If Invoice Match is marked and Revalue Inventory option is not marked for the cost variance, the account distributions would be as follows:

Accrued Purchases - Landed Cost             $0.50
Accrued Purchases - Inventory              $10.00
Purchase Price Variance                     $0.25
Accounts Payable                          ($10.75)

Until next post!

Land of the Landed Costs- Part 1 & 2

Landed cost is the total cost it takes to "land" an item on your doorstep, including additional charges like freight, customs, and processing. More and more, I find that companies are interested in landed cost as they increase the use of international suppliers and manufacturers. By using landed cost, the "total" cost of the item is reflected in inventory and is valued as an asset, rather than immediately expensing those additional costs. Take the following example, where I purchase a widget from my European supplier:

• Purchase 100 widgets @ $20/each
• Overseas shipping costs are $1500
• Customs, Duties, and other Processing Fees are $500
• Domestic shipping costs are $400

If I were to value my widgets only at the cost from my vendor, the cost in inventory would be $2000 for the 100 widgets at $20/each. And then I would expense the additional $2400 in costs.
The situation changes though, if I am using landed costs. Using the same costs above, I would value my inventory at $4400 dollars in total, or 100 widgets at $44/each. Perhaps a more accurate picture of the inventory value? In turn, this increased cost will affect my Cost of Goods Sold on the sales side, impacting my margin appropriately.
So, now that we have an example, how about talking through how Landed Costs work in Dynamics GP? With Landed Costs in Dynamics GP, these costs can be:

• Recorded as an estimate amount on a receipt
• Matched to an invoice and revalued to provide a more accurate amount

There are five key aspects to Landed Costs in Dynamcs GP from my perspective:

• Landed Cost Setup: Includes setup of individual landed costs, landed cost groups, and assignment to item/site combinations
• Purchase Order Landed Cost: Assigning landed cost groups to POs
• Receivings Landed Cost: Recording of landed cost groups on Receipts
• Receivings Landed Cost Apportionment: Apportionment of landed costs across an entire receipt
• Invoicing Landed Cost: Recording invoices for landed cost vendors and matching them back to the original product receipts

Let's take a look at each aspect. In this post, I will cover the setup items. And in my next post, I will cover the actual transaction entry. First, Landed Cost Setup.

Cards>>Inventory>>Landed Cost

This is where you can set up each individual landed cost that you plan to track. If you plan to match invoices for the landed cost, you will want to put in a Vendor ID. Matching invoices for landed cost means that (in this example above), I could record the actual insurance invoice from Associated Insurance and match it back to the shipments from my supplier for the product I purchased.
You can also select a cost calculation method, this will be how the landed cost estimate on the receipt will be calculated. If you have chosen to match invoices, the invoice amount will be compared against the original estimate on the receipt. The resulting difference can be revalued, if Revalue Inventory for Cost Variance is marked. Otherwise, the difference will post to the Purchase Price Variance account.
The GL account specified are used in the following manner:

• Accrued Purchase Account: Used as the offset to the landed cost estimate on receipts, and cleared when the amount is invoiced or matched to invoice.
• Purchase Price Variance Account: Used for the variance between invoice and receipt when matching invoices but not revaluing.

Note that no inventory or expense account is specified because the item's account will be used, since landed cost is updating the inventory cost/value of the item.

Cards>>Inventory>>Landed Cost Groups

Landed Cost Groups are used to organize landed costs in to sets that can be assigned to item/site combinations and/or to document line items like Purchase Orders and Receipts. An individual landed cost can exist in more than one landed cost group. You might choose to have landed cost groups to represent a set of landed costs for:

• A specific location
• Certain items
• A type of shipping process

In my example above, I have chose to set up a landed cost group for my domestic shipments. It includes both the insurance costs and the freight costs. Now, I can assign this landed cost as a default for certain combinations of items and sites.

Cards>>Inventory>>Quantities/Sites

This step is not required. However, if appropriate, you can assign a landed cost group to a specific item/site combination in this window. Doing this will cause the landed cost group to default on the purchase order line item, where it can be changed if necessary.

More soon on the transaction side of all of this fun!

So, here I am with part two of the landed cost saga finally. Last time, we went through the process of setting up landed costs. In our example, we were setting up insurance costs so that the invoice from the insurance vendor could be matched back to the shipments received from the product vendor. But, before we get down the road that far, let's overview the three key ways that landed costs can be used.

Landed Costs by line item

• Specify a landed cost group on a line item when recieved (can default from the item setup, from the purchase order, or manually entered)
• Calculated amounts can be adjusted by line item on receipt

Landed Costs by apportionment

• Specify an individual landed cost for the entire receipt document
• Apportion the amount across the items on the receipt by qty, extended cost, or weight
• Landed cost must use flat amount cost method

Landed Costs by invoice match

• Landed cost group specified by line item on receipt (default from item setup, or from the purchase order, or manually entered)
• Does not have to calculate an estimate amount on receipt, but it can.
• Individual landed cost must be set up for Invoice Match, and for Revalue for Cost Variance if applicable
• Use Enter/Match Invoices to enter an invoice from the landed cost vendor, and match it to the shipment from the product vendor

So, let's take a look at each of these options. In Landed Costs by line item, we can specify a Landed Cost Group on the Purchase Order line item.

Transactions>>Purchasing>>Purchase Order Entry, click on a line item, and then click the expansion arrow to the right of the Item header.

Remember, this value defaults from the item/site combination in Cards>>Inventory>>Quantities/Sites. However, it can be changed here to provide a different default to the receipt.

So, when we get to Transactions>>Purchasing>>Receivings Transaction Entry, the landed cost group will appear on the line item once again. You can view it in the same way as you did on the purchase order, by clicking on the line item and then clicking on the Item header expansion arrow. However, you may find it more helpful to view the actual landed costs being calculated for the line item, based on the landed cost group assigned. This can be done by clicking on the line item, and then clicking on the expansion arrow to the right of the Unit Cost header.

You can override the amounts specified here. The percentage or amount fields will be available based on the calculation method you defined for the landed cost.

Also, on the receipt, we can do Landed Costs by Apportionment. To do this, we simply click on the Landed Costs button at the bottom of the Receivings Transaction Entry window to open the Receivings Landed Cost Apportionment window.

In this window, you can select Landed Costs to apportion across the entire receipt. You must select a landed cost that is setup with a flat amount calculation method in order to select quantity, value, or weight in the "apportion by" field.

The system calculates each method as follows:

• Quantity: (Line item's quantity shipped - the quantity rejected)/(Sum of all line items' quantity shipped- the quantity rejected)
• Value: [(Line item's quantity shipped -the quantity rejected)*Originating Unit Cost]/Sum of all line items [(quantity shipped -the quantity rejected)*Originating Unit Cost]
• Weight: (Line item's extended shipping weight)/(Sum of all line item's extended shipping weight)

In either case, Landed Cost by Apportionment or Landed Cost by Item, the distributions that result are the same:

• Debit to Inventory
• Credit to Accrued Purchases for Landed Cost (per Landed Cost Maintenance)

So, that leaves us with the last method of Landed Cost by Invoice Match. In this example, let's assume that we posted the estimated landed cost of 10% of extended for the INSCARRIER landed cost on the shipment receipt. Now, we have received an invoice from the actual insurance carrier, Associated Insurance, and find that the costs were actually much greater. Since we set up the INSCARRIER landed cost for invoice match, we can now record the invoice from Associated Insurance and match it back to the shipment from Advanced Office Systems.

To do this, we go to Transactions>>Purchasing>>Enter/Match Invoice and enter an invoice for Associated Insurance (NOT Advanced Office Systems):

There are just a few key differences in how you enter the landed cost invoice:

• Vendor ID is the landed cost vendor
• Mark the "LC" checkbox for the line item to identify it as a landed cost
• Select the landed cost to match for the item (rather than an actual item)
• Match the landed cost to the original shipment from the product vendor using the Matched to Shipment expansion button

The distributions that result depend on whether you have selected to Revalue Inventory for Cost Variance:

If you are revaluing:

• Debit to Inventory (if cost is greater than receipt)
• Debit to Accrued Purchases for Landed Cost
• Credit to Accounts Payable

If you are not revaluing:

• Debit to Purchase Price Variance for Landed Cost (if cost is greater than receipt)
• Debit to Accrued Purchases for Landed Cost
• Credit to Accounts Payable

So, I hope this helps clarify the different ways to approach landed cost in Dynamics GP. I find that some clients will use all three methods, but many also settle on one or two ways that work best for their goals. Please share your experiences, questions, etc.

Forecasting Future Demand of Products

In 1987, Gordon Graham wrote a book, Distribution Inventory Management for the 1990s. In this book, Graham described what he considered to be the best method for forecasting the future demand for both seasonal and non-seasonal products. Let's take a quick look at these formulas:

Non-Seasonal Products: Calculate demand for the upcoming month by averaging the usage recorded in the past six months.

Seasonal Products: Calculate demand for the upcoming month by averaging the usage recorded in the upcoming six months, last year, and then applying a "seasonal trend factor" that expresses the anticipated increase or decrease in business experienced over the past year.

These are simple formulas. And at the time Gordon wrote the book, simple formulas were necessary for distributors to successfully manage their inventory:

• Many buyers could not effectively deal with mathematical formulas or computers. Ten-key calculators were considered "state-of-the-art" technology. In fact, most purchasing decisions at the time were based on "SWAG" (silly, wild-ass guessing). Any formula (including Graham's) introduced to provide consistency in ordering had to be fairly simple and easily replicated on a calculator.

• Computers did not have the power to perform comprehensive forecasting formulas for thousands of parts within a reasonable period of time. Calculating Graham's simple average for thousands of items stretched the physical capabilities of most computer systems.

The demand forecasts produced by the Graham formulas were generally more accurate than the predictions of the guy with the dull pencil and clipboard out in the warehouse. But there was still a considerable difference between Graham-based predictions and what was actually sold. At the time, these deviations were considered "unavoidable," and there was no way around them.

Now consider how market conditions have changed since 1987:

• Technology has allowed distributors to expand and increase their market areas. The result: You face more competition than ever. This competition has created more pressure on distributors to consistently have the products their customers want, when and where they want them.

• Increased competition has also put pressures on profit margins. Distributors have to offer lower prices in order to retain current business and attract new customers.

• The number of new products introduced to the market continues to increase at a rapid rate.

These conditions present some unique challenges:

• Decreased margins tend to limit the amount of money a distributor has available to invest in inventory.

• Distributors must spread the money available to invest in inventory over a greater number of products.

• Customers are less tolerant if product availability does not meet their expectations.

You're obviously in trouble if you don't have the inventory your customers expect you to have. And if you've bought too much of an item, your money is tied up and can't be invested in the other products that allow you to take advantage of new sales opportunities.

These challenges require the best possible product forecasting. You can no longer accept as "inevitable" great deviations between forecasts and actual sales. Formulas developed just to be "easy to understand" and "better than a guy with a clipboard" have to be replaced with more comprehensive methods.

Products with different patterns of usage, and different replenishment methods require different forecasting formulas. We need more than one formula for non-seasonal products, and one formula for seasonal products. For example, a product whose sales mirror local economic conditions requires a different formula than a product with steady, fairly predictable sales. And just as important, each formula needs to be easy to understand.

During the next several months, we'll look at some of the 29 different forecast demand formulas developed by EIM. We're going to start with a formula for non-seasonal products with fairly consistent usage. These are items that sell regularly and whose volume has increased or decreased less than 20% per month during the last several months.

When forecasting the usage of non-seasonal products with fairly consistent usage, we want to average the usage that was recorded during the past several inventory periods. But we also want to "weight," or place more emphasis on, the most recent month. Why?

1. There are often trends in a product's usage as it becomes more or less popular over time. For non-seasonal products, demand in the upcoming inventory period will more likely be similar to the usage recorded in the past several inventory periods than what happened six, eight, or twelve months ago.

2. At the same time, there is usually a certain amount of random variation in a product's usage from one inventory period to another. Notice how the usage of the item in the first example below has fluctuated over the past five months. This "up-and-down" pattern of usage is common for inventory items with moderate-to-high sales. If we were to use just the most recently completed one or two inventory periods in our calculations, the random fluctuations in usage would probably have too great an influence on the forecasted demand. We want to include enough history to ensure that random fluctuations do not have a significant impact on a product's forecast.

Here is a common set of weights to use in calculating demand for a non-seasonal item with moderate-to-high sales:

• Place a weight of 3.0 on the usage recorded in the most recent period.
• Place a weight of 2.5 on the usage recorded in the next previous period.
• Place a weight of 2.0 on the usage recorded in the next previous period.
• Place a weight of 1.5 on the usage recorded in the next previous period.
• Place a weight of 1.0 on the usage recorded in the next previous period.

Let's see how the forecast for an item is calculated with the following usage history. Usage is the quantity of a product sold, transferred, used in assemblies or repair orders, or otherwise taken from stock.

Month	Total Usage	Number of Business Days in Month	Usage per Business Day
June	148	20	7.4
May	133	19	7.0
April	126	18	7.0
March	110	22	5.0
February	104	20	5.2

Note that we've specified the number of business days in each month, and determined the usage per business day. Utilizing usage per business day provides more accurate forecasting than traditional forecasting methods that rely on total monthly usage or usage per calendar day. After all, if a company is closed for several days during a month (remember the Christmas holidays?), considering that month's lower usage equally with the usage recorded in other months tends to underestimate future forecasted demand. For example, in the chart displayed above, total usage recorded in May (133 pieces) is about 5.5% higher than total recorded in April (126 pieces), but the demand per business day is the same.

We will apply the weights of the demand calculating formula to the usage per business day for the five preceding months to determine the forecast demand for July:

Month	Weight	Usage per Business Day	Extension
June	3.0	7.4	22.2
May	2.5	7.0	17.5
April	2.0	7.0	14.0
March	1.5	5.0	7.5
February	1.0	5.2	5.2
Total	10.0		66.4

The extension (66.4) is divided by the total weight (10.0) to determine our prediction of the demand per business day for July (6.64 pieces per day). And this demand per day is multiplied by the number of business days in July (21) to predict the demand of 139.4 pieces for the inventory period.

Compare the results of this calculation to the demand predictions provided by other forecast formulas and methods. We think you'll be impressed with the results. Next month we'll look at non-seasonal products with significant increasing or decreasing usage. In the meantime, if you have any specific questions, please let us know.

Some items, like beach umbrellas, are more popular in summer than in winter. On the other hand, portable heaters enjoy much higher sales when the weather gets cold. These are seasonal items. But the weather is not the only factor that determines whether or not an item is seasonal. If a product's usage is controlled by an event (such as Christmas or the start of school) or an annual activity (like yard clean up in the fall), the item is also considered to be seasonal. The usage of a seasonal product rises and falls throughout the year. Look at this seasonal item's usage history:

	Jun 1999	May 1999	Apr 1999	Mar 1999	Feb 1999	Jan 1999
Usage 1999	?	300	150	80	50	30

	Dec 1998	Nov 1998	Oct 1998	Sep 1998	Aug 1998	Jul 1998	Jun 1998
Usage 1998	50	100	150	300	520	460	400

Usage of the product is very low during the winter months. But in early spring, sales begin a gradual increase and peak during the summer months of June, July and August. If we forecast demand for June 1999 by using the formula for non-seasonal products with consistent usage (described in Part One), we get the following result:

Month	Total Usage	Number of Business Days in Month	Usage per Business Day
May	300	19	15.8
April	150	18	8.3
March	80	22	3.6
February	50	20	2.5
January	30	22	1.4

Month	Weight	Usage per Business Day	Extension
May	3.0	15.8	47.4
April	2.5	8.3	20.8
March	2.0	3.6	7.2
February	1.5	2.5	3.8
January	1.0	1.4	1.4
Total	10.0		80.6

The extension (80.6) is divided by the total weight (10.0) to determine our prediction of the demand per business day for June of 8.06 pieces. Because June has 20 business days, demand for the inventory period is 161.2 pieces (20 days x 8.06 pieces per day).

Remember that demand is defined as a prediction of the usage of a product during the upcoming inventory period. Is 161 pieces a good forecast of June's usage? Probably not. After all, usage in June 1998 was nearly three times this amount (460 pieces). It is obvious that we need different formulas for calculating the demand for seasonal items.

We've found that one of the best indicators of what demand will be for a seasonal item next month is the usage recorded during the upcoming several months, last year. For example, one formula for forecasting demand for seasonal items considers the usage for the upcoming month and the following month last year, applying the following weights:

• Place weight of 2.0 on the usage recorded in the month being forecast, last year.

• Place weight of 1.0 on the usage recorded in the month following the month being forecast, last year.

Month	Total Usage	Number of Business Days in Month	Usage per Business Day
June 1998	400	19	21.1
July 1998	460	18	25.6

Month	Weight	Usage per Business Day	Extension
June 1998	2.0	21.1	42.2
July 1998	1.0	25.6	25.6
Total	3.0		67.8

The extension (67.8) is divided by the total weight (3.0) to determine our prediction of the demand per business day for June of 22.6 pieces. Because June, 1999 has 20 business days, demand for the inventory period is 453 pieces (20 days x 22.6 pieces per day).

But there is a problem with forecasting demand with history that is a year old. Business in the branch where the item is located, or in its particular line of products, may have increased or decreased during the past 12 months. For this reason, a "trend factor" can be applied to the results of the weighted average formula to reflect overall changes in your volume of business.

Many systems will allow you to manually maintain trend factors. Say, for example, you determine that the sales volume in our item's product line increased 20% over the past year. To determine the actual demand forecast for the product, we'd increase the result of the seasonal weighted average formula by 20% to determine the actual demand forecast for June, 1999:

22.6 pieces/day + 20% = 27.1 pieces/day

More advanced systems calculate a suggested trend factor by comparing the total usage in the last three completed months (before the forecast demand calculation) to the total usage in the same three months in the previous year:

Total Usage March,1999 - May, 1999 = 530 pieces

Total Usage March 1998 - May, 1998 = 462 pieces

(530 - 462) ÷ 462 = 14.7%

Business in the past three months was 14.7% greater than the same period last year. This percentage is added to the results of the weighted average formula:

22.6 pieces/day + 14.7% = 25.9 pieces/day

Whether specified manually or calculated automatically by the system, trend factors must be applied whenever seasonal forecast formulas are utilized to compensate for the change in business experienced over the past 12 months.

http://www.effectiveinventory.com/article26.html

How to determine, maintain, and report accurate costing in Inventory in Microsoft Dynamics GP 9.0 and 10.0

http://www.pinncorp.com/LinkClick.aspx?fileticket=Q1hwsoTQRlA%3D&tabid=452&mid=3482