Making Business Decisions: Excel Assignment

(dont screetshot the answer do a spreadsheet on chegg so i can edit if needed, thx)

1. Production Errors

Established in 2002, t-shirts.com has rapidly become the place to find, order, and save on T-shirts. One huge selling factor is that the company manufactures its own T-shirts. However, the quality manager for the production plant, Kasey Harnish, has noticed an unacceptable number of defective T-shirts being produced. You have been hired to assist Kasey in understanding where the problems are concentrated. He suggests using a PivotTable to perform an analysis and has provided you with a data file, the following is a brief definition of the information within the data file:

A. Batch: A unique number that identifies each batch or group of products produced.

B. Product: A unique number that identifies each product.

C. Machine: A unique number that identifies each machine on which products are produced.

D. Employee: A unique number that identifies each employee producing products.

E. Batch Size: The number of products produced in a given batch.

F. Num Defect: The number of defective products produced in a given batch.

2. Coffee Trends

College chums Hannah Baltzan and Tyler Phillips are working on opening a third espresso drive-through stand in Highlands Ranch, Colorado, called Brewed Awakening. Their original

drive-through stand, Jitters, and their second espresso stand, Bean Scene, have done well in their current locations in Englewood, Colorado, five miles away. Since Hannah and Tyler

want to start with low overhead, they need assistance analyzing the data from the past year on the different types of coffee and amounts that they sold from both stands. Hannah and

Tyler would like a recommendation of the four top sellers to start offering when Brewed Awakening opens. They have provided you with the data file for you to perform the analysis

that will support your recommendation.

3. Filtering SecureIT Data

SecureIT, Inc., is a small computer security contractor that provides computer security analysis, design, and software implementation for commercial clients. Almost all of SecureIT work requires access to classified material or confidential company documents. Consequently, all of the security personnel have clearances of either Secret or Top Secret. Some have even higher clearances for work that involves so-called black box security work. While most of the personnel information for SecureIT resides in database systems, a basic employee worksheet is maintained for quick calculations and ad hoc report generation. Because SecureIT is a small company, it can take advantage of Excels excellent list management facilities to satisfy many of its personnel information management needs. You have been provided with a sample worksheet, to assist SecureIT with producing several worksheet summaries. Here is what is needed:

1.One worksheet that is sorted by the last name and hire data.

2.One worksheet that uses a custom sort by the department in this order: Marketing, Human

Resources, Management, and Engineering.

3.One worksheet that uses a filter to display only those employees in the Engineering department with a clearance of Top Secret (TS).

4.One worksheet that uses a custom filter to display only those employees born between 1960 and 1969 (inclusive).

5. One worksheet that totals the salaries by department and the grand total of all department salaries. This worksheet should be sorted by department name first.

4. Filtering RedRocks Consulting Contributions

RedRocks Consulting is a large computer consulting firm in Denver, Colorado. Don McCubbrey, the CEO and founder of the firm, is well-known for his philanthropic efforts. He believes that many of his employees also contribute to nonprofit organizations and wants to reward them for their efforts while encouraging others to contribute to charities. He started a program in which RedRocks Consulting matches 50 percent of each donation an employee makes to the charity of his or her choice. The only guidelines are that the charity must be a nonprofit organization and the firms donation per employee may not exceed $500 a year.

Don has started an Excel file, to record the firms donations. Included in this file are the dates the request for a donation was submitted, the employees name and ID number, the name of the charity, the dollar amount contributed by the firm, and the date the contribution was sent. Don wants you to help him create several worksheets with the following criteria:

1. One worksheet that sorts the list alphabetically by organization and then by employees last name.

2. One worksheet that totals the contribution made per employee for the month of December.

3. One worksheet that sorts the list by donation value by lowest amount to highest amount.

