The 1 Euro coin has a silver-colored inner core and a yellow outer ring. The center core is made from a copper nickel alloy, while the outer ring is made of nickel brass. The coins are minted through a writs of steps that combine two separate metal disks into the final coin that should weigh 7.5 grams. (You can watch a video of the process to make a 2 Euro coin at hllps://www.youtube.com/watchbwrPDMMINV3vz) You are a quality control technician ha the mint and are responsible for ensuring that the coins meet the official weight and size specifications. As part of this work you have weighed 2,000 coins from eight different production cans to determine whether the machines producing the metal disks are operating to specifications.

Alvarez Enterprises employs three categories of workers: apprentice, machinist I, and machinist D. Olivia McGuirk Alvarez's human resources man­ager, is currently reviewing last year's payroll information and wonders if it's time Od review standard labor rates. She has provided you the information and asked you IO conduct an analysis and provide a recommendation.

Required

a. Assume that the standard cost of metal in the coin is €0.00 per gram. Calculate the direct materials quantity variance for each batch of the coin sample. Do the coins appear to be to0 heavy or too light? (You may fold the COUNTIF and SUMIF functions helpful.)

b. Prepare an XY scatterplot of the data using Excel's charting tool. The horizontal axis should he coin weight and the vertical axis should be batch number. What do you notice about the coin weights?

c. Calculate the average weight of the coin. Round your answer to 4 decimal places.

d. Calculate the average coin weight for each batch. Round your answer to 4 decimal places. (You may find the AVERAGEIF function helpful.)

e. Recognizing that there may be some variation in the weight of the disks, assume that specifications allow the coins to be within +1/-0.01 gram of the target 7.5 gram weight. Based on the information you have gathered, do you believe there is a problem with the weights of the disks being used M make the Euro coins? What if the tolerance is +/-0.03 gram?

f. Given that the coins are a combination of two separate disks of different metals, how would you determine which of the two disks is outside of specification?