UBC Theses and Dissertations

UBC Theses Logo

UBC Theses and Dissertations

Determining optimal staffing levels at the Whistler Blackcomb Ski and Snowboard School Tse, Stanley 2001

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

Item Metadata

Download

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

Full Text

D E T E R M I N I N G O P T I M A L D A I L Y S T A F F I N G L E V E L S A T T H E W H I S T L E R B L A C K C O M B SKI A N D S N O W B O A R D S C H O O L by S T A N L E Y T S E B.A. (Honors Business Administration), The University of Western Ontario, 1999 A THESIS SUBMITTED IN P A R T I A L F U L F I L M E N T O F T H E R E Q U I R E M E N T S F O R T H E D E G R E E O F M A S T E R O F SCIENCE (BUSINESS ADMINISTRATION) in T H E F A C U L T Y O F G R A D U A T E STUDIES Faculty of Commerce and Business Administration We accept this thesis as conforming to the required standard T H E UNIVERSITY O F BRITISH C O L U M B I A December 2000 ® Stanley Tse, 2000 In presenting this thesis in partial fulf i lment o f the requirements for an advanced degree at the University of Brit ish Columbia, I agree that the Library shall make it freely available for reference and study. I further agree that permission for extensive copying of this thesis for scholarly purposes may be granted by the head o f my department or by his or her representatives. It is understood that copying or publication of this thesis for f inancial gain shall not be al lowed without my written permission. Department o f _ The Universi ty o f Br i t ish Columbia Vancouver, Canada Date A B S T R A C T Whistler Blackcomb Resort experiences the highest skier visits of any resort in North America and consequently demand at the ski school is high. Due to various factors, the daily number of lesson participants is highly variable and the best number of instructors to staff each day is correspondingly difficult to estimate. The consequences of scheduling incorrectly could lead to either overstaffing or understaffing. Overstaffing results in unnecessary costs; understaffing results in lost sales and customer dissatisfaction. A scheduling tool that can assist the Ski School in staffing decisions, therefore, is developed to minimize excess costs. Daily demand predictions are made using a forecasting model and a staffing policy is applied to it to obtain a recommended staffing level. The demand forecasting model is a regression model that takes into account pre-bookings, day of the week, holidays, and yesterday's demand. The staffing rules are determined through a Newsvendor-type model derived from a marginal cost analysis of the trade-off between overstaffing and understaffing applied to the daily demand forecasts. The project is intended to formalize a systematic approach to staffing for certain lesson types (pods) one day in advance. It will assist the Whistler Blackcomb Ski and Snowboard School, as a decision support tool, in the development of daily instructor schedules that rninimize any unnecessary costs. T A B L E O F C O N T E N T S Abstract ii Table of Contents iii List of Tables v List of Figures vi Acknowledgements vii C H A P T E R I Introduction 1 1.1 C O N T E X T 1 1.2 B A C K G R O U N D 1 1.3 SKI A N D SNOWBOARD SCHOOL OPERATIONS 2 1.4 P R O B L E M DEFINITION 3 1.5 BUSINESS BENEFITS 5 1.6 OBJECTIVE 5 1.7 PROJECT SCOPE 6 C H A P T E R II Literature Review 7 2.1 SKI INDUSTRY STUDIES 7 2.2 D E M A N D FORECASTING APPLICATIONS 7 2.3 N E W S V E N D O R INVENTORY M O D E L 8 2.4 H U M A N RESOURCES/PERSONNEL SCHEDULING 9 C H A P T E R III Methodology 10 3.1 M O D E L CONSTRUCTION 10 3.2 D A T A REQUIREMENTS A N D C O L L E C T I O N 14 3.3 FORECASTING M O D E L S 15 3.4 STAFFING POLICIES 15 C H A P T E R IV Results 18 4.1 FORECASTING M O D E L F A C T O R SELECTION 18 4.2 FORECASTING M O D E L SELECTION 19 4.3 FORECAST M O D E L T E S T I N G RESULTS S H E E T A N D CALCULATIONS . . . . 20 4.4 FORECASTING RESULTS 22 4.5 STAFFING L E V E L RESULTS 25 4.6 FINANCIAL RESULTS - COST-SAVINGS 26 4.7 C O N C E P T U A L SCHEDULING T O O L M O D E L FRAMEWORK 28 C H A P T E R V Discussion and Recommendations 30 5.1 C O S T VS. SERVICE L E V E L 30 5.2 IMPLEMENTATION 30 5.3 PROJECT RISKS 30 5.4 AREAS FOR F U R T H E R INVESTIGATION 31 Conclusions 33 References 34 Appendices 35 A . l D A T A IMPUTATION 35 A . 2 FORECASTING M O D E L S 36 A . 3 S C H E D U L I N G T O O L USER'S G U I D E 38 A . 4 M S E X C E L V B A C O D E 49 iv L I S T O F T A B L E S Table 1: Potential factors that affect daily demand 12 Table 2: Costs and prices 13 Table 3: Data requirements, purpose, and source 14 Table 4: Characteristics of the Newsvendor model and similarity to the Ski School 16 Table 5: Factors excluded from models 19 Table 6: Factors included in models due to qualitative reasons instead of statistical significance 19 Table 7: Demand forecasting results (root mean square error) 22 Table 8: Regression coefficients and probability levels obtained from t-student tests 24 Table 9: Comparison of average daily costs for three staffing policies 26 Table 10: Calculation of cost-savings 26 Table 11: Data imputation methods and description 35 L I S T O F F I G U R E S Figure 1: Daily timeline of Ski School operations 2 Figure 2: Time series plot of total demand volumes for 1998-1999 and 1999-2000 3 Figure 3: Autocorrelation plot of total demand in 1999-2000 using a difference of one 3 Figure 4: Boxplot of day of week volumes for 1999-2000 4 Figure 5: Total demand in 1999-2000 grouped by holidays 4 Figure 6: Flowchart of model construction 11 Figure 7: Normal probability plot and histogram of demand forecast residuals 13 Figure 8: Illustration of the Newsvendor model 17 Figure 9: Sample results sheet for the final regression model 21 Figure 10: Bar chart comparison between the forecasting models (RMSE) 23 Figure 11: Plot of residuals showing apparent outliers 24 Figure 12: Bar chart comparison of forecasting models and staffing policies (cost) 27 Figure 13: Conceptual scheduling tool model framework 28 A C K N O W L E D G E M E N T S Before this project began, I was told that many different factors had to come together in order to successfully begin and complete it. The project has now been implemented successfully due to the effort of several people. I would like to thank the Centre for Operations Excellence (COE) for providing me with the opportunity to earn my MSc while gaining experience from working on an applied project. I acknowledge all of the hard work that my colleague Isabelle Smith put into the project throughout it. In my mind, she has spent much more time and energy than her few extra credits indicate. I appreciate the opportunity to work on such an interesting project, and so I thank David Fujimagari from Intrawest Corporation/Whistler Blackcomb Ski and Snowboard School for accepting the project. Throughout the course of the project he has been very helpful and has always taken the time to provide information or help solve any issues that arose. I would like to thank my thesis cornmittee of Martin Puterman and David Glenn for all of their invaluable help. The project might not have been completed successfully had it not been for their expert advice, project management and active involvement. Finally, I appreciate all of the encouragement from my family and friends throughout the completion of my MSc. C H A P T E R I I N T R O D U C T I O N 1.1 Context The project was initiated as part of a UBC class project in the class BABS502: Forecastingfor Management in which time series forecasting models were used to forecast daily skier and snowboarder demand at the Whistler Blackcomb Ski and Snowboard School (Ski School). For the Ski School, the class project was used to determine if demand and pre-registration (pre-booking) data were useful for predicting demand. From the results, we concluded that skier demand could be forecasted a day in advance using the pre-lesson registration data in a time series model but that snowboarder demand had to be determined using a different method and required further investigation. This thesis project, therefore, was proposed with the objective of refining these forecasts to obtain accurate predictions and to create an optimal staffing policy given these forecasts. The demand forecasting could then be applied to aid in tactical decision-making such as daily instructor scheduling decisions. 1.2 Background Whistler Blackcomb Resort (Whistler Blackcomb) is recognized as one of the most famous and popular year-round resorts in the world. In the past thirty years, the mountain has been transformed from its humble beginnings to a world-class operation. Located just north of Vancouver, British Columbia, Whistler is a mere two-hour drive from the city. Many visitors will also drive in from interior British Columbia and from Washington State in the United States. Most noteworthy of all, however, is the resort's reputation as the premier winter resort destination in the world. The clear majority of guest visit dollar volume comes from "destination" guests: visitors who fly in from around the world, stay for several nights at rental units in Whistler Village, and take lessons from the Ski School. Whistler Mountain opened to the skiing public in 1966 while Blackcomb Mountain began operations in 1978. The two mountains experienced tremendous growth and expansion under fierce competition until 1997 when Intrawest Corporation acquired Whistler Mountain and consolidated the operations of the two mountains. The merger solidified the resort's position as the largest ski resort in North America with the most skiable terrain. Consequently, the mountain experiences some of the highest guest visits for a ski resort in the world. It is one of only two ski resorts in the world that can boast of more than two million guest visits in a single season. To accommodate the teaching needs of the high volume of skiers and snowboarders, the Ski School retains over 1,200 instructors (professional, part-time, and casual) within its ranks. In order to organize the massive ski and snowboard school into smaller teams, lesson types are categorized into pods. Instructors are assigned to one of the 36 pods according to their certification level, skills, experience, and preference. Depending on the number of participants who are pre-registered and the specific day, the demand and utilization of each 1 pod varies. Throughout one season the Ski School must accommodate over two hundred thousand participants. The order of priority for meeting demand is 1) Kids, 2) Beginners, and 3) Adults. It is the aim of the Ski School, however, to ensure that demand is fulfilled by offering its instructional services to every customer that requests it. 1.3 Ski and Snowboard School Operations At the beginning of each season, the Ski School estimates the overall demand for the Ski School for the upcoming year. From these general predictions and the number of instructors staffed in the previous year, staffing levels for all of the pods are determined well before the beginning of the ski and snowboard season. A master schedule is then drawn up that dictates in which pod, when, and how often an instructor works. The pod Supervisors, however, must adjust and update the schedule each day and predict how many instructors to make available on a given day either to teach a lesson or to standby in the case of unexpected demand one day ahead. Because the instructors are paid to be available regardless of the demand, overstaffing results in unnecessary costs. More specifically, an instructor is paid for two hours at a base wage rate for each half-day that he or she must standby. Conversely, understaffing results in lost sales and customer dissatisfaction. Because a lesson is not offered while there is still demand for it, a loss of revenue for the price of the lesson along with a loss of goodwill is incurred but the cost of offering the lesson, an instructor's pay, does not have to be paid. It is the responsibility of the pod Supervisor to achieve a high service level (meet demand) as well as to meet certain profit targets. While the Supervisor may staff many more instructors than are necessary to attain a high service level, the aim of the Supervisor for each pod is to accurately predict demand for the following day to staff no more instructors than necessary. The daily timeline for demand in certain pods, as seen in Figure 1, is a combination of pre-booked lessons as well as drop-in customers. Thus, at 5:00pm each day, the exact number of instructors to staff for the following day must be determined given demand estimates from pre-bookings, prior year demand, and various other factors. It is the goal of the project, therefore, to apply a scheduling tool for use at 5:00pm each day to aid in forecasting and applying an optimal staffing policy for the candidate pods at the Ski School. Today 8:00am 12:00pm 3:00pm 5:00pm 9:00pm Tomorrow 8:00am Begin morning and full day lessons (demand realized) Afternoon lessons begin Finish lessons for day Determine forecast demand and instructor requirements for tomorrow ^ v ' Call instructors to fill in or to call off Pre-bookings made for today Figure 1: Daily timeline of Ski School operations 2 1.4 Problem Definition The Ski School experiences high demand volatility or uncertainty from day-to-day in certain pods. This uncertainty in demand makes forecasting difficult. Figure 2 below shows the variability in total demand volume for participants in Ski School programs for the 1998-1999 and 1999-2000 seasons. Ski School Demand 1998-1999 Ski School Demand 1999-2000 3000 11/06/98 12/26/98 02/14/99 04/05/99 05/25/99 Date 11/06/99 12/26/99 02/14/00 04/04/00 05/24/00 Date Figure 2: Time series plot of total demand volumes for 1998-1999 and 1999-2000 The volume of participants is affected by several factors. Some of these may be day-of-season, day-of-week, and external factors like holidays and weather. For example, an autocorrelation plot of the demand, shown in Figure 3 below, indicates that every seventh time unit (day) has a high autocorrelation. This is strongly suggestive of a day-of-the-week pattern within the demand data. This factor was investigated further when formulating potential forecasting models. Autocorrelations of Daily demand 1.0 -, 0.5 -co c o CO cu 0.0 -1— o o o Aul -0.5 --1.0 -Time Figure 3: Autocorrelation plot of total demand in 1999-2000 using a difference of one 3 Along with the autocorrelation plot, boxplots shown in Figure 4 below show the day of week variability in total participant demand volume for the 1998-1999 and 1999-2000 seasons. 3000-1 to -t-« c CO Q_ O t : CD 0_ o r 2000H 1 0 0 0 H lllpl Mon Tue Wed Thur Fri Sat Sun Day of Week Figure 4: Boxplot of day of week volumes for 1999-2000 The effect of holidays is shown below, in Figure 5, in which the total demand or number of participants at the Ski School is plotted for each day of the season. The dark triangular points are days of the season that are holidays or part of a holiday weekend. Note that those points appear, on average, higher than those days immediately preceding and proceeding the holiday time period. 3000 - | 2000 (0 a. o 'tr CD C L ]5 1000 o Holiday o o © © © ©' © o 0.0 66.7 133.3 Day of Season Figure 5: Total demand in 1999-2000 grouped by holidays 200.0 4 With good demand forecasts and the error distributions of the forecasts against the actual demand, staffing and scheduling may be undertaken in an accurate and systematic fashion. In this thesis we consider two scheduling performance criteria: service level and expected cost (See section 3.4 on Staffing Policies). A PC-based Scheduling Tool was developed to capture the results from the demand forecasts and staffing policy. The tool will act as a decision support system to help Ski School adniinistrators determine staffing levels of its ski and snowboard instructors one day in advance. Currently, the Ski School subjectively forecasts demand and manually creates instructor schedules. The scheduling tool will attempt to staff instructors daily while minimizing the costs of overstaffing and of understaffing. The tool also allows the management to adjust forecast recommendations and change the service levels. See appendix A.3 for the User's Guide issued to the Ski School for use with the Scheduling Tool. 1.5 Business Benefits The primary business benefit of this project to the Ski School is to gain a systematic approach to instructor scheduling that minimizes unnecessary costs. The scheduling tool will not necessarily automate the entire staffing process, but will act as an aid for decision-making. Thus, the tool is intended to complement the current instructor staffing process while being financially beneficial. Section 4.6 outlines the implications of the project. A significant side-benefit of the project is the development of an accurate and understandable demand forecasting model that will enable the Ski School to be proactive. Instead of attempting to re-arrange teaching assignments the day of a lesson, Supervisors may make changes the night before. As well, after analyzing the factors that have a real impact on demand, the Ski School may attempt to control some of these factors or to use them for longer-term scheduling. Finally, if the demand forecasts yield poor results, the optimal staffing policy will still be recommended to the Ski School. The optimal staffing policy can help the Ski School understand the cost trade-off between overstaffing and understaffing. 1.6 Objective The objectives of the project are the following: • Develop an accurate forecasting model to predict skier and snowboarder demand for the Whistler Blackcomb Ski and Snowboard School, • Determine a staffing policy that either maximizes profit and/or satisfies a given service level, and • Develop a one-day-in-advance scheduling tool for individual pods. 5 1.7 Project Scope The project only focuses on sixteen lesson types of the Whistler Blackcomb Ski and Snowboard School that experience considerable demand variability. The majority of customers for these pods are known as "destination customers;" they arrive at Whistler Blackcomb as a ski destination (by plane or long distance), stay in hotels or rental properties, and have a higher propensity to taking lessons. The pods are a combination of the following: Ski or Snowboard, Whistler or Blackcomb, and Adult or Kids. The remaining pods have low, infrequent, or steady predictable demand and do not require scheduling help. Demand forecasts and staffing level estimates will be made for one day in advance as an adjustment to the original instructor schedule, a master schedule set at the beginning of the season that is meant to be adjusted for daily changes. In this thesis, however, only the Ski Private Blackcomb pod details will be shown since the remaining pods are dealt with identically with the exception of group lessons. The primary difference with pods that involve groups is that an option for average desired group size is included in the scheduling tool. As well, to get the recommended or required number of instructors, the recommended staffing level for number of participants is divided by this average desired group size. All other calculations remain the same. 6 C H A P T E R II LITERATURE R E V I E W 2.1 Ski Industry Studies Few academic papers have focused on the ski industry or similar seasonal recreation businesses. Moreover, it appears that no studies have been conducted on daily demand forecasting in conjunction with optimally staffing personnel in the tourist and recreation industry. Riddington (1999) explores the topic of demand forecasting for the ski industry. The paper, however, takes a macro view of the ski industry and attempts to make yearly forecasts for the entire United Kingdom destination ski industry. In the study, the author investigates the performance of three models: a learning curve model, a fixed coefficient model, and a varying coefficient model. The learning curve model uses a logistic learning curve that includes such factors as saturation level of the industry, new skiers, and retained skiers to predict the number of skiers. The coefficient models are multiple regression models that only consider the year and the value of the pound. Riddington concludes that the best method for producing reliable forecasts is to employ a varying coefficient econometric model. More specifically, using the value of money and the year as variables, while varying the coefficients associated with these variables, produces a reliable predictive model of skier demand. While the model cited in this paper does not apply directly to forecasting daily demand of certain lesson types at a specific ski resort, it does extend the possible factors to examine for a forecasting model to include econometric or monetary factors. A study by Groebner and Merz (1990) investigates forecasting the demand for retail merchandise within the ski industry and also makes some interesting conclusions about ordering inventory for retail stores. The purpose of the paper was to solve an inventory problem for selling seasonal merchandise. The authors considered various models that included the following: Economic-order-quantity-based models, profit matrix models, single-period models, and time-phased order point systems. The paper concludes by proposing the use of a "seasonal forecast delta" model for a forecasting-inventory control system for retailers in resort areas. The proposed model is simply a combination of single order and time-phased order point models. Astonishingly, Groebner and Merz described the single-period model exactly as the formulation of a newsvendor inventory problem (See Newsvendor Inventory Model). Although the paper describes the sale of a perishable good in the ski industry, selling ski equipment over an entire season is much different than daily staffing of instructors for ski lessons because of the nature that demand is realized and the cost equation. The study, however, is reassuring since it describes a successful application of a single-period newsvendor inventory problem within the context of the retail ski resort industry. 2.2 Demand Forecasting Applications There are many examples of demand forecasting using time series analysis for industry applications. This is relevant to this thesis involving the Ski School since it is selling a product, ski or snowboard lessons, which experience stochastic demand. For example 7 Andrews and Cunningham (1995) described the use of ARTMA time series demand forecasting models for L.L. Bean's call centre in order to schedule its call centre agents. This example is similar to the problem at Whistler Blackcomb in that a time series forecasting model must be developed to predict demand for lessons after which instructors must be scheduled to meet this demand. 2.3 Newsvendor Inventory Model The so-called "Newsboy" or "Newsvendor" model has been studied extensively in management science and operations research. The well-known problem models an inventory system in which demand is stochastic and the selling season is limited (i.e. a perishable good). The decision-maker has only one opportunity to replenish inventory before the beginning of each selling period. Three outcomes may arise as a consequence of the newsvendor's order quantity. First, the order quantity may be greater than realized demand and a loss is incurred for the excess stock purchased due to downgrading or for its disposal. Conversely, the order quantity may be lower than the actual demand and the vendor will incur an opportunity loss or stockout costs. Finally, the most desirable outcome is that the exact order quantity equals demand so no overage or shortage costs are sustained. Knowing the shortage cost and the overage cost, a critical fractile can be calculated. This critical fractile indicates the percentile in the cumulative demand distribution that should be satisfied, or service level, that maximizes profit. Therefore, with a properly characterized demand distribution, the optimum order quantity for that period can be determined. The problem can be applied to various industries in which the selling product is a perishable good. As the problem's namesake implies, a newsstand may apply the problem to determine how many of a certain newspaper it should stock each day. Fashion apparel retailers are required to anticipate demand and produce orders before a selling season begins without another chance to take orders. New product launches with short lives or special promotions also face a similar problem; ordering too few products represents lost sales and ordering too many incurs holding costs as the obsolete products are slowly sold. The Ski School faces a similar problem with staffing and scheduling its instructors. The perishable product is a ski or snowboard lesson, the selling season lasts each day, and the demand for ski and snowboard lessons each day is stochastic. The ski and snowboard instructors, in essence, are the inventory of products since they provide or supply the service necessary to offer the product. They become perishable products since Ski School administrators must call upon a certain number and type of instructors several days prior to the business day but an instructor's revenue-generating capability only lasts for the day he or she is available. Because an instructor is paid for being available, whether or not he or she teaches a lesson, the cost of overstaffing can be determined. Oppositely, opportunity cost or loss of goodwill can be equated to stocking out. Thus, the Ski School may formulate its instructor staffing as a Newsvendor problem with only some minor adaptations. Various adaptations of the newsvendor problem have been developed since the original problem was defined. Lau and Lau (1988) and Li et al. (1991) considered the newsvendor problem as a multi-product problem. They explored a two-product newsboy problem with 8 various demand distributions. This adaptation can be applied to the Ski School's problem since each lesson type is a separate product with independent stochastic demand. Thus, the Ski School can be modeled with its multiple products and multiple independent demand functions. In other extensions of the problem, Gurnani and Tang (1999) investigate demand forecast updating. The authors of the paper attempt to determine the optimal ordering policy for a retailer who now has two chances to order the perishable good before the selling season. To calculate the profit-maximizing order strategy, they concluded that the retailer must balance the trade-off between more demand forecasts obtained between the first and second ordering opportunities and the higher unit costs from ordering last-minute. Again, this adaptation can be used for the Ski School problem since administrators may initially schedule instructors but as demand reveals itself through pre-registered lessons, administrators may adjust the schedule at a small cost. This extension, along with others, may allow the Ski School to develop a more accurate newsvendor-type optimal instructor staffing policy. 2.4 Human Resources/Personnel Scheduling The problem of human resource/personnel scheduling has also been studied extensively. Most literature on this subject focuses on tour scheduling for the airline industry, where the problem is generally much more complex. Brusco and Jacobs (1998) looked at personnel tour scheduling with restricted starting-time. The number of daily time periods in which employees were able to begin their shifts was constrained. A heuristic solution strategy was employed to solve the problem, but since the Ski School problem is much simpler, simple manual manipulation may be suffice to accommodate the various shift scheduling restrictions. Further to this, Brusco and Johns (1998) and Van Mieghem (1998) investigated staffing of a multiskilled workforce that focuses on cross-training policies and flexible resources. They concluded that it is advantageous to invest in flexible resources and cross-training of employees so that work activities may be applied across a number of products. From these conclusions, it may be desirable for the Ski School to cross-train or encourage transfer of instructors across pods to realize the claimed advantages. 9 CHAPTER III M E T H O D O L O G Y 3.1 Model Construction In constructing the forecasting and staffing model for this project, many different components were required to interact. Figure 6 on the following page is a detailed flowchart of the model construction. Short descriptions for each component in the illustration are given below. 1999-2000 Demand Data The historical demand data for the target pods were obtained for analysis and to build the forecasting model. The demand data is simply the daily number of participants for morning lessons in a Ski School program broken down into specific pods. The months up to and including February were used to fit models, but the month of March is used to measure a specific model's forecasting performance. Factors From an analysis of the demand various factors are believed to influence future demand. These factors are listed in Table 1 along with a short description. The factors were tested in various combinations in order to obtain several proposed forecasting models (see section 4 .4 for Forecasting Results). Other factors were eliminated based on a qualitative analysis (see section 4.2Results Forecasting Model Factor Selection). Forecasting Models Three types of forecasting models were compared. See section 3.3 for more detail. Performance Measurement Making use of March 2000 data, each of the models were compared using three measurements: Error (forecast - actual), Absolute error (absolute value of Error), and Squared error. Ultimately, it is the sensitivity of the expected cost to the prediction that was considered for model selection. 10 XJ CD — -O CD C > c it CD 01 CO O w i- o CD CD <U t J 3 CT Q. UJ < CO 4 2000 id Data CD e E CO CD 2 Q , CO 3 4 --*—> • — . o co jo o g £ -32 ^ CO O) .Q a c ro E it ^ O CO CD iii > O to co -o a) "co E ^—' CO LU CO c 2 CO CD "co CL O o o CD T3 c CD CD « CD ro. 55 CD o co oi > r o O CO O X5 rz CD E CD TO 2 8 O CO co co CD CD £- >> 2 O ^ £ CO a x 5 TJ C o co CD TO >> CD TJ i_ CD CO > OJ CD T3 c co E CD a OJ c co -<= CD X O LU X CO CD >i H— o > s CO Q o S3 o cj o s J3 O PH u PH 11 Factor Description Day of year/season Day of season since hill opening • Actual dates may not coincide with previous year, matched for day of week Day of week Monday, Tuesday, Wednesday, etc. Hotel occupancy Percent occupancy of largest four hotels Pre-booking Number of pre-registered participants the day before lesson • Full-day, AM, or PM if available Holidays Canadian, United States, and United Kingdom holidays • Each holiday may have different effect Weather Weather forecast for day of lesson from previous day • Each type of weather may have different effect Last year's demand Prior year demand for pod in A M matched by date, but offset to fit appropriate day of week Exchange rate C $/US$andC$/UK£ • Buying power of US and U K visitors Yesterday demand Morning demand in the pod from previous day Table 1: Potential factors that affect daily demand Demand Distributions The daily point prediction along with the error distribution obtained from fitting the forecasting model yield a daily demand distribution for each specific pod. The cumulative demand distributions are formulated as follows: The actual daily demand is equal to the daily demand forecast plus an error value. D, =d,+ s, where Dt = actual demand dt = demand forecast st = error term After fitting the forecasting models, it was determined that the error value was normally distributed with mean zero and a standard deviation estimated by the root mean square error of the forecasting model. s ~ N (0 , cf 2 ) where a2 = root mean square error of forecasting model See Figure 7 for the normal probability plot and histogram of demand forecast residual Note that all but three of the residuals fall within the boundaries of expected probability for a normal distribution. The histogram of the residuals also shows the somewhat characteristic bell shape. 12 Normal Probability Plot of Residuals of Demand For Histogram of Residuals of Demand Forecast 15.0n 14.0-, to Expected Normals Residuals of Demand Forecast Figure 7: Normal probability plot and histogram of demand forecast residuals The actual daily demand, therefore, is normally distributed with the demand forecast as the mean and the root mean squared error from fitting the forecasting model as the standard deviation. Dt~N(d„&2) assume D is continuous Staffing Policies and Recommended Staffing Levels Staffing policies were applied to the demand distributions to obtain a recommended staffing level. The policies are compared in detail in section 3.4. Overage and Shortage costs The data consists of the cost of having excess instructors (overage) and having too few instructors (shortage). More specifically, information on lesson prices, instructor pay rates, and the value placed on loss of goodwill are used to determine staffing levels. The cost data are specific to each pod and may change from year to year. For this project, the prices and costs are weighted averages over the entire 1999-2000 season. The Ski School currently estimates the value of loss of goodwill to be zero; there is no intangible loss of customer satisfaction when demand is not fulfilled. In the Ski School's experience, customers who request a lesson but do not receive one simply pre-book a lesson for another time without feeling any loss of goodwill towards Whistler Blackcomb. Table 2 below contains the costs used in calculations for private ski lessons at Blackcomb. Item Cost or Price Average private lesson price $360 per lesson Average instructor hourly wage $47 per hour Average cost to staff a lesson $140 per lesson Table 2: Costs and prices Cost of Meeting Service Goals The staffing level recommendations may be compared against actual demand for a specific day in order to determine the cost of meeting the specified service goal. Since the model 13 forecasts are compared against March 2000 data (out-of-sample), the results are representative of using the forecasting and staffing model in actuality. Thus, one final result, average daily cost, can be used as the single performance indicator for that model. Because the Ski School will use the model for actual staffing level recommendations, this measurement of cost is ultimately used to compare the various forecasting models instead of commonly used statistical measurements like mean absolute percent error. 3.2 Data Requirements and Collection Various pieces of data were required throughout the project to characterize Ski School operations and to develop the forecasting and scheduling models. The key pieces of data required are listed in Table 3 below along with the purpose and possible source. Note that much of the required data are identified as potential factors that affect demand. The table, however, describes the purpose and possible source for the information. Data Purpose Possible Source Participant demand data • For time series demand forecasting and model testing Revenue Reports for 1997-2000 Instructor utilization data and past forecasts • For comparing past forecasts and accuracy Ski School Forecasts and/or Schedules for 1997-2000 Pre-bookings • • For forecasting models One-day ahead reservations for lessons Ski School Forecasts/Tracking 1999-2000 Cost data • For newsvendor staffing policy and financial results Price Lists and Revenue Reports (payroll data) Weather data • For forecasting models Ski Patrol Daily Weather Reports Instructor/Pod assignment rules (operations) • For forecasting models, cost analysis, and scheduling tool Interviews and Class Size Averages Hotel occupancy • For forecasting models Tourism Whistler Hotel Occupancy Reports Exchange rate • For forecasting models Exchange rate tables Table 3: Data requirements, purpose, and source Almost all of the data was collected from the Ski School and/or the Whistler Blackcomb Resort's information systems. While not all of the data requirements could be satisfied, the data that was obtained from the Ski School appeared to be accurate and complete. Please see section 4.1 for some of the reasons for not obtaining some of the data. Most of the quantitatively oriented reports were sent by email from Whistler Blackcomb as MS Excel spreadsheet files. 14 Some important data for model formulation are not available. Pre-booking information is collected for the 1999-2000, but is not recorded for the 1998-1999 seasons. Because of this, the forecasting models are fitted using only 1999-2000 data. Several data imputation methods were considered, but those approaches are intended to fill in missing values for less than 5% of the total data points, while the 180 days in the 1998-1999 season represent about 50% of the total data points. The holidays for all seasons are not recorded in data files, but are entered manually given a list of holidays. (See appendix A.1 on Data Imputation for further details) 3.3 Forecasting Models Three basic types of forecasting models were tested (several others were considered, but were not appropriate for Ski School application): 1) Last year + x%, 2) Multiple regression, and 3) Exponentialsmoothing. Last year + x% simply takes last year's daily demand (matched according to day of year and day of week) and adds a certain percentage growth for a rough sensitivity analysis (i.e. 0%,10%,-10%). A Multiple regression that incorporated pre-bookings, day of week, yesterday's demand, and holidays as factors that could affect demand proved to be the best model. Other factors were tested, but the aforementioned model is chosen as the final forecasting model (See section 4.5 for Staffing Level Results). Exponential smoothing is a time series technique that may be used to model seasonal patterns for example, 7 days of the week. Note that for the exponential smoothing time series forecasting techniques, the objective functions are set to search for the lowest mean squared error and treat seasonality additively. This is done because some of the demand volumes can be quite small and errors would be unnecessarily magnified if the objective functions were set to search based on the lowest mean absolute percent error. See section 4.4 for the results of these various forecasting models. See appendix A.2 on Forecasting Models for each model's details. 3.4 Staffing Policies The Ski School does not currendy have an official staffing policy; each pod Supervisor has his or her own personal policy. To avoid stocking out, the majority of Supervisors take last year's demand, matched for day of week, as the predicted number of participants but add a buffer amount and staff to that number. The buffer value, however, is subjectively determined and does not necessarily consider service level and expected cost. Focusing on these two criteria, the staffing tool allows staffing policies to be based on either attaining a desired service level or using a Newsvendor model. Service Levels A policy of staffing to attain a desired service level may be used in conjunction with demand forecasts by characterizing a demand distribution through the forecasting model. Given a desired service level, the corresponding quantile of the estimated cumulative demand distribution is chosen for the staffing level. For example, given a 95% service level the staffing policy aims to staff enough instructors to meet 95% of all possible demand occurrences. A 95% service level is considered very conservative but achieves a high level of service if that is the single most important performance criterion. A 95% service level policy 15 and a 50% service level (i.e. staffing to demand forecast since a normal distribution is symmetric) are evaluated in section 4.5 on Staffing Level Results. Newsvendor Inventory Decision Model Another possible staffing policy is to use a Newsvendor Inventory Model. The objective of the model is to find the staffing level that maximizes profit and/or rninimizes cost. This type of formulation is based on a marginal cost analysis in which the overage cost and shortage cost are taken into consideration. In the Ski School context, the overage cost represents the cost of having too many instructors and the shortage cost represents the cost of not having scheduled enough instructors or stocking out. The Ski School's staffing problem appears to fit the characteristics of the Newsvendor inventory decision model. The characteristics of the Newsvendor model and its similarities with the Ski School operations are shown in Table 4 below. Newsvendor characteristic Similarity to Ski School Single period decision Must make staffing decision once the day before lessons take place Perishable good Instructor's time is perishable (once day is over, time is gone forever) Cost of overage and shortage Cost of overstaffing and understaffing Uncertain demand Daily participant demand varies unpredictably No backorders Cannot backorder an instructor's time that has passed (otherwise, considered an entirely new lesson request) Table 4: Characteristics of the Newsvendor model and similarity to the Ski School Note that historically there has always been a higher demand for lessons in the morning than in the afternoon. Assuming that this always holds true, there will always be a sufficient number of instructors for the afternoon if the morning demand is satisfied. Thus, the staffing level policy is only applied to lessons that begin in the morning (i.e. half-day A M and full-day). The goal of the model is to find the optimal staffing level for the morning of a specific day that minimizes cost. The newsvendor problem can be formulated as follows: Let Q represent number of instructors F(D) represent the cumulative distribution function for random demand D f (D) represent the probability density function for random demand D C = cost of "average lesson" (from Ski School) = $140 MP = marginal profit of a lesson = average revenue per class - average payroll cost of class = $360 - $140 = $220 per lesson 16 ML = marginal loss of having an extra instructor (cost of having an instructor on standby) = $47/hour x 2 hours = $94 The total staffing cost for a pod given demand is D and Q instructors are available is Total staffingcost = (D - Q)+ x MP + (Q-D)+ x ML + C min(<2, D) The total expected staffing cost (assuming that demand is continuous) is Total expected staffing cost = MP - Q)f(D)dD + ML^(Q- D)f(D)dD + C ^  Df (D)dD + CQ ^f(D)dD The objective is to find a staffing level that minimizes total expected cost. It is a well-known result that the optimal staffing level Q* is given by F(g-)= M P (MP + ML) where MfL is the "critical fractile." Using the given cost parameters the critical fractile (MP + ML) ' S M P - $ 2 2 ° =0.7006 (MP + ML) ($220+ $94) Thus, the optimal staffing policy for this pod is to staff at the 70th quantile of cumulative demand distribution function. Figure 8 illustrates the Newsvendor model's critical fractile and optimal staffing level. The diagram also shows the result of staffing at a 50% service level. Staffing the exact number as the point forecast is a result of staffing at the 50th quantile (assuming a symmetric demand distribution). • • * • Point forecast Q 95% Service level Staffing Level Figure 8: Illustration of the Newsvendor model 17 C H A P T E R I V RESULTS 4.1 Forecasting Model Factor Selection Of the possible factors that could affect demand, several are eliminated from testing through a qualitative analysis. Other factors are included in some of the forecasting models even though they do not achieve statistical significance when included in those models (i.e. yesterday's demand incorporated but yields a p-value of 0.93). These factors are incorporated due to qualitative reasons. First, some factors are not considered since no data was available to investigate them. For example weather data appears to be a relevant factor that affects the demand at the Ski School. Potential participants may choose not to ski or snowboard if there is extreme weather (i.e. freezing rain, heavy snow, etc.) or, oppositely, more walk-in demand may arise if the weather is nice. Weather may be incorporated in the future as part of the forecasting models if the daily weather data becomes available. Similarly, last year's demand may not be properly used as a factor in regression models since the demand data was not broken down between morning and afternoon lessons. Note that the forecasting models are used to predict morning participants only since instructors who teach half-day morning lessons are available to teach half-day afternoon lessons. And, there are always many more morning lessons than afternoon lessons. Last year's demand is approximated for model comparisons by taking the total number of participants for the day and multiplying it by 80% (The Ski School estimates that the proportion of lessons may be broken down in the following manner: 50% half-day A M , 30% full-day, and 20% half-day PM). Second, other factors are not included in forecasting models because of the data are not available daily. Daily hotel occupancy data for the four primary hotels in Whistler Village are available as reports at the end of each week. Because the forecasting models are used to predict demand volumes each day, there is not much use for a report that can only be incorporated in forecasts after the week has already ended. Third, certain factors are excluded from forecasting model testing because of the appropriateness of the data. Daily exchange rates between the United States and the United Kingdom and Canada are easily determined, but do not provide much information since only small changes (~C$0.01) occur each day. Such small daily changes are probably not able to explain the large variation in daily participant volumes. Instead, exchange rates are probably more useful for long-term forecasting of demand but not for daily forecasting purposes. Table 5 summarizes the factors excluded from the model based on the qualitative analysis performed on the potential factors that affect daily demand of participants at the Ski School. 18 Factor Reasons for removal Weather Data not available Last year's demand Data not available Hotel occupancy Not timely Exchange rate Not appropriate Table 5 : Factors excluded from models On the other hand, some potential factors that could affect demand were included in the final forecasting model even though they do not have high statistical significance using conventional measurements (p-values). Yesterday's demand is included as a factor in the final regression model since it adds an element of time series forecasting to an otherwise standard regression model, mcluding this factor indirectly incorporates the general demand level for very recent demand (i.e. yesterday). Several statistically insignificant holidays are included as indicator variables because other similar holidays are incorporated in the final forecasting model and because the RMSE is lower when all holidays are included as individual indicator variables instead of as one indicator. Table 6 summarizes the statistically insignificant factors included for qualitative reasons. Factor Reasons for inclusion Yesterday's demand Brings time series element to models and recent demand level Holidays (some) Similar holidays already in model Table 6 : Factors included in models due to qualitative reasons instead of statistical significance 4.2 Forecasting Model Selection Several forecasting models were considered initially, but discarded without formal testing for several reasons. First, regression models with day of season provide no information since only one season's daily demand data is available. Thus, only one data point would exist for each day of the season and the parameter for each day of the season would simply consist of the day's demand plus the difference from the season's demand average. Second, ARIMA (Box-Jenkins) models are considered but are not used since the forecasting model would be difficult to implement as part of a scheduling tool, and it would be difficult for the Ski School to interpret the model parameters should the Supervisors seek to understand demand behaviour better. Also, ARIMA models do not allow the incorporation of the various factors that significantly affect demand. 19 4.3 Forecast Model Testing Results Sheet and Calculations In this section, the forecast models are compared to each other using statistical and economic performance measures. First, the entire data set is split into a model fitting portion and an out-of-sample test portion. The model is fitted using daily demand data from November 1999 to the end of February 2000. The out-of-sample testing set is comprised of the daily demand data for March 2000. Out-of-sample testing provides a test set of data that enables the testing of forecasting models' predictive powers and of not over-fitting the available data. The various models are compared using the root mean square error (RMSE) for both the model fitting and the out-of-sample testing. The mean error and mean absolute error were also computed, but only the RMSE measures both positive and negative error as well as magnifies the effect of large errors. Because it is the single best measure of the three, only it is shown in the forecasting results. The models are compared economically compared by calculating the cost of using the forecasting model with applying two different service levels: a 95% service level and the 70% Newsvendor critical fractile. To calculate the number of instructors to staff for a given service level, a point prediction and standard deviation are used in an MS Excel function as outlined below: Number to staff = NOPJ^INVfser^leid, point prediction, standard deviation) The standard aeviation is estimated by the RMSE of the forecasting model while fitting it. For example, ii point prediction is 36, the 95% service level is used, and standard aeviation is 17.41, the recommended staffing level is 68.23 instructors (all forecasts and staffing recommendations are rounded up when applied in the Scheduling Tool). The equation to calculate the cost of using the forecasting model and policy is shown below: Total Error Cost = shortage and overage cost = (D-Q)+ xMP + (Q-D)+ xML Where D = the actual demand Q — the number of instructors staffed MP = the shortage cost = $220 ML = the overage cost = $94 If actual demand is only 26, there are 42.23 too many instructors staffed. Multiplying 42.23 by the overage cost of $94 per instructor, the total cost of error for the pod $3,952.89 that day. The costs for each day in the test sample (March) are averaged to obtain an average cost per day for using the particular demand forecasting model and staffing policy. The results for each model and the two policies are shown in section 4.5. Please see Figure 9 for a sample results sheet for a regression model. There are 15 result sheets for performance measurement, but only this one sample has been included in this thesis. The root mean square errors and average daily cost of error for all models are shown in sections 4.4 and 4.5. 20 - £ fi T - CO CO o m ID T - m CN T - CO It) CO i -m r- CN f O f f l S T - c o f o c o o c f l c o c o u j T - o c o o ) i n o ) S ' t t o s ( N C N r o noiT - t N i N T ^ r d - c n i - t o i n n n s o i - i O T - c j f f i f i - t -fflniDlDOtNOOCOinnaCNr-KCNNCD^nnono^ a) CM i - n to m v 1 - T - C N roi- C O T - T - O O C N - ^ - C N C O O C N T - ^ - C Q I D T - O T - T - C N O O C O C O C N C N O c o ^ ^ t ( D S C N « > c o o f N C i ( D s s m i n i n c N ^ i n o ( o c o ^ o T - t T I C N r - C N C O C O C O C O C N C O C N C O ' ^ V O ^ l - ^ r ^ ' C O C N C O C O C N C O C O p TJ CO . Q CO 73 CD c w o X C N CO CD Q . Q . 2 o O c 5 ° + ™ 5 ¥ t» £ rr 1 0 1 -S co co ™ - » a S CO O l o w O CO ° " c ™ — O fi-ll CD CO 2 _ i I W CD CD .E II S £ °^ I J O IL f t to ! o CD , 3 J ! a : t ! O I T — CO CO k O as o to f j j CO t - ^ ' f N C N CO CO if) CD T - h-^- • S CO ON <D t O O CN O CO O h - O O) O C N O C O C n n ( N C ^ T - C O C O T - W O ^ i D T - r o O ) r - S C O C N O ) ^ N C 5 ) f O f N i o c N O ^ T - i n r - n a ) c r i o c o i n i i D O ( N T - T - o c D C D O ) ^ i r ) ( D s c o ( D C D c n r N Q o s ( D c r i ^ © o ) N n s ( o i o r o c N T - n o f l CO ^ c ^ t o ^ r ^ c n c D ^ C N ^ ^ i j o c ^ c o r ^ ^ C N r ^ ^ ^ o i r ) r ^ i r j c ^ r ^ ^ i n c D L j o r ^ i n ^ r ^ c o c o c o ^ C D ^ C N C O C ^ q o o c n ^ f f l ( o n o ) o ) i D ^ T - i - ( N ( D C 5 ) T - c o ( o ^ ^ c o ' t ( o ( N t b o d m o i x J m t b c n i n t o C O T - i n S ^ f N f N W f > | T r N . r o C D W n K L D C N i j D 0 1 l f ) T - O ^ T - ( f l ^ S T - O i m 0 3 f O f N r - 0 ) K C N D N C O O i n i n O S C O ( D m D r - K C O I O N . ^ ( N O O t O S C O r ) C O ( f l l O O S O n s r - r - ^ c o i D m c n T - f N L O L O i n n i r t O r - CN s co cn I D CO CO T— C N ^ - n CO COCOCN "fr CN r - CN CN r N C N i i ) o c o n ( O k n ( N s n r - ^ o ) c o s c o n m n N K f N M t O T - C O ^ C O T - T - t D U J i n ^ T - C O n a T - f O C D r - f f l O O CN kO CO O O O O T— T— CN O C N O O I D O C O O T -O h ^ t O ' q r O n O O O C N C N t O O C O m O O C O O C D O ' O CN IX) CO O C N O T - C N O O " D O C 0 O f N ^ i i i N O K m r o ( a o ( a n ( D i n c o s s s i f ) T - ( O i C O ^ i n C O I ^ T - C N C O ^ i n c O ^ T - C N C ^ ^ l f O l j D r ^ C D C O C D t T 3 t T J C O r O ( C C O C C f O f O C O n J 0 5 C O n j < T J C T J C O n J f O T O ft® O O <D 5 B s ™ 2 co i CQ E E in u -a o C/3 1*5 Wl CD .o u u cn 3 c/> u CO u PH 21 Note that in the sample result sheet, the error calculations are not completed for March 5th and 6th. From interviews with staff at the Ski School, it is apparent that no demand data was recorded for those two days and that the demand was not, in fact, zero. Because of this, the error calculations and performance measurements are not included in the comparisons. 4.4 Forecasting Results The forecasting results are summarized in Table 7 below and show the root mean square error for model fitting and out-of-sample testing against actual morning demand. Forecasting Method Root Mean Model Fitting Square Error Out-of-sample Testing Last year demand + 0% 17.41 13.15 Last year demand + 10% 19.72 14.38 Last year demand - 10% 15.74 12.72 Exponential smoothing - Horizontal (search MSE) 9.45 9.60 Holt's Linear Trend (search MSE) 9.52 9.67 Holt Winters (7 day seasonal, additive, search MSE) 9.66 11.03 Regression: D O W 17.03 8.40 Regression: Pre-book 5.32 4.22 Regression: Holiday (one indicator only) 14.93 9.37 Regression: Yesterday 10.37 8.49 Regression: Pre-book, D O W 5.44 4.29 Regression: Pre-book, DOW, Holiday (one indicator) 5.45 4.28 Regression: Pre-book, DOW, Holiday (all indicators) 5.27 4.25 Regression: Pre-book, DOW, Holiday (all indicators), Yesterday 5.34 4.28 Regression: Pre-book, DOW, Holiday (all indicators), Yesterday (outliers removed) 2.91 4.25 Table 7: Demand forecasting results (root mean square error) Please see Figure 10 for a bar chart comparison between the forecasting models by root mean square error. 22 LU or c UJ CO 01 Ul c • 03 0) H •2- «> ra co >, o "S ^ <5 ra S 5= 0 -o cu . 1 ~-S. «T ra >, o, "2 -5 co <B 1 .9 w O TJ 0) X .5 > XJ o D) c </> ra o a> o u. 8 5? >. o o CN C D ^ - C M O O O C O ^ C N O JOJ jg a j e n b s uea|/ \ | J O O J J cu o C/5 u u 5-1 u •s c u u « o c/> •c a o u J3 Note that the final model takes out certain data points that appear to be outliers. See Figure 11 for the residual plot with the apparent outliers. The explanation from the Ski School is that the data may not be entirely correct since demand was not collected for every day of the year and some mistakes were made in recording the data. These abnormal data points were removed and checked with the Ski School to ensure that this was, in fact, the case. CO CD a: 30.0 10.0--10.0-o .Ox 0 0 C O & o o o o C C Q O o G -30.0-I , r-20.0 —\ 1 r~ 53.3 86.7 D a y Outl ier O N o A Y e s 120.0 Figure 11: Plot of residuals showing apparent outliers The final forecasting model used in the scheduling tool is a regression model that includes pre-bookings, day of week indicator variables, holiday indicator variables (for all six types of holidays), and yesterday's demand with all apparent outliers removed. The coefficients for the regression coefficient and the probability levels of each factor are shown in Table 8. Factor Coefficient P-Values Intercept 4.591724 0.000073 Pre-book 1.0424 0.00E+00 Yesterday 3.09E-03 0.930084 Christmas/New Year's -1.145356 0.387052 President's Week 1.093602 0.391414 Chinese New Year -1.231016 0.577106 University Break -1.370188 0.229957 Tuesday -1.274139 0.25291 Wednesday -1.568013 0.162551 Thursday -2.280354 0.039155 Friday -1.97113 0.068061 Saturday -0.8666236 0.44849 Sunday 0.4120814 0.720677 Table 8 : Regression coefficients and probability levels obtained from t-student tests 24 These results indicate that pre-bookings have the single most explanatory power and predict approximately one participant for every pre-booking made. Yesterday's demand has little effect with a small coefficient and high probability level. Of the holidays, President's Week is the only coefficient that is positive, suggesting that the most walk-in customers appear during that week. The day-of-week factors show that Sunday is the busiest day of the week while Thursday is the least busy. All of these results agree with the qualitative opinions and estimations of the Ski School. The R-squared coefficient for this model is, by far, the highest at 0.9634 along with the lowest root mean squared error of 2.91 for model fitting but only 4.25 for out-of-sample testing. There does not appear to be any serial correlation (the correlation at each lag value did not exceed the absolute value given for statistical significance) and the residuals appear uncorrelated and normally distributed. Comparing the models by the R-squared coefficients and the RMSEs, the regression model predicts morning demand the most accurately of the possible forecasting models. 4.5 Staffing Level Results The average daily cost of error of using the different staffing policies (50% service level, 95% service level, and 70% Newsvendor fractile) along with the various forecasting methods are compared in Table 9 below. (The calculation of the average daily error cost is shown in section 4.3.) Note that the average daily cost of error is only calculated for the out-of-sample test set of March 2000 actual morning demand and not for model-fitting data set. The last regression model in the table yields the least average daily cost for all three staffing policies. And, as expected, the Newsvendor fractile staffing policy yields the lowest cost solution of all the three staffing policies. See Figure 12, on the following page, for a bar chart comparison between the forecasting models and the staffing policies by average daily cost. Forecasting Method Average Daily Cost 50% Service 95% Service 70% Level (staff Level Newsvendor to forecast) Fractile Last year demand + 0% $ 1,686.66 $ 2,757.40 $ 1,659.61 Last year demand + 10% $ 1,684.74 $ 3,417.04 $ 1,867.85 Last year demand - 10% $ 1,757.71 $ 2,257.12 $ 1,517.28 Exponential smoothing - Horizontal (objective to minimize MSE) $ 1,187.71 $ 1,556.84 $ 968.64 Holt's Linear Trend (objective to minimize MSE, possible linear trend) $ 1,181.30 $ 1,584.18 $ 981.58 Holt Winters (objective to minimize MSE, 7 day seasonal with additive effect, see appendix A.2 for formulation) $ 1,007.01 $ 2,213.64 $ 1,297.43 25 Regression: D O W $ 1,137.74 $ 2,403.90 $ 988.45 Regression: Pre-book $ 437.65 $ 843.31 $ 419.56 Regression: Holiday (one indicator only) $ 1,213.94 $ 2,368.31 $ 984.60 Regression: Yesterday $ 1,045.68 $ 1,639.82 $ 903.63 Regression: Pre-book, D O W $ 440.62 $ 850.58 $ 388.56 Regression: Pre-book, DOW, Holiday (one) $ 469.53 $ 828.67 $ 364.05 Regression: Pre-book, DOW, Holiday (all indicators) $ 417.22 $ 851.34 $ 398.30 Regression: Pre-book, DOW, Holiday (all indicators), Yesterday $ 417.22 $ 865.95 $ 403.46 Regression: Pre-book, DOW, Holiday (all indicators), Yesterday (outliers removed) $ 399.55 $ 551.91 $ 358.70 Table 9: Comparison of average daily costs for three staffing policies 4.6 Financial Results - Cost-savings A comparison, shown in Table 10, of the final regression model using with the Newsvendor fractile staffing policy with the Ski School's current scheduling efforts enables the calculation of a rough estimate of the cost-savings of implementing the proposed final forecasting model and optimal staffing policy. The closest approximation of the Ski School's current scheduling efforts is using the model of Last year's demand + 10% and staffing to that number (i.e. using a percentile of 50%). Last year's demand +10% Regression (pre-book, D O W , holidays, yesterday) and Newsvendor fractile Cost savings Average daily cost of errors $ 1,684.74 $ 358.70 $ 1,326.04 Average cost of season (180 days) $ 303,253 $ 64,566 $ 238,687 Average cost of season (16 pods) $ 4.85M $ 1.03M $ 3.82M Table 10: Calculation of cost-savings Although these calculations are rough estimates, the savings within the Ski School of implementing the recommendations and the Scheduling Tool could potentially reach several million dollars each year. Only a thorough evaluation of the model after it has been implemented will determine if these financial savings are realizable. 26 o o o o o o o o o o o o o o o o q in o in o_ in o m oo" co CN csT &<» «o cy> </» j s o o A | | B Q a B e J S A V CU I os ra X to co" to o "S cu ro S = .9 w g X J cu o c >-•o cu > o E cu CO <" ro o "S 0 c g en c o § l i ! v ' ro o " r> ?^  ro o 8 T3 C o _ ^ 5 3 o 2 > ro D) to X J c .2 >> cu 02 c o CO ra cu l i s t X c cu I Od 5 O i o 0) TJ O O) ca u a> a: o X £ ra — ?> cu ra CO cu co ^ ra cu c > o X J X J ra ra cu <o o LU ro co "2 o o g S X Ll cu a. 2 x E LL) co ra 3 sz ? C O m Q l _ HI N ra co x CO cu >> o ra cu t/> O CJ c/) CJ o bfi I T 3 a CO "« -d o a .3 • M co rt CJ u O C O cn •a a o (J u CN SH 4.7 Conceptual Scheduling Tool Model Framework The purpose of this project was to create a model that makes daily recommendations for staffing levels for a pod in the Ski School. The model, in its final form, is captured in a PC-based scheduling tool. Several different components are necessary in order to obtain a dairy-instructor schedule when the scheduling tool is applied. Figure 13 below shows the conceptual model framework and components involved in producing the daily staffing level recommendations. Forecast Model Regression Factors: Day of Week Pre-bookings... Cost Data Shortage costs, overage costs,: critical fractile Demand Distribution Management Adjustments Staffing Level Figure 13: Conceptual scheduling tool model framework In the Scheduling Tool, the user is asked to enter the actual number of participants and number of potential participants turned away for that day (AM and PM) as well as the number of pre-bookings and whether it is a holiday for the following day. Together with the day-of-week, which is automatically calculated, the appropriate variables are entered in the forecasting model to obtain a point prediction of the following day's demand. With the appropriate cost data, the optimal staffing policy is then applied against the point prediction to obtain a recommended staffing level for the pod for the morning of the next day. The calculation for the optimal number is automated in the Scheduling Tool and simply requires the day's point forecast since the critical fractile and standard deviation have already been calculated as model parameters when fitting the model. The model parameters, however, will require periodic updating through the season to reflect the most recent demand behaviour. (See section 5.4 for a discussion of Forecast Model Updating.) Supervisors at the Ski School are able to input their adjustments to, or override, the staffing recommendations subjectively. The override value, if the adjustment is made, is then used as the staffing level. The management adjustments are necessary since weather, among other factors, is not included in the forecasting models despite the conjecture that it is a very important factor that affects demand. The adjustments enable the Supervisors to include subjective or external factors to the staffing recommendations that might not otherwise be explained by the forecasting model. As well, the Scheduling Tool is intended to be a decision support tool and is not meant to replace management's best judgment. 28 After any necessary adjustments are made, the data along with the forecasts, recommended staffing levels, and override values are submitted to a historical data sheet. The data file is used to collect accurate and complete data for future refitting of the model. See appendix A.3 in the Appendices for the Scheduling Tool User's Guide. The User's Guide gives a more detailed explanation of how Ski School Supervisors use the Scheduling Tool. Also, see appendix A.4 for the MS Excel V B A Code used in the Scheduling Tool. 29 C H A P T E R V DISCUSSION AND RECOMMENDATIONS 5.1 Cost vs. Service Level The recommended demand forecasting model and optimal staffing policy work in conjunction to rninimize expected cost. Only following the calculation of the Newsvendor fractile is the recommended optimal service level revealed. In the case of this thesis, the optimal service level is above the 50th percentile. Because the critical fractile is based purely on marginal costs, the optimal staffing policy could have recommended a service level below the 50th percentile depending on the various costs. If this were true, the optimal staffing level would recommend a number that was below the forecasted demand. Although the policy might nainimize cost, the service level at the Ski School would deteriorate severely. Several long-term intangible costs (that are not already included in the calculation of the critical fractile) to the Ski School would be severe: loss of goodwill, weakening of the Whistler Blackcomb brand, and contradiction of the primary principle of the Ski School (i.e. to satisfy all demand and attain a high service level). Fortunately the Newsvendor critical fractile, in this case, recommends a staffing level above the forecasted demand. Although the recommended 70th percentile is considerably lower than the original target of 100th percentile service level, the Ski School now understands the consequences and costs of trying to meet all demand. For the Ski School, the recommendations correspond intuitively with its current scheduling efforts. Otherwise, serious consideration, on the part of the Ski School, should be undertaken in accepting and implementing the proposed Scheduling Tool. 5.2 Implementation The implementation of the scheduling tool occurred in November 2000 immediately before the begiruiing of the ski season. Implementation consisted of installation of the tool, documentation for use, and training. The implementation was conducted by C O E Project Analysts at the Ski School administration office. First, the PC-based tool was installed on a few select workstations responsible for forecasting and scheduling. Second, the documentation briefly explained some of the background methodology and general instructions for use (See appendix A.3 for User's Guide). Last, several Ski School Supervisors and Schedulers were trained in the use of the scheduling tool in order to support decision-making. 5.3 Project Risks Some of the risks that may have prevented the success of the project include the lack of certain pieces of data, the optimal solution does not yield significantly better results than the status quo, various assumptions are not justified, commitment or buy-in to the project is deficient (so the necessary resources are not available for use), integrating the scheduling tool to existing systems if it cannot stand alone, and insufficient time to complete and implement a fully functioning tool before the beginning of the season. All of the above posed a certain amount of risk, but were avoided or solved during the course of the project. 30 As a final contingency, if all components of the scheduling project were not useful, the process of evaluating the current staffing procedure and investigating the factors that affect demand were, in themselves, useful. Valuable pieces of information to collect were identified, flaws or problems with current data sources were unearthed, and new initiatives arose. 5.4 Areas for Further Investigation Forecast Model Updating The proposed forecasting model parameters are currently fitted to data from the 1999-2000 season. If the forecasting model is implemented at the Ski School, the daily predictions must be as accurate as possible. Because each year's demand has its own peculiarities, the forecasting model should be fitted again using updated data from the 2000-2001 season as soon as it becomes available. The area that requires further investigation, however, is detemiining how often the forecasting models must be updated. The Scheduling Tool could be programmed to record the daily demand data and refit the model parameters daily whenever forecasts are made. The marginal benefit of doing such is questionable and the purely automated forecasting model might not change appropriately without human intervention. Waiting for the season to pass and refitting the forecasting model during the off-season might also prove to be too long of a period to wait. Thus, forecast model updating could be a future topic of study stemming from this project. Long-Term Forecasting and Scheduling The objectives of the project include developing an accurate daily demand forecasting model and developing optimal staffing rules. Through the analysis necessary for characterizing the factors that affect daily demand along with developing a daily scheduling tool, the background research required for developing long-term forecasting models and schedules was completed. Some of the same forecasting models that are used for predicting demand the next day can be easily extended to forecast several days, weeks, or months ahead. The benefits of long-term forecasting are numerous; less night-before scheduling, more accurate master schedule, fewer "reserve" instructors, better basis for setting prices, and other intangible advantages to being proactive. The overall size of the Ski School can be more accurately determined at the beginning of the season so that less hires must be made. Ultimately, the Ski School should be able to save money if long-term forecasting and scheduling were undertaken. Application of Revenue Management Conducting the background research into Ski School operations and the daily demand data analysis, it is apparent that demand for lessons fluctuated throughout the day in a somewhat predictable fashion. Many more participants request half-day morning lessons than half-day afternoon lessons. Thus, many more instructors are required to be available in the mornings but sit idle, while still being paid, in the afternoons. It is apparent, then, that half-day morning lessons cost the Ski School more to staff than half-day afternoon lessons. 31 The Ski School, therefore, should charge more for morning lessons appropriately higher (the price of morning and afternoon lessons are currently the same). By having higher prices in the morning, price-sensitive customers will take more lessons in the afternoon while price-insensitive customers would be willing to pay the higher prices. The net effect of such a policy is two-fold: 1) demand throughout the day should even out with more participants for afternoon lessons and less for morning lessons and 2) the Ski School will maximize its profit by m a x i m i z i n g revenue with higher prices to customers who are willing to pay more and by minimizing the cost of paying instructors to be on standby. The one question is how much more should the Ski School charge for lessons to even-out demand and maximize profit? The practice of setting product price to control demand for the product is known as revenue management. It is widely used in the airline, hotel, and car rental industries and may easily be applied to the ski industry. In a certain way it is currently being employed at the Ski School with differential pricing for Christmas, Regular, and Off-season lesson prices. By charging more during the high-demand season of Christmas, the Ski School is able to attain greater revenue since customers are willing to pay the higher lesson prices. The same principles may be applied to daily, weekly, and seasonal demand. Instead of an arbitrary price determination, however, a thorough and quantitative analysis may be performed to accurately price lessons in order to level demand and maximize profit. The application of revenue management will yield a recommended new product price list for several pods daily, weekly, and throughout the season. Since this analysis does not fully evaluate the qualitative aspects of customers' reactions or acceptance, the recommendation is only intended as a decision aid that, as a contingency, highlights the relative prices that will maximize Ski School profits. 32 CONCLUSIONS Daily demand forecasting and staffing at the Whistler Blackcomb Ski and Snowboard School are truly unique applications of a basic statistical practice and a somewhat theoretical inventory model. The final recommendation to the Ski School is to apply a regression model that takes into account pre-bookings, day of week, holidays, and yesterday's demand along with a Newsvendor inventory model. The proposed model is captured neatly in a Scheduling Tool that allows Supervisors at the Ski School to apply these recommendations daily for instructor staffing. Implementation of the tool takes place in November 2000 at the Ski School for immediate use in the 2000-2001 season. The advantages of using the Scheduling Tool to the Supervisor include intuitive, easier, and more accurate daily staffing. But ultimately, the Ski School itself benefits the most by niinimizing unnecessary overage and underage costs associated with inaccurate forecasts and scheduling. Determining how often the forecasting model needs to be updated, extending the forecasting and scheduling model to a longer-term focus, and exploring possible revenue management applications are all areas that may require further study following the completion of this thesis. 33 R E F E R E N C E S Andrews, B.H. and Cunnm^yam, S.M.; L. L. Bean improves call-center forecasting; Interfaces, Nov/Dec 1995; Vol. 25, Iss. 6; pg. 1, 13 pgs Brusco, M.J. and Jacobs, L. W.;Personnel tour scheduling when starting-time restrictions are present; Management Science, Apr 1998; Vol. 44, Iss. 4; pg. 534, 14 pgs Brusco, M.J. and Johns, T.R.; Staffing a multiskilled workforce with varying levels of productivity: An analysis of cross-training policies. Decision Sciences, Spring 1998; Vol. 29, Iss. 2; pg. 499, 17 pgs Gmebner, D.F. andMerz, CM.; Solving the inventory problem for the sale of seasonal merchandise; Journal of Small Business Management, Jul 1990; Vol. 28, Iss. 3; pg. 19, 8 pgs Gurnani, H. and Tang, C.S.; Note: Optimal ordering decisions with uncertain cost and demand forecast updating; Management Science, Oct 1999; Vol. 45, Iss. 10; pg. 1456, 7 pgs Lau, A., and Lau, H.; Maximizing the probability of achieving a target profit level in a two-product newsboy problem; Decision Sciences, Spring 1988; Vol. 19, Iss. 2; pg. 392,17 Pg s Li etal.; A two-product newsboy problem with satisficing objective and independent exponential demands; H E Transactions, Mar 1991; Vol. 23, Iss. 1; pg. 29, 11 pgs Riddington, G.L.; Forecasting ski demand: Comparing learning curve and varying parameter coefficient approaches; Journal of Forecasting, Chichester; May 1999; Vol. 18, Iss. 3; pg. 205, 10 pgs Van Mieghem, J.A.; Investment strategies for flexible resources; Management Science, Aug 1998; Vol. 44, Iss. 8; pg. 1071, 8 pgs 34 A P P E N D I C E S A.1 Data Imputation The maximum number of season's days of data were used to construct models since they become more accurate with more observations. Although demand data exists for the three seasons from 1997 to 2000, only 1999-2000 data were used for model fitting and performance testing; The 1997-1998 data were recorded and categorized by the Ski School in a different manner and are, therefore, unreliable. One of the most influential factors in the multiple regression models, pre-bookings (see 0 for factors that affect demand), was only tracked by the Ski School for the 1999-2000 season. Since observations with missing data values are simply ignored in regression modelling, the 1998-1999 demand data becomes useless if no pre-booking data exists or is imputed for that season. Because the inclusion of 1998-1999 data doubles the number of data points for model construction and make it much more accurate, the missing data values for pre-bookings were evaluated for data imputation. A list of the various methods of imputation and their descriptions appear below in Table 11. Imputation Method Description Mean Use the mean of the existing pre-bookings Median Use the median of the existing pre-bookings Midrange Take the maximum plus the minimum pre-bookings and divide by two Distribution based Calculate values based on random percentiles of pre-bookings distribution Multivariate normal Perform a regression analysis using pre-bookings as the dependent variable and all other nonmissing data as independent variables Mid-minimum spacing Trim N-percent of the pre-booking distribution and divide the maximum plus the minimum of this trimmed distribution by two Tukey's biweight, Hubers, and Andrew's wave Minimize the functions of the deviations of the observations from the estimates (M-estimators of location) None Subjective estimation or leave blank (SAS Institute Inc., 1997) Table 11: Data imputation methods and description It was determined that none of the above methods would provide a statistically sound method for data imputation for the large number of data points. While the aforementioned data imputation approaches are intended to fill in missing values for less than 5% of the total data points, the 180 days in the 1998-1999 season represent about 50% of the total data points. 35 Holidays, another factor used in multiple regression models, were imputed. The Ski School, however, provided a list of major holidays. In the Ski School's subjective estimation, there exists six types of holidays that have different effects on the demand: Christmas/New Year, Chinese New Year, University Break, President's Week (U.S.), March Break (U.S. and Canada), and Easter along with Victoria Day. Six indicator variables were created for the regression models to account for the different types of holidays. Depending on the date and the type of holiday, ones were entered for the variable and observation if a holiday did exist and zeros were entered otherwise. Thus, the method of imputation used here was "None" or subjective estimation. A.2 Forecasting Models Last Year's Demand A forecasting model that uses last year's demand simply takes the demand for that specific pod for a year prior to that date. The date, however, is adjusted for day of the week by taking the demand for last year's date plus one day. As well, a certain percent growth (or decline) over last year's demand may be incorporated. The formulation of the problem is as follows: Let Ft represent the forecast demand for date t Dt represent the actual demand for date t g represent the amount of growth (or decline) in demand over last year where ge(10%,0,-10%) The forecasting equation is Ft=(l + g)xDl_3M Multiple Regression Multiple regression forecasting models incorporate several chosen factors in an additive manner in order to make a prediction. Some of the factors considered for multiple regression in this example include the following: • Day of week • Holidays • Yesterday's demand The formulation of the multiple regression forecasting model that considered the day-of-week, pre-bookings, and holidays (only one indicator) factors is as follows: Let Ft represent the forecast demand for date t DOWtd represent a binary variable that indicates the day of week d for date t where DOW e (0,1) and cie(l = Mon,2 = Tue,3 = Wed,...,l = Sun) Pt represent the actual demand for date t 36 Ht represent a binary variable that indicates whether date t is a holiday where H e (0,1) The forecasting equation is Ft = Intercept + adx DOWtd + fix Pt + 5 x H, The objective was to minimize the mean square error of the model against the actual demand for 1999-2000 by adjusting the parameters a, 13,8 . Holt Winters' Seasonal Exponential Smoothing The Holt Winters' method is a form of time series (historical data that consists of a sequence of observations over time) forecasting that attempts to capture three components in a prediction: level, trend, and seasonality. Since seasonality occurs for day of the week and demand may be small for some pods, there are seven seasons and they are treated additively. The formulation of the problem for forecasting one day ahead is as follows: Let Ft represent the forecast demand for date t Dt represent the actual demand for date t bt represent the trend for date t Lt represent the level for date t S, represent the seasonal component for date t The forecasting equation is FM =Ll+b,+S,_1 where Z,=a( i ) , -S ' < _ 7 ) + ( l - f l r ) ( I M + 6 M ) bt=/3(L,-Lt_x) + (\-{3)bt_x St=r(Dt-Lt) + (l-r)S^ The objective is to rninimize the mean square error of the model against the actual demand for 1999-2000 by adjusting the parameters a,(5,y . 37 A.3 Scheduling Tool User's Guide Cenrre fot Opetuiibm lExcelkmcc Tte Ufiivticsitv" of British Cclnmhis COE Forecasting and Scheduling Tool Users Guide VcfSJfHl 1.0 November H, 2000 38 CO\S. Farocasinig mui'Selfedulin^ Taol LiwerV Guide TABLE OF CONTENTS What is the foretasting mill scheduling tool? 2 Why should I use the tool? 3 When do I use (he tool? 3 Where do I use the tool? 4 How do 1 use the tool? 4 Welcome menu 4 Pilling in missing values for past dales 5 Forecasting menu 6 Forecasting results 7 Recommended numher of instructors 8 Making adjustments to recommendations 8 Submitting forecast and recommendation 8 Who uses it and who do I ask if 1 have problems? 9 Appendix A: Scheduling tool setup 10 COE (UBC) Forecasting and Scheduling Tool User's Guide What is the forecasting and scheduling toot? The Centre for Operations Excellence Forecasting (COE) and Scheduling Tool is designed to help pod Supervisors and/or Schedulers determine daily staffing levels. 'This software tool was developed in MS Excel/Visual Basic to capture vital data throughout the season as well as to make daily staffing recommendations based on various factors. The forecasting and scheduling tool aims to tackle the problem of unpredictable participant demand that makes the determination of daily staffing levels difficult. Due to the high volume and variability of participants at: die Whistler Blackcomb Ski and Snowboard School (the Ski and Snowboard School) making an accurate daily demand prediction is a complex.endeavour. Through our analysis, we have determined that various rhetors* namely pre-bookings, weather, holidays, and day-of-week, may influence the number of classes to offer. Some of these variables can be useful predictors of demand, but there are other factors such as road closures and weather thai the tool does not lake into account. Even the best forecast will be subject to variability, and because of it, the task of determining the appropriate number of instructor creates challenges. The consequences of scheduling incorrectly could lead to either overstaffing or understaffing. Overstaffing results in unnecessary costs; understaffing results in lost sales anil customer dissatisfaction. The scheduling tool makes use of a set of optimal staffing rales that balances the cost trade-off between overstatTmu and understaffing and lakes in account the accuracy of die demand forecasting model. Using the demand prediction obtained from a forecasting model, an optimal staffing level for the following day is recommended by the tool. As well as helping schedule, the scheduling tool collects historical data that might be useful for refining the forecasting model and evaluating performance. This information is recorded in a separate historical information file that may be accessed for administrative purposes. For a conceptual view of how your pod's scheduling tool fits in and communicates with other components, please see Appendix A: Scheduling tool setup. The scheduling tool is intended to systematize Staffing across various pods. The aim of tool is to assist Supervisors and Schedulers in the development of daily instructor schedules but it is not expected to eliminate the users best judgment. COM Farcciismig -nid :k3ic<liMiisj|;Tanl Usui's GuiiJc Why should / use the toot? Faster The scheduling tool immediately displays most of the information that is important for making a stiffing level decision for the itexl day - past pic bookings, demand, Last year's demand, etc. User*, therefore, do not have to search for these statistics themselves. All calculations are also performed quickly in order to find a staffing level that will minimize cost (ie. having too many or too few instructors). Easier hi order to facilitate statistics collection, the scheduling tool prompts the user for the necessary data either through blank fields or pull-down menus. No oilier iwarrangiruj of numbers or data is necessary; the tool does it all automatically. Altogether, the use of the tool should simplify daily staffing level decision-making by capturing all the necessary information to make a forecast and staffing recommendation. Interactive The scheduling too] provides a recommended staffing level. The user may override and make changes to the forecast recommendations to take into account other relevant information (especially weather). A simple pull-down menu enables the user to acid comments along with adjustments. Supervisor! and schedulers must still use their bust judgment when deciding final staffing levels. When do / use the toot? The diagram shown below in Figure I shows the timeline of events throughout a typical da v. Tsxlay aooam tZCCpin 3:00pm 5:00pm 9:G0em Tomorrow B:0Dam Begin morning and AHeriwcri Finish lessons Determine H of lull day reasons lessons he$\n fee day pa<Ucipa;-,ls and (demand realized) instructor requirements Pte.bs^Xings made lor Laniciiov,' Figure 1: Timeline of events CaB liratftietore to nil in, o: lo cat oil As il is indicated in the diagram above, the forecasting and scheduling tool should be applied daily al approximately 5:00pm. If required data is not filled on a daily basis, COM Farec:ig:ni£ ami .Sc3ie<:fulii:sj»,Toctl UPRI'S Ckiide forecasts for ensuing days may be inaccurate. Missing data may be back-filled for any portion of the season by using one of the menus in the tool. Please see below in "Filling in missing values for past dates." Where do I use the tool? The forecasting and scheduling tool can be accessed from any Ski and Snowboard School network terminal. In order to access the network itself, you will be prompted for your password. This login and password should, however, be no different than when you usually access a computer terminal. The tool itself is located in the following path: 1. ' "P:::drive 2. "Forecasting and Scheduling" folder 3. "Ski Private Blackcomb" folder (or appropriate pod name) 4. "Scheduling Tool SPB" application Open the "P:" drive through Windows Explorer or in "My Computer." Enter the folder ''Forecasting and Scheduling" and then the "Ski Private Blackcomb" (or die appropriate pod name thai you are forecasting and scheduling for). Finally, double-click the icon for "Scheduling Tool SPB" to open the application/tool. (This is subject lo change) How do t use the tool? After opening the application for the appropriate pod; you have now begun using the scheduling tool. The Welcome menu should appear immediately. W e l c o m e m e n u The first screen the user sees is the welcome menu, shown helow in Figure 2. Four possible choices aa- available: Begin Forecasting. Update Dala. Mel p. or Quit. SthtftfuJitif Ton I C m ^ O m n t a r d b a - . i Ski Private Blackcomb BBBn Fpiccifitna Figure 2: Welcome menu - first screen COII {"qrgvaptbtff imd S.-tu-dotm.i* Tool U . ^ f s Guide The user may begin forecasting and determining staffiisg recommendations al this (win! by pressing the "Begin Forecasting" button. The user must enter today's participant demand, tomorrow's pre-bookings. and any other useful information for decision-making. Be sure to have this information available before using the tool. If past data is missing, the user should back-fill die information by pressing die "Update Data" button. It is highly recommended that accurate and complete, records for historical data be maintained in order lo refine forecasting models, evaluate performance, and give some context when determining staffing levels. The "Help" button enables the user to view some brief instructions, contained within this document, about the various menus. 'Tool lips" should appear aver blank fields lo prompt the user i f the captions beside the field are not clear. More specifically, place the pointer over ihe desired field for a couple of seconds to make more detailed instructions appear, The "Quit" button will close the scheduling tool in its current status. More specifically, if no data has been submitted using die tool, none will be recorded in the historical demand data files at this point. Filling in missing values for past dates To fill in missing values for past dates in the Update Data menu, the user has a choice, in ihe pull-down menu, of the past week's dates, If die date in question is longer than one ivcek past, the date may be typed (mm/dd/yyyy). The user should enter (lie following information for the date in question: « the day's half-day A M , half-day PM, and full-day participants (die number of people who actually took lessons that day); * the number of people tinned away from lessons that, day; * Ihe following day's half-day A M , half-day PM, and full-day prebookings (the number of lesson reservations for tomorrow made up to this point in time); • the number of instructors actually staffed to work that day; and • any special comments relevant for the day. Please see Figure 3, on the following page, for a detailed view of the menu. C O II E'orecijrfiai^ -.uui Scluslnling Too! User's, Guide fray lo eiJa - -!- Ptestae enter vvtiidn date to ait; Ell Please rpjt riiirte pafftpanE Itc Ite pad Itr that da,: T u r n e d Away Haw many people wanted feasants but h r r u t a ' * J i U'at day" staffing Pfesse enter tfe r u m t w rjf nslruclnrs v>ho « m 5th9t*4brJ th*t day: n 7 ~ p" 0 Plsa=e rfuj-m.rrf-ei- o f f ™ t < W H Y ) : (regstei-ed psrttcparrts) tor rert <»(•: AH 12 IJ 1 . 1 ^u| j i f - j . f] " I ? Please enter arry relevant comments abcut (re day: ii2 hj.l- to WetotTB Menu H 9 b 1^ Submit data a i d return to rnah menu Figure 3: Missing dates menu - fill in past data F o r e c a s t i n g m e n u Iii the forecasting menu, seen in Figure 4 below, the user must input the following prior lo obtaining a forecast: • today's half-day A M , half-day PM, and full-day participants (the number of people who actual!)' took lessons today); • the number Of people turned away from lessons today: » tomorrow's half-day A M . half-day PM, and fall-day prebookings (the number of lesson reservations for tomorrow made up to litis point in time); * the number of special bookings (ie. large corporate groups); * whether tomorrow is a holiday and if so, which holiday; and « desired average group size (for pods that teach group lessons only). When you have finished-inputting the appropriate infennatiori, press the "OK." button. If missing data is detected, it will prompt you to return and eliter the information to the necessary cells. COM Favvc-Aiimig imtlS<htKU&\\ngT<i<)\ User's. Guiikf Pdilbipftrrtt r-'tjatc k-pjt azual ni_rrt>a- of particpaiu fa tie p i d f i i radaj: r* [— p" l U r C I H l M J t f H o * msnff p e c i i b • v n " » l t a u n t b i t ATTV i i i r r ' r~o I T r r Pfcv-bouktviga k|ur'i.i>4M • •. t i e - too lr i j , (rcgistarsd partcpnts) fix harnrrow r7" n" ~ r HnlKtiiyc latcmarraw alvtlirtaf- W h i c h holiday? 3 'Ahat ts (ha ctesrart s w a g s 7 3 4 3 itrc"1 3 Wefcore Menu Hn(o Forecast rj^w Figure 4: Forecasting menu - input historical data F o r e c a s t i n g resu l t s The Forecasting Results show the forecast number of participants for the pad (highlighted). As you can see in Figure 5 below, historical information about the past week is also shown (pit-bookings, last year's demand, the past week's actual demand) along with a graph of this information. These figures may be useful information if you wish to change- the staffing level f r o m what is recommended. Day of Wonk ODDIHHI PnfcfcaaHngi l « r t Y » » r | ! Actual; November? Thursday 42 31 27 Novenabrr 3 Friday 40 24 3E> Nov««»k*r 4 Ssluiclay 41 18 31 Novmaber 5 Sunday 48 14 33 NovmnberS Monday 39 30 34 November 7 Tuesday 31 29 32 HnvcfiiiberH Wednesday 29 16 "34 forecast! November 9 rtniredav • a | 14 e Figure 5: Forecasting results - forecast number nf participants in the pod COE Fore*::isii3ifq '.m& S'die<hi\it^T>:hi\ Usci't Guide Recommended number of instructors The staffing level rcconuncndalion gives the number of instructors to schedule to teach the following morning given the demand forecast, desired average group size (For pads thai teach group lessons only}, desired service level, and lesson type. Recommendations are based an a forecasting model thaitakes into account several important factors. If the user feels that the recommendation requires adjustment, lie or she should click on the "Make Adjustments to Forecast Recommendations" button, If the recommended staffing level is satisfactory, the user should click the 'Submit Data" button. Please see Fmurc 6. F<MGCAC! [*h£'*4 R * C t i l t e M 1 » 6 i l J \AM*$pm. Average Grw? Nun-&*f of nvfl 5i» ImmicMK O i w a l d i i " SkSF i iv»n 21 6X1 Iftsbf a wrvko level of; &)% Holiday: M» 1 tZmhwn In krncMt -i Qjfe*n* Sae»*ast jrd. KtM'u • j staffing tad > A d ^ - t rerofm misa Figure 6: Staffing level recommendation (left) and options (right) Making adjustments to recommendations This menu allows the user to make adjustments to the staffing level recommendation. Simply input the override value in the appropriate box. This may be necessary if you believe that the recommendation requires, adjustment. Use one of the comments listed in the "Comments" pulklown menu or input your own by selecting "Other." See Figure 7 for a view of the menu. t Recommendation •- Actjtct reoammenclEd number of hsruarjrs from s "to j T El {QDrrmente [3] Figure 7: Adjustments and comments menu - override forecast recommendations Submitting forecast and recommendation At this point, the user is prompted to cheek the given information lo be added to the historical demand data file. If all clala is satisfactory, press the- "Submit Data" button. Otherwise the user may go back to the Forecasting Menu or make- adjustments to the COM Ffltvicdsmig iiini Sdhedoliis^ 'ford U'scrY Giiick; rsrornrnendations. feu Figure 8 for the Submit Numbers screen. Make sure you press "Quit71 when you are finished and before logging out of the computer. Figure 8: Submit given information to historical file Who uses it and who do I ask if I have problems? Ski and Snowboard School Supervisors and Schedulers are encouraged to use the tool daily. If after reading this user's guide, there arc still questions regarding die forecasting and scheduling tool, please direct your questions to David Fujinmgari, Ski and Snow-board Schtxd Administrator. Contact: . David Fiijimagari dfujiniagari@iulraw-est.ca Are you sire you are rgajy to •submit He ntyirwcn on this i c«iti '"" 11 9 4 7 COlf. Fcirec:istJ3ig mtd .Sclfedialm^Taol Liar's GsiiiJe Appendix A: Scheduling tool setup Figure 9, below, shows how Ihe various computer modules are setup and interact. Computers represent software applications, arrows represent flow of information, and cylinders represent data files. Raw historical data - SPB Scheduling tool - SPB Scheduling tool - SPW Raw historical data - BGW Scheduling tool • BGW SPB - Ski Private Btaitamnb SPW - Sit Private Whialcr BOW - B M I T J Group Whistler Figure 9: Scheduling Ion I setup Each tool, therefore, has historical data associated with it. The- historical data may be viewed by die Administrator to monitor the performance of the scheduling tool as well as to view reports On die overall operations of the Ski and Snowboard School. 10 A.4 MS Excel VBA Code Some selected code from the Scheduling Tool are included as part of this thesis to show how the forecasting model and staffing policies were applied. Note that this is not all of the code used for the scheduling tool, but the more important portions of the code. Opening the Workbook P r i v a t e Sub Workbook_Open() 'Opens the other necessary workbooks Application.AskToUpdateLinks = False 'Model parameters workbook Workbooks.Open ("s:\ski s c h o o l \ s c h e d u l e \ f o r e c a s t i n g and scheduling\Ski P r i v a t e Blackcomb\Model Parameters SPB.xls"), True, True 'Raw data workbooks Workbooks.Open ("s:\ski s c h o o l \ s c h e d u l e \ f o r e c a s t i n g and scheduling\Ski P r i v a t e Blackcomb\Raw data SPB.xls"), True, , , , "Whis t l e r " , True Workbooks("Scheduling Tool S P B . x l s " ) . A c t i v a t e Sheets("Home").Select Welcome.Show End Sub Updating Menu P r i v a t e Sub CommandButtonl_Click() 'Pressing OK i n the update menu Dim AMprebook, PMprebook, Fullprebook, A M p a r t i c i p a n t s , PMparticipants, F u l l p a r t i c i p a n t s , AMturnaway, PMturnaway, Fullturnaway, I n t e r c e p t , PrebookRatio -As Double Dim DOW, Day, p r e d i c t i o n As Integer ' I f user does not enter any values so no e r r o r occurs I f TextBoxll.Value = Empty Or TextBoxl2.Value = Empty Or TextBoxl3.Value = Empty Or TextBoxl4.Value = Empty Or TextBoxl5.Value = Empty Or TextBoxl6.Value = Empty Or ComboBox2.Value = " [ s e l e c t d a t e ] " Or (ComboBox2.Value = "Other" And TextBox7.Value = Empty) Then Update.Hide ErrorMessage.Show Update.Show Else 'Recording a l l the am, pm, f u l l - d a y prebooks, p a r t i c i p a n t s , and turnaway Range("e89").Value = TextBoxl4.Value AMprebook = TextBoxl4.Value Range("f89").Value = TextBoxl5.Value PMprebook = TextBoxl5.Value Range("g89").Value = TextBoxl6.Value 49 Fullprebook = TextBoxl6.Value Range("e91").Value = TextBoxll.Value AMparticipants = TextBoxll.Value Range("f91").Value = TextBoxl2.Value PMparticipants = TextBoxl2.Value Range("g91").Value = TextBoxl3.Value F u l l p a r t i c i p a n t s = TextBoxl3.Value Range("e93").Value = TextBoxl8.Value AMturnaway = TextBoxl8.Value Range ("f93") .Value = TextBoxl9.Value . PMturnaway = TextBoxl9.Value Range("g93").Value = TextBox20.Value Fullturnaway = TextBox20.Value 'Recording a l l the values f o r that day turnaway = Range("e94").Value p a r t i c i p a n t s = Range("e92").Value prebook = Range("e90").Value 'Recording day to e d i t I f TextBox7.Value = Empty Then editday = ComboBox2.Value Else editday = TextBox7.Value End I f Day = Range("DOW").Value Range("editday") = editday editday = Range("editday") 'Default f o r no p r e d i c t i o n i s 999 since occurred i n past and no s t a f f i n g , a l s o shows t o o l not used p r e d i c t i o n = 999 s t a f f i n g = "N/A" over r i d e = TextBoxl7.Value 'Writes comment: i f no typed comment, then o r i g i n a l comment value taken I f TextBox3 = Empty Then comment = ComboBoxl.Value Else comment = TextBox3.Value End I f Update.Hide 'Checks to see i f that date i s already updated Windows("raw data S P B . x l s " ) . A c t i v a t e S h e e t s ( " s h e e t l " ) . S e l e c t stopsearch = False Range ("a3") .Select Do While stopsearch = False And Not ( A c t i v e C e l l = Empty) I f A c t i v e C e l l . V a l u e = editday Then alreadyupdated = A c t i v e C e l l . O f f s e t ( 0 , 7).Value stopsearch = True 50 End I f A c t i v e C e l l . O f f s e t ( 1 , 0 ) . S e l e c t Loop 'Clears the f i e l d s I f alreadyupdated = Empty Then Update.TextBoxll.Value = Empty Update.TextBoxl2.Value = Empty Update.TextBoxl3.Value = Empty Update.TextBoxl8.Value = 0 Update.TextBoxl9.Value = 0 Update.TextBox20.Value = 0 Update.TextBoxl7.Value = 0 Update.TextBoxl4.Value = Empty Update.TextBoxl5.Value = Empty Update.TextBoxl6.Value = Empty Update.TextBox6.Value = 0 Update.ComboBoxl.Value = "None" C a l l submitnum(ByVal p r e d i c t i o n , s t a f f i n g , o v e r r i d e , comment, AMpa r t i c i p a n t s , PMparticipants, F u l l p a r t i c i p a n t s , AMturnaway, PMturnaway, Fullturnaway, AMprebook, PMprebook, Fullprebook, h o l i d a y t y p e , editday) Else Windows("Scheduling Tool S P B . x l s " ) . A c t i v a t e Worksheets("Home").Activate Range("f5").Select ErrorMessage2.Show Update.Show End I f End I f End Sub Forecasting Menu P r i v a t e Sub CommandButtonl_Click() 'Pressing OK i n the Forecast Menu Dim prebook, AMprebook, PMprebook, Fullprebook, AMparticipants, PMparticipants, F u l l p a r t i c i p a n t s , I n t e r c e p t , PrebookRatio As Double Dim DOW, Day, p r e d i c t i o n As Integer I f TextBoxll.Value = Empty Or TextBoxl2.Value = Empty Or TextBoxl3.Value = Empty Or TextBoxl4.Value = Empty Or TextBoxl5.Value = Empty Or TextBoxl6.Value = Empty Then ' I f user does not enter any values so no e r r o r occurs Forecast.Hide ErrorMessage.Show Forecast.Show Else 51 'The f o l l o w i n g are used to input demand, turned away, and prebook f o r AM, PM, and f u l l day Range("e89").Value = TextBoxl4.Value AMprebook = TextBoxl4.Value Range ("f89") .Value = TextBoxl5.Value PMprebook = TextBoxl5.Value Range("g89").Value = TextBoxl6.Value Fullprebook = TextBoxl6.Value Range("e91").Value = TextBoxll.Value AMparticipants = TextBoxl1.Value Range("f91").Value = TextBoxl2.Value PMparticipants = TextBoxl2.Value Range("g91").Value = TextBoxl3.Value F u l l p a r t i c i p a n t s = TextBoxl3.Value Range("e93").Value = TextBox20.Value AMturnaway = TextBox20.Value Range("f93").Value = TextBox21.Value PMturnaway = TextBox21.Value Range ("g93") .Value = TextBox22.Value Fullturnaway = TextBox22.Value turnaway = Range("AMturnaway").Value + Range("fullturnaway").Value p a r t i c i p a n t s = Range("AMparticipants").Value + R a n g e ( " F u l l p a r t i c i p a n t s " ) . V a l u e prebook = Range("AMprebook").Value + Range("Fullprebook").Value holidaytype = ComboBoxl.Value editday = Range("today").Value ' P r i n t s the recorded i n f o r m a t i o n to the worksheet page Range ("Prebook") .Value = prebook Range("Participants").Value = p a r t i c i p a n t s Range("Holiday").Value = holidaytype Range("turnaway").Value = turnaway Range("editday").Value = editday 'Checks to see the day of week and r e t r i e v e s the parameter c o e f f i c i e n t Day = Range("DOW").Value I f Day = "Monday" Then Windows("Model Parameters S P B . x l s " ) . A c t i v a t e DOW = Range("MonSPB").Value E l s e l f Day = "Tuesday" Then Windows("Model Parameters S P B . x l s " ) . A c t i v a t e DOW = Range("TueSPB").Value E l s e l f Day = "Wednesday" Then Windows("Model Parameters S P B . x l s " ) . A c t i v a t e DOW = Range("WedSPB").Value E l s e l f Day = "Thursday" Then Windows("Model Parameters S P B . x l s " ) . A c t i v a t e DOW = Range("ThurSPB").Value 52 E l s e l f Day = " F r i d a y " Then Windows("Model Parameters S P B . x l s " ) . A c t i v a t e DOW = Range("FriSPB").Value E l s e l f Day = "Saturday" Then Windows("Model Parameters S P B . x l s " ) . A c t i v a t e DOW = Range("SatSPB").Value Else Windows("Model Parameters S P B . x l s " ) . A c t i v a t e DOW = Range("SunSPB").Value End I f 'Finds the r e s t of the model parameters Windows("Model Parameters S P B . x l s " ) . A c t i v a t e Intercept = Range("InterceptSPB").Value PrebookRatio = Range("PrebookSPB").Value YesterdayRatio = Range("YesterdaySPB").Value 'Inputs the parameters f o r the appropriate h o l i d a y I f holidaytype = "Christmas" Then Holiday = Range("ChristmasSPB").Value E l s e l f holidaytype = "President's Week" Then Holiday = Range("PresidentSPB").Value E l s e l f holidaytype = "Easter or May 24" Then Holiday = Range("EasterSPB").Value E l s e l f holidaytype = "Chinese New Year" Then Holiday = Range("ChineseNYSPB").Value E l s e l f holidaytype = "March Break" Then Holiday = Range("MarchSPB").Value E l s e l f holidaytype = " U n i v e r s i t y Break" Then Holiday = Range("UniversitySPB").Value Else Holiday = "0" End I f Windows("Scheduling Tool S P B . x l s " ) . A c t i v a t e Worksheets("Forecasting").Activate 'The f o r e c a s t i n g model r e g r e s s i o n equation and c a l c u l a t i o n p r e d i c t i o n = Intercept + prebook * PrebookRatio + DOW + _ Holiday + p a r t i c i p a n t s * YesterdayRatio + TextBox6.Value Range("Prediction").Value = p r e d i c t i o n Range ("f5") .Select Forecast.Hide C a l l Newsvendor(ByVal p r e d i c t i o n ) End I f End Sub Staffing Policy Sub Newsvendor(ByVal p r e d i c t i o n ) 'Applies s t a f f i n g p o l i c y here Dim s t a f f i n g , Required As Integer Dim stddev, s v c l v l , AM, F u l l As Double p r e d i c t i o n = Range("Prediction").Value 'Read i n the model parameters 53 Windows("Model Parameters S P B . x l s " ) . A c t i v a t e stddev = Range("RMSESPB").Value s v c l v l = Range("PercentileSPB").Value 'Here, the r e q u i r e d number of i n s t r u c t o r s i s the p r e d i c t i o n Required = p r e d i c t i o n ' A p p l i c a t i o n of s t a f f i n g r u l e s ( i e . applying s e r v i c e l e v e l to normal d i s t r i b u t i o n of demand) s t a f f i n g = WorksheetFunction.RoundUp(WorksheetFunction.Normlnv(svclvl, Required, stddev), 0) 'Writes the r e s u l t s i n the sheet Windows("Scheduling Tool S P B . x l s " ) . A c t i v a t e S h e e t s ( " F o r e c a s t i n g " ) . S e l e c t Range("c22").Value = Required Range("d22").Value = s t a f f i n g Range("d24").Value = s v c l v l End Sub Submitting the Values Sub submitnum(ByVal p r e d i c t i o n , s t a f f i n g , o v e r r i d e , comment, AMparticipants, PMparticipants, F u l l p a r t i c i p a n t s , AMturnaway, PMturnaway, Fullturnaway, AMprebook, PMprebook, Fullprebook, holidaytype, editday) 'Submitting a l l the numbers to the raw data sheet Windows("raw data S P B . x l s " ) . A c t i v a t e S h e e t s ( " s h e e t l " ) . S e l e c t stopsearch = False Range("a3").Select 'Searching f o r the appropriate day and f i l l i n g i n the values Do While stopsearch = False And Not ( A c t i v e C e l l . V a l u e = Empty) I f A c t i v e C e l l . V a l u e = editday Then A c t i v e C e l l .Offset (1, 4) . Value = p r e d i c t i o n A c t i v e C e l l .Offset (1, 5) . Value = s t a f f i n g A c t i v e C e l l .Offset (1, 6) . Value = over r i d e A c t i v e C e l l .Offset (1, 16) .Value = comment A c t i v e C e l l . O f fset(0, 7) . Value = AMparticipants A c t i v e C e l l .Offset (0, 8) . Value = PMparticipants A c t i v e C e l l .Offset (0, 9) . Value = F u l l p a r t i c i p a n t s A c t i v e C e l l .Offset (0, 10) .Value = AMturnaway A c t i v e C e l l .Offset (0, 11) .Value = PMturnaway A c t i v e C e l l .Offset (0, 12) .Value = Fullturnaway A c t i v e C e l l .Offset (1, 13) .Value = holidaytype A c t i v e C e l l . O f f s e t ( 1 , 1) • Value = AMprebook A c t i v e C e l l . O f f s e t ( 1 , 2) . Value = PMprebook A c t i v e C e l l .Offset (1, 3) . Value = Fullprebook stopsearch = True End I f A c t i v e C e l l . O f f s e t ( 1 , 0).Select 54 Loop W i n d o w s ( " S c h e d u l i n g T o o l S P B . x l s " ) . A c t i v a t e ' I n i t i a l i z e f o r e c a s t i n g and s c h e d u l i n g r e s u l t s s h eet S h e e t s ( " f o r e c a s t i n g " ) . S e l e c t R a n g e ( " P a r t i c i p a n t s " ) . V a l u e = Empty R a n g e ( " P r e d i c t i o n " ) . V a l u e = Empty Ra n g e ( " P r e b o o k " ) . V a l u e = Empty R a n g e ( " f o r e c a s t " ) . V a l u e = Empty R a n g e ( " s t a f f i n g " ) . V a l u e = Empty R a n g e ( " o v e r r i d e " ) . V a l u e = Empty R a n g e ( " S e r v i c e L e v e l " ) . V a l u e = Empty R a n g e ( " H o l i d a y " ) . V a l u e = Empty Range("d26").Value = Empty Range("comment").Value = Empty S h e e t s ( " h o m e " ) . S e l e c t Welcome.Show End Sub 55 

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:
http://iiif.library.ubc.ca/presentation/dsp.831.1-0089910/manifest

Comment

Related Items