How to Use Excel Formulas and Functions to Analyze Inventory for a Retail Store

Excel Formulas to Analyze Inventory

My friend, Alan Friedman, is a CPA and Partner in Friedman Kannenberg and Company, PC. I have been using one of the Excel Worksheets that Alan developed to teach retail store owners and managers how to analyze their inventory. In this lesson, I demonstrate how to write and copy the Excel formulas needed to perform this analysis.

Excel Formulas for Analyzing Inventory

Many retailers use a Point-of-Sale (POS) System that can generate three numbers: Sales by Product Category, Cost of Goods Sold (COGS) by Product Category and Average Inventory by Product Category. Taking these three numbers – also known as “hard-coded” values – you can use Excel Formulas to give you:

Gross Profit Dollars (Sales minus COGS)
Gross Profit Percentage (Gross Profit Dollars divided by Sales)
Inventory Turns (COGS divided by Average Inventory)
Product Category Sales as a Percentage of Total Sales (Category Sales divided by Total Sales) – Use an Absolute Cell Reference
Inventory by Category as a Percentage of Total Inventory – (Inventory by Category divided by Total Inventory) – Use an Absolute Cell Reference
Gross Margin Return on Inventory (GMROI) – (Gross Profit Dollars divided by Average Inventory)

Tips for Entering and Copying Excel Formulas

As I demonstrate in this tutorial, you can write and copy an Excel Formula in one step when you first select the cells to receive the formula and use the Ctrl + Enter Keyboard Shortcut to complete the formula. Instead of clicking on the AutoSum Command or writing =SUM(), use the Alt + = (equal sign) to sum up the values in the adjacent range of cells.

Learn to Improve Your Excel Skills

If you want to learn how to create and use more Excel Formulas and Functions, I have created the Best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.” You can learn more about this resource by visiting my secure online shopping website – http://shop.thecompanyrocks.com