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