Tabulating Annual Sums in Monthly or Quarterly Models

The models and videos on this page address the painful issue of presenting monthly or quarterly data in different colums and then presenting annual totals after each year.  When adding up totals in a model you should not change the columm formulas. You can use INDEX and MATCH to make the summation of annuals a pretty smooth process.

 

Timing in Corporate Models and Subtotals that Accumulate Monthly or Quarterly Data

I did not really know where to put the model that illustrates how to take monthly data and then make it into quarterly, semi-annual or annual data. The thing not to do is to manually put in sub-totals with the sum function. If you do this your model will be clumsy, in-flexible and inaccurate. Instead, you can include separate sheets that summarise the same information as in your detailed analysis, but include flexible sub-totals. You need to establish a counter with the MOD function after starting with a basic period counter. Then use the SUMIF function to add across months or quarters. Finally, use the MATCH (with zero at the end) and the INDEX to put the sheet with sub-totals together. This model demonstrates the importance of setting-up time lines in corporate finance models. The time lines are essential for history versus forecast as well as the flexible sums.