Student Name Student ID No. Teacher Name Clare Milligan Teacher Contact [email protected] T. 9564 2050 Program Name Master of Professional Accounting Program Code MPA14 Subject Name Information Systems for Accountants Subject Code MPA102 Department Graduate and Degree Programs Semester & Year Semester 1, 2017 Time Allowed/Due Date Sunday 23 April by 11:55 pm (upload to Brightspace) Assessment Type Excel assignment Value of Assessment 18% of overall assessment Assessment Conditions This is NOT a group assignment, it must be completed individually Special Requirements N/A DECLARATION 1. I am aware that penalties exist for cheating, plagiarism (copying) and unauthorised collusion with other students, or external consultants. 2. I am aware of the requirements covering style and layout standards as designated by my teacher/tutor. 3. For assessments other than those conducted in-class, I have retained a copy. I understand that uncollected assessments will be destroyed. 4. This assessment was prepared using the Unit of Competency from www.training.gov.au with particular attention to; Required Skills, Required Knowledge, Evidence Guide/Critical Aspects. Student’s signature: Date: Possible Marks Actual Marks Question 1 Question 2   Question 1: Milligan Department Store This question contains the following spreadsheet skills: • Advanced Filter • DSUM Function • Nesting Functions • Average Function • Excel Table • PivotTable • Cell reference • Filter • Range Name • Consolidating Worksheets • Grouping Worksheets • SUM Function • DAVERAGE Function • IF Function • VLOOKUP Function • DMAX Function • MAX Function • DMIN Function • MIN Function Data File: Milligan_Department_Store Introduction Vasanthi Peter understands the importance of a department store’s sales force, especially when it comes to the sales force of a premiere, upscale department store like Milligan’s. Customers have long enjoyed the expert knowledge, attention to detail, and service that Milligan’s sales staff provides. Maintaining the quality sales force is a daily job for Vasanthi. She carefully evaluates the performance of her sales staff and makes adjustments when needed. Currently, she monitors the performance of her sales staff by reading daily and weekly sales productivity reports. The problem is that these reports are prepared on a word processor. Vasanthi has no efficient way of analysing the data in detail. As a new accountant at Milligan’s, you have impressed Vasanthi with your work. Vasanthi asks you to prepare a Productivity workbook for her. You will prepare seven daily productivity worksheets and a weekly productivity summary worksheet, analyse the sales data by using the PivotTable, Advanced Filter and Filter tools, and generate several charts. Scenario Milligan’s Department Store is a prestigious, upscale department store located in Chadstone Shopping Centre. The store has specialty departments for women, men, children, cosmetics, cologne, linen, furniture, and housewares. One of the reasons why Milligan’s has such a fine reputation is because of its highly trained sales staff. Milligan’s management believes in rewarding its sales staff for its hard work, so in addition to an hourly wage, sales representatives are paid a commission on sales above an established quota. Sales representatives are classified as either part-time or full-time. Part-time representatives are then subcategorised as sales assistants or sales partners. Full-time representatives are subcategorised as assistant managers, sales consultants, or sales associates. Part-time representatives work 20 hours a week, while full-time representatives work 40 hours a week. Sales representatives have input into how many hours a day they work; however, they cannot work overtime. Within the company, sales representatives are ranked and paid according to their experience and tenure with the company. Sales representatives are paid a commission on all sales exceeding their established quota. As members of the sales staff may work a different number of hours on a given day, the sales quota is based on the hours worked. For instance, if a sales representative has an hourly quota of $100 in sales and he works 5 hours, then his daily sales quota is $500. For any sales above the $500 quota, the sales representative receives a commission. Figure 1 below summarises the hourly wages and established quotas. At the end of each business day, Vasanthi prepares a Daily Productivity Report. As Figure 2 shows, the Daily Productivity Report summarises each employee’s sales activity for the day. (The data shown in Figure 2 are for illustrative purposes only. Your report format and data may vary.) This report specifies the employee’s name, rank, assigned department, daily sales, hours, base pay, commission, and gross pay. At the end of each week, Vasanthi uses the Daily Productivity Reports to prepare a Weekly Productivity Report. The Weekly Productivity Report summarises the Daily Productivity Reports. Vasanthi currently uses a word processor to prepare the reports. However, she realises that a spreadsheet application is a much better tool for the summarisation and analysis work that she needs, so she asks you to develop a Productivity workbook for her. Design Specifications Each day, Vasanthi will enter each salesperson’s sales and hours into a Daily Productivity worksheet. She then expects the worksheet to determine each salesperson’s base pay, commission, and gross pay. While the gross pay involves adding the base pay to the commission, calculating the base pay and commission requires referencing values in a lookup table. As you want the lookup table to be easily accessible and updateable, you place the lookup table in its own worksheet. The base pay and commission are dependent upon the salesperson’s rank in the company. For instance, an assistant manager is paid $23.50 per hour and receives a 3 percent commission. In contrast, a sales associate is paid $13.50 per hour and receives a 2 percent commission. You use the VLOOKUP function to build this logic into the base pay, and you use the IF and VLOOKUP functions to build the logic into the commission cells. The base pay formula uses the salesperson’s rank to retrieve the correct hourly wage and then multiplies the hourly wage by the number of hours. The determination of the appropriate commission requires more complicated logic than the base pay. For instance, the commission formula must determine if a commission is to be paid, the applicable commission rate, and the portion of sales on which to base the commission. As the commission formula involves several lookups and decisions, you realise that nesting the IF and VLOOKUP functions is required. (At this point, you may wish to review the IF and VLOOKUP functions, as well as how to nest functions.) As previously mentioned, Vasanthi wants the Daily Productivity Reports summarised into a Weekly Productivity Report. The Weekly Productivity Report provides weekly sales, hours, base pay, commission, and gross pay totals for each salesperson. For instance, the sales column will reference and sum the individual sales for Sunday through Saturday. After showing Vasanthi the workbook prototype, she asks if you can determine the number of times each salesperson made his quota that week. You assure her that the worksheet can be modified to provide this information. On each worksheet, Vasanthi requires grand totals, averages, minimums and maximums for the sales, base pay, commission, and gross pay columns. Also on each worksheet she wants to see the total quotas met and the percentage of quotas met. Information Specifications Vasanthi wants to use the Productivity workbook to analyse the performance of her sales staff. She specifically requests that you show her how to use the PivotTable to analyse the sales data. She wants to: 1. compare the commissions and sales by department, and 2. view the commissions by individuals within each department. In addition to the information requirements specified above, Vasanthi wants answers to the following questions. Using the PivotTable, Advanced Filter and Filter tools, provide Vasanthi with answers to these questions. 3. Which five sales representatives received the highest commissions last week? 4. Which sales representatives received commissions above average last week? 5. Excluding the Furniture Department and its employees, which sales representatives received commissions above average last week? 6. If sales staff must make their quotas at least 60 percent of the time, which sales staff did not achieve this goal? 7. For each department, which salesperson had the highest sales? 8. On average, how frequently will the sales staff make their quota? 9. What are the commissions and total sales for each sales position by department? 10. What are the base pay, commission, and gross pay categorised by sales position? 11. What are the total salaries and hours worked by department? 12. Which department’s staff met their quotas more often than other departments, and which department’s staff met their quotas least often than other departments? Implementation Concerns Although you are free to work with the design of your workbook, each worksheet should have a consistent, professional appearance. You should use appropriate formatting for the cells and worksheets. Each sheet tab must be clearly labelled. Use the following naming convention for the Information Specifications section (IS1, IS2, etc) and TYD1, TYD2, etc for the Test Your Design section. This case requires you to group worksheets, insert columns into worksheets, consolidate information into a summary worksheet, nest functions, use several functions, reference a lookup table, and use the retrieved value in a formula, work with an Excel table, prepare charts, and use several analytical tools to analyse the sales data. In several instances, you can use the Advanced Filter tool to filter data contained in the summary worksheet. To provide Vasanthi with correct answers, you should use the DAVERAGE, DMIN, and DMAX functions in the summary worksheet, as opposed to the AVERAGE, MIN, and MAX functions. When the Advanced Filter is used, the DAVERAGE, DMIN, and DMAX functions adjust their values based on the filtered data. The AVERAGE, MIN, and MAX functions do not. The determination of each salesperson’s commission requires nesting the VLOOKUP function within the IF function. The commission formula must look up the hourly sales quota, determine the daily sales quota, take the difference between the daily sales quota and the daily sales, and then multiply the applicable commission rate by the amount of sales above the quota. Test Your Design After creating the Productivity workbook described in the case scenario, you should test your design. Perform the following steps. 1. Vasanthi hired four new sales personnel. Enter their data into the worksheets. Leandra Shekel is classified as a PT1 and works in the Men’s Clothing Department. She worked 4 hours each day, Sunday through Thursday. Her sales Sunday through Thursday were $1,400.98, $1,500.42, $750.32, $550.08, and $900.78, respectively. Darise Ferrer was hired as an S1 and works in the Linen Department. She worked 8- hour shifts, Tuesday through Saturday. Her sales were $2,500.98, $878.23, $1,503.28, $602.98, and $1,304.17, respectively. Paulina Renfro is classified as an S1 and works in the Children’s Department. She worked 8-hour shifts, Tuesday through Saturday. Her sales were $1,500.56, $402.22, $1,090.80, $908.43, and $350.98. Sethana Aynes is classified as a PT1 and works in the Women’s Clothing Department. She worked 8 hours on Monday and her sales were $502.88. She worked 6 hours on Tuesday, and her sales were $608.12. 2. Which salespeople received more than $1,500 in commissions last week? Which salespeople did not receive a commission last week? 3. Based on past performance, what would happen if Vasanthi increased the sales quota for each position by $75 per hour? What would happen if she decreased the sales quota for each position by $75 per hour? 4. What would happen if Vasanthi increased each sales position’s commission by a half of a percent? 5. For the week, what are the average sales for the S1 employees working in the Children’s Department? For the Children’s Department S1 employees, which employee had the lowest sales for the week? For the Children’s Department S1 employees, which employee had the maximum sales for the week? 6. For the week, what are the average sales for the PT1 employees working in the Women’s Department? For the Women’s Department PT1 employees, which employee had the lowest sales for the week? For the Women’s Department PT1 employees, which employee had the most sales for the week? 7. For the week, Vasanthi wants to see the total commissions paid by each department, each department’s percentage of the total commissions paid for all departments, and the total sales for each department. 8. Based on your observations of the sales, sales quotas, and commissions, how might the sales quota system be improved? Question 2: Excel Dashboard In this part you are going to build a Dashboard for the Milligan Department Store using Excel. 1. Download the Excel file sales_data_student 2. Watch the Sales Dashboard Tutorial video and follow along using your downloaded Excel file a. Once you have finished the tutorial you are ready to design and build your own Dashboard 3. Build your own dashboard using the Milligan Department Store file a. Create a new worksheet named Dashboard and position it as the very first worksheet in your file b. Create Six charts and at least Four slicers. The names of the worksheets that contain the underlying Pivot Tables are to be named DB1, DB2, etc. c. Two of the charts must be able to show the following:  prepare a pie chart showing the commissions by department showing percentages, and  prepare a pie chart that can be filtered by department, comparing the department’s sales representatives’ sales. d. One of your charts must be a combination column-line chart e. One of the slicers must show groupings of data f. The other three charts can be of your own choosing. What needs to be submitted? Excel workbook as per the case scenario – show the answers to each question (i.e. 12 questions in the Information Specifications section and the eight questions in the Test Your Design section), all on separate worksheets. In some instances, you may need to answer a question which should be typed into a textbox. Prefix the worksheet names with IS1, IS2, etc. and TYD1, TYD2, etc. The Excel workbook also needs to contain a Dashboard worksheet (as the very first worksheet) containing your four slicers and six Pivot Charts. The worksheets containing the underlying Pivot Tables are to be named DB1, DB2, etc.