Pucinneli Supermarkets is similar to most other grocery store chains that use the 12-digit UPC code on packages to check out customers. For a variety of reasons, it is important that the computer systems using these codes validate them for accuracy and completeness. To perform this task, UPCs automatically include a check digit that can also be computed from the other digits in the code. The system works as follows: 1. The UPC includes all numbers in the bar code. An example is 064200115896. The length is 12 as required. 2. The check digit is the last digit in the codefor example, a 6 for this example. 3. Starting on the left, add the digits up to, but not including, the check digit in the odd-numbered positions (i.e., the numbers in the first, third, fifth position, etc.) together. Multiply this sum by three. 4. Add the digits up to, but not including, the check digit in the even-numbered positions (i.e., the second, fourth, sixth numbers, etc.). 5. Add the values found in steps 3 and 4.

6. Examine the last digit of the sum. If it is 0, the computed check digit is also 0. If the last digit of the result is not zero, subtract this digit from 10. The answer is the computed check digit and must equal the last number in the UPC code. To illustrate, suppose the UPC barcode is 064200115896 as shown. The steps for this example are: 1. The length is 12 as required. 2. The last value is 6 so this is the check digit. 3. Add the odd-position digits: 0 + 4 + 0 + 1 + 5 + 9 = 19. Multiply this sum by 3: 19 3 = 57. 4. Add the even-position digits: 6 + 2 + 0 + 1 + 8 = 17. 5. We add these two values together. The sum is 57 + 17 = 74. 6. The last digit in this sum is 4, the check digit is not 0, and we, therefore, subtract 4 from 10 to get 6. The computed value of 6 found in step 5 matches the check digit 6 in step 2, and we, therefore, conclude that this UPC code is valid.

UPC code: Length test: Check digit is: 064200115896 OK 6 Sum of odd digits: Sum of even digits: Odd digits x 3 Sum: Last digit: Computed check digit: 19 17 57 74 Conclusion: Valid number Requirement Develop a spreadsheet to perform the tests described here and test your model with the follow- ing UPC codes. (1) 639277240453, (2) 040000234548, (3) 034000087884, (4) 048109352495, and (5) one UPC value of your own choosing (drawn from something you own or see). For each num- ber, indicate whether the UPC number is valid or invalid. Include a print out of all your work, plus a copy of the formulas for at least one of your tests. Hints: (1) You should enter your initial UPC code as "text"not a number. (2) You can use Excel's LEN function to perform the desired length test and Excel's IF function to test whether the entered value passes it. (3) You should use Excel's MID function to parse each digit for these computations. (4) You can use Excel's IF test again to reach the conclusion (e.g., "Valid number"). UPC code: Length test: Check digit is: 064200115896 OK 6 Sum of odd digits: Sum of even digits: Odd digits x 3 Sum: Last digit: Computed check digit: 19 17 57 74 Conclusion: Valid number Requirement Develop a spreadsheet to perform the tests described here and test your model with the follow- ing UPC codes. (1) 639277240453, (2) 040000234548, (3) 034000087884, (4) 048109352495, and (5) one UPC value of your own choosing (drawn from something you own or see). For each num- ber, indicate whether the UPC number is valid or invalid. Include a print out of all your work, plus a copy of the formulas for at least one of your tests. Hints: (1) You should enter your initial UPC code as "text"not a number. (2) You can use Excel's LEN function to perform the desired length test and Excel's IF function to test whether the entered value passes it. (3) You should use Excel's MID function to parse each digit for these computations. (4) You can use Excel's IF test again to reach the conclusion (e.g., "Valid number")