1 OPIM 5984 ANALYTICAL CONSULTING IN FINANCIAL SERVICES SURESH NAIR, Ph.D. Financial Services Analytical Consulting 2 There is increasing convergence between operations, marketing and finance. Nowhere is this more evident than in the financial services industry – banking, credit cards, brokerage, insurance, mortgages, etc. What differentiates financial services from other services Large number of customers Repeat nature of interactions over the customer’s lifetime, Lots of data available for analysis and decision making, and a Wide variety of tools and techniques are applicable – from deterministic to stochastic modeling, from analytical methods to simulation. There is huge potential for analytical consulting in financial services Outline 3 Management consulting situations Attributes of a good consultant – Lessons learnt Time is of the essence – Quick analysis is very important It is far more difficult to start from a clean slate than to improve an existing process/idea. 85% of the benefit from a good idea, however implemented. Optimization only improves from there. Be Rumpelstiltskin – learn to spin straw into gold. Learn to work on unstructured problems “Socialize” recommendations – don’t surprise client Consulting situations 4 Available No need for consultants Creative Modeling Not Available Data Availability Modeling/Solution Techniques Known Not Obvious Creative Data Gathering Qualitative Inductive Recommendations 5 Attributes of a good Management Consultant Time is of the essence 6 It is more important to be timely than perfect. Problems are unstructured. No such thing as a perfect solution to a problem that is hard to define. Learn the tradeoff between time and performance If you take too long, the problem changes by then. You have the perfect solution to the wrong problem. Breakthrough vs. Incremental ideas 7 It is far more difficult to start from a clean slate than to improve an existing process/idea. 85% of the benefit from a good idea, however implemented. Optimization only improves from there. Be Rumpelstiltskin – spin straw into gold 8 Learn to work on unstructured problems Quadrant 2: Creative Modeling Quadrant 3: Creative Data Gathering Retail Bank Sweeps Credit Card solicitations End of life planning for a blockbuster drug going off exclusivity Quadrant 4: Qualitative Inductive Recommendations Impact of Comparative Effectiveness Research on drug sales 9 Service Capacity and Waiting Lines (Queueing) in Financial Services Service Capacity And Waiting Lines 10 The study of Waiting Lines or Queueing Theory is of utmost importance in the design of Service Systems, e.g., capacity study of a computer network, determining the number of servers, tellers, emergency services, size of a restaurant, number of elevators in a building, phone lines, etc., to achieve some level of service. In each of these situations, there are “servers” who provide service (e.g., tellers, phone lines) and “customers” who require that service (e.g., bank customers, phone calls). If the server is busy, the customer has to wait, and forms a waiting line of queue. Even if there are enough servers to handle customer traffic on average, queues will form because of the variability in customer traffic, and service times. Optimizing Service 11 You can add service capacity to reduce waiting, but the costs will go up. There is a trade-off between waiting costs and capacity costs. Usually, a service level is specified by the management, e.g, no more than 4 customers will have to wait, or an average customer will not have to wait more than 2 minutes. Service Configurations 12 Studies have shown that there are certain common service configurations. Poisson Arrivals, Exponential Service 13 Studies have also shown that in many cases Customer arrivals typically follow a Poisson Distribution specified by a single parameter, l , called the Arrival Rate, e.g., on average 8 arrivals/hour Service time are Exponentially distributed. Service rate is Poisson. specified by a single parameter, m , called the Service Rate, e.g, serves on l=1average 10 customers/hour. l=2 A l=4 Single Server Model 14 We evaluate various designs of service systems by analyzing the waiting lines that would result from the designs under known traffic and service patterns. If the source of customers is infinite (Infinite source, the most common case) For a SINGLE SERVER MODEL, with first come first served discipline (l/<1, M=number of servers) l 2 Average number in line In general (for single and multi-server models) Average time in line Lq Wq Average system utilization ( l ) Lq l l M Example 15 A bank customer service rep can handle 15 calls/hour on average. Calls come in at the rate of 10/hr. What would be the number of calls getting a busy signal, the amount of wait, and the utilization of the rep? Solution l = 10, =15. Lq = (10*10)/15(15-10)=100/75 = 1.33 calls Wq = 1.33/10 = 0.133 hours = 8 minutes Utilization, r = 10/(1*15)= 0.667 = 66.7% Service time = 60/15=4 minutes Total time = 8+4 = 12 minutes Exercise 16 A brokerage is considering leasing one of two photocopying machines. Mark I is capable of duplicating 20 jobs/hr at $50 per day. Mark II is capable of duplicating 24 jobs/hr, at $80/day The duplicating center is open 10 hours a day, with average arrivals of 18 jobs/hour. Duplication is performed by employees from various departments whose hourly wage is $5/hr. Should the brokerage lease Mark I or Mark II? Other Models 17 For a SINGLE SERVER, CONSTANT SERVICE TIME MODEL the queue length and wait time will be half, the other formulas remain the same. For a MULTIPLE SERVER MODEL, The formulas are complicated. Use Spreadsheet, first tab. You may use the spreadsheet even for Single Server models Example 18 In a retail bank, 5 teller counters are open. Arrivals to the counters are at the rate of 36 per hour, service is at the rate of 10/hr per counter. What will be the average length of queue? Solution: l/ = 36/10 = 3.6, M=5 From the Spreadsheet, Lq = 1.055 and P(No one in line) = 0.023 or 2.3%. Utilization, r = l/M = 36/5*10 = 72% Wq=1.055/36 = 0.029 hrs = 1.7 minutes Exercise: What would happen if arrival rate=25/hr Exercise: If waiting time (with arrival rate=36/hr) should be at most 1 minute, how many counters should be open? Analyzing the Waiting Line Formula We can rewrite the single server total time in system formula as ca 2 cs 2 WT 2 1 1 t s The above formula has three parts, the Variability part, the Utilization part, and the service Time part. We can call this the vUt equation CoV for Exponential times is 1 Note that an increase in any of the parts will increase the total time in the system. Beyond 85% utilization, the Reducing variability of arrival time and/or service time can reduce waiting time. Reducing processing time also helps. 80.0 W aitin g T im e waiting time increases rapidly 100.0 60.0 c _a= 1 40.0 c _a= 2 20.0 0.0 0 0.2 0.4 0.6 U tiliz a tio n 0.8 1 Critical Thinking 20 How do you make the tradeoff between specialization and cross training? How do you make the tradeoff between technology improvement and head count increase? 21 Creativity, Critical Thinking and Analysis Ask the Questions (Creative Brainstorming) 22 What How Why is it done at that time? Can it be done before? After? Where Can it be done some other way? Automated? Can it be made easier? When What is the objective being achieved? Why is this task done there? Can it be done somewhere else? By whom Can the task be done by someone else? Critical Examination Worksheet 23 Use the worksheet In-class Exercise – Water Filter 24 Consider a house with well water where the water filter gets clogged very quickly with particulate matter. Filters are expensive to replace every couple of weeks. Brainstorm using the worksheet to develop alternatives that will save the homeowner money. Brainstorming Ground Rules 25 Relax Have fun Laugh Support No boundaries Completely free your mind No limits on the number of ideas Fragmented ideas OK Just keywords OK Brainstorming Ground Rules 26 No criticizing (during or after) No evaluating or dismissing No dismissing EVEN BY YOU YOURSELF No “You must be joking” looks or comments Explain quickly (few seconds) No questions Let ideas you don’t understand go Speed is the key Important is “Association” not “Viability” Brainstorming Ground Rules 27 Avoid subtle evaluations How is it going to do … Isn't this violating the rules That is an excellent idea How is this different than that idea Ground Rules 28 Select a moderator No dominating No interrupting No passing Short session (20 minutes) Create ideas in silence Multiple rounds Critical Thinking Habits 29 Critical thinking is an essential component of professional accountability and apply to any discipline. These habits are show below. Confidence Contextual Perspective Consideration of the whole situation, including relationships, background, and environment, relevant to some happening Creativity Assurance of one's reasoning abilities Intellectual inventiveness used to generate, discover, or restructure ideas, imagining alternatives Flexibility Capacity to adapt, accommodate, modify, or change thoughts, ideas, and behaviors Critical Thinking Habits (contd.) 30 Inquisitiveness Intellectual Integrity Insightful sense of knowing without conscious use of reason Open-mindedness Process of seeking the truth through sincere, honest means, even if the results are contrary to one's assumptions and beliefs Intuition An eagerness to know by seeking knowledge and understanding through observation and thoughtful questioning in order to explore possibilities and alternatives A viewpoint characterized by being receptive to divergent views and sensitive to one's biases Perseverance Pursuit of a course with determination to overcome obstacles Creativity (contd.) 31 Reflection Contemplation of a subject, especially one's assumptions and thinking, for the purposes of deeper understanding and self-evaluation Adapted from R. W. Paul, Critical Thinking (Santa Rosa, Calif.: Foundation for Critical Thinking, 1992). Break-out Exercise 32 Brainstorm on how you would make the payment part of this product easier? Use the worksheet. I am keen that you fill it out completely and methodically brainstorm www.koffeekarousel.com 33 Simulating Alternative Recommendations in Financial Services Simulating Alternative Recommendations in Financial Services 34 A Simulation is an experiment in which we attempt to understand how some process will behave in reality by imitating its behavior in an artificial environment that approximates reality as closely as possible. Simulation is typically used when No formulae or good solution methods exist because assumptions in existing formulae/methods are violated. Data does not follow standard probability distributions Most importantly, to evaluate alternatives (e.g..., designs, systems, methods of providing service, etc.) Examples include valuing options, evaluating overbooking policies for airplanes, evaluating work schedules, maintenance policies, financial portfolios, real estate salesperson planning, etc. An Example 35 Jack sells insurance. His records on the number of policies sold per week over a 50 week period are: Number policies sold Frequency 0 8 1 15 2 17 3 7 4 3 Suppose we wanted to simulate the policies Jack sells over the next 50 weeks. Example (contd.) 36 Life is random Give Chance a Chance iPod Shuffle It is fairly simple to evaluate different alternative order quantities quickly using simulation. Step 1 Compute Probabilities, Cumulative Probabilities and assign Random Numbers Number policies sold Frequency Probability Cumulative Probability Random Numbers 0 8 0.16 0.16 00-15 1 15 0.30 0.46 16-45 2 17 0.34 0.80 46-79 3 7 0.14 0.94 80-93 4 3 Total 0.06 1.00 1.00 94-99 The trick for assigning random numbers is easy. Compute the cumulative probability, start from 00 to 1 less than the cum frequency. For the next row, start from the next random number to 1 less than the cum prob., etc. Step 2 Simulate the next 50 orders #Policies Simulation 37 #Policies Example (contd.) 38 Suppose 30% of the policies are Life and 70% are Supplemental, simulate the type of policies for the next 50 weeks. Suppose 25% of the Life policies are for $100K, 50% for $250K, and 25% for $500K, simulate the value of the policies for the next 50 weeks. Exercise 39 You want to start a small car rental firm and would like to lease cars that you will rent out. You want to decide how many cars to lease. You do some market research and obtain the following information Number of customers/day Probability Length of car rental Probability 0 0.2 1 0.3 1 0.2 2 0.5 2 0.3 3 0.3 4 0.2 Lease costs are $10 per day, and net profits (exclusive of lease costs) is $20/day. Simulate the process for 15 days if you had chosen to lease 3 cars. Break-out Exercise 40 For the Credit Cards data file on the website, please simulate the following for the next 24 months for a customer: Current Balance Payment Purchase + Cash advance What are the assumptions you made? What else would you have done in modeling future behavior, if you had more time? Simulating Standard Distributions 41 In Excel, use \Data\Data Analysis and then select Random Number Generation. This tool can simulate the following distributions: Normal Uniform Binomial Poisson Discrete The random numbers generated do not change when F9 is pressed (that is, once generated, they stay fixed). Standard Distributions (contd.) 42 Random numbers following certain distributions can be generated to change with every press of F9. This can be very useful in practice. Generating Normally distributed random numbers: Suppose you wanted to generate Normal random numbers with a mean of 50 and standard deviation of 5. =NORMINV(RAND(),50,5) Generating Uniformly distributed random numbers: Suppose you wanted to generate sales per day that were Uniformly distributed between 6 and 12 (inclusive). =RANDBETWEEN(6,12) Generating Exponentially distributed random numbers: Suppose you want to simulate the next breakdown of a machine that fails exponentially with a mean of 5 hours (i.e., l=0.2), then use = – 5*LN(RAND()) Standard Distributions (contd.) 43 Generating Poisson distributed random numbers: You need the average for the Poisson distribution. Use Random Number Generator under \Data\Data Analysis Generating Discrete distributed random numbers: Use Random Number Generator Exercise: Currency Notes Requirement 44 John Bender, a bank manager, needs to figure out the number of currency notes of a particular denomination to stock in his branch. If he has unused notes at the end of the day, that costs float. If he is short notes, that turns off customers. The costs are: Float cost of unused notes, per unused note $1 Penalty cost for note shortage/note $2 Customers traffic depends on how many customers came in the previous day. From past year’s data, the relationship is Customers(Wed)= 372+ 0.7091 Customers(Tues) (1) Which has a residual error of 59 (more on this later). He figures 65-85% of customers will need to withdraw cash, and they will need a mean of 10 currency notes of this denomination (Poisson distributed). Currency Notes(contd.) 45 The number of customers Tuesday was 215. How many currency notes of this denomination should the manager carry on Wednesday to minimize the sum of excess and shortage costs? Solution: Plugging 215 into (1) we get an expected customers today 525. Therefore the attendance is going to follow a Normal distribution with mean of 525 and standard deviation of 59 (the residual error stated above). Break-out Exercise (Flight Overbooking) 46 This example will focus on a very successful, regional carrier (Midwest Express Airlines). Midwest Express is headquartered in Milwaukee, Wisconsin, and was started by the large consumer products company Kimberly Clark, which has large operations in nearby Appleton, Wisconsin. Laura Sorensen is the manager of Revenue (or Yield) Management. She has been reviewing the historical data on the percentage of no-shows for many of Midwest Express' flights. She is particularly interested in Flight 227 from Milwaukee to San Francisco. She has found that the average no-show rate on this flight is 15% (Binomial, use p=0.15, number of trials, n = reservations accepted; use the function CRITBINOM(n,p,rand()) ). The aircraft (MD88) has a capacity of 112 seats in a single cabin. There is no First Class/Coach cabin distinction at Midwest Express. All service is considered to be premium service. You would believe that if you could smell the chocolate chip cookies baking as you fly along. The question Laura wants to answer is to what level should she overbook the aircraft. Demand is strong on this primarily business route. The actual demand distribution is as follows: Demand 100 105 110 115 120 125 130 135 140 145 Probability 0.03 0.05 0.08 0.12 0.18 0.19 0.12 0.10 0.08 0.05 Break-out Exercise (contd.) 47 The average fare charged on this flight is $400. If Laura accepts only 112 reservations on this flight, it is almost certain to go out with empty seats because of the no-shows that represent an opportunity cost for Midwest Express as it could have filled each seat with another customer and made an additional $400. On the other hand, if she accepts more reservations than seats, she runs the risk that even after accounting for the no-shows, more customers will show up than she has seats available. The normal procedure in the event that a customer must be denied boarding is to put the "extra" customers on the next available flight, provide them some compensation toward a flight in the future and possibly a voucher for a free meal and a hotel. This is all done to mitigate the potential ill will of the "bumped" customer. Laura figures this compensation usually costs Midwest Express around $600 on average. How many reservations should Laura accept? What is the profit for this policy? 48 Optimizing Financial Services Financial Services Optimization 49 In most business situations, managers have to achieve objectives while working within several resource constraints. For example, maximizing sales within an advertising budget, improving production with existing capacity, reducing costs while maintaining service metrics, etc. Mathematical modeling can help in such situations. Linear Programming (LP) is the most important of these techniques. It is used in a wide array of applications, such as Determining the credit card acquisitions, risk management, optimal product mix, advertising and media planning, investment decisions, branch/ATM location siting, assignment of people to tasks, etc. We will learn about how LP helps decision making by considering several of these applications. LINEAR PROGRAMMING 50 Example: (Maximization) A insurance broker sells 2 kinds of products, Homeowners Insurance (H) and Life Insurance (L). The profit from H is $300, and the profit from L is $250. The limitations are Direct personnel: It takes 2 hours to effort for sale of H, and 1 hour of effort for every sale of L. There are only 40 hours in a week. Support staff: It takes 1 hour support work for each H and 3 hours for L. There are only 45 support staff hours in a week. Marketing: The broker determines she cannot sell more than 12 units of H per week. How many of H&L should she aim to sell each week to maximize profits? Example: (Minimization) 51 A credit card company wishes to have a balance of balance carrying and monthly usage customers in its portfolio of new accounts. It is required that the portfolio have a usage rating of at least 300 units, and a monthly balance carrying level of at least 250 units. These can be produced by two types of accounts, Revolvers and Transactors. Both revolvers and transactors provide 1 unit of monthly usage per account. Only revolvers carry balance, of 3 units per account. Acquiring revolvers costs $45 and acquiring transactors costs $12/account. How many revolvers and transactors should the credit card company acquire to minimize costs while achieving its portfolio profile? Binary (0-1) Assignment Example 52 A manager Global Financial Corp, a commercial loan firm, wishes to minimize turn around time for loan processing. He has 5 associates and the task requires 4 steps. He needs to pick the best 4 associates depending on their time for each of the tasks. The average times (in minutes) for each of task was recorded as below: Task Eval and Analysis John 482 Susan 444 David 459 Ben 370 Melissa 429 Interest Rate 295 321 264 347 317 Loan Terms 379 341 384 306 397 Final Issuing 120 120 124 109 115 Who should be assigned to which task to minimize turn-around time for loan applications? Non-Binary Allocation Example 53 A bank wishes to achieve Leadership in Energy and Environmental Design (LEED) rating for its new corporate office. The energy needs in the building fall into 3 categories (1) electricity (2) heating water, and (3) heating space in the building. The costs and daily requirements are shown below: Needs Costs for Sources of Electricity Natural Solar Requirement Gas Heater /day, units Electricity Water heating 50 90 60 30 20 10 Space heating 80 50 40 30 The size of the roof limits the largest possible solar heater to 30 units/day. There is no limitation of electricity and natural gas. However, electricity needs can only be met by purchasing electricity. Find the plan that minimizes the cost of meeting energy needs. Advertising example 54 An Investment Bank often uses Linear Programming to determine an optimal allocation of advertising budgets. Recently they wanted to develop a plan that would allocate $1,200,000 among radio, TV and newspaper advertisements with the stipulation that no more than 40% of the budget be allocated to any one medium. They wanted to maximize effectiveness (# eyeballs) of the ads. After some research, the following data was gathered Media Radio TV Newspaper Effectiveness/Ad Cost/Ad 2.4 20,000 3.2 40,000 1.6 30,000 Determine the number of ads in each medium to maximize effectiveness. Credit Card Solicitation Optimization 55 A credit card company wishes to optimize it direct mail campaign for profitability and risk. It divides the mailbase into 90 segments by risk, response and balance scores. Use data file provided The company wishes to maximize pre-tax profits It wishes to pick segments to mail or not mail Each segment’s marginal risk for charge-off should be below 7.5% The total risk of charge-offs should be less than 4.5% over all segments being mailed. Break-out Exercise 56 Using the Credit Card data file do the following: Identify the optimal segments to mail for the following scenario 1. Maximize size of mailing (same constraints as before – Total Net Credit Losses < 4.5%, Marginal Net Credit Losses < 7.5%) What is the % increase in mailing from the classroom solution? What is the reduction in profit? Max mailsizeTotal NCL< 4.5%, Marginal NCL < 7.5% Max mailsizeTotal NCL< 4.5%, Marginal NCL < 7.5%, Total $ chargeoff < $50MM $ Charged off Constraint # Accounts Net Credit Losses Objective Max Profits Total NCL< 4.5%, Marginal NCL < 7.5% Marginal NCL Rate Complete the following table Pre Tax Profits 3. # Accounts Do the above with the additional constraint that total $ Charge off is less than $50MM Mail Size 2.