Building Sales Predictive Models for Ootdbuy Purchasing Agency in Spreadsheets and Its Application in Inventory Management
2025-04-24
Introduction
In today's competitive e-commerce landscape, data-driven inventory management has become essential for purchasing agencies like Ootdbuy. This article demonstrates how to construct sales forecasting models using time series analysis and regression in spreadsheets, integrate market factors, and apply these predictions to optimize inventory control - ultimately reducing costs and improving capital efficiency.
I. Dataset Structure for Historical Sales Analysis
Key components of the dataset:
1.1 Historical Sales Records
- Daily/monthly sales volume by product SKU
- Transactional timestamps for seasonality analysis
- Product categories and specifications
1.2 Market Influence Matrix
- Trending coefficients (social media mentions, search popularity)
- Economic indicators (exchange rates, purchasing power)
- Competitor pricing data scraped from marketplaces
1.3 Spreadsheet Implementation Example
Shows COUNTIFS for event-triggered sales impact and normalized Matrix(MMULT) computation for weighted variables:
{=MMULT(array_of_market_factors,normalized_weights_vector)}
II. Forecasting Model Construction
2.1 Time Series Decomposition
Using spreadsheet functions like =TREND() or array formulas to separate:
- Seasonality (Monthly/YEARFRAC periodicity)
- Trend line (Exponential smoothing via =FORECAST.ETS)
- Residual random noise (Error terms)
2.2 Explanatory Regression Model
Step-by-step modeling approach:
- Build baseline with =LINEST(sales_vol~datetime,[have_intercept])
- Add predicting factors with increasing complexity:
=LINEST(y, CHOOSE({1,2..n}, TIMESTAMP, HOLIDAY_DUMMY, CELEB_INDEX)) - Determine weights using solver for minimization of squared errors
III. Statistical Validation and Testing
Test Cases Table
| Methodology | Spreadsheet | MAPE |
|---|---|---|
| Naive Seasonal | =AVERAGEIF(same_period_hist) | 16% |
| Multivariate ARIMA | Custom using matrix inversion and based on ACF/PACF | 9% (best here) |