How are we making out? We've covered a lot of ground in the last two lessons learning about how to prepare pro forma income statements and balance sheets. If you stop there, you can probably get 80% of the story. However, there's one more important view that rounds out an understanding of a business and its future. This comes from preparing the projected statement of cash flows. I start off with this lesson has been provided so that you can follow along.

The statement of cash flows tells one of the most important stories in my mind. This statement more than any other tells me about the ability of the business to generate, preserve and allocate cash. There are three sections of a cash flow statement, it's standard financial reporting, operating, investing and financing. These categories will serve us nicely. The operating section shows us the funds generated from operations as well as the company ability to manage working capital. The investing section highlights investment or reinvestment into the business, and the financing section shows the issuance and repayment of debt and equity.

Let's work through the preparation of the projection. To begin with, there are two methods for preparing the operating section, the direct and the indirect method. The direct method may feel more familiar to those of you with a financial analysis or Treasury background, because it calculates cash receipts, less cash dispersements to determine operating cash flows. The indirect method is probably more familiar to those of you with an accounting background, as this is the method most often seen in financial reporting practice. The indirect method is essentially a reconciliation of net earnings and operating cash flows. For our modeling purposes.

This is the method we will use As it aligns to the historical comparatives. Let's dive into preparing the projection by starting with net earnings. To begin with, we will carry down net earnings from row 24 by way of a reference formula. We then need to identify all the non cash items that impacted earnings and add or subtract them to get back to a cash basis. In our model, depreciation and amortization is the only item of note and we will reference this row to row 14. In practice, you should watch out for other non cash charges such as impairment charges, stock option expenses, pension charges, gains and losses on disposals investment income and holding gains, which are non cash and debt issuance costs.

They get amortized over the life of the loan. This subtotal is not defined under generally accepted accounting principles, but some people will often refer to this line as fun from operations. Then we move into the reconciliation of cash generated or consumed from changes in working capital. These accounts are determined by referencing your formulas to the balance sheet for receivables, we will reference to row 48 last year's balance minus this year's balance and then copy this formula across row 91. And increase in accounts receivable is a bad thing on the cash flow statement because that means you have a higher amount of sales that have yet to be collected, which reduced operating cash flows. We can do the same formula for inventory by referencing back to row 49.

Recognize that when inventory goes down, cash is freed up which is a good thing on the cash flow statement. The formula for our prepaids can be copied down as it works the same way However, when we look at our accounts payable and accruals, the opposite effect occurs. Our formula takes this year's bounce and subtracts it from last year's balance from row 59 of the balance sheet. The formula can then be copied across row 94. When accounts payable increases, we are essentially holding back more money from our suppliers than we did at the previous year in, which is a good thing from the Statement of Cash Flow perspective. This positive difference would partially explain why cash in our bank account will be higher than in the previous period.

The sum of these amounts gives us cash provided or used by operations, which in many cases is going to be vastly different than our net earnings. You can copy your subtotal across row 95. The next section covers our investing activities, which includes capital expenditures, as well as proceeds from disposals Have an asset or business unit. capital expenditures can be of two varieties, either growth or sustaining in nature, growth capital expenditures, drive incremental revenues or reduce operating costs. Whereas sustaining capital expenditures are necessary to sustain operations and don't typically have an associated return. We can model these expenditures from our assumptions directly to ensure that there's only one source for all assumptions.

Because capital expenditures represent a cash outflow. The formula is a negative reference to our row 129. Let's spend a moment just talking about how you measure cash generation. free cash flow is a conceptually superior way to think about investment returns from a business. However, there are many variations in practice as to how it is calculated. One view is to take your operating cash flows and subtract off your sustaining capital expenditures, which gives you a rough approximation of how much cash is available either to repay lenders or pay out to shareholders or reinvest in the business.

Unlevered cash flow would adjust this amount and add back the after tax interest cost expended. This would give you an indication of the cash generated by the business before any consideration of the financing strategy, which may vary by firm, leveraged cash flow or distributable cash flow, on the other hand, would take the free cash flow and subtract off scheduled and contractually obligated debt repayments to determine precisely how much cash is available for distribution to the shareholder or for growth capital expenditures. Analyzing cash flow in this way, helps decision makers in determining the most efficient and effective capital allocation. This is another course unto itself but I bring it up here only in the context of why forecasting and projecting This statement is so important. The third section of the statement summarizes the cash flow from the financing activities. The repayment of long term debt can be determined by comparing this year's balance against last year's.

