Excel Project: ETL in Excel

You are to analyze production patterns for a company producing lawn equipment. The company wants to understand which regions and which managers produce the most units. They also want to know if they are producing more units than the minimum run requirement for each production run. The minimum run requirement is the number of units that must be produced to be profitable for each type of inventory item. Go to the student download page at http://www.pearsonglobaleditions.com and download the files labeled P5-7tbl_Batch.csv, P5-7tbl_Products.csv, P5-7tbl_ProductsBatch .txt, and P5-7.xlsx. The first three files are exported from systems that produce different output formats.

REQUIRED

Import each of the csv and txt files into the Excel file. Import each file into the sheet with the same name as the file. Pay attention to the delimiters in each file and whether the file uses text qualifiers or not. Combine the data from the three sheets to the tab labeled P5-7tbl_AllData. Once you have all the data gathered correctly into the P5-7tbl_AllData tab, answer the following questions, listing each answer on the appropriate sheet (e.g., Solution1 sheet, Solution2 sheet, and Solution3 sheet):

1. How many units did each location produce?

2. How many units did each manager produce?

3. How many product batch runs produced fewer units than the minimum run size?