COLLEGE FUND INVESTMENT

TOPIC 7: SIMULATION AND RISK

ANALYSIS LAN JIANG

MIS 665 Prescriptive Analytics and Advanced Topics

Week 7 Agenda

• Chapter 16: Simulation Models

– Operation Models

• Drug Production with Uncertain Yield (Example 16.3)

– Financial Models

• Financial Success of New Production (Example 16.4)

• Invest Models (Example 16.6)

– Marketing Models

• Customer Loyalty Models (Example 16.7)

• Week 7 Cengage Assignment Review

• Week 7 Case Study Discussion

Chapter 16 Simulation Models

Business Analytics Data Analysis and Decision Making (7e)

S. Christian Albright

Wayne L. Winston

© 2020 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.

OPERATIONS MODELS

Operations Models

• In the operations of both manufacturing and service companies, there is likely to be uncertainty that can be modeled with simulation.

• Examples include:

– A bid for a government contract (uncertainty in the bids by competitors)

– Warranty costs (uncertainty in the time until failure of an appliance)

– Drug production (uncertainty in the yield and timing)

Drug Production with Uncertain Yield

• In many manufacturing settings, products are produced in batches, and the usable yields from these batches are uncertain.

– This is particularly true in the drug industry.

Example 16.3: Trying to Meet an Order Due Date at Wozac

• Objective: To use simulation to determine when Wozac should begin production for this order so that there is a high probability of completing it by the due date.

• Solution: Wozac Company has recently accepted an order from its best customer for 8000 ounces of a new miracle drug, and Wozac wants to plan its production schedule to meet the customer’s promised delivery date of December 1.

Example 16.3: Trying to Meet an Order Due Date at Wozac

• The drug must be produced in batches, and there is uncertainty in the time required to produce a batch, which could be anywhere from 5 to 11 days. This uncertainty is described by the discrete distribution in the table below.

• First random variable: days to produce each batch →Use @RiskDiscrete to model this!

Example 16.3: Trying to Meet an Order Due Date at Wozac

• Wozac believes the yield can be modeled by a triangular distribution with minimum, most likely, and maximum values equal to 600, 1000, and 1100 ounces.

• Second random variable: yield of each batch production → use @RiskTriang to model this!

Example 16.3: Trying to Meet an Order Due Date at Wozac

• All batches must go through a rigorous inspection once they are completed. The probability that a typical batch passes inspection is only 0.8. If the batch fails inspection, none of it can be used to fill the order.

• Third Random Variable: probability of passing the inspection (follows a uniform distribution of (0,1) → use RAND() and compare it with the passing rate of 0.8)

Example 16.3: Trying to Meet an Order Due Date at Wozac

• Simulate successive batches and keep a running total of the usable ounces obtained so far. IF functions can then be used to check whether the order is complete or another batch is required.

• Keep track of the days required to produce all of the batches needed to meet the order and then “back up” to see when production must begin to meet the due date.

Example 16.3: Trying to Meet an Order Due Date at Wozac

• The completed model appears below.

• Two Model output: Batches required & Days to Complete

• Refer to Example_16_3_lanDemo.xlsx

Example 16.3: Trying to Meet an Order Due Date at Wozac

• Set the number of iterations to 1000 and the number of simulations to 1, and then run the simulation as usual.

FINANCIAL PLANNING MODELS

Financial Planning Models

• Many companies use simulation in their capital budgeting and financial planning processes.

• Simulation can be used to answer questions such as:

– What are the mean and variance of a project’s net present value (NPV)?

– What is the probability that a project will have a negative NPV?

– What are the mean and variance of a company’s profit during the next fiscal year?

– What is the probability that a company will have to borrow more than $2 million during the next year?

Example 16.4: Developing a New Car at GF

• Objective: To simulate the cash flows from the new car model, from the development time to the end of its life cycle, so that GF can estimate the NPV of after-tax cash flows from this car.

• Solution: General Ford (GF) Auto Corporation is developing a new model of compact car, assumed to generate sales for the next five years.

Example 16.4: Developing a New Car at GF

• The fixed cost of developing the car is $700 million, incurred at the beginning of year one.

• The margin will be $4000 in year one, decreasing by 4% every other year.

• Demand is uncertain. Assume a triangular distribution for sales with parameters 50,000, 75,000 and 85,000, decreasing by some percentage having a triangular distribution with parameters 5%, 8%, and 10%.

