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.

Developing a Basic Financial Model - Part VI: Long-Term Assets

In continuing in our series of fundamental concepts of financial modeling, and after taking a quick break to discuss the cash conversion cycle, I will now turn to another initial step of understanding how to forecast financial information. 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 Assets

The most common long-term asset for many industrial or manufacturing companies is property, plant and equipment ("PP&E"), also referred to in certain cases as fixtures, furniture, fixtures and equipment. PP&E is a category on the balance sheet that typically captures large pieces of equipment used to generate products. For example, a car manufacture would include all of the assembly line equipment like conveyor belts, robotic arms, power drills and lifts, etc., in this category. Also computers, desks, chairs, leasehold improvements, land and buildings would be included in PP&E. In most financial statements, a company lists both gross PP&E and net PP&E. The gross amount is the actual totally dollar amount a company paid for all of its equipment and the net amount represents the book value of those same items after depreciation is included.

What is depreciation? Depreciation is a means to try to establish useful lives for various assets based on both accounting standards and the tax code, which have different approaches. For example, a computer may have a five-year asset life for both accounting and tax purposes, but a company car might be depreciated over 10 years for accounting and five years for tax purposes. It is not uncommon to have assets classes with disparate timeframes between GAAP and tax methods. The Financial Accounting Standards Board regulates GAAP, which constitutes the rules for accounting methods and the IRS is the regulatory agency behind the tax code. These two entities have different rules for governing depreciation methods and a general understanding of the differences is important prior to developing a financial model. Further, some analyses may get into very complex tax code understanding, so if your project calls for a deep dive into the tax impact of decisions, you should have a resource to address those questions. In many instances of simple financial modeling, however, the book method and the tax method are left the same and much of the aforementioned differences become moot.

To keep everything simple, financial modelers will take the entire net PP&E amount and use what is called "straight line" depreciation, or subtract the same depreciation amount from PP&E each year. For example, if the beginning total was $100,000,000 and accounting rules dictate that the assets are depreciable over a 20-year period, the depreciation would be $5,000,000 per year, if there is no "residual value" (residual value, or salvage value, refers to the amount one thinks an asset would be worth at the end of the useful life to a company and this value does not exist for tax purposes). If there is a residual value of $20,000,000, you would depreciate $80,000,000 over a 20-year period, or $4,000,000 depreciation expense per year. From a modeling perspective, it is easy to do an explicit depreciation calculation based on the accounting timeframes.

Companies build up the PP&E category through capital expenditures ("CapEx"). CapEx can either be improvements to existing equipment or new equipment purchases. To determine the amount of forecasted CapEx, there are two methods: explicit time horizon or ratio. Under the explicit time horizon, the financial modeler would have specific information on the spending needs of a company. For example, assume that the management team must spend $30 million equally over the next three years to upgrade existing equipment. In this case, you know that $10 million per year will be spent. If you do not know the exact amount, you would use a ratio to determine total CapEx, like a percentage of revenue. Let's assume that over the past few years a company has spend 5% of total sales in CapEx. Barring some specific news of the future, you might assume that the 5% ratio would hold for the foreseeable future. Another way some financial modelers will forecast CapEx, particularly for a company in the mature stage of business, is to have CapEx equal depreciation. This way, the net PP&E will stay the same over the forecasted horizon. Whichever method you choose to use should just make sense from an historical performance analysis as well as incorporating future expectations.

Another common long-term asset is goodwill, which is an intangible asset. Goodwill arises when one company buys another company for a value that is in excess of the net asset value. This "extra" value is, presumably, related to the positive intangible aspects of running a successful business, and the amount is placed on the balance sheet at goodwill. The current accounting rules for goodwill dictate that the total amount be evaluated periodically for potential decreases in value. If there is a determination that the goodwill account is higher than it should be, the goodwill is considered impaired and a write-down is required. For financial models with a short forecast horizon (three to five years), the goodwill is rarely adjusted. Other intangible assets include patents, trademarks, copyrights, etc. and there are specific time periods by which these categories are amortized (amortization is depreciation but for intangible assets). Patents are generally amortized over their legal life, trademarks, while technically indefinite, are amortized over their useful lives, and copyrights are amortized over a time period reflective of the costs to obtain such copyright. From a financial modeling perspective, there accounts are very straightforward and require little to no adjustment over the forecast horizon.

