Empower Your Banking Sales Budget Planning with airSlate SignNow

How to create outlook signature

this is a spreadsheet model on Financial Planning and forecasting my name is pad Obi professor of Finance at puu University Kum meds 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 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 uh be short-term debt 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,8 so we are forecasting that sales would would go up by 20% and 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 sell of 20% you 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+ 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 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 all right and then the difference there uh we don't really need the decimals you know the difference that you see here is simply the difference uh between net income and uh and uh 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 statements again sales operating costs and retained earnings everything else follows in the fashion of a Prof fora income statements now unless you have any kind of additional information concerning for example interest expenses uh this is basically the model that should be followed and then proceeding to the balance sheet again there's 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 sem 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'll have to change are the current liabilities but only these two items here accounts payable and acrs 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 acrs 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 retain earnings so as The annotation here tells you that the balance of 15,470 plus addition to retain 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 uh 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 ingly now we're done so we add up our assets to find 47,3 we add up our liabilities and Equity to find 46,2 182 and as you can see our use of funds our total projected assets exceeds our source of funds 46,2 182 by 754.74 point3 dollar 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 that you see here would be equal to the old to the current interest payment of 510 plus the interest charge is 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% multipli it if I may go down here again multiply 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 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 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 retain 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 2987 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 adjust that balance right here plus 12% of this 29872 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 the new afn that was determined in the first pass all right so again that's going to adjust our retain 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 retain earnings would again have to be adjusted because again it's going to be equal to the original balance of 15,470 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 get that and so when when again we calculate the difference between total assets and total liabilities plus Equity we find this skeletal difference right there which uh we are 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 retain earning uh this new afn right here additional afn I should say so with that retain earnings changes in this third pass and then coming down here again short-term debt will increase further by 1.18 and of course addition to retained earnings will in uh will be adjusted it's going to be equal to this 15470 plus 277.50 to give us this so now when you look at the difference between this total assets and this now revised total liabilities plus Equity is virtually zero and if you really want to you know do something unnecessary you can do do a a final uh 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 uh needed to be dealt with because you you're trying to respond to the question how do we rate is 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 78540 that's how much external funding that this firm would need to back up its projected 20% increase in its sales now then I you can see though it's not all the times that a firm's sales are projected to rise that the firm would be needing external funding so the question is what is the self-supporting growth rate gar that self-supporting growth rate gar is the maximum Revenue growth rate that could be achieved without need for additional financing so to find this this number right here you can use the wi if command the goalseek command within the whif menu depending on the uh version of excel that you are using so if you go to data you go to wh 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 sale containing the growth rate and click okay and okay 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 $ 7854 and that's this that's the end of this presentation I am pad professor of Finance puu University calat