From the course: Financial Modeling and Forecasting Financial Statements

Use the realistic but challenging "plug": Loans

From the course: Financial Modeling and Forecasting Financial Statements

Start my 1-month free trial

Use the realistic but challenging "plug": Loans

“

Let's try the most realistic plug figure, loans payable. For most companies, the first option in obtaining financing is through borrowing. Raising new capital from investors is more expensive in terms of giving away partial ownership and typically takes more time, more investment advisor costs and more public disclosure of internal strategic plans. And even if a company does plan on seeking new investor funds, those funds are often coupled with some new borrowing in order to maintain an appropriate financing mix. So we need to learn how to include new borrowing in our financial statement forecast. Hey, easy. From the initial example we did before with year two property plant and equipment of $700, we learned that additional financing needed in year two is $368. Rather than add that to paid-in capital, let's add it to loans payable instead. We had originally planned on loans payable in year two of $400. We'll just bump that up to $768, 400 plus 368. The resulting balance sheet and income statement look like this. Perfect, that was easy. Ah, but there's a little problem. Remember before that we used year one data to estimate that the interest rate on Han Company's loans is 10%. We'll use that 10% interest rate along with a forecasted level of loans payable of $400 to forecast interest expense of $40 for year two. That $40 interest expense is what is in our current forecast. But if we use loans payable as a plug for year, the new loan amount is $768. That means that interest expense should be about $77. But that changes pre-tax income, which changes income tax expense. And the change in interest expense, coupled with the change in income tax expense, changes net income. And a new net income means a new number for retained earnings, which changes the amount of the loans payable plug figure, which changes interest expense. And around we go again. You could see why I didn't use the loans payable plug figure in our first example. But this is not an infinite loop. Things aren't as bad as they seem. I have found that it usually takes just a couple of iterations to get everything to match up. In this example, if I change interest expense to $77 to match up with the plug figure loans payable amount of $768, net income after taxes decreases by $15 from $42 down to $27. This changes retained earnings to $57 from $72, which in turn increases necessary loans payable, the plug figure amount by $15 up to $783. Okay, so we still have a mismatch between interest expense of $77 and loans payable of $783, but we're close. Well, let's go one more round. I will now change interest expense to $78 to be consistent with the loans payable amount of $783. I follow the process through and voila, here's where we are. That wasn't too bad. I have found that if you don't worry about six decimal place precision in your forecast, you can get internal consistency with just a couple of iterations. And people who are clever with spreadsheets can use a special function to do all of this automatically. Well, let's recap. We've explored the following plug figures. Paid-in capital, okay, but companies usually don't use equity financing casually. Dividends, no. Companies are more cautious and strategic with dividend policy and don't want the amount of dividends jerked around each year to fill holes in financing plans. Cash or investment. Well, I don't like this one. This seems lazy to me, to plan to dump surplus financing into some cash holding tank. Loans, yes. This makes the most sense because companies usually look to borrowing first in making their financing plans. Yes, there are some mathematical challenges in using loans payable in creating forecasted financial statements, but nothing we can't handle.

Contents