There are four parts to this assignment, and this should be completed and submitted in Excel as four different worksheets in one Excel file submitted to dropbox in Blackboard. 1. Worksheet #1: Random Numbers (6 points): Using Excel, generate a listing of 50 random numbers between 455 and 1165. a. Instructions for generating random numbers are on page 3 of this handout. b. You need to select Uniform to enter the range, leave variable blank, 50 random numbers. c. Please insert numbers 1 through 50 in front of your 50 generated random numbers OR have them in rows 1-50 of the spreadsheet so that we can both verify quickly that you have selected 50 numbers. d. Please sort the numbers in ascending order!! Remove decimals! You can just format for comma, reduce decimals to 0. e. This should be the first tab/worksheet in your Excel file. 2. Worksheet #2: Systematic Sampling (7 points): a. The population of sales invoices used during the year includes invoice numbers 7206 through 10703. b. Design a systematic sampling plan to select 50 invoices from this population. c. Show the calculation of the interval for this selection. d. You have to randomly pick which invoice you choose to start with, and state that random start for me. Remember, your random start must be within your interval that you calculated in c. e. Number the responses 1 through 50 so that we can both clearly see that you have 50 selections. f. This is NOT just generating random numbers again like in #1. g. This should be the second tab/worksheet in your Excel file. 3. Worksheet #3: Statistical Sampling (6 points) (use Tables 15-8 and 15-9 in textbook): a. Using attribute sampling, what would be the appropriate sample size for a 5 percent ARO, 4 percent TER and an EPER of 0.75? b. If, as a result of the testing performed on this sample, TWO exceptions were noted, what would be the approximate CUER? Note: You have to use a range of CUER since your sample size is between two sample sizes on Table 15-9. Would it be appropriate to conclude that the population is acceptable? Why or why not? c. This information should be the third tab/worksheet in your Excel file. 4. Worksheet #4: CMA (Cumulative Monetary Amount) sampling problem (which is another name for Monetary Unit Sampling as discussed in your text pp 586 595) (6 points, instructions and further information on pages 4 - 6 of this handout and/or in Chapter 17 of your text): Select 8 items from the following population: $1,545 1 10,382 2 7,981 3 6,492 4 26,302 5 2,300 6 3,551 7 47,526 8 916 9 4,312 10 7,693 11 $119,000 Compute your interval and use a random start of $8,000. Show the items selected and the selected items, if any, that count as more than one selection. The explanation and instructions below should help you understand this very well. This should be the fourth tab/worksheet in your Excel file. Submission details Submit your ONE Excel file (see above, four worksheets in one file) to the dropbox in Blackboard by the posted deadline. You can double-click each tab/worksheet name (the tab down on bottom) and rename to #1 through #4 or whatever you want. Not required, but looks nice! If late, all points are lost. No hard copies will be accepted. Instructions for Random Number Generation using Excel: 1. Open a blank worksheet if you dont have one open. 2. Click on the File button in the upper, left-hand corner of the screen 3. Select Options 4. Select Add-Ins 5. Manage Excel Add-Ins at bottom, and click GO 6. Make sure that Analysis ToolPak and Analysis ToolPak-VBA are checked, and click Ok 7. If asked to install, YES; if not asked to install, its already installed. 8. Select Data tab from the ribbon at the top of the screen 9. Select Data Analysis (on the far right) and then Random Number Generation 10. Leave variables blank and fill in 50 for number of random numbers. 11. Use pull down for Distribution, select UNIFORM, and enter the given range of numbers. 12. Click OK, and your numbers will appear. It will automatically create a worksheet for you and put the numbers in fields A1:A50. Thats PERFECT, dont move them. 13. Format them to take out the decimals (reduce decimal places to 0). 14. Sort in ascending order (lowest to highest). 15. You can double-click on the tab name down at the bottom and rename it to be Random numbers or #1 or something. You dont have to. CUMULATIVE MONETARY AMOUNT SAMPLING Information and Instructions for #4 of the assignment Cumulative monetary amount sampling is a type of monetary unit sampling commonly used for tests of balances sample selection. For the purpose of this illustration, we will assume that we wish to sample the balance in the raw materials inventory account in order to select individual items to test-count (physical examination). The raw materials inventory account balance as of the balance sheet date is $11,935. We have already determined that a sample size of 5 items is appropriate for our test procedures. The first step in selecting individual items for testing is to calculate a selection interval. The formula for this calculation is: Population / sample size Applying this formula, the interval for our example will equal $2,387 ($11,935 / 5). Remember, with monetary unit sampling the population is the dollar amount of the account that you are testing, in this case, inventory. The second step is to select a random start. This number must be lower than 2,387, the sampling interval. For example, you can select a random start from the serial number on a dollar bill. A random number table or random number generator would also be appropriate. For our example, we will use a random start of 1,834. Based on the above information, therefore, we will select five items for observation beginning with the item containing the 1,834th dollar and continuing with every 2,387th dollar after that. We are trying to find which of our inventory items (see the list of 9 items below) we get to the 1,834th dollar looking at the individual amounts and the resulting running total. Take a look: The following is a list of the individual items in the population: Item # Amount Cumulative (Running) Total 1 693 693 2 712 1,405 3 1,411 2,816 4 2,947 5,763 5 60 5,823 6 365 6,188 7 4,600 10,788 8 590 11,378 9 557 11,935 Total 11935 So in which item do we reach the 1,834th dollar (our random starting point as listed above)? In item 3! Cumulative total of 2nd item is only 1,405, so the 3rd item is where we reached the 1,834th item. Heres a trick to find the intervals easily, because, remember, we are choosing every 2,387th dollar after the 1,834th point (dollar). Follow these steps: Begin sampling by entering the random start into your calculator as a negative number, so enter -1,834. Add the first item in the population, 693. The result, -1,141, is still a negative number. Continue adding population items until the result is positive, so +712 (item 2), still negative, and +1,411 (item 3), and now a positive number, 982. So this is our first selection, Inventory item #3. Do NOT clear your calculator, leave that positive 982 in there!! Now subtract the interval, 2,387 from the 982 that you had in the previous step. The result will be -1,405. Add item 4, 2,947. The result is positive, 1,542. Thus, item 4 is our second selection. Subtract the interval again and continue adding population items. The next item which will result in a positive number is item 7. This is our third selection. Now it will be necessary to subtract the interval twice in order to get a negative number (-594). This means that item 7 will count as two selections. Continue adding population items and you should reach a positive number in item 9, which will be our final selection. To summarize, our resulting sample of 5 items is as follows: Items 3, 4, 7 (counts as two selections), and 9 The advantages of this sampling technique are as follows: 1. The technique is very easy to use and requires little in the way of statistical knowledge. 2. The result is a random sample which can be statistically evaluated for purposes of determining the amount of projected error in the population. 3. Though random, the technique does, due to the nature of the process, select the largest items for testing. This is useful since tests of balances are generally used for overstatement testing. 4. The fact that one item may count as more than one selection in the sample has the advantage of reducing the amount of testing necessary. 5. The process as illustrated above can be easily adapted to a computer program which will perform the sampling on a clients computerized records.