按集体合同生成薪酬工作簿

A more faithfully implements the CBA in its weekly calculations and checks. It flags the sample’s 9:30 rehearsal without exigent-circumstances confirmation, includes the synth substitute’s 25% electronic premium on rehearsal and sound-check earnings, and checks audits against two per book. B defaults that premium to “Shows only,” omitting the all-work premium on those earnings. B’s visible checks also never reconcile audit counts to books learned, while its musician-level CBA status reads payroll-register status without incorporating underlying roster, service or premium-override conflicts. Both provide editable dated rates and separate per-person payroll categories. A nevertheless falls short of the requested configuration robustness: Setup!B20’s Auto quartet rule merely tests for four weekly violin/viola/cello chairs. It can therefore award an unflagged quartet premium when an additional acoustic bass is present, contrary to the requirement that the quartet be the entire acoustic string section.

B provides the stronger weekly payroll submission: Payroll Summary totals the contractual categories by musician, Calc Detail includes rehearsal and sound-check earnings in the synthesizer substitute’s 25% electronic premium, and Roster column S checks the required two audits per book. A defaults to “Shows only” for that premium (Settings C22), excluding non-performance electronic work despite Article 4.2f.i’s “all work” provision. A’s visible audit checks do not reconcile total audits to books learned, and its first-local premium selection does not exclude inactive roster entries. Neither fully satisfies the requested robustness and conflict detection. B’s Auto quartet calculation merely counts four weekly violin/viola/cello chairs, without checking that they constitute the entire acoustic string section. Its sound-check formulas also lack an overlap check for the stand-alone requirement in Article 7.10. These are substantive gaps, but B’s core weekly calculations and audit-entitlement checks are stronger.

Both workbooks meet the core brief. Each has a dated rate table so contract years can be updated, roster and schedule inputs, CBA checks, and per-musician totals by category (performances, rehearsals, sound checks, audits, premiums, doubling, vacation, gross) with an Art. 11.4 stub. B has the broader scope: a 52-week register, Run Totals, a Compliance register, an Assumptions tab and policy switches. On correctness, consistency and usability, A is better. (1) Conflict flagging: B's audit check (Services AT) multiplies a 1×40 Coverage row by a 40×1 Roster column inside SUMPRODUCT. This builds a 40×40 array, so the count never equals 1. The render shows 'An audit service must be assigned to exactly one musician' on both sample audits while the status says OK. In Excel this would read as an ERROR that the contractor cannot clear. B's Assumptions tab also promises an audits-vs-books-learned check that is not implemented. A implements that check (CONFLICT if fewer than 2×books). (2) Sample rehearsal: the 9:30 AM rehearsal is a CONFLICT in A, with a red input cell and an 'Exigent circumstances' Y/N column to document an exception. B only issues a WARNING and offers no way to document exigency, so the warning can never clear. (3) CBA-faithful defaults: A applies the synth sub's 25% electronic premium to rehearsal and sound-check work as well as shows (Art. 4.2f.i 'all work'). It also pays the weekly guarantee of $2,008.50 for 8 shows, with rehearsals paid on top. B's defaults apply the 25% to shows only. B also counts rehearsal and sound-check pay toward the weekly guarantee, so 8 shows pay $2,008.48 and short rehearsal weeks would be underpaid. (4) Fidelity to the sample and usability: A records the sub's two audits against performances via simple X/A attendance marks. B invents two separate Monday audit services that require 'N' entries across 39 coverage columns. B's rate input table also has number formats shifted by about three columns, e.g. Vacation % shows $0.06, minimum call shows 300.00%, and the synth premium shows 0. B also has tall wrapped note rows in the rendered pages. A's rendered sheets are clean, and it adds reconciliation checks and a CBA dashboard. B handles multi-week runs within one file, whereas A uses a save-a-copy-per-week workflow. That is still adequate for a weekly submission tool.

Both workbooks cover the core task. Each has a date-keyed rate table that can be updated each year, a 40-musician roster, a service schedule, CBA flags, per-person totals by category (performances, rehearsals, sound checks, audits, premiums, doubling, vacation, gross) and an Art. 11.4 pay stub. A is broader, with a 52-week payroll register, run totals, a compliance register, policy switches and automatic first-local detection. Its weekly gross of $30,665.27 reconciles with its own formulas. A has defects in correctness and flagging: - **Audit check bug:** the check (Services col AT) multiplies a horizontal Coverage row by vertical Roster columns. Every audit therefore evaluates to "must be assigned to exactly one musician". The rendered Services sheet shows that message on the sample's valid audits S001/S002 next to an "OK" status. On a full Excel recalc these rows would become ERROR and the cover would read NOT READY. - **Early rehearsal:** the 9:30 a.m. rehearsal is only a WARNING, and there is no input to document exigent circumstances. - **Synth sub underpaid by default:** the "Shows only" setting omits the Art. 4.2f.i 25% "all work" premium on the sub's rehearsal and sound check, giving a sub gross of $1,367.72. - **Guarantee shortfall:** regular players are paid 8×$251.06 = $2,008.48 instead of the $2,008.50 weekly guarantee. - **Weight and layout:** the file is very heavy (about 48K register formulas, 12K service formulas), and the render shows cluttered OK flags on empty rows and tall wrapped-text rows. B's Calc Detail formulas pay the $2,008.50 guarantee. For the synth sub they compute the lesser of 150%×base×shows or the guarantee, plus 25% of base per show and 25% on rehearsal and sound check. Audits are 2×base per book, doubling is 25%+10% per extra double, and chair premiums use a show-has-trumpet input so the horn gets 15%. The string quartet is correctly not applied (3 strings). B flags the 9:30 rehearsal as a CONFLICT, colours the actual Time-in input cell red, and lets an exigent Y/N input clear it. It also has reconciliation checks, a CBA Checks dashboard and a clean, contractor-friendly layout. Its weaknesses are one workbook per week and manual first-local designation, both documented and validated. On correctness and usability, which matter most here, B is stronger.