There are other long-term assets, like deferred taxes, long-term investments and various prepaid rights. The category most spend time getting right, however, is PP&E. It is of paramount importance that you have a basic understanding of depreciation methodologies and CapEx rationale in order to correctly forecast PP&E. The vast majority of the other long-term assets are much easier to model and once the PP&E calculations are conquered, the rest of the long-term assets will seem like child's play.

Developing a Basic Financial Model – Part V: More on the Cash Conversion Cycle

In continuing in our series of fundamental concepts of financial modeling, I would like to spend a bit more drilling down into some detail on the cash conversion cycle, or cash cycle ("CC") prior to heading on to long-term assets and long-term liabilities. This relatively straightforward concept can provide some insights into determining whether or not a business has become more efficient or is headed towards insolvency. This has impact on a financial modeler because historical CC values should not differ substantially from the forecast unless the financial analyst has specific, relevant information to suggest that there will be a shift in the operations of a business in the future.

As a quick refresher, the CC is equal to days receivables outstanding PLUS days inventory outstanding MINUS days payables outstanding, or DSO + DIO - DPO. This determines how quickly a company receives cash when it sells a product from inventory. Depending on the industry, the cycle can be very quick or very slow. For example, assume that you are a manufacturer of large, custom made home furniture, like armoires. A typical process would be a customer placing an order on day one, and at that time, you need to begin the process of making the furniture.

The first step would be to call a supplier of raw materials and begin making the product. Assume that it takes 60 days from the time of the order from a customer to complete the work. Further assume that your supplier, while a close relationship, only extends terms to 30 days. This means that you have to finance the payment to the supplier, or the payable, until you receive cash from the customer. If you do not offer terms and 100% of the balance is due at the time of completion, then you are only financing for 30 days. If you operation is on a commercial scale in that you manufacture custom furniture for office spaces or industrial facilities, you would probably offer some sort of terms to your customers. This will increase the time you need to finance the payables. If it is 30 days, then the CC = 30 + 60 - 30, or 60 days until you receive the cash. This means that working capital is "tied up" for 60 days, or said another way, you are financing the time period of 60 days until you receive the cash for your work.

In the prior example, a company might have enough cash on hand to finance the 60-day process, but if it doesn't, another form of financing is needed, like a bank line or other external financing form. This is not free and the interest charged over the process of the borrowing constitutes part of the total cost to a company for running its business. This is not an insignificant component and needs to be considered when modeling the business operations.

The CC is more often used for manufacturing entities, or what I would call old line businesses - the bricks and mortar operations. What if you have a service business? In this case, there is likely to be little to no inventory, so that component is no longer part of the equation, leaving DSO and DPO. In many instances, say a consulting business, you bill be the hour and provide an invoice on a monthly basis. There are likely to be terms, so you would have a DSO number that is greater than zero. As for DPO, there may not be much involved in that category. To keep it very simple, if there is a consulting firm with four employees working out of a small office, the major expenses you will have are the salaries of those employees. While there may be some payables for things like office equipment or supplies, those are not related to providing a service in the similar fashion as buying materials to make saleable inventory products for a manufacturing business. Therefore, when analyzing a services business, the DSO may be the only component of the CC. In this scenario, there is little value added in understanding the CC.

In summary, I think that understanding the CC for entities who need to invest in inventory and who are more likely to be concerned with managing payables and receivables is important. You have to understand how cash it tied up and the costs associated with managing working capital. While this concept is more relevant to some companies more than others, understanding the process from delivery of goods to receiving cash is a fundamental building block of financial modeling.

Developing a Basic Financial Model - Part IV: Working Capital Historical Relationships

In continuing in our series of fundamental concepts of financial modeling, I will now turn to the initial steps of understanding how to forecast financial information. 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.

