

In [ ]:

    
# Import libraries
import gurobipy as grb
import pandas as pd
import matplotlib.pyplot as plt
import numpy.random as np
import sys
import matplotlib 
%matplotlib inline

GRB = grb.GRB

The increase revenue: $$\sum_i^{483} R_i*y_i=\sum_i^{483} R_i(a_1x_1^{(i)}+a_2x_2^{(i)}+C^{(i)})= \sum_i^{483} R_i(a_1x_1^{(i)}+a_2x_2^{(i)})+\sum_i^{483}R_iC^{(i)}=\sum_i^{483}R_i(a_1x_1^{(i)}+a_2x_2^{(i)})+C_0$$ Since the constant does not affect the optimization, we can simplify the integer linear equation to maximize: $$\sum_i^{483}R_i(a_1x_1^{(i)}+a_2x_2^{(i)})$$ where for each store i, $R_i$ is the originial revenue , $y_i$ is the projected growth rate predicted by variables, $C^{(i)}$ is the constant factor of the store.

The parameter $a_1,a_2$, trained from the dataset, is assumed as the same for all store.

Subject to:

The store originially open 14 hours, hire 24 associates. $$S=483*14*(10*24+250)$$ Zero expense change with each store had a utilities cost of $250$ per store per hour open, and an average cost of $10$ per employee per hour: $$S=\sum_i^{483}(10(x_1^{(i)}+24)+250)(14+x_2^{(i)})=\sum_i^{483}10*x_1^{(i)}*14+10*x_1^{(i)}*x_2^{(i)}+10*24*14+10*24*x_2^{(i)}+250*14+250*x_2^{(i)}$$ Simplify as: $$0=\sum_i^{483}140x_1^{(i)}+10x_1^{(i)}x_2^{(i)}+240x_2^{(i)}+250x_2^{(i)}$$
The number of associates change: $x_1^{(i)}\in \{0, +2, +4\}$
The number of openning hours change: $x_2^{(i)}\in \{0, -1, -2\}$

(lm(formula = y1 ~ x1 + x2 + x4 + x5 + x6 + x7 + x8 + x9 + AL + 
     IL + LA + MI + OH + PA + SC + SD + WA + WV + x1:x4 + x1:x8 + 
     x2:x4 + x2:x8 + x4:x6 + x4:x9 + x5:x7 + x6:x9, data = newstore))
To maximize:
x_1*(-0.481+x_4*2.256+x_8*2.578)+x_2*(-3.689-x_4*2.329-x_8*1.475)

Residuals:
    Min      1Q  Median      3Q     Max 
-573.75 -156.34    4.06  157.33  634.99 

Coefficients:
              Estimate Std. Error t value Pr(>|t|)    
(Intercept)  9.299e+02  1.816e+02   5.122 4.47e-07 ***
x1          -1.391e+01  2.892e+01  -0.481 0.630841    
x2          -1.796e+02  4.869e+01  -3.689 0.000253 ***
x4           4.260e+00  3.189e+00   1.336 0.182321    
x5           9.579e+00  3.250e+00   2.948 0.003367 ** 
x6          -5.233e-05  1.784e-04  -0.293 0.769391    
x7           3.915e+01  1.563e+01   2.504 0.012616 *  
x8           1.693e-01  1.078e+00   0.157 0.875353    
x9          -2.247e+02  1.564e+02  -1.437 0.151516    
AL           6.040e+02  2.504e+02   2.412 0.016253 *  
IL          -6.991e+01  4.662e+01  -1.500 0.134394    
LA           1.708e+02  7.982e+01   2.140 0.032864 *  
MI          -2.020e+02  8.449e+01  -2.391 0.017200 *  
OH           9.772e+01  5.338e+01   1.831 0.067778 .  
PA           8.477e+01  5.568e+01   1.522 0.128578    
SC          -1.803e+02  1.030e+02  -1.751 0.080614 .  
SD          -3.987e+02  2.454e+02  -1.625 0.104932    
WA          -1.214e+02  8.394e+01  -1.446 0.148732    
WV          -2.135e+02  1.460e+02  -1.462 0.144429    
x1:x4        1.352e+00  5.994e-01   2.256 0.024529 *  
x1:x8        2.648e+00  1.027e+00   2.578 0.010261 *  
x2:x4       -2.363e+00  1.015e+00  -2.329 0.020305 *  
x2:x8       -2.826e+00  1.916e+00  -1.475 0.140830    
x4:x6       -7.964e-06  4.359e-06  -1.827 0.068331 .  
x4:x9        1.885e+00  9.341e-01   2.018 0.044176 *  
x5:x7       -1.190e+00  4.444e-01  -2.678 0.007670 ** 
x6:x9        3.104e-04  2.083e-04   1.490 0.136828    
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 243.2 on 456 degrees of freedom
Multiple R-squared:  0.3765,    Adjusted R-squared:  0.3409 
F-statistic: 10.59 on 26 and 456 DF,  p-value: < 2.2e-16



In [ ]:

    
xl = pd.ExcelFile("Spec.xlsx")
xl.sheet_names
df = xl.parse(0)
# print(df.columns)
for title in df.columns:
    list = []
    for i in df[title]:
        list.append(i)



In [ ]:

    
m = grb.Model()
x1 = [None] * 483; x2 = [None] * 483

for i in range(483):
    obj_x1 = -0.481 + df['Competition within 5mi'][i]*2.256 + \
        (1 if df['Store Type'][i] == 'Standalone' else 0)*2.578
    
    obj_x2 = -3.689 + df['Competition within 5mi'][i]*(-2.329) + \
        (1 if df['Store Type'][i] == 'Standalone' else 0)*(-1.475)
    
    x1[i] = m.addVar(lb=0, ub=2, obj=-2*obj_x1, vtype=GRB.INTEGER)
    x2[i] = m.addVar(lb=0, ub=2, obj=-(-1)*obj_x2, vtype=GRB.INTEGER)

ad = m.addVar(lb=-5000000, ub=10000000, obj=-0.1/15, vtype=GRB.INTEGER)
m.update()



In [ ]:

    
expense = ad / 30
for i in range(483):
    expense += 140*2*x1[i] + 490*(-1)*x2[i]

m.addConstr(expense, '=', 0, name='expense')

m.update()



In [ ]:

    
m.optimize()
print "Model status =", m.Status
assert m.Status == GRB.OPTIMAL



In [ ]:

    
print(-m.objVal * 15)
print(ad.X)

for i in range(483):
    print(x1[i].X, x2[i].X)



In [ ]: