Developing a Basic Financial Model - Part VII: Long-Term Liabilities and Equity

This is the final installment in the introductory series of fundamental concepts of financial modeling. It is important that the reader has some familiarity of the three major financial statements (income statement, balance sheet and cash flow statement) that I covered in the prior three articles. If not, please read those first prior to continuing.

Long-Term Liabilities

The most common long-term liabilities are debt and capital lease obligations, deferred taxes, and pension and postretirement benefits. For financial modeling purposes deferred taxes are often left alone or changed according to specific information for forecast horizon. A similar concept may be used for pension and postretirement benefits. Pension accounting rules are fairly complex and rely upon the use of an actuary, and for the average financial modeler, the incremental logic required to correctly forecast changes in those accounts will not necessarily yield a substantially more robust model. Simple assumptions like keeping that category as a percentage of revenue can be used under the logic that people drive revenue and more revenue means additional hires in many cases (and vice versa). The complexities of forecasting deferred taxes often call for this simpler approach. Note that if you have the accounting training you can certain be more precise, but my experience has shown me that unless there is a specific reason for doing so, standard assumptions should drive these categories.

Debt and capital leases, on the other hand, are items that can be modeled correctly. Capital leases have a principal and interest component to them, and Excel has formulas for both (PPMT and IPMT). With the stated interest rate and time period, those calculations are straightforward. For debt that has a fixed rate and specified maturity (like a term loan), those calculations are also basic. If it is a security like a mortgage, the same PPMT and IMPT calculations could be done. What is more common, however, is to look at the balances of the debt at a given period and apply the interest rate to determine the amount of interest shown on the income statement. If there are mandated principal payments, those would show up on the cash flow statement, and you would use them to reduce the balance of debt shown on the balance sheet.

A common form of debt is the revolver or senior line of credit. Many companies turn to this form of debt to finance working capital or seasonal swings in the business. Normally, as long as a company has sufficient accounts receivable or inventory, management can draw down on this line of credit, pay it down when there is excess cash or do nothing until it matures. From a modeling perspective, the revolver is important in managing minimum cash balances, maintaining working capital levels or covering any one-time purchases. This is basically overdraft protection for companies. Your model should be flexible enough to determine what level of balance sheet cash is necessary, reduce the revolver to the extent there is sufficient cash flow, or even repay other forms of debt if necessary. This type of modeling is best done under Excel's iteration option under Tools, Options, Calculation. The rationale for this will become more evident as you do the models, but in short, the iteration provides the real-life dynamic interpretation of how a company will perform versus static sets of assumptions. Required retirement of debt, interest calculations and cash flow sweeps (always utilizing the excess cash flow generated to paydown debt) are all part of this component of the model. All increases and decreases in debt totals will flow through the cash flow statement in the financing section.

Equity

In financial modeling, the equity account provided few problems. Within this account, whether a c corporation, an s corporation, an LLC or any other entity, the equity account represents the value in a firm after all obligations are paid. You may see this listed as stockholders' equity, stakeholders' equity, members' interests or other similarly-named account, but they all mean the same thing. If you are modeling a c corporation, you are likely to have common stock, paid-in capital and retained earnings listed. Occasionally there may also be a preferred stock entry, a treasury stock entry or even a line for net income or distributions (as seen in QuickBooks for many small businesses).

To keep it simple, the major things that affect the equity account are the issuance or retirement of equity, dividends or distributions and net income. When you think about your financial model, know that net income will increase the equity account (specifically retained earnings) and net losses decrease the account. Dividends or distributions also decrease the account because cash is leaving the business. If you issues new equity, the overall equity account grows, and if you repurchase equity, the opposite holds true. Any dividends or equity issuances are captured on the cash flow statement in the financing section, and you will get the net income number on the income statement or cash flow statement under operations.

This concludes the introduction to financial modeling series, as future articles will get back to the focus of specific Excel formulas and delve more into intermediate and advanced financial modeling techniques. For now, with these few articles as a backdrop, you should begin to feel a bit more comfortable in developing a basic financial model. At the end of the day, the only way you will begin to be a competent financial modeler is to start modeling. Books, articles and other guides are helpful in addressing specific points, but nothing will be as helpful in learning to develop financial models as sitting down in front of your computer and doing them.