Data Analysis

Analyze the data based on the questions below 1. Inventory Examine the inventory data; graphically display the cycle count trends that were recorded for the year. Which inventory group (A, B, or C) has the worst cycle count accuracy? Does this fit the context provided? 2. Production Recipes a) Usually processing time for a product is related to its complexity (number of parts). Graphically display the relationship between processing time and product complexity (number of parts) for all products. Indicate the products that have longer than expected processing times on the chart. b) Some of the machine operators claim that the products with long processing times spend too much time waiting for missing parts. Is there any connection between inventory and longer than expected processing times? If so, what is this relationship? 3. Forecast vs Actual a) Consider the Sales forecast data. Are there any trends or relationships among the products in the sales forecast? If yes, graphically display these trends. b) On a new sheet that you label “Process Time Required,” create a composite dataset that shows the process time required for each product in order to meet the weekly sales forecast. This should show all products, quantity for each product each week (sales forecast), and processing time for each machine each week. This worksheet will be a tool for answering some of the following questions. It should be clear, but it will necessarily be a little crowded with data. c) The plant runs three shifts (7am – 3pm; 3pm – 11pm; 11pm – 7am), 5 days a week. The weekends are dedicated to machine maintenance. The plant is also closed on the weekdays indicated on the inventory tab (inventory counts were made only on the days that the plant was open). Considering this and the total processing time required per week, can the product demand (sales forecast) be met each week if there is only one machine of each type? Which weeks are problematic? Which machines are over capacity? Show your results graphically. d) Consider the Parts Shipped. Are there any trends in this data? Does the sales forecast match actual shipped? Graphically display the trends you see. e) The manufacturing plant is in a location that had an unusually warm summer which resulted in several machine breakdowns during the summer. Based on Parts Shipped, was the company able to recover from these maintenance problems to actually achieve the forecast amount? f) How many extra machines of each type does the company need in order to achieve the Shipped Parts quantities?