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

A provides the stronger reusable model: its formulas filter payroll by the selected week, use effective-dated rates and percentages, track audits by book across the engagement, and check sound-check anchors, frequency, rehearsal breaks, lunch-pair waivers and premium eligibility. It also separates the required per-person pay categories. B has fixed 40-musician/30-service grids, ignores rate end dates, checks sound-check frequency only within the current schedule, and does not enforce the three-hour anchor or quartet eligibility. B’s synth-substitute premium formula excludes rehearsal and sound-check earnings despite the electronic premium applying to all work. However, A’s rendered Rates, Premium Chairs, Schedule and Attendance pages show Err:507 in calculated cells, while its cached text shows results. That discrepancy prevents confirming that A recalculates and highlights conflicts reliably in Excel.

B’s formulas address substantially more of the actual CBA: effective-dated wages and percentages with missing/overlapping-rate checks, full-engagement sound-check frequency, three-hour anchor calls, 50/10 intermissions, meal breaks, same-location lunch waivers, and audit eligibility/history. Its weekly reports separately total the required pay categories by musician. A has material gaps: its rate lookups ignore expiry dates and silently fall back to 2025 rates; sound-check checks consider only the current week and accept any nearby service rather than a scheduled three-hour call; and Payroll!M8 omits the synth substitute’s electronic premium on rehearsals and sound checks. However, B’s rendered table pages show widespread Err:507 results despite error-free cached values, so its actual Excel recalculation usability cannot be confirmed from the supplied evidence.

Both workbooks cover the core requirements: a rate table, roster, schedule, attendance, conflict checks, and per-person totals for performance, rehearsal, sound check, audit, premiums, doubling and 5.5% vacation. For regular musicians the two models produce the same pay; the woodwind player totals $3,989.49 in both. Submission A is far more exhaustive: - Rates are effective-dated and include the percentages. - Tables hold 120 musicians and 800 services. - It adds quartet-composition validation, meal-break logic, sound-check anchor and frequency checks, and audit-per-book tracking. - It offers method toggles and a START dashboard showing $30,825.40 and NOT READY. The problem is that its rendered input sheets show Err:507 throughout. This covers the Checks column on Rates, Musicians, Premium Chairs and Schedule, the Level column on Premium Chairs, and the Date, Type, Musician and Checks columns on Attendance. Only cached values look correct. The row-level unqualified [@[...]] structured references did not parse in the renderer, so the conflict-highlighting layer is not demonstrably working. It may also not survive Excel's file parser. A is also very heavy for a contractor: - The sample needs a 100-row attendance ledger and a separate Premium Chairs sheet. - Setup asks for approval gates and stacking and synth-cap method choices. - The sample defaults to NOT READY. - It flags the valid 6pm sound check because the sample booked the performances at 2.5 hours. - Its default synth-sub reading folds the 25% premium inside the 150% cap. Submission B is simpler and visibly functional: - The rendered Schedule computes hours and pay ($278.35, $76.59, $251.06) and highlights the 9:30am rehearsal with an amber WARNING. - The Roster auto-computes premium and doubling percentages and shows compliance messages. - The Payroll sheet has a violation/warning counter, grand totals and a signature block. - Its stated totals reconcile with its formulas ($30,665.51). B does have weaknesses: - Capacity is 40 musicians and 30 services. - Percentages are not effective-dated. - It omits the synth sub's 25% electronic premium on rehearsal and sound check. - It does not validate quartet composition. - It would flag multiple woodwind designations. Overall, B is the more reliably usable weekly payroll tool for the stated purpose.

Submission A is the more reliable and usable payroll tool. **Submission A works as rendered.** - **Rates:** an effective-dated rate table, with all percentages and rule parameters held as named inputs. - **Schedule:** service pricing calculates correctly ($278.35 rehearsal, $76.59 sound check, $251.06 performance). The CBA check correctly flags the 9:30am rehearsal in amber under Art. 7.3. - **Roster:** premium and doubling percentages calculate as expected. Violin and trumpet chairs get 20%, the second guitar gets 0%, and woodwind doubling is 45%. Each row shows a working compliance check. - **Attendance:** a simple 1/0 grid, which is easy for a contractor to fill in. - **Payroll:** per-person totals for performances, rehearsals, sound checks, audit pay, premiums, doubling, vacation and total pay, plus violation and warning counters. - **Totals check out.** I recomputed the cover-note totals: 9×$2,008.50 + $376.59 = $18,453.09 performances; $3,135.24 premiums; $3,426.99 doubling; $30,665.51 total. They are internally consistent. - **Weaknesses:** the synth sub gets no 25% electronic premium on rehearsal or sound check, which is debatable under Art. 4.2.f.i. Quartet membership is not validated. Times display without AM/PM. **Submission B is more ambitious on paper.** - Effective-dated percentages, toggles for disputed interpretations, quartet and sound-check-anchor validation, and a capacity of 120 musicians. - Its cached values look sensible: $30,825.40 gross, with the electronic premium applied to all of the synth sub's work. **But B's rendered pages show Err:507 throughout its core calculated columns.** This affects Checks on the Rates, Musicians, Premium Chairs and Schedule sheets, Level on Premium Chairs, and Date/Type/Musician/Checks on Attendance. The table formulas use raw `[@[...]]` syntax, so the conflict-highlighting feature the task requires is not shown working, and Excel compatibility is doubtful. **B is also much harder to use.** - Ten sheets, a one-row-per-musician-per-service attendance ledger and a separate premium-assignment table. - Approval flags leave the sample permanently "NOT READY", with 28 attendance flags and 10 people needing review. Some flags are pedantic, such as rejecting the sound check because the show is booked at 2.5 hours rather than 3. - Dates render oddly (", 1 6, 2025"). - Its default synth-sub reading folds the 25% premium inside the 150% cap, which is the less natural reading of the clause.