Madison’s Department Stores
This case is about setting up productivity reports, both on a daily and weekly basis. Then using that data producing pivot tables, charts and advanced filter worksheet to display the same data in different configurations. The case will consist of about 22 worksheets in one workbook.
You will use consolidated worksheets functions, vlookup function, IF functions, database functions, range names, and imbedded IF/VLOOKUP functions.
To give you some hints on writing the VLOOKUP functions, IF function and database functions the following statement is an English language explanation of what the functions are trying to accomplish.
Daily Productivity Worksheets
- Base Pay is simply the hours worked times the hourly wage based on the sales position
- Commission is: IF sales is greater than the quota/hour times the hours worked, then sales minus the (quota/hour times hours worked) times the commission rate — however, if sales is less than the quota/hour times hours worked the commission is zero.
- Gross Pay is simply the base pay plus the commission
- Quota Met is simply IF commission is greater than zero, then quota met is 1 for yes, if not quota met is 0 for no.
- Worked Today is simply IF hours worked is greater than zero, then worked today is 1 for yes, if not then worked today is 0 for no.
- All of the computations from sales on is simply the sum from linking a cell from the range of daily worksheets to the appropriate cell on the weekly summary worksheets.
- Data base functions take three arguments: the entire database (given a range name of “database”, the cell containing the appropriate field name (or column number), and specifying the criteria from the field names and a blank row below it (given a range name of “criteria”.
The memo will contain the answers to the 10 questions (on page 375-376) and questions 2 and 7 (on page 377) in a professional manner (Memorandum). Best way is to reiterate the question and answer it. Remember, the executive will not remember the questions by number.
The databases turned in via gateway and the memos given to me (not emailed).
Wage Lookup table
Sunday Productivity report
Monday Productivity report
Weekly Productivity report
Commissions and sales by department
Commissions by individuals within each department
Pivot table and Pie chart ( on separate sheet) showing commissions by department
Pivot table and Pie charts (on separate sheet) comparing the department’s sales representatives’ sales
The 5 Sales Reps with the highest commission (Question 1)
Sales Reps receiving above average commission (Ques 2)
Sales Reps receiving above average commission, excluding the furniture department. (Ques 3)
Sales staff that did not reach 60% quota goal. (Ques 4)
For each department, which salesperson had the highest sales. (Ques 5)
Average of how frequently the sales staff made their quota. (Ques 6)
Commissions and total sales for each sales position by department. (Ques 7)
Base pay, commission, and gross pay categorized by sales position. (Ques 8)
Total salaries and hours worked by department. (Ques 9)
Which department’s staff met their quotas most often and least often. (Ques 10)
Which salesperson(s) received more than $1,500 in commissions last week and which salesperson(s) did not receive any commission last week.
Total commissions paid by each department, each department’s percentage of total commissions paid for all departments, and the total sales for each department.