全年生产数据与图表看板

A correctly imports Week 1, maintains consistent operator/machine/shift assignments through Week 48, and leaves future output entries blank. Its weekly formulas and visible conditional formatting work, and its dashboard calculations reconcile to 38,880 units, Operator 1 at 4,720 units, Machine 3 at 13,300 units, and day/night contributions of 58.42%/41.58%. Its week-range selections are connected to the summary formulas. B instead fills Weeks 2–48 with unsupported production figures, contaminating YTD results. B’s selection formulas reference C5:C7, which contain explanatory text rather than numeric week values; moreover, its numeric week helper is populated only on Operator 1 rows, excluding the other operators from valid week filtering. B also contains a direct circular formula at C66 (=$C66/48). These are substantive tracker failures, regardless of presentation. A is clearly the better submission, although full compliance cannot be confirmed from the available evidence.

B preserves the supplied Week 1 outputs and fixed assignments across all 48 weeks, leaves later production ready for entry, and provides automatic totals/averages with visible weekly top/bottom highlighting. Its Start/End Week validation cells drive the live summary formulas. The displayed Week 1 KPIs are correct: 38,880 units, Operator 1 at 4,720, Machine 3 at 13,300, 4,320 units per operator, and 58.42% day versus 41.58% night. A instead populates Weeks 2–48 with unsupported production figures, contaminating YTD results. A’s selection logic is also defective: Dashboard C5:C7 contain explanatory text although D9:E9 use them as week bounds, and source helper column M contains week numbers only on Operator 1’s row each week. B is therefore substantially more usable, although full compliance cannot be confirmed from the available chart and PivotTable evidence.

Submission A is the stronger deliverable because its dashboard analysis works, while B's selectable-week section is broken. **Submission A** - **Data sheet:** It has 432 rows covering Weeks 1–48 for Operators 1–9. Machine and shift assignments stay the same in every week. Week 1 matches the input file exactly. - **Calculations and formatting:** Average and Total are formulas. Conditional formatting marks the top and bottom performer each week (Op1 944/4,720 in green, Op4 760/3,800 in red). - **Weeks 2–48:** These are blank entry rows, which is consistent with the task supplying only Week 1 and asking for charts based on Week 1. - **PivotTable sections:** The layout reads like real PivotTables (Week Filter=Selected, Week # (All), Grand Total) for (a)–(d). The values check out: machines 12,935/12,645/13,300, shift averages 908.6/808.25, and a correctly sorted leaderboard. - **Week selection and KPIs:** Start and End Week drop-downs drive a Week Filter column for single weeks or ranges. KPIs are correct: 38,880 total, Operator 1 at 4,720, Machine 3 at 13,300, 4,320 per operator, Day 58.4% and Night 41.6%. The workbook has 4 charts. **Submission B** - **Data sheet and KPIs:** Week 1 is correct, the color-scale formatting is visible, and the Week 1 KPIs are correct (they key on the "Week 1" text). - **Invented data:** Weeks 2–48 are filled with made-up production figures that are not labeled as sample data. As a result, the YTD chart and leaderboard reflect invented data rather than Week 1. - **No real PivotTables:** The "PivotTables" are formula tables. - **Broken week filtering:** D9 and E9 pull from C5, C6 and C7, which hold text labels ("← week number used…"), not week numbers. Every SUMPRODUCT filter therefore returns 0, and the averages and C9 evaluate to #VALUE!. - **Second defect:** Helper column M only has the week number in the first row of each week (M2, M11, …). So even with numeric inputs, only Operator 1's row would be counted. - **Outcome:** Requirement (a)–(c) for a selected week or range does not function.

Submission B is the better deliverable. Submission A has a broken core interactive section, while Submission B's components check out against the source data. **Submission A – what works** - The Week 1 data matches the input file. - Average and Total use formulas. - A red-to-green color scale is visible on both columns. - The fixed Week 1 KPI formulas are correct. - It has 4 charts and validation lists in B5:B8. **Submission A – what fails** - **No real PivotTables.** It uses SUMPRODUCT formula imitations instead. - **Week bounds resolve to text.** D9 and E9 take their values from C5, C6 and C7, which hold text labels such as "← week number used by the single-week view". - **Result: zeros and errors.** Every operator, machine and shift total in PivotTables 1–3 evaluates to 0, and the per-week and per-day average columns return #VALUE!. - **Helper column M is sparse.** It is filled only on the first row of each week (M2, M11, M20, …). Even with numeric bounds, only Operator 1's rows would match. - **Fabricated data.** Weeks 2–48 contain invented, unlabeled output figures. These feed the YTD leaderboard and the YTD chart, which contradicts "graphs based on the Week 1 data". That is inappropriate for a tracker presented at production meetings. **Submission B – what works** - The Week 1 data is exact. Weeks 2–48 are pre-built with fixed machine and shift assignments and blank entry cells, which suits a tracker. - Average and Total formulas handle blank rows. - Conditional formatting highlights the top (green) and bottom (red) performer each week, visible in the render: Operator 1 green, Operator 4 red. - The Dashboard has PivotTable-style blocks for all four required views. Each has "Week Filter = Selected" and "Week # (All)" filter fields plus Grand Totals. - The pivot values verify against the source data: - Operators: 4720 … 3800, totalling 38880. - Machines: 12935 / 12645 / 13300. - Shift average daily output: 908.6 / 808.25. - Leaderboard: correctly sorted. - Start/End Week drop-down validation lists drive the Week Filter column. - The KPI table holds correct cached values: 38880; Operator 1 at 4720; Machine 3 at 13300; 4320 per operator; Day 58.4% and Night 41.6%. - It contains 4 charts. - Requiring a pivot refresh after changing weeks is normal PivotTable behaviour.