Excel Discount Calculation – Step-by-Step Solution - Std VI
📊 Product Sales Report - Excel Style
Interactive Excel-like spreadsheet with formulas and calculations
📋 Sales Data Table (Excel Format)
| A | B | C | D | E | F | G | H | |
| 1 | Sr.No | Product Name | Price | Quantity | Total | Discount % | Discount Amount | Total Pay Amount |
|---|---|---|---|---|---|---|---|---|
| 2 | 1 | Product A | 500.00 | 8 | =C2*D2 4,000.00 | 6% | =E2*F2 240.00 | =E2-G2 3,760.00 |
| 3 | 2 | Product B | 450.00 | 5 | =C3*D3 2,250.00 | 3% | =E3*F3 67.50 | =E3-G3 2,182.50 |
| 4 | 3 | Product C | 478.00 | 8 | =C4*D4 3,824.00 | 5% | =E4*F4 191.20 | =E4-G4 3,632.80 |
| 5 | 4 | Product D | 854.00 | 9 | =C5*D5 7,686.00 | 7% | =E5*F5 538.02 | =E5-G5 7,147.98 |
| 6 | 5 | Product E | 645.00 | 40 | =C6*D6 25,800.00 | 2% | =E6*F6 516.00 | =E6-G6 25,284.00 |
| 7 | ||||||||
| 8 | TOTALS | =SUM(D2:D6) 70 | =SUM(E2:E6) 43,560.00 | =SUM(G2:G6) 1,552.72 | =SUM(H2:H6) 42,007.28 | |||
Total Items
70
Subtotal
₹43,560
Total Discount
₹1,552.72
Final Amount
₹42,007.28
📐 Excel Formula Reference
Column E: Total (Price × Quantity)
Row 2: =C2*D2
Row 3: =C3*D3
Row 4: =C4*D4
Multiply Price by Quantity for each product
Column G: Discount Amount (Total × Discount %)
Row 2: =E2*F2
Row 3: =E3*F3
Row 4: =E4*F4
Multiply Total by Discount %
Column H: Total Pay Amount (Total - Discount)
Row 2: =E2-G2
Row 3: =E3-G3
Row 4: =E4-G4
Subtract Discount Amount from Total
Row 8: Summary Totals
Total Quantity: =SUM(D2:D6)
Subtotal: =SUM(E2:E6)
Total Discount: =SUM(G2:G6)
Final Amount: =SUM(H2:H6)
Use SUM function to calculate totals for each column
🔢 Step-by-Step Calculations for Each Product
Product A - Row 2
1
E2: Calculate Total
=C2*D2
= 500 × 8
= ₹4,000.00
2
G2: Calculate Discount Amount
=E2*F2
= 4,000 × 0.06
= ₹240.00
3
H2: Calculate Total Pay Amount
=E2-G2
= 4,000 - 240
= ₹3,760.00
Product B - Row 3
1
E3: Calculate Total
=C3*D3
= 450 × 5
= ₹2,250.00
2
G3: Calculate Discount Amount
=E3*F3
= 2,250 × 0.03
= ₹67.50
3
H3: Calculate Total Pay Amount
=E3-G3
= 2,250 - 67.50
= ₹2,182.50
Product C - Row 4
1
E4: Calculate Total
=C4*D4
= 478 × 8
= ₹3,824.00
2
G4: Calculate Discount Amount
=E4*F4
= 3,824 × 0.05
= ₹191.20
3
H4: Calculate Total Pay Amount
=E4-G4
= 3,824 - 191.20
= ₹3,632.80
Product D - Row 5
1
E5: Calculate Total
=C5*D5
= 854 × 9
= ₹7,686.00
2
G5: Calculate Discount Amount
=E5*F5
= 7,686 × 0.07
= ₹538.02
3
H5: Calculate Total Pay Amount
=E5-G5
= 7,686 - 538.02
= ₹7,147.98
Product E - Row 6
1
E6: Calculate Total
=C6*D6
= 645 × 40
= ₹25,800.00
2
G6: Calculate Discount Amount
=E6*F6
= 25,800 × 0.02
= ₹516.00
3
H6: Calculate Total Pay Amount
=E6-G6
= 25,800 - 516
= ₹25,284.00
Summary Totals - Row 8
1
D8: Total Quantity
=SUM(D2:D6)
= 8 + 5 + 8 + 9 + 40
= 70 items
2
E8: Subtotal
=SUM(E2:E6)
= 4,000 + 2,250 + 3,824 + 7,686 + 25,800
= ₹43,560.00
3
G8: Total Discount
=SUM(G2:G6)
= 240 + 67.50 + 191.20 + 538.02 + 516
= ₹1,552.72
4
H8: Final Amount
=SUM(H2:H6)
= 3,760 + 2,182.50 + 3,632.80 + 7,147.98 + 25,284
= ₹42,007.28
Comments
Post a Comment