AN LP BASED DISTRIBUTION NETWORK PLANNING TOOL FOR A LARGE PULP AND PAPER COMPANY by MARIEL SOFIA LAVIERI BSc (Industrial and Systems Engineering) University of Florida 2002 BA (Statistics) University of Florida 2002 A THESIS SUBMITTED IN PARTIAL FULFILLMENT OF THE REQUIREMENTS FOR THE DEGREE OF MASTER OF SCIENCE IN BUSINESS ADMINISTRATION in THE FACULTY OF GRADUATE STUDIES SAUDER SCHOOL OF BUSINESS We accept this thesis as conforming to the require standard THE UNIVERSITY OF BRITISH COLUMBIA April, 2004 © Mariel Sofia Lavieri, 2004 Library Authorization In presenting this thesis in partial fulfillment 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. Name of Author (please print) Date April 23, 2004 Mariel Lavieri Title of Thesis: AN LP BASED DISTRIBUTION NETWORK PLANNING TOOL FOR A LARGE PULP AND PAPER COMPANY Degree: MASTER OF SCIENCE TN BUSINESS Year: 2004 ADMINISTRATION A B S T R A C T NorskeCanada, a large pulp and paper manufacturer, was striving to improve its distribution system and reduce lead times. Given the size and complexity of the network, it was not clear where the system bottlenecks were. It was agreed with the company that the project would be conducted in two phases. In Phase I, high level process maps were created in order to understand what factors affected distribution lead times. In addition, a comprehensive sample of lead-time data was analyzed. In Phase II, a high-level distribution optimization model was created to be used for strategic decisions. The purpose of this large-scale PC based linear programming model is to determine the amount of product to send on each route and transportation mode in order to minimize total distribution costs on a monthly basis. Its simplicity to use and expand, make it ideal for scenario and "What-If' analyses. TABLE OF CONTENTS ABSTRACT TABLE OF FIGURES ACKNOWLEDGEMENTS 1. INTRODUCTION 1.1 Project Background 1.2 Problem Definition 1.3 Project Overview 2. LITERATURE REVIEW 3. LEAD TIME DATA ANALYSIS 3.1 Methodology 3.2 Results 4. DISTRIBUTION NETWORK PLANNING TOOL 4.1 Linear Programming Model 4.1.1 Model Overview 4.1.2 The Model 4.1.3 Model Assumptions 4.1.4 Data Requirements 4.1.5 Model Outputs 4.2 Model Implementation 4.3 Results 5. SUMMARY REFERENCES APPENDICES Appendix 1: Process Maps Appendix 2: AMPL Formulation Appendix 3: Steps Involved in Adding Items to the Model Appendix 4: User Interface/Excel Formulation Appendix 5: Help File TABLE OF FIGURES Figure 1: Norske Canada Production Capacity Distribution 1 Figure 2: Norske Canada Markets 2 Figure 3: Units of Flow in the System 3 Figure 4: Structure of Data of Extended Study 7 Figure 5: Data Timeline 8 Figure 6: Calculation of Storage Days at Mill 8 Figure 7: Calculation of Mill to Warehouse Days 9 Figure 8: Calculation of Storage Days at Warehouse 9 Figure 9: Sample Graphs of Average Lead Time by Route 10 Figure 10: Sample Graph of Relative Distribution of Average Total Lead Time 10 Figure 11: Sample Box Plots of Average Storage Days at Mil l , Average Mil l to Warehouse Days, Average Storage Days at Warehouse, and Average Total Lead Time by Route 11 Figure 12: General Cost Components 17 Figure 13: Cost Components for those Routes in which the Destination is a Distribution Center - Make-to-Stock 17 Figure 14: Cycle Time Components 20 Figure 15: Sample Graph of Cost Components by Type 26 Figure 16: Process Map of Model Formulation and Validation 29 Figure 17: Model Dimensions 32 ACKNOWLEDGEMENTS I would like to express my gratitude to all of you who have made the completion of this thesis possible: • Professor Martin L. Puterman, the best supervisor you could ask for; • Professor Anrning Zahng, for taking the time to be part of my thesis committee as well as for all his great advise throughout this program; • Shuya Yin, who not only was my technical advisor, but also became a true friend; . • Mats Gerschman, for all his help and guidance; • Glen Darou, for his helpful cost and financial insights; • COE personnel, for all their help in this project; • My classmates, for their constant support and all the input offered along the way; • The University of British Columbia and the Centre for Operations Excellence (COE), for offering such a great program and for the financial assistance given; • The Natural Sciences and Engineering Research Council of Canada (NSERC), for the financial assistance given; • NorskeCanada, for providing this interesting project as well as for the financial support given. I would specially like to thank Scott McLean, Don Smith, Dale Thulin, Michael Smith, Ming Tang, Kevin Gaffney, John Meredith, Valeri Gladkikh, Dave Shipman, Melissa Mulcahy, Gary Carr, John Dumbovic, Barry Heselgrave, Martha Vincent, Maurice Baird, Audry Mitchell, Gail Brissette, Talib Jiwani, Pamella Tsai, Agatha Ding, Don O'Callaghan, Richard Kim, Gloria McNeil, Mike Hutchison, and all the personnel involved, for their key kelp in this project; • My parents, Luis and Sophie, my brother, Luis Felipe, my grandparents, and all my family and friends for their unconditional love and support. v I. INTRODUCTION 1.1 Project Background NorskeCanada is the third largest newsprint and ground wood specialty paper company in North America, as well as the largest telephone directory paper producer and one of the few producers of sawdust-based pulps in the world. Its products can be classified as market pulp, containerboard, newsprint and specialties (which include directory paper, coated and uncoated paper) (see Figure 1). Figure 1: Norske Canada Production Capacity Distribution The NorskeCanada of today has a rich history. Its first predecessor, British-Columbia-Forest-Products-Limited, a logging and sawmilling company, was formed in 1946. This company expanded into pulp and paper products in the early 1950s, and was purchased by Fletcher Challenge Limited of New Zealand in 1987. In 2000, Fletcher Challenge's pulp and paper assets were purchased by Norske Skog, a Norwegian paper company, and the name was changed to Norske-Skog-Canada-Limited. Four BC pulp and paper mills (Crofton, Elk Falls, Powell River and Port Alberni) of quite different backgrounds were brought together in August 2001, when Norske-Skog-Canada-Limited acquired Pacifica-Papers, a company formed in 1998 from paper assets previously owned by MacMillan Bloedel. The name was changed to NorskeCanada. 1 With a total capacity of 2.3 million tons per year, NorskeCanada has markets in North and South America, as well as Europe and the Pacific Rim (see Figure 2). Products are produced at one of the four mills (see Appendix 1). They can be stored at the mills, shipped to a distribution center (i.e. a warehouse) or Figure 2: NorskeCanada Markets transported directly to the customer. From the distribution center (DC), products may be shipped to another distribution center, back to the mills or directly to the customer. There are three main information systems in which all transactions for paper products are recorded: SAP, an ERP system which records all global transactions; MES, or Mill Execution System, which records all transactions that take place at the mills; and WMS, which records transactions that involve the warehouses or distribution centers. 1.2 Problem Definition NorskeCanada was striving to improve its distribution system. Given the size and complexity of the network, it was not clear where the system bottlenecks were. NorskeCanada turned to the Centre for Operations Excellence (COE) at the University of British Columbia (COE) to review the system's current processes and to develop a model that would enable them to analyze and evaluate the effect of possible changes that could potentially reduce distribution lead times and distribution costs. The model created would be the basis for strategic decisions in the future. For purposes of the study, it was decided that line item would be the basic unit of flow. Each line item contains multiple rolls of the same type, that is, rolls that have the same: • SKU (roll attributes/dimensions (diameter, weight, grade, etc.)) • Production site and time • S o u t h A m e r i c a • E u r o p e a n d O t h e r • P a c i f i c R i m • N o r t h A m e r i c a 2 • Route • Transportation mode • Delivery date An order could be composed of many line items. See Figure 3 for further details. By mill, transportation m o d e ^ a t e s \ L J r i e Item By type SKU Grade Order By dimensions By customer Figure 3 : Units of Flow in the System Lead time was initially defined as the time involved moving a line item from the mill's warehouse to the distribution center docks. However, in order to avoid sub-optimizing the system, it was later decided to include the storage time at the distribution centers as well as the transit time to aggregated customers (customers grouped by regions) in the lead time calculations. Therefore, the distribution lead time starts when the line item comes off the upender1 at the mill and is placed at the mill's warehouse (the line item is ready to be shipped) and ends when the line item arrives at the customer. 1.3 Project Overview The study was conducted in two phases. Phase I began with a review of the system's current processes. Since it was not clear what the system's bottlenecks were, process 1 Last step in production process in which finished rolls are rotated to a vertical position to facilitate movement into the warehouse. 3 maps were created in order to understand what factors affected distribution lead times. In addition, a comprehensive sample of lead time data for line items produced at the four different mills was analyzed in order to identify how the factors affected lead time. Based on the initial analysis, it was possible to: • Determine bottlenecks of the distribution system. • Tabulate and investigate factors influencing distribution lead times and their variability. • Identify opportunities for the distribution lead time and cost reduction. In Phase II, a strategic linear programming model was developed to determine the amount of product to send on each route and transportation mode in order to minimize total distribution costs on a monthly basis. The model was created in three stages. In Stage I, a pilot model of the distribution system was built. An extensive user interface was also developed at this stage. Using the interface, the pilot model created in Stage I was expanded in Stage II to include actual data. Scenario analysis was performed in Stage III. Based on the model developed, we were able to: • Make recommendations on how distribution costs and lead times could be reduced. • Evaluate the possible effects of system enhancements or modification of lead times and distribution costs. • Provide NorskeCanada with an interactive tool for strategic planning. To protect data confidentiality, scales of graphs are not displayed in this thesis. Methods and results will be displayed to illustrate the analysis undertaken. 4 2. LITERATURE REVIEW Operations research and distribution planning have been widely used in the forestry and the pulp and paper industry. According to Lonnstedt (1972), by the year the article was written, lumber and pulp industries were already among the top users of operations research techniques in Sweden. Naslung (1970) illustrates how the mixed-integer warehouse location problem can be applied to the pulp industry to choose which transportation ports and transportation vessels to use in a pulp industry in order to minimize costs. Emphasis is made on the difference between full and fractional shipments. However, this model does not account for transhipments. Epstein et al (1999) describe how different OR tools (such as simulation, linear and integer programming) have been applied to solve both operational problems and strategic problems (short and long term) in the forestry industry. It is interesting to note how the level of detail of the model varies according to the purpose. For instance, the operational model described (ASICAM), is a truck scheduling system that determines how to transport products on a daily basis. Information pertinent to each individual transportation unit is included. On the other hand, the long term strategic model depicted (MEDFOR), has been designed as a strategic planning tool with a horizon of 50 years. The main purpose of the model is to determine long term production and sale policies. Therefore, the data in this model is highly aggregated. Our model falls in between these two models: even though it has been designed to be used for strategic decisions, in order to accurately represent the network, monthly information is included. Various papers have been concerned with the integration of operational and strategic models. Using hierarchical planning, multilevel decision problems can be solved by formulating models to make decisions at each level. Chase et al [2001, Chapter 12] describe the hierarchical planning process with an emphasis on aggregate planning techniques. Dempster et al (1981), introduce various examples of hierarchical planning systems and how those systems can be evaluated. Van Roy (1989) focuses on a multilevel production and distribution problem faced by a petrochemical 5 company. In order to improve the company's transportation system, strategic and operational mixed integer programs (linked by a global decision model) are formulated. We have encountered several analogies between our model and the high-level production and distribution planning model developed by Van Roy. Both models are concerned with the minimization of costs based on various flow constraints. However, while our model does not account for fixed costs (which play a key roll in Van Roy's model), it assumes that products can flow back and forth in each of the arcs or routes, and that there are different product types, as well as make-to-stock demand. 6 3. LEAD TIME DATA ANALYSIS For production and distribution planning purposes, Norske Canada personnel enters assumed lead times into SAP (the global ERP system) by route and mode. However, those lead times are not based on actual data. Accurate lead time information was also needed for as-is analysis as well as to quantify improvements in the future. The main objective of the data analysis was to identify factors affecting distribution lead times in British Columbia and compare actual to assumed lead times to determine accuracy of assumed lead times. 3.1 Methodology Before extracting an extended data set, a pilot statistical analysis was performed on a random sample of 85 line items produced in the months of February and March 2003. The purpose of the preliminary study was to establish the structure of the data to be collected and the statistical analysis to be carried out. Furthermore, the tools to perform the analysis were selected based on their graphic capabilities and the size of the data: it was agreed that the analysis would be done in NCSS and Excel. With this study, we were able to determine possible factors affecting the real distribution lead time. In order to perform the extended study, 5 months worth of make-to-order data was collected (from January to May 2003) on a line item level. The structure of the data (based on the number of line items in each category for which data was collected) can be observed in Figure 4. Productl Product2 Product3 Product4 Grand Total DCl Mill 218 165 383 MJ12 394 490 884 Mil3 200 217 417 Mil4 79 29 108 DCl Total 473 418 684 217 1.792 DC2 Mil2 1* 1* 2* MJ13 4" 4* MiM 1.042 158 1,200 DC2 Total 1,043 159 4 1,206 Grand Total 1,516 418 843 221 2,998 * Excluded from analysis Figure 4: Structure of Data of Extended Study 7 Data was grouped by origin and destination, by month, by transport mode, and by product. There are three components of lead time: storage days at a mill, transportation times from mill to warehouse, and storage days at warehouse (see Figure 5). Total Lead Time r Storage Days Mill to Storage Days at Mill Warehouse Days at Warehouse A A A _ Line item is ready to be shipped t Transportation equipment leaves mill Goods receipt time FRT = equipment leaves Sylvan = roll / is unloaded t Goods issue time Truckload leaves Figure 5: Data Timeline In order to calculate storage days at mill for line item j , production date (time when the line item is ready to be shipped) was subtracted from mill shipping date (date in which transportation mode leaves the mill) and averaged over all rolls in the line item (see Figure 6). Hi (Mill Shipping Datey - Production Date^ ) Storage Days at Mill, = nj V line items j ; s.t. nj = Total number of rolls in line item j Figure 6: Calculation of Storage Days at Mill 8 A similar procedure was performed to calculate mill to warehouse days for line item j (see Figure 7) and storage days at warehouse for line item j (see Figure 8). £j (Warehouse Goods Receipt Dateij - Mill Shipping Datejj) Mill to Warehouse Days; = n V line items j ; s.t. nj = Total number of rolls in line item j Figure 7: Calculation of Mi l l to Warehouse Days i = 1.. .nj Storage Days E; (Goods Issue Date^ - Warehouse Goods Receipt Date )^ at Warehousej = V line items j ; s.t. nj = Total number of rolls in line item j Figure 8: Calculation of Storage Days at Warehouse Total lead time was therefore assumed to be the sum of mill storage days, mill to warehouse days and storage days at warehouse. Warehouse to customer data was not included in this phase of the study since it was not available . In the next phase, for model creation purposes, warehouse to customer lead times were approximated by the transportation unit coordinators. After the data had been gathered, the first step in performing the extended statistical analysis was to determine the overall distribution of real lead times for each route. Histograms were created for all possible mill-warehouse combinations. In addition, descriptive statistics (mean, median, standard deviation, range, minimum and 2 Note that in the flow of information process map (Appendix 1) that no information is kept in the system regarding when products are delivered to the customer. 9 I maximum) of storage days at mill, mill to warehouse days, storage days at warehouse and total lead time were calculated and compared across the routes. Results were summarized by plotting all components of lead time in a graph (see Figure 9). Note that in all cases transit time from mill to warehouse was the smallest component of lead time. Average Lead Time by Route Ftoute2 Roule3 Poute4 Mil l-Warehouse • Average Storage Days at Mil • Average Mil to Warehouse Days • Average Storage Days at Warehouse Composit ion of Average Lead Time by Route • i • Average Storage Days at Warehouse) • Average Mil to Warehouse Days • Average Storage Days at Mil Roule2 Route3 Ftoute4 Mil l-Warehouse Figure 9: Sample Graphs of Average Lead Time by Route (Scale not Displayed) Next, frequency and cumulative frequencies p(x) of total lead time were calculated and plotted. A sample graph is presented in Figure 10. Note that in this graph, as was the case in most of the routes, the median is above the assumed lead time (more than 50% of the line items have a lead time greater than what is assumed). Furthermore, the data is skewed with a long right tail. Average Total Lead Time From Mill to Departure from Warehouse Mill1-DC1, All Product Types | Assumed Lead Time] Average Total Lead Time (Days) from Mill to Departure from Warehouse Figure 10: Sample Graph of Relative Distribution of Average Total Lead Time (Scale not Displayed) 3 Percentage of line items that have a lead time greater than or equal to x. 10 Based on the previous analysis, it was concluded that assumed SAP lead times did not accurately represent real lead times. Box plots were very useful in identifying factors affecting lead times as well as in comparing the variability across categories. Data was grouped by factors and by lead time components. For instance, box plots created to study the effect of mill/warehouse route on each lead time component (as well as on the total lead time) can be observed in Figure 11. Note that the first route has the smallest average total lead time while the last route has the greatest variability in average total lead time. In addition, average storage days at the warehouse have the greatest effect on average total lead time. Average Storage Days at Mill U l l l W U a r e l o i i i Average Mill to Warehouse Days MIIIMJ are t o i s « Average Storage Days at Warehouse I I I ' i U I M A J a r e l o i t e Figure 11: Sample Box Plots of Average Storage Days at Mill, Average Mill to Warehouse Days, Average Storage Days at Warehouse, and Average Total Lead Time by Route (Scale not Displayed) 11 Possible factors that affected lead time and its variability were obtained based on the key components of the process maps, as well as from the company's brainstorming sessions. They include: • Mil l : where the line item was produced. • Warehouse: first distribution center in which the line item was stored. • Break Bulk: effect of the presence of a break bulk carrier between production date of first roll and mill shipping date of last roll of line item. • Product: product type. • Month: month in which first roll of the line item was produced. • Outbound Mode: mode used to transport the line item from the warehouse to the customer. • Customer: order destination at aggregate level. Given the fact that some factors were not independent, combination of factors mentioned above were also studied. 3.2 Results Based on the statistical analysis, it was possible to conclude that lead times were highly variable. The results of this study can be summarized as follows: • The major components of lead time are storage at mill and distribution center. In all cases transit time between mill and warehouse constitutes only a small fraction of lead time. • Storage times at mill vary among the mills. • Storage times at warehouses vary among warehouses. • The presence of break-bulk carriers had an effect on lead times at only one of the mills. • Warehouse storage times vary between products. • For each route, month to month variation is small. 12 • Outbound mode affects warehouse storage days. That is, depending on the mode used to transport the product from the warehouse to the customer, line items may be stored for a longer or shorter time at the warehouse. • Warehouse storage depends on the customer. That is, the number of days line items are stored at the warehouse depend on where the line item will be shipped afterwards. 13 4. DISTRIBUTION NETWORK PLANNING TOOL 4.1 Linear Programming Model 4.1.1 Model Overview A high-level distribution model to be used for strategic decisions was developed. The purpose of this model is to determine the amount of product to be sent on each route and transportation mode in order to minimize total distribution costs from upender to delivery to aggregated demand regions in a period. Given the business cycle, a period of 30 days (or a month)4 was chosen, although this can be modified if necessary. The model is used to analyze and evaluate possible changes, that could potentially reduce distribution lead times and distribution costs, such as: • Adding/deleting mills, distribution centers, customers, products and/or modes. • Modifying production, handling, holding and transportation capacities. • Modifying demands and Make-to-Stock inventory kept. • Modifying handling, holding, inventory, and transportation costs. It was decided to formulate the distribution network as multi-modal, multi-product, single-period linear program (LP). LP was chosen over simulation given its capability of obtaining an optimum solution, as well as its easiness to modify to perform what-if analysis. Given the way most of the costs are calculated by the company (per metric ton (MT)) and the large volumes transported, LP seems to be the best approach to solve the problem. 4.1.2 The Model The indices of this model are as follows: 4 This is the longest period in which all details for strategic decisions can be included. O n the other hand, information pertinent only to operational decisions can be omitted given the length of the period. 14 Mode index i: Transportation mode used to bring the product from the source to the destination. Each mode has a unique capacity. It was decided to include 17 modes in the "as-is-model": o Barge (4) o Truck (3) o Rail (3) o Intermodal (1) o Container/Vessel (4) o Break Bulk (2) Source index j : Mills or distributions centers from which products can be shipped. The "as-is-model" included eight possible sources: o Mills (4) o Distribution Centers (4) Destination index k: Mills, distribution centers or customers where products can be shipped. Customers were aggregated into regions based on their transportation costs and lead times, as well as possible modes that can reach those customers. In order to model make-to-stock products, two different indexes where assigned to each distribution center: one for make-to-order products which can be shipped to another location within the period and another for make-to-stock products which are stored at the distribution centers for at least one period. Therefore, the "as-is-model" included the possible destinations: o Mills (4) o Distribution Centers (4) o Customer Regions (20) o Distributions Centers - Make-to-Stock (MTS) (4) Product index 1: Only paper products were included in the "as-is-model". The main products are: o Newsprint (1) o Specialties (3) 15 The decision variables of the model are defined as Xpi, which represent the quantity (in metric tons (MT)) of product 1 to be transported from j to k per period using mode i to satisfy the demand of a period. The "as-is-model" has a total of 17,408 decision variables. The mathematical formulation of the model is as follows: Min Ejj;k,i Xjjki * (Holding Costyi + Cost of Inventorypi + Handling Cost^i + Transportation Costijk) subject to Production Plan: EijkXijki demandki V customers k, (4.1.2.3) products 1 E ij X^i >Make-to-Stock requirements^ V DCs k, products 1 (4.1.2.4) Transportation Capacity: £j,k,i (xijki / mode capacityjk) <1 V modes i (4.1.2.5) s.t. capacity^ ^ 0 Handling Capacity: Ek,i Xyki + Ej',i Xjj'ji ^handling capacity^ V mills and DCs j, (4.1.2.6) transportation modes i Bounds: 0 > Source Source's Transportation Destination's Gate Mode Gate Figure 12: General Cost Components If the destination is a Distribution Center, such as in the case of make-to-stock products, an additional cost is added to account for the storage of make-to-stock products (see Figure 13, the dashed line represents the additional cost). Transportation Cost + T T , _ Holding Cost + Loading Cost + U n l o a d i n g C o S t 4- Holding Cost + Cost of Inventory Cost of Inventory Cost of Inventory Cost of Inventory , A _ A ^ ^ A ^ , A v Source Source's Transportation Destination's Destination's Gate Mode Gate Storage Figure 13: Cost Components for those Routes in which the Destination is a Distribution Center -Make-to-Stock All costs are in US$/metric ton. Costs are calculated as follows: • HoldinR Cost: In order to calculate holding cost, the holding cost per metric ton per day is multiplied by the average number of days products are stored at each 17 location (which also depends on the outbound mode). To differentiate among products, the result is multiplied by a cost factor. Holding Costyi =Holding Cost per Day-j * storage timey * Holding Cost Factor7 The holding cost factor, like the rest of the cost factors, is assumed to be 1 for some particular product, and it is then adjusted for the other products to reflect the actual holding cost. • Cost of Inventory: The total cost of inventory is calculated by multiplying the total lead time of the route5 (in days) times the cost of capital per metric ton per day. Like holding cost, cost of capital also depends on the product. Therefore, cost of inventory can be calculated as follows: Cost of Inventoryt/ki = lead timeyk * Cost of Inventory per Month * Inventory Cost Factori 30 ~~ where lead timeyk = storage timey + transportation timeyk + loading timey + unloading timeik if k * Distribution Center - Make-to-Stock lead timeik = storage timey + transportation timeyk + loading timey + unloading timeik + storage timeik if k = Distribution Center - Make-to-Stock Loading and unloading times are calculated by multiplying the inverse of the charge/discharge rate of each location times the capacity per trip of the mode for 5 Total lead time is equivalent to the average total lead time by route and mode computed in Phase I. Note that, for modeling purposes, average storage time computed in Phase I is subdivided into its components: storage time, loading time and unloading time. 18 the given route. If the destination- is a customer, both unloading time and unloading cost are assumed to be zero. • Handling Cost: This cost represents the cost of personnel, which is incurred when products are loaded/unloaded into the transportation modes. It is assumed to be the sum of the loading and the unloading costs: Handling Cost^i = (Cost/hour/personj * crew sizey * loading timey * Handling Cost Factorji) + (Cost/hour/personk * crew sizeik * loading timeik * Handling Cost Factory) • Transportation Cost: Transportation cost is assumed to be the sum of all costs (fuel, mode rental, labour cost) incurred in shipping each metric ton from the source to the destination. We now interpret the constraints above: • Production Planning Constraints: The model assumes that production plans at the mills are exogenous to the model, so that there are specific production capacities per period for each product type at each mill. Therefore, the total metric tons per product type shipped out of each mill should be less than or equal to the sum of the production capacity and the total metric tons per product type shipped into each mill. Constraint 4.1.2.1 corresponds to transhipments between mills. In addition, the stock available at each distribution center is provided. Thus, a similar constraint can be written for all distribution centers: the total metric tons per product type shipped from each distribution center should not exceed the sum of the stock available plus the total metric tons per product type shipped into each distribution center, (see constraint 4.1.2.2) 19 • Demand and Make-to-Stock Requirements: Total demand of each product type as well as total make-to-stock requirements should be satisfied (see constraints 4.1.2.3 and 4.1.2.4). • Transportation Capacity: Transportation capacities should not be exceeded. In order to calculate transportation capacities of mode i for route j,k, the following formula is used: mode capacity yk = number available^ minfcapacity per tripg, capacity per triptk} * # days/period cycle timet The first term of the formula (number available;) refers to the transportation units (barges, tucks, etc.) available of each transportation mode. The second term (capacity per trip) refers to the number of metric tons that can be transported on the route j,k by one transportation unit i . Some sources and destinations do not allow some transportation units to be fully loaded. Therefore, the maximum metric tons that can be transported in a given route will be the minimum of the capacity that can be loaded at the source and the capacity that can be unloaded at the destination. The third term of the formula (#days/period) refers to the length of the planning period (here assumed to be 30' days). This term is divided by the cycle time of the mode (see Figure 14) to determine the number of cycles that can be completed in a period. Dead Head Loading Time Transportation Time Unloading ; Time r V Request Mode Mode Departs Mode Mode Departs Mode Arrives to from Source Arrives to from Source Destination Destination Figure 14: Cycle Time Components 20 For each mode, by dividing the metric tons transported on each route (Xyki) by the number of metric tons that can be transported in a period (mode capacity^, the fraction of the capacity consumed by that route is obtained. Given that only one period is available to transport all products, the sum of all fractional capacities should be less than or equal to one (see constraint 4.1.2.5). • Handling Capacity: The total number of.the metric tons shipped into each location and out of each location should be less than or equal to the handling capacity of that location of each transportation mode (see constraint 4.1.2.6). Handling capacity is calculated as follows: handling capacity Warehouse -> Customer in BC Receive Order MES M i l l Execu t i on Sys tem Production Planning Manufacturing Mill Shipping SAP E R P system Order Analysis: Available Capacity Inventory, Lead time - Prefered mode - Alternative modes Customer Service Sylvan Order Planner Check delivery information Detail Level 48 -96 hrs before warehouse date Goods Shipment Cost Document] Freight Accruals W M S Warehouse M a n a g i n g Sys tem Warehouse Inbound Crew Warehouse Inventory Crew Update \ D r d e r F i l e Pick, stage, scan, load Warehouse Outbound Crew Deliver Order 38 Product flow for a simple example 39 Appendix 2: AMPL Formulation #### INDEX #### set customer; set source; set source_index; set product; set mode; set route within {p in product, m in mode, si in source, s2 in source}; set routecustomer within {p in product, m in mode, si in source, c in customer}; #### PARAMETERS #### param cost {route} >=0; param routecap {route} >=0; param routecap_customer {route_customer} >=0; param cost_customer {route_customer} >=0; param prodcap {p in product, si in source} >=0 default 0.0; #Assume that the production capacity at DCs is zero param stockavail {p in product, si in source} >=0 default 0.0; #Assume that the stock availability at factories is zero param demandcap {p in product, c in customer} >=0; param modecap {m in mode, si in source, s2 in source} >=0; param modecapcustomer {m in mode, si in source, c in customer} >=0; param handlingcap {m in mode, si in source} >=0; #### VARIABLES #### var Ship {route} >=0; var Ship_customer {route_customer} >=0; #### OBJECTIVE #### minimize Totalcost: sum {p in product, m in mode, si in source, s2 in source} Ship[p,m,s 1 ,s2] *cost[p,m,s 1 ,s2] + sum {p in product, m in mode, si in source, c in customer} Ship_customer[p,m,sl,c]*cost_customer[p,m,sl,c]; 40 #### CONSTRAINT #### subject to Production {p in product, j in source_index}: sum {m in mode, s2 in source} Ship[p,m,j,s2] + sum {m in mode, c in customer} Ship_customer[p,m,j,c] <= prodcap[p,j] + stockavail[p,j] + sum {m in mode, si in source} Ship[p,m,sl,j]; subject to Demand {p in product, c in customer}: sum {m in mode, si in source} Ship_customer[p,m,sl,c] >= demandcap[p,c]; subject to Transportation {m in mode}: sum {p in product, si in source, s2 in source} Ship[p,m,sl,s2]*modecap[m,sl,s2] sum {p in product, si in source, c in customer} Ship_customer[p,m,sl,c]*modecap_customer[m,sl,c] <= 1; subject to Handling {m in mode, j in sourceindex}: sum {p in product, s2 in source} Ship[p,m,j,s2] + sum {p in product, c in customer} Ship_customer[p,m,j,c] + sum {p in product, si in source} Ship[p,m,sl,j] .<= handlingcap[m,j]; subject to Bound {p in product, m in mode, si in source, s2 in source}: Ship[p,m,sl,s2] <= routecap[p,m,sl,s2]; subject to Bound2 {p in product, m in mode, si in source, c in customer}: Ship_customer[p,m,sl,c] <= routecap_customer[p,m,sl,c]; Appendix 3: Steps Involved in Adding Items to the Model Steps for Adding a Mill Workshee t Title = row 1, c o l u m n 1 T o d o Transportation-Routes Transportation Mode Availability at Each Location Insert row after first mill, modify numbers from column 1 all the way down; copy column 2 down 1 row to the riqht Transportation-Routes Routes Insert row after first mill for all transportation modes; copy all the way for that mode; insert column after first mill; copy all the way until blank Transportation-Capacity Maximum Capacity Insert row after first mill; copy colums until blank one row down Transportation-Capacity Total Capacity Insert row after first mill; copy all columns until blank one row down Lead Time Time Spent at Each Location Insert row after first mill; copy colums 1 and 2 one row down Lead Time Transit Time Insert row after first mill for all transportation modes;copy from column 3 to blank (or all columns to blank) one row down for all modes. Check numbering of row after "Dead Head". Insert column after first mill; copy column of first mill 1 column to the riqht Lead Time Total Transportation Lead Time Copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first mill; copy column of first mill 1 column to the riqht Lead Time Total Lead Time Copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first mill; copy column of first mill 1 column to the riqht Transportation-Capacity Capacity Constraint Insert row after first mill for all transportation modes; copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first mill; copy column of first mill 1 column to the right Transportation-Capacity Fractional Capacity Copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first mill; copy column of first mill 1 column to the riqht Production Plan Production Capacity Insert row after first mill; copy colums all white columns until blank one row down Handlinq-Capacity Loading/Unloading Capacity Insert row after first mill; copy all columns to the blank one row down Costs Cost of Inventory per MT Insert row after first mill for all transportation modes; copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first mill; copy column of first mill 1 column to the right Costs Transportation Cost per MT Copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first mill; copy column of first mill 1 column to the riqht Costs Cost of Handling Inventory Insert row after first mill;copy colums all white columns until blank one row down Costs Holding Cost Insert row after first mill; copy all columns until blank one row down Cost Factors Cost of Handling Inventory (productivity) Insert row after first mill; copy all columns 2 and 3 one row down Cost Factors Handling Cost per Product Type per MT Insert row after first mill for all transportation modes and all products; Copy first column and then all other columns to blank one row down for all modes and products. Insert column after first mill; copy column of first mill 1 column to the right. Costs Factors Holding-Cost-per-MT Copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first mill; copy column of first mill 1 column to the riqht. Model Routes Insert row after first mill for all transportation modes; copy column all columns to blank one row down for all modes. Model Decision Variables Insert row after first mill for all transportation modes and all products; copy column 1 and from column 3 to blank one row down for all modes and products; copy column 2 one row down for all products Model MT Handled Search for Outbound-Product. Insert row after first mill (2 columns to the left of title); copy from one column to the left of first mill all the way to blank one row down. Model MT Handled Search for Inbound-Product. Insert row after first mill (2 columns to the left of title). Copy from one column to the left of first mill to blank one row down Model MT Handled Search for Inbound-Mode. Copy from column of first mill to blank one row down Model MT Handled Search for Outbound-Mode. Copy from column of first mill to blank one row down. Steps for Adding a Distribution Center W o r k s h e e t Ti t le = r o w 1, c o l u m n 1 T o d o Transportation-Routes Transportation Mode Availability at Each Location Insert row after first DC, modify # column 1 all the way down; copy column 2 down 1 row Transportation-Routes Routes Insert row after first DC for all transportation modes, copy all the way for that mode, insert column after first DC, copy all the way until blank Transportation-Capacity Maximum Capacity Insert row after first DC; copy all colums until blank one row down Transportation-Capacity Total Capacity Insert row after first DC; copy all columns until blank one row down Lead Time Time Spent at Each Location Insert row after first DC; copy colums 1 and 2 one row down Lead Time Transit Time Insert row after first DC for all transportation modes;copy from column 3 to blank (or all columns to blank) one row down for all modes. Check numbering of row after "Dead Head". Insert column after first DC; copy column of first DC 1 column to the right. Lead Time Total Transportation Lead Time Copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first DC; copy column of first DC 1 column to the right Lead Time Total Lead Time Copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first DC; copy column of first DC 1 column to the right Transportation-Capacity Capacity Constraint Insert row after first DC for all transportation modes; copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first DC; copy column of first DC 1 column to the right Transportation-Capacity Fractional Capacity Copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first DC; copy column of first DC 1 column to the right Production Plan Stock Available Insert row after first DC; copy colums all white columns until blank one row down Handling-Capacity Loading/Unloading Capacity Insert row after first DC; copy all columns to the blank one row down Demand Demand Insert row after first DC; copy colums all white columns until blank one row down Costs Cost of Inventory per MT Insert row after first DC for all transportation modes; copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first DC; copy column of first DC 1 column to the right. Insert column after first DC - MTS; coov first row all the wav to blank and then all other rows of first DC - MTS 1 column to the right. Costs Transportation Cost per MT Copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first DC; copy column of first DC 1 column to the right. Costs Cost of Handling Inventory Insert row after first DC;copy colums all white columns until blank one row down Costs Holding Cost Insert row after first DC; copy all columns until blank one row down Cost Factors Cost of Handling Inventory . (productivity) Insert row after first DC; copy all columns 2 and 3 one row down Cost Factors Handling Cost per Product Type per MT Insert row after first DC for all transportation modes and all products; Copy first column and then all other columns to blank one row down for all modes and products. Insert column after first DC; copy column of first DC 1 column to the right. Costs Factors Holding-Cost-per-MT Copy column 2 and from column 4 to blank (or all columns to blank) one row down for all modes. Insert column after first DC; copy column of first DC 1 column to the right. Same for DC - MTS Model Routes Insert row after first DC for all transportation modes; copy column all columns to blank one row down for all modes. Insert column after first DC; copy column of first DC 1 column to the right. Insert column after first DC - MTS; copy all rows of first DC - MTS 1 column to the right. Model Decision Variables Insert row after first DC for all transportation modes and all products; copy column 1 and from column 3 to blank one row down for all modes and products; copy column 2 one row down for all products. Copy column of first DC 1 column to the right. Copy column of first DC - MTS 1 column to the right. 43 Steps for Adding a Customer W o r k s h e e t Ti t le = r o w 1, c o l u m n 1 T o d o Transportation-Routes Transportation Mode Availability at Each Location Insert row after first Customer, modify # column 1 all the way down; copy column 2 down 1 row Transportation-Routes Routes Insert column after first Customer, copy all the way until blank Transportation-Capacity Maximum Capacity Insert row after first Customer; copy colums all white columns until blank one row down Transportation-Capacity Total Capacity Insert row after first Customer; copy all columns until blank one row down Lead Time Time Spent at Each Location Insert row after first Customer; copy colums 1 and 2 one row down Lead Time Transit Time Insert column after first Customer; copy column of first Customer 1 column to the right. Lead Time Total Transportation Lead Time Insert column after first Customer; copy column of first Customer 1 column to the right Lead Time Total Lead Time Insert column after first Customer; copy column of first Customer 1 column to the right Transportation-Capacity Capacity Constraint Insert column after first Customer; copy column of first Customer 1 column to the right Transportation-Capacity Fractional Capacity Insert column after first Customer; copy column of first Customer 1 column to the right Handling-Capacity Loading/Unloading Capacity Insert row after first Customer; copy columns all columns to blank one row down Demand Demand Insert row after first Customer; copy colums all white columns until blank one row down Costs Cost of Inventory per MT Insert column after first Customer; copy column of first Customer 1 column to the right. Costs Transportation Cost per MT Insert column after first Customer; copy column of first Customer 1 column to the right. Costs Cost of Handling Inventory Insert row after first Customer;copy colums all white columns until blank one row down Costs Holding Cost Insert row after first Customer; copy all columns until blank one row down Cost Factors Handling Cost per Product Type per MT Insert column after first Customer; copy column of first Customer 1 column to the right. Costs Factors Holding-Cost-per-MT Insert column after first Customer; copy column of first Customer 1 column to the right. Model Routes Copy column of first Customer 1 column to the right. Model Decision Variables Copy column of first Customer 1 column to the right. Model MT Handled Search for Outbound-Product. Insert row after first Customer (2 columns to the left of title); copy from one column to the left of first Customer all the way to blank one row down. Model MT Handled Search for Inbound-Product. Insert row after first Customer (2 columns to the left of title). Copy from one column to the left of first Customerto blank one row down. Model MT Handled Search for Inbound-Mode. Copy from column of first Customer one row down. Model MT Handled Search for Outbound-Mode. Copy from column of first Customer to blank one row down. Model Constraints (Customer) Search for "demand<=in"; insert one row below it. Copy from numbering (2 to the right) to blank one row down Output-Tons Handled Tons Handled Insert row after first Customer; copy all columns until blank one row down 44 Steps for Adding a Mode W o r k s h e e t Ti t le = r o w 1, c o l u m n 1 T o d o Transportation-Routes Transportation Mode Availability at Each Location Insert column after first mode. Copy row 1 all the way to the right until end of last product. Copy row 3 one column to the right. Transportation-Routes Routes Count number of mills+number of DCs=x. Insert x rows after end of first mode (which is x rows long) (right before second mode). From the last row of the first mode: copy column 1 x rows down; copy column 3 x rows down; copy from column 5 all the way to the right x rows down. Write the following formula in the first cell of column 2 (=cell located 4 rows below "Routes"+1). Copy that formula all the way down. Write the following formula in the first cell of column 4 (=cell located 2 columns to the right of "From | / To --"). Copy the formula x rows down. Transportation-Capacity Transportation Capacity Calculation Insert column after first mode. Copy rows 1 and 2 one column to the right Transportation-Capacity Maximum Capacity Copy all rows (all the way down until blank) of first mode one column to the right Lead Time Time Spent at Each Location Search for "Load/Unload". Insert column after first mode; copy rows 2 and 3 one column to the right. Search for "Store - Make to Order". Insert column after first mode; copy rows 2 and 3 one column to the right Lead Time Transit Time Insert x rows after end of first mode (which is x rows long) (right before second mode); copy columns 1 to 5 (matrix titles) of last row of first mode x rows down. Update counting at the right of "Dead Head". Lead Time Total Transportation Lead Time Copy all columns to blank (all the way to the right) of last row of first mode x rows down. Lead Time Total Lead Time Copy all columns to blank (all the way to the right) of last row of first mode x rows down. Transportation-Capacity Capacity Constraint Insert x rows after end of first mode (which is x rows long) (right before second mode);copy all columns to blank (all the way to the right) of last row of first mode x rows down. Transportation-Capacity Fractional Capacity Copy all columns to blank (all the way to the right) of last row of first mode x rows down. Handling-Capacity Number of Docks Loaded at the Same Time Insert column after first mode. Copy row 2 (one row below title) one column to the right Handling-Capacity Charge-Discharge Rate (MTs/hour) Insert column after first mode. Copy row 2 (one row below title) one column to the right Handling-Capacity Total Capacity Insert column after first mode. Copy from row 2 (one row below title) to blank (all the way down until blank) one column to the right Costs Cost of Inventory per MT Insert x rows after end of first mode (which is x rows long) (right before second mode);copy all columns to blank (all the way to the right) of last row of first mode x rows down. Costs Transportation Cost per MT Copy columns 1 to 6 (matrix titles) of last row of first mode x rows down. Costs Cost of Handling Inventory Search for "Productivity". Insert column after first mode. Copy from row 2 (one row below searched word) to blank (all the way down until blank) one column . to the right Costs Holding Cost Search for "Order". Insert column after first mode. Copy from row 2 (one row below searched word) to blank (all the way down until blank) one column to the right Cost Factors Handling Cost per Product Type per MT For all products: Insert x rows at the end of first mode (which is located x rows after Product...). Copy first column (product column; 4 columns to the left of title) of last row of first mode x rows down. Copy second column (numbering column = 1, 1, ...) of last row of first mode x rows down. Copy from third column all the way to the right until blank of last row of first mode x rows down. Costs Factors Holding-Cost-per-MT Copy all columns to blank (all the way to the right) of last row of first mode x rows down. Model Routes For all products, insert x rows after end of first mode (which is x rows long) (right before second mode);copy all columns to blank (all the way to the right) of last row of first mode x rows down. 45 Steps for Adding a Product Workshee t Tit le = row 1, c o l u m n 1 T o d o Demand Demand Add column after first product. For the first row, copy first column all the way to the right. For the last row, copy first column one column to the right. Production Plan Production Capacity Insert column after first product. Copy rows 1 and 2 one column to the right. Cost Factors Distribution Cost Insert column after first product. Copy row 1 one column to the right. Cost Factors Handling Cost per Product Type per MT Count number of mil ls+number o f DCs=x. Count number of modes=y. Let p=x*y. Insert p rows after end of first product (which is p rows long) (right before second product). From the last row of the first mode: copy column 1 p rows down; copy columns 3 and 4 p rows down; copy column 6 p rows down; copy from column 8 all the way to the right p rows down. Write the following formula in the first cell of column 2 (=(cell located 4 rows below title, in column 2)+1). Copy that formula all the way down. Write the following formula in the first cell of column 5 (=cell located 4 rows below title, in column 5). Copy that formula p rows down. Write the following formula in the first cell of column 7 (=cell located 4 rows below title, in column 7). Copy that formula p rows down. Model Routes Insert p rows after end of first product (which is p rows long) (right before second product);copy all columns to blank (all the way to the right) of last row of first product p rows down. Model Decision Variables Insert p rows after end of first product (which is p rows long) (right before second product);copy column 1 p rows down; copy from column 3 all the way to the right (all columns to blank) of last row of first product p rows down. Write the following formula in the first cell of column 2 (=cell located 6 rows below title, in column 2) Model Summary Insert one row after each "First-Product". Copy row 3 (product name) of each row containing "First-Product" one row down; copy row 4 (# ) of each row containing "First-Product" one row down; copy from row 5 all the way to the right of each row containing "First-Product" one row down. Write the following formula in row 2 (=cell above+1). Count the number of products n . Copy formula n-2 rows down. Model MT Handled Search for "Outbound-Product". Insert column to the right of Outbound-Product. Copy from row 2 (one row below "Outbound-Product") to blank (all the way down until blank) one column to the right Model MT Handled Search for "Inbound-Mode". Copy from row 2 (one row below "Inbound-Mode") to blank (all the way down until blank) one column to the right Model Constraints Insert column after each column containing "First-Product". Copy all rows of First-Product one column to the right. Insert column after "Productl". Copy all rows starting from "Productl" to blank 1 column to the right. Output-MT. Handled MT Handled Insert column after each column containing "Productl". Copy all rows of Productl one column to the right. Format Chart. Output-Transportation Usage Total MT by Mode Insert column 4 columns to the right of title. Copy all rows of third column to the right of title one column to the right. Format Chart. 46 Appendix 4: User Interface/Excel Formulation Initial Screen Modtl Sununaiji •vcision Variables Constuints Main Menu \ DCWMMfiT Time Strategic Planning and Optimization ot Routing Tool Main Menu Late stSo w m jo r What IP Analysis '' Solve • View Outputs NorskeCanada M < • w\Model Summary/Model / Output-Costs / Output-CustSummary / Output-MT Handled |< r What-If Analysis Menu View Outputs Menu Whal If Analysis Form What If Analysis Inputs Ok Help View Output r Add or Delete a Mill r Modify Demand 1" Add or Delete a DC r Modify Costs ' Add or Delete a Customer c Modify Lead Times C Add or Delete a Transportation Mode c Check Feasibility r Add or Delete a Product r Enter Wizard C Add or Delete a Route c Main Menu Modify Capacities Select Output to View LI <~ Average Costs C Routes <~ Transportation Usage <~ Scenario Comparison t~ MT Handled at Each Location (~ dear Scenario Record C" Cost and Lead Time by Customer i* Main Menu f* Routes by Customer f* Model Summary Generate Output Ok Helo 47 Feasibility Check A B | C All conditions are satisfied Production capacity is sufficient to satisfy the FEASIBLE Handling capac ty is sufficient to satisfy the Transportation capacity is sufficient to satisfy the demand of: 1 Productl 2 Product 2 demand af. DCs Customers demand of 3 DC DC 1 4 DC DC2 5 Customer Customer 1 6 Customer Customer 2 All data has been inputed into the model Transportation Mode Availability at Each Location Transportation Capacity - Number Available Transportation Capacity - Capacity/Trip Production Capacity Stock Available Charoe/Discharne Rate Number of Docks Loaded at the Same Time Number of Working Hours per Day • w/ Output-RoutesGeneral / Scenarios \ Feasibility/Demand /Costs / Costs2 / Cost Factor; | < ] FEASIBLE TRUE TRUE FEASIBLE TRUE TRUE FEASIBLE TRUE TRUE TRUE TRUE FEASIBLE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE » | | L_J_ Main Menu Feasibility check Feasibility check is successful J A Model 48 Appendix 5: Help File Help Mode Characteristics Interface Frequently Asqued Questions Mode l Ch a rac t e r i s t i c s P u r p o s e s This is a strategic, high-level model, to be used to determine the optimum distribution plan based on tradeoffs between time and costs The model can be used to compare optimum costs under different what-if scenarios. D e c i s i o n Var iab les For each product type, how much to be shipped by each transport model on each possible route to satisfy customers' demand of one month Cons t ra in t s - Product flow - Do not ship more than what can be produced/stored at each location - Satisfy demand and replenishment requirements. - Satisfy transportation capacities. - Only ship product on available routes - Satisfy handling capacities. M o d e l Inputs N u m b e r Avai lab le o f E a c h Mode For each equipment type, how many are available per month For unlimited capacity, assume a very large number. Capaci ty /Tr ip Amount of MT that can be transported by a single equipment in a single trip. M a x i m u m Capac i ty Maximum capacity of each equipment type that can be handled at each location. P r o d u c t i o n Capac i ty Average MT of each product type that can be produced at each mill. Stock Avai lab le Average replenishment inventory shipped out of each DC per month 49 Charge-Discharge Rate MT that can be loaded into each equipment type per hour with a crew of the size specified. Number of Docks Loaded at the Same Time Number of equipments of the same type that can be loaded at the same time at each location. Number of Working Hours per Day Average number of working hours per day. Number of Working Days per Month Average number of working days per month. Demand For DCs: Average MT shipped to each DC of each product type per month. For customers: Average demand of each region of each product type per month. Transportation Cos t per MT All costs incurred in transportation: cost of mode, donnage, fuel, etc. per MT. Cost of Inventory per MT Average cost of capital per MT per month Cost of Handling Inventory All costs incurred in loading/unloading each MT into the transportation mode. Holding Cos t Storage cost paid per MT at each location. Cost Factors Factors represent how the cost varies between different products and locations with respect to average newsprint cost. Storage Time of Make- to-Order Products Average storage time of make-to-order products at each location based on the transportation mode used to transport the product out of that locati on. Storage Time of Make-to-Stock Products Average storage time of replenishment inventory at each location based on the transportation mode used to transport the product out of that location. Transit Times J Average total time elapsed from the moment the product leaves the source to the moment the product is ready to be unloaded at the destination. Dead Head Average time elapsed from the moment a mode is requested to the moment products can be loaded into the mode. Model Outputs Average Costs This worksheet contains the following information: - Average transportation, inventory, handling, holding and total cost per MT. - Cost versus cost component graph. - Average cost and lead time by customer, - Average cost and lead time by last leg mode. Transportation Usage The information contained in this worksheet is as follows: - Total MT of each product type transported by each equipment type. - Total MT versus transportation mode graph. - Total MT transported in the last leg for each product type, source, destination, and mode. MT Handled at Each Location This worksheet contains information regarding: - Total MT that depart and/or arrive into each location of each product type. - Total MT handled at each location and MT handled at each location graphs. Cost and Lead Time by Customer This output summarizes the average cost and lead time by customer. Routes By Customer Based on the model output, this output summarizes all routes available to each customer. Routes This output summarizes the cost, lead time and MT of all nonzero routes. Scenario Comparison In this worksheet, information of each scenario ran is recorded for later comparisons. Select "Clear Scenario Record" to delete all information contained in this worksheet. Model Summary This worksheet contains information regarding: - Number of mills, DCs, customers, modes and products in the model. - Number of decision variables and constraints in the model. - Names of items currently in the model. - Names of deleted items. - Record of names and dates of scenarios ran. Main Menu This menu is shown when the program is first opened. It has access to the three major components of the program: - What-lf Analysis - Solve - View Outputs it can also be accessed from any worksheet by clicking on the "Mam Menu" button. What-lf A n a l y s i s Please select this option in order to make modifications to the model or model inputs. Outputs are not automatically updated when changes are made to the model. Therefore, it is necessary to solve the model again before viewing model outputs. When exiting the What If Analysis Inputs Menu, select "Solve Now" in order to solve the model or "Solve Later" to either Exit or go back to the Main Menu A d d or Delete a Mil l , D C , Cus tomer , Transpor ta t ion Mode or P r o d u c t Select this option to make modifications to the model. In order to add an item, select "Add" and input the number to add. To delete or restore an item, it is only necessary to select "Delete/Restore". A d d ^ W i z a r d Adding expands all matrixes to include the new item. After the model is expanded, the user has the following options: - Return to What If Analysis Inputs Menu - Use the Wizard to input data of the newly added item. Delete/Restore Select this option to either delete an item or restore a previously deleted item When an item is deleted, constraints are modified to force the item to be 0. Use the Input Wizard to confirm that all data for the restored item is accurate. Input W i z a r d s The main purpose of the Input Wizards is to guide the user through all tables that contain data of a given item. The Wizards do not expand the model However, they recalculate possible routes based on data inputted. Furthermore, they update the "Model Summary" worksheet. A d d or Delete a Route Select this option to modify individual routes. A wizard will guide the user through each step necessary to add/delete a route " M o d i f y " M e n u s Select any "modify" option to change model inputs Based on the input that needs to be modified, the menu will bring the user to the appropriate screen C h e c k Feas ib i l i ty Select this option to do a general feasibility checkwhich includes. - Check that total production capacity is sufficient. - Check that total handling capacity is sufficient. - Check that total transportation capacity is sufficient. - Check that there is no data missing in any table If the feasibility check is not satisfactory, possible reasons are highlighted in red. Select relevant link to check appropriate tables. Solve Select thi s opti on to solve the model Before solving, a feasibility check is performed If the feasibility check is satisfactory, the user is requested to input the name of the scenario. The model is solved after the scenario name has been inputted Generate Output After the model is sotved. the user should select this option to generate the model outputs View Outputs Select this option to navigate through the model outputs. Frequently asked questions Even though feasibility check is satisfactory, the model is infeasible. The feasibility check is only a general test Therefore, the model could end up being infeasible even if the feasibility check is satisfactory. Possible causes could include: - Data is not inputted in the right units - Conflicts of transportation capacities - Others Suggestions Revise the data inputted. When adding a mill/DC/customer/mode/product a problem occurs. Excel has some bugs regarding additions of rows/columns in large matrixes. Suggestions: Close and reload the model Manually add and delete columns to the problematic table Run interface again to add mill/dc/customer/mode or product When deleting a mill/DC/customer/mode/product a problem occurs. In order to delete an item, Model Summary information should be updated. Suggestions: Go to Model Summary and click on Update Tables When opening the model, an insufficient memory message occurs. Suggestions: Close all other programs and reopen the model If the problem persists, reinstall Microsoft Office When running the interface, an error occurs regarding a missing library. In order to run the interface all VBA options should be installed Suggestions: Install all missing libraries In Visual Basic Editor & I 3 - U I II • K %( 6f S B « »—« H»ert Farm* Qatug I«*r 4*Hns a»>*~ B * ei Options... VBAProttfCt Properties Djgtt«iSffi*ur»... In Microsoft Excel '*] He 0&n Irosrt Format Toob fcata Undent tjedp Artal . 10 D517 " j feutoCorrect... Irack Changes Orfne BtMMMri 5oalS»«l„.. C^ usUnWe... 0 *« Analysis Available References; ' Microsoft Excel 9.0 Object ubrery ' OLE Automaton ' Microsoft Office 9.0 Object Library ' Microsoft Forms 2.0 Object Library ' Ref Ed* Control ' sum* ' XPRESSeng '' Solver ' Microsoft VBA MT Project Designer ' Microsoft VBA MT Proiect Designer Runtme ' Mcrosoft VBScript Regular Expressions 1.0 ' Mcrosoft VBScript Regular Expressions 5.5 ' Mcrosoft Visual Basic for Arjokations Extenwbiftv Ajd-Ins available: l~ Access Links V Analysis TooPak Cancel I - Autosave Add-n V ConoStional 5urn Wteard P Euro Currency Tools —' — f Frontkne's Mathematical Functions r Internet Assistant VBA V Lookup Wizard l~~ Lslpeng Analysis ToolPak - VBA VBA functions for Analysis TooPak 55 When I try solving the model, the solver cannot be found. Suggestions: Check that the "add in" solver has been installed. Reopen model and try solving the model again. It takes a large amount of time to add an item to the model. The order in which items are added to the model affects the expansion speed. Suggestions: Close all other programs running. Start with the pilot version, and add items in the following order: - Add DCs - Add mills - Add products - Add modes - Add customers It takes a large amount of time to run the "Input Wizard". When wizards are run, routes are recalculated, which may take a lot of time. Suggestions: Close all other programs running. Rather than running the wizard, go to feasibility check to check which tables have missing values. After adding all the data, update Model Summary Tables by selecting Update Tables button.