excel assignment

BUAD 2020 Information Technology Management

Assignment #1 – Using Excel's Functions for Data Analysis

Due: Sunday, May 20th 11:59pm

Case Background:

You have been hired by John McDougal, sales manager of Macy's Franklin Park store, to help

him better understand the productivity of his sales force.

Mr. McDougal has provided you with a download from

the POS system that contains the employee name, rank,

department, sales and hours worked. That data is

provided to you in the file named

A1_MacysDeptStore.xlsx (and is available for download

on Blackboard – under the assignment course link) and

looks similar to the image below.

Mr. McDougal would like you to produce a series of reports that will let him know how well the

sales force is performing – where performance is defined by how many employees meet their

targeted sales quotas and the overall percentage of the sales quota obtained by the cumulative

efforts of all salesmen.

Mr. McDougal has identified two ways that he would like the analysis of each week presented.

Each part is detailed below.

Part 1: Mr. McDougal would like a weekly recap of sales by salesman that displays information

regarding weekly sales, the salesman's sales quota, the percent of the quota met, number of

hours worked, base pay earned, commission due and the salesman's gross pay for the week

under review. He put together a sample of how he would like the report to look – use this

layout – or your best judgement to produce a well laid out and easy to understand report.

Notice the report includes the name in the format of Last, First. You will need to use some text

functions to display the name concatenating the empLast to the empFirst with a comma and

space between (", ") .

Assignment #1 – Using Excel's Functions for Data Analysis

2

The other formulas required to produce this detail report will need to pull information from a

table of hourly wages and quotas that Mr. McDougal has provided to you.

Each employee is assigned a "Rank" within the sales force. Macy's has 5 different ranks. The

employee's base pay and commission rate is dependent upon the rank assignment. For

example, a rank of AM indicates an Assistant Manager who is paid $23.50 per hour, is expected

to produce $200.00 of sales for each hour that he/she works and is paid a commission on his

sales, if he/she meets the hourly sales quota for the week. The table of values displayed below

should be replicated and used in your workbook:

Hourly Wages & Quotas Sales Rank Sales Title Hourly Wage

Hrly Sales Quota

Commission Rate

AM Assistant Manager $ 23.50 $ 200.00 3.0% PT1 Sales Assistant $ 10.50 $ 100.00 1.0% PT2 Sales Partner $ 11.75 $ 125.00 1.5% S1 Sales Associate $ 13.50 $ 150.00 2.0% S2 Sales Consultant $ 15.00 $ 175.00 2.5%

 Target Sales is calculated by multiplying the hours worked times the hourly sales quota of the

designated Sales Rank.

 % of Target is calculated by dividing Sales by Target Sales.

 Base Pay is calculated by multiplying the hours worked times the hourly wage.

 Commission is calculated for employees whose Sales exceeds their Target Sales – by multiplying

the weekly sales amount times the commission rate that corresponds to the employee's Sales

Rank.

 Gross Pay is the sum of Base pay and Commission.

The report should be sorted to make it easy to identify the "best" salesperson – defined by the % of

target column.

Part 2: Mr. McDougal would also like a summary report that will display the counts, sales and quotas

(targets) by Rank. (The % of Target column in this summary report is calculated as Total Sales divided

by Total Target.)

Assignment #1 – Using Excel's Functions for Data Analysis

3

Put the Excel skills taught thus far to produce the requested information for Mr. McDougal. Write (and

leave) formulas on the Raw Data worksheet to convert data, as needed. Copy and paste (AS VALUES)

the data from the Raw Data worksheet onto your Reporting worksheet. Insert worksheets within the

workbook supplied to build your report and summary solutions to present the required information.

Use absolute addressing, VLookup, IF, Count, CountIF, SumIF, and various text functions to create your

solution. Create a solution that would work with this or another similar data set. Do NOT go into the

data and modify it by hand – and do not use Pivot Tables in this assignment. Create formulas to lookup,

calculate and display the requested information.

Pay attention to formatting, column widths, headings, and other visual aids to produce easily consumed

information.

Attention: No late work accepted.

Assignment Deliverables:

 Submit your Excel workbook to the Blackboard Assignment #1 Course Link

Assignment #1 – Using Excel's Functions for Data Analysis

4

Submitted by: _________________________________ Section: ___________

Grading Rubric for Assignment #1:

Points

Earned Category Description/Requirements

______(5) Manipulation of

text data

Use of functions to manipulate downloaded data provided on

Raw Data worksheet to produce the data in the form needed for

reporting purposes. Leave the formulas demonstrating how you

converted the data from the form given to the form needed.

Failure to submit a document with those formulas will be

interpreted as if you made the changes manually and will result in

0 points awarded in this category.

______(5) Formula

Addressing

Demonstrated understanding of absolute and relative addresses

and/or named ranges to simplify copy/paste of complex formulas.

______(10) Weekly

Productivity Report

Correct use of formulas and functions to produce required

information for Weekly Productivity Report.

______(10) Summary Report Correct use of formulas and functions to produce required

information for Summary Report.

______(5) Formatting

Attention to details to format reports and report data, Print to fit,

text alignments, data formatting – and generally provide a report

easily read and consumed.

______(5) Proper Submission Post Excel document to Blackboard and submit required

hardcopy reports with grading rubric.

______(40) Total Points Earned

Attention: No late work accepted.

Notes: