answer q1-q6, q4-6 need to use excel

Q1. Identify the problems associated with the current manufacturing system, particularly comment upon shop floor operations and manufacturing planning and control systems. [10 marks]
Q2. Suggest alternative manufacturing planning and control strategies (MRP/JIT/Hybrid) to the company and provide reasons for your recommendations. There should be linkages made between the manufacturing planning and control system and shop floor operations [10 marks].
Q3. Provide a set of recommendations for improvement focusing on the issues raised in the case. These recommendations should show enough detail to demonstrate how your solutions would work in practice [10 marks].

Additional 10 marks will be awarded for style and presentation of report.
From Q4 to Q6 are calculation based question for which the answer and the procedure to calculate them is to be written down in the report and an additional Excel sheet is to be submitted.
Note the words count of Q4 to 6 will not matter for the first three questions.
Please upload word/pdf file on blackboard. In answering each question, write down all the equations used for calculation and where appropriate please include carefully labelled figures and tables when presenting results. Ensure each equation, figure and table is numbered, has a meaningful heading and that all axes are clearly labelled.

Q4. Quantify the value of the current inventory and the cost of holding the stock per year for the consumables, assuming an annual holding rate of 27% and that the 10 weeks of stock policy is correctly implemented. [20 marks – marks for writing the correct formula for each = 5, marks & for doing correct calculation for each = 5 ]

Q5. Segment the consumable inventory based on the value-usage criteria. Present and discuss the results including a graphical representation of Pareto principle. Explain what other segmentation criteria might be used. [20 marks – value usage criteria = 5 , Right Calculations = 5, Graphical Representation = 5, Other Segmentation =5 ]

Q6. For a consumable inventory, calculate the total annual cost and re-order point (ROP) for each order. Define and show formulas for all the parameters you are using for calculation with the assumption that demand and lead time is constant. Use two models to demonstrate this; average inventory and EOQ. [20 marks – marks for writing the correct formula for each = 5 & marks for doing correct calculation for each = 5 ]