# Marg ERP Inventory Valuation Worksheet

> Source: https://netray.co/tools/marg-erp-inventory-valuation-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Compute weighted average cost and lower of cost or net realisable value for closing stock, and see the write-down per item.

## Category

cost-finance

## Erp

Marg ERP

## Region

India

## Audience

- Accountant
- Pharmacy owner
- Distributor
- Auditor
- Inventory manager

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** Rs 

**Columns:**

#### Item

**Key:** item

**Type:** text

#### Batch or code

**Key:** batch

**Type:** text

#### Opening qty

**Key:** open_qty

**Type:** number

#### Opening rate

**Key:** open_rate

**Type:** number

**Format:** currency

#### Purchase qty

**Key:** pur_qty

**Type:** number

#### Purchase rate

**Key:** pur_rate

**Type:** number

**Format:** currency

#### Sold qty

**Key:** sold_qty

**Type:** number

#### NRV per unit

**Key:** nrv_rate

**Type:** number

**Format:** currency

**Help:** Expected selling price less cost to sell

#### Closing qty

**Key:** close_qty

**Type:** number

**Expr:** open_qty + pur_qty - sold_qty

#### Weighted avg rate

**Key:** wavg

**Type:** number

**Expr:** (open_qty * open_rate + pur_qty * pur_rate) / (open_qty + pur_qty)

**Format:** currency

#### Value at cost

**Key:** cost_val

**Type:** number

**Expr:** (open_qty + pur_qty - sold_qty) * (open_qty * open_rate + pur_qty * pur_rate) / (open_qty + pur_qty)

**Format:** currency

#### Value at lower of cost and NRV

**Key:** lower_val

**Type:** number

**Expr:** (open_qty + pur_qty - sold_qty) * min((open_qty * open_rate + pur_qty * pur_rate) / (open_qty + pur_qty), nrv_rate)

**Format:** currency

#### Write-down

**Key:** writedown

**Type:** number

**Expr:** (open_qty + pur_qty - sold_qty) * max(0, (open_qty * open_rate + pur_qty * pur_rate) / (open_qty + pur_qty) - nrv_rate)

**Format:** currency

**Rows:**

**Item:** Paracetamol 500mg Strip

**Batch:** B2401

**Open qty:** 400

**Open rate:** 11.5

**Pur qty:** 600

**Pur rate:** 12.4

**Sold qty:** 700

**Nrv rate:** 14.2

**Item:** ORS Sachet

**Batch:** B2318

**Open qty:** 250

**Open rate:** 15

**Pur qty:** 0

**Pur rate:** 0

**Sold qty:** 90

**Nrv rate:** 12.5

**Item:** Surgical Gloves Box

**Batch:** G7710

**Open qty:** 120

**Open rate:** 205

**Pur qty:** 200

**Pur rate:** 218

**Sold qty:** 260

**Nrv rate:** 245

**Item:** Digital Thermometer

**Batch:** T0092

**Open qty:** 60

**Open rate:** 135

**Pur qty:** 40

**Pur rate:** 142

**Sold qty:** 55

**Nrv rate:** 190

**Item:** Hand Sanitiser 500ml

**Batch:** H1150

**Open qty:** 300

**Open rate:** 78

**Pur qty:** 100

**Pur rate:** 72

**Sold qty:** 180

**Nrv rate:** 70

**Totals:**

#### Closing stock at cost

**Key:** cost_val

**Agg:** sum

**Format:** currency

#### Closing stock at lower of cost and NRV

**Key:** lower_val

**Agg:** sum

**Format:** currency

#### Total write-down

**Key:** writedown

**Agg:** sum

**Format:** currency

**Summary:**

#### Write-down as percent of cost value

**Key:** wd_pct

**Expr:** sum_writedown / sum_cost_val * 100

**Format:** percent

**Primary:** true

#### Average closing value per item

**Key:** avg_val

**Expr:** sum_lower_val / count

**Format:** currency

**Notes:** Enter figures from your stock reports, for example opening, purchase and sales quantities for each item or batch. Weighted average here uses opening stock plus purchases for the period and ignores returns and mid-period price effects, so it may differ slightly from the method set in your software. Confirm the valuation method in your accounting policy. This worksheet is a cross check and not accounting or audit advice.

## Content

**Intro:** Closing stock value flows straight into profit, so wholesalers, pharmacies and distributors on Marg ERP often want an independent check before finalising accounts. This worksheet recomputes weighted average cost and applies the lower of cost and net realisable value to show write-downs.

**Sections:**

#### How to use the worksheet

**Body:** Pull opening quantity and rate, purchase quantity and rate, and sales quantity for each item or batch from your Marg reports. Add the net realisable value per unit, which is the expected selling price less costs to sell. The sheet calculates closing quantity, weighted average rate, value at cost, value at lower of cost and NRV, and the write-down. Compare the totals with the closing stock in your trial balance.

#### The method behind it

**Body:** Inventory is generally carried at the lower of cost and net realisable value under both the older accounting standard and Ind AS 2. Cost under weighted average is the total cost of opening stock plus purchases divided by total quantity. Where NRV is below cost, the item is written down to NRV. Items with NRV above cost stay at cost and gains are not recognised.

**Bullets:**

- Weighted average rate = (opening value + purchase value) / (opening qty + purchase qty)
- Value at cost = closing qty x weighted average rate
- Lower of cost and NRV = closing qty x the smaller of the two rates
- Write-down = closing qty x the shortfall of NRV below cost

#### Reading the result

**Body:** The example shows most lines carried at cost because NRV is above cost, while ORS and hand sanitiser are written down because the selling price is below their cost. A write-down percentage that is high suggests slow moving or mispriced stock, or expiry risk. Check batches near expiry separately, since their NRV may be close to zero, and compare with your provision policy.

#### Common mistakes

**Body:** Common errors include mixing MRP with NRV, forgetting that GST is not part of inventory cost when input credit is claimed, valuing the whole item when batches have different costs, and ignoring sales returns and purchase returns. Another is comparing totals without checking negative stock items, which distort the weighted average. Clean negative quantities first.

**Cta:** If you keep rebuilding valuation checks outside your billing software, Netray can help set up private reporting over your stock data, with the query visible and read-only access.

**Faq:**

#### How is closing stock valued in Marg ERP?

**Answer:** Marg lets you choose how stock is valued, for example by purchase rate or average method, depending on configuration. Check your version's settings and your accounting policy, and use this sheet as an independent cross check.

#### What is the lower of cost and net realisable value?

**Answer:** It is the accounting rule that stock is carried at the smaller of what it cost and what you expect to net from selling it. It prevents overstating assets when prices fall or goods are damaged.

#### Should I include GST in stock value?

**Answer:** Generally not when you can claim input tax credit, since GST paid is recoverable and is not part of cost. If credit is blocked or you are unregistered, the tax may become part of cost. Check with your accountant.

#### Can I use FIFO instead of weighted average?

**Answer:** Both are accepted cost formulas, but you must apply one consistently for similar items. This sheet uses weighted average. For FIFO you need batch level purchase layers, which are better taken from your stock ageing report.

## Search terms

marg erp inventory valuation, marg stock valuation, closing stock valuation excel, weighted average cost excel india, lower of cost and nrv worksheet, ind as 2 inventory valuation, marg erp closing stock report, stock valuation for balance sheet india

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json