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

A provides the more usable working model: linked roster, schedule, attendance and payroll formulas, centralized editable rates, and separate per-person pay categories. Its rendered Schedule calculates service pay and highlights the 9:30 rehearsal. B has fundamental calculation defects: defined names such as RateFrom omit the sheet-reference exclamation mark, and the rendered Week Setup shows Err:509 throughout the rate section. Payroll!A2 also contains an invalid && operator. A nevertheless falls short of the CBA requirements: its rehearsal paid-hours formula always uses MAX(minimum call, hours), without implementing Article 7.4’s paired-rehearsal waiver; sound-check frequency is checked only within the current week rather than across the engagement/eight-week interval; and its synthesizer-substitute show top-up offsets the special per-show entitlement against rehearsal and sound-check earnings, eliminating that top-up in the sample. Thus A is the better starting point, but neither is reliable for compliant payroll submission without correction.

B has the more usable calculation chain: its rendered rate and schedule sheets calculate the supplied wages, and its attendance formulas feed separate per-musician payroll categories. A’s named ranges omit the required sheet-reference separator—for example, 'CBA Rates'$C$20—and its rendered Week Setup shows Err:509 throughout the selected rates. A also contains invalid formulas such as VenueName&& in the payroll header, so its cached totals do not demonstrate a functioning, updatable model. B nevertheless has material CBA errors: it denies the French-horn 15% premium when trumpet is present despite Article 4.2(c), always applies the three-hour rehearsal minimum without implementing the Article 7.4 waiver, and invents a base-service rate for evening rehearsals that the excerpt does not supply. Neither is ready for contractual payroll use, but B is the stronger actual workbook.

A is the better deliverable because its calculation engine works, while B's is structurally broken, even though B reads the CBA more accurately. Submission B's failures: - All of its defined names lack the sheet separator (e.g. 'CBA Rates'$C$20, 'Week Setup'$B$22, 'Lists'$A$3:$A$10). - The rendered Week Setup page shows Err:509 for base wage, weekly guarantee, rehearsal rate, both sound-check rates and the rate-table status. These are the cells every pay formula depends on through BaseRate, RehearsalRate and similar names. The correct figures ($30,924.71 gross) appear only as stored values. - The README says the workbook recalculates fully on open, so these failures should reach the payroll. Malformed names would likely trigger a repair prompt in Excel and leave #NAME? errors, and named drop-down lists would be at risk too. - Other formulas are also defective: Payroll!A2 contains '&VenueName&&', Summary E9/E14 use 'Payroll'!$R>0 and $U>0, and Payroll!AB38 outputs literal text. - A contractor could not rely on it, change inputs or update rates without rebuilding about 70 names. - It also adds invented content, including a pre-written justification for the 9:30 call, and capacity is 32 musicians/40 calls with a multi-sheet row-insert procedure. Submission B's strengths, on paper: - It pays the French horn 15% under 4.2(c). - It pays the sub 150% of base. - It tops 8-show regulars up to $2,008.50. - It detects the 7.4 waiver and tests sound checks against engagement length. - It offers interpretation switches, a Compliance sheet and a Summary. Submission A's strengths: - Its named ranges are valid, and the rendered sheets calculate: rates by year, roster percentages, $278.35 rehearsal, $76.59 sound check, $251.06 per service, and attendance counts. - The 9:30 rehearsal is highlighted with an Art. 7.3 message. - It provides year-based rate rows and editable CBA parameters. - It handles 100 musicians and 50 services. - Its Payroll sheet gives per-person performance, rehearsal, sound check, audit, premium, doubling, top-up, vacation and total columns. - A 22-rule OK/REVIEW/VIOLATION sheet supports the checks. Submission A's CBA errors are real but correctable: - It denies the French horn's 4.2(c) 15% whenever a trumpet is present. - Its 4.2.f.ii top-up nets against rehearsal and sound-check pay, so the sample sub never receives 150% on the show. - The guarantee test uses all earnings, leaving regulars at $2,008.48 of performance pay. - There is no 7.4 waiver logic. A working but imperfect tool is more usable than one whose core rates and pay formulas fail on recalculation.

Submission A has the stronger design and the more accurate reading of the CBA, but as delivered it does not calculate. Submission B is simpler and has some contract errors, yet it actually works. A cannot serve as a payroll tool in its current state, so B wins. **What A gets right:** - Rates are selected by effective date. - An instrument premium map drives premiums, and a 20% uplift replaces the 15% where it applies. - French horn gets the 15% premium under Art. 4.2(c). - The synthesizer sub is paid min(150%×base, guarantee) per show plus the 25% premium. - It detects the Art. 7.4 minimum-call waiver and counts intermissions. - It tests sound-check frequency against the engagement length. - It has a Compliance sheet and a Payroll sheet broken out by category. - Its cached total of $30,924.71 checks out against a hand calculation. **Why A fails:** - Every defined name lacks the '!' separator (e.g. `'CBA Rates'$C$20`, `'Week Setup'$B$22`). - The rendered Week Setup page shows Err:509 for base wage, weekly guarantee, rehearsal rate, both sound-check rates, the row-match count and the rate status. This is a missing-operator parse error caused by those names. - Nearly all pay formulas (BaseRate, RehearsalRate, VacationPct, PremBase), the instrument map lookups and the drop-downs depend on these names. The workbook will error or need repair when opened, and errors cascade on any edit. **What B gets right:** - Its named ranges are valid (e.g. `Rates!$B$31`), and the rendered pages show live results. - The rate table is keyed by year, with editable CBA parameters. - Roster add-on percentages compute (20/25/15/15/40/40/25/45/60/0%). - Schedule pay per musician computes ($278.35 rehearsal, $76.59 sound check, $251.06 per show), and musician counts per call are right. - The 9:30 a.m. rehearsal is highlighted with an Art. 7.3 flag. - It provides a 100×50 attendance grid, a 22-rule CBA Checks sheet, and per-musician totals by category (performance, rehearsal, sound check, audit, premiums, doubling, sub minimum, guarantee top-up, vacation). **B's flaws:** - French horn gets 0%, and B would flag a correct 15% as a violation when a trumpet is present, contrary to Art. 4.2(c). - The Art. 4.2(f)(ii) rule is applied as a floor over all earnings, so the sub never receives 150% per show. - The guarantee is tested against total earnings rather than service pay. - There is no Art. 7.4 waiver. - Audit pay is mislabeled Art. 5.5.b.