Learn how to calculate forecasted long-term debt and its effect on liabilities and interest expense.
- [Instructor] Let's keep knocking out some more of these line items in the balance sheet. I'm in 03_05_BS_Begin if you want to join me. And I want to turn my attention in particular to the long-term debt. That's our current portion in row 29 under liabilities. The amounts that we input here are the amounts owed as of the last day of each year. This is pretty straightforward. I don't imagine that the current portion of long-term loans will change too much. For the sake of having a little bit of flexibility, let's increase the amount 2% each year.
So to do that, I'm going to click on D29 for projected 2018, and I'll simply say equals Historical 2017 times 1.02. We're increasing it by 2% each year. So I'm going to go ahead and apply the same type of logic to my other years. And you'll see that the long-term portion is increasing little, by little, by little. Okay, so let's think about the long-term debt section while we're at it. For now, I'm just going to assume that other long-term debt in row 35 is going to remain the same over the years, so I'll just go ahead and fill that in.
Do-do-do, 1.08 for all the years across the board. Now, imagine that you have a bank loan of 1.20 million right here. The debt on the mortgage for your location. That's going to go in the bank loans payable in row 35. Check out C34. I already have that number inputted for you. Let's think about how that forecast is going to work. We calculated a current portion earlier in row 29, so I'll have to subtract the payable from the current for the following year to get the projected payable for the next year.
So, for example, if I'm calculating the bank loans payable for projected 2018, I'm going to say that's equal to 1.20 from the previous year, minus .45, which is the current part of this year. So I'm going to calculate that, and I'm going to do the same thing for 2019. So that's .75 minus the current portion for this year, and then the same thing for 2020. That's .29 from projected 2019 minus the projected current part for this year.
And that's going to put us a little bit in the hole there at negative .17. So, again, pro forma statements, not always perfect, so these things kind of happen from time to time. And we can make adjustments on that if needed. So now that we have this done, let's switch over to the income statement. That's 03_05_IS_Begin. First of all, it's really bothering me that we haven't filled in G16 right here, so I want to go ahead and work on that. What do you think the value should be here? Well, let's make it two divided by 39 for the depreciation on the building.
So I'm just going to simply type in equals two divided by 39. Great, now we have some depreciation for that particular cell. With that being done, we can now calculate our net operating income, which is our gross profit minus our total expenses. So we have our total expenses that were automatically calculated for us. So my net operating income is equal to my gross profit from cell G11 minus my total expenses at .17. So that will give me a net operating income of about half a million for 2017, and I'm going to go ahead and do the exact same kind of thing for 2018 and all subsequent years.
Yeah, I know, our net operating income isn't all that great. Oh well, Richard's is a growing company. We don't expect a whole lot of operating income. So let's just be glad that it's not a loss, and it looks like there's somewhat of a positive trend here from 2018 to 2020. And it looks like our cost of goods sold is growing incrementally in comparison with our net sales, so by 2020 we're expecting a net income that's back up to par with 2017 as we invest more and more in the business. Now that we have that taken care of, let's take a look at our interest income or expense.
At our current startup phase, we're definitely going to have interest expense. This interest expense is calculated by using the long-term debt values from the balance sheet. Let's use a percentage method and imagine that the interest expense is about 4% of the total long-term debt balance at the beginning of the year. Hey, it looks like we got a really great deal with interest rates. That's awesome. That long-term debt is for the bank loans payable, other long-term debt, and also the current portion of the long-term debt. We'll have to add those together and multiply by 4%.
So first let's take a look at our 2017 interest expense. From our balance sheet, which I'll go to here, it looks like we have a long-term debt of 2.28 here and a current portion of .44 here. So I'll simply add those up and multiply by .04. So on my income statement, I'll say equals 2.28 plus .44, and I'm going to put that in parentheses. And then I'm going to multiply that by .04, which is 4%.
So we get .11 million. That means we have about $110,000 in interest expense. Yikes, that's a lot of interest expense. But you know what, that's okay. We have to kind of take it with a grain of salt and say that we're a growing company. So I'm going to do the rest for 2018, 2019, and 2020 in a similar fashion. So, my equal, open parentheses, and I'm going to add 1.83 plus .45.
I'm going to multiply that by .04. So it looks like our interest expense is decreasing over time. Great. So it looks like our interest expense is coming down, of course, because our debt's coming down. Now, let's figure out income before taxes. This calculation is pretty simple. It's basically net operating income minus our interest expense. So in cell G24, I'll simply type in equals, and I'll take our net operating income from cell G20 and subtract that from our interest income in cell G23 to get my income before taxes, about $390,000 for 2017.
And I'm going to do that for our projected years as well. So about $190,000 there. About $370,000 there. And $450,000 there. So, as suspected, we are receiving more income before taxes year over year except for 2018. 2019 and 2020 are projected to be doing fairly well. Now, we're almost done with our income statement forecasting and also our balance sheet, so let's work toward getting this done so we have something to present to our potential investors and our current stakeholders.
- Explain the four different types of financial statements.
- Distinguish between the types of moving averages.
- Determine a seasonal adjusted trend.
- Break down pro-forma financial statements.
- Identify cash flows, and what increased liabilities and decreased earnings generally indicate.
- Tell what a regression is.
- Outline the naive approach.