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

A submitted no deliverable. B supplies a substantive Excel model with the correct 2025 rates, effective-date rate updates, linked roster/schedule/attendance inputs, and per-person payroll-category totals. Its rendered schedule visibly flags the sample’s 9:30 AM rehearsal. However, B does not fully satisfy the robustness and compliance requirements: its automatic quartet test merely counts four weekly violin/viola/cello rows, without verifying one-on-a-part seating or that they constitute the entire acoustic string section. Its meal-break formula can also miss more than five rehearsal hours spread across three calls separated by breaks shorter than 30 minutes. B is clearly more useful than A, but its compliance checks are not reliable for all requested configurations.

A provides a formula-driven Excel payroll workbook with editable effective-dated rates, roster/schedule/attendance inputs, and per-person totals for performances, rehearsals, sound checks, audits, premiums, doubling, vacation and gross pay. It includes CBA compliance checks and visibly flags the sample 9:30 a.m. rehearsal. These substantively support reusable weekly payroll submission. B submitted no deliverable files.

Submission A has no deliverable files, so it provides no payroll model, CBA checks or per-person totals. Submission B is a formula-driven Excel workbook loaded with the sample roster and schedule. Its Rates sheet selects rates by effective date through SUMIFS, has spare rows for future contract years, and keeps CBA percentages and rules in editable parameter cells. Input capacity is 40 musicians and 30 services per week, with an editable instrument list, which supports other orchestra configurations and production runs. CBA conflict flags appear on the Schedule and Roster sheets and roll up to a CBA Checks dashboard. They cover Art. 7 start times, minimum call, meal breaks and sound checks, plus Art. 4 premium designations, synth-sub pay and audits. The 9:30 AM sample rehearsal renders in red with a CONFLICT message citing Art. 7.3. The Payroll Summary formulas give totals by musician for performances, rehearsals, sound checks, audits, premiums, doubling, vacation and gross pay, with reconciliation checks and a Pay Stub. Weaknesses are minor: some assumptions are interpretive, such as treating the guarantee as covering 8 shows and using a switch for how doubling applies, and rendered times omit AM/PM. These do not change the outcome, because B is usable and A is absent.

Submission B submitted no deliverable, so it cannot meet the task. Submission A is a working, formula-driven Excel model that covers each requirement. 1. **Annual rate updates:** A Rates sheet holds the Art. 4.1 2025 rates in an effective-dated table with blank rows for future years. The model picks the correct row from the payroll week-ending date. All percentages are editable parameters, including premiums, doubling, vacation and the synth-sub rules. 2. **Any orchestra or production run:** It handles up to 40 roster rows and 30 service rows. An editable instrument list drives premium eligibility. Engagement dates feed the sound-check frequency logic. 3. **CBA conflict flags:** - The Schedule sheet checks Art. 7.1–7.5 and 7.10. - The Roster sheet checks first-local, concertmaster and lead premiums, the electronic premium, synth-sub pay basis and audits. - A CBA Checks dashboard rolls these up. - The sample 9:30 AM rehearsal is correctly flagged in red as a CONFLICT under Art. 7.3, which is visible in the rendered Schedule page. 4. **Totals by person and category:** The Payroll Summary shows each musician's performances, rehearsals, sound checks, substitute audits, premiums, doubling, vacation and total gross, with reconciliation checks. A Pay Stub sheet follows the Art. 11.4 breakouts. 5. **Sample logic matches the CBA on the visible evidence:** - Three strings, so no quartet premium. - Horn gets 15% under 4.2c because the show has a trumpet. - Only one first-local guitar premium. - Woodwind gets three doubles at 25% + 10% + 10%. - The synth sub is paid the lesser of 150% of base or the weekly guarantee plus the 25% premium, with two audits. No cached formula errors were reported. The only blemish is that repeated print titles mislabel headers on later Rates pages; this does not affect use.