CHAPTER 8
Reconciling Cash Flow
Most of the core calculations that generate cash flow in from assets and those that set up our liabilities and equity are done. However, there are numerous calculations necessary to understand more about the company’s performance and financial health. Primarily, we should trace cash that is flowing through the company to see how much is earned or used through operations, investing, and financing. In particular, when assessing the viability of a company we should pay particular attention to operational cash flow, which is partially comprised of working capital needs or excess. Once we know more about the sources and uses of cash, we should also take time to implement internal validations that make sure the calculations backing these cash flow figures are absolutely correct.
THE CASH FLOW STATEMENT
The last major financial statement that we have yet to discuss or implement is the cash flow statement. As alluded to in the opening paragraph, this statement organizes the cash flows of a company by operational, investing, and financing activity. The cash flow statement provides an analyst with a picture of the sources and uses of cash in a company. It is relatively simple to set up since nearly all of the calculations are references to sections already calculated in the model. No new conceptual calculations are necessary. Figure 8.1 shows a graphical representation of the cash flow statement in relation to other parts of the model.
Financial modelers use cash flow statements in two ways. First, if one does not balance the surplus cash in a similar manner as we did on the Debt and Balance Sheet sheets in Chapter 7, he or she will typically use the cash at the end of the period on the cash flow statement to balance the model. Although this is an acceptable and common means of balancing a model, it adds a layer connectivity that is not needed. The cash flow statement is really just referencing other parts of the model, and to balance a model one needs to interact only with the Income Statement, Balance Sheet, and supporting sheets.
If a financial modeler implements a balancing system similar to what we have done in Chapter 7, which is common, then the cash flow statement can be used to reconcile cash. Any change in all of the cash items on the balance sheet between periods should be identical to the change in cash from the cash flow statement. If there is a difference, it means either items are missing from the cash flow statement or other parts of the model are miscalculating. With this check in place, the cash flow statement becomes an invaluable tool for troubleshooting a model.
FIGURE 8.1 The cash flow statement is created from other sheets that have already been created in the model.
WORKING CAPITAL
One goal that we are getting closer to is ultimately corporate valuation. A large part of corporate valuation modeling is understanding the cash flow of a company—particularly cash flow related to operations. Within operational cash flow, an important set of calculations that are often separated are those that allow us to calculate working capital needs.
We should be very clear with semantics at this point:
• Working capital needs: the difference between the current period’s working capital and the prior period’s working capital.
• Working capital: in a single period, the difference between current assets and current liabilities. Typically, cash is excluded from this calculation, unless cash is an integral part of the company’s day-to-day business or the company operates in country where high cash balances (possibly in different currencies) are necessary due to exchange rate and/or inflation problems.
• Current assets: all assets that are liquid within 12 months.
• Current liabilities: all liabilities that are due within 12 months.
Working capital is sometimes confusing because people reference it in two ways: first as working capital needs and second as cash change. What is the difference between these two concepts? Let’s use an example. Imagine that we had the current assets and current liabilities in Figure 8.2.
FIGURE 8.2 Working capital should be assessed by the change in cash; the opposite is thought of as the working capital needs.
We could take the difference between current assets and current liabilities each period and then take the difference between the current and prior period. In this case, we would have a positive number. This positive number represents the working capital that is needed to keep the business operational. Without this cash flow, we would not have been able to fund the purchase of inventory, which is highly critical, nor pay down our other current liabilities, where nonpayment could lead to default.
Another thing to look at is the cash change method. The question a financial analyst needs to ask is: “What is the impact on cash?” This is often a confusing concept for people to get quickly. For instance, when inventory increases from 0 to 300 in our example, the cash impact is -300. This is because for us to create 300 of inventory we need 300 of cash. Conversely, the opposite happens for liabilities. When accounts payable increases from 0 to 200, our cash change is positive because we have received a good or service without laying out any cash.
MODEL BUILDER 8.1: CALCULATING WORKING CAPITAL
1. Working capital is a useful calculation to separate out since it will be used for the cash flow statement, free cash flow, and for the analyst’s judgment of operational performance. Some financial modelers choose to do this calculation within the cash flow statement, but given its importance we will create a separate sheet for the working capital. Insert a sheet after the Debt sheet and name it Working Capital.
2. Enter the text Cash Change Method in cell A2. This is to advise model users that we will be calculating the change in cash as described in the paragraph preceding Model Builder 8.1.
3. Set up the dates and timing for the sheet by entering the following formulas in the corresponding cells:
E2: =Vectors!E9
E3: =Vectors!E10
Copy and paste these formulas over to column Z, within their respective rows. Notice we start with the first projection period since we do not have any information prior to the first historical period. There would be no way to calculate the cash change for the historical year without the year prior to the historical year’s information.
4. We should establish labels for the upcoming working capital calculations. Something new to our model is that this is the first time that negative numbers will appear and remain part of the calculations. It is easier to work with changes in cash by viewing reductions in cash as negative values. Enter the following text in the corresponding cells:
C4: (Inc.)/Dec. in Acct. Rec.
C5: (Inc.)/Dec. in Inventory
C6: (Inc.)/Dec. in Other Current Assets
C7: Inc./(Dec.) in Acct. Pay.
C8: Inc/(Dec.) in ST Debt
C9: Inc./(Dec.) in Other Current Liab.
C10: Net Working Capital Cash Change
5. Our first actual calculations will focus on the current assets. Enter the following formulas in their corresponding cells:
E4: =‘Balance Sheet’!D9-‘Balance Sheet’!E9
E5: =‘Balance Sheet’!D13-‘Balance Sheet’!E13
E6: =‘Balance Sheet’!D14-‘Balance Sheet’!E14
Copy and paste these formulas over to column J while maintaining the formula within its respective row.
6. We should calculate the cash change for the current liabilities. Enter the following formula in cell E7:
=‘Balance Sheet’!E30-‘Balance Sheet’!D30
Copy and paste this formula over the range E7:J9.
FIGURE 8.3 The Working Capital sheet provides a detailed look at cash necessary to keep the core business operational.
7. Sum up the columns for each period to see the net working capital cash change by entering the following formula in cell E10 and copying and pasting it over the range E10:J10:
=SUM(E4:E9)
Figure 8.3 shows what the completed section should look like.
MODEL BUILDER 8.2: BUILDING THE CASH FLOW STATEMENT
1. The cash flow statement is a separate worksheet in the example model. Insert a new worksheet after the Working Capital sheet and name it Cash Flow Statement . On the Cash Flow Statement sheet, enter the text Cash Flow Statement in cell A1.
2. We will need historical information, so we should create the dates starting with the historical period. Enter the following formulas in the corresponding cells:
D2: =Vectors!D9
D3: =Vectors!D10
Copy and paste these formulas over to column Z in their respective rows.
3. The first section that we will complete is cash flow from operations. Enter the following text to create labels in the following corresponding cells:
B5: Operations
B6: Net Income
B7: Depreciation & Amortization
B8: Change in Working Capital
B9: Cash from Operations
4. We start cash flow from operations with net income from the year. We will directly reference the income statement to get this number. Still on the Cash Flow Statement sheet, enter the following reference in cell E6:
=‘Income Statement’!E36
Copy and paste this formula over the range E6:J6.
5. Net income is not the operational cash flow because non-cash items have been removed. In our example model, we have two forms of non-cash items: depreciation and amortization. Real cash flow is not reduced by these items and should therefore be added back to the operational cash flow. Enter the following formula in cell E7:
=‘Income Statement’!E15+‘Income Statement’!E16
Copy and paste this formula over the range E7:J7.
6. Earlier in this chapter we identified working capital as an integral part of operations. If there is a need to fund working capital, then this must be removed from the cash flow. Similarly, if there is an excess amount of cash from working capital, this can add to the cash flow. Enter the following formula in cell E8:
=‘Working Capital’!E10
Copy and paste this formula over the range E8:J8. Notice that since we used the cash change method on the Working Capital sheet we can directly reference that sheet here. Some financial models build the working capital calculation into this section of the cash flow statement.
7. Since we are working with positive values representing cash flow in and negative values representing cash flow out, we can sum the values for each period to get to our total cash flow from operations. Enter the following formula in cell E9 and copy and paste it over the range E9:J9:
=SUM(E6:E8)
Refer to Figure 8.4 for the current development of cash flow from operations section of the cash flow statement.
8. The next section of the cash flow statement focuses on investing activities. Enter the following text in the corresponding cells:
B11: Investing
B12: Disposal of Fixed Assets
B13: Capital Expenditures
B14: Intangible Acquisition
FIGURE 8.4 The cash flow from operations section helps us determine the operational viability of the firm over a forecast period.
B15: Sale of LT Investments
B16: Purchase of LT Investments
B17: Cash from Investing
9. The first entry of the cash flow from investing section, disposal of fixed assets, may be confusing since we have not covered it. It’s mainly here to demonstrate how the model can be scaled up to include more granular detail. At some point one may want to add items or concepts not discussed. One of the best places to start is determining where it is on the cash flow statement and where the cash would come from. In this case, an asset disposal would have cash flow through the income statement, depending on a gain or loss on sale. Since this is a scalable section not covered by the example model, we can put in proxy values of 0 right now for each cell in the range E12:J12.
10. The more obvious investing activities are capital expenditures. Here we can directly reference the Capex sheet. Enter the following formula in cell E13:
=-Capex!E10
Copy and paste this formula over the range E13:J13. Notice that there is a negative sign in front of the formula. This is because capital expenditures are always cash flow out and we used positive values in their calculation on the Capex sheet.
11. Just as intangibles were very similar in calculation to capital expenditures, their setup on the cash flow statement is as well. Enter the following formula in cell E14:
=-Intangibles!E10
Copy and paste this formula over the range E14:J14.
12. The next part of the cash flow from investing section is a bit different from any that we have seen thus far. This part is where we see whether there is cash flow, cash flow out, or both from the sale or purchase of long-term investment. As 
model builders, we have the choice to consolidate the sale or purchase of assets to one row or split it into two. What’s the difference, you might ask? One row is easier to implement since it is just the difference between the two periods. However, two rows give us the flexibility to see whether we had both a sale and a purchase or one or the other. Leaning toward a more detailed approach, enter the following formula in cell E15:
FIGURE 8.5 Certain items benefit from a more detailed look at the differences between periods. Here we can see when a purchase or a sale occurred, not just a net number.
=MAX(‘Balance Sheet’!D25-‘Balance Sheet’!E25,0)
This formula takes the difference between the prior year and the current year’s long-term investments. If the number is positive, it means there was a net sale of assets, but if it is negative, it means there is a net purchase of assets. Since we are focused only on sales in row 15 we use a MAX function as a floor to prevent any negative values from being introduced into this row. Some readers will wonder,
“If we have only one line for long-term investments on the balance sheet, why does it matter to split it into two separate rows?” This is a valid point; the method of handling multiple investments is just being shown for demonstration purposes. Copy and paste this formula over the range E15:J15. Figure 8.5 shows how this section is developed.
13. To do the purchase of long-term investments, enter the following formula in cell E16:
=MIN(‘Balance Sheet’!D25-‘Balance Sheet’!E25,0)
Copy and paste this formula over the range E16:J16. Notice that we use the MIN function to create a cap at zero. Since a purchase of long-term investments is cash flow out, this number should be only negative. Once again, it is merely for demonstration purposes because in our model we have only one line for long-term investments on the balance sheet.
14. We now sum up all of the cash changes for investing with the following formula in cell E17:
=SUM(E12:E16)
Copy and paste this formula over the range E17:J17. Figure 8.6 shows the development of the investing section of the cash flow statement.
FIGURE 8.6 Cash flow from investing is important since the investments may be required to keep the business running in the future. Also, we might want to see whether a lot of funds were derived from the sale of any assets rather than from operational income.
15. The final section we need to work on is cash flow from financing. Enter the following text in the corresponding cells:
B19: Financing
B20: Dividends Paid
B21: Inc. LT Borrowings
B22: Dec. LT Borrowings
B23: Inc. Common Stock
B24: Dec. Common Stock
B25: Cash from Financing
16. Financing brings in cash and costs cash. The first financing item is a cost: dividends paid. Enter the following formula in cell E20:
=-‘Income Statement’!E38
Copy and paste this formula over the range E20:J20. As with capital expenditure and intangibles, notice that a negative sign is put in front of the value since it is cash flow out.
17. Next we will focus on long-term debt. We will take a similar approach to the sale and purchase of long-term investments and create two separate rows for long-term debt: one for increases and the other for decreases. In cell E21, enter the following formula:
=MAX(‘Balance Sheet’!E35-‘Balance Sheet’!D35,0)
Once again, we use the MAX function as a floor to prevent negative numbers, since any increase in long-term debt will be a positive. Also be careful with the order of subtraction. For assets, we subtract the current period from the prior period. For liabilities, we subtract values from the prior period from the current period. Copy and paste this formula over the range E21:J21.
18. Create opposite functionality for decreases by enter the following formula:
=MIN(‘Balance Sheet’!E35-‘Balance Sheet’!D35,0)
Copy and paste this formula over the range E22:J22.
19. The same concepts will be applied for common stock. Enter the following formulas in the corresponding cells:
E23: =MAX(‘Balance Sheet’!E41-‘Balance Sheet’!D41,0)
E24: =MIN(‘Balance Sheet’!E41-‘Balance Sheet’!D41,0)
Copy and paste these values over to column J in their respective rows.
20. Finally, in cell E25, sum up the cash flow changes from financing by entering the following formula:
=SUM(E20:E24)
Copy and paste this formula over the range E25:J25. The final section, cash flow from financing, is shown in Figure 8.7.
21. We can now add each of the cash flow changes from the three cash flow sections: operations, investing, and financing. Enter the following formula in cell E27:
=E9+E17+E25
Copy and paste this formula over the range E27:J27. Also enter the text Net Changes in Cash in cell B27 to label this row.
22. At this point, the cash flow statement is done, allowing us to assess where cash is coming in from or going out to. However, since we balanced our model entirely on other sheets, we may want to see whether we accounted for everything by reconciling the cash flow movements from the cash flow statement with the cash balances on the balance sheet. To create such a comparison, we need to calculate
the cash balance as projected by the cash flow statement and compare it to the total cash each period from the balance sheet. Enter the following text in the corresponding cells to label the rows in this section:
FIGURE 8.7 The financing activities of a firm are very important since the capital structure of a company is a prominent indicator of financial health.
B29: Cash Balance from CF Statement
B30: Cash Balance from Balance Sheet
B31: Difference
23. We need to complete row 30 prior to 29 since there is some historical information necessary. Row 30 is a sum of the cash from the balance sheet. This includes all sources of cash: surplus funds, cash on hand, and marketable securities. Enter the following formula in cell D30:
=‘Balance Sheet’!D6+‘Balance Sheet’!D7+‘Balance Sheet’!D8
Copy and paste this formula over the range D30:J30. This formula adds up each of the cash items. For the historic period, you will notice there is no surplus cash. This is fine since it is just adding a zero and has no effect on the calculation.
24. We will take the historic cash amount and adjust it by the net change in cash from the cash flow statement. We will then take each prior period’s calculated cash using this method and add the net change in cash for each new period. These figures should be identical to those on the balance sheet. In cell E29, enter the following formula:
=MAX(D32+E29,0)
Copy and paste this formula over the range E29:J29. Note that the MAX function is used as a floor to prevent negative cash, which in this model would be calculated as short-term debt.
25. The final calculation is a comparison of the two rows. Enter the following formula in cell E31:
=E29-E30
Copy and paste this formula over the range E31:J31. There should be no difference between the cash change calculated from the cash flow statement and that on the balance sheet. If there is, you should take a logical troubleshooting approach of seeing what value the difference is and trying to find that value within the model. This calculation is particularly useful when new concepts are integrated into the model as it forces model builders to categorize the cash flow in order to reconcile cash. Figure 8.8 shows the cash reconciliation between the balance sheet and the cash flow statement.
FIGURE 8.8 Tracking and assigning all cash flow movements in the model is a useful way to ensure the model is calculating correctly.
PREVENTING ERROR THROUGH INTERNAL VALIDATION
Now that we have all three financial statements and our core calculations complete, we should make sure that our model is working correctly. There can be innumerable tests in a model to validate its performance, but the key ones that we will focus on in the example model include:
• Assets = liabilities + equity: The model must always adhere to the number-one accounting principle; every period, total assets must equal total liabilities plus equity.
• Unpaid debt principal: If scheduled principal goes unpaid, we should be able to identify such a scenario quickly.
• Unpaid debt interest: Similar to principal, if interest goes unpaid, we should be able to identify it quickly.
• ST funds limit breach: In Chapter 7 we mentioned that while we may have a balanced model we should always verify that it reflects reality. Although a company can have unlimited surplus cash, companies have limited access to debt, and the short-term debt plug should be either limited or flagged if it exceeds the commitments that are available to the company.
• Cash check: In this chapter we reconciled cash using the cash flow statement. If there is a difference between the balance sheet cash and the cash flow statement, we should be able to identify this quickly.
MODEL BUILDER 8.3: IMPLEMENTING INTERNAL VALIDATIONS
1. Since the model user will primarily operate the model from the Assumptions sheet, we should set up the internal validations on that sheet. This way, when model users change an assumption, they can quickly see whether the model has a problem. On the Assumptions sheet, in the following cells, enter the corresponding text:
L3: Internal Validation
L4: Assets = Liab + SH Equity
L5: Unpaid Debt Principal
L6: Unpaid Debt Interest
L7: ST Funds Limit Breach
L8: Cash Check
2. Each one of these validations will be a conditional test on the problem at hand. Depending on the conditional test setup, it can produce an “OK” or an “ERROR.” Readers may notice the cell-formatting changes in the example model depending on whether “OK” or “ERROR” appears. This is achieved through a feature called conditional formatting, which is described at the end of this chapter in the Toolbox. Let’s start with the first test by entering the following formula in cell O4:
=IF(ROUND(SUM(‘Balance Sheet’!E26:I26),0)=ROUND (SUM(‘Balance Sheet’!E46:I46),0),“OK”,“ERROR”)
Working through this formula we encounter a new function: ROUND. This function rounds values to a number of decimal places provided by the user in the function. If you are unfamiliar with this function, you should reference the Toolbox at the end of this chapter. Otherwise, if we pick this formula apart we see that the formula sums up all of the periods’ total assets and then rounds them so there are no decimals. The same is done for all of the periods’ total liabilities and equity. An equal sign is used to see whether those two calculated values are equal. If they are equal, a TRUE value is returned, which is used by an IF function. The IF function returns an “OK” if a TRUE is returned or an “ERROR” if a FALSE is returned. The ROUND function is necessary because there can be very minute differences between values in the projected balancing. The two comparison fields from the Balance Sheet sheet are shown in Figure 8.9.
3. The next two tests are related to the long-term debt, since this is the focus of many peoples’ analysis. Enter the following formulas in the corresponding references:
O5:
=IF(SUM(Debt!E23:I23,Debt!E33:I33,Debt!E43:I43)>0,“ERROR”,“OK”)
O6:
=IF(SUM(Debt!E19:I19,Debt!E29:I29,Debt!E39:I39)>0,“ERROR”,“OK”)
FIGURE 8.9 The first validation checks to make sure that the total assets are equal to the total liabilities plus equity each period.
These two formulas are very similar. The first one sums up the total unpaid principal for each debt issuance (rows 23, 33, and 43 on the Debt sheet) and checks to see whether that value is greater than 0. If it is, it returns an “ERROR”; otherwise, an “OK” is returned. Nearly identical is the unpaid interest check that looks to the total unpaid interest for each debt issuance (rows 19, 29, and 39 on the Debt sheet).
4. The short-term debt limit is the next test. This requires an additional field to be entered on the Assumptions sheet. Go to cell D28 on the Assumptions sheet and enter a value of 100. Name cell D28 inputs_STFundsLimit. Now go back up to cell O7 on the Assumptions sheet and enter the following formula:
=IF(MAX(‘Balance Sheet’!E31:J31)>inputs_STFundsLimit,“ERROR”,“OK”)
This test takes the maximum short-term debt balance from any period on the balance sheet and checks to see whether that value is greater than the short-term funds limit. If it is, there is a breach and an “ERROR” value is produced. Otherwise, the short-term debt is within its limits and the test passes with an “OK.” Refer to Figure 8.10 for detail.
5. The final test we will implement is the Cash Check. Enter the following formula in cell O8:
=IF(ROUND(SUM(‘Cash Flow Statement’!E31:I31),0)=0,“OK”,“ERROR”)
This formula sums up the differences between the cash flow-calculated cash balances and the balance sheet-calculated cash balances. It uses a round function in case there are a few decimals’ difference and checks to see whether that value is equal to zero. If it is, that means there is no meaningful difference between the two balances and the cash reconciliation is “OK.” Otherwise an “ERROR” is produced.
FIGURE 8.10 The short-term debt test checks the short-term debt balance on the balance sheet to make sure it does not exceed the limit set on the Assumptions sheet.
OTHER VALIDATIONS
The validations and reconciliation methods presented in this chapter are common ones to monitor. As your model develops, you might want to include tests that are specific to important items in your model. For instance, if you create a section on convertible debt, you might want to monitor the current stock price and interest rates to compare against the terms of the convertible debt. The test would then be focused on whether any of the convertible debt is in the money or not. Further actions could be built on the results of this test, such as assuming conversion and equity dilution. We will see in Chapter 10 that we can also automate some of the items for tests, such as the downloading of current stock prices or interest rates from the Web.
TOOLBOX
Conditional Formatting
A useful feature of Excel is the ability to alter the formatting of a cell or range of cells depending on the value of the cell or range of cells. This feature is known as conditional formatting since the formatting depends on conditions that the user enters. Conditional formatting is easy to use since it is a prebuilt tool in Excel, but its operation and features differ greatly between Excel 2003 and Excel 2007.
For Excel 2003 and earlier: Conditional formatting can be accessed by going to Format—Conditional Formatting and starting the process by selecting Formula Is.
For Excel 2007: Conditional formatting received a major overhaul in Excel 2007. There are now a host of new options. To access conditional formatting, go to the Home ribbon, Styles box, and select Conditional Formatting. The drop-down that appears provides multiple options, which are shown in Figure 8.11.
The first two options are an organization of conditional formatting rules. The first of those options is called “Highlight Cells Rules.” Within this section, the user can select from a multitude of rules that will highlight a cell or range of cells with a selected format depending on the value or conditional test of values in the cell or range of cells.
The second option is called “Top/Bottom Rules.” Within this section, the user can select from rules that will change the formatting of a range of cells depending on the value of each cell compared to the other values in the range. For example, one of the options is “Above Average.” This rule will examine the active range that is highlighted, determine the arithmetic average, and highlight any cells in that range where the value is greater than the average.
FIGURE 8.11 Excel 2007 provides multiple conditional formatting options.
There are three more options below “Highlight Cells Rules” and “Top/Bottom Rules.” These provide highly specialized graphic formatting that is dependent on cell values compared to one another. Similar to “Top/Bottom” rules, these rules will change the color or create different icons depending on the values of cells in the active range.
Finally there are three options at the very bottom of the initial drop-down box. These allow a user to create a new rule from scratch, clear existing rules, or edit and manage existing rules.
ROUND
The ROUND function is a simple function that does exactly what the name implies: It rounds values to a decimal place the user enters. The entry parameters for the ROUND function are:
ROUND(value, # of decimal places)
For instance, the ROUND function is used with the following entry parameters:
=ROUND(5.244,0)
The return value for this function would be 5. This is because the function was instructed to round 5.244 to the 0 decimal place; since the tenths decimal place is 2, the value is rounded down to 5. Keep in mind that the decimal place parameter is all that matters for rounding the value preceding it.
For example, the following function is used:
=ROUND(5.499,0)
The return value for this function would still be 5, which is correct. ROUND does not first round the tenths decimal place up to 5 and then round the whole number up to 6.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.