Secondary-Sales Driver Analysis & Excel Dashboard for a Mysuru FMCG Distributor (Python Regression)

This internship project studies what actually drives secondary sales (distributor-to-retailer orders) for Sri Chamundi Agencies, a fictional FMCG distributor in Mysuru that supplies about 1,150 kirana stores, medical shops and small supermarkets across 14 beats. The company's sales manager believes that more salesman visits and bigger trade schemes push volume; the accounts team believes that longer credit days do the real work. Nobody has tested either belief.

Using 18 months of invoice-level data (about 96,000 invoice lines, exported from the distributor's billing software to CSV), the project builds an outlet-month panel and fits a multiple linear regression of monthly outlet sales on beat-visit frequency, scheme depth, credit days allowed, outlet type, outlet age and festival-month dummies. A log-linear specification gives elasticities that a sales manager can read directly ("a 10% deeper scheme is associated with X% more sales").

The deliverables are a reproducible Python pipeline (cleaning → feature building → regression → diagnostics), an Excel dashboard regenerated by the pipeline (beat-wise sales, top outlets, scheme ROI, collection ageing) and a management report with actionable recommendations on beat planning and scheme design. The project fits the AICTE model MB 303 Internship Project & Viva (6 credits; report 50, presentation 30, viva 20) and is defensible because every number traces back to a script.

Abstract

Small FMCG distributors in tier-2 Indian cities spend heavily on salesman beats and trade schemes without evidence of what works. This project uses 18 months of invoice data from a Mysuru distributor to estimate, through multiple linear regression, the effect of visit frequency, scheme depth, credit days and outlet type on monthly outlet sales. A Python pipeline cleans the data, fits and diagnoses the model, and regenerates an Excel dashboard for the sales team. Results are converted into beat-planning and scheme-design recommendations.

Introduction

In the Indian FMCG route-to-market model, the company sells to a distributor (primary sales) and the distributor sells to retailers (secondary sales). Secondary sales are what the company's area manager tracks every month, and they depend on how well the distributor's salesmen cover their beats. Distributors typically run 10–20 beats, each visited on fixed weekdays, and offer schemes such as "buy 10 get 1" or a flat percentage off invoice. Credit of 7–30 days is common and is often the main reason a kirana store stays loyal. The distributor in this study had all this data in its billing software but only used it to print invoices and outstanding lists.

Literature gap

Published work on Indian retail distribution is largely survey-based (retailer satisfaction with distributors, perception of schemes). Very few student or practitioner studies use transaction data to quantify the association between distributor actions and outlet sales. Textbook sales-management chapters describe beat planning qualitatively. This project fills that gap at the scale of a single distributor, with an honest discussion of what a regression on observational data can and cannot claim.

Existing vs proposed practice

• Existing: monthly target vs achievement in Excel, prepared manually; schemes chosen by the principal company's push; beat frequency unchanged for two years; no view of which outlets are growing or dying.
• Proposed: an automated outlet-month dataset, a validated regression model with elasticities, a scheme-ROI view and an Excel dashboard that refreshes from one command.

Feasibility

• Technical: all tools are free (Python, pandas, statsmodels) or already licensed (Excel). The dataset is under 50 MB.
• Economic: no cost to the distributor beyond the time to export CSVs.
• Operational: the sales manager already uses Excel; the dashboard uses slicers and pivot charts he recognises.
• Ethical: outlet names are replaced with codes; no personal data of salesmen or shop owners leaves the premises; the company's written permission is attached to the report.

Sri Chamundi Agencies, a fictional FMCG distributor in Mysuru, serves roughly 1,150 retail outlets through 14 fixed beats and four salesmen. Over the last two years its secondary sales have grown slower than the principal company's regional average, while spending on trade schemes has risen by about a third and average credit days have stretched from 12 to 21. Management disagrees on the cause: the sales team asks for more visits and bigger schemes, while the accounts team wants to cut credit because collections are slow.

The firm has 18 months of invoice-level data but no analysis connecting its own actions to outlet sales. The problem is to quantify how visit frequency, scheme depth, credit days and outlet characteristics are associated with monthly outlet sales, test whether these associations are statistically significant, and present the results in a dashboard and a set of recommendations that the owner can act on in the next quarter — without overstating a correlational model as proof of causation.

In scope

• One distributor, 14 beats, about 1,150 outlets, 18 months (April of year 1 to September of year 2).
• Secondary sales value (₹, net of scheme discount) at outlet-month level; six SKU categories.
• Explanatory variables available in the billing and beat-plan data: visits per month, scheme discount %, credit days allowed, outlet type (kirana / medical / supermarket / paan-bakery), months since first invoice, festival-month dummy (Dasara, Deepavali, Ugadi, Sankranti).
• Python pipeline, Excel dashboard, management report and presentation.

Out of scope

• Primary sales and the principal company's pricing decisions.
• Causal claims — the design is observational; the report discusses confounding explicitly.
• Retailer surveys, competitor sales and weather data (listed as future scope).
• Real-time integration with the billing software; the pipeline reads monthly CSV exports.

Research design

Descriptive + explanatory (correlational) design on secondary data. The unit of analysis is the outlet-month. The study is a census of all active outlets rather than a sample: after removing outlets with fewer than three invoices, about 1,020 outlets × 18 months ≈ 18,000 outlet-months remain. With 10 predictors, this is far above the common rule of thumb of 50 + 8k = 130 observations (Green, 1991) for testing individual coefficients, so statistical power is not a concern — practical significance is.

Variables

Hypotheses (α = 0.05)

• H1₀: visit frequency has no association with outlet sales (β_Visits = 0). H1₁: β_Visits ≠ 0.
• H2₀: scheme depth has no association with sales. H2₁: β_SchemePct ≠ 0.
• H3₀: credit days have no association with sales. H3₁: β_CreditDays ≠ 0.
• H4₀: mean monthly sales do not differ by outlet type. H4₁: at least one type differs — tested with one-way ANOVA (Welch version if Levene's test fails), then Games–Howell post-hoc.
• Supporting tests: Pearson correlation matrix of continuous predictors; chi-square test of independence between outlet type and "growing vs declining" status (year-2 vs year-1 sales).

Model checks

VIF < 5 for multicollinearity; residual-vs-fitted plot and Breusch–Pagan test for heteroscedasticity (HC3 robust standard errors if violated); Q–Q plot of residuals; Cook's distance for influential outlets; a fixed-effects variant with beat dummies as a robustness check. Hold-out validation: train on months 1–15, test on 16–18 with RMSE and MAPE.

Timeline (8 weeks)

• Add a retailer survey (satisfaction with service, scheme awareness) and merge it with transaction data to explain why outlets respond differently.
• Replace OLS with a mixed-effects model (outlets nested in beats) or a gradient-boosting model with SHAP explanations for non-linear effects.
• Use difference-in-differences on a pilot beat where visit frequency is actually changed, turning the correlational finding into a causal test.
• Automate monthly refresh through the billing software's API and publish the dashboard in Power BI for the salesmen's phones.
• Extend to collection prediction: which outlets will cross 30 days overdue next month.