In this video, the instructor performs scenario analysis and stress testing of results.
- [Instructor] Now that we've built our hedonic pricing model, we want to go through and we want to stress test it to determine how different changes in our business practices will impact our sales. In particular, Dianne is interested in looking at how a change in price, and by extension a change in the net promoter score, will impact sales at the firm. I'm in the 04_03 folder looking at the begin financial data file.
Now as you can see, we've built a model which shows us that given these particular inputs, our expected output for sales is $751.53 million. So I'm going to create a little chart down here that shows us what our sales are under a variety of scenarios. So our base case is 751.5 million. Now, Dianne is recommending that the firm lower their price.
Doing that, she believes, will raise the net promoter score from 0.001 to 0.04. As we can see with the bolded number at the top, doing that results in the firm's sales rising to $816.8 million. If we lower the price, that's the expected impact on sales. Now we might ask, well that sounds great, but how sure are we that that's the correct number? Well to answer that, we'd want to return to our 95% confidence interval.
Perhaps this coefficient shown here is not correct. Instead, we might turn around and look at the 95% lower or 95% upper confidence intervals and this will give us an upper and lower bound on sales. So I'm going to create a row here for upper bound and lower bound on sales. And now to determine that, I'm going to look at coefficient for NPS on the 95% lower confidence interval and paste that in there and we see that our lower confidence interval, the lower bound, is 808.34 million in sales, rounded.
Now, putting in our upper bound, we see that our new sales level is 825.3 million. Now, based on our model, we have a base case if we lower sales of 816.8 million and we can be confident, in fact we can be 95% confident that the expected sales, given this one change, will be anywhere from 808.34 million to 825.3 million.
There's a little bit of variation in this range, of course, but as we see, lowering prices increase sales significantly. Whether we want to make that decision, whether we want to lower the price or not, is something that might be determined by Dianne's boss, but now Dianne can give that individual the information they need to make an informed decision. We have point estimates that tell us the upper and lower bound for sales based on lowering this price as well as our base case and moreover, we have a forecast for what sales will be if we do not change our price, in this case 751.5 million.
As you can hopefully see, this type of analysis is a very powerful tool that we can use to assess changes across many aspects of a business from R&D to pricing to taking on additional investments in the firm itself.
Join Professor Michael McDonald and discover how to use predictive analytics to forecast key performance indicators of interest, such as quarterly sales, projected cash flow, or even optimized product pricing. All you need is Microsoft Excel. Michael uses the built-in formulas, functions, and calculations to perform regression analysis, calculate confidence intervals, and stress test your results. You'll walk away from the course able to immediately begin creating forecasts for your own business needs.
- Understanding big data and predictive analytics
- Gathering financial data
- Cleaning up your data
- Calculating key financial metrics
- Using regression analysis for business-specific forecasts
- Performing scenario analysis
- Calculating confidence intervals
- Stress testing