Excel project

ISM 3012 – Group Project 1 – Spring 2018

Professor Wishart

Use the dataset provided by the instructor in the Excel workbook titled supportCalls.xlsm

Step 1: Calculate new data

using the VLOOKUP function -- 2 points

In cell H25, enter a formula to determine

payment for the support rep for a given

call. Use the VLOOKUP function to

obtain the HourlyRate corresponding to a

particular Call Type (reference the table).

Copy the formula in cell H25 to the other

support call records (H26:H383).

Step 2: Calculate new data

using the IF function -- 2 points

In cell L25, enter a formula, using the IF

function, to give a bonus to calls that have

CustomerSatisfaction equal 1 (in this

rating scale, 1 indicates the highest level of

customer satisfaction).

If the customer satisfaction for the call is 1,

then add the bonus amount in cell G11 to

the Rep_Payment.

Copy the formula in cell L25 to the other

support call records (L26:L383).

Step 3: Create a PivotTable to

show summary information -- 1 point

Create a PivotTable to show Total

HoursLogged for each support Rep

during a given month/quarter

Format the Total HoursLogged as Number

with 1 decimal place

Place the PivotTable in a new worksheet,

which you can rename as PivotTable_1.

ISM 3012 – Group Project 1 – Spring 2018

Professor Wishart

Step 4: Create a PivotChart to

show summary information -- 1 point

Create a PivotChart corresponding to the

PivotTable created in Step 3, then switch

the data so that Rep LastName is on the

horizontal axis

Hint: the Switch Row/Column command

is located in the PivotChart Tools, Design

tab

Hint 2: if you’re working with the Mac

version of Excel and cannot find the

PivotChart feature, then create a regular

side-by-side column chart to display the

data as required

Place the chart below the PivotTable

created in Step 3

Step 5: Create two additional PivotTables/PivotCharts to show summary information -- 4 points

Create two additional PivotTables of your group’s choosing to further explore/summarize this data:

1. Ensure that you use a variety of measures and/or dimensions so that the insights provided are not repetitive – measures are numerical columns (for example, profit, revenue, hours, units) that can be

used in mathematical operations (for example, number of, sum of, average of) ; dimensions are the

descriptive columns (for example, by rep, by department, by product, by date)

2. Save each PivotTable in its own worksheet

3. Create a PivotChart for each PivotTable, if the visualization aids in the analysis (that is, do not attempt to create a PivotChart if your PivotTable is too complicated to show visually)

4. Write a brief description of what is being accomplished by each of your PivotTable analyses – two or three sentences, written in the Excel spreadsheet near to the PivotTable, that describe the

analytical purpose of the PivotTable (insight it provides about the data)

Step 6: Evaluate your own and your group members’ participation and performance on

this project -- 1 point

The peer evaluation questionnaire will be made available in Canvas on the day after the project is due

Deliverable(s): 1. Attach the completed Excel file to the ism3012_groupProject1_Excel_dataAnalysis assignment link in

Canvas – the Excel file should contain all worksheets pertaining to Step 1 through Step 5 – this is a

single submission for the entire group

2. Complete the peer evaluation questionnaire to rate yourself and your group members – each group

member will do this individually the day after the project has been submitted