Speaker 1: This is a spreadsheet model on financial planning and forecasting. My name is Pat Obie, professor of finance at Purdue University Calumet. The financial planning process leads to the determination of how much external funds in the form of common stock and net new borrowing that a firm would need to support a projected increase in the level of business activities. The first step is to forecast sales for the upcoming period. Such a forecast is typically informed by historical trends and expected business conditions. After figuring out sales, step two is to then determine the level of assets needed to support the new sales level. The final step is to calculate how much additional financing in the form of additional debt and external equity that would be required to pay for any additional assets. In this example, sales are projected to grow at the rate of 20%. Interest rate on all debts is 12%. The source for any additional funds, which is really our AFN, the additional financing that will be needed would be short-term debts. Right now, fixed assets are fully utilized. If we need additional fixed assets, which we're going to need if sales are projected to grow, we're going to raise fixed assets at the rate of 7.5%. Tax rate is 40% and dividend payout ratio, to use in this example, is 45%. So here's the current year data right here, the income statement data. And then afterward, we have the balance sheet data right here. Total assets currently is 41,280. So, we are forecasting that sales would go up by 20%. So sitting down here, if I may delete that, all I did was to hit equal, click on this guy right here, times, open parenthesis, 1 plus the growth rate cell of 20%, close parenthesis. Now then, in carrying out this forecasting exercise, there are three items that would be dealt with on the income statement. They are beginning with sales, and then operating costs, which in this forecasting model called percent of sales, it's going to also go up at the same rate as sales, which is also 20%. So when I stayed there, I hit equal, clicked on this, multiplied by open parenthesis, 1 plus the same rate of 20%. So that's the second item that will need to be adjusted, and the third and final item that will need to be adjusted would be addition to retained earnings. And that's going to be based on the dividend payout ratio here. Dividends, as you can see here, is based on the rate of 45%. So all I did there was to hit equal, and then I clicked on the dividend payout ratio of 45% multiplied by net income. And then the difference there, we don't really need the decimals, the difference that you see here is simply the difference between net income and dividends. So that's our addition to retained earnings. So these are really the three items that will need to be adjusted on the income statement. Again, sales, operating costs, and retained earnings. Everything else follows in the fashion of a pro forma income statement. Now unless you have any kind of additional information concerning, for example, interest expenses, this is basically the model that should be followed. And then proceeding to the balance sheet, again, there's going to be three items that will need to be adjusted based on the percent of sales method. They are, number one, all current assets. So current assets would also increase spontaneously with sales. As you can see here, I raised each of these current asset items based on the same sales growth rates in this example of 20%. Now let's kind of leave fixed assets alone for now. The second set of items that will have to change are the current liabilities. But only these two items here, accounts payable and accruals, because these are non-investor supplied sources of capital, and they tend to change spontaneously with sales. And so in this model, we also have to bump them up, as you can see up here, at the same rate as sales. Now again, accruals and payables are the only two current liability items that would be allowed to change spontaneously with sales. The third and final item that will need to be modified would be retained earnings. Because here, we're going to have to, if I hit delete there, we're going to have to, in addition to the current balance of $15,470, add, if I scroll up here a little bit, add this new addition to retained earnings. So as the annotation here tells you that, the old balance of $15,470 plus addition to retained earnings. That's it really. Now many a time, you're not going to have to do anything with fixed assets in cases where the firm is currently utilizing them at less than full capacity. And so you'll simply have to come here, hit equal, and then reference this number right there. But in this example, if I may undo it, we are told that the firm is operating at full capacity and that fixed assets therefore would have to grow in this case at the rate of 7.5%. So based on this specific information, we increase our fixed assets accordingly. Now we're done. So we add up our assets to find $47,036. We add up our liabilities and equity to find $46,282. And as you can see, our use of funds, our total projected assets exceeds our source of funds $46,282 by $754.3. This difference is called the additional funds needed. It is the amount by which assets, which are use of funds exceeds our projected liabilities plus equity. So now the question is, what are we going to do? This company would need $754.3. These may be millions going forward. So this case says for us to raise any additional funds in the form of short-term debt, and of course it's going to cost us 12% in interest. So now we're going to begin the financing feedback. In the first pass, again, everything repeats except right here. The total interest charges that will occur as a result of financing this AFN that you see here would be equal to the old, to the current interest payments of $510 plus the interest charges associated with the new AFN. So sitting down here, if I hit delete, I hit equal, I reference the current interest charges and to that I will add, open parenthesis, the interest rate here of 12% multiplied. If I may go down here again, multiplied by the new additional borrowing, the AFN, all right, close parenthesis and hit enter. All right. And that's it right here. That's how I got that $6010. Now because the outflow of funds has increased, that's going to cause our revised net income to drop. And therefore, as you can see here, our retained earnings will automatically adjust. So now we go down here in the balance sheet section under the first pass and under the first pass again, nothing changes right here in the asset section. In the liability section though, as you can see under short term debt, this amount would be equal to the current short term debt amount plus the AFN, the additional funds which would have to be borrowed, remember, in the form of short term debt. So it's the old balance plus the AFN. But also because, scroll up just a tad bit, because our retained earnings adjusted, that means down here, if I hit delete, the revised retained earnings balance would be equal to the original balance of $15,470, not this amount right here, because this no longer holds, plus the revised addition to retained earnings. That's it. So when we do that, we have to recalculate our AFN. Again, it's going to be total assets minus total liabilities plus equity, and we still find that we have a gap of $29.87. So this gap would again have to be borrowed additionally at the rate of 12%. So now we have to embark on this iterative process until the gap turns to zero. So that means in this second pass right here, total interest payments would be equal to this adjusted balance right here plus 12% of this $29.87. So if I click on this cell right here, you will see, looking up here, that that's precisely what I did. If I hit F2, you will notice it's the blue cell, E21, which is this, plus the interest rate of 12% multiplied by cell E51. The purple cell, if I go down here, is this amount right here, the new AFN that was determined in the first pass. All right. So again, that's going to adjust our retained earnings, and so then if we come down here, the total short-term debt balance would have to be adjusted to be equal to, I hit a delete, this running balance there plus this, sorry about that, all right, will be equal to this running balance here plus this new additional AFN. And then, of course, our retained earnings would again have to be adjusted because, again, it's going to be equal to the original balance of $15,470. It's not going to be this, and it's not going to be this because things have changed, right, plus this revised amount right there. So that's how we got that. And so when, again, we calculate the difference between total assets and total liabilities plus equity, we find this skeletal difference right there, which we're a little bit of overachievers today, so we're going to have to fix that up. So up here again, our total interest charges will go up some. It's going to be equal to this amount plus 12% of that new, of this new addition to retained earnings, this new AFN right here, additional AFN, I should say. So with that retained earnings changes in this third pass, and then coming down here again, short-term debts will increase further by 1.18. And of course, addition to retained earnings will be adjusted, it's going to be equal to this $15,470 plus $2770.6 to give us this. So now when you look at the difference between this total assets and this now revised total liabilities plus equity, it's virtually zero. And if you really want to, you know, do something unnecessary, you can do a final, the fourth pass. And at that point, it will be clean zero. But by this third pass, you should be okay based on the data used in this analysis. So with all of this, as you can see, the initial AFN needed to be dealt with because you are trying to respond to the question, how do we raise this money and what is it going to cost us? Well, that question has been addressed in these subsequent passes as you go through this iterative process. And so when you add up all of these, you find the cumulative additional funds needed to be 785.40. That's how much external funding that this firm would need to back up its projected 20% in its sales. Now then, as you can see, though, it's not all the times that a firm sales are projected to rise or the firm would be needing external funding. So the question is, what is the self-supporting growth rates? G star, that self-supporting growth rate, G star is the maximum revenue growth rate that could be achieved without need for additional financing. So to find this number right here, you can use the what if command, the Goal Seek command within the what if menu, depending on the version of Excel that you are using. So if you go to data, you go to what if analysis, you choose Goal Seek. You want to set this AFN to a value of zero. By changing, you go up here, click the cell containing the growth rate and click OK and OK. Now that's going to be 10.34%. It says if our sales are expected to grow at the rate of 10.34% or less, that we're not going to need any external funding. In fact, this 10.34%, if I scroll down here, as you can see, will give us an AFN of zero. If sales are expected to rise by any rate above, sorry, by any rate below 10.34%, we would actually be having surplus funds. For example, 10%. If I go down here, as you can see, we now have a negative AFN, meaning we're going to have surplus funds. If in fact our revenues were to grow at the rate of 10%. So basically, any projected growth rate below that 10.34%, which is the self-supporting growth rate, we're not going to be needing any external funding. But above 10.34%, such as 11%, we will be needing external funding. At 11%, our AFN is 51.85%. And of course, in this example, the projected growth rate is a whopping 20%, which will cause us to need a cumulative amount of $785.4. And that's the end of this presentation. I am Pat Obie, Professor of Finance, Purdue University, Calumet.