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

B better supports reuse through ID-linked Excel-table formulas, service-date rate selection, engagement-wide audit tracking by book, and explicit quartet and booked three-hour sound-check checks. Both itemize payroll totals by person. A uses fixed 40-musician/30-service ranges; its automatic quartet test ignores bass and other acoustic strings, while its sound-check test assumes every performance or rehearsal qualifies as a three-hour call. Neither fully satisfies conflict detection: B checks meal limits within individual calls but can accept two three-hour rehearsals separated by only 15 minutes without detecting the missing 30-minute meal. B’s rendered calculated/check cells also show widespread Err:507, although its cached file checks report no Excel errors.

A implements stronger reusable CBA calculations: service-date rate selection, ID-linked attendance, per-book audit tracking across the engagement, and separate person/category totals. It checks booked three-hour sound-check anchors, while B assumes every performance or rehearsal qualifies. B’s quartet formula can enable premiums through “Yes” regardless of player count, or automatically with four upper-string chairs despite an additional bass, without flagging the conflict. B also uses fixed 40-musician/30-service ranges rather than A’s structured tables. However, A’s rendered calculation and check cells repeatedly display Err:507 despite the file check reporting no cached Excel errors, so its functioning in Excel cannot be conclusively confirmed.

Submission A is the safer, more usable deliverable. Its formulas all compute in the rendered pages with no errors, and it satisfies each requirement in a form a contractor can operate. The Rates sheet has an effective-dated rate table for future years, plus editable percentage and rule tables. Setup, Schedule, Roster and an X/A Attendance grid cover 40 musicians and 30 services per week. Conflicts are flagged on the input rows and summarized in a CBA Checks dashboard: the 9:30 AM rehearsal shows a red CONFLICT (Art. 7.3) in the rendered Schedule. The Payroll Summary gives per-person totals for performances, rehearsals, sound checks, audits, premiums, doubling, vacation and gross, with reconciliation checks, and there is also a per-musician Pay Stub. Its premium, doubling, guarantee, sound-check and vacation logic is consistent with the CBA. Its default reading of the synth sub clause is natural: capped 150% pay plus the 25% premium. Submission B is more elaborate. It offers effective-dated percentages, 120-musician and 800-service tables, method toggles, the verbatim CBA text and a review queue. Its cached values are internally consistent: the $30,825.40 total reconciles with the logic. However, the rendered pages show Err:507 throughout. It appears in every Checks column (Rates, Musicians, Premium Chairs, Schedule) and in Attendance Date/Type/Musician/Checks. These are the cells using raw [@[...]] this-row references. Excel's file format normally stores these as [#This Row], so the core flagging and calculation engine may not survive opening in Excel. B is also heavier to use: a ledger needing roughly 98 attendance rows per week, IDs, anchors and booked hours. Several approval toggles block submission. It flags the sample's normal 6–7 pm sound check because the 8 pm show is booked at 2.5 hours. Its default synth-sub reading folds the 25% premium inside the 150% cap. Given A's demonstrated, error-free function against B's visible formula failures, A better meets the task.

**Submission A** is the more ambitious design, but its visible output is broken and it is harder to use. - **Coverage:** A covers a lot. Its rate table is effective-dated and includes every percentage. It tracks audit and sound-check history across the whole engagement. It validates sound-check anchors, lunch-pair waivers, meal intervals and quartet configuration, and it has a review-queue dashboard. - **Broken formulas:** The rendered pages show Err:507 throughout the computed columns. This covers the Checks columns on Rates, Musicians and Schedule, the Level and Checks columns on Premium Chairs, and the Date/Type/Musician/Checks columns on Attendance. These are the cells that are supposed to highlight CBA conflicts. They use raw `[@[...]]` structured references, which do not evaluate in the rendering. Cached values exist (e.g., $30,825.40 gross), but the displayed workbook does not demonstrate working conflict flags. - **Usability:** A requires one attendance row per musician per service. It adds many confirmation inputs (booked call hours, SC anchor, 50/10 breaks, families complete, methods approved). The sample opens as NOT READY with 28 attendance flags and 10 people flagged. - **Questionable defaults:** - The 'Total comparison' synth-sub reading folds the 25% premium inside the 150% cap. Under it, the sub's one show totals about $376.59, versus about $439.36 under the more natural reading. - A applies the electronic premium to audit pay. - A flags the sample sound check because the 2.5-hour show is not a booked 3-hour call. **Submission B** renders cleanly and is practical for a local contractor. - **Structure:** Setup; a Rates table selected by date, with editable percentages, rule parameters and an instrument/category list; a Schedule with Art. 7 checks; a Roster with Art. 4 checks; and an X/A attendance grid. - **Conflict highlighting:** The red-formatted 9:30 AM rehearsal shows a CONFLICT flag. A CBA Checks dashboard counts flags by rule. - **Per-person totals:** The Payroll Summary gives performances, rehearsals, sound checks, audits, premiums, doubling, earnings, vacation and gross, with reconciliation checks. A Pay Stub breaks out each premium type, matching Art. 11.4. - **Calculations:** Traced by hand, its sample results are sound. - Regular players get the weekly guarantee plus 5h × $55.67 rehearsal and $76.59 sound check. - Chair and doubling percentages are applied to all work (20% concertmaster/lead trumpet; 15% others; 25%/45% doubling). - The quartet premium is correctly not applied (only 3 upper strings). - The synth sub gets the lesser of 150% of base or the guarantee, plus 25% per show and on rehearsal/sound check, with audits at base. - **Limits:** No hire dates, a 40-musician cap, and sound-check history entered as a count rather than tracked. These are minor for the stated purpose.