Problem 5 [16 points) Solve this problem in Excel: The plant manager, Murat Kristal, at York Technologies makes Aircraft Navigation Systems. He expects you, as the new OM analyst, to provide some insight for performance of the plant. High on his list is an understanding of his inventory turnover based on the financial data provided in the following table. He expects you to provide him answers on the following questions. (Note: You should be able to copy-paste the data into Excel) Sales Cost of Goods Sold LAST YEAR THIS YEAR (in (in $) $) 315,000 328,000 181.000 186,000 134,000 142.000 54,000 64,000 80,000 78,000 Gross Margin Other Expenses Net Income 3,000 6,000 Finished Goods Inventory Work-In-Process Inventory Raw Material Inventory 12.000 15,000 6,000 7.000 3,000 6,000 12.000 15.000 Finished Goods Inventory Work-In-Process Inventory Raw Material Inventory Total (Inventory (Average for Year) 6,000 7.000 21.000 28,000 150,000 Other Current Assets 141.000 Other Assets 280,000 Total Assets 421,000 292.000 442,000 a) Based on total inventory, what is the inventory turnover for last year? (2 points) b) Based on total inventory, what is the inventory turnover for this year? 12 points) c) Has inventory turnover improved this year? (2 points) d) The company's percentage of assets committed to inventory last year. [2 points) e) The company's percentage of assets committed to inventory this year. (2 points] f) The change in the percentage sets committed to inventory. [2 points) g) How many weeks of finished goods were on hand at the end of last year. [2 points) 150.000 Other Current Assets 141.000 Other Assets 280.000 292.000 Total Assets 421,000 442.000 a) Based on total inventory, what is the inventory turnover for last year? (2 points) b) Based on total inventory, what is the inventory turnover for this year? (2 points) c) Has inventory tumover improved this year? 12 points) d) The company's percentage of assets committed to inventory last year. [2 points) e) The company's percentage of assets committed to inventory this year. (2 points) The change in the percentage of assets committed to inventory. 12 points) g) How many weeks of finished goods were on hand at the end of last year. (2 points) h) How many weeks of finished goods were on hand at the end of this year. [2 points) v