Module 5: Scenario Analysis and What-ifs
Introduction
Scenario analysis is a key component in smart managerial decision making. Excel provides a powerful tool called DataTable that makes scenario analysis easy to set up, highly efficient, and easy to interpret.
Please use the comment section below to post your questions and thoughts!
One-way DataTable: Mortgage and down payment
Problem:
Mary and Joe are re-financing their mortgage. They still owe $250,000. And they were pondering if they should pay down more principle amount with their cash saving (in particular, paying down $10,000, $20,000, $30,000, $40,000 or $50,000). The mortgage term is a 15-year fixed rate at 4.50%. They want to find out their month payment for each scenario of down payment, as well as the total interest payment.
The effect of changing cut-offs
Problem
A travel guidebook is updating its restaurant ratings in a town, which has 10 restaurants. It has collected numeric data, on a 0-100 scale on food quality, service quality, and dining environment of each of the dining places. Moreover, the weights for each of the dimensions are 40%, 30%, and 30%. Set up a spreadsheet to calculate their weighted scores.
Moreover, in order to get a comprehensive picture, set up a table to count how many of them are above 65, 70, 75, 80, 85, and 90, respectively.
The impact of fee percentages on revenues
Problem:
Tom, an inspiring author, has written a new novel and is using four channels to publish and distribute his book. There are two large online channels (called A and B), an independent self-publishing channel (called C), and a physical store (called D). Each channel has its own fee structure, and each commands different market share. Suppose the novel will sell 1,000 copies, and Tom has decided to price it at $9.99. Calculate the total revenue he gets from each channel.
Tom is also concerned with the following:
(1) He wants to know how his income would change if his top two channels (A and B) were to change their fee percentage.
(2) He is also curious as to how the overall demand will affect his revenue from all four channels.
Build a what-if analysis to answer Tom’s questions.
Two-way DataTable: The joint impact of interest rate and down payment on mortgage
Problem
Mary and Joe are refinancing their mortgage. They still owe $250,000. And they were pondering if they should pay down more principal amount with their cash saving (in particular, paying down $10,000, $20,000, $30,000, $40,000 or $50,000). The mortgage term is a 15-year fixed rate at 4.50%. They want to find out their month payment for each scenario of down payment, as well as the total interest payment.
Suppose Mary and Joe wants to find out if they should wait a bit for a possible interest rate decrease to either 4% or even 3.5%. How much money could they save in terms of monthly payment under each scenarios of both interest rate and down payment?
Two-way DataTable: The effect of adoption rate and incidence rate on drug revenue
Problem
A start-up firm has developed a new drug in treating autism in children aged between 0 -14 in the US. It is estimated that roughly 3% of children in this age group are diagnosed with this condition every year. This drug is especially promising in that most patients can be treated in about six months, assuming they take one pill a day. The firm hope that it can be adopted by 10% of those patients through aggressive marketing efforts. The firm is targeting at $13 per pill. Assume for now existing patients will not use this product.
The startup wants to know how sensitive is the 10% adoption rate on the company’s revenue projection (for example, what about 6%, 8%, or 12%, 14%, or 16%). Moreover, what if the incidence rate changes between 2.5% and 3.5%. Develop a scenario analysis to study their joint effects on projected revenue.
