Assignment - Using Excel's Functions for Data Analysis
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 (", ") .
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:
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.)
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.
Attachment:- Using Excels Functions for Data Analysis.rar