Store sales evaluation and prediction using spatial panel data models of sales components

Figures & data

Figure 1. Decomposition framework for store sales.

Figure 2. Existing (dots) and new (*) store locations of the retail chain.

Note: Dot sizes reflect store sizes.

Table 1. Descriptive statistics of the store-level variables.

Dependent variable	Symbol	Mean	SD	Data source
% Non-members	$S{N}_{it}/S_{it}$	0.272	0.088	Sales register
% Outside trade area	$SL{O}_{it}/S{L}_{it}$	0.154	0.061	Sales register
Trade area perimeter	$TA{P}_{it}$	15.86	5.543	Public
Number of competitors	$N{C}_{it}$	42	12.642	Survey
Total sales	$S_{it}$	1.48 × 10^6	5.10 × 10^5	Sales register
Explanatory variables of dependent variables measured at the store level
Size (m^2)		713	110.626	Survey
Years since first established store		22.55	9.985	Survey
Proportion of year store is open		0.992	0.068	Survey
Population size (in 100,000)		0.801	0.402	Census
Nearest distance to other store (miles)		19.55	8.846	Public
Time trend		2.529	1.114	Authors’ own construction
% female assortment		0.439	0.050	Survey
% children’s assortment		0.202	0.049	Survey
% households with children		0.434	0.036	Census
% couples without children		0.388	0.022	Census
% households with high SES		0.213	0.014	Census
% households with above-average SES		0.078	0.024	Census
% households with average SES		0.316	0.029	Census
% households with low SES		0.427	0.039	Census
% of double-income families		0.139	0.021	Census
Average number of low educated		0.469	0.187	Census
> average number of low educated		0.338	0.100	Census
Average number of middle educated		0.100	0.063	Census
> average number of middle educated		0.775	0.078	Census
Average number of high educated		0.221	0.122	Census
> average number of high educated		0.307	0.080	Census
Average number of low educated		0.213	0.014	Census
> average number of low educated		0.078	0.024	Census

Table 2. Descriptive statistics of zip code-level variables.

Dependent variable	Symbol	Mean	SD				Data source
Loyalty card penetration rate	$P{R}_{jt}$	0.097	0.128				Sales register
Visits	$N{V}_{ijt}$	1.401	0.439				Sales register
Expenditures	$EX{P}_{ijt}$	67.326	29.897				Sales register

	$P{R}_{jt}$		$N{V}_{ijt}$		$EX{P}_{ijt}$
Explanatory variables of dependent variables measured at zip code level	Mean	SD	Mean	SD	Mean	SD
Size	771.210	285.534	746.255	107.493	745.296	109.239	Survey
% female assortment	0.421	0.045	0.428	0.043	0.430	0.044	Survey
% children’s assortment	0.202	0.043	0.199	0.044	0.199	0.045	Survey
Proportion of year store is open	0.993	0.064	0.996	0.050	0.995	0.053	Survey
Years since first established store	54.725	10.080	20.974	8.954	21.129	9.184	Survey
Number of competitors	39.770	11.121	44.322	13.132	45.007	13.057	Survey
Distance to the store	21.929	16.427	11.852	6.145	11.227	5.943	Public
Distance to next-nearest store	31.635	15.489	25.722	18.872	26.210	19.377	Public
% households with children	0.428	0.123	0.429	0.121	0.429	0.114	Census
% couples without children	0.382	0.094	0.389	0.087	0.389	0.079	Census
% households with high SES	0.080	0.126	0.083	0.126	0.079	0.111	Census
% households with above-average SES	0.315	0.203	0.321	0.190	0.320	0.177	Census
% households with average SES	0.417	0.211	0.421	0.198	0.424	0.184	Census
% households with low SES	0.138	0.135	0.135	0.129	0.137	0.121	Census
% of double-income families	0.213	0.057	0.213	0.053	0.213	0.049	Census
Average number of low educated	0.324	0.468	0.335	0.472	0.339	0.473	Census
> average number of low educated	0.469	0.499	0.461	0.499	0.474	0.499	Census
Average number of middle educated	0.744	0.436	0.758	0.428	0.782	0.413	Census
> average number of middle educated	0.104	0.305	0.097	0.296	0.095	0.294	Census
Average number of high educated	0.306	0.461	0.312	0.464	0.317	0.465	Census
> average number of high educated	0.241	0.427	0.245	0.430	0.217	0.412	Census
Time trend	2.500	1.118	2.538	1.114	2.527	1.114	Authors’ own construction

