Reference no: EM133672214

Wheels and Skis Inc. is a seasonal business that sells bikes and skis. Offering products for both summer and winter outdoor activities allows sales to be more evenly spread over the year. The key period for selling skis is from October to December and for selling bikes from April to August. The company is looking to gather some historical data regarding inventory turnover and has collected monthly sales and inventory data for both the bikes and skis categories. The company is hoping to use this data to manage inventories a bit better. You are the newly hired data analyst for the company and have been asked to do the following:

1. Create a pivot table (using Excel) showing the following:(1) Year, (2) average value of inventory for each year, and (3) cost of goods sold for the year.

2. Calculate average turnover for each year and add to the pivot table.

3. Create a bar chart showing turnover for the past 5 years.

4. What information does this visualization tell you about turnover over the past 5 years?

5. Create a line chart showing cost of goods sold versus inventory for the past year.

6. What information does this visualization tells you about monthly sales and inventory levels?