Streamline Your Sales Budget Process with airSlate SignNow

How to create outlook signature

this is a spreadsheet model on financial planning and forecasting my name is Pedro B 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 percent the source for any additional funds which is really our AFN the additional financing that will be needed would be short-term debt right now fixed assets are fully utilized if we need additional fixed assets which we are 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 percent and dividend payout ratio - using this example is 45 percent 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 forty one thousand two hundred and eighty so we are forecasting that sales would would go up by 20 percent 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 cell of 20 percent in closed parentheses 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 parentheses 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 percent so all I did there was to hit equal and then I clicked on the dividend payout ratio of 45 percent multiplied by net income alright and then the difference there we don't really need the decimals you know the difference that you see here is simply the difference between net income and rate and dividends so that's our Edition 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 proof former income statements 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 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 we'll have to change or the current liabilities but only these two items here accounts payable and accruals because these are none 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 of 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 fifteen thousand four hundred and seventy ad 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 fifteen thousand four seventy 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 here you call 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 the specific information we increase our fixed assets ingly now we're done so we add up our assets to find forty seven thousand and thirty six we add up our liabilities and equity to find forty six thousand two hundred and eighty two and as you can see our use of funds our total projected assets exceeds our source of funds forty six thousand two hundred and eighty two by 750 4.3 this difference is called the additional funds needed it is the amounts bike which assets which our use of funds exceeds our projected liabilities plus equity so now the question is what are we going to do this company would need 750 4.3 dollars this 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 are 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 payment of 510 plus the interest charges associated with the new AFN so sitting down here if I hit delete a hit equal I reference the current interest charges on to that I will add open parentheses the interest rate here of 12% multiplied if I may go down here again multiply it by the new additional borrowing the AFN alright close parentheses and hit enter all right and that's it right here that's how I got that six or ten 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 assets 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 I retain earnings adjusted that means down here if I hit delete the revised retained earnings balance would be equal to the original balance of fifteen thousand for seventeen of this amount right here because this no longer holds plus they 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 twenty nine point eighty seven so this gap would again have to be borrowed additionally at the rate of 12 percent so now we have to embark on this additive 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 twelve percent of this twenty nine point eighty seven 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 eat 21 which is this plus the interest rate of 12% multiplied by cell be 51 the purple cell if I go down here is this amount right here they the new AFN that was determined in the first pass alright 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 I delete this running balance there Plus this sorry about that all right will be equal to this running balance here Plus this no additional AFN and then of course our retailers would again have to be adjusted because again it's going to be equal to the original balance of 15,000 for 7470 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 the 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 attorney 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 debts will increase further by one point eighteen and of course addition to retain earnings will Inc will be adjust that is going to be equal to this fifteen four seventy plus twenty seven seventy point six 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 do it a final at 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 seven one hundred eighty five point four T that's how much external funding that this firm would need to back up its projected 20% increase in its sales now then as 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 rates g-star that self supporting growth rate GSR 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 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 rates and click OK and ok now that's going to be ten point thirty four percent it says if our sales are expected to grow at the rate of ten point thirty four percent or less that we're not going to need any external funding in fact this 10 point thirty four percent if I scroll down here as you can see will give us an AF n of zero if sales are expected to rise by any rate above sorry by any rates below ten point thirty four percent we would actually be having surplus funds for example ten percent 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 ten percent so basically any projected growth rate below that's ten point thirty four percent which is the self supporting growth rate we're not going to be needing any external funding but above ten point thirty four percent such as eleven percent we will be needing external funding at eleven percent our AFN is fifty one point 85 and of course in this example the projected growth rate is a whopping 20 percent which will cause us to need a cumulative amount of seven hundred eighty five point four dollars and that's this that's the end of this presentation I am pad will be professor of Finance Porter University Calumet