- Task 1 Worksheet 1 3 Questions 6 Marks Sales Data In Columns A B Has Been Imported From Your Company S Sales Databa 1 (38.58 KiB) Viewed 55 times
- Task 1 Worksheet 1 3 Questions 6 Marks Sales Data In Columns A B Has Been Imported From Your Company S Sales Databa 2 (68.48 KiB) Viewed 55 times
- Task 1 Worksheet 1 3 Questions 6 Marks Sales Data In Columns A B Has Been Imported From Your Company S Sales Databa 3 (67.73 KiB) Viewed 55 times
Task 1 (Worksheet 1). 3 questions (6 marks) Sales data in columns A:B has been imported from your company's sales database. The Source column (A) contains labels indicating which employee made the sale (Employee_ID), then a hyphen, then the region in which the sale was made (i.e. N,E,S,W - North, East, South, West regions). For example, 103-N means the employee with ID 103 made the sale in the North region. The Sales Analysis table in columns D:) requires a number of formulas to be written as per the task list below, some of which depend upon the data table in columns L:M.
Cells Q1 Task/problem Example Functions/features you should E12:H21 Write a formula which will Employee 102-N made Define A:B (i.e. the entire produce the sales amount for $7,815 sales in the North A and B columns) as a each employee per region. i.e. for region, $1,712 in the East, Named Range called 101 in North, East, South and $9,363 in the South, and "Sales" and use this in West sales regions. $5,827 in the West. your VLOOKUP. 2 marks In some cases, an employee has not made any sales for a given region, such as employee 104 in the South sales region. This may cause an error (e.g., "#N/A"), therefore in your formula use IFERROR to produce $0 instead. Ensure you use the correct reference types so you can copy the formula to the entire range (E12:H21), i.e. for all employees and all sales regions. Do this with one formula that is created for E12 and copied to E12:H21.
Q2 112:121 Use COUNTIF to determine if an employee has made no sales ($0) for a region. 2 marks Write a formula to evaluate Employee 101 sold more employee performance for the than: $4,000 in the North month, according to the region, $5,000 in the East following rules. region, $3,000 in the South region and $2,000 in If there are any sales regions for the West region. any individual employee which Therefore, 112 should have no sales ($0), the evaluation display Good. is "Interview", as the employee is required to explain reasons for Employee 104 had no this to management sales in the South region and therefore 115 should Otherwise, if the North region's be "Interview". sales are greater than or equal to $4,000 and the East region's sales is greater than or equal to $5,000 and South's sales is greater than or equal to $3,000 and West's sales are greater than or equal to $2,000, then the evaluation is "Good", otherwise, the evaluation is to be left blank (do not use a space for this). Q3 J12:J21 2 marks Enter the formula into 112 and copy the formula down for all employees. Employees are entitled to a Employee 103 made Use arithmetic operations, bonus payment, which is the $23,387 in sales minus the appropriate reference percentage given in M11, minimum total sales types, brackets and pay multiplied by the total of all sales ($20,000) equals $3,387. attention to the order of for that employee in all four 30% of $3,387 is $1,016 operations. regions minus the minimum total sales in M10 (bonus = bonus rate Therefore, J14 should (total sales for the four regions display $1,016. - minimum sales)). If the employee does not meet the minimum total sales, the bonus is $0. Enter the formula into J12 and copy the formula down for all employees.
SALES ANALYSIS: JANUARY Sales by region East South DATA Minimum total sales Bonus rate Evaluation Bonus $20,000 0.3 North West Employee ID 101 102 103 104 105 106 107 108 109 110 TRANSACTIONS Source Amount 101-N $ 7,830 101-E $ 6,561 101-5 $ 4,493 101-W $ 5,183 102-N $ 7,815 102-E $ 1,172 102-5 $ 9,363 102-W $ 5,827 103-N $ 1,720 103-E $ 8,035 103-5 $ 7,554 103-W $ 6,078 104-N $ 6,651 104-E $ 8,053 104-W $ 7,776 105-N $ 4,890 105-E $ 8,121 105-W $ 2,550 106-N $ 1,247 106-E $ 5,299 106-S $ 7,686 106-W $ 5,507 107-N $ 8,731 107-5 $ 6,738 107-W $ 2,392 108-N $ 9,672 108-E $ 4,054 108-5 $ 1,697 109-N $ 2,133 109-E $ 8,358 109-5 $ 9,789 109-W $ 6,801 110-N $ 8,991 110-E $ 2,550 110-5 $ 6,282