Open Sales Order Backlog Aging Worksheet
Customer service gets asked for the backlog report every week. This worksheet lists open orders and shows how much of the backlog is past due and how badly, ready to export to Excel.
| Sales order / line | Customer | Part number | Status | Promise date | Days past due | Open value | Late value | Over 30 days late | |
|---|---|---|---|---|---|---|---|---|---|
$0 | $0 | ||||||||
$9,400 | $0 | ||||||||
$26,500 | $26,500 | ||||||||
$6,200 | $0 | ||||||||
$0 | $0 |
Paste a past-due export from your ERP (SAP VA05, Epicor open order report, SyteLine order inquiry, or a NetSuite saved search) and fill days past due as today minus promise date. Sort by days past due in Excel after export.
Get this report straight from your ERP
Optional: we can show how to produce a live past-due report without manual exports.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
Add one row per open order line with the customer, part, status, promise date and open value. Enter days past due as today minus the promise date, using zero or a negative number for lines not yet due. The sheet calculates the late value and the value more than 30 days late for each row, then totals them and shows the share of backlog that is past due. Export to Excel and sort by days late to drive the daily call list.
- Use promise date, not the original customer request date, for aging
- Update status so you can see why orders are late
- Review the over-30-day column first; those orders need escalation
- Run the same layout each week to see the trend
What the numbers tell you
Total backlog shows the work in hand. Past due value shows the work that is already failing a commitment. Share past due is the simplest indicator of delivery health: a rising percentage means capacity, material or planning is not keeping up with promises. Grouping late value by status separates internal causes, such as waiting for material, from customer-caused delays such as awaiting drawings.
Getting the data from your ERP
Most ERPs provide an open order or order line report. In SAP, VA05 lists sales orders and schedule lines; Epicor, SyteLine, NetSuite and Dynamics have equivalent open order inquiries or saved searches. Export the line-level data with promise date and open value, not invoice-level data, so partial shipments show the remaining quantity only.
Common mistakes
Frequent mistakes include aging from the customer request date and hiding slipped promise dates, valuing full order value instead of the open balance, and counting orders on customer hold as late. Be consistent about what counts as late and record reasons so the report drives action rather than just a number.
Frequently Asked Questions
What is a sales order backlog report?
A list of orders received but not yet fully shipped or invoiced, usually with value, promise date and status. It shows workload and, when aged, which commitments are at risk or already missed.
How do I calculate days past due?
Subtract the promise date from today's date. A positive result means the line is past due, zero or negative means it is not yet due. Use the current promise date, and keep original dates separately if you want to measure slips.
Should orders on customer hold count as late?
Usually they are shown separately, since the delay is outside your control. Mark them with a status such as Waiting customer so they can be filtered out of internal performance measures while still being visible.
How often should the backlog report be reviewed?
Customer service typically reviews it daily for late orders and weekly with sales and operations for trends. Monthly, compare share past due with on-time delivery performance to see whether promises are realistic.
If this report takes hours to assemble each week, Netray can help automate it from your ERP data so it is current whenever someone asks, read-only.
Related Tools
Weekly Backlog and Bookings Report Worksheet
Enter opening backlog, bookings and shipments by week and get closing backlog, book-to-bill and weeks of backlog.
Discrete ManufacturingOrder Backlog Coverage and Book-to-Bill Calculator
Convert backlog into weeks of coverage, compute book-to-bill and project where the backlog goes if orders and shipments stay on trend.
Supply Chain & InventoryOn-Time Delivery Calculator
Enter shipped lines, on-time lines and in-full lines to get OTD, OTIF, late and early rates and the gap to your target.
Discrete ManufacturingCustomer Lead Time Quote Calculator
Estimate the lead time to quote a customer from queue load, setup, run time, material wait, outside processing, transit and a safety buffer.
Supply Chain & InventoryBackorder Customer Notification Letter Template
A clear letter that tells a customer which lines are backordered, the revised ship date, and what they can choose to do.
More Supply Chain & Inventory tools
See all- Oracle Fusion Inventory Turns and Carrying Cost Calculator
- Distribution ERP Fill Rate Calculator
- Distributor Rebate Tracking Worksheet
- Branch Transfer Planning Worksheet
- Distributor Slow-Moving Inventory Review Worksheet
- Distributor Vendor Performance Scorecard Worksheet
- Distributor GMROI Calculator
- Distributor Backorder Cost and Fill Rate Calculator
- EOQ with Vendor Minimum Order Calculator
Keep exploring
Free tools
- ECO Backlog Aging WorksheetList open ECOs with stage, owner, age and target days, and see overdue days and engineering hours at a glance for the weekly change board.
- Excel Open Order Aging and Late Line Formula GeneratorTell it which columns hold your due date, quantities and ship date, and it writes the Excel formulas for days past due, aging buckets, open quantity and OTD flags.
- SQL Open Purchase Orders Query TemplateA configurable open PO line query with open quantity, open value and days past due, ready to adapt to any ERP schema.
ERP fixes and how-tos
- Baan Open Order Report: Sales and Purchase Sessions, Filters and TablesHow to get a Baan open order report for sales and purchase orders: which sessions to use, how to filter open lines, and which tables to query or export.
- Epicor Kinetic Dashboard Examples (Shop Floor, Sales, Purchasing) and How to Add FiltersPractical Epicor Kinetic dashboard examples for shop floor, sales and purchasing, with the tables to query and how to add filters and parameters.
- How to Release a Customer Order from Credit Hold in SyteLineFind why a SyteLine customer order is on credit hold, see who is authorized to release it, and set credit limits and days-past-due rules so it stops recurring.
Guides
- The SyteLine Open Orders Report: Why Yours Disagrees and How to Fix ItWhy open orders is the most contested number in SyteLine, the definitional decisions a correct report must make, and how semantic views end the dueling-reports problem.
- How to Manage Inventory Aging in SyteLineStep-by-step guide to managing inventory aging in Infor SyteLine CloudSuite Industrial, including aging reports, obsolescence reserves, and disposition workflows.
AI on your ERP
- AI for NetSuite Order to Cash: Find Where Every Order and Dollar Is StuckAI for the NetSuite order to cash cycle: sales orders, item fulfillment, invoicing and collections. Ask where an order is stuck and why cash is late.
- AI for SyteLine Purchasing: Open POs, Late Vendors and Planned Order ReleaseAI for Infor SyteLine purchasing: answer PO, vendor lead time and late-receipt questions from live data, draft expedite notes, and flag planned orders worth releasing.