Please vote for this template if it helps you.
Page 1 of 16
Excel Skills | Inventory Control Template | Usage Based
Instructions
www.excel-skills.com
This unique template enables users to analyse the profitability of a business by comparing sales to inventory usage.
In many business environments, it is impractical to perform accurate calculations of cost of sales and it is a lot more
feasible to compare inventory usage to sales. The functionality that has been included in this template enables
users to control inventory by creating stock codes for all individual inventory items; recording inventory purchases,
usage and adjustments; performing physical stock counts and analysing stock take variances; calculating stock
valuations and measuring sales, cost of sales & gross profits based on any user defined date range. All inventory
The following sheets are included in this template:
StockCodes - this sheet can be used to create unique stock codes for all inventory items and also includes an
inventory valuation and inventory movement report for the stock codes that are created.
Movements - all inventory movement transactions should be recorded on this sheet.
StockCount - this sheet can be used to perform a physical stock count and to analyse stock take variances.
Physical stock count quantities are measured against theoretical stock balances and once the accuracy of the stock
take variances has been established, the calculated stock variances can be recorded as stock adjustments on the
Movements sheet in order to adjust the theoretical stock quantities to the physical stock count quantities.
Sales - this sheet can be used to record the daily or weekly sales totals in order to compare inventory usage to
sales.
Profitability - the calculations on this sheet enable users to compare inventory usage and inventory adjustments to
sales. The total inventory usage and adjustments are included in the cost of sales total and deducted from the sales
total in order to calculate the appropriate gross profit amounts and percentages. A daily sales analysis is also
included on this sheet and can be compiled based on any user defined period by simply entering the appropriate
Note: Most of the calculations in this template are based on the number of rows that are included in a continuous
cell range on each sheet. It is therefore imperative that you do not insert any empty rows between the data that is
included on each sheet, otherwise some transactions may not be included in the template calculations.
Stock Codes
Create & Maintain Stock Codes
A unique stock code needs to be created on the StockCodes sheet for all inventory items that you want to include in
the template. The stock codes that are created on this sheet are included in list boxes in the stock code columns on
the other sheets and users are therefore required to create the appropriate stock code before the code will be
A stock code convention that makes sense in the context of your business should be used and the stock code
convention should make it easy to identify stock items based on the stock code that is assigned to each stock item.
Refer to the example data that is included on the StockCodes sheet - we have used a code convention that starts
The two letters are used to identify the type of stock - for example, RM refers to raw materials and PM refers to
packaging material. The four numbers that are included in the stock code convention provides for sufficient unique
stock codes to be created for each type of stock.
Stock codes should not be duplicated - if a stock code is duplicated on the StockCodes sheet, it may result in
inaccurate inventory valuation calculations. If a stock code is duplicated on the StockCodes sheet, an error will be
reflected in the error code column. All the error codes that are reflected in this column should be rectified before
reviewing the data. Refer to the Error Codes section of the instructions for more information on the different types of
Our example stock code convention is by no means a template requirement. You can use any stock code
convention as long as only unique stock codes are created and you are able to easily identify the stock codes that
The StockCodes sheet contains the following user input columns:
Stock Code - enter a unique stock code in accordance with the stock code convention that is suitable for your type
Description - enter a description of the stock item. This description should enable users to easily distinguish
source: excel-skills.com
Previous

2/17

Next