Excel Analysis Guidelines Inventory

Excel Analysis Guidelines Inventory

Instructions/Descriptions

[Order Now]

Excel Analysis Guidelines Inventory

Part 2 Excel Analysis Guidelines – Inventory

• Always save your work each time with a new version number in case you make an error
• Always back it up
• Make sure you use XL to do your calculations, don’t just put the answer in the cell!
• For all calculations don’t forget to use cell referencing ($A$1, $A1, A$1) so that any formulae may be

dragged down columns and across rows.

• Only display the number of decimal points which are appropriate for your calculations.
• If you create separate worksheets for your analysis, name them appropriately.

The Unit Stock data which you have been given has just over 400 SKUs ( Stock Keeping Unit) Therefore is a lot of data, but little information, which is what the analysis is about… Don’t forget to use the relevant HOW2 sheets if you are not sure how to complete the excel manipulation tasks.

1. Calculate the total unit cost of each item based on the material and labour components
2. Calculate the total stock value for each item (SKU) using the unit value and the qty in stock.
3. Do a cumulative analysis by value for the stock [Don’t forget when you sort by value to include the all the columns including the forecast data, otherwise the data will be mixed up]
4. What would your ABC categorisation be and why?
5. What does this tell you about the inventory position of sewn cases in Fibreline?
6. Using the Usage column calculate the Stock Turn values and Stock Cover for one of your ABC categories: you can choose and justify how you sample/select this. This should be between 20-30 items. Highlight any anomalies or difficulties which this calculation reveals.
7. Contrast this analysis with typical *outsourcing lead times of 1 weeks [local] or 2-3 weeks [Lithuania], which items would be suitable for outsourcing and where?
8. Review the Individual demand profiles for items you are considering outsourcing, does this suggest it is a good idea, what would be a suitable level of safety stock?
9. Based on all your analysis so far produce a revised stock target and make or **buy option for your selected items on a separate work sheet. Consider how to balance protecting supply whilst reducing cash tied up in stock, and if any stock should be ‘written off’.
10. How has this affected your overall stock value?
11. Construct relevant graphs and tables, and text box(es) to showcase all of your analysis

*Outsourcing costs are broadly equivalent to making in house: any item can be outsourced **Assume min order quantities >10 locally and > 50 for Lithuania

Excel Analysis Guidelines Inventory

PLACE THE ORDER WITH US TODAY AND GET A PERFECT SCORE!!!