全年生产数据与图表看板

A preserves the supplied Week 1 outputs, leaves later weeks unentered, and connects its week dropdowns to selected-period and YTD calculations. Its summaries match the source: 38,880 total units, Operator 1 leading with 4,720, and Machine 3 with 13,300. B fills Weeks 2–48 with unsupported production figures and includes them in a 1,898,790-unit YTD leaderboard, materially misrepresenting the supplied data. However, A’s rendered Average Output and Total Output cells show Err:507 despite correct text-extracted values, so its full calculation functionality cannot be confirmed.

B better preserves the supplied production data: it populates Week 1, leaves later weeks available for entry, and uses formulas distinguishing the selected period from YTD through the end-week selector. Its cached Week 1 and YTD totals are correctly 38,880 units. A invents production for Weeks 2–48, inflating YTD to 1,898,790 units, and its validated selectors drive a separate snapshot rather than the displayed pivot summaries. Both contain the required worksheets and four chart objects. However, B’s rendered Average Output and Total Output columns show Err:507 despite correct cached values, leaving its calculation reliability unresolved.

Submission B is the better deliverable. Submission A's required auto-calculated columns fail in its rendered output, while B's calculations, formatting and Week 1 KPIs work as required. **Submission A** - **Strengths:** The design is ambitious and well aligned with the task. - The data sheet has 432 rows, with only Week 1 populated and weeks 2–48 left blank for future entry. That is an honest treatment of a tracker. - There are 4 native-looking PivotTables, filtered through 'Selected Period' and 'YTD Period' helper columns. - Those helper columns link the start/end week validation lists (B4/D4) to the pivots. - The 4 charts are dynamic. - The KPI block's cached values are correct: 38,880 units; Operator 1 at 4,720; Machine 3 at 13,300; 4,320 per operator. - **Core failure:** Average Output, Total Output and Days Entered use stored formulas such as `[@[Monday Output]:[Friday Output]]`. - Every rendered data page shows Err:507 in the Average Output and Total Output columns. These are the two required auto-calculated fields. - The `@` shorthand is not part of the file-format grammar for structured references, so desktop Excel may also flag or repair these formulas. - Every dashboard KPI and chart helper depends on these columns (via WeeklyTotals, WeeklyAverages and DaysEntered), so the failure cascades. - With error values, the top/bottom conditional formatting cannot be seen working. **Submission B** - **Strengths:** - The data sheet renders cleanly. It uses standard `=ROUND(AVERAGE(E2:I2),1)` and `=SUM(E2:I2)` formulas, and Week 1 matches the source exactly. - Conditional formatting is visible: a colour scale plus red highlighting of bottom performers. - The dashboard has pivot-style reports for (a) operator, (b) machine, (c) shift average and (d) YTD leaderboard. - Start/end week list validation is applied (B5/D5), driving a working week-range snapshot. - It has 4 charts. - Its fixed Week 1 KPI table matches the request item for item, with correct results: 38,880 total; Operator 1 / 4,720; Machine 3 / 13,300; 4,320 average per operator; day 58.4% / night 41.6%. - **Weaknesses:** - It fabricates output for weeks 2–48 without clearly labelling it. As a result, the YTD leaderboard and YTD chart show about 1.9M fictional units rather than Week 1-based YTD. - The validation lists are not tied to the pivots, which have separate Week # filters. **Verdict:** These weaknesses are less damaging than A's broken core calculations.

Submission A is a working tracker. Its data sheet has 432 rows (48 weeks × 9 operators). The Week 1 values match the input file, and each operator keeps a fixed machine line and shift throughout. Average and Total use plain formulas (=ROUND(AVERAGE(E2:I2),1) and =SUM(E2:I2)). The rendered pages show correct values with visible conditional formatting: a colour scale plus red highlighting of bottom performers. The Dashboard has Start/End week drop-downs (data-validation lists over 1–48) and four PivotTable layouts: operator, machine, shift average, and a YTD leaderboard. Its Week 1 KPI block is formula-driven and correct: 38,880 total units; Operator 1 at 4,720; Machine 3 at 13,300; 4,320 per operator; day/night shares from 22,715 and 16,165 units. It also has a week-range snapshot and four charts. Its main weakness is that Weeks 2–48 are filled with invented output and not clearly labelled as sample data, so the YTD figures (1,898,790 units) are fabricated rather than based on Week 1. Submission B has a more elaborate and more honest design: future weeks are left blank, the drop-downs drive 'Included' helper columns behind the pivots, and conditional formatting picks the top and bottom within each week. However, its Average, Total and Days Entered columns use unqualified '[@[Monday Output]:[Friday Output]]' structured references. The file check flags these as possible external formulas, and every rendered Average and Total cell, including Week 1, shows Err:507. Because the dashboard's helper columns, KPIs and pivot scope depend on these columns, the core 'automatically calculated' requirement and the downstream dashboard logic appear broken. That is a fatal usability defect for a meeting-ready tracker, so A is the better deliverable.