Welcome back. This lesson picks up from our previous lesson where we have just finished projecting the income statement. Our start file has once again been provided for this lesson to allow you to follow along with this lesson. Let's take a look at our balance sheet as it exists right now. We have two years of actual already showing in columns D and E, which will help guide us Let's work through the forecasting of each of these lines in the balance sheet in the following order, first working capital secondly, capital assets and other long term asset accounts. Thirdly, the equity accounts fourthly long term debt and then finally, we're going to deal with the cash in the bank indebtedness line.

Now, working capital typically includes receivables, inventory, raw materials, prepaids, payables and accruals. Each of these accounts need to be separately considered. Once again, I thought That ratio analysis can be particularly helpful for forecasting these balances. For example, take your receivables if we assume that our customers are going to pay us on average in 45 days, we can determine how much capital will be invested in our receivables using the average collection period formula and solving for receivables. In this case, the ratio becomes the assumption rather than the performance indicator. So, let's add this to our list of assumptions.

The formula for calculating the year end balance of receivables then becomes sales divided by 365 days to get our average daily sales times our expected average collection period the 45 days. The best way to validate the assumption is to compare the assumption to the average collection period experience for the last two years. The 45 day assumption appears to be slightly more conservative than the 40.6 achieved in each of 20 x one and 20 x two. Notice here how I demonstrating how ratios can be used to both forecast balances as well as validate their plausibility. Similarly, inventory has been expressed to us in terms of a turnover ratio. First, let's state the assumption.

Next forecast the balance of inventory similarly taking the cost of sales and dividing it by the expected turnover ratio assumption. Take this result and copy it across row 49. And then once again, compare the result against the comparatives by recalculating the ratio in 2000 next to turnover was five times versus the expected six times in 2003 and beyond However, we did have a similar level of efficiency in 2001. So perhaps the assumption appears plausible. prepaids are typically an immaterial bounce. So sometimes we can make the simple assumption that whatever they were last year, they will continue to be in the next five years.

Unless we have evidence to the contrary, this is probably an appropriate assumption. Remember, when you're forecasting the future, it's better to be approximately correct than precisely wrong. You can spend a lot of time developing complex sub schedules that have no material impact on the decision at hand. We can use the same sort of approach for forecasting our accounts payable and accrual bounds using average days payable. First, add the assumption of 20 days to the list of assumptions. Next, forecast the end of your balance using cost of sales divided by 365 days to come up with your average daily pay.

And times that by the average expected disbursement period of 20 days to arrive at the bounds of our expected accounts payable. Finally, recalculate the ratio and compare it against the prior your actuals to assess plausibility that completes our working capital projection. Note that you could similarly use vertical analysis to devise these balances that is to say, receivables, inventory and payables as a percentage of sales. The key point I want you to keep in mind is that working capital should be dynamically modeled to vary with the level of sales activity. It's not a static number. Working capital is one of those most often overlooked investment items in many financial models, so don't be that analyst.

Let's move on to capital assets. Back when we calculated our projected depreciation and amortization, in the last lesson, we had to prepare The capital assets schedule. And now it turns out that schedule will also feed this line of our balance sheet projection using the net book value of our capital assets. That is our cost basis, less the accumulated depreciation. If you have other long term assets, you will model them along similar lines using a sub schedule reconciliation. to track the continuity of the balance from the beginning of each period to the end of each period.

The ending balance flows to the balance sheet, and the change in the balance typically flows through either the income statement or the statement of cash flows. goodwill is not typically amortized for accounting purposes and arises as a result of an acquisition of another business. goodwill however, can be impaired. However, for the purposes of strategic planning, we will typically assume no changes necessary. So for today, it's fine to just carry that bows forward to each of the years of the projection. copy your subtotals for current assets in row 51 and total assets in row 55 across your worksheet, and that takes care of our assets.

Leaving aside the cash balance for the moment. Let's look at the shareholders equity section. Next, share capital represents the common shares or units issued and outstanding. Now for accounting purposes, this bounce does not get adjusted after the issuance date. So unless the company contemplates issuing more shares, you can simply carry this bounce forward. If you are however, contemplating a share issuance, perhaps to pay for a significant investment or acquisition, then perhaps you would need to model the shares issued and the receipt of proceeds.

This is more complicated but again, you approach this using a separate sub schedule that calculates the continuity of the shares outstanding and bring onto the balance sheet the ending units outstanding at the end of each period. Other equity accounts such as contributed surplus, accumulated comprehensive income can be modeled similarly using this approach, retained earnings was calculated in our last lesson. So we simply need to enter a formula that links this line to our statement of retained earnings above on row 29. The retained earnings account links our projected income statement and balance sheet together, which is why I painstakingly broke it out separately. In our last lesson, we can copy across our equity subtotal on row 67. As we move on to long term debt, we have two approaches.

