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

B is better, although neither is payroll-ready. A's named ranges omit the required '!' sheet separator—for example, PremLead refers to 'CBA Rates'$C$20—and its rendered Week Setup rate card shows Err:509 throughout. Its cached totals therefore do not establish a functioning, updateable payroll model. B has correctly formed named ranges, service-date rate lookups, populated coverage exclusions and selected-week per-musician totals for the required pay categories. However, B's default 'Shows only' synth-sub premium setting omits the electronic premium on rehearsal and sound-check work despite Article 4.2(f)(i)'s 'all work' provision. Its audit-assignment formula also mixes horizontal coverage and vertical roster ranges; the rendered sample audit rows display an assignment-error finding alongside OK statuses. It additionally imposes an exactly-one-musician-per-audit restriction absent from the CBA. These are material pay and compliance weaknesses.

A is the stronger usable foundation: it has valid named references, service-date wage lookups, and formula-linked per-musician totals for the required payroll categories. B has fundamental calculation defects: its named ranges omit the sheet-reference '!' separator, the rendered Week Setup shows Err:509 for the selected wage rates, and Payroll!A2 contains a malformed 'VenueName&&' expression. A nevertheless falls short of the requested robustness: its first-local and quartet premium calculations include inactive roster entries, and its all-weeks Payroll Register takes premium/doubling percentages from the currently selected week's Premium Calc rather than each payroll week's applicable rates. Both need substantive repairs before reliable payroll use, but B's broken references undermine the calculation system more broadly.

Submission A is well designed and closely documented, but it appears broken in practice. Its defined names omit the sheet separator: for example, it stores 'CBA Rates'$C$20 and 'Week Setup'$B$22, where B stores valid forms such as 'Premium Calc'!$AB$9. A's rendered Week Setup page confirms the effect: base wage, guarantee, rehearsal rate, both sound-check rates, the rate-row count and the rate status all show Err:509. Almost every pay, premium and check formula depends on these names (BaseRate, RateFrom, MapInstr, PremLead and others), so the rate engine fails in a recalculating application. In Excel the file would likely need repair and return #NAME? errors. A's Schedule attendance grid (P7:AU46), which drives all service pay, also has no entries. The 1,601-cell inventory accounts exactly for every other listed cell. A's cached total of $30,924.71 therefore cannot be reproduced from the file's inputs. Some of A's interpretations are stronger than B's: it tops regulars up to the full $2,008.50 guarantee, stacks premiums, puts guarantee top-ups in the premium base, caps audit pay and offers an exigent-circumstances cure. None of that helps if the workbook does not compute. Submission B's names are valid and its rendered Cover, Rates, Settings, Roster and Services pages compute cleanly. Its cached gross of $30,665.27 reconciles with its inputs: about $2,363.42 base for each regular, matching premiums and doubling, and $1,367.72 for the synthesizer sub. It meets the brief: rate periods are looked up by date for yearly updates, the roster/services/coverage inputs fit any configuration, a 52-week register and run totals handle production runs, and the Weekly Payroll shows per-musician totals for each Art. 11.4 category, supported by pay statements and a compliance register with article citations. The 9:30 a.m. rehearsal is flagged. B does have flaws: the audit check multiplies a row range by column ranges, so correct audit inputs raise a false error (the render shows that audit message), and its defaults lean conservative (highest premium only, the sub's 25% on shows only, top-up excluded from the premium base). These are moderate issues compared with A's apparent breakage.

Submission A is a working model; Submission B is well designed but delivered broken, which outweighs its design merits. **Pay calculations — both are sound in cached values.** I recomputed each workbook from the sample roster and schedule: - A's $30,665.27 is reproduced exactly: 8×$251.06 performances, 5 h×$55.67 rehearsal, $76.59 sound check, then 20% concertmaster and first trumpet, 15% first-local viola, cello, bass, guitar, horn and designated woodwind, doubling at 25%, 25% and 45%, the synth sub at 150%, two audits, and 5.5% vacation. - B's $30,924.71 also reconciles. The difference is only interpretation: B tops up $0.02 to the guarantee and applies the synth 25% to all work. **File integrity — B fails.** - B's defined names lack the "!" separator (e.g. PremLead = 'CBA Rates'$C$20; RateFrom, BaseRate, WeekStart and every List_* are the same). - B's rendered Week Setup shows Err:509 in every auto-selected rate cell and in the rate-table status. - B also contains invalid formulas: Payroll A2 has "VenueName&&", Summary E9 and E14 use 'Payroll'!$R and 'Payroll'!$U, and Payroll AB38 holds a quoted literal. - B says the workbook recalculates fully on open. If so, rates, premiums, pay and the drop-down validations would error in Excel or LibreOffice, so the contractor could not actually use it. **A works, with one false positive.** A's defined names are well-formed and its renders show live values on the Cover, Rates, Roster and Services sheets. Its one real bug is in the audit check: a row-by-column SUMPRODUCT broadcasts, so the render shows "An audit service must be assigned to exactly one musician" on S001 and S002. This is a spurious compliance error; it does not change any pay figure. **Scope — A is broader.** A supports: - multi-week runs, 40 musicians and 300 services; - a coverage grid of who plays each service; - per-period rate and percentage rows; - policy switches; - a Payroll Register, Run Totals and a per-musician Pay Statement (Art. 11.4); - a full Compliance register. B is single-week only. **Where B's design is better.** B has an exigent-circumstances input for the early call, an intermission check, and a more sensible performance-only guarantee floor. A's guarantee basis counts rehearsal and sound-check pay, and A defaults the synth 25% to shows only. These are interpretive weaknesses in A, but they are far less serious than B's non-functional file.