• The company will depreciate its development cost on a straight-line basis over the lifetime of the car. The corporate tax rate is 40%. The cost of capital is 10%.

• Develop a simulation model for NPV of after-tax cash flows over the five-year time horizon.

Example 16.4: Developing a New Car at GF

• Refer to Example16_4_lanDemo.xlsx

Example 16.4: Developing a New Car at GF

• Important Jargon:

The value at risk at the 5% level, or VaR 5%, is the 5th percentile of a distribution, and it is often used in financial models. It indicates nearly the worst possible outcome.

Financial analysts typically look at VaR 5% to see how bad—or more precisely, almost how bad—things could get.

Example 16.4: Developing a New Car at GF

• Set the number of iterations to 1000 and the number of simulations to 1, and then run the simulation as usual.

• After running @RISK, obtain the histogram shown below.

– The second slider has been positioned at its default 5th percentile setting. Financial analysts often call this percentile the value at risk at the 5% level, or VaR 5%, because it indicates nearly the worst possible outcome.

Refer to Example16_4_lanDemo.xlsx

Investment Models

• Individual investors typically want to choose investment strategies that meet some pre-specified goal, such as a retirement goal.

Example 16.6: Investing for Retirement

• Objective: To use simulation to estimate the value of Sally’s future investments, in today’s dollars, from several investment strategies in T-bills, T-bonds, and stocks.

• Solution: At age 25, Sally Evans has 40 years until retirement. She plans to invest $1000 at the beginning of each of the next 40 years.

• Each year, she plans to put fixed percentages—the same each year—of this $1000 into stocks, Treasury bonds (T-bonds), and Treasury bills (T-bills). These percentages are called investment weights.

Example 16.6: Investing for Retirement

• Think of each historical year as a possible scenario, where the scenario specifies the returns and inflation factor for that year.

• We need to prepare a Discrete Probability Table to present the replicate economic status in any future year using one of the year from 1946-2007:

❑ For any future year, randomly choose one of these scenarios.

❑ Because more recent scenarios should have a greater chance of being chosen, give a weight to each scenario, starting with 1 for 2007 → Assign weights in backwards order (start with 2007 then move all the way back to 1946!)

❑ Then the weight for any year is a damping factor multiplied by the weight from the next year.

❑With weights obtain prob for each year (sum of Prob =1!!!)

Example 16.6: Investing for Retirement

• The simulation model, with damping factor 0.98, ProbWts and Probability of the each historical years are calculated.

• Refer to Example16_6_lanDemo.xlsx

VLOOKUP

• For each of the future year, randomly pick one of the historical year to represent that year’s economic status (@Risk use RiskDiscrete picking historical year index according to its probability calculated previously using damping factor!)

• Use VLOOKUP to find the T-Bill, T-Bonds, Stocks and Inflation of that selected historical years economic status!

• Tutorial on Excel VLOOKUP (optional):

https://www.simplilearn.com/tutorials/excel-tutorial/vlookup-in- excel

Example 16.6: Investing for Retirement

• Set the number of iterations to 1000 and the number of simulations to 3 (one for each set of investment weights to be tested). Then run the simulation as usual.

MARKETING MODELS

Marketing Models

• There are plenty of opportunities for marketing departments to use simulation.

– They face uncertainty in the brand-switching behavior of customers, the entry of new brands into the market, customer preferences for different attributes of products, the effects of advertising on sales, and so on.

Customer Loyalty Models

• What is a loyal customer worth to a company? This is an extremely important question for companies.

• Companies know that if customers become dissatisfied with the company’s product, they are likely to switch and never return.

– Marketers refer to this customer loss as churn.

– The loss in profit from churn can be large, particularly because long- standing customers tend to be more profitable in any given year than new customers.

Example 16.7: The Long Term Value of a Customer at CCAmerica

• Objective: To use simulation to find the NPV of a customer and to see how this varies with the retention rate.

• Solution: CCAmerica is a credit card company. The first year a customer signs up for service typically results in a loss to the company because of various administrative expenses.

• However, after the first year, the profit from a customer is typically positive, and this profit tends to increase through the years.

• Assume that the actual profit from a customer in the customer’s nth year of service is normally distributed with standard deviation equal to 10% of the mean.

Example 16.7: The Long Term Value of a Customer at CCAmerica

• At the end of each year, the customer leaves the company, never to return, with probability 0.15, the churn rate, or stays with probability 0.85, the retention rate.

