EPPM 1113/ EPPD 1063
Lab Test 1
(Duration: 2 hours 30minutes)
You have been provided with a dataset containing records of a fictional company’s sales data. Follow the instructions below to answer the questions using Excel formulas. Save your answers and submit your Excel file with your Words file (mail merge question) on UKMFolio.
Questions:
1. Use VLOOKUP to find the region for Product ID 105.
2. Use SUMIF to find the total sales of the product named “AlphaX.”
3. Calculate the average units sold across all regions.
4. Determine the maximum units sold in any single transaction.
5. Identify the minimum price per unit for products in the "North" region.
6. Find the total sales for transactions where the salesperson is "James".
7. Use COUNTIF to count transactions in the “South” region.
8. Create a Pivot Chart to display total sales by region.
9. Use the IF function to mark transactions as “High” if total sales exceed 5000; otherwise, mark as “Low.” Create a new column next to the Total Sales and name it as Transaction Status.
10. Calculate the total revenue by summing all "Total Sales" values. Use SUM function
11. Autofill the cells to calculate the difference between each transaction’s units sold and the average units sold (Average is based on Question no 3).
12. Find the average price per unit for products sold by “Sarah.”
13. Use MIN to find the range of sales dates (earliest and latest date).
14. Calculate the sum of units sold for products with IDs above 150.
15. Create a line chart to show total sales trends over time.
16. Find the median price per unit across all products.
17. Count the number of salespeople using COUNTA.
18. Use IF to calculate a 10% bonus for transactions with units sold above 100.
19. Autofill formulas down the column to calculate the final price after applying a 5% discount on all products.
20. Identify the second-highest total sales using LARGE.
21. Calculate the total units sold using SUMIF for product – P110.
22. Use AVERAGEIF to find the average total sales for the "East" region.
23. Use IF OR to divide total sales by 3 if the region is “East” or units sold are over 50; otherwise, return “N/A”.
24. Determine the total number of transactions. Use COUNTA
25. Use IF AND to classify transactions as "Medium" if total sales are above 2000 and units sold are under 100; otherwise, return "Check Data."
26. Please use the Mail Merge function to create a personalized letter of appreciation for each salesperson based on their sales data. Use the provided template below to insert relevant fields, including the salesperson’s name, region, total sales, units sold, and product name.
You do not need to submit all 200 letters. Instead, save and submit the template file with the mail merge fields inserted, along with 5 sample letters generated from the merge. Make sure to preview a few records to confirm accuracy in your setup before completing the samples.