| This is the final installment in the introductory | | | | model should be flexible enough to determine |
| series of fundamental concepts of financial | | | | what level of balance sheet cash is necessary, |
| modeling. It is important that the reader has some | | | | reduce the revolver to the extent there is |
| familiarity of the three major financial statements | | | | sufficient cash flow, or even repay other forms |
| (income statement, balance sheet and cash flow | | | | of debt if necessary. This type of modeling is |
| statement) that I covered in the prior three | | | | best done under Excel's iteration option under |
| articles. If not, please read those first prior to | | | | Tools, Options, Calculation. The rationale for this |
| continuing. | | | | will become more evident as you do the models, |
| Long-Term Liabilities | | | | but in short, the iteration provides the real-life |
| The most common long-term liabilities are debt | | | | dynamic interpretation of how a company will |
| and capital lease obligations, deferred taxes, and | | | | perform versus static sets of assumptions. |
| pension and postretirement benefits. For financial | | | | Required retirement of debt, interest calculations |
| modeling purposes deferred taxes are often left | | | | and cash flow sweeps (always utilizing the excess |
| alone or changed according to specific information | | | | cash flow generated to paydown debt) are all |
| for forecast horizon. A similar concept may be | | | | part of this component of the model. All increases |
| used for pension and postretirement benefits. | | | | and decreases in debt totals will flow through the |
| Pension accounting rules are fairly complex and | | | | cash flow statement in the financing section. |
| rely upon the use of an actuary, and for the | | | | Equity |
| average financial modeler, the incremental logic | | | | In financial modeling, the equity account provided |
| required to correctly forecast changes in those | | | | few problems. Within this account, whether a c |
| accounts will not necessarily yield a substantially | | | | corporation, an s corporation, an LLC or any other |
| more robust model. Simple assumptions like | | | | entity, the equity account represents the value in |
| keeping that category as a percentage of | | | | a firm after all obligations are paid. You may see |
| revenue can be used under the logic that people | | | | this listed as stockholders' equity, stakeholders' |
| drive revenue and more revenue means additional | | | | equity, members' interests or other |
| hires in many cases (and vice versa). The | | | | similarly-named account, but they all mean the |
| complexities of forecasting deferred taxes often | | | | same thing. If you are modeling a c corporation, |
| call for this simpler approach. Note that if you | | | | you are likely to have common stock, paid-in |
| have the accounting training you can certain be | | | | capital and retained earnings listed. Occasionally |
| more precise, but my experience has shown me | | | | there may also be a preferred stock entry, a |
| that unless there is a specific reason for doing so, | | | | treasury stock entry or even a line for net |
| standard assumptions should drive these | | | | income or distributions (as seen in QuickBooks for |
| categories. | | | | many small businesses). |
| Debt and capital leases, on the other hand, are | | | | To keep it simple, the major things that affect |
| items that can be modeled correctly. Capital | | | | the equity account are the issuance or retirement |
| leases have a principal and interest component to | | | | of equity, dividends or distributions and net |
| them, and Excel has formulas for both (PPMT and | | | | income. When you think about your financial |
| IPMT). With the stated interest rate and time | | | | model, know that net income will increase the |
| period, those calculations are straightforward. For | | | | equity account (specifically retained earnings) and |
| debt that has a fixed rate and specified maturity | | | | net losses decrease the account. Dividends or |
| (like a term loan), those calculations are also basic. | | | | distributions also decrease the account because |
| If it is a security like a mortgage, the same PPMT | | | | cash is leaving the business. If you issues new |
| and IMPT calculations could be done. What is more | | | | equity, the overall equity account grows, and if |
| common, however, is to look at the balances of | | | | you repurchase equity, the opposite holds true. |
| the debt at a given period and apply the interest | | | | Any dividends or equity issuances are captured on |
| rate to determine the amount of interest shown | | | | the cash flow statement in the financing section, |
| on the income statement. If there are mandated | | | | and you will get the net income number on the |
| principal payments, those would show up on the | | | | income statement or cash flow statement under |
| cash flow statement, and you would use them to | | | | operations. |
| reduce the balance of debt shown on the balance | | | | This concludes the introduction to financial |
| sheet. | | | | modeling series, as future articles will get back to |
| A common form of debt is the revolver or senior | | | | the focus of specific Excel formulas and delve |
| line of credit. Many companies turn to this form of | | | | more into intermediate and advanced financial |
| debt to finance working capital or seasonal swings | | | | modeling techniques. For now, with these few |
| in the business. Normally, as long as a company | | | | articles as a backdrop, you should begin to feel a |
| has sufficient accounts receivable or inventory, | | | | bit more comfortable in developing a basic financial |
| management can draw down on this line of credit, | | | | model. At the end of the day, the only way you |
| pay it down when there is excess cash or do | | | | will begin to be a competent financial modeler is to |
| nothing until it matures. From a modeling | | | | start modeling. Books, articles and other guides |
| perspective, the revolver is important in managing | | | | are helpful in addressing specific points, but nothing |
| minimum cash balances, maintaining working capital | | | | will be as helpful in learning to develop financial |
| levels or covering any one-time purchases. This is | | | | models as sitting down in front of your computer |
| basically overdraft protection for companies. Your | | | | and doing them. |