• The company wants to estimate the NPV of the net profit from any such customer who has just signed up for service at the beginning of year 1, at a discount rate of 15%, assuming that the cash flow occurs in the middle of the year. It also wants to see how sensitive this NPV is to the retention rate.

• Keep simulating profits for the customer until the customer churns.

Discount Profit

• Special Notes:

– This question assume that the cash flow occurs in the middle of the year!!!

– DiscountedProfit = Profit/(1+rate)^(numYear-0.5)

Example 16.7: The Long Term Value of a Customer at CCAmerica

• The simulation model is shown at right. It simulates 30 years of potential profits.

Example 16.7: The Long Term Value of a Customer at CCAmerica

• Set the number of iterations to 1000 and the number of simulations to 5 (one for each potential retention rate). Then run the simulation as usual.

CENGAGE HOMEWORK REVIEW

Topic 7: Chapter 16

Problem 16-20

Start with P16_20_lanDemo.xlsx

Problem 16-26

Start with P16_26_student.xlsx

Model Representation in Excel

Hints

• Let’s first decompose the customer decision of each year.

• For a given year, you know:

– This year’s dealer choice (based on pervious purchasing experience)

– This year’s dealer satisfaction rate (either GLC or Hundo)

– Customer Satisfaction: use RAND() to model it

– Next Purchase Dealer Alternative: If this year choose GLC, next purchase’s alternative will be Hundo. If this year choice is Hundo, next purchase will be GLC

– Next Purchase Dealer Choice: if not satisfied, switch to the alternative otherwise stay!

– Profit of this year: if choose GLC, make $2,000

– Discounted Profit: use discount rate to calculate it’

Problem 16-46

Start with P16_46_student.xlsx

Model Representation in Excel

Hints

• Demand is modeled as Normal Distribution

• Capacity Level is to be determined (use RiskSimTable to try values from 30000 to 70000)]

• Production: should be modeled as min(demand,capacity).

• Building Cost: $2,000*Capacity

• Maintenance Cost: $400*Capacity

• Revenue: $14,000* Production

• Variable Cost: $10,000*Production

• Profit: Revenue-Variable Cost-Building Cost-Maintenance Cost

• NPV Profit (output) = NPV of next five years with 10% rate (USE NPV(rate,Profit of next 5 years) in Excel)

Problem 16-55

Useful Tutorial: https://college.cengage.com/geyser/albright/winston_9780357109953/video/video.ht ml?asset=chapter_16_problem_16_55

Model Representation in Excel

Hints

• Random Variables:

– Average Yield: use @RiskTriang

– Annual Demand: use @RiskTriang

• Mulitple scenarios to try:

– Number of batch to produce: 10 scenarios (try from 13-22!) → use @RiskSimtable

• Pounds Sold: min(demand, pounds for sale)

• Leftover: max(0, pounds for sale – demand)

• Profit: pounds sold*60+8*leftover-43,500*numberBatch

COLLEGE FUND INVESTMENT

Week 7 Case Study

Excel Instructions: Model Sheet

• Start with college_fund_student.xlsx (Also refer to Example_16_6_lanDemo)

• In ‘Model’ worksheet, specify:

– Allocation of S&P to try → use @RiskSimtable

– Allocation of T-Bill will be → 1-Allocatio of S&P

– For the next 6 years, economic status (scenario) will be selected from 1960-2014 (Note: we use uniform distribution here, use RANDBETWEEN in Excel to simulate this random variable)

– S&P return & T-bill return will be found by using VLOOKUP of the historical year

– Ending Cashing will be calculated based on the allocation & return

– Beginning Invest = previous year ending cash + 3000

Excel Instructions: Model Sheet

• Start with college_fund_student.xlsx (Also refer to Example_16_6_lanDemo)

• Use Excel functions to formulate the cells in light pink.

• Simulation model has already been setup with SEVEN OUTPUTS

• Run 1000 simulation with two allocation strategies

Deliverables of this Case Study

• One Excel file with ONE worksheets (complete college_fund_student.xlsx and rename it with your first name)

• One Word document based on your simulation results write a report:

–Refer to Case Study Template

–Answer all FOUR case study questions!

– Include Histrogram graph for the opt. Q of your choice

Hint: Get Output Summary Results

Hint: Get The Histrogram

Hint: Get The Histrogram

Hint: Get The Histrogram