Network Opimisation Problems And Forecasting

Ne twork Opimisation Problems And ForecastingThe Makonsel keep company, a in near integrated company that both produces and sells goods at its sell place permits. later production, the goods argon stored in companys two warehoexercisings until needed by the retail outlets. Trucks are apply to transport the goods from the two places to the wareho intentions, and thus from the wareho substance ab social functions to the three retail outlets.Using units of full truckloads, the pursual table shows each(prenominal) coifs calendar monthly output, its merchant vessels make up per truckload direct to each warehouse, and the maximum summate that it tummy ship per month to each warehouse.Unit Shipping beFor each retail outlet (RO), the following(a) table shows its monthly demand, its fare cost per truckload from each warehouse, and the maximum amount that can be shipped per month from each warehouse.Unit Shipping CostThe Managements documentary is to determine the mer chant marine plan (number of truckloads shipped per month from each seed to each warehouse and from each warehouse to each retail outlet) that depart minimise the total transferral cost. In order to strike the objective, the following issues will be discussed The distribution interlocking of Makonsel ph cardinalr, algebraic formulation for the cyberspace clay sculpture, spreadsheet formulation for this problem by using the solver of outdo and edition and recommendation of the result.The distribution networkA network example for the Makonsel high society problem as a minimum-cost flow problemAccording to the information from the table in a higher place 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 be arrow in the above Figure.Algebraic induceulationSolution purpose multivariates Makonsel must determine how some(prenominal) to ship per month from each plant to each warehouse and from each warehouse to each retail outlet. permit Xij = return 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 Makonsels problem whitethorn be formulated asObjectiveSubject toThe commencement tailfin constraints ensure that each retail outlet is meet their monthly demand, and the 2 Sources constraints are ensure that each plants monthly output and the last 10 ensure the maximum amount that can be shipped per month.Spreadsheet Formulation subsequently we finished the algebraic formulation, we can transform them to spreadsheet, and using the solver of exceed to work out the distribution problem. The spreadsheet formulations are all showed in the graph below.A spreadsheet model for the Makonsel political party minimum-cost flow problem, where the changing cells (C4C13) show the best solution obtained by the Solver and the target cell (E15) gives the resulting total cost of the flow through the network.Interpretation and RecommendationThe optimal solution for the Makonsel Company problem,where the shipping amounts are shown in parentheses everyplace the arrowsBy using excel we can describe 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 star hundred seventy-five truckloads per month from plant 2 to warehouse 1, ship 125 truckloads per month to warehouse2. later on 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 distributively transport 50 truckloads, 150 truckloads and 50 truckloads from warehouse 2 to retail outlet1, retail outlet 2 and retail outlet 3. As we contract chicanen 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 destinationDetermined 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 groundworkThe time-series below relates to the Sales of a company (00s) for the last five years.The o bjective is to use the information contained in the time-series info above to construct a prognosis of the following(a) four accommodate gross sales. In order to achieve the objective, the following issues will be discussed depth psychology this time-series, De abridge a Time-Series and construct the seasonal worker worker worker Indices by MINITAB, Forecasting the next four passs sales and use measures to identify the view trueness, Reservations active the appropriateness of the foretasteing procedure used.Time-series AnalysisMain characteristics of this time-seriesThe first step in any presageing habit is to game a graph of the time-series. We transfer the entropy from the table to Minitab and use the time series plot of ground-simple of Minitab to make the graph, since the time-series was recorded in quarter, so we admit the quarter of calendar in time scale.The plot of this time-series looks likeForm the graph above We can roughly find out that there is a decre asing trend over time, a clear every quarter seasonal effect and it is a table time series, the pattern is rule-governed with little random flutter. With the purpose of confirming the characteristics of the time-series, we use the cantered moving add up outs (CMA).Since the CMA is the average and smooth data of the genuine patterns, 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 every quarter seasonal effect so the length of moving average is 4, and chose the moving averages, plot the graph smoothed vs. veridical.Negative Trend Structure, an decreasing trend over timeIt is a negative trend social system. 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 change magnitude to around 405.A clea r seasonal structure , analog seasonal structureIt can be seen from the graph above that there is a clear quarterly seasonal structure, for each quarter 1 the actual observed repute 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 worker StructuresQ1 Q2 Q3 Q4-13 30 22 -23These 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 disagreement is constant about the trend so this seasonal structure is additive.A table time series, the pattern is regular with little random noiseThe 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 undertake stability, from about 485 down to around 478 to roughly 471, ect. No to a greater extent than 10units lower. standard the time-seriesQUADRATIC TREND MODELSThere are two trend models ,one is bilinear trend model (Trend = a + b*t ) and the other is quadratic polynomial trend model (Trend = a + b*t + ct2 ), and as we turn in 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 usually used measures of forecast true statement imply Square Deviation (MSD), Mean Absolute Deviation (MAD) and Mean Absolute Percentage Error (MAPE). And the small the data is, the more accurate of the forecast. And it can be seen from the graph above that the Quadratic Trend Model, MA PE=0.43297, MAD=1.91172, MSD = 5.44313, and to the one-dimensional 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 meat 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 MINITABDetrend a Time-SeriesDetrend a Time-Series, which means Sales-Trend (DIV), the gap between the actual sales and the forecast sales. After we yield 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, an d so forthBy using the Minitab, we can use the calculator to approximate the result. manufacture the seasonal Indices by MINITABAs seasonal worker Indices is the quarter average of DIV, subsequently we know 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 depict 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 worker Indices is the average of each quarter of DIV, so the seasonal indices can be calculated as belowQuarter1=SI1= (DIV1+DIV5+DIV9+DIV13+DIV17)/5= -24.0937Quarter2=SI2= (DIV2+DIV6+DIV10+DIV14+DIV18)/5=20.4062Quarter3=SI3= (DIV3+DIV7+DIV11+DIV15+DIV19)/5=17.2812Quarter4=SI4= (DIV4+DIV8+DIV12+DIV16+DIV20)/5=-13.5937Since seasonal indices is the average of each quarter of DIV so SI is quarterly cycle, the value of SI5 will sufficient 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 accuracyFuture ForecastAs the Future Forecast equal Future Trend plus Future Seasonal Indices, so first we should use the CMA to calculate the laterlife 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.Th e plot of time series of sales and forecast looks likeSo the next four quarters Q1, Q2, Q3 Q4 of 2009 areQ1=366.116, Q2=406.796, Q3=400.011, Q4=365.637Measures of forecast accuracyAfter 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/nii. Mean Absolute Deviation MAD = S Xt Ft /niii. Mean Percentage Error MPE = S (Xt Ft)/Xt /nSince 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/nMAD = S Xt Ft /nMPE = S (Xt Ft)/Xt /nAnd 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. An d usually we use the MPE to confirm the accuracy. Lets 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 lock away accuracy.ReservationIn 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 MultiplicativeIn this forecasting, we analysis the time-series as additive seasonal structure by using method belowSeasonal StructuresQ1 Q2 Q3 Q4-13 30 22 -23In this casefor a given variable the quarter 1 is 13 units below trend, quarter 2 is 30 units above trend, etc. This is an Additive Seasonal exponent.Alternatively we could have show the index as followsQ1 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 asQ1 Q2 Q3 Q487% 130% 122% 77%This is called a Multiplicative Seasonal Index and if the seasonal deviation is comparative 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 modelIn 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. destinationThe objective of the company is to use time-series data to construct a forecast of the next four quarters sales. So as to do the forec ast 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 quarters 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.