Suvarna Distributors: Cleaning and Dashboarding Sales Data
Suvarna Distributors is a mid-sized FMCG distributor in Karnataka and Maharashtra. Its analyst has three years of secondary-sales data from four sources: the distributor ERP (CSV), the field-force mobile app (Excel exports), retailer claim forms (scanned PDFs), and a WhatsApp group where sales officers report stock-outs. The combined file has 1.8 lakh rows. The 'Region' column contains 'North', 'north ', 'N', 'NORTH-1' and 'North Zone'; 'Date' appears as dd-mm-yyyy text, Excel serial numbers and a few blanks; 'Sales' includes commas, blanks and negative values for returns; and invoice numbers show duplicates because credit notes were exported twice. The CEO wants a one-page monthly dashboard showing region-wise sales, stock-out frequency and average claim-settlement days. The analyst has two days, a laptop with Python and Excel, and no database. The CEO asks whether Python or a spreadsheet is the better tool and what should be shown on the page.
1. Prepare a cleaning plan for Region, Date, Sales and duplicate invoices. For each, state the transformation and the validation check you would run.
Read the worked answer
Region (nominal): trim whitespace, uppercase, and map variants via a lookup table ('N', 'NORTH-1', 'North Zone' → 'North'). Validate with a frequency table before and after; send unmapped values to an exception list. Date (interval): parse dd-mm-yyyy text, convert Excel serials using origin 1899-12-30, and flag blanks rather than imputing; derive month and year. Validate by checking min/max dates and monthly counts against the ERP. Sales (ratio): remove thousands separators, convert to numeric, treat blanks as missing and investigate; keep negative values as returns and report gross and net sales separately. Validate totals against the ERP control total and count negatives. Duplicates: define a key of invoice number + date + amount; sort and flag duplicates, but first identify credit notes so genuine reversals are not deleted. Keep retained/removed counts and reconcile row counts. Store raw data read-only and log every step.
2. Recommend the descriptive statistics and charts for the one-page dashboard. Justify each choice and state two things to avoid.
Read the worked answer
Use total net sales, month-on-month growth and region share for sales; count and percentage of stock-outs by SKU/region; median and 90th percentile claim-settlement days because the distribution is likely skewed by a few slow claims. Charts: a sorted horizontal bar for region sales (easy comparison), a line chart for monthly trend (time series), a heatmap for stock-out frequency by region and month (pattern detection), and a box plot or histogram for settlement days (spread and outliers). Put the four key numbers at the top. Avoid a pie chart with many regions-it makes small differences hard to read-and avoid 3D or dual-axis charts that distort scale. If the CEO needs one page, use small multiples rather than cluttering one chart with every region.
3. Advise whether to use Python or a spreadsheet, considering the two-day deadline, repeatability and audit trail.
Read the worked answer
With 1.8 lakh rows and monthly repetition, Python (pandas) is the better core tool: it handles cleaning reproducibly, can be rerun next month, and leaves the script as an audit trail. Use Excel only as an output layer or for a quick pivot validation. The two-day deadline changes the delivery plan: first build a minimal Python script that cleans the four fields, outputs a tidy CSV and the dashboard numbers; validate against ERP control totals; then produce charts with matplotlib/openpyxl. If the CEO needs interactive filters, export to Excel rather than build an app. A spreadsheet-only approach is faster for a one-off but fragile: manual find-and-replace on 1.8 lakh rows is hard to audit and will break next month. Whichever is used, keep the raw file read-only, version the script, and document the mapping table and duplicate rule.