SURESH NAIR PhD Department of Operations and Information Management School of Business Administration 1 Business Forecasting 2 Business Forecasting A forecast is an estimate of the future level of some variable ID: 669352
Download Presentation The PPT/PDF document "OPIM 5641 business decision modeling" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
OPIM 5641business decision modeling
SURESH NAIR, Ph.D.Department of Operations and Information Management, School of Business Administration
1Slide2
Business Forecasting2Slide3
Business ForecastingA forecast is an estimate of the future level of some variable. The variable is most often demand, but also can be othersDemand forecastsSupply forecastsPrice forecastsLaws of forecastingForecasts are almost always wrong (but still useful)Near term forecasts more accurate that long term
Aggregate forecasts more accurate that individual forecastsIf calculated values can be used, don’t use forecastsForecast end products, not components (which can be calculated)
3Slide4
Steps in ForecastingDetermine the purpose of the forecast – accuracy, granularity and timeliness needed. Establish a time horizonSelect a forecasting technique appropriate for the needs and the data available.Obtain, clean and analyze dataMake the forecastMonitor the forecastForecasting MethodsJudgmental 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-axisTime Series Models – time on the x-axis
4Slide5
Judgmental Forecasts Used when judgment is better than data, when data does not reflect recent decisions, etc.Consumer SurveysBased of questionnaires submitted to potential customersExecutive and Sales force OpinionsBuild-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.
5Slide6
Causal Forecasting using Simple Linear RegressionThe 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.
6Slide7
Download file site.txt and gm.txt from website
The regression is done using a method called the Least Squares method.
7Slide8
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.8Slide9
From the output we see that the equation is Sales = 901.25 + 1.69 SqFtThis 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.25The 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).
9Slide10
Time Series ForecastingA 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 valuesThe components of a time series areTrendSeasonality
Cyclicality (like seasonality, but over more than an year)Irregular or randomThe 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.
10Slide11
Technique for SeasonalityA seasonal relative could be usedThe seasonal relative can be found using the following methodFit 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 ratioFind 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 toSeasonalize a forecast – extrapolate the time series to the next 7 periods and multiply by the seasonal relativesDeseasonalize a forecast – Take seasonal data and divide by the seasonal relative to remove the seasonal componentDownload the
Seasonality_RPM.xlsx
file from the website
11Slide12
Using Pivot tables to advantageDownload the AnnualElectricityGen.xlsx file from the websiteUse the Pivot Table wizard in ExcelSelect the data including the headingsChoose \Insert\Pivot Table
12Slide13
Using Pivot tables to advantageSelect the Filter, Column and Row variables, and the body of the matrix (S value) by dragging13Slide14
Using Pivot tables to advantageYou may choose to display the data as percentage of row or column14Slide15
Breakout Exercise (in teams)Download the Natural Gas.xlsx file from the websiteCreate a de-seasonalized and seasonalized forecast for each month of 2014How would you incorporate the changes brought about by Fracking technologies – production is increasing dramatically and prices are going down.
15Slide16
Forecast AccuracyThe following measures of the error in the forecasting model may be usedMean 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.
16Slide17
More on ForecastingIf 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.
17Slide18
Modeling What-ifsMonte Carlo Simulation
18Slide19
SIMULATIONA 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 whenNo formulae or good solution methods exist because assumptions in existing formulae/methods are violated.Data does not follow standard probability distributionsMost 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.
19Slide20
An ExampleSuppose 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?
Demand
Freq.
Lead Time
Freq.
20
0.3
1
0.4
18
0.4
3
0.6
16
0.3
20
Life is random
Give Chance a Chance
iPod ShuffleSlide21
Setting it upIt is fairly simple to evaluate different alternative order quantities quickly using simulation.Step 1Compute 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 2For a particular order quantity, say Q=75, simulate the process
Demand
Freq.
CumF
RNs
LTime
Freq.
CumF
RNs
20
0.3
0.3
00-29
1
0.4
0.4
00-39
18
0.4
0.7
30-69
3
0.6
1
40-99
16
0.3
1
70-99
21Slide22
The SimulationWeek
BegInv
RN
Demand
EndInv
Order
RN
LeadTime
Shortage
Random Numbers
Random Numbers
1
120
63
18
102
63
59
2
102
88
16
86
88
9
3
86
55
18
68
55
57
4
68
46
18
50
46
87
5
50
55
18
32
75
59
3
55
7
6
32
69
18
14
69 71413200 613 8017200 2017 975361857 36 1057811641 81 11418416257591 84 122563187 63 1382701666 70 146662046 6 154620202675573 20
22Slide23
Evaluating AlternativesStep 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 $841Choose 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?
23Slide24
Another Simulation ExampleJack 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.
24Slide25
Another Example (contd.)Step 1Compute 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
25Slide26
#Policies Simulation26Slide27
#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.
27Slide28
Breakout Exercise (in teams)Download the Medicare data, CMS Beneficiary_2010_data.xlsx from the websiteDo 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
28Slide29
Simulating Standard DistributionsIn Excel, use \Data\Data Analysis and then select Random Number Generation. This tool can simulate the following distributions:NormalUniformExponentialPoissonDiscreteThe 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.
29Slide30
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)
30Slide31
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).
31Slide32
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
32Slide33
Standard Distributions (contd.)Generating Poisson distributed random numbers:You need the average for the Poisson distribution.Use Random Number Generator under
\Data\Data AnalysisGenerating Discrete distributed random numbers:Use Random Number Generator
33Slide34
Jazz Festival CD ProductionSally 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.00Sales 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).
34Slide35
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
35Slide36
Critical ThinkingSimulation 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.
36Slide37
Business Optimization Modeling37Slide38
Prof. Suresh Nair 38Business 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 asDetermining 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.
38Slide39
LINEAR PROGRAMMINGExample: (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 areLabor: 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?39Slide40
Mathematical modelSuppose X1 = The number of units of A to be produced X2 = The number of units of B to be producedThen the mathematical model can be stated as:Maximize
Profits: 300X1 + 250X2 Objective FunctionSubject to the following constraints Labor 2X1+1X2 <= 40 Machine 1X1+3X2 <= 45 Constraints Marketing 1X1 <= 12
NonNegativity
X1,X2 >= 0
40Slide41
Solution Method (Graphical)Plot constraintsFor each constraintReplace inequality with equalitySet 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-BShade 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.41Slide42
Graphical Solution of LPsThe intersection of shaded areas is the Feasible Region. The solution has to fall in this area to satisfy all constraints.
42Slide43
Graphical Solution of LPsPlot Objective Function LineSet 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 SolutionFor 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
43Slide44
Graphical Solution of LPsThe 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.
44Slide45
Using Excel Solver for LPsSetup 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 variablesAs a cross check, change the variable values and ALL the numbers should change, except for the
green cells
.
45Slide46
Using Excel Solver for LPsPark 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 variablesClick Add to add constraints
Leave check on Make unconstrained…Use Simplex LP as Solving MethodHit Solve
46Slide47
Using Excel Solver for LPsSolver Results dialog is shown. Choose OKYour spreadsheet has the Optimal Solution populated.
47Slide48
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.
48Slide49
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.
49Slide50
Sensitivity Analysis of LPsClick on Sensitivity Report on the Solver Results dialog. Open the resulting tab.This reports tells you the sensitivity of the LP results to changes inObjective function coefficientsConstraint RHS values
Range of Values of RHS where Shadow Price is valid one at a time (not all together).50Slide51
Sensitivity Analysis of LPsRecall the solution is make 12 of A and 11 of B to get a profit of $6350Changes in Objective function coefficients
The margin for TV A is $300. This can be decreased by $217 to $83, and the solution will still be make 12 of A and 11 of B.The margin for TV A can be increased to any number above $300, and the solution will still be make
12
of A and
11
of B.
Similarly, the margins for TV B can be $0 to $899.9 (250-250, and 250+650) and
the solution will still be make
12
of A and
11
of B.
For each of these changes, the profit will change (increase or decrease) from $6350, but the # produced will stay the same,
make
12
of A and
11
of B.
The Reduced cost information, when shown, is the amount of impairment in the objective function solution value, $6350, by changing the bound on that variable by 1.
51Slide52
Sensitivity Analysis of LPsRecall you have 40 hours of labor, 45 hours of machine time, and a marketing limit of 12, to get a profit of
$6350Changes in Constraint RHS values (Shadow Prices)As managers you may want to know how much the profits would increase if you work towards relaxing constraints in your control. Remember, ALL constraints limit the profitability you can achieve. Is it worth your effort to relax a constraint?The report says if you can get an additional hour of machine time (46 hours), you will increase your profit by $83 to
$6433
. Try this in Solver, increase machine constraint to 46. What do you see?
The report says if you increase marketing effort so you can sell 13 units of A, then you increase your profit by $217 to
$6567
. Try this in Solver, increase
marketing
constraint to
13 (change back machine to 45).
What do you see
?
Changing the labor constraint does not make a difference. Why? Look at the graphical solution. Is labor constraint binding on the solution? No.
These results are indicative of impact for
small changes
in RHS. Increasing marketing limit by ten to 22 is not going to give you an additional $2170. Check this out too. It gives only $650 additional.
52Slide53
Sensitivity Analysis of LPsRecall you have 40 hours of labor, 45 hours of machine time, and a marketing limit of 12, to get a profit of $6350
Range of Values of RHS where Shadow Price is validFor the constraints, the allowable increase and decrease shows the range of RHS values where the shadow price is valid.Note that Machine allowable increase is 15 and allowable decrease is 33. Which means that Machine time can be from 12 to 60 hours (45-33, 45+15), and the profit will change by
$83.3 for each unit change
.
Similarly, Marketing constraint can be from
0 to 15 hours
(12-12, 12+3), and the profit will change by $216.7 for each unit change.
53Slide54
Breakout Exercise (in teams)Suppose Apple Computer has the following information on iPads. Touchscreens of two types and the rest of the bill of materials (BOM) are in short supply. Cost and availabilities of these are given below, along with the price and estimated demand for three models (demand and availability in ‘000).The iPad Air has a 9.7 inch display. Each inch costs $10 in the table, so each iPad Air display costs $97. There are 120,000 inches of display in stock
. The iPad Mini has a 7.9 inch of a lower resolution display, each inch costs $8, therefore the cost would be $63.2 per Mini. This display is shared with the IPad 2, but that has a 9.7 inch display, so its costs would be $77.6 per iPad 2. The total availability of this resolution display is 140,000 inches, but this is shared between the two models.How many of each model should they produce? Why do you think no Minis are being produced?
54Slide55
Breakout Exercise (contd.)Now suppose you have demand data as well:How many of each model should they now produce? Using sensitivity analysis, how much would the profit increase if the availability of Touchscreen 2048 increase by 1 unit from120,000?Suppose Apple want to produce at least as many Minis as they make Airs. What would be the production quantities now?
Send your file to me with the filename, Breakout4_TeamNN.xlsx55Slide56
Integer ProgrammingAt times, some of the values of the variables need to be integer. For example, the number of aircraft to purchase, or choice between 5 projects with different profitability. In such cases, you cannot pick ½ of one project and 1/3 of another. Mere rounding of the linear solution may not work. At other times you need to solution to be yes/no type, meaning the variable needs to be binary (0 or 1).When some variables can be linear and other need to be integer, the method is called
Mixed Integer Programming.Forcing a variable to be integer in Solver is fairly easy. Simply choose Int from the drop down for constraints.It is the Binary (
Bin
in Solver), that opens up modeling possibilities.
56Slide57
An Assignment ProblemThe coach of any age group swim team needs to assign swimmers to a 200 yard medley relay team to send to Junior Olympics. Since most of his best swimmers are very fast in more than one stroke, it is not clear which swimmer should be assigned to each of the four strokes. The five fastest swimmers and their best times (in seconds) for each of the strokes for 50 yards are:The coach wishes to assign four swimmers to the four different strokes to minimize the total relay time.Slide58
Location and Scheduling problemsLocating hospitals, banks, fire stations, malls, etc., requires you to cover significant populations. These are called set covering problems.A similar situation arises in airline crew scheduling and sales force assignments to territories.In the following example, 11 flights need to be “covered” by one or more of 12 airline crews. Costs of assigning crews is given at the bottom
What crews would “cover” the 11 flights?Slide59
Fixed Charge problemsReconsider the Amazon Kindles distribution problemThe solution was59Slide60
Fixed Charge problems (Contd.)Now suppose the three warehouses were actually not already built, but were under consideration. In the data below we have included the monthly fixed costs of running the warehouses (construction costs are included), if built. We have also increased the availability from the warehouses.The fixed cost applies only if a particular warehouse is built. The solution now may be to build only one or two of the warehouses, and distribute from them.
60Slide61
Fixed Charge problems (Contd.)To solve this problem we add set of 0/1 variables for whether to build a warehouse or notWe then add a constraint Total transported <= Build*AvailabilityTherefore, if the Build*Availability is 0, it forces the amount Transported to 0. Meaning if you don’t build a warehouse, you cannot transport from there.
Instead of Availability, any large number, M, can be used. This is the Big M method.This method can also be used to force only k of n
alternatives be chosen, for example, only one warehouse be built in the above example.
You can also force dependent decisions – we will not build Midwest unless Southeast is built. (
Southeast_build
variable>=
Midwest_build
variable)
61Slide62
Capital Budgeting with Shared costsA firm has the opportunity to undertake 7 projects, each of which (if undertaken) requires the performance of a different subset of activities, as shown below. Cost per activity is given, and revenue from the project is also shown.If the firm undertakes projects 1 and 2, its net profit is (7+10)-(4+5+7)=1, that is the activity costs for A are spent only once.
Maximize the net profit for the firm by choosing projects.62Slide63
Capital Budgeting with Shared costsTo solve this, you will need 2 sets of binary variables, one for project choice and the other to ensure activities are counted only once.The Big M method is used to ensure that the activity is counted only once.
63Slide64
Breakout Exercise (in teams)Consider the following data on the making of three products. The requirement of resources/unit product is shown in the body of the table and the last column is the availability of each resource. The data also shows the unit profit from the product and the fixed charge (manufacturing, marketing, etc.) of introducing the product.
How many of each product should they produce?Suppose they can make either product 2 or 3, but not both? How does your solution change?Now suppose Product 1 is necessary to be introduced if Product 3 is introduced. How does your solution change now?Send your file to me with the filename,
Breakout5_TeamNN.xlsx
64Slide65
Critical ThinkingBe sure that your objectives and constraints are linear.Adding a constraint will worsen your solution.Not all problems need to be feasible.Some problems may have multiple optimal solutions.Excel Solver can solve up to 200 variable problems.For larger problems, install Open Solver on your machine. It
is free. http://opensolver.org/
65Slide66
Catch your breathReview Session
66Slide67
SimulationA repair service company gets calls for refrigerator (R) and air conditioner (A) repairs. Looking at the past 200 requests for service, the manager notes that 150 (or 75%) came for A, and the remaining 50 (or 25%) for R repairs. Type of service and service times vary as below:Simulate the next 100 repair calls. What is the total service time?
67
Service
Relative Frequency
Service Time,
hrs
A
R
A
R
Clean/Adjust
0.30
0.40
1
2
Minor Repair
0.30
0.25
2
1
Major Repair
0.40
0.35
4
2Slide68
SimulationConnecticut Department of Transportation collects information from towns on salt usage in May/June, for use in the following winter season. Salt costs $50/ton with advance contracts. If additional salt is needed during winter, it could cost $150/ton. Excess leftover salt needs $10/ton for disposal/storage.Usage depends on number of storms and their severity. Forecasts for the number of storms firm up in December, much later than when salt need is collected in May/June for salt contracts.Suppose the number of storms for the next season is supposed to follow a Poisson distribution with a mean of 12, and that 30% (Binomial) are big storms (B) and the rest are small storms (S). Suppose further that for big storms the usage of salt is Normal (40000, 5000); and for small storms the usage is Uniform (10000,20000).
How much salt should the state DOT contract for?68Slide69
Business OptimizationAn 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
Determine the number of ads in each medium to maximize effectiveness.
69
Prof. Suresh Nair IIM Lucknow, NoidaSlide70
Business OptimizationA 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:
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.
70
Prof. Suresh Nair IIM Lucknow, NoidaSlide71
Business OptimizationLot Sizing Problem: Based on the production schedule for the next 4 weeks, Apple Computer has the following requirements for a particular memory chip.It has 100 units in stock and want to have an ending stock of 0 at the end of week 4.Each chip costs $2, and the vendor charges $25 for delivery, irrespective of amount ordered. Any excess stock cost $0.1/chip to carry for a week.
Shortages are not permitted in any week. How many chips should be ordered in every week?71Slide72
Breakout Exercise (in teams)An issue faced by an electric utility every day is deciding which generators to start up. The utility has 3 generators with characteristics shown below:There are two periods in a day, and the number of megawatts needed in the first period is 2900 and the second period requires 3900 megawatts.
A generator started in the first period may be used in the second period without incurring startup costs. All generators are turned off at the end of the day. Which generators should be turned on and produce how much electricity in each of the two periods?Send your file to me with the filename, Breakout6_TeamNN.xlsx
72Slide73
Service Capacity PlanningWaiting Lines (Queueing)
73Slide74
Prof. Suresh Nair 74WAITING LINES
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.
74Slide75
Why study waiting lines?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.
75Slide76
Service configurationsStudies have shown that there are certain common service configurations.
76Slide77
Case 1: Poisson Arrivals, Poisson ServiceStudies have also shown that in many casesCustomer arrivals typically follow a Poisson Distributionspecified by a single parameter, l , called the Arrival Rate, e.g., on average 8 arrivals/hourService completions are also Poisson distributed
specified by a single parameter, m , called the Service Rate, e.g, serves on average 10 customers/hour. This also implies that the time between arrivals and service times are Exponential.
77
l
=1
l
=2
l
=4
ASlide78
Single Server ModelWe 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 (Note: /<1, M=number of servers)
Average number in line In general (for single and multi-server models)Average time in line and total time in systemAverage system utilization
78Slide79
Single Server ExampleRefer to SingleServerExample_data.xlsx on the websiteTraffic and service at a customer service center with one Associate is shown in the data. What would be the number of calls getting waiting, the amount of wait, and the utilization of the rep?
Solutionl = 10, m=15.Lq = (10*10)/15(15-10)=100/75 = 1.33 calls
W
q
= 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
Retry the above at increasing
l (
say 11,12,13,14,14.5)
What do you see?
79Slide80
Important Takeaway on UtilizationYou can write the total waiting time as the following (ts is service time)In the previous example, r=2/3 and t
s=4, therefore W=3*4=12 minutes.When the utilization goes above ~85%, the wait times deteriorate dramatically (since the denominator above approaches zero)
80Slide81
Little’s LawNamed after John DC Little, MIT Professor, entrepreneur, marketing science pioneer http://dspace.mit.edu/openaccess-disseminate/1721.1/58104 Little’s Law states, if a system is in steady state, thenThe number of customers in a system, arrival rate and total wait time are related by the above simple equation.
It is very general, and applies almost everywhere. It even reads as “LAW” if you squint at it .Intuition: Our hero arrives at a queue
By the time he leaves, it has taken W time. If
l
is the rate of arrivals, in this time,
l
W
customers have arrived and are in the system behind him.
81Slide82
Little’s LawThis relationship is remarkably simple and general. We require stationarity assumptions (meaning the rates don’t change with time, and the system is in steady state), but it is quite surprising what we do not require. We have not mentioned how many servers there are, whether each server has its own queue or a single queue feeds all servers, what the service time distributions are, or what the distribution of inter-arrival times is, or what is the order of service of items, etc.
The formula also works for length of queue and wait time in queue, rather than total customers in system and total wait time seen before
It may be Little, but it is the Law
82Slide83
Little’s Law ExamplesHospital Ward: From historical records we know that the birth rate for the local community is about 5 births per day. Suppose, on average, the length of stay is 2.5 days.
We can use Little's Law to predict the average number of mothers in the maternity ward. l= 5 mothers per day, W =2.5 days. Thus, the expected queue length or number in the system is L = 12.5 mothers, meaning we will need 13 beds on average (of course you may have to plan for peak requirements, but Little’s Law provides a starting point).
Real Estate Market
: The local real estate agent in your community estimates that it takes 120 days on average to sell a house. You observe from monitoring the classified ads that
the
number of houses for sale has ranged from 20 to 30 at any point in time, with an average of 25.
Therefore, W
=
120 days
and L = 25 houses.
From
this, we can estimate the arrival rate to the system,
l
= 25/120 houses per
day, or 75
houses per year.
83Slide84
Breakout Exercise (in teams)A Health Clinic is considering leasing one of two diagnostic machines.The incoming traffic of diagnosis work and capabilities of the two machines are shown on website in DiagnosisMachine_data.xlsx
The diagnostics center is open 10 hours a day.The Clinic estimates that the cost of waiting for diagnostics is $50/hr in lost time for the patient and physician.
Mark I can be leased for $500/day and Mark II for $800/day.
Should the Clinic lease Mark I or Mark II?
Send
your file to me with the filename,
Breakout7_TeamNN.xlsx
84Slide85
Other Models, Multiple ServersFor a SINGLE SERVER, CONSTANT SERVICE TIME MODELthe queue length will be half, the other formulas remain the same.For a
MULTIPLE SERVER MODELThe formulas are complicated.Use Spreadsheet on Website, first tab.You may use the spreadsheet even for Single Server models
85Slide86
Multi-server modelThe formulas for this model are complicated.Use Spreadsheet on Website, first tab.Example: In a store, 5 checkout counters are open. Arrivals to the counters are at the rate of 36 per hour, service is at the rate of 10/hour per counter. What will be the average length of queue?
Solution: l/m = 36/10 = 3.6, M=5 From the Spreadsheet,
L
q
= 1.055 and P(No one in line) = 0.023 or 2.3%.
Utilization,
r
=
l
/M
m
= 36/5*10 = 72%
W
q
=1.055/36 = 0.029 hours = 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?
86Slide87
Determining Number of ServersIn many situations, you want a quick method for determining number of servers needed, say for an insurance processing center, or the number of emergency rooms in a hospital.An easy approximate formula exists for this, which is very similar to the familiar Normal distribution formula. See second tab in the spreadsheet on website. Here z is the Normal distribution constant for the probability of a customer
not waiting.87Slide88
Example: Determining Number of ServersExample 1: An emergency room wishes that there is only a maximum of 10% chance that any new critical care patient has to wait for a bed. The arrival rate is 3/hour, and the service rate is 1/hour.Solution: l/m
= 3; If wait probability is 10%, the probability of no wait is 90%. z for 90% is 1.65 Rounding up, we can say we need 6 critical care beds in the ER. This does not include observation beds in ER.
Confirm this from the Spreadsheet, second tab.
Example 2
: Management is willing to allow a 25%
chance that any new
critical care patient
has to wait.
They also improve service rate to 2/hour by using technology. How many rooms do they need now?
Solution:
l/m
=
1.5; z
for
75%
is
0.67;
Rounding
up, we can say we
now need only
3 critical care beds in the ER
.
88Slide89
Case 2: General Arrivals and Service (optional material begins)If the arrivals are not Poisson, and the service is not Poisson, use the following approach.The arrivals and service times can be analyzed by studying the Coefficient of Variation (CV, defined as standard deviation/mean) of time between arrivals and service times (ca and
cs respectively). Example: Time between arrivals are 2,6,4,1,7 and 10. Then the mean of these numbers is 5, and std deviation is 3.35. Then c
a
is (3.35/5) or 0.67.
c
s
can be computed similarly. Suppose the service times are 1,4,2,6,5,3, then
c
s
is (1.87/3.5) or 0.53.
If customer arrivals follow a Poisson distribution, then c
a
=1. If service is Poisson, then
c
s
=1If standard deviation is not given or known, assume Poisson arrivals and Exponential service.
It is best to use the spreadsheet on website, second tab.
89Slide90
Waiting Line Metrics (Single Server)
Please use the spreadsheet on the website.
Suppose the mean time between arrivals is t
a
and the mean service time is
t
s
. Then the utilization of the server is
For single server models
Total time in system
Average time in queue
Average length of queue
Slide91
Example
A customer service rep can handle calls in 4 minutes on average, with a standard deviation of 2. Calls come in with an inter-arrival time of 6 minutes on average, with a standard deviation of 8. What would be the number of calls waiting, the amount of wait, the total time in the system, and the utilization of the rep?
Solution
t
a
=6, c
a
=(8/6)=1.33;
t
s
=4,
c
s
=(2/4)=0.5
r
=4/6=0.67 or 67%
W
q
= 12.24-4 = 8.24 minutes
Length of queue,
L
q
= 8.24/6 = 1.37 customers
Suppose the arrivals were Poisson and the service is Poisson, then c
a
=1 and
c
s
=1Slide92
Waiting Line Metrics (Multi-server)
Please use the spreadsheet on the website.
Suppose the mean time between arrivals is ta and the mean service time
per server
is
t
s
. Then the utilization of the m servers is
For multi server models
Average time in queue
Total time in system
Average length of queue Slide93
Analyzing the Waiting Line FormulaWe can rewrite the single server total time in system formula as
The above formula has three parts, the Variability part, the Utilization part, and the service Time part. We can call this the VuT equation. Memorize this.
Note that an increase in any of the parts will increase the total time in the system.
Beyond 85% utilization, the
waiting time increases rapidly
Reducing variability of arrival time
and/or service time can reduce
waiting time.
Reducing processing time also helps
. (
Optional material ends
)Slide94
Critical ThinkingHow do you make the tradeoff between specialization and cross training?How do you make the tradeoff between technology improvement and head count increase?Managing waiting in queues may be as important as the metrics. Businesses use various techniques to take the attention of the customer away from the waitLetting them know how long the wait will beKeeping them busy, playing music, having a bar at a restaurant, showing previews before movies, etc.
Letting them make impulse purchases while waiting94Slide95
Operations Decision MakingInventory Management
95Slide96
Inventory ManagementInventory is idle goods or materials that are held for future use. The types of goods in inventory may beRaw materials and purchased partsSemifinished goods (work in process), and goods in transitFinished goods (manufacturing) or merchandise (retail stores)Spares, tools and supplies
In manufacturing firms, material costs may account for about 60% of the cost of products. This cost consists of the purchase price of the material and the cost of carrying it in inventory.Since inventory adds cost and not value to the product, we would like to minimize inventories as much as possible.Inventory has financial reporting implications in
Return on Investment
(ROI) and
Inventory Turnover
ratio computations.
Reducing inventory is the primary aim of such
management favorites as
Just in
Time
(JIT)
and
Lean
Operations
.
96Slide97
Reasons for carrying InventoryThough everyone agrees to the undesirability of carrying excessive inventories, to some extent it is a necessary evil. The reasons for carrying inventory areto meet anticipated demand (e.g., merchandise, office supplies)to smooth production requirements (e.g., seasonal items)to decouple operations (buffer stocks between manufacturing operations that run at different speeds)to permit operations (work-in-process inventories)
to protect against stock-outs (due to inaccuracies in forecasts)to take advantage of economies of scale and quantity discounts (e.g., ordering more than is immediately needed)to hedge against price increases
97Slide98
Types of inventoried itemsThere are two broad categories of items based on the type of their demand Dependent demand items - items whose demand is dependent on the demand of some other item (e.g., the demand for tires in an auto plant is dependent on the number of autos produced)Independent demand items - items whose demand is not dependent on the demand for some other item (e.g., merchandise, supplies, tools, finished goods)Inventory planning for these items are done differently. For dependent demand items we can compute requirements (e.g., if we plan to make 100 cars, we will need 400 tires). For independent demand items we have to forecast demand (e.g., we have to forecast how many cars to make).
Material Requirements Planning (MRP) focuses on dependent demand items.Here we will focus on independent demand items – e.g., items on a website for sale, grocery items, fashion items, cars, etc.
98Slide99
Managing the varietyInventory Management is challenging because of the sheer numbers involved. A store can have 50,000 different SKUs (stock keeping units)Each has to be forecast, ordered, stocked, priced, reordered, put on sale, etc.Reducing unnecessary variety (26 SKUs for Crest toothpaste – different flavors, tube sizes, containers, etc.) is an important win in inventory and supply chain management.
Marketing folks like variety to appeal to each customer segmentOperations folk hate variety because it add to complexityThought exerciseWhat kind of data analysis would be needed to make a case to reduce product variety?
99Slide100
ABC Classification Not all customers are equally important. For banks, a bout 90% of the profits are made by 10% of the customers. Similarly, not all items that are stocked are equally important.As in many other applications, a Pareto Rule (80-20) applies for inventory. That is, 80% of the costs are locked up in 20% of the items. This is fortuitous because then we need to exercise close control on only these 20% items. This is done by classifying each of the items in stock as A items: if they account for 70-80% of dollar usage value and only 15-20% of the number of items
C items: if they account for 5-15% of dollar usage value, but as much as 50-60% of the number of itemsB items: items that fall between A and C items.
100Slide101
ExampleNote that 20% of items (2) are A and account for 72% of $volume, and 50% of items (5) are C and account for 5% of $ volume.Download file from website and do ABC Analysis
101Slide102
Inventory Control SystemsThere are two kinds of inventory control systems:Periodic review systemPerpetual inventory system102Slide103
Periodic Review SystemsReview inventory on hand at fixed intervals of time, e.g., weeklyOrder up to level R, the reorder level, if inventory is under level r
103Slide104
Advantages of Periodic Review The advantages of the periodic review system are it is easy to administermany items can be ordered together (e.g., all Coke products)The disadvantages arelack of control between review periodscould have shortages between review periodsC items can be placed on periodic review.
104Slide105
Perpetual Inventory SystemPlace an order whenever the stock level goes below r, the reorder pointThe order should be for Q units, the order quantity.The advantages of the Perpetual inventory system is close monitoring of stocks. The disadvantage is the amount of record keeping, though this is less of a problem with scanning and automatic updating of stock levels.
A items may be placed on a perpetual inventory system.
105Slide106
What are the costs involved?There are three basic costs associated with inventoryHolding or carrying costsrent, electricity, heatcost of capital, insurancespoilage, theft and obsolescencestated as % of unit price. Could be 20-40%.Ordering costsS&H, typing the order, stationery, postage, calls to expedite, etc.
inspection costs on arrivalstated as $/order.Shortage costslost sales costloss of customer goodwillpenalties
106Slide107
The Economic Order Quantity ModelWe consider the simplest of models. Herethe demand rate is constantthe lead time is constantthe order is received in a single deliverythere are no quantity discounts
107Slide108
Costs in the EOQ ModelThe costs incurred areAnnual carrying cost= (avg. inventory)(inv carrying cost/unit)= (Q/2)Hwhere H is the annual carrying cost/unit/year. e.g., if the price of the item is $5 and the carrying charge is 20%, then H=0.2(5)=$1/unit/year.Annual Ordering Cost
= (number of orders/year)(cost/order)=(D/Q)Swhere D is the annual demand for the item, and S is the cost per order.Then the total cost is TC = (Q/2)H+(D/Q)S
108Slide109
Calculating the EOQThe total cost is minimized at the optimal order quantity, QoAt this order quantity, Annual carrying cost = Annual ordering cost
This Qo is called the Economic Order Quantity (EOQ)
EOQ
109Slide110
ExampleThe demand for a particular item is 18 units/week. The unit cost is $60, the ordering cost is $45/order, and the inventory carrying charge is 25% per year. What is the EOQ?Solution:D=18*52 = 936/yearS=$45/orderH=60*0.25=$15/unit/year. ThenThe total cost, TC = (75/2)15+(936/75)*45
= 562+562 = $1124The number of orders placed/year = 936/75=13/yearThe time between orders, or order cycle = 52 weeks/13 = 4 weeksIf lead time is 2 weeks, then the reorder point =demand during LT = 2*18=36 units
110Slide111
EOQ Model Sensitivity to DataThe EOQ model is relatively insensitive to small changes in parameter values entered. This is because of the square root in the expression.Also, if orders are placed of quantities around Qo but not exactly Qo, the cost increase will be marginal. Thus you can take advantage of full container loads, etc., near the EOQ, without adding to costs substantially. This is because the total cost curve is relatively flat at the EOQ.
111Slide112
Breakout Exercise (in teams)Read the mini-case on Dodge carsWhat is Chrysler's operational strategy with the Dodge Dart? Do you like having these options? What are your concerns?How does a company compete on product flexibility? What are some techniques you expect Chrysler utilizes?What are the quality concerns associated with offering this level of customization? How can a company maintain quality with this variation in product configurations?
Send your file to me with the filename, Breakout8_TeamNN.docx
112Slide113
When to Reorder - Safety StocksWe have seen that when the demand rate is constant, and the lead time is known and constant, theReorder point, r = demand during lead-timeor, r
= demand/week*LT(in weeks)In this case, we will never have a stockout because everything is predictable.What happens if demand is uncertain or leadtime is uncertain?In our example, suppose the demand could be 18/week on certain weeks, 15/week on others, and 20/week at times. Clearly, if we want to avoid
stockouts
, we will need to carry additional stocks, just in case. This is called Safety Stock.
Clearly, the more safety stock you carry, the less the chance of a
stockout
. But too much can be expensive. How do we decide how much is enough?
113Slide114
Service LevelWe need the concept of Service Level. The service level is the percentage of time we would like to be able to satisfy demand from stock, i.e., without facing a stockout.A 95% service level implies a 5% chance of stockouts, which may be acceptable in certain situations.The higher the service level needed, the higher the safety stock you will need to carry.
114Slide115
Variable Demand Rate, Constant Lead TimeMany a time, leadtime is about constant, but demand is quite variable.In such cases, it would be nice if we knew the distribution of demand.For example, we could assume that the demand follows a Normal
Distribution, if this were appropriate. Any other distribution can also be used, as applicable. This would make our choice of safety stock level easy.For the rest of the discussion, we will assume demand follows a Normal distribution. The same approach can be used for any other distribution.
Suppose the lead time is 2 weeks, and the demand has a mean of 18 with a standard deviation of 5/week. What would be the ROP?
One question to ask is, over the two weeks we know the average demand is going to be 36. Will the standard deviation over two weeks be 10?
NO. In fact, it will be 7.07, much below what you would have expected.
This is because of
Risk Pooling
.
115Slide116
Variable Demand Rate, Constant Lead TimeIt is important to remember that Standard deviations do not add up, Variances do.We could then decide how much safety stock to carry. We could use the Normal distribution tables to help us here.For example, we know that 90% of the time, the demand during lead time will be less than 36+1.28(7.07)= 45.04, or
X = m + zs
Or, 95% of the time, demand during lead time will be less than 36+1.65(7.07) = 47.67
Or, 99% of the time the demand during lead time will be less than 36+2.33(7.07)=52.47
116Slide117
Safety stock – variable demandIn other words,If we wanted a 90% service level, instead of reordering at 36 we should order at 45. The safety stock for 90% service level is 9 unitsFor 95% service level, we should reorder at 47.67(or 48). SS is 12.For 99% service level, we should reorder at 52.47(or 53). SS is 17.
The additional quantity over 36 in the above examples is the safety stock.In general,The second term in the expression is the level of safety stock.
Use
NORMSINV(p),
say
NORMSINV(0.95), to find the z value in Excel.
117Slide118
Should ABC classification affect Service Level?How about criticality of the item? Say a spare part that if not available when the machine fails can stop production for several hours.The service levels shown are only indicative of direction of reduction and not of magnitude. Actual numbers should depend on costs of inventory and criticality, as with the single period model above.Service Levels and ABC/Criticality classification
118Slide119
Breakout Exercise (in teams)Read the mini-cases on Wal-martWhat are the main takeaways from the two minicases?What are the challenges Walmart faces compared to Amazon in same day shipping? Do you think they will succeed? What
were the keys to Walmart’s success in Mexico?What would be the analytics needed to do what Walmart is doing in these cases? What data would be needed to be collected to make the decisions to start these services? What would be the metrics of success? Be as specific as possible.Send your file to me with the filename, Breakout9_TeamNN.docx
119Slide120
Critical ThinkingBrand (product line) extension's impact on inventory management. How would you evaluate the criticality of an item that is out of stock?How would you incorporate substitutes in your inventory calculations?Independence/dependence between items.How do you rate your firm’s inventory visibility? Do you know where your inventory is and in what quantities at all times? Compare with package tracking offered by UPS, etc.
How would you incorporate grouped orders, blanket orders, online orders?What has been the impact of technology on inventory management?
120Slide121
Corporate Finance Models121Slide122
Time Value of MoneyHow to determine value today of cash flows expected in the future.$100 million jackpot. Being paid as $5 million paid/year over 20 years. Are these equivalent?A dollar in hand today is worth more than a dollar sometime in the future.Because you could earn interest (or otherwise earn returns) on money you invest today.
The power of compounding is something to behold. (Einstein said it was the eight wonder of the world).If you invest $100 today (t
) at 10% interest, you will have $110 next year
(
t
). If you invest for two years, you would have $121 the following year
(
t
).
In general
Future
Value
t
= Present Value*(1+r)
t
Most times we are really interested in
Present Value
Present Value
=
Future
Value
t
/(
1+r)
t
122Slide123
The Power of CompoundingIn 1626, Manhattan was bought from the Native Americans for $24 in goods and trinkets. If this $24 was invested at 5%, it would be worth 24*(1+0.05)388, or $166 million, today. If they could invest at 6%, it would be worth $6. 6 billion instead!
Tico pays a cash dividend of $5 this year. You believe the dividend will be increased by 4% every year indefinitely. How big will be the dividend in 8 years?Future dividend8
= 5*(1.04)
8
= $6.84
Suppose you need to have $10,000 in 10 years, and can earn 6% on your money, how much do you need to invest today? 10000/(1.06)
10
=$5,584
123Slide124
Discounted Cash Flow ValuationWe know PV=FV [1/(1+r)t]The quantity
[1/(1+r)] is called the discount factor, b
(<=1)
, and the rate
r
is called the
discount rate
.
Suppose your project or investment earns $200, $400, $600, $800 in the next four years. How much is the most you should pay for this project or investment today, if you can invest your money at 11% return elsewhere.
124Slide125
Some Handy TricksSuppose you expect $S every year is perpetuity. How much is that worth today? The answer is PV=S/(1-b). Reason: PV=S+b
S+b2S+… bPV
=
b
S+
b
2
S+…
(1-
b)
PV=S, or
PV=S/(1-
b)
Ten times rule
: Look at the previous table. At
r
=11%,
b
=0.9, therefore 1/(
1-
b
)=10.
Therefore, if you plan to save $50k per year for many years, you can expect it to be worth $500k today (assuming a 10-11% annual return).
$100 million jackpot. Being paid as $5 million paid/year over 20 years. Are these equivalent
?
No, it is worth only $50 million if you could get a 11% return.
125Slide126
Some Handy TricksRule of 72: The time it takes to double your money is 72 divided by the interest rate. Suppose you can make 15% returns every year, your money will double in 72/15, or approximately 5 years.What should be the discount rate used?Should it be the interest rate on loans from banks?No, it needs to be the
Opportunity Cost of funds. That is, how much could the firm make if it freed up these funds for productive investment? If the bank loan rate is 8%, but the firm usually can make 15% on its investments, then the opportunity cost is 15%.126Slide127
Net Present ValueThis is basically figuring out the present value of cash inflows and outflows every year for a project (or investment).Suppose the investment required in a project is $10,000 and the expected revenues for the next five years is $2000 for the first two years and $4000 for the next two, and $5000 for the last year (the life of the project is 5 years). Suppose the discount rate, r, is 10%.
You would invest in this project, since the NPV is positiveThe IRR (internal rate of return) is the r that results in zero NPV (can use Goal Seek)
The
Payback Period
is the number of years to recover the initial investment
Some firms look for a 5 year payback period. Aggressive ones a 1-2 year payback.
127Slide128
Short Term Finance and PlanningWhat is a reasonable level of cash to keep on hand (or in the bank) to pay bills?How much should the firm borrow in the short term?How much credit should be extended to customers?On the balance sheet are listed Current Assets,
that are expected to convert to cash within the year (in order of liquidity)Cash and cash equivalentsMarketable securitiesAccounts receivablesInventories
Current liabilities
are obligations that are expected to require cash payment within an year.
Accounts payable
Expenses payable (including wages and taxes)
Notes payable
128Slide129
Short Term Finance and PlanningSources and Uses of CashThe basic Balance Sheet identity can be written as Net working capital + Fixed Assets = Long Term Debt + Equityand Net working
capital=(Cash + Other current Assets)-Current LiabilitiesResulting in the following on substituting and moving things around Cash = Long Term Debt + Equity + Current Liabilities - Current Assets other than Cash – Fixed AssetsActivities to Increase Cash
(from above equation)
Increase long term debt (borrowing over the long term)
Increasing equity (selling some stock)
Increasing current liabilities (getting a 90 day loan)
Decreasing current assets other than cash (selling some inventory for cash)
Decreasing fixed assets (selling some property)
129Slide130
Operating cycle and Cash cycleThe typical short-run activities of a manufacturing firm may be the following Operating and Cash cycles
Cash cycle = Operating Cycle-Accounts Payable period = 80 days in above example
Reducing the cash cycle is important for working capital management
Cash cycle does not have to be always positive. For Boeing, operating cycle is 108 days, but accounts payable period is 208 days!
130Slide131
Operating cycle and Cash cycleGo to finance.yahoo.com and get the Income Statement and Balance sheet for Pfizer (PFE). For year ending 2013,Cost of revenue is the same as cost of goods sold.From the Balance SheetInventory Period
Inventory turnover = Cost of goods sold/InventoryInventory turnover = 9.5B/6.1B=1.55Inventory period=365/1.55=235 days.
They carry inventory for 235 days on average! Too long, but need to compare with competition.
131Slide132
Operating cycle and Cash cycleReceivables Period (assuming all revenue is from credit sales0Receivables Turnover = Revenue/average receivables = 51.5B/14B = 3.69Receivables Period = 365/3.69 = 99 daysTheir average collection period is 99 days.
Operating Cycle = Inventory period + Receivables Period = 235+99 = 334 daysPayables Period
Payables turnover = Cost of goods sold/average payables
=
9.6B/7.4B
=
1.3
Payables period = 365/1.3=281 days.
Cash cycle
Cash cycle = Operating Cycle – Accounts Payable period
= 334-281 = 53 days
Not bad. Not too long to
finance payables.
132Slide133
Short term borrowingConsiderations for short term borrowingKeeping cash reserves – safe, but does not earn any interestMaturity hedging – match maturities of assets and liabilities. Finance inventories with short term bank loans, and fixed assets with long term borrowing. Firms avoid using short term loans for long term assets because short term interest rates are volatile, and will require frequent refinancing.Long term interest rates are higher than short term interest rates
133Slide134
Short term borrowing optionsUnsecuredLine of credit – borrow up to a specified amount (like credit card)Letters of credit – used in supply chain finance to reduce risk of non payment by buyer for good received. SecuredAccounts Receivable financingAssigning receivables – lender has receivables as security, but borrower is still responsible if receivable cannot be collected.
Factoring – the receivable is discounted and sold to the lender (the factor). Say for 97 cents on the dollar. Collection is the factor’s problem.Inventory loansTrade creditIncrease the accounts payable period2/10 Net 60 means buyer has 60 days to pay in full, but gets a 2% discount if paid within 10 days.
134Slide135
Find the Cash Cycle for Amazon (AMZN). Don’t be surprised if it is negative.Cash Flow BudgetingA firm has the following sales forecast, in thousands of dollars
In order to achieve these sales it needs to buy materials in advance, and sales receipts are not collected fully in the month sold. To cover working capital requirements, it has to borrow from the bank.It needs to purchase materials one month in advance. Material costs are 80% of sales.Receipts from sales from current month are 20% (of that month sales), from previous month is 60% and from two month old sales is 20%.
Interest rate paid is 1% per month on loans, and 0.5% earned on cash balances with the firm in a month.
Breakout ExercisesSlide136
Breakout Exercise (contd.)Other dataFixed costs (salaries and overheads) are $250,000 per month.Taxes of $150,000 must be paid in March, $50,000 in June. Dividends of $50,000 to be paid in JuneCash balance should never go below $250,000. Shortfall is covered by one month loans. All loans need to be paid back the next month.
You are at the start of January. The firm has $250,000 at the start of January. The firm wants to predict the max loan in a month that may be necessary.
Send your file to me with the filename,
Breakout10_TeamNN.docxSlide137
Cost Accounting Models137Slide138
Cost ClassificationCost Traceability to product or service (say airline, food store, manufacturing company respectively in examples below)Direct – pilot wages, fruits and vegetables, raw materials Indirect – insurance, utilities, supplies, property taxes, maintenanceCost behavior
Variable – peanuts and beverages, cost of groceries, direct materialsFixed – depreciation on planes, cost of building, supervisor salariesValue additionValue adding cost – costs to improve qualityNon-value adding cost – cost of HR or accounting.
138Slide139
Manufacturing Cost Flow139Slide140
Elements of Product CostsProduct costs include all costs related to the manufacturing process.Direct materials costs – can be traced to productSheet metal in automobiles, sugar in candyDirect labor costs – wages of production line workers
Overhead costs – production related costs that cannot be practically or conveniently traced directly to the end product. These includeIndirect materials costs – nails, rivets, lubricants, small toolsIndirect labor costs – cost of labor for material handling, maintenance, inspection, designOther indirect manufacturing costs – building, property taxes, insurance, depreciation
140Slide141
Allocating Overhead –Volume Based ApproachSuppose a company makes two types of product A and B. Total overhead costs next year are expected to be $20,000, and total direct labor hours (DLH) will be 400,000 hours.The overhead rate = 20000/400000=$0.05/DLH
141
A
B
DLH use
250,000
150,000
Overhead cost allocation
$12,500
$7,500
# units produced
100,000
50,000
Overhead cost/unit
$0.125
$0.150
Direct materials cost/unit
$0.180
$0.210
Direct labor cost/unit
$0.140
$0.160
Total cost/unit
$0.445
$0.520Slide142
Allocating Overhead – The ABC ApproachIn ABC one categorizes all indirect costs by activity and traces the indirect costs to the level of activities.Notice in comparison to the previous slide how dramatically the costs of A&B have changed. This can make the difference between introducing a product (or pursuing a project) and not.
“Cost is a fact, price is a policy” is a myth.Revenue sharing can similarly be done different ways, and dramatically alters incentives of partners
142
Costs
#
times done
Cost Rate/activity
Setup activity costs
$7,000
500
$14.00
Packing activity costs
$13,000
2000
$6.50
Total expected overhead cost
$20,000
A
B
# Setups
450
50
# Packings
1,800
200
Overhead cost allocation
$18,000
$2,000
# units produced
100,000
50,000
Overhead cost/unit
$0.180
$0.040
Direct materials cost/unit
$0.180
$0.210
Direct labor cost/unit
$0.140
$0.160
Total cost/unit
$0.500
$0.410Slide143
Marketing Models143Slide144
Marketing Mix4 P’s of MarketingProduct Variety, Design, Quality, Features, Brand Name, Packaging, WarrantiesPriceList price, Discounts, Payment period, Credit terms
PromotionSales promotion, Advertising, Sales force, Direct marketingPlaceChannels, Coverage, Assortments, Locations, Inventory, TransportThere are decision models for each of the above. We will look at a few.
144Slide145
Acquisition ModelingYou can use Logistic Regression to figure out a probability of response/purchase. You may use SPSS on UConn’s Skybox https://blast.skybox.uconn.edu/Open the dataset. It should have one 0/1 dependent variable, and could have one or several independent variables
Go to Analyze/Regression/Binary Logistic145Slide146
Acquisition ModelingEnter the variablesHit OK. You are doneThe output will give you a constant and coefficients of the equation.To make a prediction, in Excel simply enter
146Slide147
Segmentation and TargetingYou can use Clustering segment your customers and prospects into homogeneous groups with a view to target them separately with products and advertising. You may use SPSS on UConn’s Skybox https://blast.skybox.uconn.edu/Open the dataset.
Go to Analyze/Classify/Hierarchical Cluster147Slide148
Segmentation and TargetingEnter the variables. Uncheck PlotsChoose Save, Choose Single Solution, and enter the number of clusters you wantHit Continue, then OKA new column in the dataset will be created showing cluster membership of each customer.
148Slide149
SAMPLE SEGMENTATION STUDY FOR A HEADACHE REMEDY
% Suffer
Reg/Occ’ly
Regularly
82%
35%
58%
20%
75%
23%
63%
10%
45%
8%
Severity of Headache
(6 pt scale)
4.4
4.0
3.7
3.3
3.1
% Adults:
Past 30 Day Headache:
Sufferers
Volume
Med Treaters
Med Volume
17%
10%
17%
14%
12%
13%
25%
13%
25%
100%=
100%=
100%=
100%=
13%
16%
13%
20%
16%
18%
16%
15%
19%
16%
21%
17%
13%
8%
13%
10%
% of All Headaches:
% Mild / Moderate 100%=
% Severe 100%=
% Migraine 100%=
16%
31%
33%
12%
9%
12%
18%
20%
18%
12%
9%
11%
13%10%13%TreatmentOTC Meds atFirst TwingeMostly Rx, OTC …With Non-medsMove About;OTC MedsIf Gets WorseRx and OTC ...RelaxationPut off Till Severe,Then OTC MedsReaction toHeadacheReach forthe Cure (Med) QuicklyTreat JudiciouslyConcernUpsetting,Distracts From ResponsibilitiesSharply Limits LifeDesired MedAspectsFastPowerful,Long-lasting Relief
No Side Effects
Safe
Immediacy
Targeting
Opportunities
Share of Pain Days:
% Mild / Moderate
% Severe
% Migraine
100%
45%
33%
22%
100%
65%
20%
15%
100%
60%
25%
15%
100%
68%
18%
14%
100%
65%
20%
15%
Savvy Functionalists
Chronic
Combatants
Disciplined Bodies
Reluctantly Reliants
Super-Stressed Reactionaries
HeadacheSlide150
Modeling Brand SwitchingA firm has Brand A in the market. A competitor introduces Brand C, which is promoted heavily and attracts buyers away from A. The firm decides to introduce Brand B. It fears that B may cannibalize customers from A, in addition to getting market share back from C. The customers switch brands as shown below:Thus, if a customer uses brand A this month, s/he has a 20% chance of buying A again next month, a 20% chance of buying B next month, and a 60% chance of buying C next month. Similarly, if the customer purchases B this month, she has a 10% chance of switching to A next month, a 50% chance of staying with B, and a 40% chance of switching to C next month, and so on.
Note that the probability of making a particular purchase next month depends only on the purchase made this month, and not on the purchases made in the past. 150Slide151
Modeling Brand SwitchingWhat would be the market shares of the products after several months?It turns out they will settle to a “steady state.” Suppose the initial market shares were shown in the first row below:Note that after several periods the market share settles down
151Slide152
Modeling Brand SwitchingHow can we get the steady state market share? We first create a “transition matrix.” This is called Markov AnalysisNote that all rows add to 1. The problem is easy to solve using Solver. The variables are in blue. The constraints are
The sumproducts for columns should be equal to the variable value The variable values should add to 1The objective is not important here, but you could say the Sum should be minimized.
152Slide153
Customer Lifetime ValueWouldn’t it be great to figure out the value of a customer throughout their tenure with us? LTV calculations try to do that.LTV is computed using a combination of the previous two methods – Segmentation and Markov AnalysisSegment customers based on some relevant variables. Slot them into States. One popular way is RFM – Recency, Frequency and $ value of purchases
See how the states evolve over time using Markov analysisFigure out LTV from this analysis
153Slide154
Teaching EvaluationLet’s take a few minutes to do the Student Evaluation of Teaching (SET).This is extremely important feedback to improve the course in the next delivery.Please go to HuskyCT and complete this now. Today is the last day for doing this.::Tick tock, tick tock
::Thanks154Slide155
Breakout ExerciseA firm makes two underwater vehicles. The overhead costs are $220,000. Based on traditional cost allocation, the overhead rate is $13.75 per DLH. Unit costs for the two products are shown below.
The Controller feels that the cost of
BioScout
is too low, given the production process. Activities are shown next.
Rigger II
BioScout
Direct materials
$10,000.00
$12,000.00
Direct labor cost
$1,450.00
$1,600.00
DLH/unit
30
40
Applied overhead
$412.50
$550.00
Product unit cost
$11,862.50
$14,150.00
# units produced
400
100
Total DLH
12000
4000Slide156
Breakout Exercise (contd.)
What should be the cost of the two products using ABC?
Activity pool
Est cost
Setup
$70,000
Inspection
$20,000
Engineering
$50,000
Assembly
$80,000
Total
$220,000
Cost Driver
Rigger II
BioScout
Total
# Setups
250
450
700
# inspections
150
350
500
Engineering hours
600
1400
2000
Machine hours
5000
5000
10000Slide157
Breakout Exercise (contd.)Go to 10 cell phone pages on Amazon. Note 5-10 attributes of the product cell phones (e.g., screen size, memory, etc.)Suppose you collected web surfing data on 100 prospects. Randomly make the 100 prospects go to a subset of the 10 product pages
From this data, figure out the preference for attributes for each of these 100 prospects.Use this data to segment the prospects into 3 segments.
Look at the attributes preferred by the 3 segments and name the segments (say “Light user - Not Music Listener”)
Send
your file to me with the filename,
Breakout11_TeamNN.docx