Vend Retail Guide
Free excel inventory and sales template for retailers
Spend less time crunching the numbers and devote more energy to running and growing your business
ESSENTIAL RETAIL METRICS
Get vital inventory and sales data instantly. Use this tool to calculate metrics like GMROI, stock turn, shrinkage, conversion rate, and more.
NO NEED TO BE AN EXCEL EXPERT
You won’t have to worry about formulas or Excel tables. Just type in the data you already have and this tool will take care of the rest.
ONE SPREADSHEET TO RULE THEM ALL
Get the most important metrics in one place. We compiled essential retail formulas and put them in easy-to-understand spreadsheets so you can get insights faster.
TRACK STORE PERFORMANCE
With just a few clicks, you can get an at-a-glance view of general store performance while still being able to drill down on how each item is doing.
NO MORE BACK OF THE ENVELOPE CALCULATIONS
No need to manually calculate your sales and inventory data. Just enter your product and sales information, and the template will automatically display the answers you need.
MAKE SMARTER INVENTORY DECISIONS
Vend’s Excel inventory spreadsheet has formulas for calculating crucial stock control metrics. It helps you better understand product performance and make smarter inventory decisions.
Frequently asked questions
Vend’s Excel inventory and sales template helps you stay on top of your inventory and sales by putting vital retail data at your fingertips. We compiled some of the most important metrics that you should track in your retail business, and put them into easy-to-use spreadsheets that automatically calculate metrics such as GMROI, conversion rate, stock turn, margins, and more.
No need to manually do the math; Vend’s retail workbook crunches the numbers for you, so you can focus on gaining insights and growing your business.
Here’s a list of the metrics and formulas that we cover in this workbook along with descriptions on why they’re important:
Average inventory
This metric measures the average of your beginning and ending inventory. It gives you an estimate of the average inventory amount for a particular period.
Average Inventory = (Current Inventory + Previous Inventory) / 2
Cost of Goods Sold (COGS)
COGS is the total of all costs associated with creating goods that have been sold. It tells you how much your expenses are every time you make a sale, and it’s often used to determine pricing.
COGS = Beginning Inventory + Purchases - Ending Inventory
Gross Margin
This is your total revenue (equivalent to total sales) minus the cost of goods sold. It’s how much you make after deducting the costs of creating and selling your products.
Gross Margin = Sales - COGS
Inventory Turnover Rate
Also known as stock turn, inventory turnover is the number of times stock is sold through or used in a given time period. In most cases, the higher the stock turn, the better it is for your store because it means you’re selling a lot of merchandise without stocking too much inventory.
Inventory Turnover Rate = Cost of Goods Sold / Average Inventory
Sell-through %
Sell through is the percentage of units sold versus the number of units that were available to be sold. You can use this metric to evaluate product performance.
Sell-through % = Number of Units Sold / Beginning Inventory x 100
Gross Margin Return on Investment (GMROI)
GMROI measures your profit return on the funds invested in stock. It answers the questions, “How many gross margin dollars did I make from my inventory investment?” or “For every dollar invested in inventory, how many dollars did I get back?”
GMROI = Gross Profit / Average Inventory
Shrinkage
This refers to the difference between the amount of stock that you have on paper and the actual stock you have available. It’s a reduction in inventory that isn’t caused by legit sales. The common causes of shrinkage include employee theft, shoplifting, administrative errors, and supplier fraud.
Shrinkage = Ending Inventory Value - Physically Counted Inventory Value
Shrinkage %
This is shrinkage rate expressed as a percentage. It’s the value of lost inventory divided by sales during the same period.
Shrinkage % = Shrinkage / Sales x 100
According to a survey by the National Retail Federation, the average inventory shrink as a percentage of sales was 1.38% in 2015. It’s important to note that data varies from one retail sector to the next. Specifically:
- Grocery - 3.6%
- Specialty men’s and women’s apparel - 1.2%
- Discount, mass merchandise or super center retailers - 1.1%
Gross Profit
Gross profit pertains to revenue minus that cost of goods sold. In other words, this is the amount of profit you take home once the costs associated with making and selling the merchandise are deducted.
Gross Profit = Revenue - Cost of Goods Sold
Conversion Rate
Conversion rate is the proportion of store visits to the number of shoppers who made a purchase. It helps you measure the performance of various in-store components, including customer service, merchandising, and more.
Conversion Rate = Number of Sales / Number of Visitors
Average Basket Value
Average basket value (aka average transaction value) tells you the average customer spend in your store. It can give you a macro view of how much people are spending as well as the types and quantity of items they buy.
Average Basket Value = Revenue / Number of Sales
This template is for retailers who are using manual and cumbersome systems (i.e. pen and paper) in their business. If you’re still recording inventory and sales by hand and you’re looking for a faster and easier way to stay on top of your stock control and sales, this resource is for you.
We’d love to hear from you! If you have questions or suggestions on how we can improve this tool, send us an email here.