OPIM 5641 business decision modeling

1. OPIM 5641business decision modeling SURESH NAIR, Ph.D. Department of Operations and Information Management, School of Business Administration

2. Business Forecasting

3. Business Forecasting • A forecast is an estimate of the future level of some variable. • The variable is most often demand, but also can be others • Demand forecasts • Supply forecasts • Price forecasts • Laws of forecasting • Forecasts are almost always wrong (but still useful) • Near term forecasts more accurate that long term • Aggregate forecasts more accurate that individual forecasts • If calculated values can be used, don’t use forecasts • Forecast end products, not components (which can be calculated)

4. Steps in Forecasting • Determine the purpose of the forecast – accuracy, granularity and timeliness needed. • Establish a time horizon • Select a forecasting technique appropriate for the needs and the data available. • Obtain, clean and analyze data • Make the forecast • Monitor the forecast • Forecasting Methods • Judgmental forecasts – historical data scarce, not available or irrelevant (e.g.; demand for a new technology) • Causal Models – variables other than time (price, capacity, etc.) on the x-axis • Time Series Models – time on the x-axis

5. Judgmental Forecasts Used when judgment is better than data, when data does not reflect recent decisions, etc. • Consumer Surveys • Based of questionnaires submitted to potential customers • Executive and Sales force Opinions • Build-up forecast • Individuals familiar with a particular segment estimate the demand for that segment. These individual forecasts are aggregated. • Panel consensus forecast • Brings a panel of experts together to jointly discuss and develop a forecast. • Delphi method • Similar to panels, but experts work individually and their forecasts are shared anonymously with the rest of the panel. The effect of strong personalities in the panel is removed.

6. Causal Forecasting using Simple Linear Regression • The objective of regression is to use data to predict values. We use values of the independent variable, x, to predict the values of the dependent variable, y. • For example, we may want to predict recruitment as a function of advertising for recruitment. • The relationship between x and y may be linear or non-linear. We will focus of linear relationships. • The regression may be simple (one independent variable, x) or multiple (many independent variables, x1, x2, …). Excel can be used for both simple or multiple regression. • The equation can be represented by y = a + bx where • a is the intercept on the y-axis, that is, value of y when x=0, • b is the slope of the line, that is, the change in y for a unit change in x.

7. Download file site.txt and gm.txt from website • The regression is done using a method called the Least Squares method.

8. The R2 value is a measure of goodness of fit, called Co-efficient of determination. The closer it is to 1, the better the fit. Notice from the chart that the fit is very good. • It means that 90.97% of the variation in sales can be explained by the variation in size of store.

9. From the output we see that the equation is Sales = 901.25 + 1.69 SqFt • This equation can be used to predict the sales for any other size store not in the data set. For example, the expected sales for a store with an area of 6000 square feet would be Sales = 901.25 + 1.69(6000) = $11041.25 • The standard error of 936.85 in the output implies that the sales of a stores with an area of 6000 square feet would be normally distributed with a mean of $11041.25 and a standard deviation of 936.85. (You guessed right, it would be the same no matter what the square footage was).

10. Time Series Forecasting • A time series is a set of data obtained at regular periods over time. • The objective of time series forecasting is to use the time series data to forecast future values • The components of a time series are • Trend • Seasonality • Cyclicality (like seasonality, but over more than an year) • Irregular or random • The forecast should incorporate all these components, if present. • If no trend seems to be present, we need to smooth the series to obtain an overall long term impression. This is done using moving averages or exponential smoothing. • If trend is present, we need to estimate the trend using least squares technique.

11. Technique for Seasonality • A seasonal relative could be used • The seasonal relative can be found using the following method • Fit a trend line through the data to get a Trend for each period. • Divide the Actual by Trend for each period in the data to get a seasonality ratio • Find the seasonal relative for each of the periods in the season by taking the average of the seasonality ratios in Step 2 for each period in the season. You obtain seasonal relatives for each period in the season. • The seasonal relative can be used to • Seasonalize a forecast – extrapolate the time series to the next 7 periods and multiply by the seasonal relatives • Deseasonalize a forecast – Take seasonal data and divide by the seasonal relative to remove the seasonal component • Download the Seasonality_RPM.xlsx file from the website

