This post will focus on a few changes to keep in mind as you transition from building three statement models with annual periods to three-statement models with monthly periods. Download Monthly Three-Statement Model.
If you are new to building models, or lack experience with three statement models, we recommend getting started with an annual three-statement model before proceeding with this content (please see the Integrated Financial Statement Model video series for an introduction).
Revenue Growth in a Monthly Financial Model
In a financial model with annual periods, revenue is projected as a change over the prior period. For this reason some analysts moving to monthly projections attempt to project revenue in a similar fashion. But in most cases, especially for established businesses, growth should be projected on a Year-Over-Year (YOY) basis.
A YOY calculation compares each month in the projected period with the same month in the previous year. By way of example, revenue in January of 20X2 should be calculated as (revenue in January 20X1)*(1 + growth rate). This rule is not without exception, but it applies in most cases. If this concept remains unclear, the video that follows provides a simple introduction with visuals.
Projecting Expenses on the Income Statement
For an explanation of the formulas used to project expenses on the income statement please see the link that follows: Cost Structure: Fixed, Variable and Semi-Variable.
Working Capital Accounts: Days Used to Calculate DSO, DIO and DPO
The number of days used to calculate accounts receivable, inventory and accounts payable must match the number of days in the period used. Whereas we previously used 365 days in a model with annual periods, now we need to divide both Revenue and COGS by 30 or the number of days in the month.
For a little more detail on why this is required, let’s revisit the diagram from the Introduction to Financial Statements video series, which gives us a superficial view of how the three financial statements relate to each other.
What’s important to emphasize is that the balance sheet is always the financial position of the company. It represents the business in its entirety at any given moment. The income statement and cash flow statement, however, deal with the passage of time and represent the economic activity of the business over the interval that has elapsed between two balance sheets. For this reason the number of days needs to be adjusted to evaluate these three working capital accounts on the basis of revenue per day and COGS per day.
Working Capital Accounts: Projected on a YOY Basis
Much like revenue growth in a monthly model, the formulas used to calculate working capital balances in the projected period should pull from the same month in the prior year. To smooth out any unusual spikes in working capital, it can also be a good idea to take an average of three months by including the periods before and after the month being projected. This is visible in the projected period of the template available for download.
Accruals: Example Using Bonus Payments
You may recall from the Introduction to Financial Statements video series that on an accrual basis of accounting, revenue is recognized when earned and expenses are recognized when incurred. If an accounting period has a journal entry recording revenue or an expense that does not coincide with a cash transaction in the same period an accrual is required.
The model associated with this post includes several accruals, but the most intuitive is related to bonus compensation. A bonus is earned over the course of the year, but paid in the year that follows. This expense runs through the income statement, and builds on the balance sheet as a liability until it is paid out. For an example please see the three statement model available for download. The bonus calculations can be found under row 75.
Interest Expense vs Interest Paid
Interest expense is a period expense, so it appears in each period on your income statement in a financial model. Per most credit agreements, however, interest is only paid on a quarterly basis. To reconcile this timing difference another accrual must be included on the balance sheet. Please see this link for an explanation: Interest Expense in a Monthly Model.
Growth Rates
Working with monthly data may require converting annual growth rates to monthly growth rates. This is a calculation that is frequently done incorrectly. For a short video explaining the proper approach please see the video below. Alternatively, a dedicated post is available at the following link: Converting an Annual Growth Rate to a Monthly Growth Rate.