Description
PRIMARY QUESTION
This week we look at inventory analysis. The ABC analysis is used to help determine critical inventory and replenishment rates. This week we are going to use Excel to build an ABC analysis and create a cycle counting program to determine replenishment rates. The steps for the assignment are below.
- Perform an ABC analysis in Excel.
- You have been given a simplified menu at Stefano’s sandwich shop
- As part of any project your managers will give you, basic research is needed. You will need to develop the list of items to make up the sandwiches. Do not forget basic material such as bags and boxes since the shop will need those items as well. This assignment is also an exercise on researching materials for manufacturing.
Answer the following questions in addition to the ABC Analysis
- Define at least 3 ways in which setting a WIP Inventory limit and the associated concepts could be used to improve the manufacturing process
- Define at least one way the vendors can support this effort and the advantage for Stefano’s and the vendor: 1-2 pages both parts
- Define at least 3 ways in which Stefano’s can benefit from ABC analysis: 1-2 pages
- Define at least 3 ways in which Stefano’s and their ingredient vendors can benefit from cycle counting: 1-2 pages
Assignment format: Use an Excel sheet to build the ABC analysis and create the cycle counting program. Paste the spreadsheets into the body of the paper.
Week 6 Hints and Suggestions
*See some links at the end to help with the question on WIP Limits and benefits for manufacturers.
Below are the ONLY MENU ITEMS YOU NEED TO WORK WITH. BUILD YOUR INVENTORY BASED ON THE INGREDIENTS OF THE BELOW. That is, estimate the amount of bologna, pepperoni, oil, olives, ham, etc… that is needed to meet inventory. Estimate the costs by looking in the supermarket flyers for prices as an example.
Hints:
- Don’t forget the bread.
- Assume they make an equal number of each sandwich types each day. Assume 24 of each sandwich per day
MENU
Hot Virginian $7
Boar’s head sweet slice ham, grilled with melted cheddar, onions & Honey mustard on a braided roll
Trump Tower $7.95
1/2lb of Pastrami and corned beef, melted swiss, coleslaw and thousand island dressing on a bulkie roll
The Queen Bee $6.50
Boar’s Head chicken breast, grilled with melted cheddar, bacon and thousand island dressing on a bulkie roll
The South Common $6.50
Boar’s Head sliced buffalo chicken, pepperjack cheese, cajun mayo, lettuce and tomato in a wrap
Turkey Club $6.75
Turkey, bacon, lettuce, tomato and mayo on wheat toast
Grilled Reuben $6.75
Corned beef, thousand island dressing, swiss cheese and sauerkraut on dark rye
Liverwurst $6.50
Liverwurst with swiss cheese, spicy mustard, red onions on dark rye
Godzilla $6.75
Grilled pastrami, American cheese and thousand island on a bulkie roll
See these sites to learn more about how understanding WIP concepts can improve manufacturing
https://www.isixsigma.com/dictionary/work-in-process-wip/
https://www.planview.com/resources/articles/benefits-wip-limits/