ASSIGNMENT – 12
Answer Key – Create Pivot Table Using Data
Sales Data – Total Sales
Grand Total Sales = ₹2,63,250
Q.1 Total Sales by Country
Create Pivot Table:
-
Rows: Country
-
Values: Sales → Sum
| Country | Total Sales |
|---|---|
| India | ₹1,15,250 |
| USA | ₹85,050 |
| UK | ₹62,950 |
| Grand Total | ₹2,63,250 |
Answer:
India has the highest total sales: ₹1,15,250
Q.2 Total Sales by Quarter
| Quarter | Total Sales |
|---|---|
| Qtr 1 | ₹65,150 |
| Qtr 2 | ₹80,200 |
| Qtr 3 | ₹58,550 |
| Qtr 4 | ₹59,350 |
| Grand Total | ₹2,63,250 |
Answer:
Qtr 2 has the highest sales: ₹80,200
Q.3 Total Sales by Last Name
| Last Name | Total Sales |
|---|---|
| Sharma | ₹46,200 |
| Verma | ₹53,500 |
| Singh | ₹35,900 |
| Gupta | ₹35,300 |
| Mehta | ₹36,350 |
| Kapoor | ₹56,000 |
| Grand Total | ₹2,63,250 |
Answer:
Kapoor has the highest total sales: ₹56,000
Q.4 Total Sales of India
From the Country Pivot Table:
| Country | Total Sales |
|---|---|
| India | ₹1,15,250 |
Answer:
₹1,15,250
Q.5 Total Sales of USA
| Country | Total Sales |
|---|---|
| USA | ₹85,050 |
Answer:
₹85,050
Q.6 Country in Rows and Quarter in Columns
Pivot Table Setup:
-
Rows → Country
-
Columns → Quarter
-
Values → Sum of Sales
| Country | Qtr 1 | Qtr 2 | Qtr 3 | Qtr 4 | Grand Total |
|---|---|---|---|---|---|
| India | ₹35,000 | ₹21,300 | ₹29,450 | ₹29,500 | ₹1,15,250 |
| USA | ₹20,850 | ₹49,950 | ₹0 | ₹14,250 | ₹85,050 |
| UK | ₹9,300 | ₹8,950 | ₹29,100 | ₹15,600 | ₹62,950 |
| Grand Total | ₹65,150 | ₹80,200 | ₹58,550 | ₹59,350 | ₹2,63,250 |
Q.7 Last Name in Rows and Country in Columns
Pivot Table Setup:
-
Rows → Last Name
-
Columns → Country
-
Values → Sum of Sales
| Last Name | India | USA | UK | Grand Total |
|---|---|---|---|---|
| Gupta | ₹0 | ₹10,750 | ₹24,550 | ₹35,300 |
| Kapoor | ₹21,300 | ₹34,700 | ₹0 | ₹56,000 |
| Mehta | ₹19,600 | ₹7,450 | ₹9,300 | ₹36,350 |
| Sharma | ₹35,000 | ₹0 | ₹11,200 | ₹46,200 |
| Singh | ₹22,500 | ₹13,400 | ₹0 | ₹35,900 |
| Verma | ₹16,850 | ₹18,750 | ₹17,900 | ₹53,500 |
| Grand Total | ₹1,15,250 | ₹85,050 | ₹62,950 | ₹2,63,250 |
Q.8 Which Country has the Highest Total Sales?
From the Pivot Table:
| Rank | Country | Total Sales |
|---|---|---|
| 1 | India | ₹1,15,250 |
| 2 | USA | ₹85,050 |
| 3 | UK | ₹62,950 |
Answer:
India has the highest total sales of ₹1,15,250.
Q.9 Which Quarter has the Highest Total Sales?
| Rank | Quarter | Total Sales |
|---|---|---|
| 1 | Qtr 2 | ₹80,200 |
| 2 | Qtr 1 | ₹65,150 |
| 3 | Qtr 4 | ₹59,350 |
| 4 | Qtr 3 | ₹58,550 |
Answer:
Qtr 2 has the highest total sales of ₹80,200.
Q.10 Last Name-wise Total Sales for Each Quarter
Pivot Table Setup:
-
Rows → Last Name
-
Columns → Quarter
-
Values → Sum of Sales
| Last Name | Qtr 1 | Qtr 2 | Qtr 3 | Qtr 4 | Grand Total |
|---|---|---|---|---|---|
| Gupta | ₹0 | ₹19,700 | ₹0 | ₹15,600 | ₹35,300 |
| Kapoor | ₹0 | ₹41,750 | ₹0 | ₹14,250 | ₹56,000 |
| Mehta | ₹16,750 | ₹0 | ₹19,600 | ₹0 | ₹36,350 |
| Sharma | ₹35,000 | ₹0 | ₹11,200 | ₹0 | ₹46,200 |
| Singh | ₹13,400 | ₹0 | ₹9,850 | ₹12,650 | ₹35,900 |
| Verma | ₹0 | ₹18,750 | ₹17,900 | ₹16,850 | ₹53,500 |
| Grand Total | ₹65,150 | ₹80,200 | ₹58,550 | ₹59,350 | ₹2,63,250 |
Final Answer Summary
| Question | Answer |
|---|---|
| Q1 – Highest Country Sales | India – ₹1,15,250 |
| Q2 – Highest Quarter Sales | Qtr 2 – ₹80,200 |
| Q3 – Highest Last Name Sales | Kapoor – ₹56,000 |
| Q4 – India Sales | ₹1,15,250 |
| Q5 – USA Sales | ₹85,050 |
| Q6 – Country × Quarter | See Pivot Table above |
| Q7 – Last Name × Country | See Pivot Table above |
| Q8 – Highest Sales Country | India – ₹1,15,250 |
| Q9 – Highest Sales Quarter | Qtr 2 – ₹80,200 |
| Q10 – Last Name × Quarter | See Pivot Table above |
| Grand Total Sales | ₹2,63,250 |