Inventory turnover analysis

1. Overview

1.1 Concept

Inventory turnover is the ratio of the cost of goods sold to the average inventory balance of an enterprise in a certain period. It is used to reflect the speed of inventory turnover. The higher the turnover, the faster the inventory turnover speed. The shorter the cycle from cost to commodity sales to capital return, the better the sales situation.

Inventory turnover days is the number of days that an enterprise experiences from obtaining inventory to consumption and sales. The fewer turnover days, the faster the inventory is realized and the better the sales situation is.

1.2 Expected effect

1.3 Implementation ideas

Realized by calculation formula:

2. Example

Sample data: 

373.Inventory detail fact.xlsx

Upload the sample data to FineBI.

2.1 Create a dashboard

Enter "Dashboard", click "New Dashboard" and set the creation name and location, click "OK", as shown in the figure below:

Enter the dashboard editing interface, select "+" to create a component, select the uploaded "Inventory detail fact", and click "OK", as shown in the figure below:

2.2 Calculate the average inventory balance

Click "+" to add a calculation indicator, name it "Average inventory balance", and enter the formula: (SUM_AGG (Opening amount) + SUM_AGG (Closing amount))/2. Click "OK", as shown in the figure below:

2.3 Calculate the amount of goods sold

Click "+" to add a calculation indicator, name it "Amount of goods sold", and enter the formula: SUM_AGG (cost of goods sold * number). Click "OK", as shown in the figure below:

2.4 Calculate inventory turnover

Click "+" to add a calculation indicator, named "Inventory turnover", and enter the formula:Amount of goods sold/Average inventory balance. Click "OK", as shown in the figure below:

2.5 Calculate inventory turnover days

Click "+" to add a calculation indicator, name it "Inventory turnover days", and enter the formula: 360/Inventory turnover. Click "OK", as shown in the figure below:

2.6 Making a chart display

Drag the "month" field into the horizontal axis and drag the "Inventory turnover days" and "Inventory turnover" fields into the vertical axis. Select the "Custom Chart" as chart type, and set the graphic properties, and "Inventory turnover days" to " Column Chart", "Inventory turnover" is "Line Chart", as shown in the figure below:

Set the "Inventory turnover" value axis as the right value axis, and click "OK", as shown in the figure below:

2.7 Effect display

See section 1.2 of this article for details.

1) In 2016, the turnover days were the smallest in November at 0.04, and the turnover rate was the highest at 9231.42. The sales situation was the best.

2) Sales in the second half of the year were significantly better than those in the first half of the year.