Question

1 Approved Answer

Posted on Jul 14, 2024

please show help with #2 & 3 B 6 1 2 3 4 State MA RI MA MA MA RI MA VT NH MA RI

please show help with #2 & 3

B 6 1 2 3 4 State MA RI MA MA MA RI MA VT NH MA RI MA 5 6 7 B 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Customer St Anne's Hospital Good Shepherd Med Laboratory Gregg's Pharmacy Kort Hospital Foster Blood Tests Oaklawn Pharmacy Forgary Labs Nepco Labs St. Paul's Hospital Biotech Medical Lab Depasquale Pharmacy Miriam Hospital Miriam Women's Hospital Goodkids Clinic Bradley Hospital Kathy's Clinic Landmark Medical Center EMG & EEG Labs Luke's Medical Center Morey's Hospital Children Doctor's Hospital De Bellis Pharmacy Mayor Clinic Quincy Lab Good News Medical Center Bayshore Pharmacy Butler Hospital Hope Hopital Children's Hospital New Life Clinic Braintree Hospital St. John's Hospital Cape Psych Center Kindheart Hospital Hospital for Children Good Samaritan Hospital Woman & Infants SL Luke's Clinic St Joseph's Hospital Mom's Clinic Angelic Hospital Lady's Clinic Intants Clinic VIP Hospital Goodwill Hospital VT CA NH CA CA MA VT NH RI RI MN MA D Sales Rep SR3 SR2 SR2 SR1 SR1 SR4 SR1 SR4 SR2 SR3 SR1 SR2 SR3 SR1 SR2 SR4 SR3 SR4 SR2 SR4 SR3 SR1 SR2 SR4 SR2 SR3 SR1 SR4 SRI SR3 SR2 SR4 SR3 SR3 SR1 SR4 SR1 SR4 SR3 SR1 SR2 E Balance Owed by Customers 41,009.53 538.62 22,05270 17,987.44 32,594.27 4,513.21 52,890.08 3.274 25 1,067.45 32.799.12 14 214.50 14,800.44 8.900.00 27.800.00 9.430.72 11,318.97 22.630.79 12,583 97 15,067.54 520.65 15,890.00 12,715.35 573,34 21,565.00 11.400.51 6,010.36 11.215.67 37.500.45 20,002.45 21.620.39 36,609 80 15,623.87 31.509.10 21.007.45 23.875.00 12.847.10 7,915.99 13.290.62 47.113.50 19,050 31 38.210.84 19.210.87 23.431.08 17.892.05 34 712.50 YTD Sales 2,431.80 3,279.89 3.77128 4,120.74 4,275.56 517626 6,670.41 6,866.28 10,452.50 11,927.84 18,000.00 20,200.00 23,009.54 23,784.90 24,500.00 25,358.74 29,000.00 31,685.19 32,340.32 32,760.00 34.210.50 35,300.00 39,745.90 40,540.00 43,250.00 44,140.87 45,000.00 45.238.75 45,400.00 46.209.81 48 208.00 53.234.51 55,173.24 56,750.00 58,946.78 59,500.00 59,600 24 61,023.90 64.000.00 67.820.00 70,845 33 75.921.06 78.091 28 96,361 23 98 31758 RI MA NH CA MA CA MA MA CA RI RI RI CA SRI 44 45 46 SR2 SR2 CA SR1 45 SA Medical Technology, Inc. distributes supplies to hospitals, medical labs, and pharmacies. Records of all customer and accounts receivable data are available to department managers on the company's mainframe computer. Tom, the manager of credit and collections, was reviewing this data and noticed that the outstanding balance of several customers in California (CA) and Massachusetts (MA) appeared to be higher than the average customer balances. He wants to study these accounts in more detail with the goal of creating a plan to bring these accounts closer to the average balance. Tom was able to download the necessary data from the company's mainframe, and he set up an Excel list. Complete the following tasks: (You may choose to use Access for this project.) 1. Sort the list by year-to-date (YTD) sales (descending) for each sales rep (ascending) in each state (ascending). Print the sorted list. Include column heading and row number for your output. (5 points) Note that for questions 27 1. Each question is independent of each other; 2. Indude column heading and row number for each output 2. Extract all customers in CA or MA, and they are contacted by sales reps SR1 or SR2 or SR3. Print field names and cell formulas in the criterion section, and print the extracted list on two separate sheets. (10 points) 3. Assume the YTD Sales are normally distributed with average and standard deviation approximated by the average and standard deviation of the sample in this project. Extract all the sales reps in CA or MA, and their YTD Sales are either lower than 1.64 standard deviations below sample average or higher than 1.64 standard deviations above sample average. Print field names and cell formulas in the criterion section and the list on two separate sheets. (20 points) 4. Assume the YTD Sales are norme distributed with average and standard deviation approximated by the awerage and stoodard deviation of the sample in this project. Extract the list of customers whose Balance Owed by Customers are between top 15 percentile and top 5 percentile of the sample and whose sales reps are either SRI or SR4 with YTD Sales higher than the third quartile of the sample. Print field names and cell formulas in all the criterion sections and the list on two separate sheets. (25 points) B 6 1 2 3 4 State MA RI MA MA MA RI MA VT NH MA RI MA 5 6 7 B 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Customer St Anne's Hospital Good Shepherd Med Laboratory Gregg's Pharmacy Kort Hospital Foster Blood Tests Oaklawn Pharmacy Forgary Labs Nepco Labs St. Paul's Hospital Biotech Medical Lab Depasquale Pharmacy Miriam Hospital Miriam Women's Hospital Goodkids Clinic Bradley Hospital Kathy's Clinic Landmark Medical Center EMG & EEG Labs Luke's Medical Center Morey's Hospital Children Doctor's Hospital De Bellis Pharmacy Mayor Clinic Quincy Lab Good News Medical Center Bayshore Pharmacy Butler Hospital Hope Hopital Children's Hospital New Life Clinic Braintree Hospital St. John's Hospital Cape Psych Center Kindheart Hospital Hospital for Children Good Samaritan Hospital Woman & Infants SL Luke's Clinic St Joseph's Hospital Mom's Clinic Angelic Hospital Lady's Clinic Intants Clinic VIP Hospital Goodwill Hospital VT CA NH CA CA MA VT NH RI RI MN MA D Sales Rep SR3 SR2 SR2 SR1 SR1 SR4 SR1 SR4 SR2 SR3 SR1 SR2 SR3 SR1 SR2 SR4 SR3 SR4 SR2 SR4 SR3 SR1 SR2 SR4 SR2 SR3 SR1 SR4 SRI SR3 SR2 SR4 SR3 SR3 SR1 SR4 SR1 SR4 SR3 SR1 SR2 E Balance Owed by Customers 41,009.53 538.62 22,05270 17,987.44 32,594.27 4,513.21 52,890.08 3.274 25 1,067.45 32.799.12 14 214.50 14,800.44 8.900.00 27.800.00 9.430.72 11,318.97 22.630.79 12,583 97 15,067.54 520.65 15,890.00 12,715.35 573,34 21,565.00 11.400.51 6,010.36 11.215.67 37.500.45 20,002.45 21.620.39 36,609 80 15,623.87 31.509.10 21.007.45 23.875.00 12.847.10 7,915.99 13.290.62 47.113.50 19,050 31 38.210.84 19.210.87 23.431.08 17.892.05 34 712.50 YTD Sales 2,431.80 3,279.89 3.77128 4,120.74 4,275.56 517626 6,670.41 6,866.28 10,452.50 11,927.84 18,000.00 20,200.00 23,009.54 23,784.90 24,500.00 25,358.74 29,000.00 31,685.19 32,340.32 32,760.00 34.210.50 35,300.00 39,745.90 40,540.00 43,250.00 44,140.87 45,000.00 45.238.75 45,400.00 46.209.81 48 208.00 53.234.51 55,173.24 56,750.00 58,946.78 59,500.00 59,600 24 61,023.90 64.000.00 67.820.00 70,845 33 75.921.06 78.091 28 96,361 23 98 31758 RI MA NH CA MA CA MA MA CA RI RI RI CA SRI 44 45 46 SR2 SR2 CA SR1 45 SA Medical Technology, Inc. distributes supplies to hospitals, medical labs, and pharmacies. Records of all customer and accounts receivable data are available to department managers on the company's mainframe computer. Tom, the manager of credit and collections, was reviewing this data and noticed that the outstanding balance of several customers in California (CA) and Massachusetts (MA) appeared to be higher than the average customer balances. He wants to study these accounts in more detail with the goal of creating a plan to bring these accounts closer to the average balance. Tom was able to download the necessary data from the company's mainframe, and he set up an Excel list. Complete the following tasks: (You may choose to use Access for this project.) 1. Sort the list by year-to-date (YTD) sales (descending) for each sales rep (ascending) in each state (ascending). Print the sorted list. Include column heading and row number for your output. (5 points) Note that for questions 27 1. Each question is independent of each other; 2. Indude column heading and row number for each output 2. Extract all customers in CA or MA, and they are contacted by sales reps SR1 or SR2 or SR3. Print field names and cell formulas in the criterion section, and print the extracted list on two separate sheets. (10 points) 3. Assume the YTD Sales are normally distributed with average and standard deviation approximated by the average and standard deviation of the sample in this project. Extract all the sales reps in CA or MA, and their YTD Sales are either lower than 1.64 standard deviations below sample average or higher than 1.64 standard deviations above sample average. Print field names and cell formulas in the criterion section and the list on two separate sheets. (20 points) 4. Assume the YTD Sales are norme distributed with average and standard deviation approximated by the awerage and stoodard deviation of the sample in this project. Extract the list of customers whose Balance Owed by Customers are between top 15 percentile and top 5 percentile of the sample and whose sales reps are either SRI or SR4 with YTD Sales higher than the third quartile of the sample. Print field names and cell formulas in all the criterion sections and the list on two separate sheets. (25 points)