Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.9 Manufacturing: Production Attainment and Rejects

By Last updated:

Example first. Raja runs production planning at three fictional packaging plants and wants yesterday's output and rejects in one view.

Manufacturing project — in 30 seconds

What: A Manufacturing tracker: one Table tblShifts, the calculated column GoodUnits, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Manufacturing report: Total Produced, Attainment, Reject % are the numbers a manager reads first.

Tiny example: From 6 rows: Total Produced = 22,990, Attainment = 95.8%, Reject % = 2.5%.

First look at the data (before → after):

Manufacturing project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

Line GoodUnits
L1-A 4,724
L1-B 4,472
L2-A 3,989
L2-B 3,571
L3-A 2,896
L3-B 2,764

Expected result — KPI cells (expected results)

KPI Formula Result
Total Produced =SUM(tblShifts[Produced]) 22,990
Attainment =SUM(tblShifts[Produced])/SUM(tblShifts[Planned]) 95.8%
Reject % =SUM(tblShifts[Rejected])/SUM(tblShifts[Produced]) 2.5%
Before table and formula as text

Before — Table tblShifts (fictional sample rows)

Line City Planned Produced Rejected
L1-A Pune 5,000 4,820 96
L1-B Pune 5,000 4,610 138
L2-A Nashik 4,000 4,050 61
L2-B Nashik 4,000 3,720 149
L3-A Sambhaji Nagar 3,000 2,940 44
L3-B Sambhaji Nagar 3,000 2,850 86

Formula / click path

GoodUnits: =[@Produced]-[@Rejected]
Total Produced: =SUM(tblShifts[Produced])
Attainment: =SUM(tblShifts[Produced])/SUM(tblShifts[Planned])
Reject %: =SUM(tblShifts[Rejected])/SUM(tblShifts[Produced])

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblShifts in Table Design › Properties › Table Name.
  2. Add the column GoodUnits with =[@Produced]-[@Rejected]; the Table fills it down.
  3. On a KPI sheet type =SUM(tblShifts[Produced]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and GoodUnits in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Line.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a production sheet: good units per line, total produced, attainment (produced ÷ planned), reject % (rejected ÷ produced), good units by city, a chart and a Line slicer.

Calculated column formulas:

Column Formula
GoodUnits =[@Produced]-[@Rejected]

KPI cells (check your numbers):

KPI Formula Result
Total Produced =SUM(tblShifts[Produced]) 22,990
Attainment =SUM(tblShifts[Produced])/SUM(tblShifts[Planned]) 95.8%
Reject % =SUM(tblShifts[Rejected])/SUM(tblShifts[Produced]) 2.5%

PivotTable (expected):

City Sum of GoodUnits
Nashik 7,560
Pune 9,196
Sambhaji Nagar 5,660
Grand Total 22,416

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Line and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Manufacturing_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of GoodUnits?
  2. Filter the Table on City = Pune (Data › Filter): what is Total Produced for Pune alone?
  3. Add the Clustered Column PivotChart and the Line slicer to your Dashboard sheet.
Answer

Pune (9,196). For Pune only (2 rows): Total Produced = 9,430. The chart shows 3 bars, one per city.

Ravindra Bagale's Tip

Check Total Produced by hand for two rows before you trust the dashboard. If your hand answer and the cell differ, the formula range is usually wrong.

Loading practice tasks...

  1. Total units made (Easy): Add up Units made in D2:D11.
  2. Average rejects (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Line A (Easy): How many rows have Line "Line A"?
  4. Largest units made (Easy): The largest value in D2:D11.
  5. Units made for Line A (Medium): Total of D2:D11 where B is "Line A".
  6. Line A in Pune (Medium): Total of D2:D11 where B is "Line A" and C is "Pune".
  7. Best in Line A (Medium): Largest E2:E11 value where B is "Line A".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Mateo Garcia (Medium): The Units made for "Mateo Garcia" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Line A (Hard): Share of D2:D11 that is "Line A", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →