Kieso_Intermediate_DAP_8.1_student

Month Average Value of Inventory Cost of Goods Sold Jan-14 1000 500 Feb-14 1500 420 Mar-14 4500 900 Apr-14 6000 1600 May-14 7500 3500 Jun-14 9000 4900 Jul-14 7000 5800 Aug-14 8000 1600 Sep-14 3000 500 Oct-14 2000 3000 Nov-14 9000 5000 Dec-14 8900 7700 Jan-15 3000 6000 Feb-15 2000 2000 Mar-15 1500 900 Apr-15 6500 6000 May-15 7500 7500 Jun-15 8000 3500 Jul-15 10000 6500 Aug-15 8000 7500 Sep-15 5000 2000 Oct-15 4500 1200 Nov-15 8000 100 Dec-15 7000 10000 Jan-16 3000 9500 Feb-16 2000 1500 Mar-16 2500 2500 Apr-16 6000 6500 May-16 8500 6000 Jun-16 9000 1500 Jul-16 11500 100 Aug-16 5000 2500 Sep-16 10000 1200 Oct-16 10000 3000 Nov-16 11000 4500 Dec-16 6000 7500 Jan-17 4500 8000 Feb-17 2500 10000 Mar-17 3000 7500 Apr-17 7000 4500 May-17 9000 5000 Jun-17 9500 2000 Jul-17 12000 5000 Aug-17 11500 6000 Sep-17 5000 2000 Oct-17 5000 900

DAP8.1 An overview of this problem is provided at the end of Chapter 8. Additional instructions and materia 1. An excel spreadsheet has been provided representing Wheels and Skis’ average value of inv A. Material provided by Wheels and Skis Inc. to assist in your analysis B. Other information 1. Inventory turnover is a ratio that indicates the number of times inventory “turns” (is · Average inventory is used to account for seasonality in sales · Inventory turnover can also be calculated by dividing sales by averag · Inventory turnover is used to calculate the “days on hand” for invento

als are set out below ventory and cost of goods sold by month for the past five years. (See the raw data tab.) s stocked and sold) in a year. It can be calculated by dividing cost of goods sold by average inventory. Since W ge inventory, but this approach is less conservative because it inflates the inventory turnover ratio. ory, which is an important measure of business performance. To calculate days on hand, we divide the numbe

annual basis, and the cost of goods sold for the year, to perform the calculation. Other basic principles of inve ne year was 10, the days of hand would be 36.5.

entory turnover to note:

elow (start in cell B33). only Years for the row. gs. This will show the average MONTHLY inventory value for each year. onth. r (should be column E). r. ost of goods sold / (Average value of inventory/12).

Part C - Pivot Column Chart -- create a pivot column chart showing the average inventory turno Select and copy the pivot chart created in Part B and paste in student work area below. You need to modify the Pivot Table required in order to create the necessary pivot chart using only the average Click on the checkmarks for both Average Value of Inventory and Cost of Goods Sold to remove them from the This Pivot Table should now show the following: Re-size column width for column C to be 18, if required. After completing the Pivot Table in the student work area, use it to create a Pivot Chart, Clustered Column Char Format Cha Provide an Hide all fie Remove leg Change sty Part D - W Student Work Area (position Pivot Table here): (1) Years (2) Average inventory turnover

Part E - Pivot Line Chart -- create a line chart showing the cost of goods sold vs. inventory f Select and copy the pivot chart created in Part B and paste in student work area below. You need to modify the Pivot Table required in order to create the necessary pivot chart using average val Click on the checkmark for Average Inventory Turnover to remove this field from the values in the Pivot Ta Add Months to the rows below Years. This Pivot Table should now show the following: Using the Filter button for Year, ensure the Select field is set to Years and filer to show only 2018. Expand the year 2018 to show all months in the row. Change the value for Average Value of Inventory from AVERAGE to SUM using Pivot Table Field Settings. T Re-size value column widths to 18. After completing the Pivot Table in the student work area, use it to create a Pivot Chart, Line Chart and po Student Work Area (position Pivot Table here): (1) Years (2) Months (3) Average inventory turnover (4) Cost of Goods Sold

for the past year. alue of inventory and cost of goods sold. able. This will not change the average inventory value for each month but will change the total amount for the yea osition this chart in the space below. Format Chart Area: Provide an appropriate chart title - such as "Average Inventory vs. COGS by Month for 2018" Hide all field buttons. Move the legend to the bottom. Change style to Style 4. Part F - What information does this visualization tell you about monthly sales and invento

Part G - How can this exercise help Wheels and Skis to better manage inventories?