A04: Conditional Aggregares and Logical Functions (S1)

In this assignment, you will apply Excel skills such as conditional aggregates, logical functions and referencing.

Download file from the link below. Save it on your laptop and work on it.

Instructions are given in the worksheet Instructions. For your convenience, the same instructions are pasted below.

Context

The Data worksheet contains sales data of electronic items (Laptop, Tablet, etc.) from a distributor. Different salespersons make bulk sales to retailers across multiple regions.
Your task is to use Excel functions to create summary analyses using the specified cells and ranges in the Data worksheet.

Task 1: Create Unique Lists (6 points)
Create unique lists of Region, Salesperson, Product, and Month in columns O through R below the respective headers starting row 2.
Do NOT type the values manually.
Use Remove Duplicates to generate the lists (copy in an empty area to remove duplicates, then cut and paste in columns O-R).
Sort each list alphabetically.

Task 2: Total Sales by Region (4 points)
Copy the unique Region values to column O starting at O15.
Calculate total Sales in column P. Write the formula once in P15 and copy down. Use the Region value in column O as the criteria.
Do NOT retype the formula for each row.

2B: Region Performance Flag (3 points)
A regional sales target is provided in cell Q12. In column Q, under Region Flag, create a performance flag for each region.
If Region Sales target Strong, otherwise Weak. Write formula once and copy down.

Task 3: Count of Orders by Month and Region (6 points)
Copy unique Regions to column T starting at T4.
Transpose the unique Months across row 3 starting at U3 (under <– Months –>)
In cell U4 write a formula to count orders by:
Region (column T)
Month (row 3)

Requirements:
Copy the formula across columns and down rows. Do NOT retype the formula.
Use appropriate referencing so the formula works everywhere.

3B: Determine if there was a spike in monthly demand (3 points)
Management wants to identify whether any month experienced a demand spike across regions.
Compare each regions order count with the Monthly Demand Threshold provided in cell X2. If order count for any region in a given month is more than or equal to the threshold, show Spike in row 11 for that month, otherwise show blank (“”)
Use IF with OR to check the regional order counts within each month. Write formula once and copy across.
Do not hardcode threshold value.

Task 4: Average Units Sold by Salesperson and Product (6 points)
Place unique Salespersons in column T starting at T16.
Transpose unique Products across row 15 starting at U15 (below <– Products –>)
In cell U16, calculate the average Units for:
Salesperson (column T)
Product (row 15)
Show results with 1 decimal point.
Requirements:
Use proper referencing so the formula copies correctly across rows and columns. Do NOT retype formulas.

4B. Find Employee performance tier (4 points)
Based on the average Laptop units sold in Task 4, determine each employees performance tier in column S (Gold, Silver, Bronze, or Poor).
The tier thresholds are provided in rows 13 and 14.
For example, if the average Laptop units sold is 105 or higher, the performance tier should be Gold.
Use the IFS function to assign the appropriate tier. Reference the threshold cells do NOT hardcode values. Write the formula once and copy it down the column.

Task 5: Monthly Sales by Salesperson (5 points)
Place unique Months in column O starting at O28.
Place unique Salespersons across row 27 starting at P27 (under <– Salespersons –>)
In cell P28, calculate total Sales by:
Salesperson (row 27)
Month (column O)
Requirements:
Use correct referencing so formulas copy across and down without modification.
Do NOT retype formulas.

Task 6: Commission Calculation (5 points)
The commission rate is provided in cell T25.
Using the totals from Task 5:
Calculate total Sales by each salesperson in row 35 (sum of monthly sales in rows above).
Calculate the commission per salesperson using the commission rate for each salesperson.
Do NOT hardcode (i.e. use fixed values for) the commission rate, use proper cell reference.

Task 7: Format Sales as Currency (3 points)
Format all Sales values (original and calculated) as Currency ($).
Ensure Units and Counts remain in General format.

Submission Requirements

• Submit .xlsx file only – any other type of file will receive zero credit.
• Make sure that the file you are submitting is the one you worked on. Check file modification date before uploading.
• Ensure formulas are used manually typed values will not receive credit
• File names must not begin with _ or ~
• Write each formula once and copy whenever possible. Retyping formulas may result in point deductions.