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

A implements substantially more faithful CBA logic: service-dated rates and percentages, per-person payroll categories, a linked scheduled three-hour call for sound checks, and two audits per eligible book with prior-payroll and traveling-substitute confirmations. B’s Services formulas and Assumptions explicitly accept any following call instead of the required three-hour call. Its claimed two-audits-per-book validation is absent from the actual checks, and its premium/doubling engine applies selected-week percentages throughout the all-week register. A still needs correction: its quartet test requires four active hired-string roster rows, so it cannot properly handle a quartet containing off-payroll touring strings. Its rendered validation and attendance pages also display numerous Err:507 cells despite successful-looking cached values. Neither is a fully reliable universal payroll template, but A’s calculations, conflict checks and expandable table-based structure make it the stronger foundation.

B has stronger CBA calculation and validation logic: service-date rate and percentage lookups, per-person payroll-category totals, audit eligibility and two-audits-per-book checks, meal-break placement checks, and a required three-hour sound-check anchor. A accepts any later call as a sound-check anchor, does not implement its claimed two-audits-per-book check, excludes synth-sub rehearsal/sound-check premiums by default, and applies the selected week's premium/doubling percentages throughout the multiweek register. However, B's rendered calculation and check columns repeatedly show Err:507. Its logic is preferable, but reliable Excel recalculation is not established by the supplied evidence.

Both workbooks address the brief. Each has an effective-dated rate table with editable percentages, roster and schedule inputs, CBA conflict flags, and per-person weekly totals by category. Both flag the 9:30am rehearsal start. **Submission A** is the more rigorous design: - Excel tables for setup, musicians, premium chairs, schedule and attendance. - Detailed Article 7 checks: sound-check anchor, lunch-pair rules, meal placement, audits counted per book. - A Weekly Detail and Weekly Payroll sheet; it is the only one that pays the $0.02 guarantee top-up so 8 shows reach $2,008.50. However, its calculated columns use `[@[...]]` structured references. On the rendered pages these show Err:507 throughout Rates Checks, Musicians Checks, Premium Chairs Level/Checks, Schedule Checks and the Attendance Date/Type/Musician/Checks columns. Dates also display in a broken format (', 1 6, 2025'). This puts the core conflict-highlighting and calculation logic at real risk of not working outside the generating environment. Other weaknesses: - Data entry is heavy: one attendance row per musician per service. - It is gated to NOT READY by approval flags by default. - The electronic premium on audit pay is hardwired. - Its default synth-sub reading folds the 25% into the 150% cap. **Submission B** renders cleanly and works with standard formulas and named ranges: - A Cover with instructions and live status. - Rates with 2025 values plus placeholder periods, and every percentage and rule parameter. - Settings with interpretation switches. - Services with extensive Article 7 checks (start time, minimum call, meal, 7.4 waiver, sound-check window and frequency). - An efficient Coverage grid and automatic first-local, quartet, electronic and doubling detection. - Weekly Payroll with per-person units and amounts for performances, rehearsals, sound checks, audits, premiums, doubling, guarantee top-up, vacation and gross ($30,665.27). - A Compliance register and an Article 11.4 Pay Statement. B has flaws too: - The audit-assignment check multiplies a row range by column ranges, producing a false error. This is already visible as an 'OK' status alongside an audit message on S001/S002. - The guarantee comparison includes rehearsal and sound-check pay, so it pays $2,008.48 rather than $2,008.50. - The synth-sub premium defaults to shows only, though a switch exists. - It is capped at 40 musicians. Overall B is the more usable and reliably functioning tool for a contractor.

Both workbooks have the right structure: effective-dated rate tables, roster and schedule inputs, CBA checks, and weekly totals by musician for each category. A wins because its engine is shown recalculating, it is easier to enter data into, and it is more complete. A (14 tabs): - Rates are looked up by service date, and every premium percentage is stored in the rate table. - Roster instruments come from a dropdown list and map automatically to the first-local, electronic and quartet premiums and to doubling. - The Services tab checks for: - rehearsals starting before 9:00 (prohibited) or before 10:00 (exigent circumstances); - the 3-hour minimum call; - more than 5 hours without a meal break; - the 6:30 pm daytime cutoff and the 50/10 breaks; - the Art. 7.4 paired-rehearsal waiver; - sound-check length (1 or 2 hours), window and frequency. - Musician × service attendance is a simple grid (blank means played). - Outputs: Weekly Payroll by person and category, a Pay Statement, Run Totals and a Compliance register. - The rendered Cover shows live results: $30,665.27, 0 errors and 1 warning. The 9:30 rehearsal is correctly flagged. A's defects: - The audit check ("assigned to exactly one musician") multiplies a row range by a column range, so it fires falsely. S001/S002 show the error text while their status reads OK. - Number formats in the Rates input table are shifted: vacation shows as $0.06 and the minimum call as 300%. - The guarantee test counts rehearsal and sound-check pay, and premiums exclude any guarantee top-up. - By default the synth sub's 25% premium applies only to shows, though a switch changes this. B's strengths: - It is rigorous: explicit local ranks, sound-check anchor validation, a 2-audits-per-book check, and a test for a rate change inside the week. - The guarantee correctly offsets performance pay only ($30,825.40). B's problems: - Nearly every calculated and Checks column uses [@[...]] structured references. In the rendered workbook these show Err:507 across Rates, Musicians, Premium Chairs, Schedule and Attendance. So its conflict highlighting and engine are not shown working live, even though cached values exist. - It needs one attendance row per musician per service and manual rank entry, which is heavy input. - It applies the 25% electronic premium to audit pay. - It has no multi-week register or pay statement.