Three-statement financial model
One of our first tasks in building out a financial model, especially a three-statement financial model is to go through and evaluate the financial statements and make sure they're complete. Now what we have here are three different financial statements for a basic model, the income statement, the balance sheet, and the statement of cashflows. We have this on a historical basis for the period 2014 to 2018. And while we filled out most of the model, there are certain pieces that are missing, like Operating Income, Income Before Tax, etc. We'll need to fill these in using formulas. In addition, we have helpful comments added at various points in time that give us a sense for how different figures were arrived at. So let's go through and fill out this model, shall we?
So for Operating Income, we are going to be basing this on our operating expenses versus our revenue. In this particular case, we'll have C22, our Revenue, minus C27, our Unusual Expenses, minus our Total Operating Expenses, C28. Once we've calculated this for the first year, we can apply the same formula across all five years of our model. It's that easy, right? Similarly, when we want to calculate our income before taxes we'll go through and take our Operating Income and then subtract off our Interest Expense. Having done that, we can apply it across all five years. Now to calculate our Net Income, we're going to use our Income After Taxes plus our Extraordinary Items, and this is where you can see where notes are helpful. In this particular case, our Extraordinary Items could have been either negative or positive. Here, we've used a simple positive 100 for Net Income. We can now apply this across all five years. And to determine our normalized earnings per share, we'll take that Net Income divided by and this gives us the earnings per share on a regular basis.
We could go through and apply this same principle to the balance sheet to determine our total assets, which will be based on our current assets plus the sum of our various long-term asset components. Once we've done that, we'll be able to apply this across all five years. We can do the same thing for liabilities, taking our current liabilities and then adding to them all of our components in Long Term Debt, including the recently added Capital Lease Obligations. And then finally, we can go through and determine our Equity based on the components above, which will include Common Stock, Additional Paid-in Capital, Retained Earnings, and the Unrealized Gains we have. Once we've done all that, we may wish to make a couple of cosmetic changes, including adding some borders in Excel, just to make it look a little neater and cleaner, but as you can see, we've been quickly able to build out the rest of our basic three-statement model with minimal trouble, thanks to the power of Excel and the formulas used therein.