Home inventory excel template code#
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. The two letters are used to identify the type of stock - for example, RM refers to raw materials and PM refers to packaging material. Refer to the sample data on the StockCodes sheet - we have used a code convention which starts with two letters followed by four numbers. The stock codes 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 available for selection on the other sheets.Ī 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. You can then use the template only for controlling inventory on hand and analysing inventory movements.Ī unique stock code needs to be created on the StockCodes sheet for all inventory items that you want to include in the template. Note: If you do not want to use the profitability calculations to measure sales against inventory usage, you can safely delete the Sales and Profitability sheets without it having any effect on the other sheets. A daily sales analysis for the specified calendar month and a monthly analysis for the last 12 months are also included on this sheet and can be calculated for any user defined period by simply entering the appropriate "From" and "To" dates at the top of the sheet. 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.
Profitability - the calculations on this sheet enable users to compare inventory usage and inventory adjustments to sales. The profitability features of this template is therefore not appropriate for businesses which carry a significant amount of finished product stock as sales and usage would not always be comparable.
Cost of sales for the purpose of this analysis is defined as the total of stock usage and stock adjustments. Sales - record daily sales totals if you want to measure stock usage against sales in order to analyse profitability. Aside from the filter and report selections, no user input is required on this sheet and all workings are included below the dashboard. The dashboard also contains stats of the top products by transaction type as well as purchase price variance stats. The dashboard also includes a unique interactive report where you can select the basis of balances or movements, select amounts, costs or quantities and view all transaction types or any of purchases, usage and adjustments individually. You can select an individual stock code or clear the contents of the list box to view calculations for all stock items. 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.ĭashboard - the inventory dashboard is automatically updated based on the stock code and dates selected in the filter section of the dashboard. StockCount - this sheet can be used to perform a physical stock count and to analyse stock take variances. Movements - all inventory movement transactions should be recorded on this sheet. StockCodes - this sheet can be used to create unique stock codes for all inventory items and also includes inventory valuation and inventory movement calculations by stock code. The following sheets are included in this template:
All inventory valuations are performed on an average cost basis.
The functionality 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. 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. This unique template enables users to analyse the profitability of a business by comparing sales to inventory usage.
0 kommentar(er)
