21.9 One-Page Executive MIS
The ask. Ravindra Bagale presents a one-page MIS to the directors every month: sales, orders, AOV, delivery rate and month-on-month growth for the last three months, with three lines of commentary. All numbers must come from formulas, not typing.
Sheet Monthly (built from tblOrders with SUMIFS on month start dates). Fictional:
| A: Month | B: Sales (₹) | C: Orders | D: Delivered orders |
|---|---|---|---|
| Sep | 5,12,000 | 1,820 | 1,712 |
| Oct | 5,98,000 | 2,050 | 1,951 |
| Nov | 6,71,000 | 2,240 | 2,150 |
What to deliver (one under the other):
- Calculate the monthly rows with SUMIFS / COUNTIFS on the order date between the month start and EOMONTH.
- Add AOV = Sales ÷ Orders, rounded to the rupee.
- Add Delivery rate = Delivered ÷ Orders.
- Add Growth = this month's sales ÷ last month's sales − 1 (blank for the first month).
- Show four KPI cards for the latest month on the MIS sheet.
- Write three commentary lines: what moved, why, and the action.
Key formulas (row 2; copy down or let the Table fill them):
B2: =SUMIFS(tblOrders[Amount],tblOrders[Date],">="&$A2,tblOrders[Date],"<="&EOMONTH($A2,0))
E2: =ROUND(B2/C2,0)
F2: =D2/C2
G3: =B3/B2-1
Expected result (check your numbers):
| Month | Sales (₹) | Orders | AOV (₹) | Delivery rate | Growth |
|---|---|---|---|---|---|
| Sep | 5,12,000 | 1,820 | 281 | 94.1% | — |
| Oct | 5,98,000 | 2,050 | 292 | 95.2% | 16.8% |
| Nov | 6,71,000 | 2,240 | 300 | 96.0% | 12.2% |
November cards: Sales ₹6,71,000, Orders 2,240, AOV ₹300, Delivery rate 96.0%.
Checks before you send it:
- Column A holds real month-start dates (shown as mmm), so SUMIFS can compare them.
- Growth is blank for the first month, not #DIV/0! or 100%.
- Every number on the MIS page links to the Monthly sheet.
- The page prints on one A4 sheet (Page Layout › Fit to 1 page).
Ravindra Bagale's Tip
Directors read the first three lines and the cards. Put the action in line three, not on page two.
Ravindra Bagale's Tip – मराठी
Directors पहिल्या तीन lines आणि cards च वाचतात. Action तिसऱ्या line मध्ये टाका, दुसऱ्या page वर नाही.
Ravindra Bagale's Tip – हिंदी
Directors पहली तीन lines और cards ही पढ़ते हैं. Action तीसरी line में डालें, दूसरे page पर नहीं.
Loading practice tasks...
- Total seats (Easy): Add up Seats in D2:D11.
- Average price per seat (Easy): Average of E2:E11, rounded to 2 decimals.
- Rows in Basic (Easy): How many rows have Plan "Basic"?
- Largest seats (Easy): The largest value in D2:D11.
- Seats for Basic (Medium): Total of D2:D11 where B is "Basic".
- Basic in Austin (Medium): Total of D2:D11 where B is "Basic" and C is "Austin".
- Best in Basic (Medium): Largest E2:E11 value where B is "Basic".
- Value of row 2 (Medium): D2 times E2.
- Look up Sneha Patil (Medium): The Seats for "Sneha Patil" (use a lookup on column A).
- Total value (Hard): Sum of D times E for all rows.
- Share of Basic (Hard): Share of D2:D11 that is "Basic", rounded to 3 decimals.
- Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
- Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".