Arithmetic operators in formulas in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales assistant at a small retail store.

📋 Request: Your manager wants you to calculate the total price for each product sold, including tax, and find the overall total sales.

📊 Data: You have a list of products sold with their unit price and quantity sold. The tax rate is 8%.

🎯 Deliverable: Create a spreadsheet that calculates the total price before tax, tax amount, total price after tax for each product, and the overall total sales.

Sample Data

Product	Unit Price	Quantity Sold
Notebook	2.50	10
Pen	1.20	15
Eraser	0.50	20
Marker	3.00	5
Folder	4.00	7

1

Step 1: Calculate the total price before tax for each product by multiplying Unit Price by Quantity Sold.

In cell D2, enter =B2*C2 and copy down for all products.

Expected Result

For Notebook: 2.50*10 = 25.00; Pen: 1.20*15 = 18.00; Eraser: 0.50*20 = 10.00; Marker: 3.00*5 = 15.00; Folder: 4.00*7 = 28.00

2

Step 2: Calculate the tax amount for each product by multiplying the total price before tax by 8%.

In cell E2, enter =D2*0.08 and copy down for all products.

Expected Result

For Notebook: 25.00*0.08 = 2.00; Pen: 18.00*0.08 = 1.44; Eraser: 10.00*0.08 = 0.80; Marker: 15.00*0.08 = 1.20; Folder: 28.00*0.08 = 2.24

3

Step 3: Calculate the total price after tax for each product by adding the total price before tax and the tax amount.

In cell F2, enter =D2+E2 and copy down for all products.

Expected Result

For Notebook: 25.00+2.00 = 27.00; Pen: 18.00+1.44 = 19.44; Eraser: 10.00+0.80 = 10.80; Marker: 15.00+1.20 = 16.20; Folder: 28.00+2.24 = 30.24

4

Step 4: Calculate the overall total sales by summing the total price after tax for all products.

In cell F7, enter =SUM(F2:F6).

Expected Result

Overall total sales = 27.00 + 19.44 + 10.80 + 16.20 + 30.24 = 103.68

Final Result

Product   Unit Price  Quantity Sold  Total Before Tax  Tax Amount  Total After Tax
Notebook      2.50           10           25.00           2.00          27.00
Pen           1.20           15           18.00           1.44          19.44
Eraser        0.50           20           10.00           0.80          10.80
Marker        3.00            5           15.00           1.20          16.20
Folder        4.00            7           28.00           2.24          30.24

Overall Total Sales: 103.68

✓Multiplying unit price by quantity gives total before tax.

✓Tax is 8% of the total before tax.

✓Adding tax to total before tax gives total after tax.

✓Overall total sales sum all total after tax values.

Bonus Challenge

Add a discount column that applies a 10% discount on the total before tax if quantity sold is more than 10, then recalculate tax and total after tax accordingly.

Show Hint

Use an IF formula to check if quantity is greater than 10, then multiply total before tax by 0.9 for discount.