Putting it all together

Once we've made a set of forward-looking assumptions about how we think the firm's revenue, cost and assets and liabilities will change over time, we're prepared to then begin making projections about the firm's financial condition on a forward-looking basis. I'm in the 02 07 Begin Excel file. Now what we have here are the projections we made around revenue, growth around various firm metrics, etcetera. What we want to do is go through and build out the remainder of the income statement. We can use the same process to build out the remainder of our balance sheet and cash flow statement. 

So, how do we get started. Well begin with we're simply going to take our assumed revenue growth for 2019 and then multiply that by the previous year's revenue. We can then extend this out over a five year time horizon. Next, we'll want to determine our cost of good sold based on our assumption about COGGS as a percentage of revenue multiplied by the revenue. And we can extend this out over five years time. We can do the same thing with SG&A, our selling and general administrative expenses. Now with other smaller items like R&D, we haven't necessarily made a direct assumption about this. Fortunately in this case the firm's R&D appears to be reasonably stable but it is growing over time. We might either assume a constant growth rate using a CAGR, compound annual growth rate, or in this case we're using an average based in the last few years. We can do the same thing with any sort of unusual expenses, particularly when you start getting to one off items like unusual expenses, these can be very hard to project. After all, they are by definition unusual. Once we've done all that, we should be able to go through and apply our same Excel formulas to determine our total operating expense and our operating income. We could apply the same concept to determine our interest expense based on the historicals and then the earnings before tax. Next, we'll need to go through and determine what we expect our income taxes to be. This will be based on the income that we earned before tax and the tax rate that we assume over time. Now the reality is, items like taxes in the real world should be fairly constant over time. Sometimes tax rates go up and down because of legislative action or you might have certain tax breaks that we can take advantage of in particular years but this should be a reasonably stable figure and one of the easier ones to project. Once we've made those assumptions, we can easily go through and extrapolate what our income taxes will be and then based on that determine our income after taxes using our same formula, of income before taxes less of course the taxes themselves. We'll also have extraordinary items which we're just computing based on simple averages over time and then the net income which we determine as a formula. Next, I'm going to assume that our weighted average shares for the firm won't change. This is probably wrong, but let's face it. Almost all of our assumptions are going to be wrong in one way or another. The key is to make our assumptions as correct as possible. Now we could go through and try to tie the weighted average shares over time to any repurchases. But the problem is that we still need to know what the expected future stock price is, and that's very difficult to project. More over I'm going to go through and determine our normalized EPS based on our net income and the weighted average shares. We would follow a similar procedure to build out the balance sheet and the statement of cash flows. At this point though, you should have a good handle on going through and putting together basic financial item projections over time. The key here is to make sure that you're being careful about what your assumptions are. Are there any things that stand out to you amongst these assumptions as being a little unusual? One thing that stands out to me, is just looking at our interest as a percentage of long-term debt. Doesn't look very realistic to have that growing at that kind of rate, right? So instead, we might want to go through and adjust that over time and the nice thing about Excel, is this will dynamically update across the entire three statement financial model. Now you're all set to make basic financial projections over time for the firm on your own.