AGGREGATE LOGISTICS OPTIMIZATION FOR A LARGE BEVERAGE COMPANY by ERNEST HONGTU WU M.A. (Economics) University of British Columbia 2000 B.Econ (International Economics) Renmin University of China 1995 A THESIS SUBMITED IN PARTIAL FULFILMENT OF THE REQUIREMENTS FOR THE DEGREE OF MASTER OF SCIENCE IN BUSINESS ADMINISTRATION in THE FACULTY OF GRADUATE STUDIES FACULTY OF COMMERCE AND BUSINESS ADMINISTRATION We accept this thesis as conforming to the required standard THE UNIVERSITY OF BRITISH COLUMBIA December 2002 © Ernest Hongtu Wu, 2002 In presenting this thesis in partial fulfilment of the requirements for an advanced degree at the University of British Columbia, I agree that the Library shall make it freely available for reference and study. I further agree that permission for extensive copying of this thesis for scholarly purposes may be granted by the head of my department or by his or her representatives. It is understood that copying or publication of this thesis for financial gain shall not be allowed without my written permission. Department The University of British Columbia Vancouver, Canada Date DE-6 (2/88) ABSTRACT In 2002, The Centre for Operations Excellence (COE) at UBC Commerce carried out a logistics optimization project for Mark Anthony Group (MAG), a manufacturer and distributor of premium wine and alcoholic beverages. The goal of the project was to determine a production, distribution and inventory plan for MAG's "Mike's" products in the US market, that minimized the total logistics cost. It is a multi-commodity, multi-period network flow problem formulated as a linear program with 48,000 decision variables and over 16,000 constraints. Based on the LP model, COE developed a customized computer based planning tool, Aggregate Logistics Optimization Tool (ALOT). ALOT is designed to be an operational tool, providing recommendations on monthly production, distribution and inventory plans for MAG planners. Also, ALOT could be used as a tactical planning tool, supporting MAG's capacity planning, investment, transportation mode choice and other business decisions. ALOT has changed the way MAG does business planning. With ALOT being implemented by MAG, the production plan in 2003 is expected to be significantly different from that in 2002. ii TABLE OF CONTENTS ABSTRACT ii TABLE OF FIGURES v ACKNOWLEDGEMENTS v 1. INTRODUCTION 1 1.1 MAG Operation and Products 1 1.2 Problem Definition 2 1.3 Project Overview 3 1.4 Model Assumptions 4 1.4.1 Model Scope1.4.2 Model timeline 6 1.4.3 Demand management 7 2. LITERATURE REVIEW 8 3. METHODOLOGY 9 3.1 Decision Variables3.1.1 Indexes3.1.2 Decision Variables 10 3.2 Data 13.3 Linear Programming Model 1 3.4 Model Implementation 17 3.5 Checking Feasibility 20 4. RESULTS - APPLICATION OF A LOT 25 4.1 An Overview of ALOT4.2 Implementation as an Operational Tool4.3 Implementation as a Tactical Tool 27 5. CONCLUSION 31 REFERENCES 3 APPENDIX - ALOT User Manual .....34 TABLE OF FIGURES Figure 1: Physical and informational flow of Mike's product logistics system 2 Figure 2. Assignment of regions to DCs for order fulfillment 5 Figure 3. Logistics Network 6 Figure 4. ALOT Model in MS Excel Spreadsheet 18 Figure 5. ALOT Main Menu 19 Figure 6. Cumulative demand vs. available supply '. 23 Figure 7. Monthly Operational Planning with ALOT 26 Figure 8. Production plan of L6R by varying co-packing price at High Falls. 29 iv ACKNOWLEDGEMENTS I extend my heartfelt thanks to the many people who contributed to my successful studies and particularly to this thesis: • My parents for their kind support through out all the years up to the completion of this Master's program; • Professor Martin L. Puterman, my thesis advisor and project academic advisor, for his valuable input and comments on my thesis. It has been a pleasure working with and learning from one of the best professors in the field; • Professor Harish Krishnan, for his contribution as committee member; • Mehmet A. Begen, for his guidance and help as project manager in this project; • Center for Operations Excellence (COE) at University of British Columbia, for offering this rigorous Master's program and giving me opportunity to work on such an interesting and challenging project; • My classmates with whom the spontaneous discussion in the COE office was so valuable; and • Carol Leacy and the project group at Mark Anthony Group (MAG), for their support of this work, their interest and assistance. 1. INTRODUCTION 1.1 MAG Operation and Products The Mark Anthony Group (MAG) is a Vancouver based manufacturer and distributor of premium wines, ciders, beer and other refreshment beverage. Company brand wines include Mission Hill Family Estate, Mission Hill Vintner's Select, Cordillera, 49 North, Lapis, and Mission Ridge. MAG's portfolio of refreshment beverages includes Mike's Hard Lemonade, Chrome, Okanagan Premium Cider, Okanagan Extra Cider, and California Cooler. The company also distributes other wine and refreshment beverage products in Canada. The brands represented include Corona, Beringer-Blass, Rodney Strong, Stimson Lane, Columbia Crest, Corbett Canyon, Yalumba, Deinhard, Santa Rita, and Torres. The MAG began exporting its products in 1996, with the introduction of Mission Hill wines to the UK market. The international export of Mission Hill wine is a key element of the company's strategy to become one of the ten most recognized wineries in the world. In 1999, after phenomenal growth in the Canadian market, Mike's Hard Lemonade was introduced to the New England area of the US. The response was extremely positive. Within six weeks, a years worth of forecasted sales had been met. Based on that success, management decided to invest heavily in the US export market. A US division was soon established and the brand was rolled out in all states by summer of 2000. Impact Magazine, an influential publication on branding success, rated Mike's Hard Lemonade as one of the top 10 new brands of alcoholic beverages in the US for the year 2000. Sales of "Mike's" in the US continue to grow through 2002. In the summer of 2002 Iced Tea and Cranberry Lemonade flavors were added to the portfolio. The physical and informational flow in the US logistics system for the "Mike's" products is shown in Figure 1. Production is carried out at plants that are not owned by MAG. MAG gives production orders to the plants, which are called co-packers. MAG buys production hours from the co-packers and provides a production plan on or around the 10th of each month, fixing the volume of each stock keeping unit (SKU) produced in the next month. MAG pays the co-packers based on actual production volume. After the beverages are produced, they are shipped to Distribution Centers (DCs) in full truckload. The distributors of the products place orders to MAG sales personnel and MAG sends fulfilled orders to distributors by trucks from DCs. Meanwhile, MAG sales personnel keep in close touch with distributors, exchanging information about products, promotion, and acquiring the estimated order quantity and mix of SKUs for the coming 12 months. This forecast information is collected by MAG planners, who determine the production, transportation and inventory schedules based on the forecast and cost structure. Finished goods for future orders Finished goods (orders) Agreements for production time arrangements and adjustments (what, how much and when to produce) MAG Planning Forecasts, orders and any other information about market and distributors Orders, shipment confirmation Forecasts, promotions, information exchange MAG Sales Figure 1: Physical and informational flow of Mike's product logistics system 1.2 Problem Definition The success of "Mike's" products in the US market resulted in a series of business issues during the summer of 2001. Production plants failed to meet the production plans; distribution centers (DCs) did not have the right mix of SKUs in right volume to fulfill the orders; retailers complained about stock-outs; and high transportation cost occurred due to transshipment among DCs. In a highly competitive beverage market, these issues could erode the confidence of retailers and negatively affect MAG's expansion plan. In light of these issues, MAG needed a planning tool to improve its US logistics system for production, transportation and inventory, i.e., putting 2 right amount of the right SKUs at the right place at the right time. The Centre for Operations Excellence (COE) at the University of British Columbia (UBC), as an industrial partnership project, developed a customized computer based planning tool, Aggregate Logistic Optimization Tool (ALOT), for the production and distribution of "Mike's" products in the US market. In order to respect the confidentiality of MAG data, we use modified data in this thesis. The analyses and results are presented in a way that avoids using actual data. 1.3 Project Overview ALOT is a customized computer based planning tool designed for the production and distribution of "Mike's" products in the US market. The main goal of the ALOT project is to enhance the performance of MAG's U.S. inventory and distribution system for "Mike's" products using a computer based optimization model. Specific goals include determining optimal co-packer production levels, allocations to DCs, transshipment between DCs, and DC inventory levels by minimizing total cost while considering business constraints, and building a stand-alone tool that will be used by MAG planners for production and distribution planning. This project was carried out in two phases. During the first phase, from January to April 2002, the project team developed an understanding of MAG's operation systems in the US market, including physical and information flows, forecasting and production timelines, and the constraints involved in the operation. The COE team aggregated and simplified the problem into one that is solvable and useful for MAG planners. This simplified model included the production, distribution and inventory systems of 10 co-packers, 4 DCs, and 12 typical products, with 4 sizes (6 pack, 12 pack, 16 oz and 24 oz) for each of three flavors (Lemonade, ice tea and cranberry). This initial model was used to prove the concept and convince MAG of the model's capabilities. The first stage concluded with a presentation to MAG in the COE office in May 2002. In the second phase, from May to September 2002, the model was expanded to describe the actual logistics structure in the US market. The new model includes 10 co-packers, all 5 DCs, and all 40 SKUs currently supplied to the US market. It also allows inter-DC transshipment, which had not been implemented in the Phase I model. After the new model was validated, a stand-alone planning tool, ALOT, was developed. 3 Project teams from MAG and COE worked closely in all steps of the project. The COE team has an academic advisor, a project manager and a project analyst. The vice president in charge of systems and process integration was the team leader from MAG. The MAG team also included a data analyst, the director of purchasing and planning, the production planner and the distribution planner. The two teams worked interdependently in defining the project goal, in data collection, and in designing the tool and implementation. MAG and COE group members kept good communication by holding regular meetings, milestone presentations, and site visits. MAG provided data for the template designed by COE. After ALOT was developed, COE organized a training session for future users of the tool, and collected feedback from the users. Also, COE team members participated in planning meetings at MAG, studied the company's operation decision-making process and provided technical support for running ALOT. Collaboration between two teams was a key factor in the success of this project. 1.4 Model Assumptions Before presenting the model in the next section, we describe here the relevant business environment at MAG. These points are essential to describe the business problem in a numerical model. 1.4.1 Model scope The supply chain of "Mike's Hard" beverages includes the upstream materials suppliers, e.g., vendors of bottles and packaging boxes, and the downstream distributors and retailers. Modeling the whole system was not the goal of this project. Instead, we modeled a segment of the logistics system where relevant business issues could be addressed and value could be added. Therefore, after studying the system and meeting with MAG, we narrowed the scope of the model to two levels of the supply chain, the co-packer and DC levels. By doing so, we assume that sufficient raw materials are supplied to the co-packers in a timely manner, and all the products are sent out of DCs without delay upon order of distributors. To focus the model, we assume that a single DC serves each region in the US market. There could be urgent orders of a large quantity from one region that MAG supplies from several DCs. However, this scenario occurs rarely and is ignored for the purpose of this project. Consequently, the 55 regions are "bundled" into 5 groups according to the predetermined assignment of regions 4 to DCs (as shown in Figure 2). It was determined in view of the transportation time and transportation cost from DCs to regions. If the time and cost suggest contradictory assignment plan, say, it is cheaper for a region to receive products from one DC while it takes less time to receive products from another DC, the transportation time dominates, i.e., shorter time is more favorable than lower cost. Dart All Star New Castle Monroe Commercial Figure 2. Assignment of regions to DCs for order fulfillment Another modeling assumption is that all production is supplied to distributors via DCs. Direct transportation from co-packers to distributors is not part of this model. Figure 3 provides a symbolic description of the logistics network of MAG. The model scope, which includes the co-packer and DC levels, is shown by the dashed line. 5 Distributors Figure 3. Logistics Network 1.4.2 Model timeline The following timeline is used in modeling the multi-period problem. (1) Once a month, MAG provides co-packers with the production plan for the next month; (2) The actual production can take place at any time over the course of a month, i.e., products are not guaranteed to be ready for distributing until the end of each month; (3) Products are transported to DCs immediately after they are produced; (4) Transportation from co-packers to DCs takes no more than 4 days; (5) Transshipment between DCs takes less than 5 days; (6) The inventory at DCs is examined at the end of each month; (7) The demand forecasts are updated once per month. 6 In keeping with above timeline, we use one month as the standard time period in this model. Since the beverage is sent to DCs immediately after it is produced, production and distribution from co-packers to DCs are assumed to occur in the same period. Transshipment between DCs is also completed in one time period. We assume that no production, distribution and transshipment occurs across periods. Transportation time, either from co-packers to DCs or between DCs, is much shorter than one period. Therefore, it is ignored and has no effect in the model. 1.4.3 Demand management Since the scope of the project only covers the co-packer and DC levels, ALOT sees demand at the DC level. Forecasted demand for each SKU at each DC is the sum of the forecast of that SKU in each of the regions associated to this DC, in accordance with the pre-determined DC to region assignment. As a planning tool, ALOT optimizes production and distribution to meet forecasted demand in a timely manner, and thus maintains customer service levels. Therefore, backorders are not modeled by ALOT. As discussed above, MAG sales personnel collect the forecast information from the distributors. The monthly forecast information at individual distributor or region level might be inaccurate, compared to the actual demand. However, MAG's experience indicated that the aggregate forecast for the entire US market is close to the actual total demand. At the DC level, the positive and negative forecast error at distributor level partly offset each other during the course of aggregation. Therefore, using forecast at DC level results, to some extent, in a lower forecast error than at distributor level. In the remaining parts of this thesis, we first review some of the previous research on similar problems. Then we introduce the linear programming model. In section 4 we describe the planning tool, ALOT, and show how it is applied as an operational tool and a tactical planning tool. And Section 5 concludes the thesis. 7 2. LITERATURE REVIEW ALOT models a multi-period, multi-commodity production and inventory planning problem, in which differentiated products use common facilities and materials and they must be considered jointly instead of independently. There is a considerable body of research in the area of multi-product production and inventory planning. Johnson and Montgomery [1974, Chapter 4.6] describe multi-product linear programming models, including resource constrained production planning, process selection decision, product mix decision, production smoothing problem, and production and work force planning. Most of the problems consider backlogging, which is not considered in ALOT. Phillips and Garcia-Diaz [1981, Chapter 5.III] introduce multi-commodity network flows. They discuss linear programming formulation of models of this type, but their emphasis is on the algorithms for solving for integer optimal solutions. Evans (1977) also focuses on obtaining approximate integer solutions to the dynamic, deterministic, multi-product, and multistage production-inventory planning problems. Bazaraa and Jarvis [1977, Chapter 11.3-4] examine the application of the decomposition algorithm to the minimal cost multi-commodity flow problem, and specifically analyze characterization of basis while applying the simplex method. Ahuja et al (1995) list several applications of multi-commodity flows problem, including communication networks, railroad transportation networks, racial balancing of schools, and multi-vehicle tanker scheduling. Brown et al (2001) describe the Kellogg Planning System (KPS) used by Kellogg Company, the world largest cereal producer. We found that ALOT resembles KPS in many ways. KPS is a large-scale multi-period linear program, guiding production and distribution decisions for its cereal and convenience foods businesses. An operational version of KPS helps determine where products are produced and how finished products and in-process products are shipped between, plants and DCs. Also, KPS has a tactical version, which helps to establish plant budgets and make capacity-expansion and consolidation decisions. Uses of KPS reduced production, inventory, and distribution costs by an estimated $4.5 million in 1995. KPS also guided a consolidation of production capacity with a projected savings of $35 to $40 million per year. The major difference between KPS and ALOT is that the cereal and convenient food production process is more complex that beverage bottling. Therefore, KPS linear program models a multi-stage production system while ALOT is a single stage system, although it is multi-period. 3. METHODOLOGY The problem studied here is a multi-period, multi-product network flow problem formulated as a linear program. Given the large volume of beverage output at MAG, the possibility of non-integer solutions in production or logistics plans that are associated with LP is not important. 3.1 Decision Variables 3.1.1 Indexes Before defining the decision variables, we first introduce the indices used in this model. (1) SKU index i: Currently, MAG supplies 40 SKUs to the US market. These 40 SKUs consist of three flavors, Lemonade, ice tea and cranberry. There are five packages including 6 bottle packs, 12 bottle packs, 16 oz bottles, 22 oz bottles and 24 oz bottles. In addition to the differentiation in flavor and package, the regulations on beverage sales at some states put specific requirements on the products. For instance, some states require the bottles to be non-refundable; some other states have an alcohol percentage limit on beverage of this type. Therefore, the SKUs are indexed using flavor, package and regulations. For example, T12L represents "Ice Tea, 12 bottle pack, Low alcoholic volume". Besides the SKUs that are currently supplied to the US market, ALOT has 10 "dummy" SKUs, noted as "Dl" to "D10". "Dummy" SKUs are included for the convenience of MAG planners. When new SKUs are introduced to US market, there is no need to make structural change to the model except for replacing the "dummy" SKUs with the newly introduced SKUs. (2) Co-packer index j: As of the spring of 2002, MAG held contracts with ten co-packers to supply the US market. Hence, j belongs to a set with 10 elements. (3) DC index k: Currently, the "Mike's" products are supplied to the US market from five DCs. Thus k takes a value from a set with five elements. When there is a need to describe more than one DC, e.g., modeling transshipment, we use k' to represent the other DCs. (4) Time period index t: Since this LP problem uses month as the base time period, t runs from 1 to 12 representing a planning horizon of 12 months. 9 3.1.2 Decision variables .^represents the number of cases of SKU i produced at co-packer j for DC k in period t. X variables represent both production and distribution from co-packers to DCs. For instance, the value of XL6R, HFL, COMM, 3 represents the volume of regular 6 bottle packs of Mike's Hard Lemonade produced at co-packer High Falls to be sent to DC Commercial in the 3RD period. Yikk-, represents the number of cases of SKU i transshipped from DC k to DC k' in period t. For example, the value of YC24T, MONR, ALST, 6 represents the volume of 24 oz Texas Mike's hard Cranberry transshipped from DC Monroe to DC All Star, during period six. Iikt represents the number of cases of SKU i stored in DC k at the end of period t, i.e., the closing inventory. Although inventory variables could be inferred from the solutions of production and transshipment variable, they are put here to explicitly report the inventory level and associated cost, and simplify model formulation. All together, ALOT has 48,000 decision variables. 3.2 Data Input data in this LP model includes the objective function coefficients and the right hand sides (RHS) constraints values. Objective function coefficients refer to costs, which include unit SKU production cost of at each co-packer, unit transportation cost from each co-packer to each DC, unit transshipment cost between each pair of DCs, and unit inventory cost at DCs. The unit inventory cost MAG pays the DCs has three components, freight-in cost, freight-out cost and holding cost, which occur when a case is shipped into, out of a DC, or is held in a DC for one or a proportion of a month respectively. The cost parameters are constant during the planning horizon, as those unit costs are fixed by the annual contracts. Since most of the business sections modeled by ALOT are in the US, we use the US dollars as the currency of this model. The only exception is a co-packer, LKP, which is in Canada, and MAG pays LKP in Canadian dollars. We convert the production cost at LKP into US Dollars using the exchange rate in May 2002. The impact of exchange rate on the model is neglected. 10 This cost data, as well as the RHS constraints such as production capacity, DC capacity and transportation regulations, were provided to ALOT from MAG's information system. MAG calculates unit cost by breaking it down into subcategories, e.g., materials cost, packaging cost, human cost, and utility cost. The unit costs are updated when any sub-costs change. The RHS constraints data are stipulated by co-packing contracts. A subset of the RHS data is used to model the product mix and transportation/transshipment regulations. Availability of data, especially the accurate cost data, is a key factor contributing to the success of this project. In this model, we assume all data are deterministic. 3.3 Linear Programming Model Min X cu (Z xm<) + X tjt (X X* ) + X^(X A,) +X h (X xm + X A.,) ij k,t j,k i,t k i,t k ij,t i,k',l + X °k (X X A,) + X A, ) + X (X A*V ) k t i i,k',t k,k',t i subject to j < k' (3.3.1) X Xijkt - rjks(i)J, Vs(i),j,t (3.3.2) k,t (3.3.3) vy,* (3.3.4) X Yu*-i - z'**' VA:,*' (3.3.5) XA ^K i Vk,t (3.3.6) ^ikt — atfi,k,t+\ \/i,k for t = b,...,\2 (3.3.7) Zxij^™j< i,k vy,* (3.3.8) XX^"yv V/',y,r (3.3.9) k 11 x,kl>o ^0 \/i,j,k,t \fi,k,t Vi,k,k',t (3.3.10) (3.3.11) (3.3.12) (3.3.13) The objective of this LP model is minimizing the total logistics cost, which consists of production cost, transportation cost, transshipment cost and inventory cost. The coefficients are defined as, Cjj = unit production cost (US$/case) of SKU i at co-packer j ik = unit freight in cost (US$/case) at DC k ok = unit freight out cost (US$/case) at DC k hk = unit inventory holding cost per month (US$/case) at DC k tjk = unit transportation cost (US$/case) from co-packer j to DC k qkk- = unit transshipment cost (US$/case) from DC k to DC k' We define fik, as the forecast of product i at DC k in period t. While computing the freight-out cost occurring at DCs,/^,, summed over SKUs, are adjusted by a,, the forecast safety factor for the corresponding month. The forecast safety factor a, is defined to be greater or equal to 1. This parameter is used as multiplier to the forecast data, addressing the possible under-forecast due to seasonality or allowing for a larger safety stock. The factor varies with the time period and applied to all the DCs and all SKUs in a particular time period t. ALOT allows the users to relax each of the LP constraints. The users could choose any combination of the constraints. Only the inventory balance constraint must be selected at all time. Constraint (3.3.1) is the inventory balance equation. ALOT uses the standard production, inventory, and demand recursion: +/a,,-i=aif*>+/* va,r i k' r 12 which must hold at each DC in each period. It means the amount of each product transported into the DC plus the last period closing inventory equals the amount shipped out in this period plus this period closing inventory. The initial inventory, Iik0, is an input to the model and is challenging to obtain in a timely fashion. The production capacity constraint is formulated as (3.3.2). Due to the process requirements on bottling of different SKUs, not all the SKUs are produced in the same line. Some subsets of the SKUs compete for production capacity. Specifically, all the 6 and 12 bottle pack SKUs could be produced in the same line. 16 oz bottles need their own line, and 22 and 24 oz bottle share the same production line. This characteristic makes this model a typical multi-commodity network flow problem. s(i) represents the subset of SKUs that are produced in the same production line. Hence s(i) e {6,16,24}. kx(i)Jj represents the monthly capacity of the corresponding production line. The constraint, iesOU requires that the production level in each production line at each co-packer in each period does not exceed the reliability factor adjusted production line capacity available in that period at that co-packer, r,- represents the co-packer reliability factor, ranging from 0 to 1. This parameter is used as a multiplier of the production line capacity, and is modeled because co-packers might not be able to produce the amount they promised. The capacity of all the production lines at a co-packer in all time periods is multiplied by this parameter. MAG has historical information on co-packer reliabilities, normally expressed in percentage. Saying one co-packer is 95% reliable indicates this co-packer on average produces 95% of planned level, as shown by previous experience. Constraint (3.3.3) models the product mix with co-packers. Not all the co-packers have these three production lines defined by s(i). Most of them have one or two. Also, the co-packers do not produce all the SKUs the lines could produce. There is a specific mix of SKUs produced by each co-packer. To accommodate the SKU mix in model, we defined a parameter called zero-production, oo if SKU i is produced with co-packer j. 0 if otherwise 13 In ALOT, we use very large numbers, e.g., 20,000,000, to represent "oo" in the LP model. The SKU mix constraints require k,I i.e., the annual production of a certain SKU at a co-packer is less than or equal to the corresponding zero-production parameter. Transportation and transshipment rules are formulated as constraints (3.3.4) and (3.3.5). Distribution of the "Mike's" products in US market is subject to certain business rules, based on the regulations by US government. One of them is that transshipment from the DCs located in US to the DC in Canada is prohibited. Also, there is no transshipment within a same DC. To represent these rules in the model, we defined another two sets of parameters z'jh zero-transportation and z\r, zero-transshipment. r oo if transportation from co-packer j to DC k is allowed. z'jk= -- 0 if otherwise { oo if transshipment from DC k to DC k' is allowed. 0 if otherwise The transportation rule constraint is, which enforces the total transportation volume from certain co-packer to certain DC to be less than or equal to the corresponding zero-transportation parameter. The transshipment rule constraint is formulated as, It works in the same way as the transportation rule constraint, but with DC pairs instead of co-packer to DC routes. 14 Constraint (3.3.6) models DC capacity. Compared to the constraints discussed above, the DC capacity constraint is a "soft" one. In the operation of MAG's business, if inventory exceeds the inventory capacity MAG buys from a DC, the DC would find extra space for MAG at flat rate. As a planning tool, ALOT should enforce this constraint. However, in real operation of this tool, this constraint is usually relaxed. The parameter associated with DC capacity is wk, non-negative. The constraint requires i Safety stock issue is addressed by constraint (3.3.7). Carrying safety stock helps prepare for uncertain demands and unforeseen production problems, which were the main cause of stock-outs at MAG in summer 2001. A wealth of research has been done with respect to determining appropriate level of safety stock. Among them are Arrow, Harris and Marschak (1951) and Veinott (1965), who studied single period and multi-period inventory control models. Perhaps ALOT should have been modeled as a multistage stochastic-programming model that directly handles uncertainty in demand, and possible uncertainty in manufacturing yields. But such a model would require an unwieldy amount of data, e.g., probability transformation matrices and demand distribution. This data is hard to obtain from existing data warehouse and strong assumptions may be required to make those calculation. Moreover, such a model would be very difficult to solve. In the case of this project, experience is a good teacher. We follow MAG safety stock policy, which is to carry at least one month worth of inventory. Converting into ALOT, this policy requires Iik,^atfi,k^ Vi,k for t = b,...,\2 i.e., the closing inventory of each SKU in each DC for each period should be no less than the reliability factor adjusted forecast of that SKU faced by that DC in the following period. The forecast safety factor, a,, is defined in objective function. However, the "carrying one month of inventory" policy might be difficult to implement due to insufficient opening stock of period t = 1. It may result in a tight production plan in the first several months, even infeasible solution. In wake of this problem, ALOT provides options for users to enforce this constraint starting from period t = I, t = 2, r = 3 or completely relax this constraint. Hence, this constraint is enforces starting from period b, where 1 > b > 12. 15 Constraint (3.3.8) formulates the minimum production. In the co-packing contract signed between MAG and some of the co-packers, it is stipulated that if the annual production volume does not hit a certain target, MAG needs to pay a higher unit price the next year. This constraint is formulated as where m-fi represents the total monthly production of all SKUs with a certain co-packer. The minimum production level constraint applies only to the co-packers who require such a limit in their contracts. Other co-packers have the minimum production level of zero. Fixed production plan is modeled by (3.3.9) and (3.3.10). MAG planners occasionally need to optimize the logistics system with the production plan at certain co-packer(s) or certain time period(s) fixed. For instance, what is the optimal production plan for month 2 given the month 1 plan is fixed? To facilitate this constraint, we introduced two sets of RHS parameters, uiju production upper bound and /,y„ production lower bound, where oo if otherwise, and hjt ~~ Xij, ifxij, > 0 0 if otherwise where xijt = fixed production plan for selected SKU / at selected co-packer j, in selected time period t. (i,j,t) are selected combinations with xijt> 0. The fixed production constraint enforces k 16 For the production variables the users plan to fix, the corresponding production upper bound and lower bound are equal to the fixed value. For unfixed variables, their upper bound is infinity and lower bound is zero. Finally, constraint (3.3.11), (3.3.12) and (3.3.13) are non-negativity conditions. All decision variables shall be non-negative. 3.4 Model Implementation The model was set up in MS Excel, mainly for the purpose of easy communication with client. The clients use MS Access and Excel in their daily planning work. Thus it is much easier to pull data from and export solution to MAG's existing information system in a format compatible with ALOT. Also one could put all decision variables and constraints in one Excel spreadsheet, which makes the model a big picture easily understandable. Putting 48,000 decision variables and 16,000 linear equations in one Excel spreadsheet and locating them appropriately requires quite a bit of work. Out of the 256 columns available in Excel spreadsheet, the model uses 218 columns. Various techniques in Excel and Visual Basic, e.g. conditional formatting, range naming, are applied to make the model well organized. Figure 4 is the screenshot of ALOT model set in one Excel Spreadsheet. 17 Decision Variable: Model Constraints Cost Calculation Control Panel 6) 7) 12) 9) 4) 3) 2) 1) 5) 11) 10) 8) 13) 14) Function of Tables Please note that all of these tables are accessible from the control panel. Production Plan 1) Monthly production total ol all SKUs 2) Annual production lor each SKU 3) Monthly production (or each production line 4) Monthly production (or each SKU at each co-packers Co-packer to DC Transportation Plan 5) Annual transportation total of all SKUs 6) Monthly transportation total of all SKUs 7) Monthly transportation for each SKU Inventory Plan 8) Annual/monthly inventory total of all SKUs 9) Monthly inventory for each SKU Inter-DC Transshipment Plan 10) Annual/monthly transshipment total of all SKUs 11) Monthly transshipment total of all SKUs 12) Monthly transshipment for each SKU Total Costs 13) Monthly Cost 14) Total Overall Cost Figure 4. ALOT Model in MS Excel Spreadsheet While the model spreadsheet is the heart of ALOT model, data input and output spreadsheets are important components of the tool. ALOT has a main menu enabling the users to go to individual spreadsheets and switch between them conveniently. Using checkboxes in the main menu, ALOT users can choose the constraints to be held in each solve. Figure 5 is the screenshot of ALOT main menu. The program coding of ALOT, including form and button design, input data validation, LP solver integration, and feasibility checking, is done in Visual Basic for Application in MS Excel. Main Menu Aggregate Logistic Optimization Tool for Mark Anthony Group Mike's Piodncts' kl USA Market p Load Input Data -Production Costs Co-packer to DC Transportation Costs Inventory Costs Inter-DC TranssNpment Costs Production Line Capacity & Co-packer Reliability Factor DC Inventory Capacity Minimum Monthly Production Forecasted Demand & Forecast Safety Factor Initial Inventory Fixed Production r- Salve • p Read Output Data -Production Plan Distribution Plan —i Inventory Plan Cost Summary [•1 Perform Feasibility Check Basic Constraints (Strongly recommended) ;^ Inventory Balance (must be held at all times) SKU Mix at Co-packers ^ Co-packer to DC Transportation Rules \, Inter-DC Transshipment Rules Optional Constraints Limited Production Line Capacity Limited DC Inventory Capacity Enforced Minimum Production — Rxed Production Levels Carry One Month of Inventory Starting from Period.. 1 » Solve Go to Model User Manual Ceiine foi Op nation.'; Excellence, UBC Coinnteic e f^^^^f^^ Figure 5. ALOT Main Menu The standard Microsoft Excel Solver has a limit of 200 decision variables in linear programming problem, which is far from sufficient for this application. We needed a solver that handles as many as 48,000 decision variables and is compatible with Microsoft Excel. We chose Premium Solver Platform with Xpress Solver Engine, developed by Frontline Systems Inc.. Xpress Sovler handles up to 200,000 decision variables using the dual Simplex method. It saves solution time by applying techniques such as sparsity exploitation, steepest-edge pivoting, and degeneracy 19 overcoming. It takes about 12 minutes for Xpress Solver to solve this problem. Considering the size of the problem, this solution time is acceptable. For a comparison of solution time, as well as for validating the solution, we set up and solved the same problem in a second software, AMPL. AMPL is a modeling language for linear programming with CPLEX as the solver. CPLEX uses the primal Simplex method. The objective value and solution given by AMPL is not identical to that given by Xpress Solver, but the difference in is within 0.05%. A possible reason for this difference lies in the LP solution methods applied by two softwares. Since Xpress Solver uses Dual Simplex Method while AMPL uses Primal Simplex Method, the objective value approaches the optimum from different directions, which might cause difference in objective value and solutions. Narrowing the tolerance range for optimal solutions might ease this issue. However, the cost of low tolerance is more iterations in solutions, which implies possibly longer solution time. For a business-planning tool, shorter solution time is more valuable than a very precise solution. 3.5 Checking Feasibility ALOT is a linear program with 48,000 decision variables and 16,365 constraints, excluding non-negativity. Conflicts in the constraints might cause non-existence of the feasible region. For instance, the minimum production constraint might require an output level exceeding the production capacity, or a SKU that has positive demand at a certain DC might not be able to obtain the supply from any of the co-packers, either directly or via other DCs. Mistake in data input might also cause infeasibility. Preferably, ALOT should conduct a feasibility check before solving the problem, ensuring that all input data are valid and selected constraints do not conflict with each other. By doing so, MAG planners could make necessary revision to the input data and select constraints to avoid infeasibility before starting to solve the problem. It is too complex to check the 16,365 constraints one by one. Moreover, the combination of constraints and the input data might vary before each run. The feasibility check covers a majority of constraints in the model, and reveals the most frequently occurring problems that cause infeasibility. Satisfying the checks are necessary conditions for reaching feasible solutions, but not sufficient. The feasibility check of ALOT includes five steps: (1) Co-packer to DC Transportation connections: Due to transportation regulations, some co-packers are not able to ship any products to certain DCs, neither directly nor via other DCs by 20 transshipment. We call these co-packer and DC pairs "unconnected". Otherwise, they are "connected". This step is formulated as b'jk = indicator of connection between co-packer j and DC k 1 if z'jk = 00 or if there exist at least one k' such that Zjk- = 00 and z\•* = 00 where b'ik = 0 otherwise The condition z'jk = 00 implies DC k is able to obtain supply from co-packer j. If DC k could get supply from co-packer j by transshipping via other DC(s), it requires that there exist at least one k' such that z'jk' - 00 and zr\ck - °°- Hence, b'jk = 0 indicates the corresponding co-packer and DC are unconnected. Identifying those unconnected co-packer to DC pairs is valuable for the planner to understand the system, and the value of b'jk is an input to step II of feasibility check. (2) SKU supply to DCs from connected co-packers: This step examines whether a DC could obtain a certain SKU from one of the connected co-packers when there is positive demand of the SKU at that DC. If not, there is no feasible solution to this problem. To perform this check, we first define dik, the indicator of positive forecast demand of SKU i at DC k f 1 if fu:, > 0 for any t where dik = -s L 0 if otherwise and eik, the indicator of supply of SKU / at DC k 1 if there exist at lease one j such that z^- = °° and b'jk = 1 where eik •• 0 if otherwise The positive demand indicator dik= 1 implies positive demand of SKU i at DC k, while eik = 1 means DC k could obtain supply of SKU i from at least one co-packer. The LP problem is infeasible if there exist an (i,k) pair such that b2ik = 0 r 1 if ejk >dik \/i,k where b2jk - ~\ 0 if otherwise 21 A SKU is produced at a co-packer if and only if the production line it belongs to has positive capacity, AND the corresponding zero production parameter has a non-zero value. Then this co-packer level SKU mix is transferred to DC level using the result of step one of feasibility check, co-packer to DC transportation connection. A particular SKU is available at a particular DC only if the DC could get the supply of this SKU from connected co-packer(s). Comparing the availability of SKUs at each DC with the demand of SKUs at DC, ALOT could identify the SKUs which have no source of supply, and reports this possible infeasibility to the users. In case of infeasibility found in this step, users should ensure that the information for the corresponding SKU, co-packer and DC has been correctly entered in the input tables of production costs, co-packer to DC transportation costs, inter-DC transshipment costs, production line capacity, and forecasted demand. If no data entry error is present, the supply problem does exist and either it must be fixed or the transportation or transshipment rules constraints must be released before a feasible solution can be found. (3) Production line capacity versus minimum monthly production: This step examines whether minimum monthly production at each co-packer exceeds the aggregate capacity of all production lines at this co-packer, which is a potential source of infeasibility. This step is performed by checking the value of bJjh indictor of production line capacity vs. minimum production where b3j, •• Hi) 0 if otherwise If there exist (j,t) such that b3j, = 0, the total production capacity at co-packer j is less than the minimum production of this co-packer in month t, which implies infeasibility of the LP problem. If users confirm that the data of production line capacity and minimum production requirement are correctly entered, then one of these two constraints must be released to find a feasible solution. (4) Cumulative demand versus available supply: If, in particular sets of periods starting from t = 1, the total supply capacity of a certain production line across all co-packers could not meet or exceed the total cumulative forecasted demand of the group of SKUs produced in that line, there 22 will be no feasible solution. We define b4s(i)j as indictor of total cumulative demand vs. available supply 1 lf XlXa,, + I',*,o * S £/* Vs#,T = 1,2,..,12 1=1 | ies(i),k '=1 i'e.v(i').* where Z?4(W,7 0 if otherwise To perform this check, ALOT first adds the initial inventory, according to the production line it belongs to, to the cumulative capacity of the line from period 1 to period T, where T could be any integer from one to twelve. Meanwhile, ALOT calculates the total cumulative forecast relating to each production line for the same time length. This comparison is carried out for each T, which runs from 1 to 12. Whenever the cumulative demand exceeds the sum of cumulative capacity and initial inventory, i.e., there exist (s(i), T) such that b4s(i)J= 0, it implies infeasibility. Figure 6 shows the feasibility check for production line 24, where all the SKUs of 22 oz and 24 oz are produced. We found the total available supply is greater than the cumulative demand for all time lengths. Therefore, the demand-supply relation at line 24 will not cause infeasibility. D Demand • Supply T=1 T = 1 T = 1 T = 1 T = 1 T=1 T-1 T = 1 T=1 T=1 T=1 T = 1 to 1 to 2 to 3 to 4 to 5 to 6 to 7 to 8 to 9 to 10 to 11 to 12 Figure 6. Cumulative demand vs. available supply In the case of infeasibility in this step, users should ensure that information for the appropriate production lines and SKUs are entered correctly in the input tables of forecasted demand, production line capacity and initial inventory. If no data entry error is present, the problem of 23 insufficient supply does exist. Either it must be fixed, or the production capacity constraints must be released before a feasible solution can be found. (5) Carrying one month of inventory: Due to possibly insufficient initial inventory, ALOT might not be able to carry one month worth of inventory starting from the end of period 1. ALOT provides options for the users to start implementing the one month inventory policy from period 1, 2, 3 or not at all. However, it is helpful for the users to know whether it is feasible to carry one-month worth of inventory given the production capacity, initial inventory and forecast. We define b5S(i).T as indicator of feasibility of carrying one month of inventory T T 2J2J^S(/),;,I + Zi^i,k,0~ Z, Zif'kt— y. fi.k.i+l r where b mJ= 1 if (=1 j ies(i),k Vs(/),r = l,2,..,12 0 if otherwise r=l ies'i),k ie.v(i),* In case the feasibility check shows, from period one to a certain period T, the cumulative capacity plus initial inventory is unable to surpass the cumulative forecast by the amount of forecast of T+1, i.e., there exist (s(i), T) such that b5s(i)j= 0, the users should not implement the inventory policy starting earlier than T since ALOT does not have a feasible solution. In the spreadsheet of feasibility check in ALOT, infeasibility is shown by red cells in the tables at corresponding steps. When the users see the red cells, they should re-check the input data or relax relevant constraints in order to have a feasible solution. It is recommended that users run feasibility check each time before solving for optimal plans. 4. RESULTS - APPLICATION OF A LOT 4.1 An Overview of ALOT ALOT is a planning tool with a user-friendly interface. Users can load input data, read output data, choose model constraints and solve the model conveniently from the main menu. All data tables can be accessed from buttons on the main menu. Checkboxes on the main menu enable the users to test different combinations of constraints, to examine various operational scenarios. A user manual was integrated into ALOT, explaining how to use the tool, including cautions to be taken at each step, and the logic behind the tool. Printing options are supplied for all output data tables. ALOT also allows for future expansion of MAG business in the US market. For instance, besides all of the "Mike's" SKUs currently supplying to the US market, ten "dummy" SKUs are included in the model, which can be turned into new SKUs in the future if necessary. Finally, ALOT provides the options to close and open co-packers and DCs by varying the co-packer and DC capacity. 4.2 Implementation as an Operational Tool ALOT was developed as an operational tool, supporting MAG's regular business decision making process in production, transportation and inventory. ALOT solves the multi-period model by using a rolling horizon approach. The model is solved at one point in each month, for a planning horizon of the coming 12 months. As one period elapses and better data and forecasts become available, the model is slid forward one period. The month t = 2 becomes t = 1, etc., and the whole process is repeated. It is worth emphasizing the end effects in ALOT. Particularly, any cost minimizing, finite-horizon, production-inventory model will always have zero inventory at the end of the planning horizon. The policy of keeping safety stock may mitigate the end effect to some extent, but we still do not trust ALOT's results in the later time periods. The recommendations of the solution for the coming period, or at most the coming two to three periods, are realistic, and may be the basis for operations decision-making. To effectively employ ALOT in the MAG regular business operation, we determined the monthly schedule of implementing ALOT. Figure 7 shows the proposed schedule. 25 20th Month T-1 i 5th Month T 6 -9th 10th Provide forecasted demand for months T through T+12 r . . \ / Estimate closing inventory at the end of month t, using \^ Opening inventory of month t, Production schedule of month t, and Actual order from distributors and DC Monthly input data check-list, including Unit pro duction co st Unit transportationandtransshipmentcost Unit inventory cost Production line capacity DC inventory capacity Minimum production Fixed production byproduction and distribution personnel. Document the / \ \ changes to the data. j^Run the tool with updated data. Review the output and ^ re-run if necessary. Set production and distribution plan for month H-l and document the changes of actual plan from tool output, as well as the reasons for these ^ changes. Provide co-packers with production plan for month t+1 Figure 7. Monthly Operational Planning with ALOT Each step in the schedule is the responsibility of specific persons in the information, production planning and distribution departments at MAG. Most of the input data, including the cost structure and capacity information, are available and fixed by the contracts with the co-packers and DCs. Forecasts are updated each month. Estimating initial inventory needs extra work as 26 ALOT runs on 6-9* of each month while the ending inventory of that month is required as input data. The ending inventory is projected from opening inventory at that month, the production schedule of that month and the orders from distributors and DCs for the month, which are available by the time ALOT is run. ALOT solves for an optimal plan by minimizing the total logistics cost. While determining the actual production, transportation and inventory plan, MAG planners need to consider other factors besides the cost. Factors such as long term marketing strategy and supply chain partner relationship have to be taken into account. Therefore, the planners need to make necessary changes to the solution given by ALOT. The planners document these changes, together with the reasons of these changes, as a file for future reference. 4.3 Implementation as a Tactical Tool Although ALOT was intended primarily for operational planning, its first application was for tactical planning. During the development of ALOT, one of the co-packers ran into financial difficulty and was shut down with a short notice. The co-packer was one of the production facilities with all three production lines. Its shutting down had a large impact on MAG's summer marketing plan. MAG faced with an option either to support this co-packer by investing in it or letting it go and shift the production orders to other co-packers. The project team used the prototype of the tool and analyzed scenarios of the two options. The analysis showed that the optimal logistic plan with or without this co-packer makes negligible difference in the total cost in coming 12 months. Finally, MAG decided not to invest in this plant, based on analyses including those done by ALOT. During the planning process, decision makers always need to test the result of business options. Running those scenarios with planning tools like ALOT could help the planners see the consequence of their decision prior to actually implementing them. Here are two applications of ALOT as a tactical planning tool: (1) Transportation alternatives: MAG uses truck as the major mean of transportation in its logistic system. The distribution team was considering the possibility of using alternative modes of transportation. One of the options is inter-modal. MAG distribution personnel collected price quotations of unit transportation cost for the inter-modal shipment. To test the alternative 27 transportation tool, planners can simply replace the truck transportation and transshipment unit cost in ALOT with the inter-modal unit cost and re-run the tool. Comparing the results from the two runs helps evaluating the performance of inter-modal transportation. One issue associated with inter-modal is transportation time. Inter-modal might take longer than trucking, as trucks leave the plants immediately after a full-truck-load is produced and deliver products to the door of DCs. However, as long as the standard planning time unit in ALOT is month, one or two days of extra transportation time won't affect the validity of the solutions. (2) Capacity planning: In July of each year, MAG holds annual co-packing contract negotiations with co-packers. The contracts guide the production of "Mike's" beverage for the coming 12 months. Terms of the contract include production price, production line capacity assigned to MAG, and probably the minimum production target. Before going for the negotiations, MAG planners evaluate the potential co-packers and choose the cost efficient production resources. ALOT played an integral role in the negotiations in 2002 for 2003. It solved numerous scenarios of production plans, helping the planners negotiate for production capacity and cost. Once the unit production cost at a co-packer is fixed, MAG should determine the appropriate capacity to buy from the co-packer. Intuitively, the lower the unit co-packing price is offered, the more capacity MAG shall buy from this co-packer. However, the relation of unit co-packing price and capacity at a certain co-packer might not be linear, because the most cost efficient capacity at a co-packer is determined not only by its own price, but also by the price and capacity at other co-packers. Therefore, these capacity decisions are inter-related, and have impact on the overall production plan in the whole market. ALOT quantifies these impacts, and recommends the cost efficient solutions. The following is one set of the scenarios ALOT ran before the negotiation. High Falls is among the largest co-packers of "Mike's" products. The current co-packing price at High Falls was determined last year. ALOT ran scenarios of varying unit prices at High Falls and recommended the optimal capacity to buy for 2003. Three of the scenarios are: Scenario 1: Use the cost at High Falls and all other business units in June 2002. Scenario 2: Reduce the unit co-packing price of all SKUs at High Falls by $0.45/case and keep all other inputs unchanged. 28 Scenario 3: Increase the unit co-packing price of all SKUs at High Falls by $0.15/case and keep all other inputs unchanged. Figure 7 shows the annual production plan of one of the most popular SKUs, Lemonade regular 6-packs (L6R). • Scenario 1 LKP MNS HFL GSV BRK GWB CRL FLR CTY IND Figure 8. Production plan of L6R by varying co-packing price at High Falls The total logistics cost of Scenario 2 is about 1.1 million less than Scenario 1, and Scenario 3 has 0.12 million more in total cost than Scenario 1. Total production of this SKU is exactly the same among three scenarios. As shown in Figure 7, if MAG could negotiate a reduced unit co-packing price at High Falls (HFL) of 45 cents, MAG only needs four co-packers to supply the US market that was previously supplied by six. Lake Port (LKP) and Brick (BRK) will no long produce L6R and MAG shall give High Falls production order of L6R 17.5 times as much as in the previous year. If, as described in Scenario 3, the unit co-packing price at High Falls is 15 cents higher than the current price, MAG shall leave High Falls and give the production order to Brick. Figure 7 also shows how the price variation at High Falls affects capacity planning with other co-packers. As the price increases by 15 cents at High Falls, MAG shall take away 13% of production order from Lake Port and give it to Brick. This is a little counter-intuitive since the unit production cost at Lake Port is slightly lower than that at Brick, and the co-packer with lower cost shall get more 29 production order. What's happened here is as the production cost reduces at High Falls for all SKUs, Lake Port becomes relatively more efficient in producing other SKUs than L6R. Hence the capacity at Lake Port is assigned to other SKUs, and L6R is produced at its second "best" plant, Brick. This is a more efficient production schedule for the whole system. Other tactical analysis includes varying forecast accuracy by manipulating forecast safety factor, closing and opening plants and DCs by changing capacity. With ALOT, MAG has a strong support in decision making for internal operation as well as for external business activities such as negotiations. 30 5. CONCLUSION In this thesis, we have set up a linear programming model to optimize the logistics system of Mark Anthony Group for its "Mike's" products in the US market. Based on the LP model, we developed a computer based planning tool, ALOT, which solves for optimal production, distribution and inventory plan by minimizing the total logistics cost. ALOT is used both as an operational planning tool and a tactical planning tool. It helps the MAG planners make business decisions such as capacity planning and investment by running "what if scenarios. MAG started implementing ALOT in their daily work in September 2002. As stated by MAG vice president Carol Leacy, ALOT has changed the way MAG does business planning. With the quantitative support of ALOT, the production plan of 2003 is expected to be quite different from that of 2002, expecting to be more rational and cost efficient. There are possibilities to expand ALOT. We discuss two aspects here as directions of future research, improving forecast quality and optimizing DC to region transportation. As a logistics optimization model, the quality of performance of ALOT relies heavily on the quality of forecasts. Large savings in logistics costs can result from improved forecasting. To this end, we could turn our attention towards enhancing forecast quality as an expansion of this project. Potential topics are systematizing forecast updating, time series analysis on historical data, studying forecast error, and building appropriate forecast model for MAG business. ALOT models the transportation from co-packers to DCs and transshipment between DCs, but it does not cover the transportation from DCs to distributors. There are 450 distributors of "Mike's" products in year 2001 and the number is expected to grow in 2002. If ALOT was to be expanded to include DC to distributor transportation, the computation work would increase drastically. This is one of the reasons behind the choice of co-packer and DC levels in the project scope. However, since the shipping cost from DCs to distributors is also paid by MAG, further research shall be conducted to optimize this part of business. One problem that could be addressed is the region to DC assignment. In ALOT, this assignment is predetermined according to transportation time and unit transportation cost. We could solve for the optimal assigning plan by applying a Mixed Integer Program (MIP). The decision variables of this MIP problem will be integer variables indicating the assignment of regions to DCs, and its 31 objective function will be minimizing the total transportation cost from DCs to regions. Transportation volume from DCs to regions will be taken into consideration when the objective function is formulated. After the optimal assignment is solved, the forecasted demand seen at DCs may vary accordingly since it is calculated based on region to DC assignment. Thus, variation in region to DC assignment might affect the optimal solution of ALOT, which could be tested by running scenarios of different assigning plan. A second important problem relating to DC to region transportation is a routing problem. As explained in section I, orders from distributors are placed to DCs and MAG sends the products in trucks. Normally, a single distributor won't order as much as a full truckload. Consequently, MAG loads a truck with orders from several distributors and delivers them one by one, within the time limit of order time. This is a scheduling problem minimizing the cost of delivery subject to the order delivery time and matching the order volume with truckload, by solving for an optimal truck loading and routing plan. Integer programming is expected to apply in this model. This model, once set up, could be utilized together with ALOT to optimize the process from production to delivery in a heuristic manner. 32 REFERENCES Arrow, Harris, K. J., and Marschak J.T. 1951, Optimal Inventory Policy, Econometrica 19:250-272. ' ! Ball, M.O. et al 1995, Network Models, Elsevier Science B.V., Netherlands. Bazaraa, M.S and Jarvis, JJ. 1977, Linear Programming and Network Flows, John Wiley & Sons, New York. .» Brown, G. et al 2001, The Kellogg Company Optimizes Production, Inventory, and Distribution, Interfaces, 31:6 Nov.-Dec. 2001, 1-15. Evans, J.R. 1977, Some Network Flow Models and Heuristics for Multiproduct Production and Inventory Planning, AIIE Transactions, 9, 75-81. Johnson, L.A. and Montgomery, D.C. 1974, Operations Research in Production Planning, Scheduling and Inventory Control, John Wiley & Sons, New York. Phillips, D.T. and Garcia-Diaz, A. 1981 ^Fundamentals of Network Analysis, Prentice Hall, Englewood Cliffs, New Jersey. Veinott, A.F. Jr. 1965, Optimal Policy in. a Dynamic, Single Product, Nonstationary Inventory Model with Several Demand Classes. Operations Res. 13, 5 (September-October 1965), 761-778. 33 APPENDIX - ALOT User Manual Aggregate Logistic Optimization Tool User Manual Table of Contents I. Purchase and Install the Solver II. Model Working Process III. Data Input Tables Production Costs Co-packer to DC Transportation Costs Inventory Costs Inter-DC Transshipment Costs Production Line Capacity & Co-packer Reliability Factor DC Inventory Capacity Minimum Monthly Production Forecasted Demand & Forecast Safety Factor Initial Inventory Fixed Production IV. Feasibility Check V. Model & Model Maintenance Model Layout Function of Tables Introducing New SKUs Into the Model Opening or Closing a Co-packer/DC Varying the Units of Time Periods VI. Constraints Inventory Balance SKU Mix at Co-packers Co-packer to DC Transportation Rules Inter-DC Transshipment Rules Limited Production Line Capacity Limited DC Inventory Capacity Enforced Minimum Monthly Production Fixed Production Levels Carry One Month of Inventory Starting From Period... VII. Infeasibility VIII. Output Data Tables Production Plan Distribution Plan Inventory Plan Main Menu Cost Summary The Aggregate Logistic Optimization Tool (ALOT) was developed by the Centre for Operations Excellence in the Faculty of Commerce at the University of British Columbia, for The Mark Anthony Group's Mike's Hard products in the US Market Aggregate Logistic Optimization Tool User Manual I. Purchase and Install the Solver * This tool was developed in Microsoft Excel. It uses the Excel add-in XPRESS LP/MIP Solver (XS). XS requires both the XPRESS LP/MIP Solver Engine (XSE) and the Premium Solver Platform (PSP) to be installed. XSE and PSP are developed by Frontline Systems in the US. For purchasing and installing information, visit httpffwww snlvpr rnm. After installing the software, follow the steps below to introduce the solver to the tool and complete the installation. Step I. Open a new Excel file. Step II. Click Tools in the menu bar, and then Solver.... The Solver Parameter dialogue box will appear. Step III. In the mid-right of the dialogue box, available solver engines are shown in the drop-down selection list. If the XPRESS LP/MIP Solver engine is properly installed, you will see the option XPRESS LP/MIP Solver in the list. Locate the position of the solver in the list of engines by counting from the top. In the example above, the XPRESS LP/MIP Solver is found in position number six. Step IV. Close the blank Excel file and open the tool file (alot.xls). From the Main Menu, click the Go to Model button. Immediately below the Control Panel on the model page, you will see the Solver Engine Location box. Enter the number recorded in Step III into this box. Model Control Panel Solver Engine Location Note that the above operations only need to be performed once, after the Solver engine and platform are installed or re-installed. There is no need to reconfirm the solver engine location after It has been set. 35 II. Model Working Process Aggregate Logistic Optimization Tool fat Mark Anthony Group Mike's Products ul USA Market Input DatJ ^^^^^^^^ '"• "l • • e^-V • i J p-Hu-'.fi-jf- jffistw I. Ct-pa** Wato*.- :i tor 1 i i ;r v 'cifv ...! : J M., n.jn MontM • F r-.j..--,.,.. tV'Sc-as^d Dofrrat J J 1 oi«k sl-iii. KKLW ;: [nwal lr rarctco . j F .:>":i J in "ill - Solve - Read OutTK.-1* \ 1 1 CVr-^jcrjcr Wan 1 !•• '«'.h>.t jr. Han 1 "n 'PI JSJ -< ^ iii. r ss i i . i r:' Clin, for Operations Exc-arm Fp.i«ibdltr Check * imu« i-. ^ ti mrimei • P% si M-oscK.-. ;oiwrt» so K; vmtatttttn MM v Li'.tr-: trarmhptMnt •jtV r..,l •"il.-h BintS .unltRtilVndtit.liiii UheGsfiaaty jrr.s«d D\ In-rsmirv Capaclty :r." I li.i...jm fi.:.c.j IKI-. •i ii-: ..V L-i el The main menu screen above will appear upon opening the tool file. The main menu has three sections: Load Input Data, Read Output Data, and Solve. To use this tool, enter the input data, choose the solving options, solve the model, and finally, read the output data. The Load Input Data menu is used to update all the required data inputs to the model. It contains buttons which link to input data tables. The Solve menu includes the constraints for the model. Before solving the model, first always press the Feasibility Check button to check that the model is feasible for the given input data. According to the results of the feasibility check, you can make changes to the constraints and/or input data (see section IV below: Five-Step Feasibility Check), and press the Solve button. It takes about 7 minutes to solve the model on a computer with 1.4 GHz processor and 512MB RAM. Note that the Feasibility Check only minimizes chances of the model being infeasible; positive results do not guarantee that a solution exists. After solving, the tool will return either "Optimal solution found. Would you like to save this scenario?", or "No feasible solution. Please check input data or release constraints." Optimal solutions can be saved. In the case that no feasible solution exists, follow the tips in section Vll: Infeasibility. ' After an optimal solution is found, the optimal production, transportation and inventory plans, as well as the cost summary can be read by pressing the corresponding button under Read Output Data. ' The User Manual and Go to Model buttons are placed on the main menu for the user's convenience. The instructions are also accessible from all input and output spreadsheets. There is no need to access the model page except when entering the solver engine location, introducing new SKUs or otherwise altering the model. III. Data Input Tables * For all data input tables: For cells where information is unavailable, enter "0" or leave the cell blank. When changing input data, always retype or copy and paste the information. Do not use cell manipulation such ; or using formulas. i dragging cells Production Costs smm&ne * To carry out production of a certain SKU at a certain co-packer, ensure both of the following: A. The corresponding cell in the Production Costs table is positive (in the model, zero cost causes zero production). B. The corresponding cell in the Production Line Capacity table is positive. (6 and 12 packs of all flavors are produced at on the "6&12" line, 16 oz of all flavors on the "16" line, and 22 and 24 oz packs on the "24" line.) Example: To produce Lemon 6-pack non-refundable at Brick in period 1, both the cost of L6N at BRK from the Production Costs table and the production line capacity of the "6&12" line at BRK from the Production Line Capacity table must be positive. If either of these two cells are zero, it will lead to zero production of L6N at Brick. 36 Co-packer to DC Transportation Costs see the tatus * To be able to transport products from a co-packer to a DC, ensure that the corresponding cell in the Co-packer to DC Transportation Costs table is positive. Zero cost causes zero transportation. Example: To transport from co-packer High Falls to DC Commercial, the cost from HFL to COMM in the Co-packer to DC Transportation Costs table must be positive. Co-packer to DC Transportation Costs Cost of transportation (per case, in TJS$) from a co-packer to a DC $0.82 10.06 $0 88 $1.49 $083 $0.75 $0.85 $0.28 $1.13 ton-; $2.17 $0 35 $1.08 $0.75 $1.26 $1.71 /fl 41 "Si. 92 $1.50 $2.06 $0.70 $1 82 $1 89 «4o 07 $1 89 $1.48 $1 98 $0 026 $0 00 $0 00 | $000 $0 00 $0.00 $0.00 Inventory Costs see the tanie * In this table, enter the unit freight in, freight out and holding costs for each DC. Here, zero cost does not cause zero inventory capacity, as the inventory capacity is entered in a separate data input sheet (DC Inventory Capacity). Inventory Costs Inventory costs (per case, in US$) at each DC Note: holding costs are monthly Inter-DC Transshipment Costs see the taNe * To make inter-DC transshipments, ensure that the corresponding cell in the Inter-DC Transshipment Costs table is positive. Zero unit cost causes zero transshipment. Example: To allow transshipment from Monroe to All Star, the MONR to ALST cell in the Inter-DC Transshipment Costs table must be positive. Inter-DC Transshipment Costs Cost of transshipment (per case, in US$) from one DC to another UJO.FJ DAJT $000 $1 13 $1.71 1 $000 UJO.FJ uom. $0.79 $0.00 $1.20 tp. 14 y.oo UJO.FJ COMM $1.10 $0.99 $0.00 PS*— •^000 UJO.FJ $149 $2.17 $1 92 $0.00 $000 UJO.FJ ITWCA $0 82 $0.28 $1 26 $1.82 $0.00 37 Production Line Capacity & Co-packer Reliability Factor • Both 6-packs and 12-packs are produced on the same line. A case capacity of 250,000 for a 6&12 line is for the total of all flavours for that co-packer in that period. * 16 02 products are produced on their own line. A case capacity of 125,000 for a 16 oz line is for the total of all flavours for that co-packer in that period. • 24 oz products are produced on their own line. A case capacity of 125,000 for a 24 oz line is for the total of all flavours for that co-packer in that period. • The co-packer reliability factor is a multiplier of the production line capacity to reflect the reality that co-packers may not be 100% reliable. It is the ratio of actual production capacity to the purchased capacity. The production line capacity input to the model is adjusted with this co-packer reliability factor. This factor applies to all production lines at a particular co-packer. Example: Purchased production capacity for the 6&12 line at Lakeport in period 1 is 250,000, as given by the table below. If the reliability factor for Lakeport is 0.95, the actual production capacity used by the model is 250,000 x 0.95 - 237,500 since Lakeport is only 95% reliable. * The co-packer reliability factor is always less than or equal to one, and greater than zero. Production Line Capacity & Co-packer Reliability Factor Ideal maximum production levels (in cases) on each line at each co-packer • '' FLR 230,000 1 o 630,000 200,000 130.000 2C0.O00 o 300,000 I a 0 0 100,000 0 70,000 0 24 0 o 0 0 0 0 0 0 250,000 0 650,000 20LI,IJIJU 150,000 200,000 II 300,000 7 id 0 0 0 0 100.000 0 70,000 0 .. 24 o 0 L: 0 0 0 0 0 250,000 0 630,000 200,000 130.000 200.000 0 300.000 T • : Ifi 0 0 0 0 100,000 0 70,000 0 24 0 0 0 0 0 0 0 0 Co-pae Ratio of actuot ker Reliability Factor production capacity to purchased products. • • osv - liPK. | . GWB ... .. CRL.. SLR if 093 ^1 1 00 1.00 1 00 1.00 1 100 : DU 1.00 DC Inventory Capacity * Shows the total inventory capacity in each time period at each DC. See the table Minimum Monthly Production See the table * Shows the minimum total production in each time period at each co-packer. Forecasted Demand & Forecast Safety Factor * The forecasts are entered for each of the next 12 months and are updated each month. When the forecast data is entered, the current period is period 0, T » 0, and the following period is T-1, T-2 ... Example: If months are used as the unit of period, and the model is run in April 2002, then in the forecast table, T = 1 shows the forecasted demand of May 2002 as projected in April, T-2 shows the forecast for June 2002 made in April, etc. If the model is run again in May 2002, T - 1 means the forecast for June 2002 made in May, etc... ' The forecast safety factor is a multiplier of the forecasts. It can be used as a planning tool to examine circumstances in which demand is higher than what would have been predicted. The factor only differs between time periods, and it applies to all products and all co-packers in a particular period. The actual demand level used by the model is the forecast multiplied by the safety factor for the corresponding period. Example: If the forecast for L12R at DART is 100,000 cases in period T - 1, but the forecast safety factor is set to 1.2 for L12R at DART, the forecast used by the model will be 100,000 x 1.2 - 120,000 cases. • The forecast safety factor is always greater than or equal to one. 38 Initial Inventory Sm the tahlR * This table shows the opening inventory for period T = 1. Example: If months are used as the unit of period and the model is run in April 2002, which is period T - 0 , the Initial Inventory table will give closing inventory at the end of April 2002, which is opening inventory at beginning of May 2002. Fixed Production See the table * This powerful scenario analysis table allows the user to set fixed production levels of certain SKUs at specific co-packers in any time period, and optimize production at other co-packers. Example: The user wants to determine the optimal production plan for all facilities, given a fixed production production at Lakeport in the first period. The given production plan for selected SKUs is entered into the table in the first time period at Lakeport. Cells with a value of 0 remain variable, and thus are optimized. Fixed Production T^*r I o ' • \ 86,23« Cm :• 4,320 C6T 0 CELcan : 'so, ctzr ••. Sffl L c%i pa. o C34nCMT ^ IV. Five-Step Feasibility Check * Due to constraints, or simply a data entry error, the model may be unable to find a feasible solution. In the case of infeasibility, the input data should be checked and/or some of the constraints should be released. The five-step feasibility check helps to find possible causes of infeasibility before actually solving the model. The user can then adjust the modei before solving, which minimizes the possibility of infeasibility, and saves time. " Red cells in steps II, III, IV and V indicate infeasibility, which must be dealt with before solving begins. Step I provides necessary information for the feasibility check. Tan cells in this step do not indicate infeasibility. * Note that the Feasibility Check does not guarantee the feasibility of a scenario: it only reveals the most likely causes of infeasibility. Step I. Checking the connection between co-packers and DCs. Due to rules for co-packer to DC transportation and inter DC transshipment, not all DCs are connected to all co-packers, either directly or via other DCs. Tan cells in this table show co-packers and DCs which are not connected. This step is for the user's information only. Step II. Matching the supply and demand ot SKUs at DCs. If, at any DC, there is positive demand of a certain SKU which can not be supplied from any of the co-packers connected to the DC, the model is infeasible. Red cells in this table show this type of infeasibility. Example: If the cell indexed by L12R and ALST is red after the feasibility check is finished, it means that there is a positive demand of Lemon 12-pack regular (L12R) at DC All Star, but All Star can not obtain supply of this SKU from any co-packer connected to it, either directly or transshipped via other DCs. 39 Step II. SKU Supply to DCs from Connected Co-packers This step examines whether each DC can be supplied with all required SKUs from oi DAM MOKE. COMM ALST NWCA LER LSN 1ST In case of red cells in this table, please ensure that the information for the corresponding SKU, co-packer and DC has been correctly entered in the tables below: 1) Production Costs: Zero production cost causes zero production. 2) Co-packer to DC Transportation Cost: Zero cost blocks transportation from co-packers to DCs. 3) Inter-DC Transshipment Costs: Zero cost blocks transshipment between corresponding DCs. 4) Production Line Capacity: Zero capacity causes zero production. 5) Forecasted Demand If no data entry error is present, the matching problem does exist and either it must be fixed or the Transportation or Transshipment constraints must be released before a feasible solution can be found. Step III. Checking whether the production capacity is less than the minimum production level. If this is the case, enforcing the production line capacity constraint and the minimum production level constraint simultaneously will cause the model to be infeasible. Red cells show at which co-packer and in which period this infeasibility occurs. St*p HL Production Line Capacity vs. Minimum Monthly Production This step checks whether the minimum production level is greater than the production line capacity If so, one of these constraints must be released to find a feasible solution Red cells show co-packers where the production capacity is less than the mimmui a production level ; , HFt OSS BRK OWB CRL ; FLR CTY WD T*1 T=2 T=3 T=4 Example: Here, the total production capacity at co-packer Minnesota is not high enough to meet the minimum production level in time period T . 1. If both Production Line Capacity and Minimum Monthly Production data are entered correctly, one of these two constraints must be released to find a feasible solution. Step IV. Checking the cumulated supply, demand and initial inventory levels in each production line. A feasible solution exists for the model if and only if the cumulated supply capacity meets or exceeds the cumulated demand in particular time durations. Specifically, in all durations extending from the first time period to any subsequent time period, for each production line, the initial inventory plus the cumulated capacity must be greater than the cumulated demand for all the SKUs produced in this line. Red cells in this table indicate that the capacity is not high enough to meet the cumulated demand for the corresponding time period, so the model is infeasible. Example: In the table below, the cell indexed by "T - 1 to 3" for production line 24 is red, meaning that the opening inventory of all 24 oz SKUs in period T-1 plus the sum of the capacities of all 24 oz SKUs in the first 3 periods is less than the total forecasts in the first 3 periods, and similarly for the first 4, first 5 and first 12 periods. 40 Line 24 ::: Mftflth Cumulated Forecast Initial Invsntory Pius T= 1 to 1 165,423 00 412,575,01 T=lto2 299,674.11 412,575.02 T=lto3 425,690.78 T = 1 to 4 512,43658 T-1*0-5 587,397,86 T = 1 to 6 656,049 42 692,575 06 T= 1 to 7 735,315 77 832,575 08 T = 1 to 8 B67.50B.2B 972,57509 T= 1 to 9 974.90B.2B 1,112,575 10 T= 1 to 10 1,106,974.28 1,252.575.11 T=lto 11 1,389,795.54 1,392,575,12 T= 1 to 12 1.538,464 77 In case of red cells in this step, ensure that information for the appropriate production lines and SKUs is entered correctly into the following tables: 1) Forecasted Demand: The demand may drastically increase in a certain period. In this example, the 12th period demand is much higher than the previous period's demand. 2) Production Line Capacity: Capacity may not be high enough for a feasible solution. In this example, we intentionally set the capacity in periods 3 through 5 to zero, which causes the infeasibility. 3) Initial Inventory: Infeasibility may result if there is not enough opening inventory. If no data entry error is present, the problem of insufficient supply does exist. Either it must be fixed, or the production line capacity constraint must be released before a feasible solution can be found. Step V. Examining the feasibility of carrying one month of inventory. This step compares, at each production line, the demand for the next period with the sum of the initial inventory and the cumulated production line capacity, minus the cumulated demand up to the current period. If the demand is greater than this sum, it is infeasible to carry one month of inventory in this period, even if the production is carried out to full capacity in all previous periods. Red cells show periods in which it is impossible to carry one month of inventory. Example: The cell indexed by Month T • 1 is red, showing that it is infeasible to carry one month's worth of inventory starting from the first month. In this case, the user may set the model constraint of "Carry one month of inventory starting from..." to "2", "3" or "No". Line &&12 T = 1 1,101,514.13 1,470,156 82 T = 2 1 881.357 91 1,181,619 08 T- 3 2,949,738 84 963,187 1 7 T = 4 4,236,65256 817,130 73 T = 5 5,669.422 93 750.936 68 T = 6 7,168,486 25 834,598 61 T = 7 8,583,887 64 1.377204 61 T = 8 9,456.683 03 850,975 00 T= 9 10,885,700 03 1,661,991.00 T = 10 11.443,717.03 2,415.739 84 T = 11 11,077,977.20 2,222,366 66 T = 12 10,905,610 64 0.00 In case of red cells in this step, ensure that information for the appropriate production lines is entered correctly into the following tables: 1) Forecasted Demand: If the demand increases too drastically in a certain period, it will be impossible to carry one month of inventory during the previous month. 2) Production Line Capacity: The line cannot produce enough to allow carrying one month of inventory. 3) Initial Inventory: There may be too little opening inventory to carry one month's worth of inventory. If no data entry error is present, the "Carry one month of inventory starting from period..." constraint must be set to a greater value than the red cell's period. If a red cell is at T = 3 or later, this constraint must be set to "No". Otherwise, the model will not find a feasible solution. V. Model and Model Maintenance Model Layout The model layout is presented here for the user's information. Since data input and output are performed using separate worksheets, there is typically no need to access this model page, except when entering the solver engine location immediately following installation of the software, or when altering the model (such as when introducing a new SKU). 41 The model page consists of four major parts: decision variables, model constraints, cost calculations and a control panel. See below for details. Decision Variable: Mode! Constraints Cost Calculation Control Panel 6) 7j 12) 9) 4) 3) 2) 1) 5) 11) 10) 8) 13) 14) Function of Tables Please note that all of these tables are accessible from the control panel. Production Plan 1) Monthly production total of all SKUs 2) Annual production for each SKU 3) Monthly production for each production line 4) Monthly production for each SKU at each co-packers Co-packer to DC Transportation Plan 5) Annual transportation total of all SKUs 6) Monthly transportation total of all SKUs 7) Monthly transportation for each SKU Inventory Plan 8) Annual/monthly inventory total of all SKUs 9) Monthly inventory for each SKU Inter-DC Transshipment Plan 10) Annual/monthly transshipment total of all SKUs 11) Monthly transshipment total of all SKUs 12) Monthly transshipment for each SKU Total Costs 13) Monthly Cost 14) Total Overall Cost Introducing New SKUs Into the Model ' This model allows for the addition of new SKUs into the US market. There are 10 "dummy" SKUs in the current model, which can be changed to new SKUs. See the following example for adding a new SKU: Example: User wants to introduce a new SKU, Mike's Hard Orange 6-pack Regular, into US market. To do this, the user replaces the dummy SKU "D1" in the current model with "R6R" (for Orange 6 Regular, or any other symbol). Step I. Change all occurrences of "D1" to "R6R" in the model (all sheets), using the Find and Replace function. Step II. On the Main Menu, press the Go to Model button. From the Control Panel, under Production Plan, go to the Monthly Production of each SKU at each Co-packer table. Here, find the row indexed "R6R", and enter the production line on which this new SKU is produced in the Line column. Example: If Orange 6-pack Regular is produced on a 6&12 production line, enter a "6" in the Line column. For details of production line assignment, please see the section above on the Production Line Capacity & Co-packer Reliability Factor data input table. Monthly production of each SKU at each co-packer Step III. Update the production cost, forecasted demand, initial inventory, and (if necessary) fixed production data input tables with the information for this new SKU. Opening or Closing a Co-packer/DC * To shut down a co-packer or close a DC, simply set the capacity of the corresponding co-packer or DC to zero. * To re-open a co-packer or a DC, follow the instructions in the Production Costs Data Input Table and Inventory Costs Data Input Table sections. Varying the units of time periods * The model's default time period unit is months. This can be varied to any length of time, as long as the input data relating to time periods -production line capacity and co-packer reliability (actors, DC capacity, minimum production, forecasts, initial inventory, and fixed production -is consistent with the chosen time period. (The time variable T that is used in the model can be arbitrarily interpreted. However, in a time period of less than two weeks, transportation time - which we have taken as negligible - becomes significant, and the model will become less accurate.) VI. Constraints • There are two types of constraints: basic constraints and optional constraints. It is strongly recommended that the basic constraints be held at all times, as they are essential for this model. The option to release basic constraints is provided for running extreme scenarios. Optional constraints may be released for the sake of scenario analysis, or in case of infeasibility. While running the model for operational purposes (e.g., calculating the monthly production and distribution plan), it is recommended that ail constraints be held. Inventory Balance ' This constraint is the "heart" of this model. It must be held at all times. It ensures that in each time period, at each DC and for each SKU, the opening inventory plus all incoming shipments (either from co-packers or from other DCs) must be equal to all outgoing shipments plus the closing inventory. SKU mix at Co-packers * This constraint represents the mix of SKUs produced in each co-packer. It is controlled by the Production Costs table. This constraint allows production of certain SKUs with a co-packer only if the corresponding cell in Production Costs table is positive. Zero cost implies zero production. * Releasing this constraint allows any SKU to be produced at any co-packer, subject only to each co-packer's production line capacity. Example: If this constraint is held, only L6R, L6N and L6L are produced out of all Lemon 6-pack SKUs at Lakeport. Note that the production costs of L6T and L6P are zero and the production capacity of the 6&12 line at Lakeport is positive. 43 Production Cost: Cost of prodjction (in USS) of cat U3fi L12T 0 00 Production Line Cap Ideal maximum production levels (in cases) c MNS 6* 12 £250,000"} 0 18 0 24 0 0 250,000 0 I 16 0 0 34 0 0 <j&\2 250,000 0 ta 0 0 24 0 0 r Solve — | Perform Feasibility Check Basic Constraints (Strongly recommended) Inventory Balance (mustbe hekl at ail times) :U Mix at Co-packers Co-packer to DC Transportation Rules Intw-DC Trarrsshtpment Rules If the constraint SKU Mix at Co-packers is released, then the Production Costs table has no effect on the SKU mix at co-packers, which then is controlled only by the Production Line Capacity. Since capacity is positive at line "6&12" at Lakeport, all Lemon 6 and 12 pack SKUs are allowed to be produced at Lakeport - including L6T and L6P, which can't be produced with this constraint enforced. However, Lakeport is still unable to produce 16 and 24 packs, since the production capacity for these lines is zero. Co-packer to DC Transportation Rules * This constraint represents the transportation channels from co-packers to DCs. It is controlled by the Co-packer to DC Transportation Costs table. This constraint allows transportation from a co-packer to a DC only if the corresponding cell in the Co-packer to DC Transportation Costs table is positive. Zero cost enforces zero transportation. • Releasing this constraint means that transportation is possible from any co-packer to any DC. Inter-DC Transshipment Rules ' This constraint represents the transshipment channels between DCs. It is controlled by the Inter-DC Transshipment Costs input table. This constraint allows transshipment between a pair of DCs only if the corresponding cell in the Inter-DC Transshipment Costs table is positive. Zero cost enforces zero transshipment. * Releasing the constraint means that transshipment is possible between any pair of DCs. Limited Production Line Capacity * This constraint requires the total monthly production of each production line to not exceed the capacity as given in the Production Line Capacity data input table. See the instructions for the Production Line Capacity table above for the assignment of SKUs to production lines. * Releasing this constraint means that production line capacities can be unlimited, according to the SKU mix at each co-packer determined by positive elements in the Production Costs table. Limited DC Inventory Capacity * This constraint forces the closing inventory in each period at each DC to be no more than the capacity specified in the DC Inventory Capacity input table. Releasing this constraint causes all DCs to have unlimited capacity. Enforced Minimum Monthly Production " This constraint ensures that production levels at each co-packer meet those specified in the Minimum Monthly Production data input table. * Releasing this constraint means production levels have no minumum. Fixed Production Levels * This constraint allows the model to optimize the logistic system while keeping production levels for certain SKUs, co-packers, and periods fixed. • This constraint is a very likely source of infeasibility. If a positive fixed production level of a certain SKU is given to a co-packer where the corresponding unit production cost is zero - i.e., production of this SKU is not allowed at this co-packer - the model is infeasible. Also, capacity issues become extremely complicated once production levels at particular co-packers are fixed. Steps 3 through 5 of the Five-Step Feasibility Check are rendered ineffective while this constraint is held. Although Fixed Production is a powerful tool for scenario analysis, users must be very careful enforcing it. If the model returns no feasible solution, this constraint should be the first to be released. * Leaving this constraint deselected causes all production levels to remain variable. 44 Carry One Month of Inventory Starting From Period... * Carrying one month of inventory requires the closing inventory of any period to be no less than the forecasted demand for the next period, for each SKU at each DC. However, if the initial inventory at a particular DC is low, it might be impossible to satisfy this constraint and this DC will not be able to carry one month's worth of inventory starting from the first period. The user can then instead not start carrying one month of inventory until the end of second or third period, or release this constraint. * Available options in this constraint are "1", "2", "3", and "No". The numeric options force the model to carry one month's worth of inventory, starting from the end of the specified period (the first, second or third). "No" means that DCs will not be forced to carry the following month's inventory at any point. * To avoid infeasibility, refer to the Feasibility Check sheet, Step V. If red cells appear in this table, it is not feasible to carry the next month's forecasted inventory at the end of that period, so a later period than that must be chosen for the constraint. If the red cell shows in a period later than T = 3, the only choice to get a feasible solution is "No". VII. Infeasibility * If, after solving the model, the tool returns the message "No feasible solution. Please check the input data or release constraints", it means that the model cannot find a solution which satisfies all given constraints and input data. . * In this case, first re-run the Feasibility Check. * If the Feasibility Check does not show any warnings, try each of the following steps in order. After each step, try re-solving the model, and if there is still no feasible solution, try the next step. 1) Release the Fixed Production constraint. 2) Release the Limited DC Inventory Capacity constraint. 3) Release the Minimum Monthly Production constraint. 4) Raise the value of the "Carry One Month of Inventory Starting From Period..." constraint to two, and then to three, and finally to "No", re-solving the model each time until a feasible solution is reached (if no solution is reached, proceed to step 5). 5) Release the Limited Production Line Capacity constraint. When a feasible solution is found, check the output data tables for red cells to see where the infeasibility originally occurred. If a feasible solution was found with steps 2 or 4, check the Inventory Plan table. If a feasible solution was found with step 5, check the Distribution Plan table. VIII. Output Data Tables * The output data given by these tables makes sense only if an optimal solution has been found. The output data given by an infeasible solution is meaningless. Production Plan sco ^ Pian * Returns the production plan for: 1) Monthly total production of all SKUs at each co-packer. 2) Annual production totals for each SKU at each co-packer. 3) Monthly production of each SKU at each co-packer. 4) Monthly production totals of each production line at each co-packer. * If a line is producing to its full capacity, its cell will turn brown for the corresponding time periods. * If production exceeds the line capacity the corresponding cell will turn red. This may happen in case of infeasibility or if the user has released the production line capacity constraint. Transportation Plan See the plan * Returns the transportation plan for 1) Annual transportation totals of all SKUs from co-packers to DCs 2) Monthly transportation totals of all SKUs from co-packers to DCs 3) Monthly transportation totals for each SKU from co-packers to DCs 4) Annual transshipment totals of all SKUs between DCs 5) Monthly transshipment totals of all SKUs between DCs 6) Monthly transshipment totals for each SKU between DCs Inventory Plan see the plan * Returns the inventory plan for 1) Annual and monthly inventory totals of all SKUs at each co-packer 2) Monthly inventory for each SKU at each co-packer * In the first of these inventory tables, if the closing inventory hits the DC inventory capacity, the corresponding cells will turn brown. If closing inventory exceeds the DC inventory capacity, the corresponding cell will turn red. This may happen in case of infeasibility or if the user has released the Limited DC Inventory Capacity constraint. Cost Summary See the plan " Returns: 1) Total cost over all periods 2) Monthly cost breakdowns for production, transportation, transshipment, and inventory costs (including freight in, freight out and holding costs) 45
- Library Home /
- Search Collections /
- Open Collections /
- Browse Collections /
- UBC Theses and Dissertations /
- Aggregate logistics optimization for a large beverage...
Open Collections
UBC Theses and Dissertations
Featured Collection
UBC Theses and Dissertations
Aggregate logistics optimization for a large beverage company Wu, Ernest Hongtu 2002-10-17
pdf
Page Metadata
Item Metadata
Title | Aggregate logistics optimization for a large beverage company |
Creator |
Wu, Ernest Hongtu |
Date Issued | 2002 |
Description | In 2002, The Centre for Operations Excellence (COE) at UBC Commerce carried out a logistics optimization project for Mark Anthony Group (MAG), a manufacturer and distributor of premium wine and alcoholic beverages. The goal of the project was to determine a production, distribution and inventory plan for MAG's "Mike's" products in the US market, that minimized the total logistics cost. It is a multi-commodity, multi-period network flow problem formulated as a linear program with 48,000 decision variables and over 16,000 constraints. Based on the LP model, COE developed a customized computer based planning tool, Aggregate Logistics Optimization Tool (ALOT). ALOT is designed to be an operational tool, providing recommendations on monthly production, distribution and inventory plans for MAG planners. Also, ALOT could be used as a tactical planning tool, supporting MAG's capacity planning, investment, transportation mode choice and other business decisions. ALOT has changed the way MAG does business planning. With ALOT being implemented by MAG, the production plan in 2003 is expected to be significantly different from that in 2002. |
Extent | 10170539 bytes |
Genre |
Thesis/Dissertation |
Type |
Text |
File Format | application/pdf |
Language | eng |
Date Available | 2009-10-17 |
Provider | Vancouver : University of British Columbia Library |
Rights | For non-commercial purposes only, such as research, private study and education. Additional conditions apply, see Terms of Use https://open.library.ubc.ca/terms_of_use. |
DOI | 10.14288/1.0090976 |
URI | http://hdl.handle.net/2429/13980 |
Degree |
Master of Science in Business - MScB |
Program |
Business Administration |
Affiliation |
Business, Sauder School of |
Degree Grantor | University of British Columbia |
Graduation Date | 2002-11 |
Campus |
UBCV |
Scholarly Level | Graduate |
Aggregated Source Repository | DSpace |
Download
- Media
- 831-ubc_2003-0075.pdf [ 9.7MB ]
- Metadata
- JSON: 831-1.0090976.json
- JSON-LD: 831-1.0090976-ld.json
- RDF/XML (Pretty): 831-1.0090976-rdf.xml
- RDF/JSON: 831-1.0090976-rdf.json
- Turtle: 831-1.0090976-turtle.txt
- N-Triples: 831-1.0090976-rdf-ntriples.txt
- Original Record: 831-1.0090976-source.json
- Full Text
- 831-1.0090976-fulltext.txt
- Citation
- 831-1.0090976.ris
Full Text
Cite
Citation Scheme:
Usage Statistics
Share
Embed
Customize your widget with the following options, then copy and paste the code below into the HTML
of your page to embed this item in your website.
<div id="ubcOpenCollectionsWidgetDisplay">
<script id="ubcOpenCollectionsWidget"
src="{[{embed.src}]}"
data-item="{[{embed.item}]}"
data-collection="{[{embed.collection}]}"
data-metadata="{[{embed.showMetadata}]}"
data-width="{[{embed.width}]}"
async >
</script>
</div>
Our image viewer uses the IIIF 2.0 standard.
To load this item in other compatible viewers, use this url:
http://iiif.library.ubc.ca/presentation/dsp.831.1-0090976/manifest