Challenge Scenario
In retail merchandising and commercial pricing strategy, profit margins vary significantly across merchandise lines. Applying a flat discount across all departments would erode margins on low-margin goods like electronics, while being insufficiently competitive for higher-margin apparel and furniture.
As a Commercial Pricing Analyst, you have been provided with the promotional pricing policy for the upcoming seasonal campaign:
- Electronics: 5% promotional discount (
0.05).
- Furniture: 20% clearance discount (
0.20).
- Clothing: 15% seasonal discount (
0.15).
Your goal is to build dynamic pricing formulas in Columns D and E to compute the exact discount deduction and final net selling price, then roll up total campaign impact at the bottom.
Your Sheet Layout
Before entering promotional pricing formulas, take a moment to understand the sheet architecture. Separating raw product catalog inputs from discount deduction logic and executive revenue summaries makes commercial models clear and easy to maintain.
The sheet is organized into three distinct functional zones:
- Product Catalog Zone (Columns A–C): Lists product items, department categories, and baseline catalog prices.
- Pricing Computation Zone (Columns D & E): Applies category-specific discount percentages and calculates final net selling prices.
- Campaign Financial Summary (Rows 7–8): Aggregates total promotional savings given to customers and net revenue collected.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Product Name, Category, Base Price |
Data (Catalog) |
Master product inventory |
Read-only source items and base retail prices. |
D2:D5 |
Discount Amount |
Transformation Zone |
=IFS(B2="Electronics", C2*0.05, B2="Furniture", C2*0.2, B2="Clothing", C2*0.15) |
Apply category discount rates (5%, 20%, 15%) to calculate dollar savings. |
E2:E5 |
Final Price |
Net Pricing Zone |
=C2 - D2 |
Subtract discount dollar savings from the base catalog price. |
B8 |
Total Savings |
Summary (Promotional Cost) |
=SUM(D2:D5) |
Sum total discount savings awarded to customers across all items. |
C8 |
Total Revenue |
Summary (Net Cashflow) |
=SUM(E2:E5) |
Sum total final selling prices to calculate net campaign revenue. |
This layout ensures clear visibility into commercial margins, allowing analysts to balance aggressive promotional discounts with company profitability.
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column D (D2:D5), calculate Discount Amount based on Category (5% Electronics, 20% Furniture, 15% Clothing) using IFS.
2
In Column E (E2:E5), calculate Final Price by subtracting Discount from Base Price.
3
In cell B8, calculate Total Savings using SUM.
4
In cell C8, calculate Total Revenue using SUM.
Solution & Formula Breakdown
In retail and e-commerce, category-based pricing policies ensure margins stay protected. Let's walk through the formula mechanics step by step.
1. Multi-Tier Discount Calculation with IFS (D2:D5)
=IFS(B2="Electronics", C2*0.05, B2="Furniture", C2*0.2, B2="Clothing", C2*0.15)
Here is how the IFS function determines the discount:
- Condition 1: If Category is
"Electronics", multiply Base Price by 5% (C2 * 0.05). For the $800 Smart TV, discount = $40.
- Condition 2: If Category is
"Furniture", multiply Base Price by 20% (C2 * 0.20). For the $350 Office Desk, discount = $70.
- Condition 3: If Category is
"Clothing", multiply Base Price by 15% (C2 * 0.15). For the $60 Cotton Hoodie, discount = $9.
2. Calculating Net Selling Price (E2:E5)
=C2 - D2
Simply subtract the computed discount in Column D from the original base price in Column C (e.g. 800 - 40 = 760).
3. Campaign Financial Rollup (B8 & C8)
- Cell B8 (Total Savings):
=SUM(D2:D5) → Evaluates to $129.
- Cell C8 (Total Net Revenue):
=SUM(E2:E5) → Evaluates to $1,281.
You can also use the SWITCH function for exact-match category rules: =SWITCH(B2, "Electronics", C2*0.05, "Furniture", C2*0.2, "Clothing", C2*0.15). It is even cleaner because you only have to reference cell B2 once!