12. Using Pivot tables to advantage • Download the AnnualElectricityGen.xlsx file from the website • Use the Pivot Table wizard in Excel • Select the data including the headings • Choose \Insert\Pivot Table

13. Using Pivot tables to advantage • Select the Filter, Column and Row variables, and the body of the matrix (S value) by dragging

14. Using Pivot tables to advantage • You may choose to display the data as percentage of row or column

15. Breakout Exercise (in teams) • Download the Natural Gas.xlsx file from the website • Create a de-seasonalized and seasonalized forecast for each month of 2014 • How would you incorporate the changes brought about by Fracking technologies – production is increasing dramatically and prices are going down.

16. Forecast Accuracy • The following measures of the error in the forecasting model may be used • Mean Absolute Deviation (MAD) • MAD is the average of the absolute deviations between the observed and the fitted values. • Use ABS(.) in Excel to obtain the absolute value. • Mean Square Error (MSE) • MSE is the average of the squared deviations between the observed and the fitted values.

17. More on Forecasting • If there are variables in your control that increases variability, try to reduce those variabilities first, before attempting forecasting. • For example, to forecast credit card remittance processing and billing volume, variability could be reduced by managing accounts in various billing cycles and leveling the load. Only then do the forecasting. • Forecasting models can also be used to back into rankings and fix issues that will improve rankings. • Download the US News MBA rankings data from the website.

18. Modeling What-ifsMonte Carlo Simulation

19. SIMULATION 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 evaluating overbooking policies for airplanes, inventory policies in stores, deciding on the number and location of warehouses/emission stations/fire stations, evaluating work schedules, maintenance policies, emergency room schedules, financial portfolios, real estate salesperson planning, etc., etc.

20. An Example • Life is random • Give Chance a Chance • iPod Shuffle Suppose demand and lead time for procuring a particular item is Suppose the beginning inventory of the item is 120, and the reorder point is 36. Suppose it costs $0.30 to carry one unit of the item in inventory per week, it costs $45 to place orders and get a new consignment, and the penalty for shortages is $20/unit. What is the best order quantity?

21. Setting it up It is fairly simple to evaluate different alternative order quantities quickly using simulation. • Step 1 Compute cumulative frequencies and assign random numbers The trick for assigning random numbers is easy. Compute the cumulative frequency, 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 freq., etc. • Step 2 For a particular order quantity, say Q=75, simulate the process

22. The Simulation

23. Evaluating Alternatives • Step 3 Calculate costs for this value of Q Holding cost = S Ending Inv*0.3 = 620*0.3 = $186 Ordering cost= S Orders*45 = 3*45 = $135 Shortage costs= S Shortages*20 = 26*20 = $520 TOTAL $841 • Choose another Q and repeat steps 2 and 3 • Choose the Q that minimizes total costs This procedure of simulation is called Monte Carlo Simulation. If the probabilities were 0.155 0.381 0.464 How many digit random numbers would you choose?

24. Another Simulation Example Jack sells insurance. His records on the number of policies sold per week over a 50 week period are: • Suppose we wanted to simulate the policies Jack sells over the next 50 weeks.

25. Another Example (contd.) Step 1 • Compute Probabilities, Cumulative Probabilities and assign Random Numbers 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

26. #Policies Simulation

27. #Policies Example (contd.) • Suppose 30% of the policies are Life and 70% are Supplemental, simulate the type of policies for the next 15 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 15 weeks.

28. Breakout Exercise (in teams) • Download the Medicare data, CMS Beneficiary_2010_data.xlsx from the website • Do a simulation for the # chronic conditions for 1000 beneficiaries. • If you wished to build a hospital for Medicare patients and wanted to figure out the number of beds needed, what data would you need to collect? Be specific. • Send your file to me with the filename, Breakout2_TeamNN.xlsx

