The Makonsel Company, a fully integrated company that both produces and sells goods at its retail outlets. After production, the goods are stored in company’s two warehouses until needed by the retail outlets. Trucks are used to transport the goods from the two plants to the warehouses, and then from the warehouses to the three retail outlets.
Using units of full truckloads, the following table shows each plant’s monthly output, its shipping cost per truckload sent to each warehouse, and the maximum amount that it can ship per month to each warehouse.
Unit Shipping Cost
For each retail outlet (RO), the next table shows its monthly demand, its shipping cost per truckload from each warehouse, and the maximum amount that can be shipped per month from each warehouse.
Unit Shipping Cost
The Management’s objective is to determine the shipping plan (number of truckloads shipped per month from each plant to each warehouse and from each warehouse to each retail outlet) that will minimise the total shipping cost. In order to achieve the objective, the following issues will be discussed : The distribution network of Makonsel Company, algebraic formulation for the network model, spreadsheet formulation for this problem by using the solver of excel and interpretation and recommendation of the result.

Get Help With Your Essay
If you need assistance with writing your essay, our professional essay writing service is here to help!
Essay Writing Service

The distribution network
A network model for the Makonsel Company problem as a minimum-cost flow problem
According to the data from the table above we put it into a distribution network. The supply nodes in this network are P1 (plant1) and P2 (plant2), the transshipment nodes are W1 (warehouse1) and W2 (warehouse2) and the demand nodes in this network are RO1, RO2 and RO3. And the shipping cost and the shipping capacity differ considerably among these shipping lanes. The cost per unit shipped and the maximum amount that it can ship per month (given in square brackets of the arc) through each lane is shown above corresponding arrow in the above Figure.
Algebraic Formulation
Solution:
Decision variables —
Makonsel must determine how much to ship per month from each plant to each warehouse and from each warehouse to each retail outlet.
Let Xij = Number of truckloads to ship from i to j (i = P1, P2; j = W1, W2).
Let Xjk =Number of truckloads to ship from j to k (j =W1, W2 k=RO1, RO2, RO3).
Then Makonsel’s problem may be formulated as
Objective:
Subject to:
The first five constraints ensure that each retail outlet is meet their monthly demand, and the 2 Sources constraints are ensure that each plant’s monthly output and the last 10 ensure the maximum amount that can be shipped per month.
Spreadsheet Formulation
After we finished the algebraic formulation, we can transform them to spreadsheet, and using the solver of Excel to work out the distribution problem. The spreadsheet formulations are all showed in the graph below.
A spreadsheet model for the Makonsel Company minimum-cost flow problem, where the changing cells (C4:C13) show the optimal solution obtained by the Solver and the target cell (E15) gives the resulting total cost of the flow through the network.
Interpretation and Recommendation
The optimal solution for the Makonsel Company problem,
where the shipping amounts are shown in parentheses over the arrows
By using excel we can calculate the minimum total shipping cost of Makonsel Company is ¿¡488.125. In order to make the minimum total monthly shipping cost of ¿¡488.125, the Makonsel Company should first transport 125 truckloads per month from plant 1to warehouse 1 and 75 units to warehouse 2. And ship 175 truckloads per month from plant 2 to warehouse 1, ship 125 truckloads per month to warehouse2. After that the retail outlet1, retail outlet 2 and retail outlet 3 should get 100 truckloads, 50 truckloads and 100 truckloads from warehouse 1 respectively. And should separately transport 50 truckloads, 150 truckloads and 50 truckloads from warehouse 2 to retail outlet1, retail outlet 2 and retail outlet 3. As we have known the shipping cost per truckload from each plant and each warehouse from the table.
Thus the Minimum Cost= 425*125+560*75+510*125+600*175+470*100+505*50
+490*100+390*50+410*150+440*50
=488,125
Conclusion
Determined the shipping plan which can minimise the total shipping cost is the management objective of Makonsel Company. By building the distribution network , formulating the constraints and calculating the result through using the solver of excel , Makonsel Company successfully solve the distribution network problem and construct the shipping plan with the minimum total shipping cost of ¿¡488.125.
Forecasting
Introduction
The time-series below relates to the Sales of a company (00’s) for the last five years.
The objective is to use the information contained in the time-series data above to construct a forecast of the next four quarter’s sales. In order to achieve the objective, the following issues will be discussed: Analysis this time-series, “Detrend” a Time-Series and construct the Seasonal Indices by MINITAB, Forecasting the next four quarter’s sales and use measures to identify the forecast accuracy, Reservations about the appropriateness of the forecasting procedure used.
Time-series Analysis
Main characteristics of this time-series
The first step in any forecasting exercise is to plot a graph of the time-series. We transfer the data from the table to Minitab and use the time series plot-simple of Minitab to make the graph, since the time-series was recorded in quarter, so we choose the quarter of calendar in time scale.
The plot of this time-series looks like:
Form the graph above We can roughly find out that there is a decreasing trend over time, a clear quarterly seasonal effect and it is a table time series, the pattern is regular with little random noise. With the purpose of confirming the characteristics of the time-series, we use the cantered moving averages (CMA).
Since the CMA is the average and smoothed data of the actual figures, which is much easier for us to determine the characteristics of the time-series, we use this plot instead. As the time-series was recorded in quarters and with quarterly seasonal effect so the length of moving average is 4, and chose the moving averages, plot the graph smoothed vs. actual.
Negative Trend Structure, an decreasing trend over time
It is a negative trend structure. Look at the smoothed line of this time-series, as at the beginning of the time-series the sales of this company is about 485 ,however , it keeps decreasing and from about 485 down to around 478 to roughly 471 and finally it decrease to around 405.
A clear seasonal structure , additive seasonal structure
It can be seen from the graph above that there is a clear quarterly seasonal structure, for each quarter 1 the actual observed value is about 13 units below the trend value. For quarters 2, 3 & 4 estimating from the graph the actual observed values are 30 above, 22 above and 23 below the estimated trend values.
Seasonal Structures:
Q1 Q2 Q3 Q4
-13 30 22 -23
These are estimates of the seasonal indices; and also in this case, for a given variable the quarter 1 is 13 units below trend, quarter 2, 3 are 30 units and 22 units above trend, quarter 4 is 23 units below trend. And it can be seen from the graph above, seasonal deviation is constant about the trend so this seasonal structure is additive.
A table time series, the pattern is regular with little random noise
The graph of Moving average plot for sales above shows us that the pattern is regular with little random noise, it decreasing stability of the seasonal pattern, and also from the smoothed line we can find that the series reduce stability, from about 485 down to around 478 to roughly 471, ect. No more than 10units lower.
Model the time-series
QUADRATIC TREND MODELS
There are two trend models ,one is linear trend model (Trend = a + b*t ) and the other is Quadratic trend model (Trend = a + b*t + ct2 ), and as we have been calculated the Cantered Moving Average (CMA)above, which is the average and smoothed line of the actual sales, so by using the CMA, we can use value of these two models to compare with the value of CMA, and then choose the model which the value is much closer to the CMA as our forecasting model.
There are three commonly used measures of forecast accuracy: Mean Square Deviation (MSD), Mean Absolute Deviation (MAD) and Mean Absolute Percentage Error (MAPE). And the smaller the data is, the more accurate of the forecast. And it can be seen from the graph above that the Quadratic Trend Model, MAPE=0.43297, MAD=1.91172, MSD = 5.44313, and to the Linear Trend Model, MAPE=0.51281, MAD=2.21232, MSD = 7.74838. The data of the Quadratic Trend Model are all smaller than the Linear Trend Model, which means that the value of quadratic trend model is much closer than value of the CMA; the quadratic trend model is much more accurate than the linear trend model, so choose the quadratic trend model to forecast.
Detrend a Time-Series and construct SI of MINITAB
Detrend a Time-Series
“Detrend” a Time-Series, which means Sales-Trend (DIV), the gap between the actual sales and the forecast sales. After we have decided to take the quadratic model to forecast, we can record the data as the trend data, and the plot the graph above to compare with the sales and trend. And use the actual sales data minus the forecast one we can “Detrend” a Time-Series. As the graph shows us above the DIV1=472-500.367=-28.3673, DIV2=516-493.333=22.6674, DIV3=507-486.459=20.5414, DIV4=462-479.745=-17.7454, etc.
By using the Minitab, we can use the calculator to figure the result.
Construct the Seasonal Indices by MINITAB
As Seasonal Indices is the quarter average of DIV, after we have calculated the DIV, we can use MINITAB to construct the seasonal indices.
And in the MINITAB, we use the decomposition to figure out the SI.
As we have described before that the sales trend of this company is additive and seasonal and the data were recorded in quarter, so the seasonal indices is four quarters as a unit, the seasonal length is 4 and the model type is additive. And Seasonal Indices is the average of each quarter of DIV, so the seasonal indices can be calculated as below:
Quarter1=SI1= (DIV1+DIV5+DIV9+DIV13+DIV17)/5= -24.0937
Quarter2=SI2= (DIV2+DIV6+DIV10+DIV14+DIV18)/5=20.4062
Quarter3=SI3= (DIV3+DIV7+DIV11+DIV15+DIV19)/5=17.2812
Quarter4=SI4= (DIV4+DIV8+DIV12+DIV16+DIV20)/5=-13.5937
Since seasonal indices is the average of each quarter of DIV so SI is quarterly cycle, the value of SI5 will equal to the value of SI1, SI6=SI2, etc. And also it can be seen from graph above that the SI is quarterly cycle.
Forecasting and measures of forecast accuracy
Future Forecast
As the Future Forecast equal Future Trend plus Future Seasonal Indices, so first we should use the CMA to calculate the future trend of the next four quarters. Since the CMA is the average and the smoothed data of the actual data, using the data of CMA can let forecast more accuracy. And the time-series is seasonal structure of quarter, so the number of forecast is 4. And we use trend analysis to calculate the future trend.
After we figure out the future trend, copy the first four Seasonal Indices (SI is quarterly cycle) which we have calculated before (-24.0937, 20.4062, 17.2812, -13.5937), as Future Seasonal Indices.
And then use the FTrend and FSI to figure out the Future Forecast value (FFC=FTrend+FSI).
After figure out the FFC, copy them after the FC to plot a forecast.
The plot of time series of sales and forecast looks like:
So the next four quarters; Q1, Q2, Q3 & Q4 of 2009 are:
Q1=366.116, Q2=406.796, Q3=400.011, Q4=365.637
Measures of forecast accuracy
After we calculate the forecasts for the next four quarters, we need to know whether the forecast is accurate or not, so we use the three commonly used measures of forecast accuracy: MSD, MPE and MAD to check the forecasts.
i. Mean Square Deviation: MSD = S (Xt – Ft)2/n
ii. Mean Absolute Deviation: MAD = S |Xt – Ft| /n
iii. Mean Percentage Error: MPE = S |(Xt – Ft)/Xt| /n
Since all of measures above need the value of Xt – Ft (error), so we should calculate the error first. Error = Sales-FC, in the Minitab we use calculator to figure it out.
After calculated the error, we can figure out the value of accuracy.
MSD = S (Xt – Ft)2/n
MAD = S |Xt – Ft| /n
MPE = S |(Xt – Ft)/Xt| /n
And for this forecast the MSD=29.3526, MAD=4.69560, MPE=1.08963. As we all know for each forecast indicator, the lower value, the higher prediction accuracy. And usually we use the MPE to confirm the accuracy. Let’s look at the MPE, the value of MPE is equal to 1.08963%, though the value of MPE is slightly higher than 1%, it close to 1%, the forecast is still accuracy.
Reservation
In this forecasting procedure we faced two choices, one is determined the seasonal structure of the time-series, determining whether the seasonal structure is additive or multiplicative. And the other one is to confirm the trend model, choosing the linear model or the quadratic model. The choice we make will affect the accuracy of forecasting.
Additive or Multiplicative
In this forecasting, we analysis the time-series as additive seasonal structure by using method below¼š
Seasonal Structures:
Q1 Q2 Q3 Q4
-13 30 22 -23
In this case¼Œfor a given variable the quarter 1 is 13 units below trend, quarter 2 is 30 units above trend, etc. This is an Additive Seasonal Index.
Alternatively we could have expressed the index as follows:
Q1 Q2 Q3 Q4
-13% 30% 22% -23%
Here the quarter 1 data is 13% below the trend value, or more conventionally 87% of trend, similarly for the other quarters. It is conventional to express this Seasonal Index as:
Q1 Q2 Q3 Q4
87% 130% 122% 77%
This is called a Multiplicative Seasonal Index and if the seasonal deviation is proportional to the trend then the seasonal structure is multiplicative.
In this case we preferred the additive seasonal structure as the time-series constant about the trend, but in fact it could proportional to the trend and become the multiplicative seasonal structure in the future, so we should make appropriate adjustments base on the future data.
Linear or Quadratic model
In this case, we modeled the time-series as quadratic model due to the data the company provided closer to the quadratic model now, however, with the future data the model may be transformed into the linear model.
Conclusion
The objective of the company is to use time-series data to construct a forecast of the next four quarter’s sales. So as to do the forecast first we analysed the time-series to determine main characteristics of this time-series and modeled it, then found out the difference between the sales and trend to construct the seasonal indices, after that did the forecasting and to identify whether the forecast accurate or not by using the MAD, MSD and MPE. And the next four quarter’s sales of this company are Q1=366.116, Q2=406.796, Q3=400.011, Q4=365.637. However, during the forecasting procedure we should also consider about the choice we have made whether to choose additive or multiplicative, the linear model or the quadratic model will affect the accuracy of forecasting.