15. Final Project: Blinkit 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. |
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.