How to keep track of goods and materials received and issued: turnover register, goods report and Excel template

The month is over. The shop owner asks the accountant two questions: "How much stock is left in the warehouse?" and "What did the goods we sold actually cost us?". The first is answered by the quantity and amount for each item, the second by the cost of issues. To see both in one place, without errors and in the same order every month, accountants keep registers. Below are the two main registers of a trading company, their formulas, and a calculation using the fictional «Namuna Savdo» LLC. At the end of the article you will find an Excel template with formulas and a completed example.

Which registers does an accountant of a trading company keep?

Under NAS No. 21, goods are recorded in the accounts of the 2900 group. Goods in the warehouse are carried in 2910 at purchase cost (paragraph 145), and goods in shops and kiosks in 2920 (paragraph 146). If goods in retail are carried at selling prices, the markup is accumulated separately in account 2980 "Trade markup" (paragraph 152). Analytical accounting for 2900 is kept by materially responsible persons, item names and storage locations (paragraph 153). In practice this leads to two kinds of registers:

The table layouts below are a training aid prepared by the author, not an officially approved form. The valuation method, however, must be stated in the entity's accounting policy (NAS No. 4, paragraph 67).

How is the cost of issues calculated by the weighted average method?

Paragraph 62 of NAS No. 4 gives three methods for valuing inventories issued: specific identification, weighted average cost (AVECO) and FIFO. Only one method is applied to a group of goods throughout the year (paragraph 63). Under AVECO, the total cost of identical items in the opening balance and those received during the period is divided by their total quantity; the average price may be calculated periodically or on receipt of each new batch (paragraph 65). The template uses the periodic option, calculating once at the end of the month.

Flour in the «Namuna Savdo» warehouse for January:

Average price = 23,650,000 ÷ 4,200 = 5,630.95 UZS. Amount issued = 23,650,000 ÷ 4,200 × 2,600 = 14,640,476.19 UZS (rounded to 2 decimal places). The closing amount is found by subtraction: 23,650,000 − 14,640,476.19 = 9,009,523.81. This balance carries over to the February sheet as the opening balance.

One subtle point: if an item is issued in full, its amount must also fall to zero. If you multiply by a rounded average price, 0.01 UZS may be "left hanging" even when the quantity is 0. That is why the template writes off the whole amount when the entire balance is issued.

How are markup, VAT and the selling price related?

In the goods report each receipt document appears in five columns. The chain is always the same: cost, plus markup, selling price excluding VAT, plus VAT, selling price including VAT. The VAT rate sits in a separate cell in the template: the general rate is 12%. The example is illustrative, with a markup of 20%:

For issues the direction is reversed: the till gives an amount including VAT, and the part excluding VAT is separated from it. For example, if monthly sales are 30,240,000 UZS: 30,240,000 ÷ 1.12 = 27,000,000 UZS excluding VAT and 3,240,000 UZS of VAT.

Note: a 20% markup on cost is not 20 percent of the selling price but 20 ÷ 120 = 16.67 percent. It is exactly this "share in the price" that the next calculation needs.

How is the cost of goods sold found when goods are carried at selling prices?

Under paragraph 61 of NAS No. 4, the cost of goods sold is "the difference between the selling value and the trade markup amount attributable to them". The average markup percentage = (markup at the start of the month + markup on goods received) ÷ (selling value of the opening balance and of goods received). The template takes this ratio from the selling price excluding VAT.

The «Namuna Savdo» shop, January (illustrative figures; the markup is 15% on one of the four receipt documents and 20% on the others):

Average share = 7,660,000 ÷ 47,960,000 = 15.97%. Markup on goods sold = 27,000,000 × 15.97% ≈ 4,312,343.62 UZS (the template uses the percentage without rounding). Cost = 27,000,000 − 4,312,343.62 = 22,687,656.38 UZS. The gross profit on goods sold (sales excluding VAT − cost) equals exactly this markup amount.

In the example the shop also returned goods worth 448,000 UZS to the supplier and wrote off spoiled goods worth 134,400 UZS (both at selling prices including VAT). The closing balance for the month came to 22,892,800 UZS (including VAT). The inventory count found goods worth 22,840,000 UZS: a difference of −52,800 UZS, i.e. a shortage. The template shows this difference automatically.

What journal entries are made?

The table in paragraph 154 of NAS No. 21 contains the following account correspondences for goods. The amounts come from the January example above:

In the table, line 13 is called "Write-off of the cost of goods sold"; the table does not say at what price it is recorded. Author's interpretation: because 2920 is carried at selling prices, this entry is also made at the selling price, and then, under paragraph 152, the part of the markup attributable to goods sold is posted to the debit of 2980 and the credit of 9120. As a result, 27,000,000 − 4,312,343.62 = 22,687,656.38 UZS, i.e. the actual cost, remains in 9120. In the template the 2920 balance is taken at the selling price excluding VAT (cost + markup); the VAT column is there for the price tag and for separating sales proceeds.

Other cases: under paragraph 152, the markup on goods written off (spoiled, short and other) is also posted to the debit of 2980. For a shortage the table has line 11 (if no culprit is identified, Dr 5910) and line 14 (through the fault of the materially responsible person, Dr 4730). Set out the entries for sales revenue, VAT and returns to suppliers in your accounting policy; for revenue see the article "What journal entries are made when goods are sold?".

Practical tips

• Do not add up quantities: kilograms, litres and pieces cannot be added together. The total line should contain amounts only.
• Record the number and date of every receipt document: it is the first thing asked for in an audit.
• If a "negative balance" appears, the error is almost always that an issue was recorded before the receipt or a document was forgotten.
• At the end of the month look at the check line: opening balance + receipts − issues − closing balance = 0.
• Compare the goods report balance with the inventory count result; you will see the difference immediately.
• Do not change the valuation method mid-year (NAS No. 4, paragraph 63), and state it in the accounting policy.

How to use the template?

1. On the «Sozlamalar» (settings) sheet enter the company name and the year; check the VAT rate (12%) and the default markup percentage.
2. On sheet «01» enter the list of items, units of measure and the opening balance for the year (quantity and amount). In the following months the names and balances carry over automatically.
3. Each month enter receipt documents in the «Kirim 1–3» columns and the quantity issued in the «Chiqim» column. The average price, amount issued and closing balance are calculated by formulas.
4. The «Yillik» sheet shows the annual totals for each item; the check column should read «OK».
5. For a retail shop, fill in the «Tovar hisoboti (oy)» sheet: opening balance, receipt documents, issues (including VAT). The markup, VAT, cost of goods sold and amounts for journal entries appear automatically.
6. Fill in only the yellow cells; the rest are protected (no password). The «Aylanma qaydnoma (oy)» sheet is a standalone one-month copy for materials or a separate warehouse.

The completed example has January and February entered for «Namuna Savdo» LLC, so you can see the formula results straight away. Both files are linked at the bottom of the page. The files are in Uzbek (Latin script).