An Excel demand and purchasing model for a coffee shop chain
A demo model on training data: 12 items over 24 months. It removes seasonality, fits a trend, forecasts a year ahead, splits the range by ABC-XYZ and calculates safety stock and the recommended order. You can download the file and check the formulas.
- 2,515
- formulas
- 6
- sheets
- 4%
- forecast error in the check
The task
Show how a sales export becomes a working tool: a forecast, a range classification and a purchasing plan — on live formulas, with no manual recalculation.
What I did
- Forecast: trend on the deseasonalised series × monthly index — 4% average error in a simulation check instead of 21% for the naive version
- ABC (80/95%) and XYZ (10/25%) by formulas, sorted automatically
- Safety stock z·σ·√(LT/30) with a service level by class, the reorder point and an order rounded to the pack size
- Dashboard: an item picker, 6 KPIs, 3 charts, sparklines
Proof
- Every value matches an independent recalculation in Python to about 10⁻¹⁰
- 2,515 formulas, zero errors; the file opens in Excel 2019 and Microsoft 365
Materials
6 · Click any item to enlarge it
What else you can hand over to me
- A sales or purchasing planning model in Excel
- A dashboard in Excel or Yandex DataLens
- A report formatted to GOST 7.32
- An Access database: tables, queries, forms
- Fill a pack of contracts from photos of documents
An explanatory note to GOST 7.32: a document that does not fall apart
Styles, an automatic table of contents, numbered figures, tables and equations — 12 pages with no manual formattingContracts for a children’s school, filled from photos of forms and passports
The client sends photos — and gets filled-in contracts plus a list of discrepancies