Table 3. Overview of dependent variables and type of model to explain and predict them.

Dependent variable	Description	Transformation	Econometric model^a	Predictor
S	Total sales	Log	Store-RE	Equation (6) + detransf.
TAP	Trade area perimeter	Log	Store-RE	Equation (6) + detransf.
SN/S	% non-member sales	Logit	Store-RE	Equation (6)
SLO/SL	% outside trade area sales	Logit	Store-RE	Equation (6)
NC	Number of competitors	Log	Store-RE	Equation (6) + detransf.
PR	Penetration rate	Logit	Zip-RE-SA	Equation (8)
NV	Number of visits	Log	Zip-Unbalanced-RE-SA	Equation (10) + detransf.
EXP	Expenditures	Log	Zip-Unbalanced-RE-SA	Equation (10) + detransf.

Note: ^aStore = explained at the store level; Zip = explained at the zip code level; RE = random effects; SA = spatial autocorrelation; Unbalanced = estimation and prediction based on unbalanced spatial panel data; Detransf. = detransformation correction from log to level based on Miller (1984).

Table 4. Parameter estimates of sales components explained at the store level.

Explanatory variable	Dependent variable
	Log trade area perimeter		Logit % non-member sales		Logit % outside trade area sales		Log number of competitors		Log total sales benchmark model
	Coefficient	t-value	Coefficient	t-value	Coefficient	t-value	Coefficient	t-value	Coefficient	t-value
Constant	2.485*	1.73	−6.824*	−1.84	−2.563	−0.80	0.659	0.54	13.633***	24.62
Time trend	0.015***	2.62	0.004	0.07	0.017	0.47	0.032**	1.95	−0.080***	−4.58
Store characteristics
Size (in 10,000 m^2)	13.605*	1.91	−2.728	−0.31	−4.566	−0.33			0.647***	3.24
% female assortment	−1.748	−0.92	−2.517	−1.16	0.517	0.14			−2.494***	−4.14
% children’s assortment	−1.377	−0.68	−4.485*	−1.77	0.746	0.19			−1.988***	−3.02
Proportion of year store is open	0.012	0.13	−0.682***	−2.87	−0.343***	−3.37			1.645***	5.47
Years since first established store (/100)	0.010	0.09	0.332	0.40	0.415	0.31			−0.774***	−3.33
Nearest distance to other store (miles)	0.001	0.36	0.011*	1.71	−0.012***	−3.24	−0.002	−0.95	−0.002	−0.63
Competitor characteristics
Number of competitors (/100)	1.053*	1.74	2.455***	3.39	−0.503	−0.45			10.975***	4.87
Consumer characteristics
Population size (/100,000)	−0.255	−1.31	−0.130	−0.51	0.412	1.32	0.207***	8.06	−0.358***	−5.07
% households with children			7.536**	2.14	−0.779	−0.38	−1.588**	−2.10
% couples without children			7.637*	1.94	−3.805*	−1.71	0.039	0.04
% households with high SES			4.235	0.54	−5.023	−1.41	−2.832*	−1.85
% households with above-average SES			−0.934	−0.30	2.731*	1.79	1.025	0.83
% households with average SES			−2.528	−0.88	4.298***	3.15	1.556	1.18
% households with low SES			6.197**	2.26	2.816**	2.12	2.171*	1.91
% of double-income families			0.215	0.06	0.722	0.42	−0.620	−0.42
Average number of low educated			0.637	0.76	1.112**	2.28	0.200	0.66
> average number of low educated			1.694*	1.73	0.415	0.87	0.022	0.06
Average number of middle educated			−3.506***	−4.38	−0.265	−0.70	0.385	1.06
> average number of middle educated			−2.811***	−3.51	0.536	1.42	0.565**	2.17
Average number of high educated			−0.362	−0.62	−0.001	−0.01	0.366	1.40
> average number of high educated			0.844	1.43	−0.015	−0.05	−0.169	−0.94
Other characteristics
Random effect	0.097***	10.05	0.197***	9.90	0.045***	10.09	0.438***	9.08	0.997	0.827
R^2 equation	0.162		0.475		0.367		0.574		0.660
R^2 prediction (Verbeek, 2000)	0.977		0.935		0.990		0.840		0.664
Number of observations	102		102		102		102		102

