with these two file below attached, contain all information needed, required to build an excel and arrange all data in order( detail prescription in file)
Assignment 7
House & Home (H&H) is a regional retailer of furniture, housewares and decorative items. The company operates approximately 60 stores in the southeastern U.S. H&H was founded by Auburn graduate (class of ’81) Dusty Smith in 1985 and has enjoyed consistent growth as measured by new store openings of 1-2 per year throughout its history. During a recent review of company finances, Dusty determined the company was holding too much inventory.
Dusty has hired you as a consultant to help distinguish the top sellers from the “dogs”. To that end Dusty has provided you a random sample of 300 SKUs and their sales over the past 12 months. Additionally, each SKU is categorized as either a full time stocking item that’s always in the store (ST), a seasonal item that is available for sale for a 2 month period (SN), or a holiday item that’s available for about 4 weeks in the November-December time frame (HO). Dusty wants to be able to closely monitor his “strategic sellers” which constitute the 20% of items with the greatest sales (the A items). He wants to provide only minimal inventory management of the lowest selling 10% of item (the C items). He believes the remaining B items require a bit more attention. All items listed in the sample are ordered in cases of 10 units.
Assignment
1. Develop an ABC analysis for the full time stocking items in the H&H 51W sample.
2. Determine the appropriate (a) average inventory stocking level, (b) re-order point, and (c) number of cases to order for each item in the analysis based on:
a. A items should carry an average inventory of 8% of sales rounded up to the nearest 100.
b. B items should set to an average inventory level of 6% of sales rounded up to the nearest full case quantity (e.g., an item with a calculated average inventory of 25 should set the re-order point at 30, or 3 full cases).
c. C items should carry an average inventory of 1 full case.
d. Re-order point should be 50% of the calculated average inventory level.
3. Create a report (including report titles, column headers, and summary totals) using Excel following the format shown on the next page. Only full time stocking items should be listed on the report. The report should be sorted by annual sales from largest to smallest. Report should not exceed 5 printed pages.
