Extra Credit Assignment For MGNT 3020 Chapters 9 & 10 ✓ Solved

Extra Credit Assignment for MGNT 3020 Chapters 9 & 10 Name:

______________________ Date: _______________________ Problem #1. Create charts using Excel or by hand: 1. Given the data above, create a histogram of the employee ages in the plant in five-year increments. 2. Given the data above, create a Pareto chart of employee ages in the plant in five-year increments. 3. On the histogram, draw and label a line showing the mean age of all employees (show calculation below the chart). 4. On the Pareto chart draw and label a line showing the approximate median age of all employees (show calculation below the chart). 5. Create a second Pareto chart of the years of service of the employees in the plant in five-year increments. 6. Create a second histogram of the years of service of the employees in the plant in five-year increments. 7. On the second histogram, draw and label a line showing the mean years of service of all employees (show calculation below the chart). 8. On the second Pareto chart draw and label a line showing the approximate median years of service of all employees (show calculation below the chart). For all charts, clearly label the X and Y axes. Problem #2. Below find the 42-week production output of a plant showing: (1) week of production, (2) number of production parts planned for the week, (3) number of shifts needed to create the actual output that week, (4) average output per shift for the week, and (5) total number of parts actually produced that week. Create charts using Excel or by hand to analyze production performance across the 42 weeks.

Paper For Above Instructions

Introduction and approach

This paper explains step-by-step how to complete Problem #1 (employee age and years-of-service histograms and Pareto charts, with mean and median lines) and Problem #2 (charting and analyzing 42 weeks of production output). Where raw numeric lists are required, the method below assumes the user has a tabular dataset. The paper provides concrete formulas, Excel instructions, recommended chart types, charting best practices, interpretation guidance, and sample calculations so that charts produced by students meet the assignment requirements (Montgomery, 2009; Heizer & Render, 2016).

Problem #1 — Histograms and Pareto charts (ages and years of service)

Step 1: Prepare the data table. Create two columns: "Age" and "Years of Service." Each employee is a row. Verify no missing or erroneous values, and sort if helpful (Evans & Lindsay, 2014).

Step 2: Define five-year bins. For ages, typical bins are 20–24, 25–29, 30–34, etc. For years of service, use 0–4, 5–9, 10–14, etc. In Excel, create a "Bins" column listing bin upper limits (e.g., 24, 29, 34...). Use the FREQUENCY or COUNTIFS function to compute counts per bin (Microsoft, 2023).

Step 3: Create the histogram. In Excel: select the Age data and use Insert → Chart → Histogram, or use a bar chart built from the bins and frequencies. Ensure the X-axis labels show the five-year intervals and the Y-axis shows "Number of Employees" (Tufte, 2001). Add a vertical line to indicate the mean age: compute mean = SUM(Age)/N and add it to the chart as an added series plotted on a secondary axis or as a data label line (Levine et al., 2017).

Sample mean calculation (formula): Mean age = (Σ age_i) / N. For example, if ages = {25, 30, 35, 40, 50} mean = (25+30+35+40+50)/5 = 36. (Levine et al., 2017)

Step 4: Create the Pareto chart for ages. Produce a descending-frequency bar chart (bins on X, counts on Y) and compute cumulative percentages. Add a cumulative percentage line and identify the median approximate value. Median location = the value at the 50th percentile. Using the cumulative percent line, draw horizontal/vertical guides to find the median bin, and annotate the chart accordingly (Juran & Godfrey, 1999).

Median calculation guidance: If N is odd, median is the middle value; if even, average the two middle values. When data are binned, find the bin containing the 50% cumulative frequency and interpolate within the bin if needed (Montgomery & Runger, 2014).

Step 5–8: Repeat steps 2–4 for "Years of Service." Create a second Pareto (descending counts by service-bin with cumulative percentage) and a second histogram with a mean years-of-service line. Label axes: X-axis = "Years of Service (5-year bins)"; Y-axis = "Number of Employees." Always show the numeric mean and median calculations below or beside each chart (Heizer & Render, 2016).

