BUAD 3050 Information Technology Management/Assignment #1 – Using Excel’s Functions for Data Analysis Assignment #1 – Using Excel’s Functions for Data Analysis
BUAD 3050 Information Technology Management/Assignment #1 – Using Excel’s Functions for Data Analysis
Assignment #1 – Using Excel’s Functions for Data Analysis
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.
The 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. A table of values is displayed below:
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.
Part 2: Mr. McDougal would also like a summary report that will display the counts, sales and quotas (targets) by Rank.
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 Numbers) the data from the Raw Data worksheet onto your Reporting worksheet. You may need to seek out additional functions (not covered) to manipulate the downloaded text data and produce the information in the requested arrangement. Insert worksheets within the workbook supplied to build your solution and 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. 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
· Turn in a hardcopy of both reports in class on the due date. Use a reasonable “Print to Fit” layout so that your reports are easily read.
· Turn in a hardcopy of both reports – with Formulas displayed. (Select the FORMULAs ribbon and SHOW formulas.) Be sure that the entire formula is visible… you may need to resize columns to accomplish that.
·
· Include, as cover page, a printed version of the final page of this assignment document. This will be used as a Grading Rubric to communicate details of your grade on this assignment back to you. Only the total score earned for the assignment will be posted to Blackboard.
Submitted by: _________________________________
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 report and report data, Print to fit – and generally provide a report easily read and consumed.
______(5)
Proper Submission
Grading Rubric as cover page submitted in class with hardcopy version of Weekly Productivity Report and Summary Report attached AND hardcopy version of formulas for both report – with formulas visible and easy to read.
Submission of electronic copy of Excel document to Blackboard Assignment Course Link.
______(40)
Total Points Earned
Attention: No late work accepted.
Notes