Business Intelligence Case

Project Background

Mell Industries is a national manufacturing firm that specializes in textiles based out of

Chicago. Starting out as a small factory in Warrenville, Illinois, the firm experienced a period of steady growth over the past twenty-four years. Steadily opening new warehouses and factories in the surrounding areas in Michigan and Indianapolis until eventually moving their base of operations to Chicago. Due to this expansion, Mell Industries is at the height of its production and hopes to avoid any interferences or deceleration of growth.

In recent years, the firm has been under heavy media scrutiny for supposedly compensating its female staff unfairly lower compared to male counterparts. This was initiated when a disgruntled employee leaked the company payroll allegedly showcasing an unjust gap of income between the female employee and her male counterpart. This type of gender pay gap is highly criticized and as a precaution, Mell Industries has hired Cal Poly Pomona to conduct research to determine the validity of these claims. Mell Industries has provided Cal Poly Pomona with a data set of a sample population of

747 employees. Mell Industries has also offered Cal Poly Pomona compensation for any promising information gathered. Mell Industries may use information gathered from this project in future employee compensation decisions.

The initial dataset has been given to you in the form of an excel spreadsheet titled

Case_dataset.xlsx consisting of 12 columns labeled:

Column A - Employee ID

Column B - Gender

Column C - Date of Birth

Column D - Date of Hire

Column E - Termination Date

Column F - Occupation

Column G - Salary

Column H to L - Employee Evaluation Metrics

In addition, Mell Industries provided the latest annual employee performance review evaluation results rating each employee in various performance categories. They have turned over this information separately and as a consultant, it is your task to provide Mell Industries with the most accurate and relevant information in a digestible form. Furthermore, using excel skills learned during the course, you will manipulate and analyze the data set in order to make appropriate managerial decisions. You will utilize excel functions highlighted in this project as well as a pivot table and chart to form a decision support system in order to answer the critical thinking questions.

Project Objective

The purpose of this project is to perform a methodical data analysis to assist the company make an informed decision. This could also serve as a basis for implementing critical adjustments to certain business aspects if necessary. Illustrate the business process by condensing a large set of data, to present relevant information with data visualization. We will be utilizing Microsoft Excel 2016 to complete this project.

TASKS

Task 1. Identify the Issue

Identify the issue by reading the project background thoroughly, and have a clear objective in order to provide the client with a meaningful service.

Task 2. Download dataset

From Blackboard, download the dataset Case_dataset.xlsx. This is the initial data provided to you by Mell Industries, which contains information about their current employees.

Task 3. Create new workbook

Create a new Excel workbook and title it myAnalysis.xlsx. You will use this workbook to create additional sheets that will allow you to analyze the data clearly. Once this project has been completed, this workbook will provide the information critical to the client. It will also serve as a basis to either support or reject the claims about the alleged pay gap in his company and perhaps make adjustments if necessary.

Task 4. Create new sheets

Rename Sheet1 as Census. Add two (2) new worksheets by clicking the plus sign next to the sheet tabs and name the tabs Female_Employees and the other Male_Employees.

Task 5. Select and paste data into your workbook.

Go back to the MellIndustries_Initial_dataset.xlsx. Select all data by clicking on the top, left-hand side corner of the sheet. This will highlight all the data on that sheet. Copy and paste the data to your myAnalysis.xlsx Census tab. Another way to select all is to click CTRL+A, then copy paste.

Task 6. Filter

On your Census tab, under the Gender column, filter ONLY the female employees. Select all the data, then copy and paste it on the Female_Employees sheet. Repeat the same steps for the male

employees.

Figure 1.3

Task 7. Data Analysis

Create a new sheet and name it Data_analysis. Figure 1.4 shows how you need to format this worksheet and what it needs to look like, as well as the content necessary to create a visual representation of the given data. Each section will require you to use excel functions to calculate the missing values. To answer the Top 10 highest paid positions section, you will need to create a pivot table to derive the answers.

Data Analysis Sheet

IMPORTANT NOTE: To receive full credit, Excel functions must be used for each required field. When functions are used, the formula NOT the value must appear in the function bar when the cell is selected.

1. Total number of male employees: = COUNTIF

2. Total number of female employees: = COUNTIF

3. Total number of employees: = SUM

4. Male/Female count: = COUNTIF

[Hint: =COUNTIF(range,">=X")-COUNTIF(range,">Y")]

5. Average salary: = AVERAGEIF

[HINT: AVERAGEIF( range, criteria, [average_range] )]

6. Salary bracket percentage by gender: = Calculate %

7. Top Highest Paying Positions: =GETPIVOTDATA

Task 8. Create an Occupation Pivot table

Go back to your Census tab and highlight the entire sheet (CTRL + A). While the dataset is highlighted, from your menu select INSERT > PIVOT TABLE. A dialog box will appear. When the dialog box appears, select to place the pivot table in New worksheet. Click OK. This will open a new sheet. Rename this to Occupation_pivot

In the following step, you will select the fields that will be added to this report. For this pivot table, you want to view the number of employees for every job title. To do that, drag OCCUPATION from the pivot table fields and drop it under ROWS. Next, drag EMPLOYEE_ID and drop it in VALUES.

Once the columns had been populated, SORT the data by Count of Employee_ID. Click the drop-down

button to the right of the ROW LABEL. Select More sort options > Click Descending by then on the drop down select Count of EMPLOYEE_ID. Click OK.

As you can see, now you have the list of Job titles on the left, and the count of employees to the right. This shows how many employees are working in each type of occupation in descending order. Results should show the four occupations that are under Average Salary Report back in your Data_Analysis table. We have chosen to analyze the top 4 positions that has the highest count of employees.

Task 9. Create a Salary Pivot table

Now that you know how to create a pivot table, create another one and name it Salary_pivot. This pivot must show the following information:

1. Occupation

2. Gender, both male, and female (columns)

3. Salary

Make sure that the item in VALUES is set to MAX of Salary, then sort the data by descending order. To sort, click the drop-down button next to the Row labels title, then select More Sort Option. Use an excel formula to show the results from the pivot, to your Data_Analysis table.

Task 10. Create Charts

Go back to Data_Analysis tab. On the same sheet, create a chart for the following:

1. Salary Bracket - Compare COUNT of both male and female employees.

Use a Clustered Bar chart

2. Average Salary by Occupation - Show the difference in pay between male and female employees based on the specified job positions (Customer Service Representative, Category Manager, Stocking Associate, Inventory Retrieving Associate

Use a Clustered Column chart

3. Highest Paid positions

Use a Clustered Column chart

Note: Make sure to add a titles and data labels on the chart.

Additional Data Entry

Up to this point, you have generated reports to show if there is a discrepancy between male and female salaries. To substantiate the results, a study of additional data must be performed. The following steps are necessary to determine if the length of employment correlates directly with the employees salary.

Task 11. Additional Data

JOB DURATION

Go back to the Census sheet. Insert a new column between Termination Date and

Occupation. Title this column Job Duration. Use a formula that will calculate the length of employment for each employee, including the ones who had already left the company. Once

this is complete, save the file.

TOTAL EMPLOYEE EVALUATION SCORE Employee Evaluation Metrics

Employees are subject to performance evaluation every year, based on 5 different criteria: Knowledge of work, Communication, Teamwork, Leadership, and quality of work. The scores are determined by their direct supervisor through evaluation of supplementary performance reports.

Exceeds Expectations	3
Meets Expectations	2
Below Expectations	1

Add a new column to the Census sheet next to the last criteria of the employee evaluation section. Title this column Evaluation Score. Use a formula that will calculate the sum of the evaluation score for each employee.