Things are working out very well in your positon as a logistics analyst/manager at LUML. For at least the next few months (until the end of the term!) you have no plans to change jobs or to even actively look for a new positon.
There are three parts. Be sure to answer all three!
Part 1
It turns out that LUML has 10 items in the inventory. The following summarizes the basic information on these items.
Stock # Annual volume $ Unit cost
#01036 100 8.50
#01307 1,200 .42
#10286 1,000 90.00
#10500 1,000 12.50
#10752 250 .60
#10867 350 42.86
#11526 500 154.00
#12572 600 14.17
#12760 1,550 17.00
#14075 2,000 .60
You are asked to develop an ABC analysis of this inventory. (Hint: keep in mind that we have covered two �ABC�s�. This one deals with stratifying an inventory. The other ABC dealt with cost, right?)
You ask the manager if there are any company rules or policies for determining the categories (A, B, or C). The answer is �Huh?�. You correctly deduce that you are pretty much on your own.
For each question, show your work and explain the computation.
Each question/answer is worth 10 points with 2 points for the work.
Q1. Using the annual cost as the basic analysis, prepare an ABC analysis.
Q2. How many items are in each category (A, B, and C)?
Q3. What proportion of the annual cost is in each category (A, B, and C)?
Q4. What proportion of the annual volume is in each category (A, B, and C)?
Q5. Briefly explain your rationale for deciding on the cutoff point for each category.
Q6. The price for #10286 increases to $120.00.
Q6a. What happens to the total inventory value?
Q6b. How does this change your analysis, if any?
Q7. What are two other ways that we might have used to stratify the inventory into ABC?
Part 2.
Today is September 30, 2014. It is 10 AM. Tom was working on a project to brief the CEO this afternoon at 3 PM when he suddenly became sick. Your boss recognizes the great job that you did on the ABC analysis, and volunteers your services to help prepare the briefing.
Alas, your great PowerPoint skills are not what are in demand. What is needed is your analytical skill.
Luckily, Tom has been keeping some fairly good records that you find in a binder on his desk.
2014 Actual Sales in units
Jan 6,500
Feb 5,950
March 6,340
April 7,010
May 6,650
June 6,520
July 6,000
Aug 7,030
Sep 7,050
Oct
Nov
Dec
Each question is worth 10 points indicated.
Q1. (10 points) Using a simple moving average of the last three months, what is the forecast for October sales in units?
Q2. (5 points) The sales price per unit is $125. What is the revenue forecast for October sales in $ terms?
During the briefing you earn accolades for your quality work on short notice (an �attaboy� or �attagirl�). However, the CEO says that she doesn�t feel that all three months should count equally. She thinks that the most recent month should be 60 percent; the previous month would be about 30 percent; and the other month the balance.
�Get back to me with a brief memo with the revised sales forecast in units and dollar terms. Ok?� Of course, the �ok� is rhetorical.
Answer Q3 and Q4 in a brief memo to the CEO.
Q3. (10 points) What is the revised sales forecast in unit terms? Briefly explain your work
Q4. (5 points) What is the revised forecast in dollar terms? Briefly explain your work.
Fast-forward to the end of October 2014. Because the company had planned on using these metrics it is very easy and fast to get the needed reports in an hour or less. You get an email saying that the actual October sales were 6,950.
In a short memo to the CEO answer the following:
Q5. (5 points) What was the difference between the October sales forecast and the October actual sales?
Q6. (5 points) Why might there have been a difference between the projection and the actual? (Hint: stick to the facts. Don�t assume too many unusual scenarios. For example, it is unlikely that the Pod-People-landed-on-Earth-and-influenced-the-demand-for-our-lamps.)
Q7. (10 points) What is the November sales forecast in units using a four month simple moving average?
Q8. (5 points) What is the November sales forecast in dollar terms using a simple moving average?
Q9. (10 points) What is the November sales forecast in unit terms using a weighted average of the most recent month = 35%; then 30%; then 20%; and fourth month is the balance?
Q10. (5 points) What is the November sales forecast in dollar terms using the weighted average?
Part 3
(20 points each topic � ABC and sales)
In a memo to the CEO (two pages max), as a logistician outline the pro�s and con�s of the usefulness of forecasting sales; and managing inventory using an ABC classification methodology.
Include how these might also help the overall operations of the entire organization.
And be sure to include an overall assessment of each (i.e., ABC and forecast)
Logistics Analysis
Name
Institution
Instructor
Date
Part 1
Q1. Using the annual cost as the basic analysis, prepare an ABC analysis.
Stock # | Annual volume | $ Unit cost | Total Cost Per Year | % Usage of total Usage | Cumulative % of total |
#10286 | 1,000.00 | 90.00 | 90,000.00 | 38.78% | 38.78% |
#11526 | 500.00 | 154.00 | 77,000.00 | 33.18% | 71.97% |
#12760 | 1,550.00 | 17.00 | 26,350.00 | 11.35% | 83.32% |
#10867 | 350.00 | 42.86 | 15,001.00 | 6.46% | 89.78% |
#10500 | 1,000.00 | 12.50 | 12,500.00 | 5.39% | 95.17% |
#12572 | 600.00 | 14.17 | 8,502.00 | 3.66% | 98.83% |
#14075 | 2,000.00 | 0.60 | 1,200.00 | 0.52% | 99.35% |
#01036 | 100.00 | 8.50 | 850.00 | 0.37% | 99.72% |
#01307 | 1,200.00 | 0.42 | 504.00 | 0.22% | 99.94% |
#10752 | 250.00 | 0.60 | 150.00 | 0.06% | 100.00% |
Total Usage | 232,057.00 | 100.00% |
Category | Stock # | % of items | % usage | Action |
A | #10286 and #11526 | 20% | 71.97% | Close control |
B | #12760, #10867 and #10500 | 30% | 23.20% | Regular review |
C | #12572, #14075, #01036, #01307 and #10752 | 50% | 4.83% | Infrequent review |
Q2. How many items are in each category (A, B, and C)?
There are 2 items in category A, 3 items in category B and 5 items in category C.
Q3. What proportion of the annual cost is in each category (A, B, and C)?
Category A contains 71.97%, Category B contains 23.2% and Category C contains 4.83% of the total annual cost.
Q4. What proportion of the annual volume is in each category (A, B, and C)?
Category A contains 20%, Category B contains 30% and Category C contains 50% of the total annual volume.
Q5. Briefly explain your rationale for deciding on the cut-off point for each category.
The rationale for deciding on the cut-off for each category was informed by the ABC Pareto’s analysis rule. It proposes that category A items are those with 70-80% of the annual volume which represents 10-20% of the total stock. Category B items comprise of 15-25% of the annual volume which accounts for 20 -30% of the total stock and category C items comprise of less than 5% of the annual consumption which accounts for 40 – 50% of the total stock.
Q6. The price for #10286 increases to $120.00.
Q6a. What happens to the total inventory value?
The total inventory value increases to $262,057.
Q6b. How does this change your analysis, if any?
This does not affect my ABC analysis in any way.
Q7. What are two other ways that we might have used to stratify the inventory into ABC?
- The GMROI (Gross Margin Return On Investment in %) Method
- The Hits Method
Part 2
Q1.
October sales = (July sales + August sales + September sales)/3
= (6000 + 7030 + 7050)/3 = 6,693 Units
Q2.
Revenue forecast for October = No. of forecast unit sales × cost per unit
= 6693 ×125 = $ 836,625
Q2 and Q4
TO: Christian Cooper, CEO
FROM: Michael Woods, Logistics Analyst/manager
DATE: September 30, 2014
SUBJECT: Revised sales forecast in units and dollar terms.
The simple moving average gives equal weight to all sales in units of last three months. The weighted moving average gives more weight to the sales from September and August. September sales are assigned a 60% weight, August 30% and July 10% weight.
Revised sales forecast in unit terms.
By the weighted moving average forecasting,
October sales = 0.6(September sales) + 0.3(August sales) + 0.1(July sales)/3
= 0.6(7050) + 0.3(7030) + 0.1(6000)/3 = 4230+2109+600 = 6,939 Units
Revised forecast in dollar terms.
Revenue forecast for October = No. of forecast unit sales × cost per unit
= 6939 ×125 = $ 837,375
Q5, Q6, Q7, Q8, Q9, Q10
TO: Christian Cooper, CEO
FROM: Michael Woods, Logistics Analyst/manager
DATE: October 31, 2014
SUBJECT: Revised sales forecast in units and dollar terms.
With the actual October sales being 6,950 units and the revised sales forecast being 6,939 units, there exists is a positive forecast error difference of 11 units. There were more units sold than the actual sales forecast probably from an increase in demand due to promotions or sale incentives to the customers.
November sales forecast in units and dollar terms using a four month simple moving average
November sales = (July sales + August sales + September sales + October sales)/3
= (6000 + 7030 + 7050 + 6950)/3 = 6,757 Units
Revenue forecast for November = No. of forecast unit sales × cost per unit
= 6757 ×125 = $ 844,625
November sales forecast in units and dollar terms using a four month weighted moving average where October is 35%, September is 30%, August is 20% and July is 15%
By the weighted moving average forecasting,
November sales = 0.35(October sales) + 0.3(September sales) + 0.3(August sales) + 0.15(July sales)/3
= 0.35(6950) + 0.3 (7050) + 0.2(7030) + 0.15(6000)/3 = 2432 + 2115 + 1406 + 900 = 6,853 Units
Revenue forecast for November = No. of forecast unit sales × cost per unit
= 6853 ×125 = $ 856,625
Part 3
TO: Christian Cooper, CEO
FROM: Michael Woods, Logistics Analyst/manager
DATE: October 31, 2014
SUBJECT: The objectives of forecasting sales; and managing inventory using an ABC classification methodology.
ABC classification methodology
The primary aim of inventory classification is to ensure the company stocks the most important items in the right quantities. It ascertains that the purchasing staff is able to utilize resources efficiently by attending to the most important items first rather than treating all items equally. The ABC analysis divides items into 3 categories A, B and C. A being considered as the most valuable items and C the least valuable items. By this classification, supply managers are able to identify the most valuable stock items and distinguish them from the many other unimportant items.
Each category is then treated in accordance with its class.
- Category A items should be controlled tightly secured safely and afforded frequent sales forecasting. The reordering process should be quick, frequent and predetermined to avoid any eventuality of a stock-out.
- Category B items are less important and should receive a moderate management since they are within the intermediate status. However, a keen monitoring of potential crossing over to category A should be done frequently.
- Category C items should be reordered less frequently since these items are in low demand and therefore pose a risk of high costs of storage and dead stock.
The outcome of the ABC analysis impacts greatly other functions of inventory management such as review of stocking levels, cycle counting, vendor stocking, turnover ratios and inventory goals.
Forecasting Sales
Sales forecasting is essentially the anticipation of future sales performance based on past sales trends. The main objective of operations management is to match supply to demand. Forecasting on expected sales therefore aids in the planning and instituting the supply to meet the demand. Forecasting relies on two most important aspects; the expected level of demand and the level of accuracy of the forecast.
Sale forecasts inform the actions of budget making, capacity building, productions, purchasing among others. Forecasts affect decision making in almost all sectors of an organisation such as;
- Accounting – product cost estimating, cash flow management and profits projections.
- Finance – equipment maintenance and replacement and funding/borrowing need analysis.
- Human Resources – recruitment and hiring timing and layoff planning.
- Marketing – correct pricing, timely promotions and strategy formulation.
- Operations – scheduled maintenance, project management, business outsourcing decisions and inventory planning.
Therefore forecasting not only informs future demand but also is used to predict revenues, costs, price variations, raw material availability, fluctuations of key economic indicators and the price of stocks and equities.