We begin by setting up the income statement and balance sheet, populating each with the most recent year (“MRY”) financial data sourced from a 10-K, annual report, or other filing. If the most recent fiscal year ended on December 31, but the most recent quarter ended on June 30, for example, you should show the June 30 data in the first column. That leaves you with a six-month “stub” period from June 30 to the next year-end.
For simplicity and to avoid complications related to timing, we start with MRY in our annual operating model. We build the flexibility to accommodate stub periods and conversion to a quarterly model with the first populated row in the spreadsheet, “Fractional years per period.” In a quarterly model, the values in this row would all be 0.25. If we had a six-month stub period, the first projected period would be 0.50, followed by 1.00 in each subsequent period. Formulas in this operating model that would be affected by a change in timing link to this row.
We have built into our model five years of projections. For a merger model or an LBO model, you may need to project financials a few more years out. That can be done easily by copying the column in the last projected year over a few more columns.
As we walk through this tutorial, we will highlight changes in each step with black cells at the left edge of affected rows. Also, we will set up some cells to be populated before we are ready to actually populate them. Those cells are highlighted pink as a reminder to address them in a subsequent step.