Reference no: EM13762408
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.
Complete the following:
1. Use Notepad (or another text editor) to open the text file named Sloan.txt 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. Rename Sheet 1 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.
|
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 Chromatography
|
625
|
|
Glove Boxes
|
627
|
|
Microscopes
|
629
|
|
Reactors
|
631
|
|
Spectrophotometers
|
633
|
|
Ultrasonic Cleaners
|
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 each 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.
Attachment:- sloan.txt
|
Write paper on song for a dark girl and conversion of jews
: Write a paper on Song for a Dark GirlWrite paper on Song for a Dark Girl and the Conversion of the Jews. How does this affect the impact of the poem?
|
|
Federal unemployment taxes
: Westway Company pays Suzie Chan $2,550 per week. Assume Social Security is 6.2% on $110,100 and 1.45% for Medicare.
|
|
Display all data from each of the tables in the moremovies
: Within SQL*Plus, list names of the tables that you have created whose name starts with MM (Hint: use data dictionary view USER_TABLES)
|
|
Describe chinas process of creative destruction
: Explain how China provides an example of alternative strategies to those advocated by the Washington Consensus. To what extent did China use "shock therapy"? To what extent did it pursue the gradualist approach?
|
|
Importing and analyzing data for johnson equipment
: 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 indu..
|
|
Viability of upgrading to windows
: Provide a detailed upgrade plan for the organization, including the sales staff.
|
|
Shock therapy and rapid privatization in russia
: Why were the IMF and U.S. Treasury stubbornly committed to shock therapy and rapid privatization in Russia? What was their political objective? What "imminent danger" worried them?
|
|
Basic components of organization
: The two basic components of organization are selection and arrangement.
|
|
Formulae to calculate their center point
: Given n points in the 3D space (xo, yo, zo), (x1, y1, z1), (xn-1,yn-1, zn-i), we use the following formulae to calculate their center point (xc, yc, zc)
|