Class 11 - Prescriptive Analytics Flashcards
Explain what Sensitivity Analysis is and Sensitivity Analysis of Pricing Strategy
Sensitivity analysis is used to examine the impact of various inputs on an expected output, given a mathematical model and set of assumptions.
It helps understand how the uncertainty regarding the inputs will affect the outcomes.
Key Formulas:
Revenue = Price * Demand
Demand is assumed to be 65,000 - 9000*price (demand decreasing as price increases)
Variable Cost = Unit Cost * Demand
Profit = Revenue - Fixed Cost - Variable Cost
Need the price ranges and unit cost ranges
Price would be on top, unit cost would be on side, put profits inside the boxes
Look for the highest value in the grid, because it shows the most profitable combinations of price and unit cost
Explain what Sensitivity Analysis is and Sensitivity Analysis of Acquisition NPV
An Internet company is thinking of purchasing another e-tailer. The e-tailer’s current annual revenues are $100 million, with expenses of $150 million. Current projections indicate that the e-tailer’s revenues are growing at x% (ranges from 10% to 50%) per year and its expenses are growing at y% (from 2% to 20%) per year. Assume that the e-tailer will last for 10 years, with a 9% discount rate. How will the NPV of this acquisition vary with revenue growth and expense growth?
Step One: Write Down Given Information
Current Revenue: 100 million
Current Expenses: 150 million
Revenue Growth: ranges from 10% to 50%
Expense Growth: from 2% to 20%
Discounted Rate: 9% Years: 10 Years
Step 2: Calculate The Projections
Calculate Revenue for each Year: Revenue = Current Revenue * ( 1 + x)^t
Calculate Expense for eash Year: Expense = Current Expense * (1 + y)^t
Calculate Profit for each Year: Profitt = Revenuet - Expenset
Discount the Profits: Discounted Profitt = Profit/ (1+0.09)^t
Sum all the dioscunted profit for the 10 years
This gives you one value in the revenue expenses box that has the sum of NPV in all 10 years for one
Scenario Analysis: What is it? Whats the difference between
Scenario analysis is used to predict outcomes under different scenarios (e.g., events, environments, market conditions, etc.).
In other words, it looks at different versions of the future. It usually considers three scenarios: base-case (or most likely), best-case, and worst-case scenarios.
While sensitively analysis focuses on how outcome varies with the change in one or two input variables, scenario analysis requires the specifications of all input variables under each scenario.
Scenario Analysis: What is a project’s NPV and each year’s profit based on the following information assuming that the project lasts for 5 years? Do the Scenario Analysis
Year 1 sales 10,000 units
Sales growth 10%
Year 1 price per unit $10
Year 1 total cost per unit $6
Interest rate 15%
Cost growth per year 5%
Price growth per year 3%
Make your table for scenario analysis
Table is Revenue, Cost, Profit, NPV
Year 1: Is the base case- most likely case given fill in info
Calculations after Year 2:
Revenue: Use price growth to calculate: Units * (1.03)
Cost: Use two %: cost growth, and sales growth
Total Cost Year = Cost per unit * Sales Units
Total Cost Year = (Year 1 Cost * Cost Growth) * (Year 1 Sales * Sales Growth)
Calculate Profit Subtract Revenue and Cost
Get The Discounted Profit for each year
Total Discounted Profit for NPV
Scenario Analysis: How is the summary on excel shown
Scenario Summary: Shows Current Values (Base Case on LHS): Year 1 Units, Sales Growth Rate, Price Per Unit: 10,000, 10%,10
Then Shows:
Best Case: 20,000, 20%, 10
Most Likely: 10,000, 10%, 7.5%
Worst Case: 5000, 2%, 5
What is Goal Seek Analysis
Goal seek analysis is a form of what-if analysis that tells us what input will be needed or what will need to be done (or assumed) in order to reach a desired outcome
Goal seek analysis is the ability to calculate backwards to understand the input needed to achieve a certain output.
Goal Seek Analysis: Back to the lemonade business example. Let’s assume that unit price is $3.0, unit cost is $0.45, and fixed cost is $15,000. Price = $3.0 Unit cost = $0.45 Fixed cost = $15,000 Revenue= demandprice Variable cost= unit_costdemand Profit= Revenue - $15,000 - variable_cost § How many units does the business need to sell to break even?
On Excel:
Goal Seek Analysis has 3 Components
One is set cell: here to break even we need to set the profit
The other component is To Value: the value is the what we need to set the profit to which is 0 to break even
The third component is by changing cell: This would be demand/sales (desired outcome to break even units given our input of profit)
Manually:
Profit = (Demand×Price)−Fixed Cost−(Demand×Unit Cost)
0 = (Demand * 3) - 15,000 - (Demand * 0.45)
Solve for Demand: 5882 Units
What is Monte Carlo Simulation?
Monte Carlo simulation enables you to model situations that present uncertainty and then play them out on a computer thousands of times based on the presumed distribution or unknown distribution
Simulation allows us to estimate the probabilities of uncertain events. For example, what is the probability that a new product’s cash flows will have a positive net present value?
Many companies use Monte Carlo simulation as an important part of their decision-making process.
GM, P&G, Pfizer use simulation to estimate both the average return and the risk factor of new products to determine which products come to market.
Explain the steps that go into Monte Carlo Simulation: Production Planning on Excel?
Understand the Demand Distribution Table:
The table shows demand values (e.g., 10,000, 20,000, etc.) and the associated probabilities for each demand level.
These probabilities are cumulative; for example:
Demand = 10,000: Probability = 0.10.
Demand = 20,000: Probability = 0.10 + 0.35 = 0.45 (cumulative).
Demand = 40,000: Probability = 0.45 + 0.30 = 0.75 (cumulative).
Demand = 60,000: Probability = 0.75 + 0.25 = 1.00.
Create a Cumulative Distribution Function (CDF):
Add a column for the CDF to accumulate the probabilities from the demand table. The final row should equal 1.00.
Generate Random Demand Values:
Use Excel’s RAND() function to generate random numbers between 0 and 1.
Use the VLOOKUP function to map these random numbers to the demand levels based on the CDF. For example:
If RAND() = 0.20, it maps to Demand = 20,000 because 0.20 falls between the CDF range 0.10 and 0.45.
Simulate Production and Compute Profit:
Set up the following values:
Unit production cost = $1.50.
Unit price = $4.00.
Unit disposal cost = $0.20.
For each simulated production level (e.g., 5,000 to 60,000 units):
Calculate revenue as:
Revenue = min(Production, Demand) * Price
Calculate variable costs as:
Variable Cost=Production × Production Cost.
Calculate disposal costs for excess production as:
Disposal Cost=max(0,Production−Demand)×Disposal Cost per Unit.
Compute profit as:
Profit=Revenue−Variable Cost−Disposal Cost.
Repeat for Multiple Simulations:
Run the simulation 1,000 times for each production level to account for variability in demand.
Use Excel’s Data Table feature to automate this for each production level.
Compute Average Profit for Each Production Level:
Average the profits across all 1,000 simulations for each production level.
Identify the production level with the highest average profit.
Example of a Monte Carlo Simulation: Production Planning Manual Calculation Problem:
A company produces widgets, and the demand for their product is uncertain. The following table shows the demand forecast and the associated probabilities:
Demand (Units)
Probability
5,000
0.20
10,000
0.35
20,000
0.30
30,000
0.15
Each widget costs $2.50 to produce and sells for $6.00. However, if the company produces more widgets than the demand, the excess inventory is disposed of at a cost of $0.50 per unit.
Your job is to decide the optimal production level (5,000, 10,000, 20,000, or 30,000 units) that maximizes the expected profit.
How would you do this manually?
Define important values
Unit Production Cost = 2.50
Unit Price = 6.0
Unit Disposal Cost = 0.50
Create Profit Table Using Formulas: Payoff Table Demand Levels and Production Levels
Revenue = min(Production, Demand) * Price
Variable Cost=Production × Production Cost.
Disposal Cost=max(0,Production−Demand)×Disposal Cost per Unit.
Profit=Revenue−Variable Cost−Disposal Cost.
Calculate Expected Profit for each Production Level (Multiply By Probabilities)
Example: Expected Profit for Production Level = 5,000
Expected Profit= (17,500×0.20)+(17,500×0.35)+(17,500×0.30)+(17,500×0.15)
MC Simulation NPV: Explain how we know
This time were simulating the variable 1000 times like we did before. This time we don’t have growth rates, uncertainty is growth rate
The growth for revenue is the growth for expenses (follows a normal distribution with means and standard deviation)
MC Simulation NPV: Explain the steps on excel to make this run
- Input and Setup
Revenue Growth Rate:
The mean growth rate is 20%, with a standard deviation of 5%.
Use the formula =NORM.INV(RAND(), 20%, 5%) to simulate revenue growth rate values randomly.
Expense Growth Rate:
The mean growth rate is 10%, with a standard deviation of 3%.
Use the formula =NORM.INV(RAND(), 10%, 3%) for expense growth rate values.
Other Inputs:
Current Revenue: $100
Current Expense: $150
Discount Rate: 9% - Simulate Revenue and Expense for Each Year
For each year (e.g., 10 years):
Simulate Growth Rates:
Use the formulas above to generate random growth rates for revenue and expense.
Calculate Revenue:
Formula: Current Revenue × (1 + Revenue Growth Rate)
Update this calculation each year, using the prior year’s revenue as the “current” value.
Calculate Expense:
Formula: Current Expense × (1 + Expense Growth Rate)
Similarly, update this calculation yearly.
Compute Profit:
Formula: Profit = Revenue − Expense. - Compute NPV for Each Simulation
Use the NPV function in Excel:
Formula: =NPV(Discount Rate, Range of Profits).
Example: If profits are in cells C10:L10, and the discount rate is 9%, the formula would be:
=NPV(9%, C10:L10). - Automate with Data Table for Multiple Simulations
To run multiple simulations (e.g., 1,000): hom, fill series, columns 1 -1000
Set Up a Data Table:
Use the RAND() function to generate multiple sets (1000) of random growth rates for revenue and expense.
Calculate NPV for each simulation.
Steps:
Go to Excel’s Data Tab → What-If Analysis → Data Table.
Set “Row Input Cell” to an empty cell (leave this blank for one-way tables).
Set “Column Input Cell” to any reference cell where random variables are linked. (something u won’t run into later, far away cell) - Calculate Average NPV Across Simulations
Compute the average NPV from all 1,000 runs.
Use the formula: =AVERAGE(Range of NPV Results).
MC Simulation BIDDING: Explain Optimal bidding and how to use a simulation to determine the optimal bid for a construction project? How to simulate a binomial random variables? How to simulate a normaal random variable?
How to use a simulation to determine the optimal bid for a construction project?
How to simulate a binomial random variable?
The formula BINOM.INV(n, p, RAND()) simulates the number of success in n independent trials, each of which has a probability of success equal to p; RAND() generates a normal random number.
How to simulate a normal random variable?
The function NORM.INV(RAND(), mu, sigma) generates a simulated value of a normal random variable with a mean mu and a standard deviation sigma.
Explain the steps to use on excel for optimal bidding.
- Define Inputs
Start by entering the static values:
Cost of project (A1): $25,000
Cost of bid preparation (A2): $1,000
Number of bidders (A3): This will be simulated using the BINOM.INV function (explained below).
Your bid (A4): Manually input different bids (e.g., $30,000, $35,000, etc.) to simulate the outcome for each bid level. - Simulate the Number of Competitors
Formula Meaning: BINOM.INV(n, p, RAND())
n = Total number of potential competitors (6 in this case).
p = Probability of a competitor bidding (50%, or 0.5).
RAND() = Random number generator to simulate variability.
Output: Randomly simulates how many competitors are bidding (e.g., 0 to 6). - Simulate Competitor Bids
For each bidder:
Check if the bidder is in:
Formula: IF(Current Cell ≤ Number of bidders, “Yes”, “No”)
Meaning: If the bidder number is less than or equal to the number of simulated competitors, that bidder is bidding; otherwise, they’re not.
Generate a bid if the bidder is “Yes”:
Formula: IF(“Yes”, NORM.INV(RAND(), Mean, Std Dev), $100,000)
Meaning:
If “Yes”: Use a normal distribution (mean = $50,000, std dev = $10,000) to simulate the competitor’s bid.
If “No”: Assign a high bid ($100,000) so they don’t affect the results. - Determine If You Win
Formula: IF(Your Bid ≤ MIN(BID RANGE), “Yes”, “No”)
Meaning: Compare your bid to the lowest competitor bid:
If your bid is the lowest, you win.
If not, you lose. - Calculate Profit
Formula: IF(You Win, My Bid − Project Cost − Cost Bid, −Bid Cost)
Meaning:
If you win:
Profit = (Your Bid) − (Project Cost) − (Bid Preparation Cost).
If you lose:
You lose the Bid Preparation Cost (Profit = −Bid Cost). - Simulate Multiple Scenarios
For each potential bid amount (e.g., $30,000, $35,000, etc.):
Simulate the number of competitors.
Simulate bids for each competitor.
Check if you win for that bid amount.
Calculate profit for that bid amount.
Repeat this process for 1,000 simulations per bid amount. - Compute the Average Profit
After running 1,000 simulations for each bid:
Calculate the average profit for each bid amount.
Identify the bid amount with the highest average profit (e.g., $35,000).
What is the goal of Asset Allocation: Monte Carlo Simulation
How to allocate investment among stocks, T-bills, and bonds?
What asset allocation over a five-year planning horizon will yield an annual expected return of at least 6 percent and minimize risk?
The underlying distribution of the returns for stocks, T-bills, and bonds are not known!
However, we have historical return data from 1973 to 2009.
