In a spreadsheet, column A (A2:A101) contains product codes, column B contains the unit price, and column C contains the quantity sold. Column D must show the revenue for each product, applying the following discount policy:

- If quantity  50, apply a 10 % discount on the unit price,
- If 20 ≤ quantity ≤ 50, apply a 5 % discount,
- Otherwise, no discount.

(a) Write the formula to be entered in D2 (and copied down) that implements this policy using only Excel functions.
(b) Using that formula, calculate the revenue for a product with code "P075", unit price = 120, and quantity = 68.

Question

In a spreadsheet, column A (A2:A101) contains product codes, column B contains the unit price, and column C contains the quantity sold. Column D must show the revenue for each product, applying the following discount policy:

- If quantity > 50, apply a 10 % discount on the unit price,
- If 20 ≤ quantity ≤ 50, apply a 5 % discount,
- Otherwise, no discount.

(a) Write the formula to be entered in D2 (and copied down) that implements this policy using only Excel functions.
(b) Using that formula, calculate the revenue for a product with code "P075", unit price = 120, and quantity = 68.

Accepted Answer

Formula: =IF(C2>50, B2*0.9*C2, IF(C2>=20, B2*0.95*C2, B2*C2)); Revenue = 7344

City Guilds Model Questions Vol1 2021 Information Technology Question 44

Answer Options

About This Question

Master the Exam!