Notes: SES, socioeconomic status; *, **, ***significance at the 10%, 5% and 1% levels, respectively.

Table 5. Parameter estimates of sales components explained at the zip code level.

Explanatory variable	Dependent variable
	Logit loyalty card penetration		Log visits		Log expenditures
	Coefficient	t-value	Coefficient	t-value	Coefficient	t-value
Constant	−6.076***	−15.53	−0.709**	−1.99	4.979***	13.02
Time trend	0.105***	23.81	−0.012***	−3.66	−0.003	−0.75
Store characteristics
Size (in 10,000 m^2)	1.489	1.54	3.259*	1.87	−0.832	−0.46
% female assortment	3.931***	5.94	0.605	1.34	−1.239***	−2.61
% children’s assortment	5.996***	9.33	1.479***	3.08	−1.436***	−2.87
Proportion of year store is open	0.887***	11.82	0.450***	5.40	−0.331***	−3.23
Years since first established store (/100)	−1.977***	−7.61	−0.177	−1.03	−0.295	−1.62
Competitor characteristics
Number of competitors (/100)	1.789***	9.51	−0.086	−0.67	0.042	0.31
Consumer characteristics
Distance to the store (in miles)	−0.102***	−58.81	−0.025***	−43.64	0.006***	5.94
Distance to next-nearest store (in miles)	0.002*	1.89	0.004***	11.92	0.001*	1.80
% households with children	0.491***	4.03	0.120***	6.65	0.070	1.17
% couples without children	0.319**	2.52	0.029	1.30	0.112	1.47
% households with high SES	0.395***	4.32	−0.015	−0.79	0.217***	3.31
% households with above-average SES	0.230***	3.12	−0.005	−0.32	0.116**	2.07
% households with average SES	0.111	1.60	−0.030*	−1.91	0.141**	2.49
% households with low SES	−0.121	−1.37	−0.020	−1.05	−0.011	−0.16
% of double-income families	1.167***	6.73	−0.083***	−2.60	0.128	1.16
Average number of low educated	0.060**	2.05	−0.008*	−1.77	0.053***	3.47
$>$ average number of low educated	0.123***	3.58	−0.011*	−1.83	0.069***	3.77
Average number of middle educated	0.082***	3.69	0.023***	5.02	0.081***	5.34
$>$ average number of middle educated	0.072**	2.28	0.039***	5.56	0.092***	4.01
Average number of high educated	−0.008	−0.40	−0.006*	−1.68	−0.004	−0.35
$>$ average number of high educated	−0.117***	−4.10	−0.024***	−4.33	−0.017	−1.00
Other characteristics
Random effect^a	3.904***	71.12	0.271***	3.16	0.032***	3.03
Spatial autocorrelation coefficient (λ)	0.164***	10.66	0.626****	71.19	0.099***	6.97
R^2 equation	0.525		0.520		0.051
R^2 prediction (Verbeek, 2000)	0.861		0.725		0.128
Number of observations	16,032		10,611		9726

Notes: SES, socioeconomic status; *, **, ***significance at the 10%, 5% and 1% levels, respectively.

^a The random effect parameter has a different interpretation than Table 4; see the section on the econometric model for an explanation.

Figure 3. (a) In-sample store sales forecasts using the proposed model, MAPE = 179,870; (b) in-sample store sales forecasts using the benchmark model, MAPE = 272,700; (c) one-year-ahead store sales forecasts using the proposed model, MAPE = 258,330; (d) one-year-ahead store sales forecasts using the benchmark model, MAPE = 272,130; (e) store sales forecasts of new stores using the proposed model, MAPE = 213,200; and (f) store sales forecasts of new stores using the proposed model, MAPE = 287,820.

Note: MAPE, mean absolute prediction error.

Miller, D. M. (1984). Reducing transformation bias in curve fitting. American Statistician, 38, 124–126. https://doi.org/https://doi.org/10.1080/00031305.1984.10483180

 Google Scholar

Verbeek, M. (2000). A guide to modern econometrics. Wiley.

 Google Scholar