Working Capital Historical Relationships

We often see in disclaimer language that "past performance is not indicative of future results" or other types of language similar to that to let the reader know not to put too much stock in the historical outcomes of a particular situation. This is commonly seen in relation to stock price performance or asset manager historical returns. In the case of forecasting financial information of specific companies, this is not necessarily true. In fact, in investment banking or private equity, historical relationships help to drive the expectations of future performance.

For example, let us assume that there is a company in a relatively mature stage of growth, meaning that the future growth is likely to be more along the lines of a bit above the inflation rate for the next several years. The last two years have showed that the days outstanding for accounts receivable ("DSO") was 32 (the days from the point of booking a sales turns to cash). In the absence of specific information related to the company, whether a macro event or industry overhaul, there is likely no reason to assume that there would be a significant change from that.

DSO is calculated by taking the average accounts receivable over a time period, like annual, and dividing the result by the total daily sales value for the most recent period. In other word, if annual, the denominator would be the sales figure divided by 365 and the numerator would be the average of the accounts receivable calculated for the current year and the prior year. If the total accounts receivables for the past two years was $50 and $65, and the total sales for the most recent period was $1,050, the DSO would be approximately 20, or on average, accounts receivable is outstanding 20 days before coming to the company as cash.

The same concept would apply to days payables outstanding ("DPO") or days inventory outstanding ("DIO" – although, a more common concept is inventory turns). DPO is calculated similarly to DSO, except the accounts payable becomes the numerator and the denominator is cost of sales divided by 365. You can easily see the similarities between the DSO and DPO because one is a method to track when cash comes in (DSO) and the other tracks when you have to pay (DPO). These are two components of the cash cycle (also known as the cash conversion cycle).

The cash cycle is a way companies can track how quickly cash comes into the company, and when an analyst is forecasting results for a company, the historical information is very important to understand. In simple terms, the cash cycle follows the purchase of raw materials (often building up payables) to creating a saleable product to the collection of receivables. The cash cycle is = days inventory + days receivable – days payable or DIO + DSO – DPO. The DIO is calculated similarly to DPO in using the cost of sales as the denominator with the average inventory as the numerator (the inventory turns is calculated by taking the total costs of sales as the numerator and the average inventory as the denominator and the result yields how often a company is "turning" its inventory, or selling its products).

To continue with our prior example of DSO = 20 days, if we assume that average inventories was $28, average accounts payable was $37 and the cost of sales in the most recent period was $750, then DIO = 14 and DPO = 18. This translates to a cash conversion cycle of 16 days (20 + 14 – 18). This means that a company is getting cash every 16 days from the current operations. What this really tells you is that the time period of 16 days is what a company must finance prior to getting cash. As a slight tangent, there are instances, although rare, where companies have been able to "self-finance," or not rely on a credit facility or other source of funding to finance its working capital. If you change the numbers above and have DSO or 11, DIO of 10 and DPO of 22, you can see that the cash cycle is approximately 0 (rounded to -1). This is an instance where an entity is developing inventory to sell faster and receiving cash from accounts receivable faster than that company is paying to its vendors. That is a great position, but it is not a common situation.

Other current assets include prepaid assets, which many financial analysts will see as a percentage of total sales. Other current liabilities include accrued expenses or other accrued liabilities, which financial analysts may view as a percentage of total cost of sales. For example, if prepaid expenses at the end of the year have been on average 3% of total sales, in the absence of specific information, any deviation from that going forward is probably not warranted. The similar logic holds for current liabilities.

I want to make two quick points before ending this section. The first is that for companies who are new and rapidly growing, it is acceptable to look at all working capital relationships as a percentage of total sales. In many instances, if you attempt to forecast receivables, inventory or payables using historical information for a company only in business for one or two years, you will get misleading data. To better understand this, think of a new company who will be building up inventory ahead of a marketing launch and who may be offering better receivables terms to purchasers in order to generate sales. Additionally, a new company is likely to have more stringent terms on payables so you will be double penalized from a cash cycle perspective. As a company goes from upstart to established entities, the various ratios will reach equilibrium and then using the DIO, DPO or DSO analyses is more prudent.

