Bulk Import & Pricing Rules

# Bulk Item Import & Multiple Pricing Tiers Guide

A comprehensive operational guide on bulk importing inventory, managing opening stock balances, and configuring multi-tier packaging pricing while strictly adhering to financial and inventory valuation guardrails.

1. Overview & Business Purpose

The Bulk Import Items feature allows organizations to rapidly onboard products, physical stock, services, and multi-tier pricing structures into Qotrack without manual entry.

Whether you are a new organization onboarding with inventory already typed out or exported in Excel/CSV, migrating from another software, initializing opening balances for a new branch, or updating wholesale pricing grids, this tool automates:

1. Catalog Registration: Creates items with SKUs, barcodes, categories, and account links.
2. Opening Stock & Ledger Integration: Automatically logs opening stock on hand and generates the corresponding double-entry journal entries (Inventory Asset vs Owner's Equity).
3. Multi-Tier Pricing Grids: Establishes wholesale, carton, box, pack, and retail pricing tiers in a single pass.
4. Intelligent Duplicate Resolution: Detects existing items in real time and offers instant choices to skip or update existing records.

2. The Import Interface & Workflow

To access the bulk importer:

1. Navigate to Inventory > Items in the main navigation menu.
2. Click the Import Items button in the top toolbar to open the centralized import modal.

Key Controls on the Import Modal

• Download CSV Template: Generates an instant, pre-formatted spreadsheet template populated with header columns and sample products, services, and multi-tier packaging formats.
• Target Branch (for Opening Stock): Specifies which branch receives the opening inventory quantities and financial asset valuations.
• Apply Pricing Tiers Across All Active Branches:
  • When checked, the pricing tiers configured in the spreadsheet are replicated across all active branches in your organization.
  • When unchecked, pricing tiers are assigned only to the selected Target Branch.
• Duplicate Handling Rule:
  • Skip duplicates (Keep existing records untouched): Preserves existing items in the database without modification; only new items are created.
  • Update duplicates (Overwrite existing item properties): Updates pricing, active status, and attributes of existing catalog items matching by SKU, Barcode, or Item Name.
• Drag-and-Drop Dropzone: Supports Microsoft Excel (.xlsx, .xls) and Comma-Separated Values (.csv).

3. Spreadsheet Column Reference & Specifications

The import engine reads 18 standard columns. The first row must contain column headers.

4. The Principal Multiple Pricing Tiers Cost Price Rule

When selling goods that are packaged in multiple units (e.g., selling by the Carton, Box, or individual Piece), you must understand how Qotrack calculates inventory value, Cost of Goods Sold (COGS), and gross margins.

The Underlying Mathematical Principle

In Qotrack, every inventory item is tracked in terms of its atomic base unit (dimension = 1). When you define packaging tiers with dimensions greater than 1 (e.g., a Carton containing 24 pieces has dim = 24), the system determines the cost of an individual unit behind the scenes using:

Unit Cost per Base Piece = Cost Price / Maximum Dimension (max_dim)

Because the system uses the maximum packaging dimension as the divisor, the Cost Price field MUST represent the acquisition cost of the HIGHEST packaging unit, never the single piece!

The Golden Rule of Multiple Pricing Tiers

For any item with multiple packaging tiers where max_dim > 1:

Smallest Tier Rate (Piece Price) < Cost Price < Highest Tier Rate (Carton Price)

1. Upper Bound Constraint (Cost Price < Highest Tier Rate): The Cost Price must be lower than the selling price of the highest tier. If Cost Price >= Highest Tier Rate, selling the carton produces a negative or zero gross profit margin.
2. Lower Bound Constraint (Cost Price > Smallest Tier Rate): The Cost Price cannot be lower than or equal to the selling price of the single piece. Entering a single piece cost into an item profile that has a carton tier causes the system to divide the piece price by the carton dimension, producing distorted, astronomical margins and severely understated inventory valuations.

5. Right vs. Wrong Examples: Detailed Financial Impact

Let us examine a real-world scenario of an item sold both by the Carton of 24 and by the Individual Piece.

• Packaging Tier 1 (Carton): Label = ctn, Dimension = 24, Selling Rate = GH₵ 36.00
• Packaging Tier 2 (Piece): Label = pc, Dimension = 1, Selling Rate = GH₵ 2.00

Scenario A: The Correct Configuration (Cost Price = GH₵ 24.00)

Here, the merchant inputs the true purchase cost of 1 Carton (GH₵ 24.00).

• Behind-the-Scenes Calculation: Unit Cost per Piece = GH₵ 24.00 / 24 = GH₵ 1.00

• Financial Outcome when selling 1 Carton (ctn):

  • Selling Revenue: GH₵ 36.00
  • Cost of Goods Sold (COGS): GH₵ 1.00 × 24 = GH₵ 24.00
  • Gross Profit: GH₵ 36.00 - GH₵ 24.00 = GH₵ 12.00
  • Gross Margin: 33.33% (Accurate, realistic commercial profit)

• Financial Outcome when selling 1 Piece (pc):

  • Selling Revenue: GH₵ 2.00
  • Cost of Goods Sold (COGS): GH₵ 1.00 × 1 = GH₵ 1.00
  • Gross Profit: GH₵ 2.00 - GH₵ 1.00 = GH₵ 1.00
  • Gross Margin: 50.00% (Accurate, realistic retail markup)

Scenario B: The Wrong Configuration (Cost Price = GH₵ 1.00)

In this erroneous case, the merchant mistakenly typed the cost of 1 piece (GH₵ 1.00) into the Cost Price field, while configuring a carton tier of 24.

• Behind-the-Scenes Calculation: Unit Cost per Piece = GH₵ 1.00 / 24 = GH₵ 0.04167

• Financial Distortion when selling 1 Carton (ctn):

  • Selling Revenue: GH₵ 36.00
  • System-Calculated COGS: GH₵ 0.04167 × 24 = GH₵ 1.00!
  • Distorted Gross Profit: GH₵ 36.00 - GH₵ 1.00 = GH₵ 35.00!
  • Distorted Gross Margin: 3,500.00% (Outrageous, distorted margin that ruins P&L reports!)

• Financial Distortion when selling 1 Piece (pc):

  • Selling Revenue: GH₵ 2.00
  • System-Calculated COGS: GH₵ 0.04167
  • Distorted Gross Profit: GH₵ 2.00 - GH₵ 0.04 = GH₵ 1.96 (98% margin instead of true 50%)

• Severe Balance Sheet Impact: If the business holds 100 cartons (2,400 pieces):

  • True Inventory Value: 100 × GH₵ 24.00 = GH₵ 2,400.00
  • Erroneous Value in Books: 2,400 × GH₵ 0.04167 = GH₵ 100.00
  • Balance Sheet Understated by GH₵ 2,300.00!

Scenario C: Selling Below Cost (Cost Price = GH₵ 40.00)

If the merchant inputs a Cost Price of GH₵ 40.00 while selling the carton at GH₵ 36.00:

• Selling Revenue: GH₵ 36.00
• COGS: GH₵ 40.00
• Gross Loss: -GH₵ 4.00 (-11.11% margin)
• The system blocks this setup to prevent unintentional loss-making operations.

6. Formatting the "Pricing Tiers" Column in Spreadsheets

In the spreadsheet template, additional pricing tiers are defined in the Pricing Tiers column using a semicolon-delimited syntax:

Label:Dimension:Rate; Label:Dimension:Rate

• Label: Name of the tier (e.g. Box, Carton, Pack, Wholesale).
• Dimension: Number of base pieces contained in this tier.
• Rate: Selling price for this specific tier.

Practical Examples for the Template

1. Pharmaceutical Product (Pills / Strips / Boxes / Cartons):

   • Base Unit: strip (Selling Price = 5.00)
   • Cost Price: 250.00 (Cost to purchase 1 Carton containing 100 strips)
   • Pricing Tiers: Box:10:45; Carton:100:400
   • Meaning: 1 Box has 10 strips for GH₵ 45.00; 1 Carton has 100 strips for GH₵ 400.00.

2. Beverages / Soft Drinks:

   • Base Unit: bottle (Selling Price = 3.50)
   • Cost Price: 60.00 (Cost to purchase 1 Crate of 24 bottles)
   • Pricing Tiers: Pack:6:19.50; Crate:24:72.00

3. Electronics & Accessories:

   • Base Unit: pc (Selling Price = 18.00)
   • Cost Price: 140.00 (Cost to purchase 1 Wholesale Pack of 10)
   • Pricing Tiers: Pack:10:160.00

7. Built-in Validation & Error Guards

To guarantee that your inventory balances and profit metrics remain pristine, Qotrack enforces validations at multiple levels:

1. Real-Time Pre-Import Analysis

Upon uploading your spreadsheet, the importer analyzes every row before touching the database:

• Identifies rows where packaging units exist (max_dim > 1) and verifies that smallest_tier_rate < cost_price < highest_tier_rate.
• If an error is found:
  • A prominent red alert banner appears at the top indicating the number of offending rows.
  • The offending row displays a red Error badge with an explanatory tooltip.
  • The Cost Price cell is highlighted in bold red with a warning icon.
  • Clicking Import Items is blocked and launches an instructional dialog explaining how to correct the Cost Price.

2. Server-Side Bulk Import Verification

If an invalid row is submitted, the backend processing engine catches it during the database transaction:

• The offending row is safely skipped.
• The system returns an itemized audit log detailing the exact row number, item name, and price violation.

3. Manual Form Validation

When creating or editing items through the manual item modal (Inventory > Items > Add / Edit Item):

• The form's client-side validator inspects each branch tab.
• If any branch has a packaging tier with an invalid Cost Price, submission is halted and a notification alert directs the user to fix the pricing ratio.

8. Duplicate Detection & Resolution Guidelines

When importing inventory spreadsheets, Qotrack matches uploaded rows against your existing catalog using three criteria:

1. SKU Match (case-insensitive exact match)
2. Barcode Match (searches all individual barcodes in comma-delimited lists)
3. Item Name Match (case-insensitive normalized title)

Choosing Your Duplicate Rule

[!TIP] Safety Prompt: If the uploaded spreadsheet contains items that already exist in your system, Qotrack displays an interactive amber dialog summarizing the exact duplicate count and asking you to explicitly confirm whether you wish to Skip or Update before committing changes.