Sensitivity analysis and financial models

If we've built our financial model correctly, when we get done, the whole model should be tied together so that any change to the model dynamically updates across the entire spreadsheet. This is very useful when dealing with practical problems like the need to account for different kinds of scenarios that may arise in corporate finance. 

Let me show you what I'm referring to. I'm in the 03_02_Begin Excel spreadsheet. Now what we have here are the revenue projections and other projections that we've made over time, and they're tied to our revenues, our net income, our earnings per share, and of course the various balance sheet and cash flow statement items. Now, let's pretend that the firm is concerned about a recession going forward. Well, we've assumed for 2019 that we'll have revenue growth of 6.3%. But there's nothing magical about that assumption. Indeed, it's not even a very sophisticated assumption. It's simply based on an average of revenue growth over the last four years. It's much more realistic to be using specific forecasts for that year, so if we're projecting a minor recession towards the end of 2019 and then a deepening recession that might hit revenues by, say, 9% in 2020 before starting to taper off, what's this going to do to the firm? Well, let's take a look. We've gone from having positive revenue growth over time to relatively negative revenue growth. How's this impact our earnings per share? Well, we go from $3.41 in earnings per share down to $2.84 in 2020. Moreover, the feedback effects over time impact the firm, right? We see that the firm's growth rate is much weaker than it was before. So in this context, we can go through and we can use single changes about what might happen in the economy such as economic projections to directly determine what we think our forecast earnings will be. Let's pretend that we're betting on a strong rebound after that recession. We've going to have a 6% upgrowth in that year and maybe an additional seven the following year. What's that do to our earnings per share? Well now, they leap forward towards the end of the period, finishing up $3.35 per share. Hopefully at this point, you have a better idea how we can go through and adjust our specific assumptions based on individual projections and see how that dynamically flows through the rest of our model.