BUAD 2020 Information Technology Management Project #4 Using Excel for Data Analysis (40 points)

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 datafile downloaded 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 P4_Macys.xlsx (and is available for download on Blackboard) and looks similar to the image below.

Using the file given (Do NOT copy into another Excel workbook),

Mr. McDougal would like you to produce a series of reports that will let him know how well the sales team 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. Complete your work on the worksheet provided.

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 screenshot below pictures an unsorted version of the report. Your submission will sort the report by Salesperson.)

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 (", "). Insert a new column with a title of Salesperson. Write the function needed to join the last name to the first name in that column, and then hide the original columns.

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.

AM Assistant Manager $

PT1 Sales Assistant $

PT2 Sales Partner $

S1 Sales Associate $

S2 Sales Consultant $

23.50 $ 200.00 3.0% 10.50 $ 100.00 1.0% 11.75 $ 125.00 1.5% 13.50 $ 150.00 2.0% 15.00 $ 175.00 2.5%

Project #4 Using Excel's Functions for Data Analysis

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 provides you with the ranks and corresponding data (it is available in the downloaded file on the Inputs worksheet):

Hourly Wages & Quotas

Sales Rank Sales Title

Hrly Sales Commission Hourly Wage Quota Rate

The formulas needed to create the other columns of the first report are defined below:

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.

Once you have built the report, add Filter capability and sort the report by Salesperson.

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.)

Create the Summary report you see below in the area to the right of the Weekly productivity report created in part 1.

2

Project #4 Using Excel's Functions for Data Analysis

General Comments:

Use the Excel functions taught thus far to produce the requested information for Mr. McDougal.

Use absolute addressing or named ranges, 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. These reports should look good!

No late work is accepted.

Assignment Deliverables:

Review the rubric to the right to assess your submission and ensure that you have met the requirements

Your work must be performed directly in the file posted to Blackboard's assignment link. No exceptions.

Submit your Excel workbook containing the two reports described above to the Blackboard Assignment Link

Remember that the work you submit must reflect independent thought and effort.

Can you please help me with the excel formulas?