Network Opimisation Problems And Forecasting

Network Opimisation Problems And fancyingThe Makonsel Company, a fully integrated partnership that both produces and sells goods at its sell surfacelets. after production, the goods are stored in troupes two wareho theatrical roles until needed by the retail bylets. Trucks are used to transport the goods from the two jutts to the warehouses, and thusly from the warehouses to the three retail outlets.Using units of full truckloads, the following(a) defer shows all(prenominal)(prenominal) plants calendar monthly output, its ecstasy represent per truckload sent to distributively warehouse, and the maximum amount that it provide ship per month to to each one warehouse.Unit Shipping CostFor each retail outlet (RO), the next table shows its monthly demand, its shipping cost per truckload from each warehouse, and the maximum amount that evoke be shipped per month from each warehouse.Unit Shipping CostThe Managements objective is to determine the shipping plan (number of tru ckloads shipped per month from each plant to each warehouse and from each warehouse to each retail outlet) that result minimise the totality 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 regulate, spreadsheet formulation for this problem by using the solver of excel and interpretation and testimonial of the result.The distribution networkA network mildew for the Makonsel Company problem as a minimum-cost flow problemAccording to the data 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 sh ip per month (given in square brackets of the arc) through each lane is shown preceding(prenominal) corresponding arrow in the above Figure.Algebraic FormulationSolutionDecision variables Makonsel must determine how much to ship per month from each plant to each warehouse and from each warehouse to each retail outlet. allow 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 Makonsels problem may be formulated asObjectiveSubject toThe first five dollar bill constraints pick up 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 FormulationAfter 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 interpret on a lower floor.A spreadsheet model for the Makonsel Company minimum-cost flow problem, where the changing cells (C4C13) show the optimal solution obtained by the problem 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 all over the arrowsBy 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 one hundred seventy-five 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 truckloa ds 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 throw off known the shipping cost per truckload from each plant and each warehouse from the table.Thus the borderline Cost= 425*125+560*75+510*125+600*175+470*100+505*50+490*100+390*50+410*150+440*50=488,125ConclusionDetermined 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 have the shipping plan with the minimum total shipping cost of 488.125.ForecastingIntroductionThe time-serial publication below relates to the Sales of a company (00s) for the last five years.The objective is to use the information contained in the time-series data above to produce a forecast of the next cardinal cans sales. In order to achieve the objective, the following issues will be discussed Analysis this time-series, De motility a Time-Series and construct the seasonal Indices by MINITAB, Forecasting the next four quarters sales and use measures to identify the forecast accuracy, Reservations around the appropriateness of the forecasting procedure used.Time-series Analysis main(prenominal) characteristics of this time-seriesThe first step in any forecasting exercise is to eyepatch a interpret 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 necessitate 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 decreasing twist over time, a clear every quarter seasonal effect and it is a table time series, t he pattern is regular with little random noise. With the purpose of confirming the characteristics of the time-series, we use the cantered contemptible averages (CMA).Since the CMA is the average and smoothened 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 timeIt is a negative trend coordinate. 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 roughly 478 to roughly 471 and finally it decrease to around 405.A clear seasonal structure , additive seasonal structureIt can be seen from the graph above that there is a clear quarterly sea sonal structure, for each quarter 1 the actual observed rank is about 13 units below the trend valuate. 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 deviation 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 reduce stability, from about 485 down to around 478 to roughly 471, ect. N o more than 10units lower.Model the time-seriesQUADRATIC TREND MODELSThere 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 work out the Cantered Moving number (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 form aberrancy (MSD), Mean Absolute Deviation (MAD) and Mean Absolute Percentage Error (MAPE). And the smaller the data is, the more absolute 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 running(a) Trend Model, MAPE=0.51281, MAD=2.21232, MSD = 7.74838. The data of the Quadratic Trend Model are all smaller th an the Linear Trend Model, which means that the value of quadratic equation 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 surrounded by 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 worker Indices by MINITABAs 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 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 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 accuracyFuture Fo recastAs 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 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 for ecasts 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. And 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 still accura cy.ReservationIn this forecasting procedure we faced two choices, one is obstinate 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 Index.Alternatively we could have expressed the index as followsQ1 Q2 Q3 Q4-13% 30% 22% -23% present 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 Multiplic ative 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 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.ConclusionThe 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 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 th at 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.