- Library Home /
- Search Collections /
- Open Collections /
- Browse Collections /
- UBC Theses and Dissertations /
- An LP based distribution network planning tool for...
Open Collections
UBC Theses and Dissertations
Featured Collection
UBC Theses and Dissertations
An LP based distribution network planning tool for a large pulp and paper company 2004
pdf
Page Metadata
Item Metadata
Title | An LP based distribution network planning tool for a large pulp and paper company |
Creator |
Lavieri, Mariel Sofía |
Date Created | 2009-11-18 |
Date Issued | 2009-11-18 |
Date | 2004 |
Description | 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. |
Extent | 6520219 bytes |
Genre |
Thesis/Dissertation |
Type |
Text |
File Format | application/pdf |
Language | Eng |
Collection |
Retrospective Theses and Dissertations, 1919-2007 |
Series | UBC Retrospective Theses Digitization Project |
Date Available | 2009-11-18 |
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.0091457 |
Degree |
Master of Science in Business - MScB |
Program |
Business Administration |
Affiliation |
Business, Sauder School of |
Degree Grantor | University of British Columbia |
Graduation Date | 2004-05 |
Campus |
UBCV |
Scholarly Level | Graduate |
URI | http://hdl.handle.net/2429/15278 |
Aggregated Source Repository | DSpace |
Digital Resource Original Record | https://open.library.ubc.ca/collections/831/items/1.0091457/source |
Download
- Media
- ubc_2004-0250.pdf
- ubc_2004-0250.pdf [ 6.22MB ]
- ubc_2004-0250.pdf
- Metadata
- JSON: 1.0091457.json
- JSON-LD: 1.0091457+ld.json
- RDF/XML (Pretty): 1.0091457.xml
- RDF/JSON: 1.0091457+rdf.json
- Turtle: 1.0091457+rdf-turtle.txt
- N-Triples: 1.0091457+rdf-ntriples.txt
- Citation
- 1.0091457.ris
Full Text
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 Datê ) 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 <production capacitŷ + Ey Xy-ji V mills j , products 1 (4.1.2.1) E;,k Xyki ŝtock availableji + E; j- Xyji V DCs j , products 1 (4.1.2.2) Demand and Make-to-Stock Requirements: E ij X^i >demandki V customers k, (4.1.2.3) products 1 E ij X^i >Make-to-Stock requirementŝ 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 capacitŷ V mills and DCs j, (4.1.2.6) transportation modes i Bounds: 0 <Xijki < routes yki V i , j , k , l (4.1.2.7) 16 The objective of the model is to minimize total holding, inventory, handling and transportation costs incurred in a period. A graphical representation of the cost components for each route based on the physical location of the product appears in Figure 12. The constraints are explained in more depth below. ^ Transportation Cost + Holding Cost + Loading Cost + Unloading Cost + Cost of Inventory Cost of Inventory Cost ot Inventory _ A . . A r—; Y v >> 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<y = charge/discharge ratey * number of docks loaded at the same timey * number of working hours per dayj * number of working days per monthj Charge/discharge rate refers to the number of metric tons that can be loaded by a crew, of the size specified, in an hour. Number of docks loaded at the same time refers to how many transportation units can be loaded at the same time at the given location. Number of working hours per month is the product of the number of working hours per day and the number of working days per month. By multiplying these four factors the number of metric tons that can be handled at each location of each product type can be calculated. • Bounds: Constraint 4.1.2.7 ensures that products are only shipped on feasible routes. If a route is infeasible, routeSjjki will equal 0 (which ensures that nothing is sent on that route). On the other hand, if a route is feasible, routespi will equal a large number (mode capacity^). A route is assumed to be feasible if the following three conditions are satisfied: 1. Al l data is available for the given route 2. The transportation mode can be handled at both the source and the destination 21 3. The route is not infeasible, that is, the company has not specific reasons to avoid the route. 4.1.3 Model Assumptions The model is based on the following assumptions: • A production plan is given: It assumes that production capacities at all mills, as well as the amount of make-to-stock to be shipped in and out of each distribution center per period, are given. This information is based on historical data, and may result in a sub-optimal model since it does not allow for feedback between production and distribution plans. • Demand is assumed to be deterministic: Given that the model is designed for strategic decisions, period demand is obtained as an average of historical data. • Fixed costs are omitted: Fixed costs in the system are approximated as variable costs (cost per metric ton). Given the size of the problem, this approximation should not have a large impact in the solution. • Items are not damaged: Given the low probability of damaging an item each time it is handled, this factor is not included in the model. • Al l modes are assumed to be independent, that is, loading capacities of a mode do no affect loading capacities of other modes. Since that is not always the case, specific constraints can be added to the model for modes that share docks and/or other resources. 22 4.1.4 Data Requirements The main inputs to the model are as follows. They can be varied for sensitivity purposes to asses their impact: • number available^. Number of transportation units i (barges, trucks, etc.) available each period. For those modes that have unlimited capacity, a very large number (10,000 transportation units) is assumed. • capacity per tripy (MT): This value refers to the maximum load that can be shipped by each transportation unit i in and out of each location j. • production capacityji (MT): Average MT that can be produced at each mill j of each product type / in a period assuming that other product types are also being produced at the given mill in the same period. • stock availableji (MT): Average replenishment inventory shipped out of each DC j of each product type / per period. • charge/discharge ratey (MT/hour): For each transportation unit i , this value refers to the rate at which products are loaded/unloaded at each location j. For the purposes of the model, it is assumed that loading and unloading rates are equal. • number of docks loaded at the same timey: Number of transportation units i that can be loaded at the same time at each location j. • number of working hours per dayj (hours): Average number of hours that are spent loading/unloading products into the transportation modes at each location j per day. 23 number of working days per monthj (days): Average number of days that are spent loading/unloading products into the transportation modes at each location j per month. This value is calculated as follows: number of working days per monthj = number of working days per weekj * 30 _ demands (MT): Average MT of each product type / sent into each customer region k per period. Make-to-Stock requirementski (MT): Average MT of make-to-stock (MTS) products / required at each DC k per period. This value is from hierarchical data based on the balance of inventory equation, that is, for each month m, Make-to-Stock requirementskim are obtained as follows: Make-to-Stock requirements k i m = stockk,m - stockk,m-i + stock availablek,m The total metric tons shipped each month into each distribution center should be equal to the difference in stock between the end of the previous month and the end of the current month plus the stock shipped out of the distribution center in the current month m. Make-to-Stock requirementski are calculated as an average of monthly Make-to-Stock requirements. Transportation Costyk (US$): For each route and mode, this value is obtained by adding all costs incurred from the moment the transportation unit i departs from the source j to the moment it arrives to the destination k. For those routes for which costs are not available, it is assumed that the transportation cost from j to k is equal to the transportation cost from k to j . To avoid including infeasible routes, the user may manually establish that the route is infeasible, even if the transportation cost of the route is available. 24 Cost of Inventory per Month (US$/MT/month): Cost of capital incurred for each MT in the system per month. Cost/hour/personj (US$): Labor cost incurred in loading and unloading products into the transportation units per person per hour at each location j. crew sizeif. Average crew size needed at each location j to load/unload products into each transportation unit i. Holding Cost per Dayj (USS/MT per day): For those warehouses not owned by NorskeCanada, this value refers to the variable cost incurred in holding one MT per day at locations j. Cost Factors: Some costs are proportionally higher or lower depending on the location j and/or the product /. Therefore, costs are expressed in terms of a baseline and multiplied times the respective cost factor. The main cost factors included in the model are: o Holding Cost Factor•/ o Inventory Cost Factori o Handling Cost Factorp storage timey (days): This term refers to the average number of days products are stored in the given mill j or distribution center before they are shipped to another location by transportation unit i. transportation timeyk (days): This term refers to the average number of days it takes to ship a product from the source j to the destination k by a given mode i. 25 • dead heady (days): Dead head is related to the number of days each transportation unit i takes to go back to the source j to pick up a new shipment. 4.1.5 Model Outputs After the linear program has been solved, the model outputs are summarised as follows6: • Cost/MT: Each cost component (transportation, inventory, handling and holding cost) is divided by the total MT in the system to calculate cost per MT. The sample graph (see Figure 15) shows that transportation costs play a key role in the system. Cost Composition us $; )S t/M T l O i ^ H l l Transportation Cost/MT Cost of Inventory/MT Inventory Handling/MT Holding Cost/MT Cost Component Figure 15: Sample Graph of Cost Components by Type (Scale not Displayed) • Transportation Usage: In order to understand which modes are being used and which products are shipped by each mode, transportation usagen is calculated: 6 T o p ro t ec t d a t a con f iden t i a l i t y , ou tpu t s a re inse r t ed w i t h o u t de ta i l s u m m a r i e s . 26 transportation usageu = Hj^Xyu Vi, I transportation usageu is, therefore, the total MT of each product type / transported by each transportation unit i. Results are plotted in the Total MT versus transportation mode graph. • Last Leg MT: This table is created to summarize the total metric tons transported in the last leg7 for each product type, source, destination, and mode. • MT Handled at Each Location: The metric tons of each product type arriving o and departing from each location are calculated by the following formulas : V k ^Distribution Center and k ^Distribution Center - Make-to-Stock, 1: MT handled INu = E ij Xyki Else, V k = Distribution Center, 1: MT handled INkt = E u Xijkl + E ̂ XiJk+dc+Ci, s.t dc — number of distribution centers, c - number of customers MT handled OUTji = 1,^X^1 Vj, I Furthermore, for each location, the total MT handled is assumed to be the maximum of the metric tons that arrive into the location and the metric tons that depart from the location. • Routes Used: Decision variables that have a value greater than 0, as well as their cost and lead times, are extracted from the solution. 7 Last leg refers to all those routes in which the destination is a customer region. In the company, all information is usually summarized by the last leg. 8 O u r analysis assumes that routes are of the following structure: Mills, Distribution Centers, Customers, Distribution Centers - Make-to-Stock. and that all distribution centers may handle both make-to-order and make-to-stock products. 27 • Routes by Customer: A program was written using Visual Basic to determine possible global routes (routes that include transhipments) from the mill or the distribution center to each customer region based on the decision variables (segments) that are part of LP solution. Working backwards from the LP results, global routes are obtained by assuming that a global route is only feasible if at least one metric ton is shipped in each of its segments. The possible global routes considered are: o Mill to Customer o DC to Customer o Mill to DC to Customer o Mill to Mill to Customer o DC to DC to Customer o DC to Mill to Customer o Mill to DC to DC to Customer The metric tons shipped in each global route is assumed to be the minimum of the metric tons shipped in each of its segments. Costs and lead times for the routes are calculated as a weighted average of the cost or lead time times the metric tons shipped in each of its segments. From the perspective of the system's design, this is the key output. This information is also summarised based on the last leg mode. • Scenario Comparison: The decision variables and total cost of each scenario is recorded for later comparisons. 4.2 Model Implementation Given the size and complexity of the LP, it was necessary to follow a systematic process in developing the model. The main steps involved in creating and implementing the model are summarised in Figure 16. 28 - N o - Understand process Formulate basic mathematical model -Yes- - N o - Check whether model is expandable Validate individual expansions -Yes- - N o - Formulate expansion interface Validate expansions -Yes- - N o - Use interface to expand model Yes- - N o - Formulate Check pilot model extremes -Yes- Validate model Input As-Is data into model Other validation techniques Formulate pilot model using another software Modify model and interface Perform what-if analysis Figure 16: Process Map of Model Formulation and Validation 29 As mentioned earlier, before creating the LP model, it was necessary to understand the system. This was accomplished by performing site visits and numerous interviews of personnel that had extensive knowledge in the following areas: • Barge coordination • Rail coordination • Trucking coordination • Break bulk coordination • Container coordination • Distribution Center activities • Mill activities • Production planning • Customer service • Cost structure of the whole distribution system The creation of process maps and the lead time study were also key milestones in our understanding of the problem. Next, it was decided to model the system using linear programming. The mathematical model was formulated and discussed with the company. Changes in the decision' variables and the constraints included in the model where made at that time. After the model had been formulated, and the company had agreed that the model was valid, a pilot version (with only two modes, four sources, six destinations and four products) was set up in MS Excel. MS Excel was chosen given the interface capabilities of the software. Premium Solver Platform with Xpress Solver Engine (by Frontline System Inc.) was selected due to its compatibility with Microsoft Excel. The pilot model was validated by analysing the effect that extreme values (such as very high/very low demands, high capacities, low transportation cost, etc.) as well as other changes in the input had in the solution. The feasibility of the solutions was also validated. In addition, the problem was formulated in AMPL (see Appendix 2). 30 Using Visual Basic, a program was created that extracted all input values from the Excel Spreadsheet and created the dataset to be input into AMPL. Another program was created to extract the solution obtained using the AMPL solver and input it into the Excel Spreadsheet. By obtaining the same solution using both the Xpress Solver Engine and AMPL solver further validation of the model was achieved. However, it was decided to use Xpress Solver Engine as the recommended engine for the tool given its ease of use. The next step, after the validation techniques described above had shown that the pilot model was valid, was to manually expand the model. Independently, a mill, a distribution center, a customer, a mode and a product were added to the model. The expanded models were considered valid if the pilot model solution was obtained when one of the previous mills, distribution centers, customers, modes and products of the expanded model were set to zero. The steps followed when manually expanding the model were tracked down for future use (see Appendix 3). A program was then written using Visual Basic that allows the user to add a mill, a distribution center, a customer, a mode or a product by only pressing a button. This program was based on the steps that had been followed when the model had been expanded manually. It was validated by comparing the results obtained when a model expanded using the interface was solved versus when a model expanded manually was solved. The interface was assumed to be valid if the same results could be obtained. When the Visual Basic program had been validated, the interface was used to expand the model to resemble as close as possible the As-Is system. Data was collected from the ERP system and validated by NorskeCanada personnel. A program was written to determine missing data as well as data given for impossible routes. Some data that was not available, had to be collected directly by the company. In addition, the main results obtained from the lead-time study (such as median lead time for the different routes) were input into the model. 31 The final model had 17,408 decision variables and 17,689 constraints (including upper bounds). Changes in the dimensions when additional items are added to the model can be observed in Figure 17. Note that, in the table, the number of constraints includes upper bound constraints. For number of constraints calculation purposes, it is assumed that all modes are independent. Mills DCs Cust. Modes Prod. Decision Variables Constraints 4 4 20 17 4 17,408 17,689 4 8 20 17 4 32,640 33,021 4 4 24 17 4 •19,584 19,881 4 4 20 21 4 21,504 21,821 4 4 20 17 8 34,816 35,225 Figure 17: Model Dimensions Afterwards, the expanded model with the inputted data was solved, and the solution was validated by the company. The analysis concluded with some preliminary sensitivity analysis, training users before the model handed off to the company. One of our key concerns was that the model would be used by non-LP-expert personnel. Therefore, it was necessary to create a user friendly interface to facilitate the usage of the model. When the model is initially launched, an introductory screen lets the user decide whether to perform changes to the model, solve the model, or view the outputs. Screen shots of some of the menus can be observed in Appendix 4. A user manual was also developed, and it can be observed in Appendix 5. 32 Each time the "Solve" option is selected, a quick feasibility check of the model is performed. In those cases where the model is not feasible, the interface guides the user through some possible infeasibility reasons. The feasibility check can also be accessed from the What-If analysis menu, and it consists of the following steps: • Production capacity is sufficient to satisfy the demand: The total production capacity for each product type should be greater than or equal to the total demand of that product type, that is: Lkdemandki <E/ production capacityy VI • Handling capacity is sufficient to satisfy the demand: For Make-to-Stock demand, the handling capacity at all mills should be at least the sum of the make-to-stock demand at all distribution centers: Efc / Make-to-Stock requirementsu ^Ey handling capacityy Vi,j = mill, k = Distribution Center - Make-to-Stock, / On the other hand, assuming that make-to-order demand has to be satisfied at the same time as make-to-stock demand, the sum of the handling capacity at all mills and distribution centers minus the handling capacity consumed by the make-to-stock demand should be at least the sum of the demand at all the customer regions: Ehidemandki ^Ey handling capacity y - E ,̂/ Make-to-Stock requirementsu Vi,j, k = Customer, I • Transportation capacity is sufficient to satisfy the demand: The total transportation capacity at each distribution center and customer should be sufficient to satisfy its demand: 33 Ei Make-to-Stock requirementsu ^Etj mode capacityVk = Distribution Center - Make-to-Stock Ek,idemandki ^Etj mode capacity^ Vk = Customer • Al l data has been inputted into the model: It is checked that there are no missing values in the tables. 4.3 Results Based on the fact that a more cost effective distribution plan could be obtained by solving the As-Is model, it was decided that the current system was not optimal. Routes and transportation modes not typically used were suggested in order to minimize total cost. Several as-is scenarios were studied. They were created using the interface developed, and they include: • Remove all DCs from the model and ship directly from mills to customers • Leave only those DCs in the model that are located in the Lower Mainland • Include break bulk contract in the model, which establishes that there is a lower bound on the number of metric tons to be shipped by break-bulk per period • Modify lead times and other input data It was concluded that removing all DCs from the system may actually increase total cost. Break bulk contracts may be costing the company money. In addition, a reduction in lead times will result in a reduction of costs and, in some cases, a selection of new routes. 34 5. SUMMARY In this thesis we have developed a high-level linear programming model of the distribution network of NorskeCanada. This LP model has been designed to be used for strategic decisions based on tradeoffs between time and cost. Lead times are assumed to play a key role in both capacity and cost calculations. Extensive thought was given to the model creation and validation methodology. By automating the model expansion, we have ensured that the model is not only mathematically valid, but it is also ideal for scenario and "what-if' analysis. Using the tool, we were able to analyze and evaluate the effect of possible changes that could potentially reduce distribution lead times and distribution costs. There are several ways this tool could be expanded: • Integrate the production planning into the model: As previously mentioned, the current model does not allow for feedback between production and distribution planning. By incorporating production planning in the distribution model, we may be able to ensure that the optimal solution minimizes costs and lead times of both the production and the distribution system. • Expand the model to perform operational decisions: This model could be expanded to include the greater level of detail necessary for operational decisions. Some of the changes that would need to be done include: o Take into consideration the variability of demand and incorporate forecasting into the model, o Use a greater level of disaggregating the customers, o Incorporate transportation scheduling and inventory decisions in the model. 35 • Include fixed costs and other pricing schemes: This will require incorporating integer variables in the model, which may increase its complexity and computing time significantly. i 36 REFERENCES Chase, R. B. et all 2001, Operations Management for Competitive Advantage 9 Edition, McGraw-Hill Irwin, New York Cummings, S. 2000, VBA for Dummies, IDG Books Worldwide, Inc., California. Dempster, M . A. H. et al 1981, Analytical evaluation of Hierarchical Planning Systems, Operations Research, 29:4, Jul.-Aug. 1981, 707-716. Epstein, R. et al 1999, Use of OR Systems in the Chilean Forest Industry, Interfaces, 29:1 Jan.-Feb. 1999, 7-29. Lonnstedt, L. 1972, Characteristics of Some Swedish Companies Using O.R., Operational Research Quarterly, 23:2, Jun. 1972, 226-229. Naslung, B. 1970, Combined sea and Land Transportation, Operational Research Quarterly, 21:1, March 1970, 47-59 Perry, G. 1999, Teach Yourself Visual Basic 6 in 21 Days. Professional Reference Edition, Sams Publishing, Indianapolis. Van Roy, T. J. 1989, Multi-Level Production and Distribution Planning, with Transportation Fleet Optimization, Management Science, 35:12, December 1989, 1443- 1453. Wu, E. H. 2002, Aggregate Logistics Optimization for a Large Beverage Company, The University of British Columbia. Web site of NorskeCanada http://www.horskecanada.com/ 37 APPENDICES Appendix 1: Process Maps Flow of information for a simple example: Mill -> 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„.. Ĉ 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.
Cite
Citation Scheme:
Usage Statistics
Country | Views | Downloads |
---|---|---|
China | 19 | 0 |
United States | 10 | 0 |
Japan | 6 | 0 |
France | 2 | 0 |
City | Views | Downloads |
---|---|---|
Beijing | 19 | 0 |
Unknown | 10 | 5 |
Tokyo | 6 | 0 |
Seattle | 1 | 0 |
Ashburn | 1 | 0 |
{[{ mDataHeader[type] }]} | {[{ month[type] }]} | {[{ tData[type] }]} |
Share
Share to: