Open Collections

UBC Theses and Dissertations

UBC Theses Logo

UBC Theses and Dissertations

A proposal for improving the meal provisioning process at Canadian Airlines Morency, Vincent 2000

Your browser doesn't seem to have a PDF viewer, please download the PDF to view this item.

Item Metadata

Download

Media
831-ubc_2000-0121.pdf [ 3.68MB ]
Metadata
JSON: 831-1.0099498.json
JSON-LD: 831-1.0099498-ld.json
RDF/XML (Pretty): 831-1.0099498-rdf.xml
RDF/JSON: 831-1.0099498-rdf.json
Turtle: 831-1.0099498-turtle.txt
N-Triples: 831-1.0099498-rdf-ntriples.txt
Original Record: 831-1.0099498-source.json
Full Text
831-1.0099498-fulltext.txt
Citation
831-1.0099498.ris

Full Text

A PROPOSAL FOR IMPROVING THE MEAL PROVISIONING PROCESS AT CANADIAN AIRLINES by Vincent Morency B.Eng. Electrical Engineering, McGill University, 1998 A THESIS SUBMITTED IN PARTIAL FULFILLMENT OF THE REQUIREMENTS FOR THE DEGREE OF MASTER OF SCIENCE (BUSINESS ADMINISTRATION) in THE FACULTY OF GRADUATE STUDIES (Department of Commerce and Business Administration) We accept this thesis as conforming THE UNIVERSITY OF BRITISH COLUMBIA December 1999 © Vincent Morency, 1999 r In presenting t h i s t h e s i s i n p a r t i a l f u l f i l m e n t of the requirements f o r an advanced degree at the U n i v e r s i t y of B r i t i s h Columbia, I agree that the L i b r a r y s h a l l make i t f r e e l y a v a i l a b l e f o r reference and study. I f u r t h e r agree that permission f o r extensive copying of t h i s t h e s i s f o r s c h o l a r l y purposes may be granted by the head of my department or by h i s or her r e p r e s e n t a t i v e s . I t i s understood that copying or p u b l i c a t i o n of t h i s t h e s i s f o r f i n a n c i a l gain s h a l l not be allowed without my w r i t t e n permission. (Signature) Department of The U n i v e r s i t y of B r i t i s h Columbia Vancouver, Canada Date -hpn^lttA H , Abstract Catering flights is an important part of an airline's operations. The meal service has a critical impact on customer service quality and represents significant costs. Unfortunately, due to high passenger load variability and minimum production lead-time requirements, it is difficult to get the number of meals to exactly match the passenger count on each flight. The objective of the project is to reduce meal-catering costs due to over-catering while simultaneously improving service level by reducing under-catering. The proposed solution takes the form of a meal bank system. This concept suggests that flights should be systematically under-catered with the possibility of uploading a generic meal to fill in any shortage at the last minute when the final passenger load is known. A thorough investigation of current processes was carried in order to define and recommend process change based on the meal bank approach. The project concludes with a costing analysis of recommended solutions weighing additional investments, cost savings, and service improvement against current performance. Costing analysis results for Vancouver International Airport operations showed that service level could be improved by an approximate reduction of 50 percent in revenue passenger meal shortages. Also, net costs can be reduced up to an estimated $190,000 annually. 11 Table of Contents Abstract ii Table of Contents iii List of Tables iv List of Figures v Acknowledgments vi I - Introduction 1 II - Background 2 A. The Catering Operations 3 III - Methodology and Approach 11 A. Preliminary Meal Bank System Analysis 11 B. Investigation of the Meal Catering System 13 C. Scope of the Project 14 D. Model Development & Consolidation of Results 15 IV - Analysis 16 A . Meal Bank Service Options 16 B. Meal Bank Storage and Lifetime 17 C. Aircraft Operations 19 D. Ordering Policy Analysis 21 E. Scenario Analysis 23 V - Discussion and Recommendations 28 A. Meal Ordering 28 B. Meal Production Control and Inventory Management 28 C. Equipment Balancing 29 D. Transportation Logistics 29 E. Aircraft Operations 29 F. Scenario Selection 30 G. Markov Decision Process Model 30 H. Confirmation Study 31 I. Implementation 32 VI - Conclusion 33 Bibliography 34 Appendix A: LAP Meal Diversity and Quality 35 Appendix B: Preliminary Analysis 36 Appendix C: Costing Analysis Database 38 A. Data Source Issues 38 B. Database Structure 39 C. Source Data Tables 49 Appendix D: Meal Bank Wastage Cost 50 Appendix E: Delivery Van Driver Schedules 52 A . Scheduling Methodology 53 Appendix F: Costing Analysis Results 57 A. Identification of Outliers, an Example 61 B. Ordering Policy Parameters 63 List of Tables Table 1: Delivery vehicle cost 18 Table 2: Passenger boarding cut-off times 19 Table 3: Uploading time estimates 20 Table 4: Total added vehicle costs 25 Table 5: Added man-hour costs 25 Table 6: Added cost summary 26 Table 7: Reduced system analysis results 27 Table 8: Meal type variables 35 Table 9: Current L A P meals 35 Table 10 : Sample first policy results 36 Table 11: Sample second policy results 37 Table 12: Source data tables 49 Table 13: Meal bank wastage cost estimation 50 Table 14: Mea l bank wastage cost summary 51 Table 15: Van driver schedules 52 Table 16: Flight assignments 53 Table 17: Detailed costing results 58 Table 18: Detailed service level results 60 Table 19: Outlier events 61 Table 20: Identification of an outlier 62 Table 21: Booked load history for an outlier 62 Table 22: Ordering policy parameters 63 iv List of Figures Figure 1: Meal quantity vs. passenger load 2 Figure 2: Catering operations overview 3 Figure 3: Mea l production process 4 Figure 4: F low of information and parts in the flight kitchen 5 Figure 5: M a i n order delivery procedure 6 Figure 6: Mea l delivery containers 9 Figure 7: L A P meal uploading procedure 10 Figure 8: Analysis date ranges 12 Figure 9: Second ordering policy performance 13 Figure 10: Reduction factor sensitivity 22 Figure 11: Intermediary upload parameter sensitivity 23 Figure 12: Sample final passenger load variability 36 Figure 13: Costing database user interface 40 v Acknowledgments M y thesis was completed with the help and support of numerous persons and organizations. I would like to take the opportunity to acknowledge their efforts. I would like to thank Associate Dean Martin L . Puterman for his help in writing the thesis document and carrying out the project, and Professor Derek Atkins for taking the time to be part of my thesis examining committee. I would also like to thank the Centre for Operations Excellence for providing facilities and funding to master student projects like mine. Special thanks go to Stephen Jones and Andre Powell for their guidance and assistance. I would also like to thank all students and staff involved in the project. I extend my gratitude to Canadian Airlines International for providing an interesting project as well as financial support. I would especially like to thank the Vice President of Inflight Services Marshall Wilmot, Director of Catering Services, Michael Joss, Manager Aircraft Provisioning, Manly Sitter. Thanks to Mandy Zhou from the Inflight Services finance department for her patience and essential help. I would also like to thank David Lee and Steve Passmore from L S G Lufthansa Skychefs. A l l staff mentioned and many others were instrumental in providing resources, describing the systems, and facilitating access to data. I would like to thank my parents for their long-distance encouragement and support through the rougher times. Financial assistance received from the Natural Science and Engineering Research Council ( N S E R C ) through the Industrial Postgraduate Scholarship was greatly appreciated. v i I - Introduction This document presents the final report of my Master of Science in Management Science thesis project. This project was done as part of the Centre for Operations Excellence (COE) Master Programme between September 1998 and December 1999. As for all COE projects, it was conducted with one of the Centre's partners: Canadian Airlines International (CAI). Canadian Airlines is the second largest national carrier. In 1998, Canadian carried more than 12.5 million passengers to over 245 destinations worldwide in 23 countries on five continents. In Canada, Canadian flies to 109 destinations.1 The work done in the present project concerned the catering area of Canadian Airlines' operations. The Meal Provisioning Project consisted in the development of a proposal for improvement in the meal ordering, production, and delivery system as a whole. The study and proposal covered CAI operations at Vancouver International Airport. One of the key features of the airline industry is the variability in passenger demand. Variable demand creates uncertainty as to the number of passengers that will board any given flight. This uncertainty makes the task of consistently providing the appropriate number of meals to each flight very challenging. As a result, large expenses are currently incurred by overages in meal provisioning. In addition, service quality can suffer significantly when a flight is under-catered. The Meal Provisioning Project has two key objectives. The first objective is to reduce meal-catering costs. Simultaneously, the project strives to improve service level, by reducing under-catering instances. These twin objectives have been addressed through an in-depth review of the catering process and the application of management science techniques. A similar document was presented to Canadian Airlines management outlining the potential costs and benefits associated with implementing a revised catering policy. The approach has been to build a management science tool that allowed us to study the impact of various catering policies on service level and cost. This thesis document gives a more detailed background and explanation of the methods used to arrive at the recommendations presented to CAI management. ' Canadian Airlines International web site, http://www.cdnair.ca i l II - Background 2 The Meal Provisioning Project follows on the work accomplished by Jason Goto . In his project, Goto completed an investigation of the current catering system performance. The final report identified a potential opportunity to reduce under-catering of flights as well as meal wastage costs through a comprehensive analysis of the catering policy. The scatter plot in Figure 1 below summarizes some of the key findings of his study. It shows the number of over-catered flight instances (dots above the 45-degree line) and the number of under-catered instances (dots below the line). The objective is to bring all dots as close as possible to the 45-degree line by more closely catering at the final passenger load. M e a l Q u a n t i t y 400 3 5 0 ' 300 250 200 150 100 ' 50 0 | I I I I | l l I I | I I I I | I I I I | I I I I | I I I I | I I u | I I I I | 0 50 100 150 200 250 300 350 400 P a s s e n g e r Load Figure 1 : M e a l quantity vs. passenger load Goto also developed a Markov Decision Process (MDP) model for the meal ordering system at Canadian Airlines. This model provided an optimal decision rule for each individual flight at multiple decision epochs prior to departure. For example, the optimal policy for the decision epoch at 3 hours prior to departure would tell the system to increase the order quantity to 54 meals given that the current passenger load is 58 and that the meal order at the previous decision epoch was 48 meals. The approach for my project is to propose process changes for the catering operations and evaluate their feasibility with regards to costs and logistic concerns. The ordering policy is only one of the areas of the project. Goto's model was considered as a potential solution for the meal ordering aspect of the proposed changes. 2 Goto, Jason H . (1999). A Markov Decision Process Model For Airline Meal Provisioning. 2 A. The Catering Operations The current operations involved in provisioning flights with a meal service are described here. Understanding and mapping these operations has been an essential part of the project. Flight Kitchen Meal Production Commissary Equipment Assembly Delivery Figure 2: Catering operations overview The catering operations are divided between two main departments: the flight kitchen, and the commissary department. The flight kitchen is responsible for meal production, assembly, and storage. A meal refers to the main service for any given flight. The meal is usually composed of two parts: a main dish or casserole, and a Tray Set-Up (TSU) that usually includes a salad, a bun, a dessert, and utensils, served on a tray. Other meals can be served in a basket or lunch box, but are still stored on a tray. The commissary department is responsible for assembling all items that make up the galley. The galley is the set of containers and equipment that is loaded and unloaded on the aircraft before and after each flight. It includes all food items like the meals coming from the flight kitchen, but also dry stores (e.g. peanuts), alcoholic and non-alcoholic beverages, cooking equipment and utensils, magazines and newspapers, and headsets. Every day, the commissary staff assembles the galley equipment for each flight inside the caterer's warehouse and delivers it to the aircraft via high-lift truck. Meal Production The flight kitchen operates in a batch process flow structure. The meal service for each flight is treated as an individual entity that goes through the process flow illustrated in Figure 3. The production quantity is scheduled in the preliminary production sheet completed by the information services group and transmitted to the Chefs department (kitchen). The Chef will then order ingredients from the store room. The store room staff delivers batches of ingredients mapping the requirements of each individual flight. The ingredients are assembled and cooked in the kitchen resulting in the main dish or casserole part of the meal. Simultaneously, the material handling & delivery staff assembles necessary components for the TSU's. The ingredients, which can include lettuce, cheese, plates, and buns, are delivered on a rack to the assembly personnel. The assembly staff is organized in individual workstations according to flight batch sizes. They assemble the TSU and casserole on individual trays and store them in galley containers. These containers can either be trolleys, large metal boxes on wheels, or carriers, smaller metal boxes with a handle (see Figure 6). The containers are grouped by flight number and by the location of their destination inside the aircraft (forward, center, 3 or aft). The grouped containers are stored in the refrigerated holding room. The order checker w i l l verify that meal quantities inside the containers match the updated and final order sheet before commissary employees deliver them to the aircraft. Time prior to departure 36 to 24 hours 24 hours 18 to 14 hours 14 to 3 hours 3 to 1 hour V Preliminary sheet production Order ingredients t \ TSU assembly Carrier/trolley assembly Check order Delivery Aircraft Figure 3: Meal production process The information services group controls the flow of information and parts throughout the flight kitchen as illustrated in Figure 4. They establish production quantities based on booked passenger loads, a forecasting tool, and the manager's judgment and experience. Information services also handle communications with individual flight controllers at the 4 airport terminal. A flight controller can make adjustments to the meal order for the flight under his or her responsibility up to one hour prior to departure. Information on order quantities is transmitted to the store room on a longer aggregate basis for inventory management and ordering from suppliers. As mentioned earlier, the kitchen receives the preliminary production sheet in order to determine production quantities. Store Room Kitchen Information Services ! • Material Delivery & Handling JAssembly w Ware-wash Equipment Delivery & Handling Information Checkers Parts Delivery Figure 4: F low of information and parts in the flight kitchen The assembly is controlled through a Variable Production Schedule (VPS), also completed by information services. This information is used for assigning assembly staff and production quantities. Employees are hired either permanently or on call. The number of permanent employee is set to a low level so that temporary employees are almost continuously needed. This results in an efficient use of staff. No set up cost is required when calling in temporary employees. Both permanent and temporary employees receive an hourly wage. Commissary Operations Commissary operations are divided into two main tasks: assembling galleys, and delivering them to the aircraft. The assembly is done in a similar batch process flow where each flight is an entity. The equipment is stored inside carriers and trolleys (see Figure 6) that are in turn grouped by flight number. The assembled galleys are temporarily placed in a holding area of the warehouse next to the door leading to the docking bay. Delivery drivers pick-up assembled galleys, roll them onto the truck, and add the meal containers from the kitchen's refrigerated holding area (see Figure 5). 5 The caterer uses a combination of high-lift trucks and vans for delivery operations. High-lift trucks serve for delivering the full galleys and meals to flights (referred to as the main order in this document). A team of two employees is assigned to each delivery on high-lift trucks. Vans are used to answer any last-minute request for catering equipment or small meal quantities prior to the departure of a flight. A single driver operates a van. If an irregular situation at a flight requires a large last-minute upload, high-lift trucks can be used at a higher cost. Holding fridge Loading dock Flight kitchen Commissary Warehouse Delivery trucks Assembled galleys Figure 5: Main order delivery procedure Commissary Operation Scheduling The commissary dispatcher is responsible for scheduling delivery operations. The dispatcher has access to an R G G (SABRE) terminal. This information system provides him with up-to-date flight departure and arrival times. Every morning the dispatching staff prints out the list of flights for the day and assigns trucks to each of them using their truck number. The dispatcher basically links the R G G schedule to a truck schedule. In the truck schedule, each row represents a truck/team and each cell represents an assignment. For example truck 105 is assigned the aft galley of flight CP007 at 12:00. The first dispatcher shift starts at 4:00. Three truck schedules are produced during a day: from the first flight to 10:30, 10:30 to 16:00, and 16:00 to the last flight of the day. The truck schedules are revised continuously as the day progresses. These schedules are all paper based. The dispatcher uses a highlighter colour code to designate truck assignment status. The status can be assigned (matching RGG), in progress (truck has left the kitchen dock and is loading/off-loading), or done (returned to kitchen dock). On May 6, 1999 the caterer handled 74 flights at Vancouver International Airport; 56 of them were Canadian Airlines flights. 6 Communication Truck and van drivers are equipped with a radio transmitter linking them with the dispatcher's desk. Communication is essential to the van operations. It allows aircraft crews to send last minute requests to the vans through the dispatcher: The quality of the transmission received in a van can make it very hard to understand the dispatcher. Resources The caterer owns 25 high-lift trucks. On average, one truck is kept aside on maintenance rotation. The caterer also has 9 vans. A l l commissary galley equipment is owned by the airline. N o usage fee is charged to the caterer and inventory management and replacement is the airline's responsibility. The Late Augmentation Plan (LAP) The Late Augmentation Plan was instated over a year ago as a means to provide meals to flights at the last minute prior to departure. It was implemented only for a subset of Canadian Airlines flights (transcontinental and transborder). The L A P can be considered as the precursor to the meal bank system proposed in this document. The L A P is different from the regular meal provisioning system described above in both the meal content and the delivery procedure. A L A P meal is produced for a set of flights bearing the same characteristics in aircraft type and route sector. The L A P meal diversity is detailed in Appendix A . These meals are produced using the same process as any other regular flight. They are stored in a separate holding area and are identified by L A P meal numbers. LAP Meal Production The business class L A P meal casseroles are prepared in the afternoon of the preceding day. Quantities must be established 24 hours in advance. Economy class L A P meal casseroles are pop-outs. Pop-outs are complete ready-made casserole entrees purchased from a supplier. One supplier handles all C A I kitchen requirements in Canada. Wi th such a large demand, the airline can gain economies of scale by using pop-outs rather than having the casserole dishes made in-house. Note that pop-outs are used as the casserole for all regular Transcontinental economy-class meals. Within the next year, they should be used for economy-class meals on all routes. Pop-outs are easily accessible from any North American station as well as European stations. Pop-outs are high quality meals even i f they are less expensive than in-house produced casserole dishes. Pop-outs are ordered every month in large quantities from the supplier and stored at an intermediary supplier location where they can be retrieved on a daily basis. A s long as they are kept frozen, these pop-out dishes can last almost indefinitely. A s the final load control sheet becomes available in the afternoon of the day prior to departure, the necessary amount of pop-outs for a given flight is taken out and left to thaw overnight 7 (about 18 hours). Since they arrive in plastic containers, the pop-out contents have to be transferred onto a casserole dish. The pop-outs can then be joined to the T S U just as any casserole made in the flight kitchen. The L A P T S U ' s are assembled during the graveyard shift or very early in the morning. Complete meals have a life span of approximately 24 hours as long as they are kept refrigerated. LAP Meal Wastage It is not possible to know precisely how many L A P meals w i l l be used on any given day at the time of production. This is why more meals are generally produced then necessary. A s long as they are kept refrigerated, meals can be used over a 24-hour period. The meals are tagged with a colour code identifying their date of production. The unused L A P meals are pushed to the next day's usage. I f the meals are not used after the second day, they w i l l be discarded. The airline shares the cost of wasted meals with the caterer. The non-food part of the T S U is re-used instead of being sent to washing thus saving T S U assembly labour costs. LAP Meal Delivery Each LAP-designated flight is assigned to a delivery van. There are usually two van drivers assigned to Canadian Airlines flights. On top of L A P delivery, the van drivers must respond to any request from all catered flights. For example, these requests can be for a missing coffee pot or a missing oven tray. One van is assigned to last minute flight requirements during the whole day (7:00 to 19:00). A second van is involved in other miscellaneous operations, like handling duty-free transportation, in the morning and supports the first van for last minute flight requirements during the peak period (12:00 to 15:00). Van drivers visit all the L A P flights to which they are assigned. They visit other flights only i f required. The van drivers' responsibilities require a quick response time. They have to move from gate to gate and often have no time to return to the flight kitchen base in between. The vans therefore carry all sorts of equipment. This leaves little room for L A P meal containers. They can currently fit two or three carriers comfortably. The vans are not refrigerated so dry ice must be used to keep meals cool. The uploading procedure for L A P deliveries is as follows (see Figure 7). The driver shows up at the boarding gate of a particular flight approximately 15 minutes prior to departure. The driver exits his vehicle and accesses the gate entrance inside the airport terminal using the exterior stairs that lead to the access bridge. The driver asks the flight controller about any additional meal request. I f any are needed the driver goes back to the van and pulls out the necessary quantity of meals. L A P meals are transported in carriers containing 7 (wide-body aircraft) or 8 (narrow-body) meals. The van driver is responsible for delivering the meals to the appropriate container inside the aircraft galley. Passenger flow disruption is an important issue in the L A P delivery operation. The driver enters from the front of the aircraft and sometimes has to bring carriers to the center or aft galleys. A l l of this takes place while passengers are boarding the plane and 8 getting to their seats. It can be hard for the driver to return to his truck against the incoming flow of passengers. Another issue concerns the security of passengers during the meal loading operation. Meal trays are transported in metal carriers with sharp corners. As the driver walks down the aisle, he must be careful not to hit passengers. Deadhead Equipment Each airport in the airline's network of destinations holds an inventory of commissary equipment. Each time an aircraft flies it moves part of this equipment from one station to another. Assuming all point-to-point transfer quantities are constant from day to day, inventory levels should be balanced across all stations. This is why the airline uses deadhead equipment - catering equipment without any food - i f a given flight is not full. For example, a flight with a capacity 88 economy class flies with only 50 passengers on board. Assuming that exactly 50 meals were prepared, the remaining 38 slots will be filled with deadhead tray equipment sets. Although deadheading solves the equipment balancing issue, it creates operational costs. Handling fees are charged for each deadhead tray since they need to be assembled at one end and washed at the other. Deadheading adds weight to the aircraft load and increases fuel costs. It also creates additional work for the L A P meal delivery personnel. When uploading meals inside the aircraft, the van driver has to swap a deadhead tray for a L A P meal instead of simply inserting the added meal. Carrier Trolley Figure 6: Meal delivery containers 9 Airport terminal Figure 7: LAP meal uploading procedure Ill - Methodology and Approach The project's approach to improving catering performance is through the application of a meal bank system. The study examined the feasibility and net benefits of such a system. Using a meal bank implies reducing the main order meal quantity and uploading meals in the final minutes before departure. This way, it is possible to fill in any gap between the final passenger count and the number of meals boarded. Our approach was to first perform a preliminary analysis of potential gains. Based on the success of the preliminary analysis, we investigated current operations in order to understand the logistics of the meal bank system and define the scope of the project. We formed focus groups during the pilot study phase to understand the processes and to formulate feasible process changes. The recommendations in operational changes were then incorporated in a thorough data analysis of the meal bank model. Finally, a costing analysis was performed based on the model output, current costs, and added cost from process changes. The major steps in the methodology are detailed below. A. Preliminary Meal Bank System Analysis The meal bank concept suggests that the main meal order should be purposely made smaller than the expected passenger load. The meal bank then fills any need for meals in the last 15 to 20 minutes. As a first analysis, we were looking for an appropriate main order size for the system such that the final upload requirements can be met. We approached the problem with forecasting techniques, using past information to predict what the future will be. The first application of the meal bank concept was done using data on final passenger loads only. Data Source Data was obtained from the Terradata system at Canadian Airlines. This system contains information extracted from TAPS (Total Aircraft Provisioning System) and AMOS (Automated Meal Ordering System). Some Terradata tables contain the exact same information from TAPS and AMOS while some other present summary information built from cross table queries. The SABRE group in Dallas, Texas manages all Terradata systems. The tables are accessed by Canadian Airlines through ODBC drivers using MS Access. Pre-departure information was used for the purpose of preliminary analysis. This information is broken down to a flight number, city pair, date, and passenger class aggregate level. This table consists of measurements on booked passenger load and checked-in passenger quantity made at 12 different query periods prior to departure. For 11 analysis purposes, we were focusing on the last 6 query periods (6 hours prior to departure, 3 hours, 2 hours, 1 hours, 30 minutes, and post-departure). Analysis Method The first step was to extract data from the appropriate table in M S Access. Secondly, we imported the data to a pivot table in M S Excel . The analysis was restricted to flights departing Vancouver International Airport, the largest station in Canadian Airl ines ' system. Time series data on final passenger loads were used. A n initialization set was formed using data segmented by flight number and day of the week. The average and standard deviation was computed for each time series segment of the initialization set. The last month of data was used as a test set or holdout set. A n ordering policy was applied on this test set and results were compared to the actual data. The performance of the policy was evaluated by assessing the quantity of meals from the meal bank that would have been needed in order to supply for any gap between the number of passengers and the main meal order. 2/1/98 7/1/98 7/31/98 1 1 h — • . J "Y Initialization set Test set Figure 8: Analysis date ranges First Ordering Policy The first proposed policy was strictly based on final passenger load data. It was a simple approach in which the standard order was the average final passenger load, as computed on the initialization set, less one or two standard deviations. Final passenger load forecasting was done on a flight by flight basis and on a day of the week basis. For example, we used past final loads for flight CP912 on Wednesdays to predict the final load on this flight on the present day (given that it is a Wednesday). Large variability in final loads resulted in poor forecasting performance. This forced the system to heavily reduce the main order size resulting in unreasonable last minute upload quantities. See Appendix B : Preliminary Analysis for details. Second Ordering Policy A second approach used historical pre-departure data. This data not only contains final passenger loads, but also pre-departure booked loads. Here, past information is used to understand the nature of the volatility of booked passenger loads to determine an appropriate policy and process. 12 From the pre-departure information an initialization set was formed on which we calculated the average and standard deviation of the difference between the final passenger load and the number of passengers booked six hours prior to departure. The policy calculates a main meal order quantity by taking the average difference between the 6 hours prior to departure and final passenger loads, subtracting one standard deviation from it, and adding it to the current number of passengers booked six hours prior to the departure of a given flight. The formula is shown below. Variations to the number of standard deviations in the formula could have been used to obtain an optimal performance. However, this being the preliminary stage of the analysis, one standard deviation was used in order to prove the feasibility of the meal bank concept. Sample data on this analysis is included in Appendix B : Preliminary Analysis. Second Ordering Policy Let Pax 6 be the number of passengers booked at the 6-hour pre-departure point Let Pax 0 be the number of passengers checked-in at departure time Let D i f f 6 0 be the difference between Pax 0 and Pax 6 Rule: Pax 6 + [Mean(Diff6_0) - Standard Deviation(Diff 6 0)] = Main Meal Order Analysis of the second policy yielded encouraging results. These results, shown in Figure 9, were presented to C A I management in late January1999. The meal bank system successfully passed the initial feasibility step. The objective was to create a final upload requirement in the order of 10% of total meals and reduce overage as low as possible. 14.0% g 12.0% i 10.0% s £ 8.0% 0 6.0% 1 4.0% S 2.0% Q. 0.0% Meal Bank Requirement ^ <P <tl- <!»• <t° -i? Date (July 1996) Overage Performance Date (July 1998) Figure 9: Second ordering policy performance B. Investigation of the Meal Catering System The implementation of a meal bank system required at least a partial re-design of the catering process. Prior to any recommendations it was essential to conduct an investigation of current operations. This investigation allowed for a better understanding of the "As-Is" environment and provided insight into opportunities for improvement. Following the investigation, focus group meetings were held with resource personnel within both the airline's and the caterer's organization. During these meetings, opportunities for improvement were described and proposed changes were evaluated. 13 The deliverables from the workgroups are listed in the Discussion and Recommendations section of this document. C. Scope of the Project The project scope includes the following five key areas described in detail below: 1. Meal Ordering Policy 2. Meal Production Control and Inventory Management 3. Equipment Balancing 4. Transportation Logistics 5. Aircraft Operations Meal Ordering Policy The ordering policy is a key area of the project. A s determined in the preliminary scenario analysis phase, substantial savings can be obtained in reducing meal wastage by using an improved policy. A n automated database system was designed using M S Access to simulate different ordering policies and to evaluate the performance of each scenario. This analytical tool allowed for presenting the multiple alternatives to Canadian Airlines management and determining the appropriate parameter values. The database system is described in details in Appendix C: Costing Analysis Database. Meal Production Control and Inventory Management The central concern in meal production resides in the management of the assembled meal inventory. This inventory system has daily delivery and reorder points. The items in the meal inventory not only have a short lifetime but also are highly differentiated due to the different possible meal services across all flights. A large component of the potential savings resides in the reduction of meal wastage. A n alternative meal design and delivery procedure was researched in an effort to mitigate the wastage problem. Equipment Balancing A sub-project emerged from the aircraft operations area with regards to the equipment balancing issue. A n alternative to the current deadhead policy would yield increased uploading performance and reduced handling cost. To analyze this problem, a prototype database structure has been developed for the purpose of simulating catering equipment transfer across the major airports in the Canadian Airlines transportation system. This analysis is currently being conducted as a separate C O E project. 14 Transportation Logistics Logistics research has focused on scheduling delivery vans to flights that required visits given the ordering policy. The constraints have been dictated by results coming from research in the aircraft operations area. A preliminary schedule has been designed for Vancouver airport flights. Aircraft Operations This area of the project pertains to the operations that take place at the airport terminal and inside the aircraft as bank meals are uploaded. These operations also take place in the short time window between the moment when the final passenger load is known and the flight's scheduled departure. A recommended procedure was only defined after consulting with gate agents, flight attendants, and catering delivery personnel. The objective is to maximize the meal uploading capacity within strict time limitations. D. Model Development & Consolidation of Results The ordering policy model is an analytical tool that allows us to test different operational policies, see how they would have performed, and compare them to historical data. Note that Monte-Carlo simulation was not used. It was designed using Microsoft Access with significant input from focus groups. We have adopted the suggestions of user groups where possible. The source data used in the model consists of Terradata tables downloaded from Canadian Airlines information systems. Performance was initially evaluated on the basis of overage reduction, final upload size, and underage quantity. In the consolidation phase, the model evolved from pure policy testing to overall cost evaluation. A t this stage, performance was now evaluated on the basis of meal cost, under-catered meal quantity and overage cost. Costing formulas were integrated into the ordering policy model. The source data was also expanded to include actual financial data for cost comparison purposes. Added costs from catering process changes were estimated and included in the final results. The costing analysis w i l l provide the necessary information to support C A I management's decision-making. Appendix C: Costing Analysis Database details the structure and content of the ordering policy model. Also , please refer to this appendix for details on data source issues. 15 IV-Analysis J This section contains findings and results from focus group meetings and interviews with resource personnel. These findings relate to implementation issues for the meal bank system. Meal bank service options and meal bank storage and lifetime are discussed in greater detail. Another part of the analysis detailed in this section is the choice of parameter values for the model's ordering policy. Finally, the analysis has been categorized into different meal bank implementation scenarios. The alternative scenarios are explained and costing results are detailed. A. Meal Bank Service Options Part of the investigation was dedicated to defining the appropriate type of meal to be used for final uploads. Ideally the meal bank would be composed of one type of meal that could be served on any flight and would last for an extended period of time. However, in order to be accepted, the bank meals must comply with Canadian Air l ine 's quality standards and be as close as possible to the main order meals. Different options were considered as a solution for the meal bank service. These options are listed below. Traditional Tray Set-Ups (Pop-Outs) This option is currently used in the L A P system. It involves the use of pop-outs for economy meals on transcontinental and transborder flights. For business class, the meals are very similar to standard meals. L A P meals are generally chicken-based due to chicken's overall acceptance and popularity. The chicken casserole is also usually the lowest cost option. A proposed variant is to fully cater flights with Tray Set-Ups (TSU) and keep a portion of the T S U ' s devoid of a casserole dish. The uploads would simply be casseroles to be inserted on incomplete trays. This system would imply full T S U cost on all flights. This approach was tried in the past and abandoned since it was too expensive. Meal Pouches Meal pouches are low quality meals that can be reused from one flight to the next. These meals are very different from the regular service meals. Quality standards would require that the portion of such meals used on a flight be kept too low to be acceptable for meal bank system purposes. 16 Shelf-Stable Meals This new type of meal has the advantage of being transferable to the next flight i f not consumed. It can stay at room temperature for extended periods of time. These meals are manufactured in Thailand and are provided in sealed individual containers. The quality of these meals is significantly lower than that of pop :out meals. B. Meal Bank Storage and Lifetime Daily wastage is a significant part of the meal bank system implementation costs. A s explained in the L A P Meal Wastage section of the Background, these costs currently exist with the Late Augmentation Plan. Wastage analysis was based on the use of 48-hour life cycle meal bank. B y having a 48-hour lifetime, the unused portion of a day's production can be used on the following day, thus drastically reducing meal wastage. The appropriate meal bank inventory level can then be set using fixed daily reorder points. The nature of a long-lasting meal is detailed below. The key consideration in having a 48-hour lasting meal is keeping a constant chain of refrigeration. This implies a need for refrigerated delivery vans. The cost of a refrigerated vehicle is provided in Table 1 below. Food safety is a crucial requirement in the meal process. The catering system follows H A C C P standards in order to monitor and ensure a safe product. A s described in detail further below, there is concern that a refrigerated delivery system would not meet safety requirements. This issue wi l l be determined in a subsequent phase i f the meal bank system is to be tested. The analysis for meal bank wastage cost was performed assuming a meal that could be reused on the following day. Results show an estimated total monthly wastage cost of $6,600. Appendix D : Meal Bank Wastage Cost details the estimation method and provides an example. Types of Lonq-Lastinq Meals 1. Shelf stable: This type of meal can stay at room temperature for extended periods. The quality of such a meal is much lower. 2. Resistant: The meal, i f it is kept refrigerated, w i l l remain at an acceptable quality and safety level. Small modifications to the economy class meals would convert it to a resistant meal. The only part of the T S U that is not resistant for 48 hours is the tossed salad. It could be replaced by any type of marinated salad. This change would result in a small incremental cost of less than 10% of the meal cost. With business class meals, the casserole portion can last 48 hours i f kept refrigerated. However, there exists a problem with the appetizer and T S U . They are made with higher quality ingredients with very limited lifetime. The FlexMeal is a cold T S U meal 17 available as an alternative to the aisle service menu. Those meals are cold but due to the nature of their ingredients they are not designed to last more than 24 hours. Changing the business class meals to fit the safety requirements of a long-lasting meal would require lowering their quality and appearance. Refrigerated van fleet To make the long-lasting meal alternative possible, the transportation system must be refrigerated. The two possibilities are to retrofit existing vans or to purchase new ones altogether. Retrofitting Insulation Installation of refrigerating unit New refrigerated van Standard new van Warranty Insulation Installation of refrigerating unit Exterior graphics package Table 1: Delivery vehicle cost HACCP standards The Hazard Analysis Critical Control Point is an international standard inspection system. The key element of a HACCP system is its preventative nature and the exercising of control throughout the manufacturing process, at critical steps called Critical Control Points (CCP). This way, defects which could impact on the safety of the food being processed can be readily detected and corrected at these points before the product is completely processed and packaged. A ready-to-eat meal such as the bank meals is considered to be a Critical Control Point in itself. HACCP requires that critical limits be established as well as an adequate monitoring procedure. For example, the meals have to be kept at the adequate refrigeration temperature. This emphasizes the need for refrigerated trucks in a long-lasting meal system. There is concern that the monitoring procedure during the delivery process would not be adequate. The human factor is affecting the monitoring of critical limits outside the kitchen fridge holding area. According to a Food and Beverage Analyst at CAJ, re-using meals over more than a one-day span through a refrigerated van system is risky due to unacceptable monitoring and human intervention. The current process would have to be modified in order to satisfy food safety requirements. $ 3,900.00 $ 6,185.00 $10,085.00 $27,400.00 $ 1,850.00 $ 3,900.00 $ 6,185.00 $ 621.00 $39,956.00 18 C. Aircraft Operations This section of the process analysis concerns the last-minute meal uploading procedure or final upload. The most important aspects of aircraft operations are defining the exact procedure, the time required to perform the upload, and the maximum meal upload size. A focus group was formed to study this particular part of the catering operations. The recommendations from the aircraft operation focus group are included in the Discussions and Recommendations section. Important facts and issues to consider for the uploading procedure are detailed here. Uploading Procedure and Timing In order to define an appropriate delivery procedure we must understand the issues, and people involved. It is essential to determine the exact required time window for the upload operations. This time window w i l l be used in designing the delivery van schedules. The minimum time window is dependent on the following requirements: • the final passenger load must be known, • sufficient time must be allowed for the uploading operation to take place without incurring any delays to the flight. Important Facts • Passengers cannot show up later than the cut-off time in order to be allowed boarding. The cut-off times are shown in Table 2. • The door of the aircraft must be closed 5 minutes before departure at the latest. • The driver usually takes carriers to the aircraft one at a time. Although some drivers w i l l handle two in rushed situations. Domestic Transborder & International A t the Check-In Counter 20 minutes 30 minutes A t the Gate 15 minutes 20 minutes Table 2: Passenger boarding cut-off times Description of Uploading Operations The van driver is responsible for delivering the meals to the appropriate location inside the aircraft. Dropping carriers at the door is not an acceptable option according to the flight attendants' employment safety standards. If the quantity of meals is reasonably small (5 or less) the flight attendants can store the meals in the front and move them later. The presence of deadhead trays requires a swapping procedure as discussed in the Deadhead Equipment section of the Background. This time consuming procedure could 19 be fully or partially eliminated i f deadhead equipment is reduced. This issue is dependent on the on-going equipment balancing project. A signing procedure is currently in place to ensure that the airline gets charged for the exact amount ordered by the flight controller. The van driver must walk up to the gate to obtain the required upload quantity and get the controller's signature for it. Once the final upload quantity is known the time requirements are defined by the following factors: • Passenger status (boarding, sitting, still at the gate): I f passengers are still boarding this means that the longest process times are to be considered. • Aircraft type: For a B737, it is possible to simply swap carriers. For others, all meals are in trolleys. This implies that trays must be inserted individually. • Receiving galley: (front, center, or aft) Except for the B737 and A320 aircraft, a space should be made available in the center galley for meal bank increase purposes. • Deadhead equipment: i f deadhead trays are present they must be swapped. • Equipment requests: whether or not the driver is responsible for requests for other missing equipment, i.e. coffee pots, oven trays, etc. Time estimates The time taken to get to the gate for order confirmation is not included ( if so add another 2 to 5 minutes). It is assumed that the upload is delivered to the center galley on wide-body aircraft. N o time is included for other equipment requests. The variation is dependent on passenger status. These estimates are based on an interview with a delivery van driver. Note that time estimates w i l l vary from one driver to the next. The values listed below account for worst case scenarios. Upload Quantity Required Time 1 carrier, J-class 2 to 4:30 minutes 1 carrier, Y-class 5 to 10 minutes 2 carriers, Y-class 10 to 15 minutes 1J+ I Y 7 to 12 minutes 1J + 2 Y or 3 Y 12 to 17 minutes Table 3: Uploading time estimates Maximum Upload Size The number of meals that can be uploaded directly affects the model's performance. In fact the maximum upload is one of the parameters that define a test policy. We want to have as large a maximum as possible but the model must respect logistic constraints. The constraints are dictated by the combination of limited meal carrying capacity and limited available time. 20 The final uploads are delivered by vans. There is a limit to the upload quantity that can be reasonably handled by a van driver. Here are the facts to consider: • The maximum quantity that can be carried in one trip up the stairs is limited to 1 or 2 carriers. • The driver can make an additional trip i f multiple carriers have to be uploaded. • Due to the injury risk, transporting two carriers at a time could become a safety issue and be completely ruled out. For analysis purposes, we assume a maximum upload quantity of 3 carriers, 24 meals (narrow-body) or 21 meals (wide-body). Also note that for international flights the limit is set to 10 passengers since two meals are served. A procedure has to be defined for cases where the required upload goes beyond the limit. The alternatives are the following: • Use shelf-stable meals to be boarded as a standard for meal bank flights. • Use a high-lift truck for the final upload delivery instead of a van. This implies that a high-lift truck is assigned on a regular basis to the final upload of the flight in question since 20 minutes is too late for calling on a unplanned high-lift truck delivery. High-lift trucks are much more expensive than vans and require double the manpower costs. This makes using the high-lift truck for regular operation highly non-practical. In the analysis of the meal bank model, we assumed that i f the upload requirement goes beyond the maximum quantity, the flight is under-catered. D. Ordering Policy Analysis The ordering policy is a function of different user specified parameters. The database model, detailed in Appendix C: Costing Analysis Database, was designed so that different parameter values could be tested. There are two areas for parameter analysis: the main order reduction factor and the intermediary upload decision rule. Main Order The parameter determining the main order policy is the reduction factor, a number multiplied by the standard deviation in the formula as used in the second policy of the preliminary analysis phase. Note that the main order is made at the 3-hour pre-departure point in accordance with the caterer's contractual agreement. Main Order Formula Let Pax3 be the number of passengers booked at the 3-hour pre-departure point Let Pax0 be the number of passengers checked-in at departure time Let Diff3_0 be the difference between Pax0 and Pax3 Rule: Pax3 + [Mean(Diff3_0) - Reduction_Factor*StdDev(Diff3 0)] = Main Meal Order 21 The average and standard deviation are trimmed in order to be unaffected by past outlier events. This was achieved by screening out Diff3 0 values that where outside of an absolute maximum value of 30 meals. Different reduction factors were tested in order to determine an appropriate policy. The sample data in Figure 10 was obtained using long sector transcontinental flights in June 1999. The use of one standard deviation is justified as an adequate compromise between cost savings and under-catered instances. A s we see on the left hand side graph, the total monthly meal cost start to rise more significantly as the reduction factor becomes smaller than 1. Although not ideal, the underage level at one standard deviation (155 meals) is significantly lower than the actual under-catering for June 1999 (272 meals). The problem is that a small number of exceptional instances drive the under-catering figures. This is explained in the Results section and detailed in Appendix F : Costing Analysis Results. Lowering the reduction factor is not an adequate solution to reduce meal under-provisioning since it drives the costs higher and over-caterers most of the flights. This is similar to a situation where excess inventory is used to cover productivity problems. Total Monthly Meal Cost $220,000 $215,000 $210,000 $205,000 $200,000 $195,000 0 0.5 1 Reduction Factor 1.5 2 Total Monthly Underage s 100 0 0.5 1 Reduction Factor Figure 10: Reduction factor sensitivity Intermediary Upload The meal bank system includes a delivery van visit between the main order delivery and the final upload. This visit, referred to as an intermediary upload, allows for making meal quantity adjustments when an unexpected shift in passenger load occurs or the flight controller is aware of special situations such as a delayed connection or a mechanical problem on a competitor's aircraft. The intermediary upload can be a simple adjustment to the main order only i f it is still in the holding fridge. This is very unlikely for the 1-hour period, but possible on certain flights (short sector - narrow bodies) at the 2 hour count. Experience shows that generally, at the 1-hour count the main order cannot be adjusted and a separate delivery is required. The ordering policy database model uses expected meal shortage to determine i f the intermediary upload is necessary. The upload size can either be 2 carriers (14 meals) or one carrier (7 meals). The policy is a function of two parameters: M a n d m. These 22 parameters are respectively referred to as Minimum trigger for uploading 14 meals, and Minimum trigger for uploading 7 meals in the costing database form. Intermediary Upload Formula Let Pax! be the number of passengers booked at the 1-hour pre-departure point Let Pax 0 be the number of passengers checked-in at departure time Let Diff-,_o be the difference between Pax 0 and Pax! Let Expected Final Upload = Pax! + Avg(Di f f 1 0 ) - Main Meal Order Rule: If Expected Final Upload >= M, then order 14 meals If Expected Final Upload between M and m, then order 7 meals If Expected Final Upload < m, then make no order at this point Analysis on the intermediary upload was also conducted to determine appropriate (M, m) values. The sample data shown in Figure 11 below was obtained using the same flights as above with a reduction factor of 1 standard deviation. Four different (M, m) pairs were tested. The chosen pair was M= 18 and m = 10. This parameter choice is an adequate balance between meal cost and underage. The choice o f intermediary upload parameters does not have a significant impact on cost except maybe for the smallest pair (14,7) that is slightly higher than others. A s expected, the underage decreases as the parameters are reduced. The true benefits are achieved by simply having an intermediary upload available. The choice of these parameter values is not crucial. Total Monthly Meal Cost $220,000 , $215,000 $210,000 $205,000 » — $200,000 $195,000 J , , , 14 18 20 28 7 10 12 14 Intermediary Parameters Figure 1 1 : Intermediary upload parameter sensitivity E. Scenario Analysis This section details the different scenarios that were studied in the costing analysis. Note that the analysis was performed exclusively on economy class service. Assumptions on parameter values, added costs and procedures are detailed as well as final costing results. Full Meal Bank System Implementation Scenario In the Fu l l Mea l Bank System Implementation all flights are included in the meal bank program. • A van is scheduled to visit each flight every day before departure time. Underage 200 = O j , , , 14 18 20 28 7 10 12 14 Intermediary Parameters 23 • The schedule allows for intermediary uploads for wide body aircraft only. • The main order is purposely reduced for all flights, based on a reduction factor of 1. Reduced Meal Bank System Implementation Scenario Here, only international and long-sector transcontinental flights are included in the meal bank program. The catering procedure for other flights (short-sector transcontinental and transborder flights) is unchanged from the present situation. • A van is scheduled to visit each international and transcontinental-long flight every day both at final and intermediary upload periods. • Flights departing later than 16:00 are excluded from the system. This only excludes flights 133, 996 and 976 from the meal bank system and greatly reduces the van driver shift requirements. • Transborder and Transcontinental-short flights currently in the L A P program remain as is. • The main order is reduced for international and transcon-long flights only, based on a reduction factor of 1. Assumptions Main Order Reduction M a i n order reduction for scenario analysis purposes is defined as using a reduction factor of one standard deviation. Provisioning Objective It is assumed that the objective is to provide one meal for each passenger on board. Note that in fact, Canadian Airlines designates some of its flights as partially provisioned. These flights are catered to 90 percent of the passenger load since a significant portion of passengers usually decline meal service. These are usually short-sector flights with passengers connecting from a previous flight with a meal service. Maximum Final Upload N o documentation or set rules determines what could be the maximum upload quantity handled by L S G commissary personnel. The performance of the meal bank system is directly affected by the maximum upload limit. Costing analysis results are shown for a maximum upload quantity of 3 carriers. This implies a maximum meal quantity of 21 meals for transcontinental flights and 10 for international flights. Additional Vans It was assumed, for analysis purposes, that refrigerated vans would be used. This is necessary for allowing the re-use of meals over a 24-hour period. Two vans are already available but would need to be retrofitted with a refrigeration system. Two new 24 additional vehicles would be purchased. Costs for additional vans are discounted over a 5-year period using lease calculator estimation. Two new refrigerated vans Purchase price Down payment Lease amount Term Interest Rate Buyout value Monthly payment Total (annual) $ 40,000.00 /van $ $ 40,000.00 5 years 6% $ 10,000.00 $ 625.00 /van $ 1,250.00 for 2 vans $ 15,000.00 for 2 vans Retrofitting two existing vans Purchase price Down payment Lease amount Term Interest Rate Buyout value Monthly payment Total (annual) $ 10,000.00 /van $ $ 10,000.00 5 years 6% $ 25,000.00 $ 156.25 /van $ 312.50 for 2 vans $ 3,750.00 for 2 vans Fuel, Maintenance, Registration, and Insurance Two tanks/week $ 60.00 /van $ 120.00 for 2 vans Annual $ 6,240.00 Maint. (annual) $ 3,000.00 /van $ 6,000.00 for 2 vans Regist & Ins. (annu$ 1,200.00 /van $ 2,400.00 for 2 vans Total (annual) $ -14,640.00 for 2 vans Table 4: Total added vehicle costs Total Annual Added Cost:| $33,390.00 | Increased Man-Hours (Van Schedule) Van schedules were built for evaluating the requirements for additional vehicles and drivers for each scenario of the meal bank system. The detailed results are included in Appendix E : Delivery V a n Driver Schedules. Summary results for added man-hour costs are shown here. Driver Wages Added Cost Current Reduced System Implementation Full System Implementation $ 136,500.00 $ 250,250.00 $ 404,950.00 $ 113,750.00 $ 268,450.00 All figures annual Table 5: Added man-hour costs Meal Bank Wastage Cost analysis was performed assuming meals can be re-used on the following day. This implies that meal wastage cost would be incurred i f meals are not used on the second day, The estimated amount as shown in Appendix D : Meal Bank Wastage Cost is included in added meal bank costs. 25 Scenario Analysis Results The cost analysis was performed on each scenario. The full system implementation was rejected at an early stage since projected meal cost savings did not justify added costs. A reduced system concentrates on high meal service cost flights. The opportunity for improvement is thus higher. Flights with a similar meal service are selected to reduce diversity problems and allow pooling of safety stocks. We present the costing results for the reduced meal bank system (see Table 7). Results are divided between meal cost and meal underage quantities. The former shows the savings provided by the meal bank system and the latter shows the service level improvement. The savings are the difference between current billed costs and meal bank model output costs for flights included in the proposed meal bank program. Net savings are obtained by subtracting the added cost summarized in Table 6 to the savings. Service level is measured by the total number of meal shortages for a given month. I f this amount increases, service level decreases and a negative figure is displayed. Service level is shown for revenue passengers only as well as for all passengers. Vehicles Man-Hours Meal Wastage Total Cost $ 2,782.50 $ 9,479.17 $ 3,288.35 $ 15,550.02 Al l c o s t s per m o n t h Table 6: A d d e d cost summary Detailed costing analysis results for each flight on each month is provided in Appendix F: Costing Analysis Results. Note that irregular events are taken out of the results. These are events where an unusual change in the booked load occurs between the main order and the final upload. A meal bank model based on average behaviour cannot handle such events. In the model, these flights w i l l suffer large meal underage. The method of identification of the outliers is detailed in Appendix F: Costing Analysis Results. The net savings and percent underage reduction results vary significantly across test months. This variation is mostly due to the actual performance of the caterer on each of these months. If over-provisioning was frequent over a given month, the underage was usually low. In such a case, the cost savings are high and the underage reduction is low (e.g. February). The reverse situation occurs i f flights were actually provisioned at a lower level with more under-provisioning (e.g. March). The schedule change in A p r i l resulted in high potential savings. During that month, all flights were over-catered as a safety measure for adjusting to the new schedule. We also observe that the opportunity for improvement in both cost and service level decreases as the load factor increases. For example, June was the first month of the high season for traveling, where most flights were full (high load factor). For that month, the net savings and percent underage reduction were low. 26 Meal Cost Month Savings (Loss) Net Savings (Loss) Feb-99 $ 29,690.50 $ 14,140.48 Mar-99 $ • 11,941.15 $ (3,608.87) Apr-99 $ 76,406.84 $ 60,856.82 May-99 $ 21,832.09 $ 6,282.07 Jun-99 $ 17,050.00 $ 1,499.98 Underage - Revenue Passengers Only Month Current Meal Bank Reduction Percent Reduction Feb-99 Mar-99 Apr-99 May-99 Jun-99 0 16 43 66 33 11 0 33 6 19 (11) 16 10 60 14 0.0% 100.0% 23.3% 90.9% 42.4% Underage - All Passengers Month Current Meal Bank Reduction Percent Reduction Feb-99 Mar-99 Apr-99 May-99 Jun-99 36 140 194 212 104 35 0 99 63 102 1 140 95 149 2 2.8% 100.0% 49.0% 70.3% 1.9% Table 7 : Reduced system analysis results 27 V - Discussion and Recommendations A. Meal Ordering The main order should be done using the 3-hour booked load, as the caterer is responsible to adjust the order up to this period. The main order is set to be lower than the current booked load using the following formula: Main Order = Booked Load 3 . h o u r + Mean(Difference f i n a i - 3-hour) - Standard Deviation(Difference f i n a i - 3-hour) The intermediary upload w i l l be done at the 1-hour pre-departure mark. The upload size is either 7 or 14 meals. The decision is made according to the following decision rule: Let Expected Final Upload = Booked Load i.hour + Mean(Difference n n ai- i -hour) - MainOrder If Expected Final Upload >= 18, then order 14 meals. If Expected Final Upload between 18 and 10, then order 7 meals. If Expected Final Upload < 10, then make no order at this point. B. Meal Production Control and Inventory Management Main Order N o changes are proposed for the production of meals for the main order. The main order w i l l be automatically generated. The kitchen w i l l simply use the output number and build the order accordingly. Use of passenger load information prior to the 3-hour point can be incorporated into the model to provide the kitchen with production quantity forecasts. Intermediary Upload The intermediary upload could be either a decision coming from the flight controller, from the kitchen, or automatically generated. Generally, the latter would be the preferred option since it has the advantage of reducing the risk of confusion and could reduce communication requirements, especially during busy periods of the day. Automation also reduces the responsibilities of flight controllers. However, flight controller intervention should be possible, and is required, to cope with irregular operations caused by canceled flights or missed connections. Meal Bank Production The recommended meal bank w i l l be composed of traditional T S U ' s with pop-out casseroles. The production process w i l l remain unchanged. 28 Four types of meals w i l l be required: one for each service for international flights, one breakfast service, and one lunch/dinner service for transcontinental flights. Meal Bank Inventory The bank w i l l be held in the same refrigerating area and tagged using the current system. Significant cost savings can be achieved through the use of a long-lasting meal with a refrigerated van fleet. However, according to Food & Beverage Standards, the risk involved in the delivery process is a potential problem even i f vehicles are refrigerated. C. Equipment Balancing The issue of deadhead equipment reduction is in progress. Aircraft operations w i l l be eased i f it is determined from the parallel equipment balancing simulation project that we do not require a full deadhead policy. D. Transportation Logistics Scheduling for the reduced system scenario requires the use of four delivery vans with five shifts. This requires the addition of two vans and more driver shifts. Refer to Appendix E : Delivery V a n Driver Schedules for the base schedule. E. Aircraft Operations The visit time for final uploads w i l l be 20 minutes prior to departure for all flights included in the program. A n alternative way to communicate the upload quantity to the drivers is required. The final upload cannot be larger than 3 carriers. Final Upload Order Communication The focus group team recommended the use of gate telephone communication. It was also suggested to provide the van drivers with cellular phones. This w i l l reduce communication time by allowing the drivers to get the order quantity information from their vans before getting to the aircraft. Busy signals on the gate telephone could be avoided by equipping the line with call-waiting. The distinct ring option could also be added to the lines such that controllers would know that the call is coming from catering and give it a higher priority. The Vancouver International Airport Authority manages these gate telephone lines. We could not get a final answer on the cost and feasibility of such changes to the airport lines. 29 F. Scenario Selection We recommend the implementation of the reduced meal bank system as presented in Table 7. Testing of such a system on five months from February 1999 to June 1999 at Vancouver International Airport confirmed the potential for average monthly cost savings in the order of $15,800 and service level improvement of approximately 50% less shortages for revenue passengers. The system should be targeted at economy class meals only. The production of a meal bank for business class would not be practical unless quality standards are lowered. We also observed that with new upgrade policies in place, the business class load is close to capacity on most flights. A special procedure must be in place in the event of a flight requiring a final upload exceeding the maximum amount. Meal pouches or shelf-stable meals could be used to provide all passengers with a meal. Space should be dedicated for these meals on all flights as a precautionary measure. These meals may be especially useful for non-revenue passengers on long-haul flights. Special attention should be given to irregular operation as a cause for poor service level performance. Outliers drive the number of under-catered instances in the meal bank model results. The ordering policy tool cannot predict irregular behavior in booked passenger patterns. Several factors can explain such a behavior. Although the flight controller may be aware of irregular operations as they are taking place, it is not clear how to accurately model the response without more data or anecdotal information. The month of A p r i l causes problem due to the flight schedule change. The system makes decisions on meal orders based on past data. If new flights are added, no past data is available to calculate the passenger count difference distribution. We propose a buffer period during which these flights are safely catered and data can be accumulated. New flights added to the schedule in Apr i l were excluded from the cost savings and service improvement estimates for A p r i l data and then inserted in the analysis for May. G. Markov Decision Process Model A s explained in the background section, a possible solution for a new ordering policy is to use the Markov Decision Process model developed by Jason Goto. Using a simpler decision rule, the database approach was more flexible and adaptable to real data. Although it is not providing an optimal solution, the database model is a more practical and efficient approach to obtaining cost savings and service improvement estimates for the meal bank system. The M D P model was implemented using S A S . One of the basic requirements for running the model on a given flight number is that the aircraft capacity would be constant across all dates in the test data range. This is essential since the M D P model calculates 30 optimal decisions based on past information for that flight where the capacity is constant. Unfortunately, the data on aircraft capacity contains a lot of variability for most of the flights. This results in incomplete output data where only flight dates where the aircraft capacity is equal to that entered as a run-time parameter are kept. This problem would require that the data be cleaned to have uniform aircraft capacity. Another issue with the M D P model involved the time required to run a test policy for each flight. The run time of the M D P model is more reasonable for aggregate models of the state space. This may introduce some error due to rounding. The use of the MSAccess database model developed in the current project presented in this document did not cause such problems. H. Confirmation Study The purpose of this section is to sketch some of the major issues that the implementation plan must address as wel l as some suggested approaches that may be used as a guide to building the plan. The following is not intended to be a substitute for detailed implementation planning. Prior to any implementation, a minimum two-week confirmation study should be undertaken in order to confirm the results of this study. There are two broad objectives of the confirmation study. The first is to ensure that the recommended approach is considered feasible by users. The approach needs to be completely transparent to users in order to identify any deficiencies prior to implementation. The second objective o f the confirmation study w i l l be to provide an opportunity to get user feedback prior to the implementation. User feedback may provide opportunities for improvements that have not been identified in the analysis. The confirmation study should use recent historical flight data and passenger load data. The month o f September 1999 could be used for this purpose since it is within the summer flight schedule but was not included in the analysis. The goal is to develop an understanding of what would have happened i f we had implemented the findings of the study. In particular, we would like to ensure that the flights that required uploads would have received the necessary upload amount. Irregular operations need to be examined to ensure that, in such cases, human intervention could improve on the performance o f the model. We w i l l also identify the benefits that would have accrued to Canadian i f the recommendations of the study had been applied. Certain areas of the proposed process improvements would need further analysis and discussion. These areas w i l l be addressed in the confirmation study. The first area is the flight schedule change at the end of October. The delivery vehicle assignments should be revised according to the winter schedule to ensure adequate coverage of meal bank flights. Second, the new communication procedure between the delivery personnel and flight controller requires testing. Third, we must confirm that sufficient time and meal 31 quantity is allocated for performing meal uploads. Fourth, we must ensure that the vehicle refrigeration system for reusability of meals meets safety standards. /. Implementation The implementation should be done in progressive steps. A first phase could be planned with a few flights within the same meal group using a single van. This step would provide additional consultation to ensure that personnel support the changes. Once this first step is confirmed another vehicle and additional flights could be added to the meal bank system. This progressive approach would eventually lead to a full roll-out of the proposed changes. 32 VI - Conclusion In this document, we demonstrated the potential for service improvement and meal cost savings through a revision of the catering process at Canadian Airlines. The study covered flight operations at Vancouver International Airport. The proposed system is restricted to economy class service on a number of flights in the international and long-sector transcontinental groups. Through the present study, Canadian Airlines management gained valuable insight regarding the hard constraints of the meal process. The study allowed for a better understanding of the current operations and the costs associated with them. It also proposed improvements where appropriate. Following the completion and acceptance of the proposed process changes, a detailed implementation plan is required i f the findings of this study are to be implemented. 33 Bibliography Canadian Airlines International web site, http://www.cdnair.ca Canadian Food Inspection Agency (1998). Food Safety Enhancement Program (FSEP) Manual - Volume One, http://www.cfia-acia.agr.ca/english/ppc/haccp/manual/home.html. Goto, Jason H . (1999). A Markov Decision Process Model For Airline Meal Provisioning. Richard B. Chase (1998). Production and operations management: manufacturing and services - 8th ed., M c G r a w - H i l l . Makridakis, Spyros G . (1998). Forecasting: methods and applications - 3rd ed., John Wi l ley & Sons Inc. 34 Appendix A: LAP Meal Diversity and Quality Ideally one type of L A P meal would fit all flights. Unfortunately, differences in aircraft configuration do not permit this and as a result 11 different L A P meals are required. Variable Values Possibilities Class of passenger Business (J) or Economy (Y) 2 Type of meal Breakfast, cold lunch/dinner (J only), hot lunch/dinner 3 Tray dimensions 331,358, 1011, 1611 4 China type (J only) Royal Doulton, White 2 Table 8 : Meal type variables The class of passenger, tray size, and china type variables are inter-dependent. They are related to aircraft type also. If it were not for the tray size and china type variables, 5 types of meal would do; one breakfast meal for each passenger class, the same for hot lunch/diner, and one J-class cold lunch. Trays are necessary for trolley size limitations. China is an aesthetics issue, but deemed important to the airline. China is an issue only for J class meals. Eliminating china diversity could save significant costs in inventory stock reduction and employee time, on top of simplifying L A P meal operations. Using a single type of china could eliminate two of the current 11 L A P meal varieties. Type of meal Passenger class Tray size China L A P 1 Breakfast J 331 L A P 2 Breakfast J 1611 Royal Doulton L A P 3 Breakfast J 1611 White L A P 4 Breakfast Y 331 L A P 5 Breakfast Y 1011 L A P 6 Hot lunch/diner J 331 L A P 7 Hot lunch/diner J 1611 Royal Doulton L A P 8 Hot lunch/diner J 1611 White L A P 9 Cold lunch/diner J 1611 L A P 10 Hot lunch/diner Y 331 L A P 11 Hot lunch/diner Y 1011 Table 9: Current LAP meals If the L A P program were to be extended to all routes, the number of L A P meals would increase for the following reasons: second meal on certain international flights, different meals on different route sectors, picnic baskets, short-sector flight meals on a 358 tray. 35 Appendix B: Preliminary Analysis Winnipeg and Ottawa p) C C §5 = s a s Edmonton Flights S S § r? 5 £ a S ~ =: a c — a PI Figure 12: Sample final passenger load variability Capacity 88 leaving YVR on Mondays Test Set Flight 7/6/98 7/13/98 7/20/98 7/27/98 1 85 85 82 75 2 77 77 84 71 3 72 79 59 64 4 68 81 80 68 5 74 77 76 69 6 82 81 87 79 7 81 79 81 88 e 74 80 64 68 9 67 40 45 49 10 86 75 81 76 11 87 81 76 84 12 82 86 84 80 13 88 71 69 83 14 77 81 77 71 15 59 51 56 33 16 48 58 83 42 17 44 51 41 43 18 70 51 53 43 19 50 48 33 39 20 88 79 79 68 21 77 76 68 66 22 88 87 69 60 23 82 84 84 81 24 88 88 88 88 25 84 88 84 88 26 87 56 75 66 27 77 47 42 73 28 88 70 76 48 29 85 78 84 73 30 68 71 86 76 31 67 68 ' 81 86 32 83 73 85 73 33 77 68 71 70 34 71 70 75 88 35 75 53 59 58 36 52 54 76 37 37 34 36 46 41 38 77 75 76 82 39 83 61 48 54 40 77 73 50 67 41 64 80 88 67 42 67 85 75 54 43 66 66 68 77 44 22 28 39 43 Initialization Set Avg StdDev Min 66-176 16.727 28 55.514 15.888 31 45.868 20.294 16 49.763 14.615 22 53.343 15.324 29 73.735 8.0653 52 78 13.134 45 62.813 13.299 30 27.167 22.146 2 57.483 23.828 16 67.344 14.302 34 71.7 7.6891 56 65.531 13.078 41 64.667 16.883 26 69.375 15764 40 62.73 18.133 0 58.526 13.669 41 62.316 18.695 29 47 1.4142 46 76.974 8.1589 55 72.605 13.814 26 73 12.789 42 71.179 15.302 28 72.229 15458 40 80.921 10.607 50 64.361 16.701 33 56.514 18.625 29 71.8 12.776 39 71.622 16.892 33 57429 25.913 14 71.441 15.812 29 67.657 19.873 32 69444 15.522 39 70.769 14.141 45 62.147 17.921 29 67.313 16.772 41 63.306 18 448 0 81 459 7.4297 57 54.658 20.062 20 61.722 18.064 23 37.182 25.339 7 59.636 16.794 34 45.25 15.295 24 47.286 10.688 38 Initialization Set Range: 10/13/97 - 6/29/99 Overage (Final Upload) Main Order Quantity Supplying mean - 1 stdev Supplying mean - 2 stdev mean - 1 stdev mean-2stdev 7/6/98 7/13/98 7/20/98 7/27/98 7/6/98 7/13/98 7/20/98 7/27/98 49 33 (36) (36) (33) (26) (52) (52) (49) (42) 40 24 (37) (37) (44) (31) (53) (53) (60) (47) 26 5 (46) (53) (33) (38) (67) (74) (54) (59) 35 21 (33) (46) (45) (33) (47) (60) (59) (47) 38 23 (36) (39) (38) (31) (51) (54) (53) (46) 66 58 (16) (15) (21) (13) (24) (23) (29) (21) 65 52 (16) (14) (16) (23) (29) (27) (29) (36) 50 36 (24) (30) (14) (18) (38) (44) (28) (32) 5 -17 (62) (35) (40) (44) (84) (57) (62) (66) 34 10 (52) (41) (47) (42) (76) (65) (71) (66) 53 39 (34) (28) (23) (31) (48) (42) (37) (45) 64 56 (18) (22) (20) (16) (26) (30) (28) (24) 52 39 (36) (19) (17) (31) (49) (32) (30) (44) 48 31 (29) (33) (29) (23) (46) (50) (46) (40) 54 38 (5) 3 (2) 21 (21) (13) (18) 5 45 26 (3) (13) (38) 3 (22) (32) (57) (16) 45 31 1 (8) 4 2 (13) (20) (10) (12) 44 25 (26) (7) (9) 1 (45) (26) (28) (18) 46 44 (4) (2) 13 7 (6) (4) 11 5 69 61 (19) (10) (10) 1 (27) (18) (18) (7) 59 45 (18) (17) (9) (7) (32) (31) (23) (21) 60 47 (28) (27) (9) - (41) (40) (22) (13) 56 41 (26) (28) (28) (25) (41) (43) (43) (40) 57 41 (31) (31) (31) (31) (47) (47) (47) (47) 70 60 (14) (18) (14) (18) (24) (28) (24) (28) 48 31 (39) (8) (27) (18) (56) (25) (44) (35) 38 19 (39) (9) (4) (35) (58) (28) (23) (54) 59 46 (29) (11) (17) 11 (42) (24) (30) (2) 55 38 (30) (23) (29) (18) (47) (40) (46) (35) 32 6 (36) (39) (54) (44) (62) (65) (80) (70) 56 40 (11) (12) (25) (30) (27) (28) (41) (46) 48 28 (35) (25) (37) (25) (55) (45) (57) (45) 54 38 (23) (14) (17) (16) (39) (30) (33) (32) 57 42 (14) (13) (18) (31) (29) (28) (33) (46) 44 26 (31) (9) (15) (14) (49) (27) (33) (32) 51 34 (1) (3) (25) 14 (18) (20) (42) (3) 45 26 11 9 (D 4 (8) (10) (20) (15) 74 67 (3) (D (2) (8) (10) (8) (9) (15) 35 15 (48) (26) (13) (19) (68) (46) (33) (39) 44 26 (33) (29) (6) (23) (51) (47) (24) (41) 12 -13 (52) (68) (76) (55) (77) (93) (101) (80) 43 26 (24) (42) (32) (11) (41) (59) (49) (28) 30 15 (36) (36) (38) (47) (51) (51) (53) (62) 37 26 15 9 (2) (6) 4 (2) (13) (17) Average (25) (21) (22) (18) (41) (37) (38) (34) Sum (1.108) (956) (993) (819) (1,824) (1.672) (1.709) (1.535) Table 10 : Sample first policy results 36 Flights From YVR on Mondays Test Set Pax6 Flight 7/6/98 7/13/98 7/20/98 7/27/98 1 70 75 74 91 2 83 78 70 68 3 93 71 70 77 4 91 101 97 92 5 69 44 36 60 6 80 83 82 72 7 85 94 101 94 8 83 46 47 75 9 78 55 53 41 10 80 77 55 76 11 93 94 72 68 12 91 75 84 49 13 243 249 212 208 14 88 84 93 72 15 159 190 196 171 16 96 87 89 85 17 212 173 160 192 18 91 85 87 84 19 91 89 96 87 20 84 98 69 89 21 89 93 96 90 22 76 74 76 91 23 189 156 129 153 24 70 25 91 80 88 84 26 101 103 91 96 27 107 96 93 119 28 94 77 82 94 29 104 78 118 93 30 100 78 106 95 31 101 88 91 87 32 88 87 83 72 33 99 91 92 91 34 94 77 92 82 35 188 156 102 134 36 175 185 191 196 37 92 88 81 92 38 319 194 186 189 39 80 68 82 71 40 103 99 95 106 41 76 87 94 86 42 45 65 74 53 43 92 78 82 80 44 93 95 92 93 45 84 72 65 74 46 75 74 73 79 47 94 85 89 95 48 80 58 63 51 49 92 56 81 71 50 85 85 77 71 51 100 63 48 56 52 56 52 54 32 53 240 255 226 230 54 46 50 45 48 55 70 42 42 48 56 180 173 197 Init. Set Diff6 0 Mean StdDev -6.2857 4.8902 -3.8095 4.5893 0.6667 10.758 -5.3182 3.5908 1.5909 6.5441 -5.1905 3.7232 -11.857 10.155 0.5263 9.8959 -1.4545 4.5327 0.1579 6.9862 -1.0455 7.2274 -1.5455 6.724 7.1429 18.575 -0.3333 8.3506 -9.619 30.331 -11.455 16.919 -5.0476 12.048 -0.2857 8.2592 -4.3636 4.4137 -9.1818 9.8833 -8.55 8.3001 3.6 8.0092 8.6818 29.806 -4 9.3808 -5.0455 4.8352 -3.6364 11.253 -7.6364 6.6372 -1.7273 7.2518 -4.3636 4.2375 -22 20.474 -5.9524 6.3834 -2.7619 3.9485 -5.1111 7.3396 13.15 27.639 -0.0952 15.446 -46.182 32.408 -5.7368 7.8233 -2.3333 7.2134 1.6818 9.6973 -8.2857 7.0295 2.1905 11.197 3.35 5.7057 -6.2727 6.212 -2.6818 7.4219 1.4545 12.872 -6.5 10.071 -5.0476 4.5987 3.2 9.9398 -3.1429 5.6505 -4.8636 7.4405 0.8571 6.6429 -1.4286 4.0196 -14.4 13.801 -3.5238 7.6917 -2.25 8.9788 -13.9 10.182 Main Order* 7/6/98 7/13/98 7/20/98 7/27/98 59 64 63 80 75 70 62 60 83 61 60 67 82 92 88 83 64 39 31 55 71 74 73 63 63 72 79 72 74 37 38 66 72 49 47 35 73 70 48 69 85 86 64 60 83 67 76 41 232 238 201 197 79 75 84 63 119 150 156 131 68 59 61 57 195 156 143 175 82 76 78 75 82 80 87 78 65 79 50 70 72 76 79 73 72 70 72 87 168 135 108 132 57 81 70 78 74 86 88 76 81 93 82 79 105 85 68 73 85 95 69 109 84 58 36 64 53 89 76 79 75 81 80 76 65 87 79 80 79 80 63 78 68 172 140 86 118 96 106 112 117 78 74 67 78 309 184 176 179 72 60 74 63 88 84 80 91 67 78 85 77 43 63 72 51 80 66 70 68 83 85 82 83 73 61 54 63 58 57 56 62 84 75 79 85 73 51 56 44 83 47 72 62 73 73 65 59 94 57 42 50 51 47 49 27 212 227 198 202 35 39 34 37 59 31 31 37 156 149 173 * Pax6 + [Avg(Diff6_0) - Stdev(Diff6_0)] Test Set PaxO Meal bank requirement" 7/6/98 7/13/98 7/20/98 7/27/98 7/6/98 7/13/98 7/20/98 7/27/98 66 70 72 77 7 6 9 -3 78 79 70 67 3 9 8 7 92 68 78 79 9 7 18 12 82 85 87 79 0 -7 -1 -4 67 45 32 67 3 6 1 12 78 81 77 71 7 7 4 8 72 81 81 70 9 9 2 -2 78 49 46 78 4 12 8 12 78 52 55 47 6 3 8 12 81 75 59 74 8 5 11 5 88 87 76 66 3 1 12 6 88 77 78 57 5 10 2 16 236 221 223 200 4 -17 22 3 85 79 87 75 6 4 3 12 166 178 172 179 47 28 16 48 77 79 86 73 9 20 25 16 103 189 -40 14 83 86 88 86 1 10 10 11 86 82 75 6 -5 -3 74 79 60 64 9 0 10 -6 74 77 76 70 2 1 -3 -3 77 78 88 86 5 8 16 -1 179 175 139 156 11 40 31 24 67 10 80 86 79 88 -1 16 1 14 86 98 86 88 0 10 10 7 108 100 102 100 15 18 23 -5 81 87 83 88 -4 19 10 3 99 71 108 79 4 2 -1 -5 89 61 81 79 31 25 17 26 87 83 81 84 -2 7 2 9 88 80 79 70 7 0 3 5 88 88 88 88 1 9 8 9 83 80 88 75 3 17 10 7 177 140 153 110 5 0 67 -8 171 164 178 196 75 58 66 79 86 78 86 77 8 4 19 -1 317 195 179 176 8 11 3 -3 77 70 74 76 5 10 0 13 96 103 103 95 8 19 23 4 64 80 88 69 -3 2 3 -8 49 59 84 44 6 -4 12 -7 88 71 73 85 8 5 3 17 88 84 88 3 2 5 82 76 88 21 22 25 75 62 74 18 6 12 88 85 80 13 6 -5 55 64 58 4 8 14 59 78 69 12 6 7 80 65 68 7 0 9 64 55 58 7 13 8 53 56 33 6 7 6 219 199 196 -8 1 -6 51 42 44 12 8 7 42 48 49 11 17 12 174 164 172 18 15 -1 4080 4911 4986 4929 Average 8.3415 9.6296 10.127 8.0909 Sum of positive 352 556 607 516 Sum of negative -10 -36 -50 -71 Max 75 58 67 79 Min -4 -17 -40 -8 * If positive, values are final upload. If negative, values are meal overage Table 11: Sample second policy results 37 Appendix C: Costing Analysis Database This appendix describes the structure and content of the Costing Analysis Database. The appendix is divided into two sections. The first section describes data source issues. The second section explains how the database functions. This is done by describing every form, report and query in the database detailing its purpose and how data is filtered and calculated. A. Data Source Issues Issue of full data set vs. test subset The final recommendations are mainly based on the comparison of cost and quantities obtained from the ordering policy testing model against current cost figures obtained from Canadian Airlines data archives. Comparison is done on a monthly total basis. A n important aspect of the analysis is to ensure that both the actual and model values are obtained from the same set of flight/dates. A s it occurs, the source data used in the model is not complete. Holes in pre-departure data force the model to exclude certain flights/dates. A s a result the model output set is slightly smaller than the total actual set for a given month. We therefore ensure a valid comparison by reducing the actual flight data set to be equal to the one used for the model. This reduced set of flight w i l l be referred to as the test subset in this document. Note that the test subset is approximately 97% of the real set for the flights included in the final scenario analysis. Current meal cost Canadian Airlines information systems keep track o f all caterer invoice figures. The information contains invoice details down to the item number and description. This information can be retrieved from a table that lists all items that were charged by the caterer for a given flight on a given date, along with the actual expensed amount, the corresponding accounting code, and the class of service code. A query was designed using the accounting code as a filter to strictly obtain food item costs. In order to compare invoiced amounts to model-generated costs certain items should be screened out. These include special meal surcharges, deadhead equipment charges, L A P meal charges, and reduced tray charges. A surcharge is added to the invoice on top of the regular price of a meal i f it is changed to a special meal. The fee for deadhead equipment is introduced for the handling and washing of the empty trays used to f i l l up the galleys. L A P fees are added i f a flight requires an upload from this program. A reduced tray charge is added i f flights, for which a meal service was prepared, are canceled on short notice. A l l the above-described charges would neither be captured nor affected by the model costing. 38 Note that the FCPV107t table dynamically grows as invoices are received. A small portion of the real cost could be missing due to late invoices, rejected invoices, or retroactive price adjustments billed later. Current Meal Quantity The number of meals catered is obtained from invoice data just as the meal cost described above. In this case though, a different table containing summary invoice information is used instead o f the one containing information at the item level. B. Database Structure The policy tool architecture consists of layers of tables, queries, reports, and forms. What the user sees is a form with text boxes, and action buttons. Changing the values of parameters inside the form allows for testing different policies. To see the results of a test policy, the user presses a button that prints a report containing performance measures. The reports extract information from queries and perform statistical calculations on them. There are multiple layers of queries. The top layer assembles the appropriate information in one summary table. The intermediary layers create fields calculated from Terradata table values. The bottom query layer extracts necessary fields from the table layer. The base tables are directly imported from the Terradata information system. Main Form (Forml) This form is the main menu for the whole costing database. It serves two functions: entering the parameters and data ranges for running a single test policy, and launching the reports for the given parameter set. Policy Parameters Minimum trigger for uploading 14 meals (M) Parameter used in the intermediary upload formula explained below. Min imum trigger for uploading 7 meals (m) Parameter used in the intermediary upload formula explained below. Under-catering Factor (Reduction Factor) Parameter used in the main order formula. This fraction is multiplied by the standard deviation of the 3hr-to-departure difference distribution. Underage Cost per Passenger Dollar value cost for each instance of a passenger without a meal. I f set to 1, the result is simple underage meal quantity. Maximum Final Upload Maximum number of meals that can be added to a flight at the final upload. 39 SU Costing Analysis Database E M E U ( . l l l . l l l i >u A i i i i l i e s v \ Intermediary Upload - -Minimum trigger for uploading 14 meals Minimum trigger (or uploading 7 meals Under-calering Factor Underage Cost per Passenger Maximum Final Upload Data Range Start date for historical data End date for historical data Start date for test period End date for test period 2/1/99 3/31/99 4/1/99 4/30/99 From Flight To Flight Current System Report Actual Billed Report AMOS report Meal Bank Report Figure 13: Costing database user interface Data Range Parameters From Flight Lower bound of the range of flights to be tested. Coded by flt_num. To Flight Upper bound of the range of flights to be tested. Coded by fltnum. Start date for historical data Lower bound of the date range for the data sample period (initialization set). Stop date for historical data Upper bound of the date range for the data sample period (initialization set). Start date for test period Lower bound of the date range for the data test set (holdout set). Start date for test period Lower bound of the date range for the data test set (holdout set). Buttons Current System Report Prints the report containing the actual cost results based on meal quantity. Meal Bank Report Prints the report containing the model cost results for the specified ordering policy. 40 A M O S Report Prints the report containing the A M O S model cost results using the specified delivery parameters (max upload, minforl4, minfor7). Actual B i l l ed Report Prints the report containing the actual cost results based on invoice history. Actual Billed Cost Billed cost Report Presents data from b i l l edcos t query and calculates totals for each column. Billed cost Query Extracts records from Detailed_bill_yvrJantoAug99 table that match records in HST_01_subset (test subset) on the basis of date, flight number, departure station, arrival station, and class of service code. This record matching step is necessary to ensure that both the actual and model values are obtained from the same set of flight/dates. Filters B R D F L T N R Inside flight number range from M a i n Form. C O S _ C D Y class only C T G C D 3 Exclude code for Special Meals I T M N R Exclude codes for Reduced Trays, Deadhead charge, and L A P meal delivery charges. B R D F L T D T Inside data test set Current Cost (Actual Meal Quantity) Cost real Report Presents data from cost_real query and calculates totals for each column. Cost real Query Joins records from cos t rea lmea l s query and cost_real_underage query with matching flight numbers. Filters F L T N R Inside flight number range from M a i n Form. Formulas UnderageCost_rev IIf([MealCost]=0,0,[SumOfUnderageCost_rev]) If a flight has no meal service (cost) default revenue passenger underage to zero. 41 UnderageCost_all IIf([MealCost]=0,0,[SumOfUnderageCost_all]) If a flight has no meal service (cost) default total underage to zero. Cost real meals Query This query multiplies the number of meals catered by the unit price of each item scheduled for any flight. • Meal quantity taken from HST_Ol_over_ml_lvCHlST query. • Scheduled meal service items taken from f l i g h t _ i t e m s _ Y V R _ Y _ J a n _ J u n 9 9 • Meal service item prices taken from item_prices_YVR_updated2 Filters I T M P R C E F F D T Price effective date must be before lower bound of data test set I T M _ P R C _ D I S _ D T Price discontinued date must be after upper bound of data test set C T G _ C D _ 3 Only extract records with codes for food items d p t s t a c d Y V R only F L T D T Inside data test set C O S _ C D Y class only Formulas There are two different pricing methods: one using a provisioning ratio, and one using a set quantity per passenger rule. The provisioning ratio simply indicates what percent fraction of the total load should be provided with the item, e.g. 60% chicken casserole and 40% beef. The set quantity figure indicates to supply one item per X passengers, e.g. supply 1 B B Q pork bun per 9 passengers. Note that the great majority of meal items are supplied based on a ratio function. Mult iplying the meals catered quantity by a ratio yields fractional meal quantities. The caterer's ordering system rounds up these ratio quantities before multiplying them by their unit price. This is replicated in the costing formula by using the standard CintO function. MealCost Sum(IIf([PVN R A T CD]="P",[ITM P R C A M T ] * C I n t ( [ P V N R A T Q T Y ]/100*[CTR P S G QTY]) , [ ITM P R C A M T ] * C i n t ( [ C T R P S G Q T Y ] / [ P V N _ R A T _ Q T Y ] ) ) ) Calculation of cost based on meal quantity ( C T R P S G Q T Y ) from HST_01_over_ml_IVCHIST query. OverCost Sum(Hf([PVN R A T CD]="P",[ITM P R C A M T ] * C I n t ( [ P V N R A T Q T Y ]/T00*[over m l qty]),[ITM P R C AMT]*CInt([over ml qty] / [PVN R A T QTY]))) Calculation of overage cost based on over-catered meal quantity ( o v e r m l q t y ) from H S T O l o v e r m l J V C H I S T query. 42 HST 01 over ml IVCHIST Query Extracts records from lnvoice_hist_YVR_JantoJun99 that match records in HST_Ol_subset (test subset) on the basis of date, flight number, departure station, arrival station, and class of service code. This record matching step is necessary to ensure that both the actual and model values are obtained from the same set of flight/dates. Filters BRD FLT NR Inside flight number range from Main Form. COS CD Y class only CTG_CD_3 Exclude code for Special Meals ITM_NR Exclude codes for Reduced Trays, Deadhead charge, and LAP meal delivery charges. B R D F L T D T Inside data test set from Main Form C T R I V C O P C D Only use accounting code 00 (Routine scheduled service) Formulas over ml qty IIf([CTR_PSG_QTY]-[ob_rev_psg_qty]-[ob_nrv_psg_qty]>0, [CTR_P S G Q T Y] - [ob_rev_psg_qty] - [ob_nrv_psg_qty] ,0) Number of meals over-catered is the difference between meals catered minus total on-board passengers. If negative, default to zero. Cost real underage Query Calculates totals grouped by flight number on fields in cos t_ rea l_underage_a l l . Filters Dpt_sta_cd YVR only F L T D T Inside data test set from Main Form Cost real underage all Query Extracts records from HST_ 0 1_subset in order to calculate underage figures. Filters COS CD Y class only Formulas Underage_nrv_incl [ob_rev_psg_qty]+[ob_nrv_psg_qty]-[act_ctr_ml_qty] Total number of meals under catered (both revenue and non-revenue passengers) UnderageCost_all IIf([Underage_nrv_incl]>0,[Underage_nrv_incl] * [Forms]! [Form 1 ] .[undercost],0) Total under-catering cost (revenue &non-revevue) based on 43 Underage Cost per Passenger in Main Form. UnderageCostrev IIf([Underage]>0,[Underage] * [Forms]! [Form 1 ]. [undercost],0) Revenue passengers under-catering cost based on Underage Cost per Passenger in Main Form. Meal Bank Cost (Model) Cost Report Presents data from c o s t query and calculates totals for each column. Cost Query Joins records from cost_meals query and cost_under query with matching flight number, departure station, and arrival station. Filter FLT NR Exclude flight 8 Cost meals Query This query multiplies the number of meals catered by the unit price of each item scheduled for any flight. • Meal quantity taken from Ext_MBDemand query. • Scheduled meal service items taken from f light_items_YVR_Y_Jan_Jun99 • Meal service item prices taken from item_prices_YVR_updated2 Filters Same as Cost_real_meals Query Formulas MealCost Sum(IIf([PVN RAT CD]="P",[ITM PRC AMT]*CInt([PVN R A T QTY ]/100*[MealsCatered]),[ITM PRC AMT]*CInt([MealsCatered]/[PVN R A T_QTY]))) Calculation of cost based on meal quantity (MealsCatered) from Ext_MBDemand query. OverCost Sum(IIf([PVN_RAT CD]="P",[ITM PRC AMT]*CInt([PVN R A T QTY ]/100*[over ml qty]),[ITM PRC AMT]*CInt([over ml qty]/[PVN R A T QTY]))) Calculation of overage cost based on over-catered meal quantity (over_ml_qty) from Ext_MBDemand query. Cost under Query Calculates underage cost from Ext_MBDemand query. 44 Filters FLT DT Inside data test set from Main Form Formulas UnderCost Sum([under_ml_qty]* [Forms]! [Forml].[undercost]) Multiply the number of under-catered meals for revenue passengers only by the Underage Cost per Passenger in Main Form. UnderCostnrv Sum([under_ml_qty2] * [Forms]! [Form 1 ]. [undercost]) Multiply the number of under-catered meals for all passengers by the Underage Cost per Passenger in Main Form. Ext MBDemand Query Two options: 1) Extract records from MBDemand 2) Extract records from MBDemandnonrv In both cases this query calculates the amount of meals catered for each flight, the total passenger quantity, the number of meals under-catered (total), the number of meals under-catered (revenue only), and the final upload quantity. Formulas MealsCatered [lhrUpload]+[Final_Upload]+[MainOrder] The total number of meals catered is the sum of all three deliveries. Paxqty [ob_rev_psg_qty]+[ob_nrv_psg_qty] Total passenger quantity is the sum of on-board revenue and non-revenue passengers. Under_ml_qty IIf([ob_rev_psg_qty]-[MealsCatered]>0,[ob_rev_psg_qty]-[MealsCatered],0) The number of under-catered meals for revenue passengers only. If negative, default to zero. Under_ml_qty2 IIf([Pax_qty]-[MealsCatered]>0,[Pax_qty]-[MealsCatered],0) The number of under-catered meals for all passengers. If negative, default to zero. Final_Upload IIf([FinalUpload]>0,IIf([FinalUpload]<Int([Forms]![Forml].[maxupl oad]), [FinalUpload], [Forms]! [Form 1 ]. [maxupload]),0) This constrains the final upload quantity from MBDemand query to the maximum upload set in the Main Form. MDDemand Query This query models the ordering policy. It calculates the order quantities for the main, intermediary, and final uploads. These decisions are made on descriptive statistics of 45 inter-period passenger counts, taken from YVRf l i g h t s M e a n D i f f e r e n c e s query, and current multi-period passenger counts, taken from D i f f e r e n c e T a b l e . Filters F L T J D T Inside data test set from M a i n Form F L T N R Inside flight number range from Main Form Dpt sta cd Y V R only C O S _ C D Y class only 4 Not null. Eliminate a record i f data for this query period is missing. 2 Not null. Eliminate a record i f data for this query period is missing. 0 Not null. Eliminate a record i f data for this query period is missing. Formulas MainOrder IIf(CInt([DifferenceTable3]. [4]+[YVRflightsMeanDifferences] . [AvgOf Diff4_0]-[Forms]![Forml].[Fraction]*[StDevOfDiff4 0])<=[avl st qty],CInt([D ifferenceTable3].[4]+[YVRflightsMeanDifferences].[AvgOfDiff4_0]-[Forms]! [Form 1 ]. [Fraction] * [StDevOfDiff4_0]), [ a v l s t q t y ] ) Meal order = booked load at 3-hour pre-departure + average difference between 3-hour and final - reduction factor * standard deviation from average difference distribution. A n If statement prevents the main order from being higher than the aircraft seat capacity lhrUpload IIf( [ 1 hrUpload_pre]+[MainOrder]<=[avl_st_qty], [ 1 hrUpload_pre], [avl _st_qty] - [MainOrder]) This If statement prevents the sum of the main order and intermediary upload from exceeding the aircraft seat capacity lhrUpload_pr e IIf([DifferenceTable3].[2]+[YVRflightsMeanDifferences].[AvgOfDiff 2_0]-[MainOrder]>=[Forms]! [Forml ]. [Minfor 14], 14, IIf([DifferenceTable3].[2]+[YVRflightsMeanDifferences].[AvgOfDiff 2_0]-[MainOrder]>=[Forms]! [Forml].[Minfor7],7,0)) Let Expected Final Upload be: booked load at 1-hour pre-departure + average difference between 1 -hour and final - MainOrder . If Expected Final Upload >= minimum for 14, then order 14 meals. If Expected Final Upload between minimum for 14 and minimum for 7, then order 7 meals. If Expected Final Upload < minimum for 7, then make no order at this point. FinalUpload [ob_rev_p sg_qty]+[ob_nrv_psg_qty ] - [MainOrder] - [ 1 hrUplo ad] Final upload= final passenger count - meals already boarded. Overcatering Iif(([MainOrder]+[lhrUpload]+IIf([FinalUpload]>0,[FinalUpload],0)-[DifferenceTable3].[0])>0,[MainOrder]+[lhrUpload]+IIf([FinalUpload ]>0,[FinalUpload],0)-[DifferenceTable3].[0],0) Let total catered meals be: main order + intermediary upload + final upload (zero i f negative). Number of meals over catered = total catered meals - final passenger 46 count. If negative, default to zero. Vis i t IIf([FinalUpload]>0,l,0) Boolean value for the necessity of a delivery van visit at the final point (true i f final upload > 0). MBDemand no nrv Query Same as MBDemand except that no meals are uploaded for non-revenue passengers. Formulas FinalUpload [ob_rev_p sg_qty] - [MainOrder] - [ 1 hrUplo ad] Final upload = final revenue passenger count - meals already boarded. AMOS System Cost Cost AMOS Report Presents data from c o s t A M O S a l l query and calculates totals for each column. Cost AMOS all Query Joins records from cost_AMOS_quantities and cost_AMOS_meals query with matching flight number, date, departure station, and arrival station combinations. Filters F L T N R Inside flight number range from M a i n Form. Formulas Underage_rev Sum([cost_AMOS_quantities]. [underage_rev] * [Forms]! [Form 1 ]. [u ndercost]) Mult ip ly the number of under-catered meals for revenue passengers only by the Underage Cost per Passenger in M a i n Form. Underage_all Sum([cost_AMOS_quantities]. [underage_all] * [Forms]! [Form 1 ]. [un dercost]) Mult ip ly the number of under-catered meals for all passengers by the Underage Cost per Passenger in M a i n Form. Cost AMOS quantities Query This query uses the historical data (HST_01_subset) and A M O S forecasts (cost_AMOS_forecast) to calculate the final upload size, the overage quantity, and the underage for revenue passengers only and for all passengers. Formulas Upload_raw [ob_nrv_p sg_qty]+[objre v_p s g q t y ] - [drv_fcs_psg_qty ] 47 Final upload = final passenger count (rev+nrv) - meals already boarded ( A M O S forecast). Upload IIf([Upload_raw] Between 0 A n d [Forms]! [Forml].[maxupload],[Upload_raw],IIf([Upload_raw]<0,0 , [Forms]! [Form 1 ]. [maxupload])) This constrains the final upload quantity from MBDemand query to the maximum upload set in the M a i n Form. If negative, default to zero. total_meals [drv_fcs_psg_qty]+[Upload] The total final number of meals catered. overage IIf(( [totalmeals] - [ob_rev_psg_qty ] -[ob_nrv_psg_qty])>0, [totalmeals] - [ob_rev_psg_qty]-[ob_nrv_psg_qty] ,0) Number of meals over catered = total catered meals - final passenger count. If negative, default to zero. underageall IIf(([ob_rev_psg_qty]+[ob_nrv_psg_qty]-[total_meals])>0,[ob_rev_psg_qty]+[ob_nrv_psg_qty]-[total_meals],0) The number of under-catered meals for all passengers = final passenger count - total catered meals. If negative, default to zero. underage_rev Ilf(([ob_rev_psg_qty]-[total_meals])>0,[ob_rev_psg_qty]-[total_meals],0) -The number of under-catered meals for revenue passengers only. If negative, default to zero. Cost AMOS forecast Query Extracts the 3-hour pre-departure forecasts for flights within the data test range from the pre-departure information table (AMOS_01) . Filters F L T J D T Inside data test set from Main Form dpt_sta_cd Y V R only els qry_pd cd = 4 (the 3-hour query period) C O S _ C D Y class only Cost AMOS meals Query This query multiplies the number of meals catered by the unit price of each item scheduled for any flight. • Meal quantity taken from Cost_AMOS_quantities query. • Scheduled meal service items taken from f light_items_YVR_Y_Jan_Jun99 48 • Meal service item prices taken from item_prices_YVR_updated2 Filters Same as Cost_real_meals Query Formulas MealCost Sum(IIf([PVN RAT CD]="P",[ITM PRC AMT]*CInt([PVN RAT QTY ]/100*[total meals]),[ITM PRC AMT]*CInt([total meals]/[PVN R A T Q TY]))) Calculation of cost based on meal quantity (total_meals) from Cost_AMOS_quantities query. OverCost Sum(IIf([PVN_RAT CD]="P",[ITM PRC AMT]*CInt([PVN RAT QTY ]/100*[overage]),[ITM_PRC_AMT]*CInt([overage]/[PVN_RAT_QTY]))) Calculation of overage cost based on over-catered meal quantity (overage) from Cost_AMOS_quantities query. C. Source Data Tables The following table describes source data tables referenced by the queries detailed above. Database Table Description Station Date Range HST01 Final loads YVR Nov98 - Jun99 PD_1998 Pre-departure data All Feb98 - Jan99 AMOS_01 Pre-departure data (costing tables) YVR Sep98 - Jun99 Flight_items Flight scheduled items YVR Jan99 - Jun99 Item_list Item prices YVR Detailed_bill_yvrJantoAug99 Invoice (detailed to item level) YVR Jan99 - Aug99 Invoice_hist_YVR_JantoJun99 Invoice history YVR Jan99 - Aug99 Table 12: Source data tables 49 Appendix D: Meal Bank Wastage Cost The following is an example of the method used to estimate meal bank wastage for the reduced scenario. Trans Continental Flights - Lunch/Dinner Daily Target Daily Unused Wasted End of Day Date Demand Inventory Production Meals Meals Inventory 2/1/99 42 77 64 54 0 54 2/2/99 52 77 23 25 2 23 2/3/99 46 77 54 31 0 31 2/4/99 19 77 46 58 12 46 2/5/99 22 77 31 55 24 31 2/6/99 27 77 46 50 4 46 2/7/99 49 77 31 28 0 28 2/8/99 17 77 49 60 11 49 2/9/99 48 77 28 29 1 28 2/10/99 29 77 49 48 0 48 2/11/99 29 77 29 48 19 29 2/12/99 39 77 48 38 0 38 2/13/99 12 77 39 65 26 39 2/14/99 35 77 38 42 4 38 2/15/99 13 77 39 64 ' 25 39 (break in table for summarization) 6/19/99 31 77 32 46 14 32 6/20/99 33 77 45 44 0 44 6/21/99 37 77 33 40 7 33 6/22/99 38 77 44 39 0 39 6/23/99 41 77 38 36 0 36 6/24/99 69 77 41 8 0 8 6/25/99 31 77 69 46 0 46 6/26/99 36 77 31 41 10 31 6/27/99 46 77 46 31 0 31 6/28/99 52 77 46 25 0 25 6/30/99 57 77 52 20 0 20 Mean Demand 32.3 Maximum 91 Std Dev 14.9 Inventory Level 77 Mean Wastage Total Wastage Unit Meal Cost Total Wastage Cost 9.2 1358 $ 5.00 $ 6,790.00 Monthly Wastage Cost| $ 1,358.00 Table 13: Meal bank wastage cost estimation 50 Flights in the meal bank program are pooled into three categories based on their meal service: Transcontinental breakfast, Transcontinental lunch/dinner, and International meals (double service). Note that flights CP900 and CP916 are isolated from the Transcontinental breakfast service group since they use different tray sizes. Dai ly demand for both intermediary and final uploads from the model output is summed up across all flights in each pooled category. The daily target inventory level is calculated using the mean plus three standard deviations of the daily demand distribution. Dai ly production is the difference between the target inventory level and the previous day's final inventory. The unused part of the day's inventory is then divided between wasted (unused meals from the previous day's production) and the end of day inventory (unused meals from the current day's production). The wastage figure is calculated from the sum of meals wasted and the unit meal cost. According to the current agreement Canadian Airl ines ' share would be half of the cost. Target Inventory Mean Daily Wastage Monthly Cost TransCon - Breakfast 52 11.5 $ 1,124.90 CP900 (Breakfast) 18 5.3 $ 493.50 CP916 (Breakfast) 16 3.0 $ 120.40 TransCon - Lunch/Dinner 77 9.2 $ 1,358.00 International 46 10.2 $ 3,479.90 Total Monthly Cost $ 6,576.70 CAI Share $ 3,288.35 Table 14: M e a l bank wastage cost summary 51 Appendix E: Delivery Van Driver Schedules Van driver schedules were built for evaluating man-hour requirements for each scenario. The costs are calculated using an estimated hourly wage of $25 for drivers. The added cost shown below is obtained by subtracting the current wages from the scenario total cost. In the final costing results, the current costs were not subtracted since we assumed that existing delivery van service would be kept as is. Current Driver Van Start End Man-Hours Salary 1 1 7:30 19:20 12 2 2 11:45 14:45 3 Total Daily 15 $ 375.00 Weekly 105 $ 2,625.00 Yearly 5460 $136,500.00 Reduced System Implementation Driver Van Start End Man-Hours Salary 1 1 7:05 16:00 9 2 2 7:05 19:20 12.5 3 3 7:00 8:00 1 3 3 11:10 14:55 4 4 4 11:15 12:15 1 Total Daily 27.5 $ 687.50 Weekly 192.5 $ 4,812.50 Yearly 10010 $250,250.00 Added Cost (annual) $ 113,750.00 Full System Implementation Driver Van Start End Man-Hours Salary 1 1 6:20 16:00 10 2 1 17:10 22:45 6 3 2 7:20 18:30 11.5 4 3 7:55 17:55 10.5 5 4 11:45 14:10 3 6 5 11:15 14:05 3.5 1,112.50 7,787.50 34,950.00 Added Cost (annual) $ 268,450.00 Table 15: Van driver schedules Total Daily 44.5 $ Weekly 311.5 $ Yearly 16198 $ 52 A. Scheduling Methodology Above results are based on the summer 1999 schedule for Vancouver International Airport flights. A base van schedule was designed for each scenario using June 2, 1999 scheduled departure times. Full System Implementation • Vis i t all flights with a meal service • A l l o w minimum 5 minutes for transit time between one flight to the next • Intermediary upload visits for wide-body aircraft only • Visi t time: 10 minutes for narrow-body, 15 minutes for wide-body, and 10 minutes for intermediary uploads Reduced System Implementation • Include international (1-99) and transcontinental long-sector (900-999) flights only (excluding late flights) • A l l o w minimum 5 minutes for transit time between one flight to the next • Intermediary upload visits for all included flights • Visi t time: 15 minutes for final uploads, and 10 minutes for intermediary uploads • Other flights are serviced according to current procedure A reduced implementation scenario schedule was designed for each month in the analysis (February to June 1999). The schedules were also tested with actual departure times on three different sample weeks in A p r i l , May, and June. In all cases, the planned schedule could be adjusted within the set fleet and driver shift limitations from the base schedule. The van driver schedule for June 1999 is shown on the next pages. Coloured flights are those included in the meal bank system. Other flights are either visited as part of the current L A P program (two blocks, no colour) or visited on demand for special missing equipment requests (one block, no colour). The vans are assigned to flights that are part of the same meal type pool as best as possible. This way, meals can be loaded on any flight and less replenishment is required. Van Meal Type Flights 1 Breakfast 902, 984 Lunch/dinner 988, 992, 994 International 9, 29, 3 2 Breakfast 916, 986 Lunch/dinner 910, 990, 912, 982 3 Breakfast 900 Lunch/dinner 920 International 29, 17 4 International 1 Table 16: Fl ight assignments 53 CO CD CD a C; CO _, cn CD co ro a ro CO Cn •o CO £ cr c CD CD Cn a Cn ro ro CO CO o Cn CO CD cn CO Cn CD CO ro CD CD O) CD 4-Cn SB CD CO CD ro Cn CO o cn Cn O CD K Flight # CO Ik :•. £ 6 Cn .• ro U cc c ' (.:"• ro ro • - Cn cn 8 CD fo CD o it CO o .': *• a ro -C ro ro ro o O o CD ro o CO co co CD g cn Cn Jk. u •U co cn o cn o IO CO o ro ro ro ro ro Cn O o CD co o s 8 co Co CO Co Co s 2 1^ S Scheduled o cr o cn O cn o o o -.; Cn O c O o O c o cn cn Ui C" O O o (? 5 o cn Cn Cn cn o cn O O cn o o •c. a' o O O o o cn Departure < < N < < o in T o > X < m O O X Z < < a < < O a -< m Q > X < < < O -< m o < •< N Z o i < -< O -C 1 < C < P* > X i a CO Tl O O XJ D rjj O Co •< TJ m < < o 8 1 Z Q O X o —i TJ m -< O < X -< < < n -< O XJ a < m CD < 3 a < a > X •< O s < C 1 < < O o X a < X < < < o -< -< a > X Destination 4:30 4:3b 4:40 4:45 1:50 4:55 5:00 5 0 5 5:10 5:15 5:20 5:25 5:30 5:35 5:40 5:45 5:50 5:55 6:00 6:05 6:10 6:15 6:20 6:25 6:30 6:35 6:40 6 45 6:50 5:55 w 7:00 w 7:05 7:10 7:15 7:20 „ 7:25 7:30 ho 7 35 CJ IO 7:40 to 7:45 u 7:50 7:55 -I 8 00 8:05 3:10 8:15 8:20 B :25 1 8:30 B :35 8:40 8:45 8:50 8:55 9 '00 9:05 M 9:10 H 9:15 U 9:20 9:25 9:30 9:35 3:40 9:45 9:50 3:55 10:00 10:05 10:10 10:15 10:20 10:25 10:30 10:35 10:40 10:45 10:50 54 3 -' .. 2 3 ; t. j> CD O W CO co CT.-X OB N) CD .7: .c 0 u O) •' • D CO • 7, D D ro Ui •J ;• fc.' cn ro no o o ID O •0 c 3 co cn D o cn -J 7 •.; '• 0 no ct t- CM co B cc o> ; t- ID 3 CO -. J> no ro C 8 -t- 5 :light # Scheduled departure 2245 ~ OI 3 • o o •o cn CD O CO C: CO CO Cn 1 -o o .:: O O • O cn .•• fc. J 1 e o -fc; -t-cn •fc=-• | CO Oi •• . S no Co o no no no • •g . cn o no r\ o c ~ :•. _• O cn O o ... (O Co O o co CO W CD CO 3 3 CO :. § -g Cn -J cn cn :•• O •< •< -< o in -n O > X -< m O •< < o X z < < O > X -< -< O < m < i < < o < m O Z 33 -H o 1 -< < o < < c -< •< Kl > x < < Ci CO T| O 0 D D ./• < m < o X z Q O X a rn < < X < -< -o ? o § < m O O i -< O i < 1 < c o < < o O 33 Z) -c X < < < O < < > X Destination - 0: 55 - ISO 1: 30 1: 35 „ 1 1 I 10 11 15 11 20 11 25 1: 30 1 fl 1. 35 CO *> 11 40 w n- 1 - 1 1 45 • J 3 1 1 50 CO 1 1 55 *• w 12 00 to 12 05 12 10 CO 12 15 to M 12 20 1? 25 -* 12 30 12 35 12 40 1 12 45 12 50 12 55 13 00 i 13 05 13 10 13 15 13 20 13 25 M 13 30 co M 13 35 Co 13 40 CO 13 45 CO 13 50 -i 13 65 I 14 00 - 14 05 14 10 14 15 _ l 14 20 14 25 14 30 CO 14 35 1 14 40 14 45 to 14 50 14 55 * 15 00 15 05 15 10 i : 15 Ii 20 15 25 H 30 II 35 i lb 40 15 45 L 15 50 15 55 IE :00 1( :05 11 :10 ' i :15 11 :20 If :25 If :30 11 :35 It 4 0 H :45 II :50 11 :55 1 :00 1 :05 1 :10 i :15 55 CD -si tD • cn Oi o a; CT! E> s .0 cn X C/1 ro on V) ••• cn J-I 3 O 7. Co • 3D t o • .D 8 -J • o '•• -.. fc- . -o co D JD 0 cn o CO : 0 ID ••• •i CT> •o Ifi Cc  0 £ Co Co CO D rc - s r fc. s D fc. o XI VI -n ":; CO 8 fc. i "light # no t. o ro Xi o 5 D Cn -•• D a :: CO :• O CO o • n o D • C J-i O o o cn S -fc. CO Ik cn fe fe *. Ci Oi CO n ; -o O ro o ••• CD ~o C3 o n no no cn ro no cn V o o • O oo o • o CO o 0 Co o 8 CO  co o co o CO o CO 5 -fc- -J O cn Scheduled Departure |YYZ -< -< O m Tt O > X < s -< -< o I Z -< < O > X < < O < • < m > X < •< •< O < < •< NI z TJ -1 a -< < O < < c < S : TI o TJ O UJ O CT) < TJ m < < o Q Z O O X Q -i •g m < i < X < < < O O TJ a -< m O a i -< ni < -< o S < 1 < -< -C •< O o TJ o < X < < < o -< -< o > X Destination 7:20 7:25 7:30 7:35 7:40 7:45 fO 7:50 ro 7:55 ro 8:00 18:05 18:10 18:15 18:20 18:25 ro 18:30 18:35 18:40 18:45 18:50 18:55 19:00 19:05 M 19:10 19:15 19:20 19:25 19:30 19:35 19:40 19:45 19:50 19:55 20:00 20:05 20:10 20:15 20:20 20:25 20:30 20:35 20:40 ?0:45 20:50 20:55 21:00 21:05 21:10 21:15 21:20 21:25 21:30 21:35 21:40 21:45 21:50 21:55 22:00 22:05 22:10 22:15 22:20 22:25 1 1 22:30 56 Appendix F: Costing Analysis Results Detailed results for each test month are presented below. The first table shows total monthly cost savings per flight. The second table contains the shortage totals per flight. Month Flight Savings 2 3 $ 2,650.13 2 7 $ 2,810.58 2 9 $ 6,495.38 2 17 $ 2,656.85 2 29 $ 3,500.87 2 900 $ 412.46 2 902 $ 633.90 2 910 $ 1,050.20 2 912 $ 794.64 2 984 $ 1,010.63 2 986 $ 584.86 2 988 $ 1,906.80 2 990 $ 2,638.72 2 992 $ 1,150.66 2 994 $ 1,393.82 Month Flight Savings 3 3 $ (605.86) 3 7 $ (2,236.37) 3 9 $ 111.53 3 17 $ 423.16 3 29 $ 3,317.37 3 900 $ 634.22 3 902 $ 670.68 3 910 $ 1,307.32 3 912 $ 1,109.30 3 984 $ 676.80 3 986 $ 1,268.84 3 988 $ 1,514.96 3 990 $ 945.72 3 992 $ 1,281.61 3 994 $ 1,521.87 Month Flight Savings 4 3 $ 10,848.38 4 7 $ 17,566.41 4 9 $ 7,961.91 4 17 $ 24,870.20 4 29 $ 8,966.73 4 900 $ 332.59 4 902 $ 468.39 4 910 $ 40.95 4 912 $ 1,207.48 4 984 $ 516.52 57 4 986 $ 622.42 4 988 $ 363.27 4 990 $ 643.39 4 992 $ 1,125.82 4 994 $ 872.38 Month Flight Savings 5 3 $ 2,155.63 5 7 $ 3,513.43 5 9 $ 416.81 5 17 $ 4,366.92 5 29 $ 2,069.22 5 900 $ 619.05 5 902 $ 168.52 5 910 $ 620.42 5 912 $ 1,107.92 5 916 $ 297.17 5 920 $ 1,809.29 5 984 $ 257.84 5 986 $ 430.61 5 988 $ 1,295.97 5 990 $ 297.23 5 992 $ 1,207.86 5 994 $ 1,198.20 Month Flight Savings 6 3 $ 1,622.96 6 7 $ 1,494.04 6 9 $ 1,080.55 6 17 $ 1,567.16 6 29 $ 2,688.41 6 900 $ 222.58 6 902 $ 346.30 6 910 $ 479.86 6 912 $ 1,278.88 6 916 $ 480.52 6 920 $ 683.88 6 982 $ 1,385.87 6 984 $ 588.38 6 986 $ 543.37 6 988 $ 524.90 6 990 $ 368.55 6 992 $ 587.51 6 994 $ 1,106.28 Table 17: Detailed costing results Current Underage Meal Bank Underage Month Flight Revenue Passengers All Passengers Revenue Passengers All Passengers 2 3 0 0 1 4 2 7 0 0 0 0 2 9 0 0 0 0 2 17 0 0 0 0 2 29 0 7 0 0 2 900 0 9 0 0 2 902 0 11 0 0 2 910 0 0 0 0 2 912 0 0 0 0 2 984 0 0 0 0 2 986 0 4 0 2 2 988 0 0 0 0 2 990 0 0 0 0 2 992 0 5 10 29 2 994 0 0 0 0 Current Underage Meal Bank Underage Month Flight Revenue Passengers All Passengers Revenue Passengers All Passengers 3 3 1 6 0 0 3 7 0 20 0 0 3 9 2 4 0 0 3 17 4 18 0 0 3 29 0 7 0 0 3 900 0 4 0 0 3 902 0 2 0 0 3 910 0 0 0 0 3 912 0 1 0 0 3 984 8 24 0 0 3 986 0 12 0 0 3 988 0 6 0 0 3 990 0 23 0 0 3 992 1 8 0 0 3 994 0 5 0 0 Current Underage Meal Bank Underage Month Flight Revenue Passengers All Passengers Revenue Passengers All Passengers 4 3 6 8 9 10 4 7 7 54 0 36 4 9 3 10 0 0 4 17 23 28 0 0 4 29 0 1 0 0 4 900 0 2 6 17 4 902 0 4 0 0 4 910 0 0 0 0 4 912 0 0 0 0 4 984 0 16 0 0 4 986 0 16 0 0 4 988 0 13 0 0 4 990 0 13 0 0 4 992 0 12 0 0 4 994 4 17 18 36 Current Underage Meal Bank Underage Month Flight Revenue All Revenue All Passengers Passengers Passengers Passengers 5 3 3 3 3 5 5 7 0 5 0 14 5 9 8 13 0 0 5 17 13 18 0 0 5 29 11 16 0 5 5 900 5 11 0 0 5 902 6 19 0 0 5 910 0 3 0 0 5 912 0 2 0 0 5 916 0 3 0 0 5 920 5 9 0 0 5 984 0 5 0 1 5 986 0 5 0 0 5 988 0 30 3 28 5 990 0 45 0 . 10 5 992 0 1 0 0 5 994 15 24 0 0 Current Underage Meal Bank Underage Month Flight Revenue All Revenue All Passengers Passengers Passengers Passengers 6 3 1 4 0 0 6 7 0 0 0 0 6 9 15 16 6 6 6 17 0 0 4 4 6 29 7 7 0 0 6 900 1 9 0 0 6 902 0 2 0 0 6 910 3 18 0 0 6 912 0 0 0 0 6 916 0 0 0 0 6 920 0 1 0 0 6 982 2 4 0 2 6 984 0 8 0 38 6 986 0 8 0 24 6 988 0 0 0 0 6 990 0 7 0 0 6 992 0 8 3 16 6 994 4 12 6 12 Table 18: Detailed service level results Outlier events were excluded from the results. These events are shown in Table 19 below. Outliers were extracted from both meal bank and current cost and underage totals. We observe that for most of these flights, no shortage occurred in fact. We can conclude that, although the booked load changed dramatically in the last 3 hours preceeding the flight's departure, the flight controllers were aware of the situation and adjusted the meal order appropriately. The method of identification for these events is explained below. Current Underage Meal Bank Underage Revenue All Revenue All Flight Date Departure Arrival Passengers Passengers Passengers Passengers 988 2/3/99 YVR YYZ 0 0 25 38 992 2/6/99 YVR YYZ 0 0 32 33 984 2/11/99 YVR YYZ 0 0 9 28 984 3/3/99 YVR YYZ 0 0 29 53 984 3/25/99 YVR YYZ 0 9 24 41 984 4/8/99 YVR YYZ 0 26 0 23 986 4/8/99 YVR YYZ 0 0 21 23 992 4/9/99 YVR YYZ 0 0 20 48 992 4/17/99 YVR YYZ 0 0 25 42 990 4/19/99 YVR YYZ 0 0 22 29 990 4/25/99 YVR YYZ 0 55 0 22 994 6/22/99 YVR YYZ 19 25 27 33 994 6/24/99 YVR YYZ 0 0 20 30 984 6/12/99 YVR YYZ 170 170 0 0 Table 19: Outlier events A. Identification of Outliers, an Example To identify outliers, a distribution of underages from the meal bank output was compiled. Events in the top quartile were identified as outliers or irregular events. The table below shows an example of a flight affected by an irregular event. The data is taken from the meal bank model output for flight CP988 in the test period of February 1999. This flight is never under-catered except for one day where a large shortage occurs. 61 Date flt_nr dpt_sta arr sta MealsCatered Final_Pax_Qty Underage_Rev Underage_AII 2/1/99 988 YVR YYZ 145 145 0 0 2/2/99 988 YVR YYZ 165 165 0 0 2/3/99 988 YVR YYZ 83 121 25 38 2/4/99 988 YVR YYZ 118 116 0 0 2/5/99 988 YVR YYZ 108 108 0 0 2/6/99 988 YVR YYZ 0 0 0 0 2/7/99 988 YVR YYZ 170 161 . 0 0 2/8/99 988 YVR YYZ 111 107 0 0 2/9/99 988 YVR YYZ 159 135 0 0 2/10/99 988 YVR YYZ 113 112 0 0 2/11/99 988 YVR YYZ 151 151 0 0 2/12/99 988 YVR YYZ 155 155 0 0 2/13/99 988 YVR YYZ 153 132 0 0 2/14/99 988 YVR YYZ 168 157 0 0 2/15/99 988 YVR YYZ 112 106 0 0 2/16/99 988 YVR YYZ 116 104 0 0 2/17/99 988 YVR YYZ 134 134 0 0 2/18/99 988 YVR YYZ 119 119 0 0 2/19/99 988 YVR YYZ 180 180 0 0 2/20/99 988 YVR YYZ 218 218 0 0 2/21/99 988 YVR YYZ 180 180 0 0 2/22/99 988 YVR YYZ 143 143 0 0 2/23/99 988 YVR YYZ 128 115 0 0 2/24/99 988 YVR YYZ 125 125 0 0 2/25/99 988 YVR YYZ 164 143 0 0 2/26/99 988 YVR YYZ 180 169 0 0 2/28/99 988 YVR YYZ 180 180 0 0 Table 20: Identification of an outlier The explanation for the shortage of flight CP988 on February 3 becomes clear when we look at the change in pre-departure booked loads in the last 6 hours prior to departure. Date flt_nr QueryPer iod Pax_Qty Capacity 2/3/99 988 5 (6-hour) 57 180 2/3/99 988 4 (3-hour) 58 180 2/3/99 988 3 (2-hour) 107 180 2/3/99 988 2 (1-hour) 112 180 2/3/99 988 1 (30-min) 112 180 2/3/99 988 0 (final) 121 180 Table 21: Booked load history for an outlier At the decision point for the main order, the booked load was only 58 passengers. By the 1-hour mark, the load had increased to 112. As the ordering policy model is set-up, intermediary uploads can be 0, 7, or 14 meals. We can safely assume that a particular event has taken place and that the flight controller is aware of it. Human intervention could rectify the situation at that point with a special delivery or a modification to the main order. 62 B. Ordering Policy Parameters The following is the list o f scenario tests that were run to obtain the Mea l Bank cost figures presented in Table 17 and Table 18 above. Each parameter corresponds to one of the text boxes in the M a i n Form of the Costing Analysis Database (see p.37 in Appendix B) . ID M m Reduc. Mnxupload StopFlight starthist stophist startrest 1 18 10 1 10 1 99 1 '15/99 1/31/99 2/1/99 2/28/99 2 18 10 1 10 1 99 1/15/99 2/28/99 3/1/99 3/31/99 3 18 10 1 10 99 1/15/99 3/31/99 4/1/99 4/30/99 4 18 10 1 10 1 99 4/1/99 4/30/99 5/1/99 5/31/99 5 18 10 1 10 1 99 4/1/99 5/31/99 6/1/99 6/30/99 6 18 10 1 21 900 999 1/15/99 1/31/99 2/1/99 2/28/99 7 18 10 1 21 900 999 1/15/99 2/28/99 3/1/99 3/31/99 8 18 10 1 21 900 999 1/15/99 3/31/99 4/1/99 4/30/99 9 18 10 1 21 900 999 4/1/99 4/30/99 5/1/99 5/31/99 10 18 10 1 21 900 999 4/1/99 5/31/99 6/1/99 6/30/99 Table 22: Ordering policy parameters 63 

Cite

Citation Scheme:

        

Citations by CSL (citeproc-js)

Usage Statistics

Share

Embed

Customize your widget with the following options, then copy and paste the code below into the HTML of your page to embed this item in your website.
                        
                            <div id="ubcOpenCollectionsWidgetDisplay">
                            <script id="ubcOpenCollectionsWidget"
                            src="{[{embed.src}]}"
                            data-item="{[{embed.item}]}"
                            data-collection="{[{embed.collection}]}"
                            data-metadata="{[{embed.showMetadata}]}"
                            data-width="{[{embed.width}]}"
                            async >
                            </script>
                            </div>
                        
                    
IIIF logo Our image viewer uses the IIIF 2.0 standard. To load this item in other compatible viewers, use this url:
https://iiif.library.ubc.ca/presentation/dsp.831.1-0099498/manifest

Comment

Related Items