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

A provides a usable linked model with date-effective rates, roster and attendance inputs, per-person totals for the contract’s payroll categories, and rule-specific checks—including the visibly flagged 9:30 a.m. rehearsal. B has fundamental calculation defects: defined names such as 'CBA Rates'$C$20 omit the required separator, Payroll!A2 contains 'VenueName&&', and the rendered rate cells display Err:509. Its cached payroll totals therefore do not establish reliable recalculation when inputs or rates change. A’s rendered sheets are also substantially more usable than B’s blank, cropped and horizontally split pages.

B is substantially more usable: its effective-date rate lookups use valid references, sample X/A attendance is populated, payroll formulas separate the required categories by musician, and the rendered schedule highlights the 9:30 a.m. rehearsal conflict. A's defined names omit the sheet-reference '!' (for example, 'CBA Rates'$C$20), and its rendered Week Setup shows Err:509 throughout the rate calculations. Payroll!A2 also contains the malformed expression VenueName&&, undermining its submission sheet. B nevertheless falls short of the requested robustness: Calc Detail!I6:I45 returns zero guarantee/performance wages whenever performances equal zero, even for Weekly musicians. Its automatic quartet test also counts only weekly violin/viola/cello chairs, allowing the premium when an additional acoustic bass makes the acoustic string section larger than four.

Submission A is a working, internally consistent model. Submission B is better designed on paper but technically broken. **Submission A** - **Rates:** A dated rate table auto-selects rates via SUMIFS on the week-ending date (Rates!D20 = TRUE). All CBA percentages and rule thresholds sit in editable named cells. - **Inputs:** A 30-row Schedule, a 40-row Roster and an X/A Attendance grid drive the calculations. - **Pay logic:** Calc Detail applies: - the weekly guarantee; - the 4.2f.ii synth-sub lesser-of rule; - the 3-hour minimum and 7.4 waiver; - 1-hour and 2-hour sound-check flats; - first-local, 20% lead/concertmaster, quartet and electronic premiums; - 25%/10% doubling; - audit pay at 2 per book; - 5.5% vacation. - **Per-person output:** The Payroll Summary gives per-person totals for performances, rehearsals, sound checks, audits, premiums, doubling, vacation and gross, plus grand totals and reconciliation checks. There is also a per-musician Pay Stub (Art. 11.4). - **Conflict flagging:** Conflicts are flagged on the input sheets and counted on a CBA Checks dashboard. - **Rendered output:** Pages show computed values with no errors. The sample's 9:30 AM rehearsal is highlighted red under Art. 7.3. Defined names are well formed (e.g., `Rates!$B$21`), and no cached errors were found. **Submission B** - **Design:** Conceptually rich, with a Compliance sheet, a Summary with sign-off and an instrument premium map. - **Malformed named ranges:** Every defined name lacks the `!` separator (e.g., `'CBA Rates'$C$20`, `'Week Setup'$B$22`). Nearly all pay, rate and threshold formulas depend on these names. - **Rendered failure:** The Week Setup render shows Err:509 for the base wage, weekly guarantee, rehearsal rate, both sound-check rates, the row-match count and the rate status. These are the core auto-selected rates. - **Formula syntax errors:** - `VenueName&&` in Payroll!A2; - `'Payroll'!$R>0` and `'Payroll'!$U>0` in Summary; - a string-literal reference bug in Payroll!AB38. - **Practical effect:** In Excel this would likely trigger file repair and #NAME? errors on recalculation, which B forces on open. The rule checks that rely on named thresholds would then silently drop conflicts. - **Cached values:** These look plausible (gross of $30,924.71), but the model is not usable as a robust, updatable tool. - **Presentation:** README print pages are split, with text cut off. A fully satisfies the task in working form; B does not.

Both workbooks are designed for the task. Each has an effective-dated rate table with auto-selection, a configurable roster and schedule, CBA conflict checks, per-musician category totals and sample data loaded from the attachments. A has the more ambitious design (three interpretation switches, an audit cap, a duplicate-name check and a rule library), but its implementation appears broken. A's defects: - Every defined name lacks the '!' separator (e.g. PremLead = 'CBA Rates'$C$20, BaseRate = 'Week Setup'$B$22). This is not a display artifact: the same check shows B's quoted name as 'CBA Checks'!$D$20. - A's rendered Week Setup page shows Err:509 for base wage, guarantee, rehearsal rate, both sound-check rates, the row-match count and the rate status. Nearly every pay formula, check and dropdown depends on these names, so the engine fails once it recalculates. The README says the file is set to recalculate in full on open. - Other formula syntax errors: Payroll!A2 contains 'VenueName&&', and Summary E9/E14 use invalid references ('Payroll'!$R, 'Payroll'!$U). - Payroll AB38 prints a literal string, "'Compliance'!$F$5". - Content artifacts such as "Rents: Paying the Wages" and "AF&A". - In Excel, A would likely trigger a repair and show #NAME?-type errors. B's evidence: - Defined names are well formed. - Rendered pages show live results: rates auto-selected at $251.06/$2,008.50/$55.67/$76.59/$137.87; the Setup checks evaluate to TRUE/FALSE correctly (string quartet FALSE with 3 strings, trumpet consistency TRUE). - The Schedule visibly highlights the 9:30 AM time-in cell in red with an Art. 7.3 conflict message. - Its Payroll Summary gives totals by person for performances, rehearsals, sound checks, audits, premiums, doubling, earnings, vacation and gross, with reconciliation checks, a per-musician Pay Stub and a CBA Checks dashboard. - Its calculations are sound: guarantee for weekly players, the 4.2f.ii lesser-of rule plus 25% for the synth sub, 25%/10% doubling, 5.5% vacation and the 3-hour minimum call with the 7.4 waiver. B is a usable, correct deliverable; A likely is not as delivered.