Formula for Inventory Turnover in Excel

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.

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.