Beverage Distribution Forecasting
A sales analytics internship at a beverage distributor in Mexico. The forecasting half became my undergraduate capstone. I built the database and the forecasting model behind more than 600,000 transactions, then turned the results into dashboards and decisions the business could act on.
From raw transactions to a forecast.
The company had years of sales sitting in spreadsheets and no reliable way to see what was coming. I built the database, the model, and the reporting layer that turned that history into a forward view.
Monthly revenue, January 2023 to December 2025, with a least-squares trend line and each year's tallest and shortest month marked. Annual revenue grew 8.6% in 2024 and 7.9% in 2025, and both the peaks and the troughs climb: 17.29m to 18.60m to 19.79m at the top, 10.76m to 11.54m to 12.72m at the bottom. The gap between them never narrows, which is the growth-without-stability problem in one picture.
Line chart of monthly revenue from January 2023 to December 2025 with a straight trend line. Annual revenue grew 8.6% in 2024 and 7.9% in 2025. Yearly peaks rise from 17.29 million to 18.60 million to 19.79 million and yearly troughs rise from 10.76 million to 11.54 million to 12.72 million, so the swing between the best and worst month of each year stays about as wide throughout.
A few products carry the business.
Revenue is heavily concentrated. The top three products alone account for nearly half of everything sold, which changes how you think about stock, pricing, and which shortages actually hurt.
Share of revenue by product
Top 6 of 202The top three products alone are 48.3% of revenue. Bar length is each product's share, scaled to the largest.
The customers behind the revenue
Top 10 of 11,124LD alone takes 13.18%, more than the next two together, and the ten together take 33.0%. That is 0.09% of the customer base carrying a third of the business.
Three years, 615,615 transactions.
Transaction volume held close to flat across the three years: 199,300 in 2023, a dip of 1.2% to 196,985 in 2024, then a rise of 11.3% to 219,330 in 2025. About 11% apart at the widest.
Route concentration
- 56% of all revenue comes from just 20 of the 74 delivery routes.
- The top single route alone carries 12%.
Growth over the period
- Revenue grew 17.2% from 2023 to 2025.
- A clear seasonal shape repeats each year, and that seasonality is exactly what makes the sales forecastable in the first place.
- Figures are shown as an index rather than in currency, since the underlying revenue is the client's.
Bulk buyers drive growth, not stability.
Plotting every large customer by how often they order against how much they take separates them into two groups that behave nothing alike. One group orders rarely and enormously. The other orders constantly and small. Only one of them can be planned around.
Below: the forty customers with the largest share of revenue, positioned by total transactions and total boxes sold across 2023 to 2025, with bubble area their share of revenue. LD sits alone at the top, 202,924 boxes across only 621 orders. The customers further right place four to eight times as many orders and take a fraction of the volume.
Order frequency against order size
Bubble chart of the forty largest customers by revenue. The horizontal axis is total transactions from 0 to about 900, the vertical axis is total boxes sold from 0 to about 203,000, and bubble area is share of revenue. LD is an outlier at 621 transactions and 202,924 boxes, 13.18% of revenue. Vinos y Licores Tony took 95,814 boxes across only 117 transactions. Most other customers cluster below 20,000 boxes, and the highest-frequency customers, at 600 to 900 transactions, sit near the bottom of the volume axis.
Bulk buyers
- Few transactions, very large order volumes.
- They move the monthly total on their own.
- Any forecast is hostage to whether they happen to order that month.
- High impact, hard to predict.
Frequent buyers
- Many transactions, smaller orders.
- Individually small, but the pattern repeats.
- The part of demand you can actually plan around.
- Lower impact, predictable.
This difference in buying behavior is the reason the monthly revenue line swings the way it does.
Growth without stability.
I came back to the same data later for a Power BI and storytelling assignment, and asked a harder question. The business was growing 7 to 9% a year, so on the surface it looked healthy. Underneath, almost all of that revenue rested on a handful of customers and a handful of products.
Growth is concentrated in a few products
Two 100% stacked bars comparing the same fifteen products on two measures. By product count they are 7.43% of the 202-product catalog against 92.57% for the other 187. By revenue they are 79.5% against 20.5%. The proportions are close to inverted.
The bulk-against-frequent split, and this one, are the whole answer to why revenue kept growing without ever becoming steadier.
Database, model, dashboards.
The analysis was only useful if the company could keep using it after I left, so each layer had to stand on its own.
Clean
Consolidated years of transaction, customer, and product records, and fixed the inconsistencies that made them impossible to join.
Database
Designed and built a MySQL database so the data had one reliable home instead of living across spreadsheets.
Model
Built a weekly forecast of average order size on more than 600,000 sales records. Across 14 weeks of actuals it came within about 25% on average, which is 75% on a mean absolute percentage error basis.
Dashboards
Six Tableau dashboards so the team could read demand by route, product, and period without asking an analyst.
Analysis that became decisions.
The part I am proudest of is that this did not stop at a report. The forecast and the route analysis fed directly into how the company planned its operations.
| What changed | How |
|---|---|
| Three cost-saving strategies | Identified from demand patterns and where the money actually concentrates across products and routes. |
| Route optimization | Delivery planning informed by which routes carry the revenue and how their demand moves through the year. |
| Self-serve reporting | The team could answer its own questions in Tableau instead of waiting on a manual export. |
The recommendation was not to chase more growth, it was to change where the growth comes from.
| Recommendation | Target |
|---|---|
| Diversify the customer base | Bring reliance on top accounts below 25% of revenue, by attracting and keeping more mid-size customers. |
| Grow the frequent buyers | Push smaller, more regular orders through discounts and deals, so more of the monthly total is the predictable kind. |
| Expand the product mix | Lift non-top products to at least 30% of revenue, using the 90% of the catalog that currently sits idle. |
My first real business analytics project.
This was the project that taught me the analysis is the easy half. Getting messy operational data into a shape people trust, and then handing back something they can use without me, is the part that actually decides whether the work matters. It became my undergraduate capstone and it is still the project I explain most often in interviews.