One is to model the contemplated terms of the existing anticipated death facilities for each year the financial projection. Once again, you would prepare your continuity sub schedules and amortization schedules and bring onto the projected balance sheet the current long term portions of debt at the end of each period. For our model, we are told that long term debt amortizes out rate of $5,000 per year with no refinancings, let's set that up as an assumption and reference the current portion onto the projected balance sheet by way of a formula. That means the long term portion of debt must be the balance at the end of last year, less the expected principal repayments for the year. Notice in 2007, this actually creates a negative balance, which is obviously not right. We can fix this using an IF function.

If e 62. minus f 60 is less than zero, give me zero. And if it's not, give me the formula. Have you got that? Then copy that formula across all yours in row 62. Our long term debt is now dynamic with whatever principal repayments are designated on the existing debt over the next five years. Now the other approach to forecasting long term debt is to use more of a theoretical approach assuming a constant capital structure.

When you use this approach, long term debt is typically assumed as a constant percentage of total assets. This means the amount of long term debt may automatically increase or decrease depending on these balances. This may feel too theoretical to be practical, however, not so businesses will often refinance themselves as their balance each change through the years but in the real world, it's just a little more lumpy. With our liabilities now modeled, let's copy across subtotals for all years for rows 6163 and 69 to arrive at our total debt and equity. Finally, we need to deal with the surplus or shortage of cash to make the balance sheet balance when assets are high than the liabilities and equity, we will need to borrow under short term credit facilities to fund the deficit. When the opposite is true, high cash surplus is projected on the balance sheet at least that's how we're going to model it today.

Making the financial model smart enough to know when it has cash and when is boring under a line of credit, once again, requires you to use the excels IF function. Let's start with bank indebtedness and model that line refer to the formula at the top of the screen, which essentially says if total assets less accounts payable in a cruel, less the long term debt less the equity is less than zero, give me zero, because that essentially means we have excess cash. However, if it's greater than zero, give me that number to represent the amount of draw on the credit line. copy the formula Across row 58. Next, go up to the cache line on row 47 and do something similar. I'm going to shortcut this formula a little by saying, if the bank indebtedness line is zero, ie row 58, then I probably have cash.

So give me total liabilities and equity, last receivables, inventory prepaids, capital assets and goodwill. Copy this formula across row 47. You know, you've got to write when the projected balance sheet balances. In fact, let's add a little integrity check reminder, this little check helps flag for your attention when the formula is not behaving properly. We still have one last account to forecast that was left over from our previous lesson, interest expense. We can now go back to this line on the income statement and calculate How much interest we expect to pay which will be a combination of the interest paid on the long term debt and on the bank indebtedness.

The interest rates have been separately provided to us for each of these facilities. So let's first add those to our list of assumptions. Now, we can go back and calculate interest expense on row 19. If you use a debt continuity schedule for your long term debt, you may have a more detailed calculation supporting the amount of interest expense paid on the long term debt, which you can reference into directly to this line. Otherwise, you can calculate interest expense in one of two ways. The first way is to calculate interest expense based on the opening balance of debt.

Excel will calculate this without issue. However, there's a conceptual flaw in this approach, in that the principal draws or repayments during the year are not factored into the category Wouldn't it be more accurate to use an average balance, the second approach calculates the average balance of debt and applies the relevant effective interest rate. The tricky part of this approach is that you have now created a circular reference in your model. Now a circular reference was created because we use the formula to balance our assets with our liabilities and equity, which is in part determined by the amount of net earnings flowing through retained earnings, which is in part determined by the amount of interest expense. So to calculate the exact amount of interest manually requires an endless loop of trial and errors to get this number accurate. Fortunately, Excel has the capabilities to do all these trial and error calculations for us by enabling a function called interest calculations.

Your Excel may or may not have this option turned on if you're getting a warning Have a circular reference as we just saw, then this functionality is turned off. To turn it on, go to File, Excel options, formulas, and select the checkbox for enabling the inner of calculation. Excel will magically resolve your circular references and precisely calculate the expected interest payable for each of our projected years based on the average balance outstanding. This is one of my absolute favorite features of Excel, as I hate having conceptual flaws in any of my calculations. Before we close this lesson, once again, I encourage you to add a few more ratios to your balance sheet to help you interpret your projections and validate your findings against past performance. Let's add a current ratio d 51 divided by d 61.

Because maybe that's a bank covenant. Our current ratio appears to be deteriorating over the life of the projection, likely driven up by the buildup of bank indebtedness. One assumption that drives up bank indebtedness is paying out dividends. Let's also add a funded debt to equity ratio, which is calculated as the bank indebtedness, plus the current and long term portions of long term debt divided by equity. This gives us an indication of the financial leverage. Perhaps the executives are concerned about maintaining financial leverage within a given range specified by the board of directors.

So there's a lot in this lesson particularly for those of you who have a limited accounting background. The key points to keep in mind from a financial modeling perspective are first, you can use your knowledge of ratios to forecast various balances of working capital. Secondly, prepare those continuity schedules that try The balance of each account related to long term assets equity long term debt, and tie in the related balances at the end of the period. Third, balance the accounting equation assets equals liabilities plus equity using either the cash or the bank indebtedness line. With the balance sheet projection complete, you can now go back and complete the interest expense as we learned. In the next lesson, we're going to look at the projected statement of cash flows.

So until then,