How to Track Inventory Turnover by Month in Excel​

Inventory Turnover Step by Step in Excel

1. Set up the monthly table structure

• Create a Month column starting with January and drag down to December.
  
• Add these column headers:
  
  • Starting Inventory
    
  • Inventory Purchases
    
  • COGS
    
  • Ending Inventory Value
    
  • Average Inventory Value
    
  • Inventory Turnover
    

2. Enter the starting inventory value

• In January Starting Inventory, enter your beginning inventory number (example shown: 320,000).
  
• This is your baseline inventory value for the first month.
  

3. Calculate Inventory Purchases by month with SUMIF

• Use SUMIF to sum purchases for each month.
  
• Select the purchase amount column as the sum range (the column that contains dollar amounts for purchases).
  
• Select the month column as the criteria range.
  
• Use the month name in your table (January, February, etc.) as the criteria.
  
• Format as currency:
  
  • Ctrl + Shift + 4 for dollars
    
  • Remove decimals if desired
    
  • Center align if desired
    

4. Calculate COGS by month with the same SUMIF logic

• Use the exact same SUMIF formula you used for purchases.
  
• The only change is the sum range should reference the COGS column instead of the purchases column.
  
• Fill down for all months.
  
• Quick fill trick:
  
  • Select the first cell with the formula
    
  • Ctrl + Shift + Down
    
  • Ctrl + D to fill down
    
• Use Format Painter if you want consistent formatting across columns.
  

5. Calculate Ending Inventory Value

• For each month, calculate:
  
  • Ending Inventory = Starting Inventory + Purchases − COGS
    
• Fill down for all months.
  

6. Link next month’s Starting Inventory to prior month’s Ending Inventory

• February Starting Inventory should equal January Ending Inventory.
  
• Fill down so every month’s starting value pulls from the prior month’s ending value.
  

7. Calculate Average Inventory Value

• Calculate monthly average inventory as:
  
  • (Starting Inventory + Ending Inventory) / 2
    
• Important: use parentheses so Excel calculates correctly.
  
  • Without parentheses, Excel divides before adding due to order of operations.
    
• Fill down for all months.
  

8. Calculate Inventory Turnover

• Use this structure:
  
  • Inventory Turnover = COGS / Average Inventory Value
    
• Fill down for all months.
  
• Format to 2 decimals:
  
  • Ctrl + Shift + 1
    

9. Create the Inventory Turnover line chart

• Highlight the Month column.
  
• Hold Ctrl and highlight the Inventory Turnover column.
  
• Insert a line chart:
  
  • Insert → Line Chart
    
• Optional formatting:
  
  • Double click the axis or labels → Number format → set to 1 decimal
    
  • Add data labels:
    
    • Click the chart → click the plus icon → Data Labels
      

10. Create the Purchases vs COGS clustered column chart

• Highlight the Month column.
  
• Hold Ctrl and highlight:
  
  • Inventory Purchases
    
  • COGS
    
• Insert a clustered column chart:
  
  • Insert → Column Chart → Clustered Column
    
• Rename the chart title to something like:
  
  • “Purchases vs COGS”
    

11. Apply conditional formatting color scales

• Highlight a value column (example: Starting Inventory).
  
• Go to:
  
  • Home → Conditional Formatting → Color Scales
    
• Pick the scale that matches your intent:
  
  • For inventory levels: you may want green = high
    
  • For purchases or COGS: you may want red = high
    
• Repeat for other columns, or speed it up:
  
  • Double click Format Painter and apply the same formatting across multiple ranges.