Excel Data for Practice Free Download

You should know how to use the following functions: IFERROR, IFS, AND, COUNTIFS, SUM, COUNTIF, SUMIF, WEEKDAY, and SUMPRODUCT. Additionally, you should know about the Conditional Formatting and change the font color to get the solution to the problem.

Download the Practice Files

Problem Overview

This is a dataset about a small electronics retail company. There are two sales employees, working on separate shifts. The payment is per shift. The night shift pays more. The cost price and selling price are the accumulation of all items sold on each shift per day. We have 5 tasks to solve. Additional data is provided in the “Reference Data” sheet. The “Solution” sheet contains the answers. Here is a snapshot of the “Problem” sheet, which is only showing the first 19 rows of the 88 rows from the dataset.

• Task 01 – Find the Profit Percentage for Each Sale.
  • Solution: You can use the formula “(selling price – cost price)/cost price” to find the profit. Then, you can divide the profit by the cost price to get the profit percentage.

• Task 02 – Calculate the commission for sales.
  • Solution: A certain profit percentage will reward the salesman with a commission. The exact amount is provided in the “Reference Data” sheet. You can integrate that data with a conditional formula. We have created ours by combining the AND, IFERROR, and IFS functions. Moreover, the following image shows the sheets of our Excel file.

• Task 03 – Count the number of shifts for each employee.
  • Solution: You can use the COUNTIFS function to calculate this. Alternatively, you can use multiple COUNTIF functions to do so.

• Task 04 – Determine the Income for Employes based on Shift
  • Solution: The payment is higher for the night shift. So, the employee who works more on the night shift will earn more.

• Task 05 – On average, which day of the week is the most profitable for the company?
  • Solution: This can be a tricky question for some. You can either solve this by creating helper columns or using the SUMPRODUCT function. We have shown both ways in the Excel file. Here, Monday is 1, Tuesday is 2, and so on. Additionally, you can use the conditional formatting to highlight the maximum value.

Here’s an overview of the completed sheet.