ZOOM + BATCH 1 2 3 4 5 6 7 7 8 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 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 MACHINE 5 7 6 8 6 7 8 5 6 7 5 7 8 6 7 5 6 8 A 7 6 7 8 7 8 7 6 6 7 8 5 6 5 7 5 8 6 7 PRODUCT 10 20 30 30 20 20 30 10 10 30 20 30 10 30 30 10 10 10 10 20 10 20 30 30 20 20 20 30 30 30 10 10 20 30 30 w 20 10 30 10 10 20 20 30 20 0 30 20 20 30 30 10 10 10 10 20 10 20 30 30 20 20 20 30 30 30 10 10 20 30 20 10 30 30 30 20 10 10 EMPLOYEE 3333 5555 2222 4444 3333 1111 2222 3333 1111 5555 3333 4444 2222 3333 1111 1111 1111 4444 3333 2222 5555 0303 3333 4444 2222 5555 1111 2222 3333 4444 5555 4444 5555 3333 2222 4444 1111 3333 2222 4444 2222 2222 2222 2222 3333 1111 4444 2222 5555 4444 5555 1111 2222 3333 4444 5555 2222 1111 3333 2222 4444 3333 5555 4444 1111 2222 1111 2222 3333 4444 2222 5555 4444 5565 3333 1111 5555 BATCH SIZE 500 10000 5000 1000 1000 5000 10000 10000 5000 500 500 1000 5000 10000 10000 5000 500 500 500 1000 10000 10000 10000 5000 1000 1000 5000 10000 500 500 10000 500 5000 1000 1000 5000 500 10000 5000 1000 1000 5000 10000 500 500 10000 500 5000 1000 1000 5000 EON 500 10000 500 5000 1000 1000 5000 500 10000 5000 1000 1000 5000 10000 500 500 10000 500 10000 5000 1000 500 500 10000 5000 NUM DEFECTIVE 16 10 13 12 5 9 20 14 17 19 4 7 7 5 8 21 15 6 9 0 15 12 10 8 12 6 8 5 18 15 4 13 5 5 18 11 23 14 3 9 1 15 19 3 0 12 6 8 5 18 15 4 4 13 5 18 11 23 14 3 9 17 19 4 7 5 8 21 15 6 9 9 0 15 12 10 8 12 6 8 6 7 5 6 B 6 7 7 5 5 8 6 7 5 6 7 7 6 5 7 6 7 B 8 6 7 5 6 7 6 7 63 64 65 66 67 68 69 70 71 72 73 74 75 76 6 7 5 7 6 7 ZOOM + January February Key Jitters = North Bean Scene = South Product Month Store Revenue Regular January North 4320 Regular February North 4500 Regular March North 3528 3026 Regular April North 3960 Regular May North 3996 Regular June vane North 3960 Regular July North 3960 Regular August North 4230 Regular September North 4230 Regular October North 4275 Regular November North 4365 Regular December North 4455 vecer Regular South 3960 200 Regular South 4230 Regular March South 4230 Regular April South 4275 Regular May CE South 4365 Regular June South 4455 Regular July South 4128 Regular August South 4176 Regular September South 3936 October South 4090 November South 3936 Regular December South 4350 Decaf January North 102 4350 Decaf February N. North 4350 Decaf ML March North 4350 Decaf ML April North 3610 Decaf May North 3640 Decaf NAL June North 3610 Decaf NAL July North 2210 Decaf August North 2400 2430 Decaf September North 2700 2430 Decaf October North 2460 Decaf Decal November North 2540 Decaf Boar December North veco 2310 Decaf Boar South 2620 Decaf Boar February South 2450 Decaf March South 2430 Decaf April South 2480 " Decaf May South 2540 Decaf way June South 2610 Decaf 2018 July South 2340 Decaf August South 2380 Decaf September South 2540 Decaf October South 2310 Decaf November South 2400 Regular Regular January ZOOM + February Decaf Decaf Decaf Decaf Decaf Decaf Decaf Decaf Decaf Decaf Decaf Decaf Local French Roast French Roast French Roast Frenc French Roas! French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast French Roast one French Roast Vanilla Vanilla Menu Vanilla wa Vanilla wa Vanilla Man Vanilla Man Vanilla Man Vanilla V Vanilla Mobil Vanilla Vanilla Vanilla Vanilla Vanilla Vanilla Vanilla Vanilla January South February South March South April South May South June ware South July South August South September South October South Me November South December South January North February North March North April North May North June ware North July North August North September North October North November North December North January South South March South April April South South M May South South June South South July South August South September South October South November South December South January North February North March North April North May North June North July North August . North September North October North ML November North December North January South February South March South April South May South 2620 2450 2430 2480 2540 2610 10 2340 2010 2380 2540 04 2310 2400 2320 - 2290 5290 4320 1020 4500 3528 3960 3996 3960 3960 4230 4230 10 4275 06 4365 4455 2050 3960 1230 4230 1230 4230 1275 4275 nor 4365 4455 4128 1 4176 00 3936 4090 3936 4350 4350 4350 1960 4350 3610 3640 sean 3610 2240 2210 20 2430 2430 2460 2540 2310 2620 2450 2430 ZOOM + February Vanilla Vanilla Vanilla Vanilla Vanilla Vanilla Vanilla Vanilla Vanilla Vanilla Vanilla Irish Cream Irish Cream Irish Cream Irish Cream inish Cream Irish Cream Irish Cream Scream Irish Cream Irish Cream Irish Cream Irish Cream Scream Irish Cream Irish Cream Scream Irish Cream irish Cream Irish Cream Irish Cream Irish Cream Irish Cream Irish Cream Irish Cream Irish Cream Irish Cream gear Irish Cream Irish Cream sealt Irish Cream Hazelnut Hazelnut Hace Hazelnut hacen Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut February South March South April South May South June vane South July South August South September South October South November South December South M January North North March North April North May North June June North July North August North September North October North November North Novem December North January South February South March South April South May South June South July South August South September South October South November South December South January North February North March North wa April North North way June North North Aui. August North NO September North October North North November North December North January South February South March South April South May South June South 2310 2620 2450 2430 2480 2540 2610 2340 2380 2540 2310 2400 2320 - 2290 5290 4350 100 4350 4350 4350 3610 3640 3610 2210 10 2430 2430 2400 2460 2540 2310 2620 2450 2430 2480 2540 2610 40 4350 3610 3640 2010 3610 2210 2430 2430 2460 2540 2310 2620 700 2450 700 2430 2480 2540 2610 2340 2380 2540 May July ZOOM + ON Irish Cream Irish Cream Irish Cream Irish Cream Irish Cream watan Irish Cream Irish Cream Irish Cream Irish Cream cam Irish Cream . Irish Cream Irish Cream WL Irish Cream hich can Irish Cream Irish Cream Irish Cream is cream Irish Cream Irish Cream Irish Cream Irish Cream is crean Hazelnut Hazelnut nazen Hazelnut Hazelnut Hazelnut Hazelnut Wozlout Hazelnut Mozolut Hazelnut hacen Hazelnut Hazelnut Hace Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut Hazelnut May North June North July North August North September North October North November North December North January South February South March South April South May South June South July South August South September South October South November South December South vecem January North February North March North April North May North June North July North August North September North October North November North De December North . January South February South South April South May South June South vure July South August South September South October South November South December South 4350 4350 4350 4350 3610 3640 500 3610 2210 700 2430 2430 700 2460 2540 04 2310 2620 - 2450 2400 2430 2400 2480 2540 2010 2610 010 4350 3610 1 3640 2010 3610 10 2210 2430 2430 2460 2540 2310 2310 2620 w 2450 wo 2430 2480 2540 2610 1 2340 2380 we 2540 2310 2400 2320 2290 5290 4320 March ID First Name Last Name Department Title 2956 Michael Goldstein Engineering Engineer 4123 Steve Ballmer Engineering Engineer 3117 Frederich Bednarczyk Engineering Engineer 2122 Yuan Chang Engineering Engineer 2451 Francine Detweiler Engineering Engineer 2896 Samuel Gates Engineering Engineer 2768 Phyllis Leonard Engineering Engineer 1119 Sharad Manispour Engineering Manager 2344 Nemesha Mehta Engineering Engineer 3210 Carmen Ortega-Molina Engineering Engineer 2002 Edgar Rothrock Engineering Engineer 3015 Patti Stonesifer Engineering Engineer 2678 Koshi Yamamoto Engineering Engineer 2733 John Zumkowski Engineering Staff 3314 Svetlana Kartashev Human Resources Staff 4006 Alice Rovik Human Resources Manager 2042 Hillary Cushner Management 1851 Melinda English Management Manager 1355 Barbara Grabowski Management Manager 1173 Roberta Kurzweil Management Manager 1441 James Van Horn Management Manager 1042 Maria Andretti Marketing CSR 1032 Hillary Flintsteel Marketing Staff 1009 Kevin Grundies Marketing CSR 1614 Artie Lambros Marketing CSR 2105 Luca Pacioli Marketing Staff 1016 Oscar Gomez Marketing CSR Salary 51,000 42,900 56,700 46,540 56,700 66,300 59,800 54,500 65,000 46,000 53,300 64,300 49,600 33,000 38,000 43,000 32,000 62,040 75,700 63,000 66,500 42,500 34,500 38,900 41,000 42,300 43,500 Hire Date Birth Date Gender Clearance 4/11/97 3/7/63 M TS 5/1/02 4/14/70 M N 5/14/99 5/27/70 M S 9/5/95 3/4/64 M TS 10/10/95 4/29/70 F S 1/28/97 4/10/74 M S 12/13/96 7/2/70 F TS 10/13/90 2/4/69 M S 10/1/95 2/12/65 F 9/16/99 3/28/52 F S 18/94 3/21/70 S 7/6/98 3/10/66 F S 11/16/95 1/24/63 F S 4/18/96 12/12/72 M TS 11/5/01 5/3/82 F N 12/1/01 1/26/55 M S 8/26/94 5/6/61 F TS 10/1/93 2/14/56 F TS 10/13/92 4/10/65 F 6/16/92 12/22/58 F S 12/18/92 2/3/58 M S 3/21/90 8/20/70 N 3/21/90 8/22/58 F N 12/24/89 3/4/61 5/16/93 7/13/70 M N 8/26/95 5/6/50 2/16/90 4/29/67 M N Staff zoznz zu un cont ca as za oso o co Submitted Employee ID Last Name First Name Organization 11/29/2021 688 Abbs Don Habitat for Humanity 12/5/2021 08T Adams David Red Cross 12/9/2021 Y7CA Ascott Karen Red Cross 12/5/2021 M5NA Bagby Sharon Rotary Club 12/3/2021 39N Butler Barry Rotary Club 12/3/2021 ONNA Clark Jolene Toastmasters 12/2/2021 ONNA Clark Jolene Make a Wish Foundation 12/6/2021 ONNA Clark Jolene Leukemia Society 12/16/2021 Donovan Gary Make a Wish Foundation 12/12/2021 1XBA Doster Glenn Amnisty International 12/16/2021 L3H Dunn Elaine Make a Wish Foundation 12/4/2021 56NA Foster Scott Rotary Club 12/2/2021 56NA Foster Scott Meals on WHeels 12/17/2021 1F9A Hughes Gary Red Cross 12/27/2021 J5M Johnson Les Foster Children's Fund 12/10/2021 44JA Kelleher George Romania Relief 12/20/2021 57XA Lomstein Thomas Meals on WHeels 12/12/2021 FA5A Peters Roger Toastmasters 12/14/2021 FA5A Peters Roger Foster Children's Fund 12/6/2021 ROM Simpson Joseph Red Cross 12/13/2021 R9M Simpson Jospeh Habitat for Humanity 12/6/2021 08A Thimsen Timothy Rotary Club 12/3/2021 C3P Warren James Lion's Club 12/27/2021 R45A Womak Anthony Habitat for Humanity Amount Donation Sent $ 50 12/1/2021 $ 100 12/6/2021 $ 100 12/18/2021 $ 25 12/6/2021 $ 250 12/10/2021 $ 20 12/5/2021 $ 75 12/4/2021 $ 60 12/19/2021 $ 130 12/21/2021 $ 200 12/21/2021 $ 225 12/29/2021 $ 125 12/10/2021 $ 125 12/26/2021 $ 50 12/22/2021 $ 25 12/30/2021 $ 180 12/14/2021 $ 115 12/22/2021 $ 35 12/15/2021 $ 80 12/19/2021 $ 50 12/19/2021 $ 150 12/23/2021 $ 50 12/14/2021 $ 60 12/13/2021 $ 100 12/29/2021