USING A QUADRATIC PROGRAMMING APPROACH TO SOLVE SIMULTANEOUS RATIO AND BALANCE EDIT PROBLEMS

1. USING A QUADRATIC PROGRAMMING APPROACH TO SOLVE SIMULTANEOUS RATIO AND BALANCE EDIT PROBLEMS Katherine J. Thompson James T. Fagan Brandy L. Yarbrough Donna L. Hambric

2. Economic Census Editing • Ratio edits of basic data items 1 Annual Payroll/1st Quarter Payroll  4.4 30  Annual Payroll/Employment  70 • Balance edits to ensure additivity Annual Payroll = 1st Quarter Payroll + 2nd Quarter Payroll + 3rd Quarter Payroll + 4th Quarter Payroll • Items that appear in both ratio and balance edits may fail to satisfy the original ratio edit tests.

3. Addressing the Problem • Often not a problem • Motivated two Economic Census Applications: • Assets Complex (Manufactures Sector – 1997) • Gross Margin/Gross Profit (Wholesale Trade – 2002) • Quadratic programs replaced manual or existing systems

4. General Set Up (Motivating Example) • Five data items: A, B, C, D, and E. • Two ratio relationships (edits): • LAB A/B  UAB • LCD C/D  UCD • One balance edit: A + C +D = E • Non-negativity edits on all five items • Objective: Minimize change from input to output data while satisfying the ratio and balance constraints

5. Quadratic Program min [(A – A*)2 + (B – B*)2 + (C – C*)2 + (D – D*)2 + (E – E*)2 ] subject to (s.t.) A* + C* + D* – E* = 0 Balance constraint LAB A/B  UAB Ratio edit constraints LCD C/D  UCD A*, B*, C*, D*, E*  0 Non-negativity constraints [Note: an asterisk denotes the output value from the quadratic program solution.]

6. Special Considerations • Not all items equally reliably reported • Preference for adjusting imputed values before (retained) reported values • Solution: item-specific reliability weight example 1:A = 10 for data item A B = 100 for data item B (B more reliably reported than A) example 2:

7. Revised Quadratic Program Min [A(A – A*)2 + B(B – B*)2 + C(C – C*)2 + D(D – D*)2 + E(E – E*)2 ] s.t. same constraints (ratio, balance, non-negativity) Comments • Minimizes residual sums of squares (Deming, 1943) • least squares estimation solution • Data item Yi= E(Yi)+ i and i  /i • We used subjectively-defined reliability weights • Non-integer solutions (controlled rounding)

8. Other Modifications • “Fixing” variables • drop squared term from objective function • include values in constraint as constants • No limit (found yet) on number of allowable constraints • remove redundant edits from program for speed

9. Assets Complex (Manufactures) • Two dimensional balancing constraints: • Row Constraints: TOTAL item = the BUILDING item + the MACHINERY item • Column constraints: value of d = a + b - c. • Ratio constraints on three marginal row totals: Ending Assets, Capital Expenditures, • and Retirements.

10. Production Application • Ratio edits on marginal totals performed separately (first) • Quadratic program on all data items • original ratio constraints • additional ratio constraints on some details to associated marginal totals • all balance constraints • Same reliability weights for all items

11. Gross Margin/Gross Profit (Wholesale) • Two Derived Items: Gross Margin = Sales – Gross Selling Value – Beginning Inventories – Purchases + Ending Inventories GrossProfit = Gross Margin – Operating Expenses + Commissions • Payroll, Employment, Receipts, Operating Expenses, Purchases, and Inventories collected from all establishments (ratio edited) • Commissions and Gross Selling Value collected when available (no pre-editing)

12. Goals • Replace manual edit system with automated edit • Retain analyst preferences used in manual system • Include all ratio and balance constraints in minimization problem (not feasible with manual system)

13. First Attempt • Included all eight data items plus two derived items in objective function • Included all ratio and balance constraints • Two different item reliability weights for each item (one for reported value; one for imputed value)

14. Results • Too many items changed by a “small” amount • One-pass approach not implementing analyst procedure • If Gross Margin failed its ratio test, then wanted to first adjust values of Purchases, then Sales. • If after correcting Gross Margin, Gross Profit still failed its ratio test, wanted to adjust values of Operating Expenses, then Purchases, and lastly Sales.

15. Production Program: Second Attempt • Split into two separate quadratic equations • Gross margin and associated constraints • Gross profit and associated constraints • Different reliability weights for each item in each program • Dropped distinction between reported and imputed values of same item • Eliminated Gross Selling Value, Commissions, and Inventories Items from objective functions (treated as constants in constraints)

16. Comments • In both applications, quadratic program did not find solutions for all records • generally very intractable (poorly reported) data • Very disparate values of reliability weights needed to control outcome (Wholesale weights different by a factors of 106 and 109) • Solution as good as constraints

17. Comments • Objective different from “traditional” balance edit objective • does not try to preserve distribution of details (e.g., raking) • can adjust certain item values by large amount without using statistically based model • No generalized programs...yet