29. Simulating Standard Distributions • In Excel, use \Data\Data Analysis and then select Random Number Generation. This tool can simulate the following distributions: • Normal • Uniform • Exponential • Poisson • Discrete • The random numbers generated do not change when F9 is pressed (that is, once generated, they stay fixed). • Excel functions can be used to generate some of these and other distributions. This can be done on the fly and is very handy, as we will see next.

30. Standard Distributions (contd.) • 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)

31. Standard Distributions (contd.) 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, then use • = – 5*LN(RAND()) • Exponential distribution can be used for time between arrivals of events (breakdowns, customers to a restaurant, customer service calls, cars at a toll plaza, customer orders, etc.) • For number of arrivals of events, use Poisson distribution (number of breakdowns/day, #customers to a restaurant/hour, #customer service calls/hour, etc.) • Exponential and Poisson distributions are sister distributions, both require only one number, the Mean time between arrivals (unlike the Normal distribution which requires the mean and standard deviation).

32. Standard Distributions (contd.) Generating Binomially distributed random numbers: • Use Binomial when you have a yes/no, response/no response, kind of binary situation • = CRITBINOM(n,p,rand()) • Where n is the number of trials, and p is the probability of success

33. Standard Distributions (contd.) 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

34. Jazz Festival CD Production Sally Ward wants to press CDs immediately after the Friday performance of the Festival in Cambridge, and sell CDs on Saturday and Sunday performances. Costs for manufacture of CDs and revenues are as follows: Fixed costs $15,000 Unit manufacturing costs $4.50 Revenue/unit sold $15.00 Sales depend on attendance on Friday, Saturday and Sunday. From past years she obtains the following equation for attendance Att(Sat+Sun)= 36,578+ 0.7091 Att(Friday) (1) Which has a residual error of 5952 (more on this later). She figures 4-12% of people who attend the Saturday and Sunday performances will make CD purchases. The attendance of this Friday was 21,500. How many CDs should she press that night for sale on Saturday and Sunday? Solution: Plugging 21,500 into (1) we get an expected attendance on Sat and Sun of 51,823. Therefore the attendance is going to follow a Normal distribution with mean of 51,823 and standard deviation of 5952 (the residual error stated above).

35. Breakout Exercise (in teams) A soon-to-graduate MSBAPM student is considering starting her own analytic consulting business. In assessing the market, she finds that she can respond to 10 Requests for Proposals (RFPs) every month. The chance of her getting approved depends on the hourly pricing she uses, which she estimates below. • She figures the probability of approval follows a binomial distribution (meaning when she submits 10 RFPs and the chance of approval is 50%, she will not always get 5 projects approved, it could be 3,4,5,6,…). • Being an entrepreneur, she is willing to work 240 hours a month, and any extra hours she will farm out to a student contractor at $25/hour. Suppose the hours per project follows a Poisson distribution with a mean of 50 hours. (Note: No need to simulate each project separately, assume all projects in a month have same duration.) • Determine what her pricing should be to maximize her annual earnings. • Send your file to me with the filename, Breakout3_TeamNN.xlsx

36. Critical Thinking • Simulation does not find you the optimal solution right away, but allows you to evaluate alternatives and pick the best one. • The number of iterations needed should be enough to stabilize your results. If your result bounces around, you need more iterations. • There is software available for Monte Carlo simulation, such as @Risk and Crystal Ball. These are easy to use, but Excel works perfectly fine as well. • Can you simulate continuous time processes using Monte Carlo simulation? Like the inventory simulation, entities (parts, orders) travel through the process in time. This is difficult to do using MC techniques, which are better for discrete time, static simulations. There are better software available for process simulation, such as Arena. • Using simulation software does not excuse you from modeling correctly. They only help you avoid the chore of keeping track of the accounting for various events happening simultaneously.

37. Business Optimization Modeling

38. Business Optimization • 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 had its origins during WW2 as a means of improving the effectiveness of men and materiel in the war effort. • It is used in a wide array of applications, such as • Determining the optimal product mix, transportation plans, production schedules, advertising and media planning, investment decisions, routing of trucks, location siting, assignment of people to tasks, etc. • We will learn about how LP helps decision making by considering several of these applications. Prof. Suresh Nair 38

39. LINEAR PROGRAMMING • Example: (Maximization) A firm makes 2 kinds of TV sets, A&B. The profit from A is $300, and the profit from B is $250. The limitations are • Labor: It takes 2 hours to assemble A, and 1 hour to assemble B. There are only 40 labor hours in a day. • Machine: It takes 1 hour of machine time for A and 3 hours for B. There are only 45 machine hours in a day. • Marketing: They cannot sell more than 12 units of A per day. How many of A&B should be produced each day?

40. Mathematical model Suppose X1 = The number of units of A to be produced X2 = The number of units of B to be produced Then the mathematical model can be stated as: • Maximize Profits: 300X1 + 250X2 Objective Function • Subject to the following constraints Labor 2X1+1X2 <= 40 Machine 1X1+3X2 <= 45 Constraints Marketing 1X1 <= 12 NonNegativity X1,X2 >= 0

41. Solution Method (Graphical) • Plot constraints • For each constraint • Replace inequality with equality • Set X1 to 0, calculate value of X2, say A. Mark A on Y-axis. • Set X2 to 0, calculate value of X1, say B. Mark B on X-axis. • Draw straight line A-B • Shade area below line for <= constraint, and above line for >= constraint. • The intersection of shaded areas is the Feasible Region. The solution has to fall in this area to satisfy all constraints.

42. Graphical Solution of LPs • The intersection of shaded areas is the Feasible Region. The solution has to fall in this area to satisfy all constraints.

43. Graphical Solution of LPs • Plot Objective Function Line • Set objective equal to some value divisible by both X1 and X2 coefficients (e.g., LCM, or product of the two numbers) • Plot a line for the Objective Function, just as if it were a constraint, as we did above. • Move objective line till you find the Optimal Solution • For Maximization problems, move the line away from the origin until it touches the furthest corner point of the feasible region. • For Minimization problems, move the line towards the origin until it touches the closest corner point of the feasible region from the origin. • This point is the Optimal Solution. • Note the value of X1 and X2 for this point, substitute in the objective function to obtain the value of the maximum profit (the objective function) in this example

44. Graphical Solution of LPs • The optimal solution is at the intersection of the orange and green lines. • If the objective line at the max were falling on an entire surface, instead of a point, you would have multiple optimal solutions to the problem.

45. Using Excel Solver for LPs • Setup the problem, and connect all the cells with formulas. Only the variable cells and RHS of constraints should be constants. The objective value cell should be computed. • Enter some arbitrary values for the variables • As a cross check, change the variable values and ALL the numbers should change, except for the green cells.

46. Using Excel Solver for LPs • Park your cursor on the Objective cell and Go to \Data\Solver and enter the problem parameters. • Choose Max or Min in the first set of radio buttons. • Point to the blue cells as variables • Click Add to add constraints • Leave check on Make unconstrained… • Use Simplex LP as Solving Method • Hit Solve

47. Using Excel Solver for LPs • Solver Results dialog is shown. Choose OK • Your spreadsheet has the Optimal Solution populated.

48. Example: (Minimization) Suppose Amazon stores Kindles at three warehouses in the Southwest, Midwest, and Southeast. The cost per unit for transporting them to Supply points is shown below: Use Solver to determine the optimal distribution policy for Kindles for the company.

49. Another example: (Minimization) Hartford’s police department’s requirements for on-duty police officers varies according to time of day, as shown below: Officers report for duty at the start of each of the above six time slots and remain for 8 consecutive hours. Use Solver to determine the minimum number ot police officers Hartford should hire to meet its needs throughout the day.

50. Sensitivity Analysis of LPs • Click on Sensitivity Report on the Solver Results dialog. Open the resulting tab. • This reports tells you the sensitivity of the LP results to changes in • Objective function coefficients • Constraint RHS values • Range of Values of RHS where Shadow Price is valid one at a time (not all together).