Product preview

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.


Meet the creator

Excel Inventory Management Template – Track Stock, Costs & Profit

$8.50
Excel Inventory Management Template – Track Stock, Costs & Profit