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
Watch Tutorial in High Definition
I produce my Excel Tutorials in High Definition. Follow this link to view this lesson on my YouTube Channel – DannyRocksExcels
How to Take Advantage of the Go To Special Dialog Box Options in Excel
Go To Special Options
In my opinion, the Go To Special Options Dialog Box offers some of the most useful tools in Excel!
Why?
Because, you must…
Select Cells Before Performing an Action on these Cells
The “key” to understanding ANY MS Office or Windows Program is… You MUST select a single cell or a range of cells BEFORE you can perform an action on them – e.g. Formatting you selection, deleting your selection, editing your selection or auditing your selection.
Tips Presented in this Video Tutorial
I am positive that Excel users at ANY LEVEL will be able to pick up at least one solid tip from this Video Tutorial. Please send me your comments to let me know what you learned – or what you need clarification on.
Watch Tutorial in High Definition Mode
Follow this link to view this Excel Tutorial in High Definition / Full Screen Mode on my YouTube Channel – DannyRocksExcels
Learn About My New Extended Length Excel Video Tutorials
I have just published the first in a series of “Extended Length” – 90 Minutes – Video Tutorials, “Excel Pivot Tables to Summarize, Analyze and Present Your Data.” Follow this link to learn more about this tutorial. I have created separate versions of the tutorial for Excel 2010, 2007 and 2003.