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

A is materially closer: it has effective-dated rates and percentages, an ID-linked service ledger, and per-person totals for the contract’s payroll categories. Its formulas check sound-check three-hour anchors, 56-day spacing, rehearsal exceptions and meal placement. B has demonstrably broken references: defined names such as 'CBA Rates'$C$20 omit the required !, Payroll!A2 contains VenueName&&, and rendered rate cells show Err:509. B also grants quartet premiums merely by counting four violin/viola/cello players, ignoring other acoustic strings, and omits important sound-check restrictions. A nevertheless falls short of full robustness: its schedule-level paired-rehearsal waiver feeds every attendee’s paid hours without checking that each musician attended both qualifying calls. Someone attending only one two-hour half can therefore receive less than the three-hour minimum without a corresponding flag. A’s rendered checks and attendance cells also show Err:507, which requires investigation before operational use.

B has the stronger implemented model: ID-linked Excel tables, effective-dated rates, engagement-wide audit tracking by book, per-person payroll category totals, and checks for quartet eligibility, three-hour sound-check anchors and eight-week frequency limits. A has demonstrably malformed references: its defined names omit the sheet-reference '!', and Payroll!A2 contains 'VenueName&&'. Its rendered rate calculations show Err:509, while the complete Schedule inventory contains no attendance entries despite nonzero cached payroll totals. B is more complete and maintainable, but its rendered pages also show Err:507 throughout Checks and Attendance calculations, conflicting with its cached results. Its live Excel functionality therefore cannot be confirmed from the supplied evidence.

Both workbooks cover the same requirements: a rates table, roster and schedule inputs, CBA checks, and per-person category totals. Their cached results agree for the nine regular musicians. The whole $99.31 gap ($30,825.40 for A vs. $30,924.71 for B) comes from how each reads the synth-sub clause. A's default puts the 25% inside the 150% cap but offers the alternate reading as a switch; B applies the 25% on top of the 150% amount. A is stronger on robustness and rate updates: - Each effective-dated rate row holds every base rate and every percentage, so a new contract year is one complete row. - Its tables hold 120 musicians, 800 services and 8,000 attendance rows, with hire dates and engagement-long history for sound-check frequency and audit tracking. - A Premium Chairs table records local rank, seat and woodwind designation. - Its checks cover early calls, 50/10 breaks, minimum call, lunch-pair waiver, meal breaks, sound-check anchor and frequency, quartet validity, duplicate first-local ranks, and horn vs. trumpet. - The renders show conflicting inputs highlighted amber (the 9:30 start and unconfirmed designations). - The Weekly Payroll sheet gives per-person Performance, Guarantee, Rehearsal, Sound check, Audit, Premiums, Doubling, Vacation and Gross. - A's weaknesses are heavy complexity and Err:507 in the LibreOffice render of its structured-reference check columns. That syntax is standard Excel, and the cached check messages and values are present. B has the friendlier attendance grid, Compliance sheet and Summary sheet, but shows concrete technical defects: - Its defined names lack the sheet separator, e.g. 'CBA Rates'$C$20. - Its render shows Err:509 for the base wage, guarantee, rehearsal rate, sound-check rates and rate status, so the year-to-year rate engine itself fails. - The Payroll A2 formula contains an invalid '&&'. - Summary references the invalid ranges 'Payroll'!$R and $U. - Capacity is 32 musicians and 40 calls, and expanding it means synchronized manual edits across three sheets. - Premium percentages are not effective-dated. - It contains odd filler text ('Rents: Paying the Wages', 'AF&A', a 2026 build date). Overall, A is the more complete and reliable model.

Both workbooks have similar scope. Each has effective-dated rate tables, roster and schedule inputs, Article-cited CBA checks, and per-musician category totals. A's Payroll shows performance, sub show, rehearsal, sound check, audit, guarantee top-up, premiums, doubling, vacation and gross. B's Weekly Payroll shows the same plus a synth uplift. The cached sample math agrees on first-local, concertmaster/lead, horn 15%, doubling and vacation. The totals differ only in how each reads the synth-sub clause ($30,924.71 vs $30,825.40). A has definite file defects. All of its roughly 70 defined names are stored without the '!' (e.g. "'CBA Rates'$C$20"). In the render, this shows as Err:509 across the entire 'CBA rates in effect' block on Week Setup. Payroll A2 contains "VenueName&&". Summary E9 and E14 use invalid refs ('Payroll'!$R, $U). In Excel these would likely force a repair and cascade #NAME? through Payroll, Roster, Schedule, Compliance and the drop-downs. B's render shows Err:507 in every column that uses [@[...]] this-row references (Checks, Level, Attendance Date/Type/Musician). That is a real risk. However, its START and Setup sheets render cleanly with no other syntax faults. Its design is also stronger on the task's robustness and updatability demands: - Excel tables sized for 120 musicians, 800 services and 8,000 attendance rows; A has 32 lines and 40 calls, expanded by hand across three sheets. - Effective-dated percentages as well as base rates. - Finer CBA checks: a sound check must precede a 3-hour call, meal placement, reciprocal lunch pairs, and audits per book across the engagement. - Explicit switches for the ambiguous synth-cap and stacking clauses, plus the verbatim excerpt. A is friendlier for a contractor (1/blank attendance grid, consolidated Compliance and Summary sheets). Its breakage, however, is more certain and more visible. Net, B is the better deliverable, though neither is cleanly verified as working.