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 overlap, you need to merge the data from the new company with similar data for you 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 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.

Complete the following:

Category	Prefix
Analyzer	600
Autoclave	601
Balances	603
Bath	605
Biohood	607
Cell Disrupters	609
Cell Harvesters	611
Centrifuges	613
Chromatography	615
Desiccators	617

Category	Prefix
Evaporators	619
Fermentors	621
Furnace	623
Gas Chromatographs	625
Glove Boxes	627
Microscopes	629
Reactors	631
Spectrophatometers	633
Ultrasonic Cleaners	635

1. Rename initial Data file provided Sloan.xlsx with the name 4-Ex2-Johnson-SloanYourName.xlsx and save it in the Chapter 7 folder. Rename Sheet1 as Sloan.

2. Create a PivotTable report from the data in the Sloan worksheet that list the Sum of Total Products (rename as Total Products) for each Category name the worksheet as PT- Report-1. Format the Total Products Column as Number with no decimals

3. Create a PivotTable report from the data in the Sloan worksheet that list the Maximum of January thru June for each Product and filter by Category name the worksheet as PT- Report-2. 4 – Add a column with the maximum of the 1 Semester. Select only one Category to test it. Use an IF to avoid have value zeroes if no data.

5- Add the recommended bar chart 6. Add your name and Current Date at the end of the workbook.

7. Save and close the 4-Ex2-Johnson-Sloan-YourName.xlsx workbook.