Who is this template for?
- Small business owners.
- Retail stores and shop owners.
- Online sellers and eCommerce businesses.
- Wholesale distributors.
- Warehouse and inventory staff.
- Entrepreneurs who want affordable inventory management.
INVENTORY MANAGEMENT – STOCK IN / OUT / BALANCE – COST / SELLING PRICE / PROFIT
Currency: VND. Cost of goods is calculated using the weighted average method.
1. SHEETS IN THIS FILE
Products Declare products: code, name, category, unit, cost price, selling price, opening stock, minimum stock. Profit/unit and margin % are calculated automatically.
Stock In One row per purchase: date, voucher no., select item code, quantity, purchase price. Item name, unit and amount are filled in automatically.
Stock Out One row per sale/issue: date, voucher no., select item code, quantity. Selling price, cost, revenue and gross profit are automatic. Warns when issuing more than the stock on hand.
Inventory Automatic: opening + in – out = closing stock, average cost, stock value, expected profit and status (In Stock / Low Stock / Out of Stock).
Profit by Item Automatic: quantity sold, revenue, cost, gross profit, margin % and profit rank of each item (all time).
Report Yearly overview: revenue, cost of goods, gross profit, purchases, stock value, monthly chart. Change the 'Report Year' cell to view another year.
2. HOW TO USE (5 STEPS)
Step 1 Sheet 'Products': enter item code (unique), name, category, unit, cost price, selling price, opening stock, minimum stock.
Step 2 Sheet 'Stock In': when receiving goods, pick the item code from the dropdown, then enter quantity and purchase price.
Step 3 Sheet 'Stock Out': when selling, pick the item code and enter the quantity. If you sell at a price other than the list price (discount, wholesale), enter it in the 'Adjusted Selling Price' column; leave it blank to use the selling price in Products.
Step 4 Check the 'Inventory' sheet to see how much stock is left and which items are running low and need reordering.
Step 5 Check the 'Report' and 'Profit by Item' sheets for profit and loss by month and by item.
3. COLOR CODE
Blue text Input cells – enter your data in these cells.
Gray fill Automatic formula cells – do not edit (locked).
Yellow fill Key setting cell (e.g. Report Year).
4. CALCULATION FORMULAS
Profit/Unit = Selling Price – Cost Price (in Products).
Margin % (on selling price) = Profit/Unit ÷ Selling Price (profit margin). Markup % (on cost) = Profit/Unit ÷ Cost Price.
Purchase Amount = Quantity In × Purchase Price.
Closing Stock = Opening Stock + Total In – Total Out.
Average Cost = (Opening Stock × Cost Price + Total Purchase Amount) ÷ (Opening Stock + Total Quantity In). Opening stock is valued at the Cost Price in Products.
Revenue = Quantity Out × Applied Selling Price.
Gross Profit = Revenue – (Quantity Out × Average Cost). Margin % = Gross Profit ÷ Revenue.
Stock Value = Closing Stock × Average Cost.
5. NOTES
Capacity Up to 200 items, 1,500 stock-in rows and 1,500 stock-out rows. Contact the seller if you need more capacity.
Sheet Protection Formula cells are locked to prevent accidental deletion (no password set). To edit: Review > Unprotect Sheet.
Do Not Change Item Codes Do not change an item code after stock movements have been recorded, as past data will no longer match. Add a new code instead of editing.
Warning The 'Warning' column in Stock Out turns red when issuing more than the stock on hand; Stock In shows 'Code not found' if the code is not declared.