Problem #2 — Analyzing 42-week production output

Step 1: Clean and structure the weekly production table. Columns should include Week, Planned Parts, Shifts Used, Avg Output per Shift, and Total Produced. Resolve any missing or inconsistent formats.

Step 2: Recommended charts. To analyze performance across 42 weeks, create the following visuals:

• Line chart of Total Produced per Week (time series): shows trends, seasonality, and large deviations (Montgomery, 2009).
• Bar chart of Planned vs. Actual per Week: side-by-side bars reveal under- or over-performance against plan (Heizer & Render, 2016).
• Line chart of Avg Output per Shift over time: indicates productivity per shift and possible drift.
• Histogram of Weekly Totals: shows distribution of weekly outputs and identifies common output ranges.
• Control chart (e.g., X̄ and moving range or Individuals chart) of weekly totals: assesses whether the process is in statistical control and flags out-of-control weeks deserving root-cause analysis (Montgomery & Runger, 2014).

Step 3: Excel steps and annotations. Use Insert → Chart → Line for time-series charts. To create a control chart, calculate the average weekly total (X̄) and standard deviation (σ) or use average moving range for Individuals chart; plot centerline (X̄) and control limits (X̄ ± 3σ). Annotate weeks with holidays or rework to explain deviations (Evans & Lindsay, 2014).

Step 4: Interpretation guidance. Look for: trends (consistent upward/downward), shifts associated with bank holidays or rework, weeks where additional shifts increased total but decreased average per shift (efficiency loss), and outliers. Use Pareto thinking to prioritize causes for low-output weeks (80/20 principle) (Juran, 1999).

Reporting and required calculations

For each histogram and Pareto chart, include: the bin definitions, frequency table, mean calculation (explicit formula and numeric result), median calculation (value or interpolated bin), axis labels and units, and short interpretation (one or two sentences) describing what the chart shows. For the production charts, include the mean weekly production, standard deviation, and any control limits used for the control chart (Montgomery, 2009).

Best practices for presentation and grading criteria

Ensure all charts have titles, axis labels, legends (if needed), annotations for mean/median lines, and written captions summarizing the key insight. Export charts as high-resolution images or embed them into the report. Provide raw frequency tables and show the arithmetic work under each chart as requested. Present charts in a logical order: ages (histogram, Pareto), years of service (histogram, Pareto), then production analysis (time series, histograms, control charts) (Tufte, 2001).

Conclusion

Following the steps above will satisfy the assignment: create five-year-bin histograms and Pareto charts for age and years of service with mean and median annotations, and produce a set of production charts for the 42 weeks that highlight performance, variability, and exceptions. Use Excel functions (AVERAGE, MEDIAN, STDEV.P, FREQUENCY, COUNTIFS) to compute required statistics and add lines or series for mean/median on your charts (Microsoft, 2023). Interpret results succinctly and include numeric calculations beneath each chart for full credit.

References

• Evans, J. R., & Lindsay, W. M. (2014). Managing for Quality and Performance Excellence. Cengage Learning.
• Heizer, J., & Render, B. (2016). Operations Management (11th ed.). Pearson.
• Juran, J. M., & Godfrey, A. B. (1999). Juran's Quality Handbook. McGraw-Hill.
• Levine, D. M., Stephan, D. F., Krehbiel, T. C., & Berenson, M. L. (2017). Statistics for Managers Using Microsoft Excel. Pearson.
• Montgomery, D. C. (2009). Introduction to Statistical Quality Control. Wiley.
• Montgomery, D. C., & Runger, G. C. (2014). Applied Statistics and Probability for Engineers. Wiley.
• Microsoft. (2023). Create a histogram in Excel. Microsoft Support. https://support.microsoft.com/
• Tufte, E. R. (2001). The Visual Display of Quantitative Information. Graphics Press.
• Wilkinson, L. (2005). The Grammar of Graphics. Springer.
• National Institute of Standards and Technology (NIST). (2012). Engineering Statistics Handbook. NIST/SEMATECH.