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

A provides a formula-driven Excel workbook with linked schedule and attendance inputs, editable rates, separate per-person payroll-category totals, and CBA checks—including the sample’s unexcused 9:30 a.m. rehearsal. B submitted no deliverable. A nevertheless falls short of full correctness and robustness: its calculation ranges are fixed at 30 musicians and 24 weekly services, and it forces all electronic-instrument substitutes into the special Synth Sub pay rule, although the CBA limits that provision to synthesizer substitutes.

A submitted no deliverable. B provides a formula-driven Excel payroll workbook using the supplied CBA rates, with attendance entry, separate per-person payroll categories, and compliance checks—including flagging the sample 9:30 a.m. rehearsal without confirmed exigent circumstances. However, B does not fully meet the robustness and correctness requirements: its calculation ranges are fixed at 30 musicians and 24 weekly services; it continues using week-start rates when a rate period ends midweek; and its roster checks force all electronic-instrument substitutes into the special Synth Sub rule, although Article 4.2.f.ii specifically addresses synthesizer substitutions. B is substantially more useful than A, but still needs correction before dependable general use.

Submission B delivered no file, so it fails the task outright. Submission A is a complete, formula-driven Excel workbook built for the contractor's purpose. It has seven sheets: Instructions, Rates, Instruments, Schedule, Roster, Payroll, CBA Compliance and Pay Stub. The file checks show no cached errors. **Rate updates:** The Rates sheet stores dated rate periods (2025 row: $251.06, $2008.50, $55.67, $76.59, $137.87). It selects the right period with SUMIFS on the payroll week date. Premium, doubling and vacation percentages are named parameters, so year-to-year changes are easy. **Any orchestra or run:** It handles 30 musicians and 24 services per week. The Instruments list is extendable and maps each instrument to a premium group. **Conflict highlighting:** The Schedule checks Art. 7.1–7.5 and 7.10 rules: 9am and 10am start limits, the 6:30pm end, the 5-hour meal break, the 3-hour minimum call with the 7.4 waiver, and sound check length, window and frequency. The sample's 9:30am rehearsal is correctly shown in red as a 7.3 conflict. The Roster checks duplicate or missing first-local designations, synth-sub status mismatches and audit allowance under 4.5.b. A summary compliance sheet totals all of these. **Per-person category totals:** The Payroll sheet breaks out performance, rehearsal, sound check, audit, premium, doubling, gross, vacation (5.5%) and total for each musician, with a reconciliation check. **Sample calculations check out:** - 5-hour rehearsal = $278.35. - 1-hour sound check = $76.59. - 8 shows = weekly guarantee. - Synth sub gets MIN(150% × base, guarantee) plus 25%, and 2 audits at base. - Violin concertmaster and lead trumpet get 20%; viola, cello, bass, designated guitar, woodwind and horn get 15%. - No quartet premium, since only 3 strings are on the roster. - Woodwind with 4 instruments gets 25% + 10% + 10% doubling. Ambiguous points are documented and can be toggled on the Rates sheet. The only flaw seen is cosmetic: Roster time headers display as 9:29 and 7:59.

Submission A provided no files, so it cannot meet any part of the task. Submission B provides a working Excel workbook that covers the brief. - **Rate updates:** A dated Rates table (rows 5-14) is looked up by the payroll week date. Premium, doubling, vacation and work-rule parameters sit in separate named cells, so yearly changes need no formula edits. - **Any orchestra or run:** It allows up to 30 musicians and 24 services, and the Instruments list can be extended with premium groups. Status is Regular, Substitute or Synth Sub, with chair designations and a count of instruments played for doubles. - **Conflict highlighting:** The Schedule flags each service against Art. 7.1-7.5 and 7.10. The rendered page shows the sample 9:30 a.m. rehearsal correctly flagged red as an Art. 7.3 conflict. The Roster checks duplicate or missing first-local designations, synth-sub status mismatches and audit limits. A CBA Compliance summary blocks the 'ready to submit' status until conflicts are cleared. - **Totals by person per category:** The Payroll sheet gives performance, rehearsal, sound check, audit, premium, doubling, gross, 5.5% vacation and total pay per person, plus a reconciliation check. These match the Art. 11.4 breakout. - **Spot-checked calculations look right:** - Rehearsal: 5 hrs × $55.67 = $278.35. - Sound check: $76.59. - Eight shows pay the weekly guarantee. - The synth sub gets the lesser of 150% of base per show or the guarantee, plus 2 audits at base. - Concertmaster and first trumpet get 20%; other first locals get 15%. - The string-quartet premium is correctly off with only 3 regular strings. - Woodwind doubles total 25% + 10% + 10%. - No cached Excel errors. - **Minor issues:** - Some labels are truncated on Schedule. - Times show without AM/PM. - The Roster render shows 9:29 and 7:59 time artifacts. - Several interpretive assumptions are made, but they are documented and toggleable.