• 103 05/05/2020

    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. Consequently, in a monthly financial model you will have periods with interest expense on the income statement without a corresponding cash outflow for interest paid. (Template available for download at the bottom of this post.)

    To reconcile this timing difference, add a line item titled “Accrued Interest” to you balance sheet under current liabilities. This will pull from the supporting debt schedule, which will be amended as follows. First, add three line items just below the formula for Ending Principal Balance:

    1. Interest Expense
    2. Interest Accrued
    3. Interest Paid

    Interest Expense in a Monthly Financial Model

    Next, calculate interest expense for each period (see image). This will be similar to the approach used to project interest expense in a model with annual periods with two exceptions:

    1. There is no need to take an average of the principal balance.
    2. The interest rate needs to be adjusted to reflect the period of time.

    Interest Expense in a Monthly Financial Model

    Then calculate interest accrued on the line that follows. This is the line item that will link to the balance sheet to project accrued interest. To complete this calculation sum interest expense in the current period with interest accrued in the previous period, and then subtract interest paid in the current period (see image).

    Interest Expense in a Monthly Financial Model

    Finally, in the last step related to the debt supporting schedule, we will calculate interest paid using the =IF() and =MOD() functions. The =MOD() function is a great way to confirm if the month is divisible by three, which makes it easy to identify a quarter (=MOD() video explanation). When the formula identifies a quarter end period it will return the sum of the previous three months of interest expense (see image). 

    Interest Expense in a Monthly Financial Model

    Interest expense will link to the income statement in precisely the same way it does in an annual model, and now the Interest Accrued line item can link to Accrued Interest on the Balance sheet. 

    Interest Expense in a Monthly Financial Model

    The final step is to add a line item to the cash flow statement under changes in working capital. Title it “Accrued Interest” and subtract the current period from the prior period to reflect a cash outflow when the current balance declines from one period to the next. 

    Interest Expense in a Monthly Financial Model

    You may notice that the cash outflow on the cash flow statement is equivalent to -$25,000 in period 3/31/2021, which does not match interest paid of $37,500 on the debt schedule. This is due to the fact that $12,500 of interest expense is included in net income, the first line on the cash flow statement. 

    Download: Link to Monthly Three Statement Model Template

    Note: FASB requires that this sum be included in cash flow from operations. It should never be included under cash flow from financing activities. 

 



  • 102 05/04/2020

    The most common error I see in financial models as it relates to growth rates is to divide an annual growth rate by 12 to arrive at the monthly growth rate. In this post we will explore the correct way to convert growth rates for all periods.

    There is also an Excel template available for download that will make this calculation for you so that you can check your math (the calculator will apply the conversion to annual, quarterly and monthly periods).

    The correct approach is to apply exponents, and we can explain why this is the correct approach with a simple explanation. When a value grows by 5% from one period to the next you multiply the value by 1.05. If this were to occur for 12 consecutive periods the multiplication pattern would repeat.

    The simplest way to explain this is to solve for the value that when multiplied by itself 12 times returns (1 + the Annual Growth Rate). So for an annual growth rate of 5% we would take the approach that follows.

    Convert Annual Growth Rate to Monthly Growth Rate

    And since we are solving for (1 + Growth Rate), we subtract 1 from the outcome:

    Convert Annual Growth Rate to Monthly Growth Rate

    Formulas for Each Period Follow:

    Annual To Monthly: (1 + Growth Rate)^(1/12)-1

    Annual to Quarterly: (1 + Growth Rate)^(1/4)-1

    Quarterly to Monthly: (1 + Growth Rate)^(1/3)-1

    Quarterly to Annual: (1 + Growth Rate)^(4)-1

    Monthly to Quarterly: (1 + Growth Rate)^(3)-1

    Monthly to Annual: (1 + Growth Rate)^(12)-1

     

    Convert Annual Growth Rate to Monthly Growth Rate

     

    For more information on working with monthly periods please click on this LINK (or the image below).

     

    Monthly Three Statement Model

 



  • 101 04/27/2020

    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 (work in progress - updates to come).

    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 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.

    Three Financial Statements Linked Balance Sheet Income Statement Cash Flow Statement

    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 calcuation 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.

 




 



Models are:
 
A) really boring
B) pretty sweet
C) super important
D) somewhat easy
E) kind of hard
F) fun
G) all of the above

 

 


*Answers a, b, c, d, e, f and g are all correct.