Inventory Turnover Ratio in Excel:
Find Where Cash Is Trapped

Inventory Turnover Ratio in Excel

1. Confirm the required columns in the data tab

• Open the data tab and identify the fields used in the video:
  
  • Month end date
  • Part category
  • Beginning inventory value
  • Ending inventory value
  • Issued units
  • Unit cost
• Note that the video computes three additional columns off these inputs before building the insights view.

2. Create the calculated helper columns in the table

• Add the “Average Inventory” column and calculate it using AVERAGE across beginning and ending inventory values.
• Add the “Issued COGS” column by multiplying issued units and unit cost.
• Add the “Turnover” column by dividing issued COGS by average inventory, then format it as a percentage.

3. Start the insights area and generate a clean month list

• Move to the insights area and remove any temporary calculations that are no longer needed.
• Create a unique list of month end dates using UNIQUE.
• Make sure you reference the structured table column (not the raw column outside the table) so the list stays consistent.
• Apply date formatting so the list displays as actual dates.
• Center-align the output for readability.

4. Generate the category list and format it for a matrix layout

• Create a unique list of part categories using UNIQUE from the table column.
• Sort the category list alphabetically using SORT.
• Turn the sorted list into a horizontal header row using TRANSPOSE.
• Copy and paste values with Ctrl + Shift + V to remove formulas and keep the layout stable.

5. Calculate turnover for each month and category using conditional totals

• In the matrix body, calculate turnover using two conditional totals:
  • A conditional total of issued COGS for the selected month and category
  • A conditional total of average inventory value for the same month and category
• Use SUMIFS to aggregate the right values by both filters.
• Format the result as a percentage.

6. Make the matrix fill correctly across rows and columns

• Adjust references so the matrix behaves correctly when copied:
  • Categories should stay aligned to the header row when filling down.
  • Months should stay aligned to the left column when filling across.
• Use absolute and mixed references so month and category criteria do not “shift” incorrectly.
• If dragging causes table column references to change unexpectedly, use copy and paste across the range instead of dragging.

7. Add “Overall” summaries by month and by category

• Create an “Overall” row (overall by month) and an “Overall” column (overall by category).
• Reuse the same approach as the matrix, but remove one filter each time:
  • For overall by category, remove the month filter portion.
  • For overall by month, remove the category filter portion.
• Format these summary cells the same way as the matrix for consistent reading.

8. Calculate the single overall benchmark cell

• Build the single “overall” turnover benchmark by taking:
  • Total issued COGS across the entire table
  • Divided by total average inventory value across the entire table
• Use SUM on the table columns to create this one benchmark number.
• Format it as a percentage.

9. Apply conditional formatting to create the heat map

• Select the full matrix and the summary row.
• Apply conditional formatting using:
  • Highlight Cells Rules
  • A “Less Than” rule referencing the benchmark cell
• Set the formatting style to red so anything below the benchmark is clearly flagged.

10. Add a label to the benchmark without breaking calculations

• If you want the benchmark cell to display a label like “Target” or “Threshold” without converting it into text:
  • Open the Format Cells dialog with Ctrl + 1
  • Use a Custom number format to add the label while keeping the value numeric
• This keeps formulas and conditional formatting working correctly.

11. Convert the benchmark to “Days in Inventory”

• Since the turnover numbers are monthly, convert the benchmark turnover into a “days” interpretation by dividing a 30-day assumption by the benchmark turnover.
• Format the result as a number with one decimal place if desired.
• Use Ctrl + 1 custom formatting to add a label like “Days in inventory” so your team understands what the number represents.

12. Use the days benchmark to interpret red flags

• With the days benchmark in place, anything highlighted red corresponds to inventory sitting longer than the benchmark days threshold.
• Adjust the benchmark value and watch the heat map update automatically, so the team can align on what “too slow” means.