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

A is more dependable for the core weekly payroll calculation and separately totals the stipulated pay categories. B offers broader run tracking, but its premium lookups include blank category-list entries that match unused instrument slots, creating false premium eligibility. Its audit validation also combines horizontal Coverage ranges with vertical Roster ranges; the rendered sample audit rows show an assignment-error message despite an OK status. Additionally, B uses the selected week's premium and doubling percentages for every register week, making multi-rate run totals unreliable. A still misses material CBA validation: its sound-check frequency check only counts the current week and ignores engagement length/history (Art. 7.10), it does not validate one-/two-hour sound-check calls, and its rehearsal minimum-call waiver checks neither same location nor daytime eligibility (Art. 7.4). Thus neither fully satisfies the requested robustness and conflict highlighting, but A has fewer defects affecting payroll correctness.

A better implements the required CBA checks: its Article 7.4 rehearsal-waiver formulas test daytime eligibility, matching date/location, lunch duration and combined hours. It also checks rate-period coverage and calculates weekly totals by musician and payroll category. B’s waiver ignores location and daytime eligibility, its rate lookups ignore Effective-to dates, and its sound-check frequency check only counts the current weekly schedule rather than the engagement. However, A is not reliable as delivered: its audit validation combines horizontal Coverage entries with vertical Roster arrays, first-local premium selection ignores active/engagement status, and every register week reuses the selected week’s premium/doubling percentages. Both require corrections; A is the closer match despite B’s clearer presentation.

Both workbooks cover the core requirements. Each has an effective-dated rate table and roster and schedule inputs. Each breaks out pay per person for performances, rehearsals, sound checks, audits, premiums, doubling and vacation, and each flags conflicts. Both flag the 9:30am rehearsal as an Art. 7.3 warning. Both compute the same sample results: 20% for concertmaster and lead trumpet, 15% for first-local players, 0% for the second guitar, 25%/45% doubling, 5×$55.67 rehearsal, $76.59 sound check, 2×$251.06 audits, the synth sub at 150% plus 25% per show, and 5.5% vacation. A's formulas are internally consistent and its totals reproduce on recalculation: performances $18,453.09 and gross $30,665.51. Weekly musicians are paid MAX(shows×base, guarantee), so regulars receive the $2,008.50 guarantee. Audit pay is capped at 2 per book with a warning, and the Schedule and Roster checks render cleanly ('OK — no conflict' plus a single amber warning). The layout is simple and usable: README, Rates, Schedule, Roster, Attendance, then a Payroll sheet with violation and warning counters and a signature block. B is much broader. It has 14 tabs, a 52-week register, pay statements, a compliance register, automatic first-local and string-quartet detection, policy switches and per-period premium percentages. That makes it stronger for multi-week production runs, but it has real correctness defects: - **Audit check:** it multiplies a horizontal Coverage row by vertical Roster columns inside SUMPRODUCT. Every valid audit therefore evaluates to 'An audit service must be assigned to exactly one musician'. The Services render shows this text next to a contradictory 'OK' status; in Excel the COUNTIF('E|*') status check becomes ERROR and the sample reads NOT READY. - **Weekly guarantee:** its default test counts rehearsal and sound-check pay toward the guarantee. Regulars therefore get $2,008.48 with a $0 top-up instead of the $2,008.50 guarantee, and in weeks with few shows this could materially underpay. - **Unimplemented check:** the Assumptions tab promises a 2-audits-per-book check that does not exist. - **Usability:** heavier complexity and some wrapped-text layout problems. B's extra scope does not offset its flawed conflict flagging and guarantee logic, while A meets every stated requirement correctly.

Both workbooks meet the core brief. Each has an effective-dated rate table, a roster, a schedule, attendance or coverage inputs, CBA flags, and per-person totals by category. Their sample results agree almost exactly: A gives $30,665.27 and B gives $30,665.51. Both apply concertmaster and lead trumpet at 20%, other first-local players at 15%, synth sub at min(150%, guarantee) plus 25% per show, doubling at 25%/10% (woodwind 45%), 2 audits at base, and 5.5% vacation. Both flag the 9:30 a.m. rehearsal as a warning. A is substantially more robust on the explicitly stated needs, which is why it wins: - **Production runs:** it covers multi-week runs through a 52-week Payroll Register, a week selector, Run Totals and Pay Statements. - **Orchestra configuration:** premiums are determined automatically from instrument lists and roster order, with string-quartet auto-detection and overrides checked against the CBA amount. - **Rate updates:** every rate and percentage is stored per period, and the rate table itself is validated. - **Conflict checks:** it has a much broader consolidated Compliance register. This covers sound checks that are not exactly 1 or 2 hours, a rolling 8-week sound-check frequency, a meal-break input, 7.4 waiver checks including location, and roster-consistency checks. A has real flaws: - **False audit error:** the audit check multiplies a coverage row by a roster column inside SUMPRODUCT. This yields 10 instead of 1, so audits are always flagged "must be assigned to exactly one musician". The rendered Services page shows this message on S001/S002 even though status reads OK. - **Guarantee basis:** the weekly guarantee is compared against performance plus rehearsal plus sound-check pay. Weekly players therefore get $2,008.48 rather than the $2,008.50 guarantee, and the settings offer no performances-only option. - **Unimplemented promise:** the Assumptions tab claims an audits-per-book check that is not implemented. B is clean and accurate: performances are max(per-show pay, guarantee), its flags work, and its rendered pages are consistent. However, it is a single-week template. First-local, quartet, electronic and doubling-count designations are all manual. Sound-check frequency is checked only within the week, there is no meal-break input (so any rehearsal over 5 hours is flagged as a violation), and its engagement-length input is unused. On balance, A's far broader robustness and compliance coverage outweigh its contained bugs.