USING A QUADRATIC PROGRAMMING APPROACH TO SOLVE SIMULTANEOUS

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 1

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

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 3

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 4

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 LAB A/B UAB Balance constraint 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. ] 5

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: 6

Revised Quadratic Program Min [ A(A – A*)2 + B(B – B*)2 + C(C – C*)2 + (D – D*)2 + (E – E*)2 ] D E 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) 7

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 8

Assets Complex (Manufactures) Row Description Total Building Machinery a Beginning year assets TAB BAB MAB b Capital expenditures TCE BCE MCE c Retirements TRT BRT MRT d Ending year assets TAE BAE MAE • 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. 9

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 10

Gross Margin/Gross Profit (Wholesale) • Two Derived Items: Gross Margin = Sales – Gross Selling Value – Beginning Inventories – Purchases + Ending Inventories Gross Profit = 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) 11

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) 12

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) 13

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. 14

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) 15

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 16

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 17