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

B implements more of the required contract logic: effective-date rate lookups, selected-week category totals, the Article 7.4 paired-rehearsal waiver, and rolling 56-day sound-check checks. A always applies the three-hour rehearsal minimum and checks sound-check frequency only within one week. A also has material pay errors: Payroll column N offsets the synthesizer substitute’s show entitlement against rehearsal and sound-check earnings, leaving the sample substitute with no 150% performance adjustment. Its roster logic denies the French-horn premium whenever a trumpet is present, despite Article 4.2.c listing French horn independently. B calculates the substitute performance adjustment separately and provides that 15% French-horn premium. However, B is not sufficiently reliable as delivered: its first-local and quartet calculations do not exclude inactive or out-of-engagement musicians, and its quartet test does not establish the one-on-a-part and entire-acoustic-string-section conditions. These can produce incorrect premiums without an appropriate conflict flag.

A provides better contract coverage: effective-dated rate lookups, weekly totals by musician and payroll category, and an implemented Article 7.4 paired-rehearsal/lunch exception. B always applies MAX(3, hours) to rehearsals, so cannot apply that exception. B also incorrectly suppresses the sample French horn’s separate 15% entitlement under Article 4.2(c), and its substitute-minimum formula offsets rehearsal and sound-check earnings against the synthesizer substitute’s show entitlement. However, A is not fully reliable either. Its first-local premium selection ignores inactive status and engagement weeks, allowing an inactive first-listed player to suppress an active player’s premium. Its claimed two-audits-per-book completeness check is not implemented in the compliance formulas. Premium and doubling percentages for every register week also depend on the selected week’s rate period. These are material gaps for a model intended to handle changing orchestras and production runs.

Both workbooks provide the core deliverable. Each has a rate table, roster and schedule inputs, CBA conflict flags, and per-musician columns for performance, rehearsal, sound check, audit, premiums, doubling, guarantee top-up, vacation and total. The difference is CBA accuracy on the sample and overall robustness. Submission A has two substantive misreadings that affect the sample payroll. (1) It sets the French Horn premium to 'None'. Its Roster Q formula also flags any horn premium as a violation whenever a trumpet is on the roster. That ignores Art. 4.2.c, which expressly lists French horn for 15% regardless of seating. The horn is underpaid about $354 plus vacation, and the tool would mark the correct payment as a conflict. (2) The Art. 4.2.f.ii substitute top-up is MIN(1.5×base×shows, …)−(M−J). This compares the 150% show minimum against rehearsal, sound check and premium pay, so the sample sub's top-up computes to 0 and the 150% rule never applies. Premium designations are manual, and nothing flags a missing first-local premium. A handles one week per file, and the payroll year must be set by hand. Submission B is more accurate. - It assigns premiums automatically from roster order and local status: concertmaster 20%, lead trumpet 20%, French horn 15% under 'Other', and the designated woodwind 15%. - Doubling comes from instrument slots (woodwind 45%). - The sub is paid MIN(150%×base, guarantee) plus a 25% per-show premium, and audits are paid at base. - Rates are looked up by effective date. - It adds a multi-week register, run totals, an Art. 11.4 pay statement and a compliance register. - The Cover's $30,665.27 gross matches a hand recalculation of its formulas. B's weaknesses: - Its audit check multiplies a row range by column ranges inside SUMPRODUCT. The array broadcast falsely flags each audit as 'must be assigned to exactly one musician', and that text is visible in the render. - Its default 'Shows only' switch omits the 25% electronic premium on the sub's rehearsal and sound check, although this is configurable. - It caps the roster at 40 musicians. These are less damaging than A's hard-coded pay errors.

Submission A is the stronger and more contract-faithful model. Submission B has a usable structure, but it makes material CBA errors. Submission A – structure: - Rate table keyed by effective date, with the 2025 rates, all premium percentages and the rule limits; formulas look rates up by service date. - Roster of 40 musicians with up to 6 instruments each, a 300-row service schedule and a coverage grid. - 52-week payroll register, run totals and a printable pay statement. - Weekly Payroll shows per-musician columns for performances, rehearsal hours/pay, sound checks, audits, premiums, doubling, guarantee top-up, vacation and gross, plus an Art. 11.4 category-totals block. Submission A – calculations reconcile: - The cached Cover gross of $30,665.27 matches a hand calculation exactly. - Concertmaster 20% gives $2,992.09; the woodwind gets 15% plus 45% doubling. - The first local French horn gets 15% under Art. 4.2c. - The synth sub is paid 150% of base for its one show ($376.59) plus a 25% per-show premium, and 2 audits at $502.12. Submission A – checks: - Rehearsal start before 10 am (9 am hard stop) and the 3-hour minimum call, with the 7.4 waiver. - Meal break after 5 hours, 7.2 daytime cut-off, and sound-check length, 3-hour window and 56-day frequency. - Premium or doubling overrides below the CBA are errors; rate-coverage checks are included. Submission A – flaws: - The audit-assignment check multiplies a coverage row range by a roster column range inside SUMPRODUCT. This produces a false "must be assigned to exactly one musician" message on the correctly assigned audits. - Rates-table number formats are shifted by about 3 columns (vacation shows $0.06, minimum call 300%, synth multiplier "2"). - By default the sub's rehearsal and sound check omit the 25% electronic premium; a switch is provided. - The layout is dense. Submission B – what works: a simpler single-week workbook with year-keyed rates, Roster/Schedule/Attendance inputs, per-person category columns and a CBA Checks sheet. It catches the 9:30 am rehearsal. Submission B – errors: - It gives the French horn no premium whenever a trumpet is present, and flags any horn premium as a VIOLATION, despite Art. 4.2c listing French horn explicitly. - The synth-sub 150% minimum is offset against the sub's rehearsal, sound-check and premium pay (N = MIN(…) − (M − J)). It is therefore zero for the sample, and the sub's show is paid at plain base. - It invents base-per-service pay for non-daytime rehearsals, or rehearsals without confirmed intermissions. - There is no 7.4 waiver, no check for an omitted first-local premium, and no cross-week sound-check tracking. - B's sample total would be about $30,252, understated mainly through the horn and the sub.