6-37. Direct materials variance calculation and interpretation**Show Calculations in Excel**

(LO 2) (Data set from 'The Weight of Euro Coins: Its Distribution Might Not Be As Normal As You Would Expect," Ziv Shkedy, Marc Aerts, and Herman Callaert, Journal of Statistics Education, Volume 14, Number 2 [2006], used by permission of the authors.) 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 series 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 https://www.youtube.com/watch?v=rFDsSMDeV3w.) You are a quality control technician in 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 runs to determine whether the machines producing the metal disks are operating to specifications. Required:

(a) Assume that the standard cost of metal in the coin is .09. Calculate the direct materials quantity variance for each batch of the coin sample. Do the coins appear to be too heavy or too light? (You may find the COUNTIF and SUMIF functions helpful.) (b) Prepare an XY scatterplot of the data using Excel's charting tool. The horizontal axis should be 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 coins. (d) Calculate the average coin weight for each batch. (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 +/-.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 to make the Euro coins? What if the tolerance is +/-.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?