15. Final Project: FreshGo Maharashtra Monthly Report
15.4 Stage 3 – PivotTable Analysis
Steps in Excel
- On a
Calcsheet build pivots fromtblOrders(Module 13.3):pvtCity(City × Sum of Amount, Status = Delivered),pvtTrend(Order Date by day),pvtCategory(Category × City),pvtOnTime(City × On Time, Show Values As % of Row Total),pvtKPI. - City vs target: next to
pvtCity,=GETPIVOTDATA("Sum of Amount",$A$3,"City","Pune")/XLOOKUP("Pune",tblTargets[City],tblTargets[Monthly target]). - Top categories per city: in
pvtCategoryuse Row Labels filter › Value Filters › Top 10… › Top 3 Items by Sum of Amount. - Write each answer to the five questions in one sentence on the
Notessheet.
Worked example – answer format (your numbers will differ).
| Question | Answer (example) |
|---|---|
| Q2 City | Pune gave the highest sales (about 37%); Solapur is at 92% of target. |
| Q3 Trend | Daily sales rose sharply from 14-09-2026 with the Ganeshotsav start, with modaks and pooja items leading. |
| Q5 Delivery | All cities except Nagpur delivered ≥ 90% of orders within 12 minutes. |
About this data: Teaching sample for a fictional shop “Orbit Logistics”. Figures and names (e.g. Meera Joshi) are made up for this lesson on stage 3 pivottable analysis.
Ravindra Bagale's Tip
Many students build pivots but don't write the "answer" from them – they just show numbers. A manager doesn't want numbers; they want a conclusion: "Solapur is at 92% of target". Write one sentence for each question, and use that same sentence as the chart title on the dashboard.
Ravindra Bagale's Tip – मराठी
बरेच students pivot बनवतात पण त्यातून "answer" लिहित नाहीत – फक्त आकडे दाखवतात. Manager ला आकडे नको, निष्कर्ष (conclusion) हवा: "Solapur is at 92% of target". प्रत्येक प्रश्नासाठी एक वाक्य लिहा; हेच वाक्य dashboard च्या chart title मध्ये वापरा.
Ravindra Bagale's Tip – हिंदी
बहुत से students pivot बनाते हैं पर उससे "answer" नहीं लिखते – सिर्फ़ आँकड़े दिखाते हैं. Manager को आँकड़े नहीं, नतीजा (conclusion) चाहिए: "Solapur is at 92% of target". हर सवाल के लिए एक वाक्य लिखो; वही वाक्य dashboard के chart title में इस्तेमाल करो.
Practice task
Build the five pivots, calculate achievement vs target for all six cities, and write one-sentence answers to all five brief questions on the Notes sheet.
Loading practice tasks...
- Total sales (Easy): Add up Sales (₹) in E2:E11. (sheet "Data")
- Average orders (Easy): Average of Orders in D2:D11, rounded to 1 decimal. (sheet "Data")
- Pune rows (Easy): How many rows have City "Pune"? (sheet "Data")
- Max sales (Easy): Largest Sales (₹) in E2:E11. (sheet "Data")
- Grocery count (Easy): How many rows have Category "Grocery"? (sheet "Data")
- Pune sales (Medium): Total of Sales (₹) where City is "Pune". (sheet "Data")
- Snacks orders (Medium): Total of Orders where Category is "Snacks". (sheet "Data")
- May grocery sales (Medium): Sales where Month is "May" and Category is "Grocery". (sheet "Data")
- Pune share (Medium): Pune sales / all sales, 3 decimals. (sheet "Data")
- Jun sales (Hard): Total of Sales (₹) where Month is "Jun". (sheet "Data")
- High order rows (Hard): How many rows have Orders > 100? (sheet "Data")
- Above avg sales? (Hard): "High" if E2 > average sales else "Low". (sheet "Data")
- Unique cities (Hard): How many unique City values? (sheet "Data")
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
