The inventory turnover ratio is used in fundamental analysis to determine the number of times a company sells and replaces its inventory over a fiscal period. To calculate a company's inventory turnover, divide its sales by its inventory. Similarly, the ratio can be calculated by dividing the company's cost of goods sold (COGS) by its average inventory. Retailers tend to have the highest inventory turnover, but the rate can indicate a well-run company or the industry as a whole.
Key Takeaways
- Inventory turnover is the number of times a company sells and replaces (turnover) its inventory in a given period.
- The formula is sales divided by inventory.
- However, the inventory turnover can also be calculated by dividing the cost of goods sold (COGS) by average inventory.
- Inventory turnover can easily be calculated using Microsoft Excel.
Calculating Inventory Turnover in Excel
Inventory turnover can be easily and quickly calculated using Microsoft Excel. For example, let’s compare the inventory turnover ratios for Ford (F) and General Motors (GM) using Excel.
For the fiscal period ending Dec. 31, 2020, Ford had an inventory of $9.99 billion and total revenue of $127.14 billion.
General Motors had an inventory of $10.40 billion and total sales of $122.49 billion for that same fiscal period.
In Excel, right-click on columns A, B, and C, and click on Column Width. Next, change the value to 30 for each of the columns. Then, click OK.
Enter Ford into cell B1 and General Motors into cell C1. Enter Dec. 31, 2020, into cells B2 and C2. Next, enter inventory into cell A3, Total Sales into cell A4, and Inventory Turnover Ratio into cell A5.
Enter =9990000000 into cell B3 and =127140000000 into cell B4. Ford's inventory turnover ratio is calculated by entering the formula =B4/B3 into cell B5. The resulting inventory turnover ratio of Ford Motor Company is 12.73.
Next, enter =10400000000 into cell C3 and =122490000000 into cell C4. Similarly, the inventory turnover ratio of General Motors Company is calculated by entering the formula =C4/C3 into cell C5. The resulting inventory turnover ratio is 11.78.
Ford's higher inventory turnover ratio may indicate it is able to sell its cars faster, turning its inventory over faster. General Motors is holding more inventory than Ford and its sales are less.
The Bottom Line
Note that Excel is a powerful tool that allows for quick and easy calculations of ratios and formulas. Taking this analysis a step further, we could better assess Ford and General Motors' respective inventory turnover by looking at historical numbers.
This can be done by looking at the inventory turnover over the last several years (such as five) for both companies. As well, an average of these inventory turnover ratios could be calculated to assess the current inventory turnover.
Alliteratively, we could pull in additional carmakers to get a broader representation of what a "good" inventory turnover ratio is in the auto industry.
