Case Problems Level 1 - Importing and Analyzing Data for Johnson Equipment Johnson Equipment, the medium-sized laboratory equipment manufacturing company where you work, is in the process of acquiring Sloan Manufacturing, a smaller equipment manufacturer in the same industry. Because operations between your company and Sloan overlap, you need to merge the data from the new company with similar data for your company. The text file you received from Sloan contains categories, product numbers, and product descriptions, in addition to the on-hand quantity for each product and the number of products produced during each month of the past year. You need to add prefixes to the category names so the data will match the existing data that your company uses. Then, you will use Excel to organize and summarize the data. Chapter Exercises +/- Accounting Complete the following: 1. Use Notepad (or another text editor) to open the text file named Sloan.txt from the Chapter 7 folder to examine the data, and then close the file and Notepad. 2. Import the text from the Sloan text file into a new workbook. Name the work- book Johnson-Sloan.xlsx and save it in the Chapter 7 folder. Rename Sheetl as Imported Data 3. Convert the information in the worksheet into columns, if necessary. 4. Sort the data by category and then by product number in ascending order. 5. Use the CONCATENATE function to add the appropriate prefix and a dash to each Category in a new column titled CategoryPrefix using the following list. For example, the Analyzer category would begin with 600, followed by a dash and the category name (600-Analyzer). (Hint: Try placing these values in your spreadsheet in a column named Prefix using a VLOOKUP function.) Delete the original Category column. 487 You are a regional manager for Home Station, a national chain of home renovation stores Chapter 7 Organizing Data for Effective Analysis Prefix Prefix 600 601 603 619 621 623 Category Analyzer Autoclave Balances Both Blohood Cell Disrupters Cell Harvesters Centrifuges Chromatography Desiccafors 605 607 609 611 613 615 617 Category Evaporators Fermentors Furnace Gas Chromatographs Glove Boxes Microscopes Reactors Spectrophotometers Ultrasonic Cleaners 625 627 629 631 633 635 6. Change the data set into an Excel table. 7. Use the Total row to calculate the number of units on hand and the number of units produced in cach month. Add a new column named Total to the right of the December column that calculates the number of units produced for the year for each part. 8. Display the top 20 items based on the values in the Total column to show the parts with the highest production by month. 9. Save and close the Johnson-Sloan.xlsx workbook Level 2 - Analyzing Manager Performance at Home Station $ Sales You are analyzing the weekly sales data for one of the The sales data istinn Fichier Edition Format Prsentation Fentre Aide fru.blackboard.com Sp DET Sloan.txt STAT Category, Product Number, Description, Quantity en- hand, January February March April May June July Angust.Sertenec, October, November Decembe 1 SP Desiccators, 490620,2 cubic foot plastic dessicatar, cabinet., 85,59,924,627,680,788,392,197,525, 98,852,646,201 Bath, 490184,260010 refrigerated bench top bath with Pelletier thermoelectric cooling from ambient to 40C.,93,685,838,997,418,639, 479,94,493, 258,48,701,204 Spectrophotometers , 490780,6 uv/vis spectrophotometer with computer monitor and printer.,8,812,535, 367,616,985,566,85, 396, 437,5,522,928 Str.499422. Sat.cfgs., 94,352,112,957, 648,120,770,202, 67,992,885,364,238 Glove Boxes..498709. Stainleas steel 2-sided glove box with dual column gas dryer. 3 vacuum pumps and 2 recirculating chillers. Factory reconditioned. ,60,710,728,644,824,499,267,882,63,369,678,667,818 Bath 499174.tissus float bath., 48,59,796,429,438,911, 163,582,38,329,892,92,968 MASCAS.CARSKA 499227. tringsklar compound microscope with 4x 4ox and 100% bortswr.txt objectives. , 14, 138, 345,949,311,137,715,994,741,260, 360,438,988 Cell Disrupters, 490292, (Thermal Spectronic) French Press complete with FA-030 cell (40 00 lhs) and rapid-fill kit accessory 20, 131, 168, 378,484,176,586,319,968,430,225,347,199 Microscopes, 498713,10 4-10-40-100x oil microscope., 12,56,993, 640, 664,993, 255,817,819,752, 27, 560,66 Microscopes, 490714, 104-40-100x plans microscope., 93, 157,909,698,796,451,966,269, 274,339,663,58,435 Desiccators, 490613, 18 inch diameter glass dessicoter. Complete with plate and cover., 91,799, 31,982,735,731,958,255,705, 94,384,936, 452 Calculato Desiccators, 490614,10 inch diameter glass vacuum dessicoter. Complete with plate and SX Deti cover.,85,853,444,167,47,486,934,316,442,593,202, 177,531 Analyzer, 490004, 1000 tablet? Hardness Tester, 3,154,45,859,180,729, 486,862,33,955, 497,20,121 Spectrophotometers . 490787,100-30-uv/vis , DOCK excel week 5 - Jaune Pom week 5 CV Charles part B View the course description Vert Blackboard Collaborate loin session The Distance Learning department can be reached th pertaining to Blackboard. They can also be reached by Attendance View your attendance B Groups . NEW A spectrophotoneters 24,990,498,26,203,609,296,371,422, 315, 829,896,782 1 Course Description