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

A submitted no deliverable. B supplies an Excel workbook with centralized yearly rates, roster/schedule/attendance inputs, formula-driven totals by musician and pay category, and CBA warnings. However, B has material contract errors: it treats the French-horn premium as nonpayable when trumpet is present, despite the independent entitlement in Art. 4.2.c; its rehearsal paid-hours formula always applies the three-hour minimum without implementing the Art. 7.4 split-rehearsal waiver; and its sound-check frequency check considers only the current week, not the engagement or eight-week interval. B is clearly the better submission, but requires corrections before reliable payroll use.

A provides a formula-based Excel workbook with editable yearly rates, roster/schedule/attendance inputs, compliance checks, and separate per-musician payroll-category totals. B submitted no deliverable. However, A has material CBA gaps: Schedule applies a three-hour minimum to every rehearsal without implementing Article 7.4’s split-rehearsal waiver; sound-check frequency checks cover only the current week rather than the engagement/eight-week limits; and Payroll column N subtracts rehearsal and sound-check earnings when calculating the synthesizer substitute’s per-show minimum, allowing those separately payable services to eliminate the show-pay top-up. Thus A is clearly better, but not fully compliant or robust as requested.

Submission A provided no files, so it does not complete the task. Submission B provides a working Excel model that covers the requested functions. The Rates sheet holds a rate table keyed by year with a payroll-year selector, plus a separate block of CBA percentages and limits, so contract rates can be updated each year. The Roster, Schedule and Attendance sheets allow up to 100 chairs and 50 services, which supports different orchestra configurations and production runs. Data-validation dropdowns cover the main inputs. Conflicts are checked against the CBA in Roster column Q, Schedule column M and a 22-rule CBA Checks sheet that labels each rule OK, REVIEW or VIOLATION. The Schedule render shows the 9:30 a.m. rehearsal highlighted and flagged under Art. 7.3. The Payroll sheet shows, for each person, performance, rehearsal, sound check, audit, premium and doubling pay, a substitute minimum, a guarantee top-up, 5.5% vacation pay and a total, with column totals. These match the Art. 11.4 breakouts. Several calculations match the CBA and the sample data: the rehearsal pays 5 × $55.67 = $278.35, the 1-hour sound check pays $76.59, woodwind doubling is 25% + 10% + 10% = 45%, no horn premium is paid because the show has a trumpet, the strings are correctly treated as not a quartet, and the substitute receives 2 audits at base scale. There are weaknesses. The Art. 4.2.f.ii substitute top-up is compared against earnings that include rehearsal and sound check pay, which may underpay the substitute's per-show rate. On the Read Me sheet, the instruction text is squeezed into a narrow column. Even so, B is a usable deliverable and A is empty.

Submission B contains no deliverable, so it cannot meet any part of the task. Submission A is a working, formula-driven Excel workbook with seven sheets: Read Me, Rates, Roster, Schedule, Attendance, Payroll and CBA Checks. It covers each stated requirement. (1) Year-to-year updates: a rate table keyed by payroll year feeds every calculation through INDEX/MATCH and named ranges, and the CBA percentages and limits sit in a separate editable parameters block. (2) Any orchestra or production run: there are 100 roster chairs, 50 schedule services and a P/A attendance grid with drop-down validation. The sample roster is entered correctly, including doubles of 2/2/2/4 instruments (woodwind computes to 45%), concertmaster and lead trumpet at 20%, the synth sub at 25% electronic, and no horn premium because a trumpet is present. (3) Conflict highlighting: per-row check columns on Roster and Schedule feed 22 OK/REVIEW/VIOLATION rules on CBA Checks. The rendered Schedule shows the 9:30 a.m. rehearsal shaded and flagged under Art. 7.3. Computed pay also renders: a 5-hour rehearsal at $278.35, a 1-hour sound check at $76.59 and performances at $251.06. (4) Per-person totals: the Payroll sheet breaks out performance, rehearsal, sound check, audit, premiums, doubling, sub minimum, guarantee top-up, 5.5% vacation and a weekly total, with column sums. Weaknesses in A: (a) Art. 4.2.f.ii is applied as MIN(150% per show, guarantee + 25%) and netted against all non-audit earnings, so the sample sub's 150% show rate is effectively absorbed and underpaid. (b) The weekly guarantee is compared against total earnings including rehearsals and premiums, which is a debatable reading. (c) A 20% designation on non-trumpet/violin/horn instruments is not flagged. (d) Read Me text overlaps in a narrow column. These are accuracy and polish issues within a substantially complete tool, whereas B delivered nothing.