Help provide correct answer using Excel Solver for the following:

Homework 3

Q1 Last-mile delivery (25%). There is a warehouse located in "Ams". One vehicle will be dispatched to deliver products to 11 customers in Ath, Ber, Brus, Cope, Dub, Lis, Lon, Lux, Mad, Par, and Rom. Use the iterative method to find the shortest routing sequence. The first step is already given below and in excel file. Please finish the remaining subtour elimination iterations.

Table 1. Distance matrix (di,j) ci,j Ams Ath Ber Brus Cope Dub Lis Lon Lux Mad Par Rom Ams 10000 50 949 901 934 906 911 856 914 904 981 1013 Ath 50 10000 934 886 917 856 861 806 864 855 931 963 Ber 949 934 10000 50 50 1000 968 971 1022 946 1096 1019 Brus 901 886 50 10000 45 960 930 930 982 908 1058 983 Cope 934 917 50 45 10000 952 920 924 974 898 1048 969 Dub 906 856 1000 960 952 10000 40 50 22 61 100 117 Lis 911 861 968 930 920 40 10000 64 61 22 128 102 Lon 856 806 971 930 924 50 64 10000 63 72 143 162 Lux 914 864 1022 982 974 22 61 63 10000 82 81 120 Mad 904 855 946 908 898 61 22 72 82 10000 150 110 Par 981 931 1096 1058 1048 100 128 143 81 150 10000 122 Rom 1013 963 1019 983 969 117 102 162 120 110 122 10000

Step 1. We use the excel solver to solve the initial model below and get the results in the table below

Objective function: Constraints: , for all j N , for all i N Table 2. Model results xi,j Ams Ath Ber Brus Cope Dub Lis Lon Lux Mad Par Rom Ams 0 1 0 0 0 0 0 0 0 0 0 0 Ath 1 0 0 0 0 0 0 0 0 0 0 0 Ber 0 0 0 1 0 0 0 0 0 0 0 0 Brus 0 0 0 0 1 0 0 0 0 0 0 0 Cope 0 0 1 0 0 0 0 0 0 0 0 0 Dub 0 0 0 0 0 0 0 1 0 0 0 0 Lis 0 0 0 0 0 0 0 0 0 1 0 0 Lon 0 0 0 0 0 0 0 0 1 0 0 0 Lux 0 0 0 0 0 1 0 0 0 0 0 0 Mad 0 0 0 0 0 0 1 0 0 0 0 0 Par 0 0 0 0 0 0 0 0 0 0 0 1 Rom 0 0 0 0 0 0 0 0 0 0 1 0

Interpret the results and identify the subtours:

Ams-Ath-Ams Ber-Brus-Cope-Ber Dub-Lon-Lux-Dub Lis-Mad-Lis Par-Rom-Par

Step 2. Identify subtours and add subtour elimination constraints

Table 3. Identified subtours and the decision variable matrix in the subtour

xi,j Ams Ath Ber Brus Cope Dub Lis Lon Lux Mad Par Rom

Ams 0 1 0 0 0 0 0 0 0 0 0 0 Ath 1 0 0 0 0 0 0 0 0 0 0 0 Ber 0 0 0 1 0 0 0 0 0 0 0 0 Brus 0 0 0 0 1 0 0 0 0 0 0 0 Cope 0 0 1 0 0 0 0 0 0 0 0 0 Dub 0 0 0 0 0 0 0 1 0 0 0 0 Lis 0 0 0 0 0 0 0 0 0 1 0 0 Lon 0 0 0 0 0 0 0 0 1 0 0 0 Lux 0 0 0 0 0 1 0 0 0 0 0 0 Mad 0 0 0 0 0 0 1 0 0 0 0 0 Par 0 0 0 0 0 0 0 0 0 0 0 1 Rom 0 0 0 0 0 0 0 0 0 0 1 0

Then add subtour elimination constraints.

Table 4. Subtour elimination constraints

Subtours Number of all arcs in the subtour Limit: number of nodes - 1

Ams-Ath-Ams Summation of yellow area <= 1 Ber-Brus-Cope-Ber Summation of orange area <= 2 Dub-Lon-Lux-Dub Summation of dark blue area <= 2 Lis-Mad-Lis Summation of light blue area <= 1 Par-Rom-Par Summation of red area <= 1

Please finish the remaining steps until no subtour exists.

Q2 Compare Travel Times in Two Routes (30%). There are two routes connecting location A and location B. 10 vehicles (all vehicles are the same type) are dispatched via Route 1 and another 10 vehicles are dispatched via Route 2.

The travel times are presented in the table below.

Sample number (vehicle) Route 1 travel time sample Route 2 travel time sample

1 26 17 2 28 19 3 20 17 4 19 18 5 25 17 6 21 16 7 26 19 8 23 19 9 22 18 10 24 20

a. There is a hypothesis that the mean travel time in Route 1 is shorter than Route 2. Use the t test to demonstrate whether you believe this hypothesis given the confidence level of 95%. (50%)

b. There is a hypothesis that the travel time variance in Route 1 is smaller than that in Route 2. Use the F test to demonstrate whether you believe this hypothesis given the confidence level of 95%. Either do it manually in excel or use the data analysis tool for analysis. (50%)

Q3 Forecast Sales (45%) A car company is developing models to forecast sales. The historical car sale data is presented in the Excel file. Please answer the following question

a. Develop an autoregressive model, AR(2), for prediction, and plot the predicted results vs the observed results. (30%)

b. Develop a moving average model MA(2) for prediction and plot the predicted results vs the observed results. (30%)

c. Develop the ARIMA(2, 1, 2) model for prediction and plot the predicted results vs the observed results. (40%)