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

B provides the more usable payroll model: linked roster, schedule and attendance formulas, centralized editable rates, and per-person totals separating the contract’s payroll categories. A has serious formula defects: its defined names omit the sheet-reference separator (for example, 'CBA Rates'$C$20), its selected rate cells render as Err:509, and its Payroll header contains VenueName&&. Its cached sample totals therefore do not establish a reliable, updatable workbook. B still falls short of full CBA compliance: its paired-rehearsal waiver ignores the same-location and daytime requirements, required rehearsal intermissions are not checked, and sound-check frequency is counted only within the week rather than across the engagement. B also adopts a blanket highest-single-premium policy that the supplied excerpt does not establish. Neither fully meets the task, but B’s operational calculations and clearer submission workflow make it substantially stronger.

A is substantially closer to a usable payroll tool. Its populated attendance grid feeds per-person formulas for the contract pay categories, and its rendered Schedule calculates the sample rehearsal at $278.35 and sound check at $76.59 while flagging the early rehearsal. B has a broken rate engine: defined names such as 'CBA Rates'$C$20 omit the required sheet-reference separator, and rendered page 6 shows Err:509 throughout the selected wage rates. Its cached totals therefore do not demonstrate reliable recalculation. A nevertheless falls short of robust CBA enforcement: its rehearsal-minimum waiver does not verify the required same-location/daytime conditions, it omits intermission checks, and its sound-check frequency check only counts the current week rather than enforcing engagement-wide and eight-week limits. Neither fully meets the task, but A requires considerably less repair.

Submission B wins because its model calculates live, while Submission A's calculation layer is broken despite its broader design. **Submission A.** On paper A is much broader. It has Compliance and Summary sheets, an instrument premium map, automatic quartet detection, an exigent-circumstances flag, and intermission and engagement-level sound-check tests. It also applies the 25% electronic premium to all of the sub's work. - The file checks show malformed defined names with no sheet separator, such as `'CBA Rates'$C$20` and `'Week Setup'$B$22`. B's names are well formed (`Rates!$C$15`). - The rendered Week Setup shows Err:509 for every base, guarantee, rehearsal and sound-check rate, the rate-row count and the rate status. - Pay formulas and Schedule checks depend on these names (BaseRate, RehearsalRate, VacationPct, EarliestStart). So do the dropdown lists. - Other formulas look syntactically invalid: `VenueName&&"...` in Payroll!A2, and `'Payroll'!$R>0` and `'Payroll'!$U>0` on Summary. Payroll!AB38 quotes a cell reference as literal text. - The $30,924.71 total appears to be only a stored result, since the README says the file recalculates on open. A contractor cannot rely on it flowing from new inputs or a new rate row, which defeats the purpose of a reusable, updatable model. - A also pre-fills invented justifications, such as a load-in excuse and an "attached" designation. It assigns the horn a "Principal" seat not in the sample, creating a red conflict the roster did not state. It applies premium to audit pay as well. **Submission B.** B is simpler but works as delivered. - The render shows live results: $278.35 rehearsal, $76.59 sound check, $251.06 per performance, premium percentages of 25/20/15/0, and doubling of 25%/45%. - The 9:30 rehearsal is highlighted amber with an Art. 7.3 message. - Effective-dated rates and rule parameters sit on one Rates sheet. - Capacity is 40 musicians and 30 services, with pick lists and an attendance grid. - Payroll formulas give per-person columns for performances, rehearsals, sound checks, audits, premiums, doubling, vacation and total, plus violation and warning counters. - The formula logic reproduces the cover-note totals: performances $18,453.09, doubling $3,426.99, and about $30,665.51 overall. B has real weaknesses: - The sub's 25% premium is paid only on the show, not on rehearsal and sound check, despite the "all work" wording. This is disclosed and is about $89 before vacation. - The quartet is a manual flag with no four-player test. - There is no intermission or same-location test. - Sound-check frequency is checked only within the week. These are limited gaps compared with a workbook whose rates and dependent pay logic fail on recalculation.

A is the more usable payroll tool because its model demonstrably recalculates, while B's core rate and name plumbing is malformed. Submission A: The rendered workbook recalculates cleanly. The effective-dated Rates lookup returns $251.06, $2,008.50, $55.67 and $76.59/$137.87. Schedule prices each service, and the 9:30 rehearsal start is highlighted amber with an Art. 7.3 message. Roster derives the expected premiums (20% concertmaster and lead trumpet, 15% first locals, 0% second guitar, 25% synth) and doubling (25%, and 45% for four instruments). Its Payroll formulas break out performances, rehearsals, sound checks, audit pay, premiums, doubling and 5.5% vacation by person with totals. I recomputed its stated totals by hand: 18,453.09 performances, 3,135.24 premiums, 3,426.99 doubling, 30,665.51 total. They reconcile. It has 40 musician and 30 service lines, and rates update by adding a row. A's weaknesses are real but secondary: - The synth sub gets 25% only on the show's base, not on rehearsal or sound check. - The engagement-length input is not used to track sound checks across weeks. - There is no 10/50 intermission check. - Multiple woodwind designees would be flagged as a violation. Submission B: The design is richer, with a Compliance sheet and rule library, instrument map, calculation switches, intermission and engagement-level sound-check tests, and a Summary sign-off. Its cached figures are nearly identical to A's except for the sub. However, the file is technically defective: - Defined names lack the sheet separator (e.g., 'CBA Rates'$C$20, 'Week Setup'$B$22). - The render shows Err:509 in every auto-selected rate cell and in Rate table status on Week Setup. - Payroll!A2 contains an invalid '&&'. - Summary!E9 and E14 contain the invalid reference 'Payroll'!$R>0. - Payroll!AB38 prints a reference as literal text. Nearly all pay, checks and dropdowns route through these names, and the README says the workbook recalculates in full on open. The displayed 30,924.71 therefore appears to be a cached value, not evidence of a working model, and a contractor could not safely change inputs or roll rates forward. That defeats the robustness and rate-update requirements. B also pre-fills invented names, IDs, theatre details and justification text. A's simpler working model better serves the stated purpose.