# SAP Business One Cycle Count Variance Worksheet

> Source: https://netray.co/tools/sap-b1-cycle-count-variance-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

An editable worksheet that compares system and counted stock, values the variance and shows the percentage before you post the inventory adjustment.

## Category

sap

## Erp

SAP Business One

## Industry

Discrete Manufacturing

## Audience

- Warehouse supervisor
- Inventory controller
- Cost accountant
- B1 super user

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Item code

**Key:** item

**Type:** text

#### Warehouse / bin

**Key:** whs

**Type:** text

#### ABC class

**Key:** abc

**Type:** select

**Options:**

- A
- B
- C

#### System qty

**Key:** sys

**Type:** number

#### Counted qty

**Key:** counted

**Type:** number

#### Unit cost

**Key:** cost

**Type:** number

**Format:** currency

#### Variance qty

**Key:** var_qty

**Type:** number

**Expr:** counted - sys

**Format:** number

#### Variance value

**Key:** var_val

**Type:** number

**Expr:** (counted - sys) * cost

**Format:** currency

#### Variance

**Key:** var_pct

**Type:** number

**Expr:** if(gt(sys, 0), (counted - sys) / sys * 100, 0)

**Format:** percent

#### Action

**Key:** action

**Type:** select

**Options:**

- Accept and post
- Recount
- Investigate
- Pending

**Rows:**

**Item:** SHF-4010

**Whs:** 01 / A-03-2

**Abc:** A

**Sys:** 240

**Counted:** 238

**Cost:** 12.4

**Action:** Accept and post

**Item:** BRG-6205

**Whs:** 01 / B-01-4

**Abc:** A

**Sys:** 1500

**Counted:** 1460

**Cost:** 3.15

**Action:** Recount

**Item:** FST-M8x30

**Whs:** 01 / C-07-1

**Abc:** C

**Sys:** 8200

**Counted:** 8200

**Cost:** 0.06

**Action:** Accept and post

**Item:** RM-STL-40

**Whs:** 02 / RACK-1

**Abc:** B

**Sys:** 96

**Counted:** 101

**Cost:** 8.2

**Action:** Investigate

**Item:** PKG-CTN-12

**Whs:** 01 / D-02-1

**Abc:** C

**Sys:** 640

**Counted:** 590

**Cost:** 0.95

**Action:** Pending

**Totals:**

#### Net variance value

**Key:** var_val

**Agg:** sum

**Format:** currency

#### Largest variance

**Key:** var_pct

**Agg:** max

**Format:** percent

**Summary:**

#### Lines counted

**Key:** lines

**Expr:** count

**Format:** integer

#### Net adjustment to post

**Key:** net

**Expr:** sum_var_val

**Format:** currency

**Primary:** true

**Notes:** Set your own tolerance by ABC class, for example tighter for A items. In Business One the posting itself is made from the inventory counting and posting documents; use this sheet to review variances and get approval before posting.

## Content

**Intro:** Cycle counts only improve accuracy if variances are reviewed before they are posted. This worksheet values each variance, shows the percentage and records what you decided to do.

**Sections:**

#### How to use the worksheet

**Body:** Enter the item, warehouse or bin, ABC class, system quantity, counted quantity and unit cost. The sheet calculates variance quantity, value and percent. Choose an action for each line: accept, recount, or investigate. Review the net adjustment before anything is posted, and have a second person approve large values.

#### The calculations

**Body:** Variance quantity is counted minus system. Variance value is that quantity times unit cost, so shortages are negative. Variance percent is variance quantity divided by system quantity, shown as zero when the system quantity is zero to avoid a meaningless figure. A system quantity of zero with stock found needs investigation, not just a posting.

**Bullets:**

- Negative means stock missing
- Positive means unrecorded stock
- Value matters more than percent on expensive items

#### Using it with Business One

**Body:** Business One records counts through inventory counting documents and posts differences with an inventory posting document, which creates the stock adjustment and the related journal entry. The count should be taken while the area is frozen or movements are recorded, otherwise transactions between the system snapshot and the count create false variances. Counting by bin and batch where used gives more useful results than totals per item.

#### Common mistakes

**Body:** Posting every variance without looking for the cause, counting during receipts and issues, and using one tolerance for all items. A recurring variance on the same item usually points to a process error such as wrong unit of measure at receipt, unrecorded scrap, or issuing to production without a transaction.

**Cta:** Netray can surface recurring stock variances and likely causes from your ERP transaction history, with a read-only AI layer.

**Faq:**

#### What tolerance should I use?

**Answer:** It depends on item value and risk. Many sites use a tight tolerance for A items, often near zero for high-value stock, and wider ones for low-value C items. Set it with finance and write it down.

#### Should I recount before posting?

**Answer:** Yes for variances above tolerance. A second count by a different person removes most errors. Only investigate and post when two counts agree or the cause is understood.

#### How often should I cycle count?

**Answer:** A common approach counts A items more often than B and C. Frequency should reflect value, movement and error history. The goal is to find process errors early, not only to correct the numbers.

#### Why does zero system stock matter?

**Answer:** It suggests receipts that were never booked or negative stock that was corrected. The percent cannot be calculated, so the sheet shows zero. Treat any such line as an investigation item.

## Search terms

cycle count worksheet excel, sap business one inventory counting, inventory variance worksheet, cycle count sheet template, stock count variance calculator, sap b1 inventory posting variance, warehouse cycle count template

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