Inventory Analysis

This is a personal test to be performed in an individual manner.                        
It is important to leave as much evidence as possible of your calculation logic, formulas, and tools used.                        
The following simple case study is an example of a typical inventory analysis in the industry.                        
A small sample of 20 material items is given to evaluate the candidate's excel and analysis skills.                        
In the sheet "Inventory" there is a list of the on-hand-inventory available in the warehouse.                        
The sheet "Consumption" contains a list with the last 3 months of consumption usage of these materials.                        
                        
TASKS:                        
                        
Identify the quantity consumed for Item05 in Production Line02                        
Answer:                        
                        
Identify the production line that consumes the most value of materials                        
Answer:                        
                        
Identify the items that account for 20% of the value of inventory                        
Answer:                        
                        
Generate a quantity consumed Pivot table (tabla dinamica) with the following headers:                        
                        
Item    March    April    May    Total    Frequency    
                        
                        
Fill the following table:                        
                        
Item    Material Group    Qty on hand    Value $ on hand    Three months Average consumption    Standard Deviation of consumption    
Item01    Group D                    
Item02    Group A                    
Item03    Group C                    
Item04    Group C                    
Item05    Group B                    
Item06    Group C                    
Item07    Group B                    
Item08    Group A                    
Item09    Group A                    
Item10    Group D                    
Item11    Group A                    
Item12    Group A                    
Item13    Group C                    
Item14    Group D                    
Item15    Group D                    
Item16    Group D                    
Item17    Group D                    
Item18    Group B                    
Item19    Group A                    
Item20    Group B                    
Total                        
                        
                        
Based on the information from the tables, identify three critical items that should be procured immediately                        
Suggest quantities to be purchased and explain your logic                        
                        
                        
Identify the items that are surplus                        
                        
                        
Identify which two items have the most stable usage trend                        
                        
                        
Identify which two items have the most erratic or intermittent usage trend                        
                        
                        
Tell us your conclusions about the analyisis and general inventory status of this case study