The inventory turnover ratio is used in fundamental analysis to determine the amount of times a company sells and replaces its inventory over a fiscal period. The inventory turnover ratio compares a company's sales and its inventory. 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.
Explaining How to Calculate the Inventory Turnover Ratio in Excel
Compare the inventory turnover ratios between Ford Motor Company and General Motors Company using Microsoft Excel. For the fiscal period ending Dec. 31, 2014, Ford Motor Company had inventory of $7.866 billion and total revenue, or total sales, of $144.077 billion. General Motors Company had inventory of $13.642 billion and total sales of $155.929 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 Motor Company into cell B1 and General Motors Company into cell C1. Enter Dec. 31, 2014 into cells B2 and C2.
Next, enter Inventory into cell A3, Total Sales into cell A4 and Inventory Turnover Ratio into cell A5. Enter =7866000000 into cell B3 and =144077000000 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 18.32.
Next, enter =13642000000 into cell C3 and =155929000000 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.43.
Ford's higher inventory turnover ratio may indicate it has strong sales or there is less buying of the cars produced by General Motors Company.