The second point is that some companies will include short-term borrowings or current portion of short-term debt in the current liabilities section. For working capital analyses, these should not be included. The only counter-argument to this is that is there is a specific working capital line that is associated with financing working capital (not just based on the balances but actually used to finance working capital) some would choose to include that in certain working capital calculations. In my experience in financial forecasting, I have separated that component of debt and I do not view it as a current liability but as part of a company's capital structure (i.e., long-term financing and not a current liability like payables or accrued expenses). But, as long as the methodology is clearly defined, the financial modeler can choose either path, as the results should not differ.

The next article will cover long-term assets and long-term liabilities and how a financial modeler can use some historical perspective to forecasting these items.

Developing a Basic Financial Model – Part III: The Cash Flow Statement

Excel is probably the most popular spreadsheet in use today, and certainly a mainstay of investment banks, private equity firms and hedge funds. It offers a tremendous amount of flexibility to develop a wide array of financial computations, ranging from simple, static calculations to complex, dynamic analyses. In order to effectively develop financial models for use in valuation analyses or forecasting, it is important to understand how companies show their information. This article continues the overview of the link between the basic components of a full financial spreadsheet by discussing the last of the three main components: the cash flow statement. Because these financial statements are based on accounting rules, there will be some accounting theory used in this article but only very high level, basic elements to allow the reader to follow along.

The Cash Flow Statement

The cash flow statement (or statement of cash flows) provides an accounting (in the literal and financial sense) of how a company generates cash. Since generally accepted accounting principals (also known as "GAAP") are based on accruing revenues and expenses, understanding how a company earned the cash recorded on its books at the end of a reporting period would be very difficult. The following numerical example will shed some light on this issue.

For the sake of simplicity, let us assume that the only components on a company's balance sheet at December 31, 2007 is cash of $100, accounts receivable of $200, accounts payable of $100, and equity of $200. At the end of December 31, 2008, the company shows accounts receivable of $350, accounts payable of $150 and equity remained $200. What would the cash balance be? First, you look at the change in accounts receivable, and if that balance increases, that is a use of cash (and vice versa for a decrease in the balance). So, given the information above, it is clear that there was a use of cash of $150, meaning that the cash from the balance from the year prior would be decreased by that amount. Why does this happen?

GAAP requires companies to record sales of products or services but the company will usually offer terms, say 30 days for the purchaser to pay for those products or services. During this period, a company does not have the cash from the sales and will not get the cash until the purchasers pay. During this period, the company is effectively lending money to the purchaser, or tying up the company cash. This is why some companies will get bank lines or other credit facilities to finance receivables so the cash in the business does not get used. In short, building receivables (or other assets, like inventories) uses cash.

A similar process occurs for payables, except in an opposite process. The accounts payable have increased by $50, so that increases the cash amount. Think of this as deferring a payment due today until some time in the future, and in keeping with the financing discussion above, a third party is providing financing for you, and thus, this becomes a source of cash. In this example, the $150 increase in accounts receivable offset against the $50 increase in accounts payable nets to a cash use of $100. With equity remaining the same, cash from the prior period would be reduced by $100. In short, cash balance would be zero at December 31, 2008.

The cash flow statement will include all changes in assets and liabilities, including the aforementioned receivables and payables. There will also be expenditures for building up the physical property of a company, changes in bank borrowings and changes in the shareholder equity account (like dividends paid or issuance of new stock). Just like its name, the cash flow statement provides a way to track how cash is generated for a business by "unwinding" the accrual methods of accounting. In conjunction with the income statement and balance sheet, the cash flow statement provides a way to analyze the operations of any company and show how business generate or lose cash.

This is the final part of the basic understanding of financial statements. It is now time to talk a bit more in detail about setting up these statements to do financial modeling. The next several articles will cover a series of steps to walk through building a financial forecast and how to use the historical data to provide guidance to projected information.