Remember to pick up both the current portion on row 60 and the long term portion on row 62. When debt is declining, cash is being used to make those principal repayments and this gets reflected as a negative number on the statement of cash flows. The change in short term indebtedness works similarly, take this year's balance from row 58 and subtract last year's balance. When you're done you can copy this formula across row 105. The payment of dividends consumes cash and similarly gets reflected as a net cash outflow. If we plan to repurchase any shares, these would also be reflected as a negative cash flow.

Flow on this statement whereas an issue it's a new shares, or proceeds from the exercise of stock options, which show us positive cash inflows in this section. Let's fill in the subtotals for each section and for the overall net change in cash during the period. Finally, we should be able to reconcile the change in the cash balance year over year. First, carry forward the ending balance from the previous year to make that the beginning balance, and then calculate the cash end of your balance by taking the beginning cash and adding the net change in cash during the year. Another important model integrity check compares the calculated balance of cash at the end of the period with the cash projected on the balance sheet on row 47. These two must agree though on your first attempt, they will often not so let's begin In a moment talking about how to reconcile your model when it doesn't balance.

The reason your model does not balance is contained somewhere on your income statement and balance sheet. Let's look at four of the most common reasons and steps you should take. You need not model these changes into your copy of the model that we've been working on. First of all, check your fixed asset roll and let me show you how typically we need to consider any accounts associated with additions disposals and depreciation. So start with calculating the change in the fixed asset balance year over year. What we discussed earlier still holds a decrease in a fixed asset balance is a good thing.

In other words, a net cash flow positive on the statement of cash flows. So in 2003, we need to explain a net change of a positive 1200 $96. The accounts that make up this difference in our model, our depreciation, which is a positive, add back if you recall of 3000 In $296, and a negative investment in capital assets for sustaining capex of $2 million. Review your model and ask yourself if both of these have been captured on the statement of cash flows, it can get a lot more complicated if you have disposals as the gains and losses in the proceeds need to be included in both the reconciliation and the statement of cash flows to make it work. Fortunately, we rarely model disposals and financial projections. Secondly, remember that the first year of your projection needs a comparative if it's an existing business.

Another reason for pulling in comparative numbers to our financial model. If you're modeling a startup, then your comparatives are zero when the full amount of investment in working capital and capital assets will be reflected as cash flows. Third, watch for those buried non cash expenses and in practice, they are very common and not separately disclosed on the income statement. Remember the list I shared with you earlier, including stock options and pensions and debt issuance costs and the like. And then finally, focus on the balance sheet on a line by line basis if necessary, to see how the change in each and every account has been reconciled on the statement of cash flows. Now, I've been building financial models for decades, and even my first attempt often has error, so don't get discouraged.

If you follow a rigorous approach and follow the processes that we've discussed. In this course, you'll find your errors and be able to reconcile your model. Fortunately for you, unlike how we're whizzing through and constructing a financial model in just an hour in this course, in practice, you'll have a lot more time to think about how to best model each of the relevant accounts in calculations. So in this lesson, we drafted the last component of our financial model, the statement of cash flows, keep in mind these three ideas. First of all, that the cash flow statement provides insight about the true cash returns generated To buy the business capital allocation is a process that management uses to determine what to do with those returns. Secondly, the cash flow statement itself is a reconciliation of changes in the balance sheet from the previous period to the current period.

And thirdly, the cash flow statement is the final integrity check of the financial model. Cash calculated at the end of the period must always agree to cash on the balance sheet. When it doesn't, you need to go back and determine what has been missed. What we now have is a financial model that we can take to our strategic planning session next week, we can forecast the financial results, the balance sheet position the cash flow generation for the next five years by changing a handful of assumptions. So for instance, if our lending facility requires us to maintain a minimum current ratio of one to one, we can initiate the discussion next week by stating that the current plan Doesn't work as indicated by the ratios highlighted on the screen. The first suggestion might be to explore what impact the increase in the annual dividends are having on this metric.

So instead of modeling an increase of $500 a year, let's assume that it remains that the existing $3 million that has been historically paid out in the previous two years. What impact does this have on the current ratio, we change the assumption, go back up to the current ratio. And lo and behold, when the model recalculates, our potential covenant violation is now fixed. This is just one of dozens of scenarios that you or others may want to explore as you guide decision making to optimize the strategic plan. Once you are finished with your model, play with it, test it, stress it and start to think strategically. How can it be optimized are certain assumptions interrelated?

Which assumptions are most sensitive. A well designed financial model is an extremely powerful management tool. And our next and final lesson we're going to look at some advanced financial modeling techniques, particularly related to modeling multiple businesses simultaneously. Until then,