@prefix vivo: . @prefix edm: . @prefix ns0: . @prefix dcterms: . @prefix dc: . @prefix skos: . vivo:departmentOrSchool "Business, Sauder School of"@en ; edm:dataProvider "DSpace"@en ; ns0:degreeCampus "UBCV"@en ; dcterms:creator "Morrison, Laura Jean"@en ; dcterms:issued "2009-07-28T20:42:54Z"@en, "2000"@en ; vivo:relatedDegree "Master of Science - MSc"@en ; ns0:degreeGrantor "University of British Columbia"@en ; dcterms:description """This thesis presents a catch allocation model that was developed for J.S. McMillan Fisheries, Ltd. A linear program (LP) was developed to allocate chum salmon catches of variable size to a set of end products, where both the input salmon and output products are differentiated based on strict physical characteristics and quality requirements. The LP is designed to maximise the net profit of a catch subject to constraints on processing line capacity, market orders, and an upper boundary on production. A decision support tool was built around the LP model to facilitate user-directed reporting and updating of model parameters. The decision tool was shown to improve the structuring of the decision process, decrease the company's reliance on expert knowledge in making the catch allocation decision, as well as significantly decrease the time investment in the decision process."""@en ; edm:aggregatedCHO "https://circle.library.ubc.ca/rest/handle/2429/11411?expand=metadata"@en ; dcterms:extent "3910271 bytes"@en ; dc:format "application/pdf"@en ; skos:note "DECISION-MAKING IN THE FISHERIES INDUSTRY: DESIGN OF A CATCH-ALLOCATION DECISION TOOL by L A U R A J E A N M O R R I S O N B .Com. (Operations Management), University o f Alberta, 1999 A THESIS S U B M I T T E D I N P A R T I A L F U L F I L L M E N T OF T H E R E Q U I R E M E N T S F O R T H E D E G R E E OF M A S T E R OF S C I E N C E in T H E F A C U L T Y OF G R A D U A T E S T U D I E S (Department of Commerce & Business Administration) We accept this thesis as conforming to the required standard T H E U N I V E R S I T Y O F B R I T I S H C O L U M B I A December 2000 © Laura Jean Morrison, 2000 In presenting this thesis in partial fulfilment of the requirements for an advanced degree at the University of British Columbia, I agree that the Library shall make it freely available for reference and study. I further agree that permission for extensive copying of this thesis for scholarly purposes may be granted by the head of my department or by his or her representatives. It is understood that copying or publication of this thesis for financial gain shall not be allowed without my written permission. Department of fcrYUrtt/TJ Uki^/nrfiimniliAClf)^ ) The University of British Columbia Vancouver, Canada Date i a / Q S / f f c DE-6 (2/88) A B S T R A C T This thesis presents a catch allocation model that was developed for J.S. M c M i l l a n Fisheries, Ltd. A linear program (LP) was developed to allocate chum salmon catches of variable size to a set of end products, where both the input salmon and output products are differentiated based on strict physical characteristics and quality requirements. The L P is designed to maximise the net profit o f a catch subject to constraints on processing line capacity, market orders, and an upper boundary on production. A decision support tool was built around the L P model to facilitate user-directed reporting and updating of model parameters. The decision tool was shown to improve the structuring of the decision process, decrease the company's reliance on expert knowledge in making the catch allocation decision, as well as significantly decrease the time investment in the decision process. n T A B L E O F C O N T E N T S A B S T R A C T ii L I S T O F F I G U R E S iv L I S T O F T A B L E S iv A C K N O W L E D G E M E N T S v I. Project Background 1 1.1 INTRODUCTION TO S A L M O N C A T C H A L L O C A T I O N PROJECT 1 1.2 T H E FISHERIES E N V I R O N M E N T 2 1.2.1 Industry in British Columbia 2 1.2.2 Salmon in British Columbia 3 1.2.3 Salmon Catching Methods 3 1.2.4 Salmon Processing Methods 4 1.2.5 Product Valuation & Marketing 5 1.3 C O M M E R C I A L FISHING & O P E R A T I O N S R E S E A R C H : R E V I E W OF C U R R E N T L I T E R A T U R E 7 1.3.1 Fisheries Processing Applications 7 1.3.2 Other Fisheries Applications 8 II. Defining the Catch Allocation Problem 9 2.1 C O M P A N Y O P E R A T I O N S 9 2.2 N A T U R E OF T H E P R O B L E M 11 III. Approach & Methodology: 12 3.1 A N A L Y S I S OF D E C I S I O N - M A K I N G P R O C E D U R E S 1 2 3.2 M O D E L C O N S T R U C T I O N & V A L I D A T I O N 15 3.2.1 User Input Parameters 18 3.3 S U R V E Y OF L P SOLUTION ENGINES 19 3.4 T O O L CONSTRUCTION 2 0 3.4.1 Linear Program 21 3.4.2 User Interface 22 IV. Analysis & Results 30 4.1 T E S T I N G OF D E C I S I O N T O O L 3 0 4 .2 P R E S E N T A T I O N TO J .S . M C M I L L A N 3 0 4.3 A L L O C A T I O N S C E N A R I O S O L V E D P R O B L E M 31 4.4 A R E A S FOR F U R T H E R R E S E A R C H 3 4 R E F E R E N C E S 36 A P P E N D I X 1: P R O C E S S F L O W D I A G R A M S 38 A P P E N D I X 2: C H U M S A L M O N - S U M M A R Y O F P R O D U C T S 39 i i i L I S T O F F I G U R E S F I G U R E 1 : T I M E L I N E O F T H E C A T C H A L L O C A T I O N D E C I S O N 13 F I G U R E 2 : T H E L P A S E N T E R E D I N T H E S O L V E R D I A L O G B O X 2 2 F I G U R E 3: T H E C A T C H A L L O C A T I O N D E C I S I O N T O O L S P L A S H S C R E E N 2 3 F I G U R E 4: T H E R E C O M M E N D A T I O N T A B 2 4 F I G U R E 5: A N E X C E R P T F R O M A C A T C H A L L O C A T I O N R E P O R T 2 4 F I G U R E 6: T H E C A T C H C H A R A C T E R I S T I C S T A B 2 5 F I G U R E 7: T H E E D I T S C R E E N F O R C A T C H C O M P O S I T I O N E S T I M A T E S 2 6 F I G U R E 8: T H E P R O D U C T P A R A M E T E R S T A B 2 7 F I G U R E 9: A N E X C E R P T F R O M T H E E N T R Y S C R E E N F O R D A Y 1 P R O F I T 2 7 F I G U R E 10: A N E X C E R P T F R O M T H E E N T R Y S C R E E N F O R R E C O V E R Y R A T E S 28 F I G U R E 11: A N E X C E R P T F R O M T H E M A R K E T O R D E R E N T R Y S C R E E N 2 8 F I G U R E 12: T H E C A P A C I T Y S E T T I N G S T A B 2 9 F I G U R E 13: T H E V I E W I N G S C R E E N F O R P R O C E S S I N G L I N E C A P A C I T I E S 2 9 F I G U R E 14: P R O C E S S I N G L I N E C A P A C I T I E S 3 2 F I G U R E 15: R A N K I N G O F P R O D U C T S B Y P R O F I T 3 2 L I S T O F T A B L E S T A B L E 1: S U M M A R Y O F C A T C H C H A R A C T E R I S T I C D E T A I L S 10 T A B L E 2: D E S C R I P T I O N O F D A T A O B T A I N E D F R O M J S M 31 T A B L E 3: A V E R A G E P R O D U C T R E C O V E R Y R A T E S 33 T A B L E 4: C A T C H S I Z E A N D C H A R A C T E R I S T I C E S T I M A T E S 33 T A B L E 5: O P T I M A L C A T C H A L L O C A T I O N P L A N F O R S O L V E D P R O B L E M 3 4 iv A C K N O W L E D G E M E N T S There are several individuals whom I would like to acknowledge for their contribution to the success of this project and thesis. From the Centre for Operations Excellence (COE), I would like to thank Dr. Martin L . Puterman, Stephen Jones, and Lauren Gray for the time and effort they have given in support of my thesis, industry project, and education. I would like to extend my deepest gratitude to my thesis supervisor, Dr. David Glenn, for all the invaluable time, feedback, and guidance he provided over the course of writing this document. J.S. M c M i l l a n Fisheries Ltd. receives my sincerest appreciation. I would like to thank Steve Parkhill and Paulli Bodmer for allowing me the opportunity to work on an applied project for their company, and providing so much support and encouragement. I would also like to acknowledge Sean Baird for helping me out of some trying situations with the V B A programming. Heartfelt thanks, too, to Christopher Blake, for kindly providing a warm place for my \"writing vacation\" this past October. v I. Project Background 1.1 Introduction to Salmon Catch Allocation Project This thesis describes the process of developing a decision-support tool for J.S. M c M i l l a n Fisheries Ltd. It represents a joint project undertaken between J.S. M c M i l l a n and the Centre for Operations Excellence of the Faculty of Commerce & Business Administration at the University of British Columbia in July 2000. J.S. M c M i l l a n Fisheries Ltd. (JSM) is a Vancouver-based company pursuing integrated commercial fishing activities in the waters off o f British Columbia and Alaska. The focus of the company's current operations is procurement and processing of pacific salmon. These activities are directly related to the company's key operational decision: how to allocate a catch of salmon among their product lines so as to maximise the total profit o f the catch. J S M approached the Centre for Operations Excellence (COE) with the catch allocation problem upon recognising the value that formal quantitative-based techniques for decision-making could bring to their operations and profitability. Over the company's history, the decision about how to best allocate a catch of salmon of varying characteristics amongst a set of final products has been made based on intuition and knowledge of past outcomes, and then fine-tuned with expectations about the business environment and global market. A s the company's operations evolved, expanding operations, markets, and products, the number o f different variables involved in this decision has increased. Unt i l recently, the owners of the company, a pair of brothers who grew up working in this family business, have tackled this task. The brothers are eagerly looking forward to the day when they can retire from the day-to-day operations of J S M , and have been working to pass the decision-making responsibility to the company's president. Making an \"optimal\" or good decision using this basic technique has become increasingly challenging. A s an example, for chum salmon alone J S M offers fifty-two different products, most of which have very strict appearance and quality requirements for the individual salmon that make those products. The classification of chum based on appearance and quality has resulted in forty-eight differentiable types of chum salmon inputs. A s the number of possible input salmon - output product pairings increases, it is harder to have confidence that the decision that one has made is the most fortuitous one. The purpose of the catch allocation model developed at the C O E is to overcome the difficulties of making the catch allocation decision manually. The goal is to provide a customisable decision-support tool to automate a quantitative decision process, with the resulting decision being made with more confidence in a more timely manner. To set the framework for examining this decision, the next section in this thesis is a brief summary of the main elements in the business of commercial fishing. This section was 1 included to introduce the environment in which companies like J S M and its competitors operate. The chapter concludes with an examination of applications of operations research in the commercial fisheries industry. Excepting models related to conservation and other biological/ecological matters, this section attempts to briefly describe interesting applications of operations research to fish processing and other areas in the industry. The decision-making framework for the catch allocation problem is described in Chapter Two. First, a background of J S M and their operations is given, followed by an examination of the different elements in their catch allocation problem. The third chapter describes the methodology and approach used to resolve the catch allocation problem, as well as detailing the steps completed to build a linear programming model and decision tool to support J S M ' s operations. The final chapter of this thesis discusses how the decision tool was tested and the company's reaction to the progress made. A numerical example is provided, and areas for further work on this project and J S M are discussed. 1.2 The Fisheries Environment 1.2.1 Industry in British Columbia The seafood sector of the agri-food industry in Brit ish Columbia has been worth over $525 mil l ion annually in the final years of the last decade. This figure represents the value o f finfish (salmon included), shellfish, and marine plants at the time that they are released from the individual farmers. The wholesale value of the industry, which includes the value-added from processing activities, has been closer to $876 mil l ion. In 1998, 72,500 tonnes of salmon were caught commercially in British Columbia (30,200 tonnes o f wi ld salmon, and 42,300 tonnes o f farmed salmon). The combined value paid to farmers upon harvest was $282.3 mil l ion, and the value added by processing allowed the total catch to reach a wholesale value of $424.9 mil l ion. Despite this, the number of w i ld salmon in British Columbia has been decreasing over time and conservation activities were put in place by the 1998 season. Currently, five hundred and thirty seven companies are licensed for commercial fishing in Brit ish Columbia. Many commercial fisheries are small independent firms, but multinational companies also operate on the pacific coast. There are 159 fish processing facilities in the province, with more than half located in the Lower Mainland Area. The total number of workers in British Columbia's fishing and aquaculture industry is estimated to be slightly more than 12,800, with three thousand workers alone dedicated to processing. 2 J S M is a privately-held company so its financial information is not publicly available. Without this, it is difficult to assess J S M ' s contribution to the profitability of the industry. The company operates two of British Columbia's processing facilities, and employs a workforce of more than a hundred individuals. 1.2.2 Salmon in British Columbia There are six species of Pacific salmon native to British Columbia: Sockeye, Chinook (also known as Spring), Coho, Pink, and Chum. In addition to these wi ld species, Atlantic salmon is widely farmed in this province. Geographically, Pacific salmon territory spans the north Pacific and eastern Arctic oceans, and includes the river systems that empty into each. A salmon's environment is composed of the multiple habitats that are contained within the watershed o f a river system. In British Columbia, the primary salmon stock comes from the Fraser River 's salmon runs. British Columbia's north coast sustains several species o f salmon stock. Chinook, Coho, Pink, and Chum salmon are all found in the Chatham Sound area, near Prince Rupert. Chinook and Coho are also found in the Douglas Channel, Milbanke Sound, Rivers Inlet, and the waters of the Queen Charlotte Islands. The salmon stocks are very plentiful on the south coast of Brit ish Columbia. The fishing areas of Campbell River, northern Vancouver Island, Sunshine Coast, Strait o f Georgia, Vancouver, and Howe Sound are home to all five species of Pacific Salmon. Both Chinook and Coho are found in the Barkley Sound and Nootka Sound waters, while Chinook alone inhabit Clayuquot Sound and Kyuquot Sound. Coho and Sockeye can be caught in the waters surrounding southern Vancouver Island. J S M ' s operations focus on wi ld salmon exclusively, processing three different species. The majority of the salmon processed by J S M is caught in fishing grounds near Prince Rupert, British Columbia and the Alaska coast. For a smaller part o f the season, some fishing takes place off o f the north coast of Vancouver Island. 1.2.3 Salmon Catching Methods Three methods are commonly used to catch salmon in commercial fishing: trolling, gillnetting, and seine netting. Trolling is the method that is most similar to recreational fishing, relying on metal hooks attached to multiple fishing lines, towed from a small boat. Multiple hooks are attached to each line. Gillnetting is the process of vertically submerging a large net from a boat to create a wall-like barrier that entangles the salmon as they attempt to pass through. The process of seine netting involves the fishing boat surrounding a group of fish with a massive net, entrapping the catch. 3 While the gillnetting and seine netting methods are generally more efficient in terms of the volume of fish caught simultaneously, salmon caught by trolling are typically damaged considerably less in the process and can be sold at a higher price. Gillnetting is a risky harvesting method as entanglement in the net can damage gills, leading to rapid death, and shortening the window of opportunity for the salmon to be processed into a quality product. The net also tends to damage flesh, decreasing quality and the price a fish can command on the market. Salmon caught by a seine netting process are more at risk of being crushed under the pressure of being hoisted out o f the water in a net and into an on-boat hold that can contain up to fifteen hundred other fish. One characteristic of large-scale fishing methods is that fish of a different species w i l l inevitably be captured along with the variety of salmon actually sought. The term applied to these unintentionally netted fish is by-catch. While J S M owns a small fleet of their own, many fishermen work for them on a contractual basis. Gillnetting and seine netting prevail as the most popular harvesting techniques among all of J S M ' s suppliers. 1.2.4 Salmon Processing Methods Salmon processing in British Columbia takes several forms. The most popular produce fresh, frozen, smoked, filleted, and canned salmon. Preparation of a fresh salmon is the simplest form of processing. A fresh salmon is one that is sold to the end consumer either completely intact or de-gilled, gutted, and beheaded. I f the fresh salmon has been prepared by the latter method, it is known as a dressed salmon. Fresh salmon are generally packed in ice or in an insulated container to await purchase and consumption. A dressed fresh salmon is processed into a frozen salmon through a cold freezing process. This can take up to twelve hours to complete for each fish, depending on the technology used. Frozen salmon are typically glazed after the freezing process, which creates a layer o f protective ice. The advantage of freezing a salmon over leaving it fresh is that the freezing and packaging process allows the purchase and consumption o f the salmon to be postponed for up to a year, while still yielding a high-quality product. Separating the sides of a dressed salmon from the backbone, and then extracting them whole creates salmon fillets. Normally, each salmon yields a pair o f fillets. Salmon fillets are usually marketed with the skin and tail removed (though this practise is not always true for other varieties o f fish). Time and temperature together effect the quality of processed salmon. After harvesting, salmon undergo a series of changes in muscle tone that result in tissue degradation, so it is imperative to begin processing as soon as possible. A good quality fresh or filleted salmon is iced within an hour of slaughter, and delivered to its final market within 72 -4 96 hours. Frozen salmon are also of higher quality i f the fresh fish enter the freezing process within the sixty-minute time window after slaughter, and are never thawed prior to final sale. Fresh or frozen salmon of superior value are characterised by minimal scale loss and negligible damage to the fins or tail. For transport, salmon are usually boxed and insulated, and loaded onto aircraft or refrigerated trucks or railcars. Because of the significant time and quality considerations for fresh, filleted, and frozen salmon, processing and preparation is sometimes even carried out by the fisherman and farmers themselves or by on-boat processing facilities. The first step in producing smoked salmon involves marinating salmon fillets in a cold brine solution. To enhance the flavour of the marinade, a variety of spices, liquors, and sugars are often added to the solution. There are two forms of the smoking process. I f a salmon is smoked at less than thirty degrees Celsius, the resulting product is cold-smoked salmon. Hot smoking occurs when the smoking process is completed at temperatures of over thirty-seven degrees Celsius. The freshness of smoked salmon, a perishable product, can be extended for the short-term by keeping the salmon frozen, refrigerated, or canned. Canned salmon can be either fresh or smoked. In the case of fresh, dressed salmon are cut into smaller pieces and packed with water or o i l into cans. The meat for canned smoked salmon is prepared from fillets and undergoes the smoking processes discussed above. After the salmon has been packed, the cans are sealed, sterilised, and then cooked in a retort machine. While some canning companies w i l l affix their brand label to the freshly canned salmon, it is common practice for canneries to sell and ship unlabeled canned salmon to third parties for marketing. Canning facilities are land-based operations, with the salmon catch delivered to the processing plant by tender boats. For the journey, the catch is preserved in ice or chilled seawater, though the quality of salmon dedicated to canning operations need not be kept as high as that for selling fresh or frozen. The salmon selected for canning are more likely to have sustained bruising and fin damage. In addition to these products, the roe of chum salmon, a common ingredient in Japanese cuisine, are harvested from adult female fish and sold. Lower quality and bruised salmon are often made into fishmeal and marketed to salmon-farming operations. Round (whole) salmon can be exported to the Mediterranean to be made into fish paste. J S M ' s current product line features a variety of fresh, frozen, filleted, and canned products. A s well , they are actively marketing chum salmon roe. 1.2.5 Product Valuation & Marketing The British Columbia export market for seafood is quite sizeable, totalling over $800 mil l ion in 1998. The majority (fifty-six percent) of the exported goods are destined for the United States, while Japan comes in second, taking twenty-five percent of the 5 available exports. The remaining eleven- percent of the seafood in the export market is allocated to the European Union and other countries in Asia . British Columbia exports seafood to nearly fifty different countries around the globe, in total. This province also actively imports salmon and other finfish. In 1998, when the salmon harvest was very limited by government regulation, more than sixteen thousand tonnes of salmon were imported to meet production commitments for canned salmon products. Currently, the majority of the salmon exported from British Columbia is canned salmon, but this is being challenged by the growing success of aquaculture. Farm-raised salmon are being exported in growing volumes. Their quality makes them very suitable for export whole or dressed. Despite this, much of British Columbia's salmon is marketed domestically. In the case of canned salmon, processors sell to frequently sell the product to distributors who market the product under a brand name. The price that canned salmon commands takes into consideration the results of quality inspections, product colour, and the species of salmon used. The sale of fresh and frozen fish is significantly different. The majority of the product is sold at markets or by auction, which might take place on a dock, in a fish market, or be administered on-line. Buyers base their offers on a combination o f criteria like size, species, grade, catch location, method of catch and processing, delivery date, and credit terms. The result is that the world market price for salmon changes on a daily basis. Farmed salmon, such as Atlantic, almost always commands a greater market price because raising it in the controlled environment guarantees higher quality. The value o f a processed catch of salmon currently does not depend on the identity o f the fishery or processing operator because there is little way for consumers to differentiate between salmon that were caught or processed by one company and salmon that were caught by another. Both fisheries and processors bring their goods to be sold at the auction markets. Wholesalers representing restaurants, catering establishments, and secondary processing operations typically attend the proceedings. The internationalism that characterises the market for Canadian-caught salmon complicates the sales environment. Participating in global markets means that processors have to be cognisant o f the different cultural preferences for prepared salmon. For example, canned salted salmon is produced exclusively for the Japanese market. Salmon processors also expose themselves to foreign exchange risk when they conduct business with buyers from other countries, as well as having to navigate import and export laws and regulations. Buyers in the international market must deal in volumes of significant size in order to justify the costly import costs. J S M markets its products in both the domestic and international markets. Most of the fresh product (dressed salmon and fillets) are sold to customers in the lower mainland of British Columbia. Frozen salmon is regularly destined for other markets in the western 6 Canada and the United States. Salted canned salmon and roe are exported to As ia , and on occasion, whole round salmon is shipped to Europe to manufacturers of salmon paste. 1.3 Commercial Fishing & Operations Research: Review of Current Literature Commercial fisheries and their related operations have provided inspiration for a great deal of published research on applications of operations research/management science (OR/MS) . A survey o f the body of work reveals that research has tended to focus on areas of harvest management, fleet sizing, dispatch, and production planning. There appears to be slightly less emphasis on fish processing in the literature, but there still are several interesting contributions in this area. 1.3.1 Fisheries Processing Applications Randhawa and Bjarnason (1995) address the problem of coordinating trawler-based fishing operations with on-shore processing activities. The authors used the results o f a simulation o f trawler activities to build a linear programming model that determines the labour requirements, inventory balances, and production levels of final products that correspond to trawler expeditions of varying duration. The results of the L P model are used to determine a sensible schedule for trawler expeditions for a commercial fishing company in Iceland. Coordination between trawler and processing operations is desirable because landing large catches or having large and perishable inventories close to the end of the work-week puts a large burden on the amount of labour the processing plant has available over the weekend. The results of this model show that smoothing catch landings over the work-week reduces over-time labour expenses and inventory size fluctuations, which together lower the company's operational expenses. A n L P model developed by Renaud and Yacout (1996) is designed to allocate the tails, claws, and knuckles o f Canner and Market lobsters amongst twelve different consumer products. The objective o f the model is to maximise the revenues for an unidentified company in the commercial fishing industry. This activity is constrained by a limited supply of lobster and limited labour hours available for processing. The model includes a scalar applied to the available quantity of lobster (in pounds) to account for the majority o f its weight being lost to the cooking and shelling process. The results of this analysis were a set o f scenario-dependent profitability situations provided to the company. A n article by Jensson (1988) discusses the issue o f randomness in the supply of raw material as related to constructing a good production schedule for the processing of fish. What is very notable about this article is that it mentions that the end users at the freezing plant initially had trouble trusting the results of a linear programming model and letting it do its work. A t the beginning, some of the model parameters were selected by the plant foremen were too conservative for the model to produce schedules that were as good as the ones they constructed on their own. Some of the processes at the plant with non-linear behaviour patterns could not be included in the model. The foremen had to take 7 time becoming comfortable with the limitations of the model in order to be able to see the value in its linear programming approach. 1.3.2 Other Fisheries Applications A n article by Booman, Parin, and Zugarramurdi (1997) discusses a particularly interesting part o f fishery operations to be tackled by O R / M S : sorting a catch off ish by size prior to processing. Sorting is undertaken at processing plants to meet size standards for the final processed products and machine set-ups. This activity is challenging because of the sheer number off ish that comprise a catch (normally hundreds o f thousands) and the speed at which they flow along a conveyor system off o f the tender boats and into a processing assembly. The authors discuss the problem o f sorting a heterogeneous catch of anchovies in order to reduce the amount of wastage that arises from sending fish through a dressing machine that has been calibrated for fish of a larger size. If the fish can be sorted by size before they approach the dressing machine, it can be re-calibrated in logical stages to match the size of the anchovies. The authors discuss their prototype designs for two machines that automate the task of size-sorting the fish as well as a mathematical cost function they developed to assess the trade-off between improving efficiency and the cost of mechanising the sorting process. The results o f this analysis suggest that there is indeed a cost benefit to size sorting the catch before processing. Mi l l a r (1998) addresses the issue of coordinating catch volumes with uncertain demand. He applies a rolling horizon concept to the planning of fishing schedules. Aggregate schedules are made to reflect estimated supply and demand requirements over a pre-determined planning horizon, but are revised when updated information becomes available about catch volumes and production commitments. Mi l l a r separated this study into two separate parts: a trawler-dispatching problem, modeled as a mixed-integer programming problem, and a simulation to assess cost implications of varying the time differential between the frozen time horizon and the total planning horizon. The conclusion of his investigation was that using a rolling horizon, where the total planning horizon extended beyond the frozen horizon by only a few weeks, was the most cost-effective time frame for minimising the effects of prior catch and demand volume under-estimation. In another article, Mi l l a r and Gunn (1991) examined the issue of fleet scheduling, taking on the problem of scheduling tours of fishing trawlers so as to meet specific catch (or demand) requirements. The authors formulated this problem, for the groundwater fishing in Atlantic Canada, as a mixed-integer programming problem. They then devised an initial heuristic to solve the trawler-routing problem such that each trawler trip would be assigned a catch level, processing plant, and a fishing area. A s well , the heuristic ensured that the trawler takes the least costly route to pass through its assigned fishing ground and then onto its assigned landing plant, from a point of origin. To account for any myopic behaviour in the trawler-routing heuristic, the authors applied several improvement 8 algorithms to the solutions resulting from the heuristic. Their work resulted in reducing operational costs of a specific commercial fishery by approximately thirty percent. II. Defining the Catch Allocation Problem 2.1 Company Operations J.S. M c M i l l a n Fisheries Ltd. is a privately-owned company headquartered in Vancouver, British Columbia. The company was founded in 1953 as a commercial fishing brokerage, facilitating purchases and sales between third parties without getting directly involved with fishing operations. Over time, the company integrated other components of the industry into their business; for example, primary and secondary processing, fleet operations, and distribution. The company currently focuses on processing activities. J S M operates their primary processing facility in Prince Rupert, British Columbia, with a smaller facility in Vancouver. Newly-caught salmon (the focus of this thesis) and groundfish are transported to the processing facilities by tender boat and other smaller craft, while the main fishing craft tend to remain anchored in the fishing grounds. J S M processes sockeye, chum, and pink salmon into three broad product categories: dressed salmon, fillets, and canned salmon. Canned salmon are marketed in a variety o f sizes (one-quarter pound to four pounds) and packages (regular and pull-top cans) for sale to different commercial buyers and general consumers. The company outsources the canning to specialised processors. J S M sells dressed salmon in both fresh and frozen varieties. The size of the fish, skin colour, and overall quality of the fish become important in determining the suitability of a particular fish for sale as either fresh or frozen, as wel l as its profit potential. J S M typically sorts fish into five broad size categories. The skin colour grades for different species of salmon are not set by the fisheries themselves but by their regulatory bodies. There are currently nine such alphanumeric grades for chum salmon, ranging from A (bright) to I (dark). It is convention within the industry to divide the grades into three broader categories. The assessment of overall quality is a combination o f regulatory standards and subjective judgements made by J S M upon inspecting the fish for deterioration, cuts, bruises, seal bites, and other external damage. These characteristics are further detailed in Table 1. 9 Standard Sizing for Chum at J.S. McMillan *head to tail measurement 4 - 6 lbs. 9 - 12 lbs. 6-91bs. 12 +lbs. Consolidated Skin Colour Grades for Chum A - C - highest quality, light-coloured, \"bright\" skin D - F - medium quality G -1 - lower quality salmon, darker, duller skin [Overall Quality Grades 1 These quality grades are awarded subjectively to individual fish upon inspection. A 2 chum awarded Quality 1 might be a very fresh fish with firm flesh and no damage to 3 the flesh, skin, or fins. A Quality 4 fish might be soft, possibly caught farther in the 4 past, with its body exhibiting various seal bites or cuts. Qualities 2 or 3 are assigned to fish between the two extremes. Table 1 : Summary of Catch characteristic details for sizing, skin colour, and quality of chum. Fresh salmon are marketed based on several of these specific characteristics (high quality, certain sizes and skin colours) or as \"ocean run\". \"Ocean run\" is the term applied to fish that are good quality, but have not been sorted specifically for size and skin colour. Fresh salmon are sold individually, in fifty-pound packages, or in totes of one thousand pounds. Some dressed salmon are frozen for immediate sale and distribution, for later sale, or simply to preserve the quality of the fish for processing into canned salmon outside of the peak fishing season. The frozen salmon product line is J S M ' s broadest. Salmon are categorised by size, skin colour, and quality, as are fresh salmon, and also by flesh colour and whether the product is sold with the head attached or removed. The sizing of frozen salmon also is sometimes based on the five categories described above, or else simply described as being under or over a nine-pound standard. A l l o f these possible combinations create a total of thirty-six differentiable frozen salmon products. J S M makes five different types of fillet products. The two main varieties, Type 1 and Type 2, are distinguished by differences in quality, where the Type 1 fillets are of superior quality. Two secondary products come about from \"screening\" the catch before processing. Before processing, the roe of female chum salmon are harvested and packaged for export to Asia . L o w quality and damaged salmon are separated from marketable fish and processed into fishmeal at a reduction plant. Complete listings of the fifty-two products in the catch allocation model are included in Appendix 2. 10 2.2 Nature of the Problem A s discussed earlier, J S M regularly has to determine how to allocate a catch of salmon among the set of end products so as to maximize the profit from their sale. Deciding how best to go about doing this is no simple matter. Making a good decision is complicated because subsequent decisions are not mutually exclusive. Making the decision to allocate part of a catch to a particular product affects all o f the future decisions that can be make with regard to the remaining catch and the other products. With so many possible ways to allocate the catch, it is hard and time-consuming to manually evaluate whether a complete set of allocations that seem good could be improved upon by re-allocation. The reality of the business environment is that the number of variables that the company must consider when allocating the catch has expanded, and as the company has developed, the decision framework has grown increasingly complex. Allocating the entire catch to the most profitable product would easily meet the criteria of maximum profitability. However, this simple allocation strategy is not possible because not all salmon are suitable for processing into all products. For example, it might happen that freezing bright, twelve-pound chum salmon yields the most profit. Certainly, all o f the twelve-pound chum with a skin colour index in the A - C range could be allocated for freezing. A l l other fish in the catch are unsuitable for this, however, and must be allocated to other products for processing. Product commitments made to customers also constrain the catch allocation decision. JSM's processing operations have to satisfy product commitments that were arranged by the marketing department. While it might not be in the company's immediate financial interests to allocate part of a catch to products that are not the most profitable choices, not fulfilling customer-specified orders might jeopardise future business agreements and long term financial success. The capacity of the processing lines also constrains the catch allocation decision. In a particular period of time, there is a limit to the amount of salmon that can be processed into each product, most profitable or otherwise. I f the input was not time-sensitive this might not be an issue. The company could process only the most profitable product on the same processing line for days on end. In reality, the quality of a salmon is significantly influenced by the number of hours between when it is taken out of the ocean and when it reaches the processor or consumer. The price that it w i l l command at market reflects its quality. Thus, it is reasonable to process as much salmon for the most profitable product as there is capacity available to do so and demand for the product. Because of the impact of time on the quality of the fish, it is also financially sensible to process other high-quality products concurrently instead of extending the processing of what w i l l become lesser quality (and less profitable) product as time passes. For frozen products, JSM utilises frozen storage facilities, but their capacity to hold product over time is limited. 11 The company's senior management makes the catch allocation decision. The time-window for decision-making is relatively great. The company is typically notified with between twenty-four to forty-eight hours in advance that a tender boat has left the fishing grounds to bring a catch in for processing, but sometimes it is as few as five or six hours. A typical delivery ranges between three hundred thousand and one mil l ion pounds of fish. A t this early time, the basic characteristics of the catch (species, average length, and weight) are reported. Because of the seasonality of salmon runs, with the exception of a small by-catch, the deliveries are homogenous in species. III. Approach & Methodology: The characteristics of the catch allocation problem suggest that a linear programming approach may be effective for modelling the problem. B y combining this computational technique of management science/operations research with the functionality of a spreadsheet environment, a decision support system can be designed to interactively calculate the profit-maximising catch allocation subject to the current market prices for prepared salmon. Designing the tool within the MS Excel spreadsheet environment (as requested by the users due to familiarity) allows the design of a graphical interface through which users can update model parameters with ease as appropriate. Designing a user-interface for the model eliminates the need for the user to have an intimate understanding of the mechanics of the L P engine available in MS Excel. More importantly, the interface relieves the user of the task of altering the model directly to reflect changes in the problem's parameters, a task that sometimes can lead to overwritten formulas and model malfunctions. A user-interface also allows concise control o f the model output. Even though J S M processes pink, chum, and sockeye salmon, the decision was made to build a preliminary decision tool focused on chum salmon specifically. The rationale for this was that the company expected a chum opening to occur in October 2000, which coincided with the projected model completion time. The development of this decision-support tool spans four phases: i . Analysis o f the decision-making procedures i i . Model construction and validation i i i . Survey of L P solution engines iv. Tool construction 3.1 Analysis of decision-making procedures Historically, the catch allocation decisions at J S M have been the responsibility of two brothers. The M c M i l l a n brothers have been involved in the family business since its inception, and through their experience and knowledge of the inner workings of the salmon market, rely on past history and intuition to decide how to best allocate a salmon 12 catch. This process, relying on manual calculation, is considerably time-intensive and still might not result in the most profitable catch allocations. A s catch allocation decision-making continues to increase in complexity and the brothers become less involved, the manual decision-making technique has become too risky to rely on. Recently, the president of J S M has tried to introduce quantitative techniques for making the catch allocation decision, believing that an analytical approach w i l l lead the company into greater profitability. More importantly, it might reduce their dependence on expert knowledge and expedite the decision-making process. The initial complexity in allocating a catch comes from the uncertainty surrounding the inputs. Salmon come in various species, sizes, conditions, etc., and commercial fishing techniques do not discriminate between them. A s the characteristics of an individual salmon are important in deciding how it should be marketed, J S M estimates how the catch w i l l decompose into various categories of species, size, condition, and so on. These estimates are revised at several phases throughout the decision-making period, until the actual situation can be assessed. These phases and the events they contain are depicted in Figure 1. Appendix 1 contains a companion process flow diagram. Phase 1 Phase II Phase III Phase IV , A A A A ( ^ C \\ r \\ r ^ Dept of Fisheries & Catch estimates are Catch is unloaded and Catch estimates are Oceans announces an revised based on report. sorted at plant. revised based on opening. flesh-colour Cannery is booked in Catch estimates are observations during Boats travel to fishing anticipation. revised based on dressing. grounds to harvest information from catch. Additional frozen storage sorting. space is booked in Estimates for catch advance, if need descriptors are anticipated. estimated. Marketing Department calls customers for orders. 4 8-36 hours ^. ^ 1-3 hours ^. Milestone Receive Catch Catch is landed Processing Events: Report Begins Figure 1 : Timeline of the Catch Allocation Decison, with milestone events 13 The decision process begins when the Department of Fisheries and Oceans announces an opening. A n opening gives companies and independent commercial fishermen permission to fish a certain species in a certain area, for a specified length of time. A t this point, J S M makes their first estimates for the composition of the catch, and begins to plan how it w i l l be allocated to final products and the appropriate processing lines. The initial estimates are developed based on the location of the fishing grounds, time of year, and catch trends. A t the end of the opening, JSM receives a \"catch report\" from the fishing grounds. Receiving the catch report is a key event. The catch w i l l arrive at the processing plant within eight to thirty-six hours (typically) after receipt of the report, depending on the location o f the fishing grounds. The report is also significant as it relays some of the rough details about the actual catch, particularly the volume of fish caught and their average weight. These details assist the company in revising their initial estimates, and further planning their production. A t this time, the company's marketing department w i l l contact potential customers, and arrangements with the labour force w i l l be made. The next key event in the decision framework is the landing o f the catch. The salmon are unloaded from their boats onto a system of conveyors that run from the dock into the processing plant. A s this happens, the catch is sorted by gender and size (as best as possible, considering the speed of the conveyor). A t this point, the estimates of the catch characteristics are revised yet again, prompting adjustments to the production plan. With only a handful of hours between unloading and sorting activities and the beginning of processing, these adjustments have a \"last minute\" quality. Processing is the last milestone activity in the decision-making framework. B y this time, the catch has been allocated to one of the product lines (based on the estimates of size, skin colour, and quality) but an element of uncertainty remains. The colour of a salmon's flesh is a factor which influences its value as a fresh, frozen, or filleted product. The problem is that the flesh colour cannot be determined from a salmon's external appearance. In every catch, a small proportion of the fish are redirected from the dressing and filleting machines because their flesh turns out to be too pale to command a good price at market. Processing flows are depicted in Appendix 1. The introduction of the catch allocation decision tool w i l l expedite this decision process. A t each point in the decision framework where the estimates o f the catch characteristics are updated, the estimates can be entered into the decision tool. The decision tool w i l l be used many times throughout the decision process, generating revised allocations plans that increase in quality as the estimates increase in accuracy, in considerably less time. 14 3.2 Model Construction & Validation The catch-allocation L P model is formulated algebraically as follows: Max ^CiJk-djXiJk Subject to: ^ ^ Xilk < Kck V t , where C = the set of indices for canned salmon i jeC ^Xijk < KRk V t , where R = the set of indices for salmon sent to reduction i jeR y Xi!k < KHk V t , where H = the set of indices for salmon sent to Rycol Handline ^ y Xijk < KFk V t , where F = the set of indices for salmon sent to the filleting line i jeF Y d j ^ ^ D j k V . i k Indices: i = type of salmon, differentiated by a unique combination of size, skin colour, and quality (forty-eight in total) j = unique final product (fifty-two in total) k = shift number, assuming two shifts in each of three days (six in total) The index sets C, R, H, and F, defined in constraints (2 - 5) of the model above, represent the set of end-products processed in canning, reduction, Ryco/Handline (processing line for dressing salmon), and the filleting line respectively. 15 L P Parameters: Xtjk = Pound of salmon type i allocated to product j for processing on period k Qjk = Net profit per pound of salmon type i made into product j in period k Sk = Capacity of frozen storage dj = Recovery rate for product j Bj = Upper bound for production of each product j Ti = Catch volume for fish type i Djk = Sales commitment for product j in period k The first step in formulating an L P is to identify the problem decision variables. In linear programming, the convention is to represent each possible alternative in a decision with a separate variable. Thus, in the catch allocation model there would be a decision variable corresponding to every possible combination of salmon size, skin colour, and quality, paired with each of the final products. In the objective function (1), Xyk is the amount of salmon in round pounds of type i allocated to final product j for processing on shift k. The scalar dj is a recovery rate factor that translates round pounds of salmon into processed pounds of salmon. Recovery rates are included in the model because the input salmon and final products are measured in slightly different units. Input salmon are weighed in units of round pounds, which is the weight o f the whole fish. When the input salmon are transformed into final products, some of this weight is lost in the processing operation. Final products are weighed in units of processed pounds. The recovery rate parameters scale the weight of the input salmon to the weight of the final products to which they are allocated. Cyk is the expected profit (revenue less expenses) per pound for final product j made from salmon type i (where i is a unique combination of size, skin colour, and quality) in period k. Thus, the objective function maximises the total profit o f the catch. In the salmon catch allocation model, there is a constraint on the maximum number o f pounds of salmon that can be processed into each end product in a given time frame. The first set of constraints in the model deal with the capacity o f JSM's processing lines. Constraint (2) ensures that the volume o f salmon (round pounds) sent to the cannery on shift k does not exceed the capacity reserved for canning (Kck) on shift k. Constraint (3) specifies that the volume of salmon (round pounds) sent to the reduction line on shift k cannot exceed the capacity of the reduction line (KRk) on shift k. Constraint (4) ensures that the volume of salmon (round pounds) sent to the Ryco and hand lines (ie. the dressing lines) on shift k does not exceed the capacity available on the Ryco and hand lines (Km) on shift k. Constraint (5) species that the volume o f salmon (round pounds) sent to the filleting line on shift k cannot exceed the capacity available on the filleting line (Kfk) on shift k. The next constraint, (6), specifies that the volume of processed product must meet demand commitments (Djk) for those products. These market commitment parameters are entered into the model to represent volumes of product from the current catch that J S M ' s marketing department has promised to customers. Though not a regular occurrence, customer orders are occasionally accepted before the catch has been processed. Each of 16 J S M ' s products has a demand parameter associated with it. These parameters can also be used to represent hard production goals. Constraint (7) balances the catch volume with the production volume. The volume o f salmon processed into final products (round pounds) must be less than or equal to the volume of salmon landed (7/, round pounds). This would be a balance equation in the truest sense i f the constraint expressed that the inflow and outflow quantities must be equivalent. This would not be an appropriate relationship for this model; the profit potential o f the catch would not be realised i f the model were forced to allocate some o f it to products with negative profit in order to balance the volume of fish caught with the volume allocated. The amount of space that J S M has available to store frozen products is also limited with a constraint. Constraint (8) ensures that the amount of processed salmon frozen does not exceed the capacity (Sk) o f J S M ' s frozen storage facility and any rented frozen storage space. A n upper bound (Bj) on production of individual products is represented by constraint (9). This constraint avoids allocating more of a particular salmon product than the company expects they w i l l be able to sell. Constraint (10) is a non-negativity constraint, ensuring that the model does not recommend the allocation of negative pounds of chum. In situations where some of the final products have negative profits, this constraint is necessary. The number o f decision variables in the catch allocation problem is determined by the number of elements in each of the sets of indices. With four primary size categories, three skin colour groups, and four overall quality assessment measures, there are forty-eight differentiable types of chum to allocate over one shift or period. These are represented by index i. The index j accounts for the number of products into which J S M processes their salmon. Including all distinct canned, frozen, fresh, and filleted products, including fishmeal, there are fifty-two final products in total. Together, the indexes / and j create 2,496 decision variables. Due to the perishable nature of unfrozen, unprocessed fish, J S M has a strict practice of keeping fish in the processing facility for only two days beyond the day that the fish were received. This policy allows them to better guarantee a fresh and quality product. In terms of the allocation decision, this means that for any catch, the fish must be allocated for processing within a period o f three days. Otherwise, it is disposed. The index k represents the production period in which a portion of the catch is allocated to a final product. This past season, J S M ' s processing plants operated one shift per day. Under this scenario with the three-day processing framework, the number o f decision variables would increase to 7,488. 17 For the upcoming fishing season, JSM plans to explore the operation of a second processing shift. The catch allocation L P is easily augmented to allocate production o f final products to a second shift for each of the three days in the processing window. In the second shift, the types of chum salmon and the final products are identical to those in the primary shift. The number o f decision variables for the allocations in the second shift is also 7,488, bringing the total number of variables for the entire problem to 14, 976. One product marketed by J S M that is not considered in the catch allocation model is salmon roe. Roe is best described as a by-product of J S M ' s chum processing operation. Unl ike the other products in the company's portfolio, roe is not created from salmon flesh, nor is it transformed into another product in the manner that a round salmon is transformed into a canned, frozen, or filleted product. Chum salmon are the primary unit of input in the processing operation, and any roe found in the females is incidental to that. Nevertheless, roe is considered an important part of the business. The profit margin from the sale o f roe far exceeds that of any of the other products produced by J S M , so there is never any question about whether a catch's roe should be marketed. The company's policy is to sort chum salmon by gender prior to processing so that any roe contained in the females can be harvested and preserved. 3.2.1 User Input Parameters The total catch volume 7} for each product is computed from estimated entered by the user. The user enters estimates of the catch composition (size, skin colour, and quality) and recovery rates from the processing of whole fish into marketable products. User Input Parameters: TC = Total catch volume (in round pounds) SP - Estimates of species ratios (chum and bycatch) SZ = Estimates o f size ratios for chum salmon SC = Estimates o f skin colour ratios Q = Estimates of overall quality ratios The foremost parameter in the catch allocation problem is an estimate of the total size o f a catch (TC). This parameter varies from one catch to another due to the dynamics of the salmon population and the length of the openings. B y communicating with their fishermen and fleet, J S M obtains updates about the volume of fish they have to make processing and allocation preparations for. The catches that J S M receives are not homogeneous in species; along with the salmon species that the company seeks, fish of other varieties are caught. Before the catches leave the fishing grounds, a report comes in that gives J S M a notion o f what the predominant species of the catch is, as well as general impression of catch quality, but this information is far from being detailed enough to allow the company to use it unaided 18 for the planning process. Instead, the company enhances this assessment with historical information about what previous catches from the same fishing grounds and same time of year have produced in the past. From this, the company w i l l estimate proportion parameters for the species composition of the catch (SP), as well as parameters for what proportion w i l l fall into the size (SZ), skin colour (SC), and overall quality (Q) categories. Ti is calculated from these by the equation Ti = TC-SPr SZi • SC • Q The profit for a particular product j using fish type i in period k (denoted by Cyk) is computed from a profit parameter entered by the user. The profit parameter is calculated from J S M ' s expense (Ejjk) and revenue (Ryk) values using the equation Cijk ~ Rijk \" 2) Eylc For each product marketed, J S M ' s variable costs are the average cost per input fish, the cost of labour, and packaging expenses (all per processed pound). Labour and packaging expenses remain constant from one catch to another during the season. The revenue per processed pound from each product is the current market price (or an estimate o f the expected market price). The revenue and expense figures are then used to calculate a profit parameter for each product in the model. 3.3 Survey of L P solution engines Confirming the dimension o f the problem at the beginning of the modeling process is critical because the best software package or solution engine for a particular problem is often determined by the size of the problem. The scale of the problem for J S M (14,976 decision variables) is too large to be handled by the standard L P solver that is shipped with the M S Excel software (which is the base application for the catch allocation decision tool). The determination of this led to an investigation of the options available for solving an L P in a spreadsheet environment. Since the release of Microsoft Excel 4.0, the software has been sold with an add-in that solves linear programming problems. This tool, called Solver, was originally developed by Frontline Systems, and licensed to Microsoft for distribution. This Solver is capable of solving linear programs, mixed-integer programs, and non-linear programs with a maximum of two hundred decision variables and about eighty constraints. Frontline Systems and a handful of other software companies have continued to develop and improve their portfolio of mathematical programming solvers since that time. These enhanced solvers are marketed separate from spreadsheet software to institutional and commercial users. The starting point for this search was an article in OR/MS Today entitled \"1999 Linear Programming Software Survey\". This article featured a series of tables that listed the 19 main features of forty-one software packages with linear programming functionality. From this survey, a set of five products were identified as compatible with M S Excel , and investigated further. Three of the software packages, OMNI by Haverly Systems Inc., MPL for Windows 5.0 by Maximal Systems, and OPTIMAX 2000 by Maximal Systems, are best described as \"modeling environments\". In these software packages, the model is constructed within the program, with the option of reading in data from M S Excel . The OPTIMAX 2000 software is the most developed in terms of interfacing with the spreadsheet. The model is coded in the MPL language, a program written in the Visual Basic for Applications (VBA) language to read in the MPL model and solve it, and then another program written in VBA that w i l l export the solution from MPL model into M S Excel . While each of these software packages appears adequate to handle the catch allocation linear program for J S M , none o f them yet meet the company's criteria of interfacing with M S Excel directly. O n the other hand, the suite of solvers developed by Frontline Systems and the What's Best software from Palisade Corporation are designed to be add-ins to the M S Excel environment. L ike with the standard solver in M S Excel , the products offered by Frontline Systems and Palisade Corporation reference a model set up on a spreadsheet worksheet with data arrays and algebraic formulas. Palisade Corporation markets four different versions of the What's Best software, differing from each other in their scalability and extra solution engines. The commercial version of What's Best handles problems with up to two thousand decision variables and one thousand linear constraints. The professional version handles up to eight thousand decision variables and four thousand. In contrast to this, Frontline Systems has developed three different solvers, each suited to slightly different problems. Premium Solver and Premium Solver Plus are more robust versions of the standard solver that was initially developed for M S Excel . These add-ins handle problems with one thousand and two thousand decision variables, respectively, and are designed to find higher quality solutions in less time when compared to the standard solver. The Large-Scale LP Solver, the third of Frontline Systems' products, takes advantage of sparsity in a model to use less system memory for the solution process, and features enhanced model pre-processing capabilities allowing large, industrial-sized linear programs to be solved in a timely manner. This add-in is capable o f solving problems with 16,000 decision variables. Considering these options and the scale o f the catch allocation model (14,976 decision variables) for JSM, the most appropriate choice for the problem is the Large-Scale LP Solver by Frontline Systems. 3.4 Tool construction 20 Construction of the catch allocation decision tool began after verifying model dimensions and parameters with representatives of JSM. Model validation was critical because the decision variables and parameter coefficients are assigned to specific cells in the spreadsheet. Once programming code is written to reference specific cells, changing the location of the model's coefficients in the spreadsheet creates a rather arduous and error-prone re-coding task. 3.4.1 Linear Program Building a linear program in M S Excel is very different than building an L P in a specialised programming environment like A M P L or G A M S . Instead of specifying variables, defining sets, and entering linear inequalities (common in code-based linear programming), in a spreadsheet, the different elements of an L P are formed by laying out vectors and arrays in the spreadsheet's cells that contain the coefficients from the linear constraints. For the catch allocation decision tool, the decision variables (Xyk) are comprised o f six arrays of 2,496 cells each. Each array represents one of the six planning periods (index A vector formed from summing the final values of the decision variables for each final product keeps track of the amount o f salmon (in round pounds) allocated each product in the optimised catch allocation. A similar vector is constructed to track usage of the production lines and frozen storage, and another uses product recovery rates to compute the amount of each product produced in processed pounds. Each of these vectors are paired with vectors containing the left-hand side coefficients of the market commitment (6), storage (8), and processing line capacity constraints (2-5). The vectors formed from summations of the decision variables contain the coefficients of the right-hand side o f the same constraints. The objective function (1) is modelled by evaluating the dot product o f an array depicting the volume of each product produced (dj X^) with a second array containing the profits (Qjk) for the products per processed pound. The locations in the spreadsheet of the objective function, the decision variables, and constraint vectors are entered into the Large-Scale LP Solver dialog box (Figure 2) by using standard M S Excel cell references. For example, an inequality constraint is entered by specifying that one array or vector must be either greater than or less than (or equal to) another array. The Solver dialog box also gives the option of specifying that all decision variables be non-negative, as opposed to stating that explicitly with a constraint. Once entered, details of the linear program are saved within the Solver dialog box, eliminating the need for the users o f the decision tool to specify the model before each run o f the tool. 21 •Equal To: C? Max <~ Min . r v a l u e of: J 0 . Close — - « t By'Changing Variable Cells: ( t _ _ „ ^ _ Guess; ..,1'' r - Options i=j $F$3: $BE$50, $F$52: $BE$99 j $F;$ ljO 1': $BE$ 148^ $1 - ^ J i $BK$ 170: $BP$ 172 <= $BK$ 175: $BP$ 177 $DM$3:$DM$50.<= $DN$3:$DN$50: %H 170 :$K$172 ;<= $F$175:$K$ 177 $F$ 180: $BE$ 182 >= $F$164: $BE$ 166 - . • — - — 1 *- • • >;.„.... . . . JLarge-Scale LP Solver fl ' ' A d d . . \" : Variables > •. | 11 '^Change;\" j;. -, • Reset All *| • ^ Delete & f ~Help | Figure 2: The LP as entered in the Solver dialog box. 3.4.2 User Interface The interface is a critical component of the catch allocation decision tool because it provides a clear means to update changes in pricing information, the processing environment, or simply to perform scenario analysis to assess the sensitivity of a particular market situation. The ultimate goal in building a tool like the catch allocation model is to end up with a product that the end-user believes is a valuable addition to the decision-making process, and is actively used as such. Interacting with the model through an interface allows sections of the model to be organised in a manner intuitive to the user. The object of the decision tool interface is to mitigate the need for users to have to update parameters directly within the spreadsheet environment. The spreadsheet model and its formulas can be protected from accidental change by the software's security features, and all intentional changes to model parameters facilitated through the user interface. This is important because many of the model parameters change over time, and altering the model directly through the spreadsheet introduces the risk of accidentally overwriting critical formulas. The interface for the catch allocation decision tool is coded in the programming language Visual Basic for Applications (VBA), which is designed by Microsoft. V B A is an object-oriented language used to write programs that are supported entirely by an application program (e.g. a spreadsheet program, a graphics program) in order to run. Naturally, V B A is completely compatible with application software developed by Microsoft (Excel, for example). The interface is created out of a series of UserForms, which are a type of customisable object available in the V B A language. These forms are designed to have the same appearance as the message and input boxes that are a part o f the interface to M S Excel . The UserForms were created to collect data and then pass it to the spreadsheet model, to summarise data in a clear and coherent manner, and to act as a menu system to navigate through various parts of the model. 22 When a user opens the catch allocation decision tool, a splash screen (Figure 3) prompts the user to begin using the tool or to exit the application. The next form that the user encounters is the main menu. B y selecting one of the four tabs across the bottom of the menu form, the user can run the model, view the previous allocation recommendation, or view and adjust the parameters of the model. | The Catch Allocation Decision Tool ' i !•= • f . a s s e s - .y ^ © J I '• ' 1 ^ 1 ; \". I i' for Chum Salmon • M&eated-bY'fiWGentre'foi^ Hi Start Using Decision Tool Maln'MetSa^ J 1. N ,End Application Session i Exit i f ••• i SfeK Figure 3: The Catch Allocation Decision Tool splash screen. The user enters the decision tool from this screen. The first tab, labeled \"Recommendation\" (Figure 4), allows the user to run or re-run an allocation scenario (that is, the L P model). Pressing the button labeled \"New Solution\" calls the solver function, into which the formulas for the constraints and objective function of the model have been pre-programmed. A l l parameters are read from the current values in the spreadsheet, Catch_Allocation.xls. A pair o f \"Yes /No\" option buttons allows the user to specify whether he or she wishes to have a second shift. Under the \"Reporting\" section, the user selects the allocation report day from the drop-down box, which automatically advances the user to the appropriate report (Figure 5) within the tool. The reports can be printed into a hard copy at this stage. 23 The Chum Salmon Allocation Decision Tool ;Re6pmmendecl;AI location Rlan i s^eGommenc la t io r Generate New Allocation Rlan ] NeW'Solution1 ApKSecona Shift 9iYes 1?R^o^rnenaat.ionJ* Gatch Characteristics.: Rrodu^^Rarimeters': &a'pa!ity;Settirigs; -Figure 6: The Catch Characteristics tab allows the user to make changes to Ave classes of model parameters. 25 Update Catch Composition ill Ehtertupdated/^ 1| X, ^ »„• J - V - ^ - ^ I I I'. ''[^llJpda£e:Catcfr^ Chum Pink J Spring lCoh'6 Other Dayri' Day?; Da f3 Figure 7: The Edit screen for one of the Catch Composition parameter classes. The user would enter the catch decomposition (SP) in percentages. Another section of the main menu is dedicated to the final products (Figure 8). The first section in the \"Product Parameters\" tab allows the user to update and review the expense and revenue figures (Figure 9) for each of the final products. A final profit figure (Q*) is computed from the revenue earned per processed pound (Ryk) and the expense per processed pound (Eyk) incurred to procure raw materials, remunerate the labour force, and provide product packaging. The next section on the menu (Figure 10) enables the user to enter the recovery rates (dj) that convert the input salmon, measured in round pounds, to output products, measured in processed pounds. The button on the \"Market Orders\" section of the menu (Figure 11) allows the user to enter any confirmed orders (Ay*) for the final products. 26 I VieWAdjust Product Parameters^ > \\—- ^ oduct^Costing and Reyenue i ; - „ ^ -; View/A?)jusb>G6'sting'.& ReyeriQe Figures') r5elecfiDay ^ound-tQ-Processed LKBecbyery^Rates^-r'View/ASjustifj'eeo^y^R^tios!]: I-View\" Recovery --RVo'duct-.iyiarket CommitmentsV-Vje\"w/Aa]UsBl^ arlRyco/Hand:Lin o . 1 ; r V ' : 1 i. Filleting 1 P't l l l l l fp 1 -Reduction ' . i | spogop i' 1 Freezing - Inte rnal - | P*\"->P ' J .•1 Freezing ^Ren ted- | 0 1 SIEttBiii i l i IlllSSt illii Edit Settings •Hose Figure 13: The Viewing screen for processing line capacities. Capacity is entered in units of round lbs. 29 IV. Analysis & Results 4.1 Testing of Decision Tool The timeline for the development of the catch allocation decision tool had been designed to take advantage of an anticipated chum salmon season in October and November o f this year, allowing J S M to test the first version of the model in a real decision-making environment. This particular test period would have allowed J S M to identify the operational strengths and weaknesses of the decision tool, as well as further verify the representation of their decision processes. These insights would be invaluable in setting direction for the development o f an expanded catch allocation model. Contrary to the original plans, the company learned at the beginning of October that the Department of Fisheries Oceans decided against opening a fall salmon season. This unexpected outcome meant that the completion of the catch allocation decision tool would not coincide with any processing activities at J.S. M c M i l l a n . In lieu o f this setback, a second plan was developed to facilitate testing of this project. Instead of testing the tool in real-time, chum salmon catch allocation scenarios from the summer of 2000 were recreated within the decision tool, subject to the quality and availability of data. The outcome of this is discussed in Section 4.3. 4.2 Presentation to J.S. M c M i l l a n On November 17, 2000, the first version of the catch allocation decision tool was presented to J S M . The intent o f the meeting was to show the results of four months of work between J S M and the C O E . It was also time to reaffirm understandings about the overall goals of the decision tool, as well as revisit details about J S M ' s process flows, product portfolio, and model assumptions. The company's reaction to the catch allocation tool was extremely positive and encouraging. Exceptional feedback was obtained on the quality of the match between the tool's features and J S M ' s needs, and working through a complete allocation scenario with the company produced valuable insight for future improvements. Three features o f the decision tool stood out amongst the others: 1. Speed - The decision tool generates a feasible allocation plan in under a minute. Using the current, manual decision-making methods, J S M takes up to five hours to arrive at an allocation plan. The company anticipates using the decision-making time-savings to experiment and perform \"what-if ' analysis on the recommended allocations. 2. Systemisation of the process - In the catch allocation tool, the elements o f the catch allocation decision are explicitly stated and very structured. This forces a decision-maker to think about the decision process and account for all o f its 30 variables in a logical and systematic manner. J S M foresees that this w i l l enable more employees to understand the decision process and contribute to its success. 3. Flexibility - Because the linear program in the catch allocation tool is driven by user inputs, the tool lends itself very well to sensitivity analysis. B y varying the parameter values for pricing, orders, production bounds, etc., J S M w i l l better understand the impact those elements have on the allocation plan itself and catch profitability, and use this knowledge to make even better allocation decisions in the future. This meeting represented a very successful conclusion for the first phase o f the catch allocation project between J S M and the C O E . 4.3 Allocation Scenario Solved Problem To test the mechanics and decision-support abilities of the catch allocation decision tool outside of the salmon season, a quantity of data describing a typical one-day allocation scenario from the past summer was made available by J S M . The contents are listed in the table below (Figure 14 &15, Tables 2 & 3): 1. Processing line capacities Canning Ryco/handline (dressing) - Filleting Reduction Freezing 2. Frozen storage capacities - in-house (500,000 lbs.) 3. A v g . profit calculation figures selling price per processed lb., each product - purchase price per processed lb., each product labour, packaging, and miscellaneous expenses, each product round-to-processed lb. conversion factors, each product market orders, each product Table 2: Description of data obtained from JSM for testing the catch allocation decision tool. Due to the confidentiality of the information, not all figures will be reported. 31 Example Processing Line Capacit ies at J S M 600,000 •a 300,000 S. 200,000 100,000 Canning Freezing Filleting Ryco/handline Processing Line Reduction Figure 14: Processing line capacities for one day in the summer of 2000, ranked in ascending order. Ranking of Products by Profit 0. _ Q B S- 77 9. >*$ ' 139 to ->• i ! 1 9 lbs., firm, red-meated bright 331 Frozen, type 1, 4-6 lbs., head-off, red-meated dark 177 Frozen, type 1, 6-9 lbs., head-off, red-meated dark 600 Frozen, type 1, 9-12 lbs., head-off, red-meated dark 321 Frozen, type 2, < 9 lbs., firm, red-meated dark 903 Frozen, type 2, > 9 lbs., firm, red-meated dark 890 Table 5: Results of catch allocation. This plan is optimal for the inputs provided. In the optimal catch allocation plan, a total of 257,330 round lbs. are allocated to twenty-one of the products in J S M ' s portfolio. This result is puzzling at first; after all , there were 300,000 round pounds in the starting catch. However, there is a sensible explanation. A s much o f the catch as possible was allocated to the production of profitable products, and when the available capacity on the processing lines was exhausted, no more profit-maximising products could be made. In a real allocation situation, the remaining product would be processed the next day when more capacity is available. The final value of the objective function for this allocation scenario was roughly $82,000. 4.4 Areas for Further Research From the beginning of this project, the C O E was very excited about the opportunity to work with J S M . Ideas for subsequent fisheries projects were generated at nearly every meeting between the two parties. Three of these ideas, in particular, have great potential to be strong projects to further the J S M and C O E partnership. 1. Dynamic Pricing Model for Salmon - the majority of J S M ' s fish supply is caught by independent fishermen, whom they pay directly. The purchase price is not set by any regulatory body, but through agreements between the parties involved. The price paid varies based on the species, size, quality, etc. 34 of the individual fish. Development of a dynamic pricing model for salmon (and other species) could provide valuable pricing insight to J S M when they renegotiate catch prices during the fishing season. 2. Catch Composition Forecasting Model - as discussed earlier in this paper, J S M estimated ratios for the species composition of their catches, as well as for the characteristics of the individual fish in the catch, based on observed patterns. A forward-looking forecasting model could incorporate historical outcomes, scientific effects (for example, global warming), salmon population cycles, and recent trends to develop better quality estimates for J S M to use in planning their production. 3. Expanded Catch Allocation Decision Model - This is the next project in line for J S M and the C O E . Pending a success with the first version of the Catch Allocation Decision Tool for Chum Salmon, an expanded version w i l l be developed. This expansion w i l l l ikely incorporate functionality for the allocation of pink and sockeye salmon to their sets of products, as well as incorporate catch by-products like chum roe. The second version of the catch allocation tool is expected to be functional for the salmon season in the summer of 2001. 35 R E F E R E N C E S Arctic Seas, L . L . C . , Arctic Seas, 26 July 2000, Booman, A . C . ; Parrin, M . A . ; and Zugarramurdi, A . 1997, \"Efficiency of size sorting of fish\", International Journal of Production Economics V o l . 48, No . 3, pp. 259 - 265. Fish B C , British Columbia Saltwater Fishing, 26 July 2000, Fourer, R. 1999. \"1999 Linear Programming Software Survey\", O R / M S Today, V o l . 26, No . 4. Online, Internet. Frontline Systems, Premium Solver Products for Microsoft Excel, 15 August 2000, Haverly Systems, OMNI Modelling Language, 15 August 2000, Jensson, P. 1988, \"Dai ly production planning in fish processing firms\", European Journal of Operational Research V o l . 36, pp. 410 - 415 Maximal Systems, MPL Modelling System, 15 August 2000, Maximal Systems, OptiMax 2000, 15 August 2000, Mil la r , H . H . 1998, \"The impact of rolling horizon planning on the cost o f industrial fishing activity\", Computers & Operations Research Vol .25, No . 10, pp. 825 - 837. Mi l la r , H . H . , and Gunn, E . A . 1991, \"Dispatching a fishing trawler fleet in the Canadian Atlantic groundfish industry\", European Journal of Operational Research V o l . 55, No . 2, pp. 148 -164 . Ministry of Finance and Corporate Relations. 2000, \"Brit ish Columbia's Fisheries & Aquaculture Sector\", Government of British Columbia. Palisade Corporation, What's Best, 15 August 2000, Randhawa, S.U.; and Bjarnason, E.T. 1995, \" A decision aid for coordinating fishing and fish processing\", European Journal of Operational Research V o l . 81, No. 1, pp. 62 - 75. 36 Renaud, I.; and Yacout, S. 1996, \"Resources Allocation and optimal product mix at a fish and seafood processing company\", Computers & Industrial Engineering V o l . 31, No. 1-2, pp. 355 - 3 5 8 . Shaw, Susan A . , and Muir , James F. 1987, SALMON: ECONOMICS AND MARKETING, Croom Helm Ltd. , Beckenham, Kent. Vancouver Aquarium, Visitor Information: Aquafacts: Pacific Salmon, 25 July 2000, 37 A P P E N D I X 1: P R O C E S S F L O W D I A G R A M S Fishing Grounds Fishing closes Department of Fisheries decides to have an opening Fishing boats travel to fishing grounds and fish JSM estimates preliminary catch characteristics; decides whether or not to reserve cannery Final catch report is generated and received by JSM J.S. McMillan Marketing activities, pricing Catch is landed, unloaded, & sorted Processing begins Catch characteristic estimates updated Catch characteristic estimates updated Estimates revised subject to final inspection Flow of Decisions and Pre-Processing Activities Send fish to FYocessing completed cannery Redirect to Ftocessing completed cannery R-ocessing completed Send fish to Ftocessing completed reduction plant Flow of Processing Events 38 A P P E N D I X 2: C H U M S A L M O N - S U M M A R Y O F P R O D U C T S CANNED PRODUCTS , Sizes - 1/4 lb. - 1/2 lb. - 1 lb. tall -41b. -1/4 lb. pulltop FISHMEAL . , FRESH SALMON Ocean Run - 50 lb. package - 1000 lb. tote Bright Fresh - 50 lb. package - 1000 lb. tote FILLET PRODUCTS Fillets - Type 1 (highest quality) - Type 2 - Canned -Pale - Scrap FROZEN PRODUCTS Frozen - Type 1, all size classes, head on or off, red meated bright - Type 2, all size classes, firm or soft, red meated bright - Type 1 or 2, unsized, firm or soft, pale meated, bright or dark - Type 2, less than 9 lbs., firm or soft, red meated bright - Type 2, greater than 9 lbs., firm or soft, red meated bright - Type 1, all size classes, head off, red meated dark - Type 2, all size classes, firm, red meated dark - Type 2, less than 9 lbs., firm, red meated dark - Type 2, greater than 9 lbs., firm, red meated dark 39 "@en ; edm:hasType "Thesis/Dissertation"@en ; vivo:dateIssued "2001-05"@en ; edm:isShownAt "10.14288/1.0089890"@en ; dcterms:language "eng"@en ; ns0:degreeDiscipline "Business Administration"@en ; edm:provider "Vancouver : University of British Columbia Library"@en ; dcterms:publisher "University of British Columbia"@en ; dcterms: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."@en ; ns0:scholarLevel "Graduate"@en ; dcterms:title "Decision-making in the fisheries industry : design of a catch-allocation decision tool"@en ; dcterms:type "Text"@en ; ns0:identifierURI "http://hdl.handle.net/2429/11411"@en .