Excel: Forecast net income and sensitivity analysis

In the net income Excel workbook the first tab is titled one net income so it's this tab right here make sure your screen looks like this. Here we have a year one actual income statement for Sophie company showing net income of 40 that's actual that's last year. There's also a corresponding column for forecasted net income for the next year for the year 2082 that's this forecasted column right over here. We're going to forecast the income statement and describe the impact of changes in assumptions. We're going to do a little sensitivity analysis. For year 2, sales are expected to increase 50%. This expected growth rate is shown in orange right here in column I. It's in cell I4, 50%. Now in year 1, Sophie's gross profit percentage, that's gross profit divided by sales was 30%. That was gross profit of 300 divided by sales of 1,000, 30%. That was the gross profit percentage in year one. In year two, if the sales mix and profit margins are expected to be the same, then it makes sense to assume that the gross profit percentage will remain the same at 30%. So that's our assumption for year two, that the gross profit percentage will remain at 30%. This number is shown right there in light green in cell I6. In year one, the implied property, plant, and equipment useful life was 20 years. Let me show you how that was computed. Depreciation expense is 15. Property, plant, and equipment, which we see down here. And remember, these are the actual numbers from last year. So property, plant, and equipment are 300. Divided by depreciation expense of 15 implies a depreciation life of 20, or if you want to think of it the other way, property plan and equipment of 300 divided by a depreciation life of 20 gives you depreciation expense of $15 per year. So the thing that we would expect to remain about the same year after year is the expected depreciation life. So we're going to assume that it's the same in year two. So in column I, cell I7, in this nice little kind of pinky color there, we've got 20 years for our estimated depreciation life. Now you can see that in year one, the other operating expenses were 18.5% of sales. I'm glad that we chose these numbers. 185 divided by a thousand, we can easily figure out that's 18.5% of sales. Now if other operating expenses are variable expenses, such as wages, And if Sophie Company expects to do business in a similar way in year two, then other operating expenses can be expect to be, again, 18.5% of sales. That number is shown over here in light blue in column I. That's cell I-8, all right? In year one, the loan interest rate was 10% per year. And we can compute that like this. If loans were 300 and interest expense was 30, that implied a loan interest rate of 10% per year. Well, our best assumption unless circumstances have changed is that that loan interest rate will remain unchanged in year two. So in column I, cell I10 in yellow here, we've also put in our assumption that the interest rate will be the same at 10%. Now let's talk about income taxes. We certainly don't want to forget our income taxes. The income tax rate in year one is computed as income taxes, 30, divided by income before taxes. And that number comes out to be 42.9%. In the absence of any change in tax law, we would expect the interest rate to be about the same. So we'll put that in in column I here, cell I-12, same tax rate, 42.9%. That's the number shown in gray. Okay, so we've specified all of our year two forecast assumptions. We're gonna assume that things stay the same as they were in year one, except that sales are forecasted to go up by 50%. So now let's create the year two forecasted income statement. Let's go to cell G is right here in the forecasted income statement for year two. G6, we're going to combine data from our expected sales amount. 1,500, we'll multiply that by expected gross profit percentage, 30%. And we see that we get expected gross profit of 450. Now we don't want to skip past our cost of goods sold number. and we know what the answer needs to be, but I want it to appear negative, just like it showed up here negative in year one. So what I'm going to do is I'm going to take the gross profit number minus the sales number, and we know what the answer should be. It should be minus 1,050. We did a little mental math there. Minus 1,050, just so that expenses show up as negative amounts. That's why I did that. So the key number here is the assumption that our gross profit percentage will remain the same in year two at 30% of sales that gives total gross profit of 450. Now let's go to cell G7. And we want to estimate our depreciation expense for year two. We're going to combine the amount of property, plan and equipment. And we're assuming that remains the same at 300. And here's what I'm going to do. I'm going to grab that number, but I'm gonna put a minus sign in front of it because I know I'm estimating an expense here just to save myself some time. So I put the property plant and equipment, 300, but I put a minus sign in front of it. And over what life are we going to depreciate that property plant and equipment? Over 20 years. That's right here in cell I-7. So divided by 20 years, that gives estimated depreciation expense in year two of 15. Beautiful. Now, we're just going down the income statement here, having the time of their lives. Let's do this. Let's estimate the other operating expenses. Our forecast, our assumption, is that other operating expenses will be same as they were last year, 18.5% of sales. So I'm going to multiply sales, 1,500, but notice I put a minus sign there in front just to save myself some time, because this is an expense. I want it to come out to be a negative number, multiplied by the 18.5% minus 278. That's our forecasted amount of other operating expenses next year, minus 278. Now let's do this. I want to compute operating profit. All operating profit is the gross profit, take out the depreciation expense, take out the other operating expenses. Since those are already shown as negatives, I can just add them up. So it's the 450 plus the negative 15 depreciation expense plus the negative 278 for other operating expenses. That gives me forecasted operating profit next year, year two of 158. Now we're at interest expense. Interest expense is going to be a function of our loans and our estimate is that loans payable next year will be the same as they were last year, 300. So let's start with that. My interest expense is gonna be the forecasted loan amount multiplied by the forecasted interest expense. So I'll put a little minus sign here just to get us started. Loans payable multiplied by the forecasted interest rate, which we say is gonna be the same as it was last year, 10%. That should give us a forecasted interest expense of $30. Now let's compute our income before taxes. We'll just add forecasted operating profit and forecasted interest expense, which is a negative number. We've got forecasted income before taxes of 128. That only leaves one more expense, it's income taxes. So go to cell G12 here and income taxes going to be based on the amount of income before taxes and notice again I put a little minus sign there it's going to be income before taxes multiplied by what we expect the tax rate to be 42.9 percent same as it was last year. So the estimated amount of income taxes for year two will be 55. And finally if we want to get our final estimate what is forecasted net income In year two, let's just take our forecasted income before taxes, combine that with our forecasted income taxes, which is a minus number, and we get 73. Now I don't know about you, but to me this is amazing. Here's what we've done. Year two hasn't even started yet, but we sat down with our sales people and our marketing people and everybody within the company and they said we think we can make sales go up to $1,500 next year, a 50% growth rate. Then we sat down with all the operations people. What about our gross profit percentage with our profit margins and our purchase costs from our suppliers? Yeah, we can maintain the same gross profit percentage of 30%. Depreciation expense, yeah, it looks like it's going to be about the same. Our other operating expenses, they're going to track sales just like they have in prior years. Loans are going to stay the same and the interest rate is going to be 10%. like the tax rate's going to be the same so we made all those assumptions and now before year two ever started we have an estimate of what net income for the year is going to be 73 and it's not just a random number that we pulled out of nowhere we made a very careful forecast. Let's click to the next tab net income solution and that looks like it's exactly what we just did we did it step by step. That $73 forecast net income number reflects an 82.5% net income increase compared to year one. Let's stop and think about that for a second. Our net income is going to go up, what was it? 82.5%. And that's sales increased by 50%, but net income goes up by 82.5%. That's kind of a multiplier effect and how did that happen? Well, it's because we assumed that this huge increase in sales could be accomplished with no new property plant and equipment, so we had the same depreciation expense, and no new loans, resulting in the same interest expense. So we're learning a whole bunch about this company. Okay, let's do a little bit more. Go to this tab, two changes. Well, we worked hard to create that year two forecasted income statement. So let's use that same template to see the impact on net income of a number of changes. Now notice at the bottom here that now we're changing our forecast of property, plant, and equipment. We're saying that it's going to go up to 700 and we're also saying that loans payable are going to go up to 400. So it's a different set of assumptions. With this 50 percent sales increase we are going to have to increase property, plant, and equipment and we We are going to need some more new loans. So we've changed the assumptions. Sales increase is still forecasted to be 50%. We see that right here in cell I-4. OK, so let's look at the data in column H. And let's use these revised forecasts, reflecting different assumptions about how Sophie will do business in year two, to generate a new net income forecast. In cell I6, for now, we've got forecasted gross profit of zero. Well, that's not that great, but here in column H, it says we've talked to our people, salespeople, the purchases people, and they say we think we can get our gross profit percentage to go from 30%, which is what it was last year, to 32%. So let's put in that 32%. I'm putting that in cell I6. That's our new forecast. So that means we're expecting our gross profit to be $480 and we already had built in the formula to automatically compute cost of goods sold. We did that before at $1,020. So there we go, forecasted gross profit $480. What did we say it was before? Instead of $450, assuming the same gross profit percentage. So we're seeing the impact of these changes. All right, let's go down to the next cell, cell I-7. I put in here just as a placeholder that we're assuming our depreciation life is 10,000 years. Well, no, we don't think that our property, plant and equipment is going to last 10,000 years. It says here that our assumption is going to be the same life, 20 years, 20 years last year. Let's put in our forecast for year two. So I'll put in 20 and we have forecasted depreciation expense of 35. It's higher than it was last year because we're going to have more property, plant and equipment. It's going to go from 15 last year and we'll forecast it'll go up to 35. Beautiful. All right now we go to cell I-8. What should be in cell I-8? What are we assuming about other operating expenses? In this column H we're assuming that other operating expenses are going to go from 18.5 percent, which is what they were in year one to 20%. That's our forecast for next year. Sales increases don't happen by themselves. We're gonna have to increase, maybe increase sales commissions or increased expenses for wages. Who knows what it's going to be. Our people have very carefully revised the forecast and said that our other operating expenses are going to be 20% of sales. So let's put 20% in this cell I-8 and we see that our forecasted other operating expenses next year will be $300. That gives us forecasted operating profit next year of $145. Cell I-10 should include what we think the interest rate is going to be next year. Well our assumption is the interest rate will be the same. Same in year two as it was in year one, 10%. So let's put that in the yellow cell, which is cell I-10, 10%, and we get forecasted interest expense of 40. Wait, I remember before that forecasted interest expense was 30. Yeah, that's because before it was 30, but because we were assuming that loans payable were 300. Well, in this adjusted scenario, we're assuming that loans payable will be 400. More loans payable, more interest expense. Finally, we're at cell I-12 here. Tax rate, our final assumption, and we've got some somewhat startling news here. The tax rate is expected to go from 42.9% to 60%. Just put it in. That's what is expected to happen. That'll be based on our computed income before taxes of 105. So forecasted income tax rate is 60%. I put in 60%. That means our forecasted income taxes are 63, giving us total forecasted net income of 42. Now, as you can see, once the forecast framework is set up in a spreadsheet, a sensitivity analysis like this is easy. If you don't like the result of an estimate, such as the relatively low net income here, then you can go back and look at the assumptions. Just think about this. What we're saying is sales are going to go up 50% and yet our net income is only going to go up from 40 to 42. It seems hardly worth all the bother. And we can work back through. In this case, we'll find out that the biggest factor causing that is the increase in the tax rate, but with increase in tax rates we might want to change our plan. The beauty is we've done all this before the year ever begins, and so it's easy to change the plan if you put it in a spreadsheet first, tinker with it with some sensitivity analysis, and then make the changes that you think are necessary before the year even begins.