3-Statement Model Case Study (30 Minutes) (Illinois Tool Works, Industrials, Manufacturing) (34:34)

In this tutorial, you’ll learn how to complete a three-statement modeling case study for an industrials company (Illinois Tool Works) under extreme time pressure – 30 minutes – and you’ll get the key tips, tricks, mistakes to avoid, and suggested completion order.

 

Table of Contents:

  • 3:47: Assumptions and Setup
  • 15:28: Income Statement
  • 18:33: Balance Sheet and Cash Flow Statement
  • 26:55: Finishing the Balance Sheet and Linking the Statements
  • 29:48: Checking Our Work and Case Study Responses

Transcript: 3-Statement Model Case Study (30 Minutes) (Illinois Tool Works, Industrials, Manufacturing)

Hello, and welcome to our first sample case study. This is a three-statement modeling case study and we’re using this company, Illinois Tool Works, which is an industrials or a manufacturing company. We’re given a set of assumptions here and we are given historical statements over the past three years as of the time of this case study for this company. Our task will be to project the three statements over the next five years shown in this Excel file.

This is a 30-minute case study. Now the actual tutorial here may or may not be exactly 30 minutes because I want to explain a bit about how to approach it and then also go over some of these bonus questions here if we get extra time. In real life, of course, the priority would have to be on finishing the case study, but here, we are going to answer these questions and explain some of the thought process behind what goes into this.

[00:57]

I have some notes over here on the right-hand side of the page in the finished version of this file about how to approach it. The number one mistake and the number one problem I see with these types of case studies is that people over think very small details that don’t matter much. This is a problem not just for simplified speed test type case studies like this, but also in a lot of more complex and open ended case studies.

I’ve seen a lot of focus on minutiae that makes no difference. Things like how you treat the pension liability or the pension expense, or the exact accounting treatment of depreciation, or what to do if certain numbers don’t match up on the income statement and cash flow statement. Focus on the company’s cash flow and how its cash and debt balances change over time. If you get those right, chances are you will get any type of case study correct.

So, the general approach that we recommend here is filling out the assumptions at the top, not obsessing too much about the specific numbers to use.

[02:01]

Then filling out the entire income statement, filling out the parts of the balance sheet that we can actually complete, fill out the entire cash flow statement and then go back and complete the rest of the balance sheet, and then check our work, and answer the case study questions at the end.

Now the reason why we recommend this order is that with these types of speed test case studies, you don’t want to be jumping around between different sections if you can avoid it. Now sometimes you can’t avoid it because with the balance sheet, for example, you almost always want to come back and fill this out last to make sure that the balance sheet balances and that your work is correct. To do that you are going to need elements of the cash flow statement first. So, you cannot always follow this exact order, but you should try to do it as much as you possibly can.

So, what that means is that in the assumptions section, you don’t want to be filling in items here and then jumping down to the income statement or balance sheet, or cash flow statement to link in these items. You want to do everything up here in one fell swoop first.

[03:00]

Then when you’re done with everything in this area, go down and move through the statement sequentially as much as you can.

What makes this case study a little bit tricky is that they’ve given us some of the assumptions for Capital Expenditures, for example, for Stock Repurchases. Even for other items like the Amortization of Debt, the Revenue Growth, SG&A as a percent of revenue. So, we have some assumptions, but then we have to make our own judgment and fill in other assumptions for the ones that have not been provided to us. So, that’s what we’d say overall.

Again, I have my notes over here on the side you can refer back to or look up if you want to get an overview of how to complete this case study. But with all that said and out of the way, let’s now jump into this and fill out everything here starting with the assumption section up at the top. Don’t waste time with filling out the historical revenue growth here because it’s completely irrelevant. All we need is the future revenue growth which we already have.

[04:00]

Now, the gross margin assumption we do need to fill out. Because here, the issue is that we’ve been given an Annual Gross Margin Improvement, but we haven’t been given the historical numbers. So here we need to go down and take Gross Profit and divide by Total Revenue. I’m using a plus sign in front. You don’t have to do it if you don’t want to. It’s just a stylistic preference. This is an example of the type of thing that you should not spend much time thinking about or you’ll never finish these case studies. We’ll copy that across.

Then for the Gross Margin Improvement, we can just take the old number and add 0.3% to it and copy that across. Don’t worry about using an average or doing something more complicated. Keep it very simple and do not overcomplicate a time pressure case study like this one. With SG&A as a percent of Revenue, similarly it would be a huge waste of time to go in and try to get the historical numbers here. We don’t need them. All we care about are the future numbers which have already been provided to us.

[04:59]

Amortization of Intangibles, Impairment of Goodwill. Similarly, we already have these numbers. We don’t need to do anything. Now with the Amortization of Debt, this may seem a little bit unusual. Why are we going in and filling this out? The answer is that we want to finish all these assumptions so we’re not jumping back and forth, that leads to errors and that leads to incomplete parts of the model.

So here, I am going to go down and take our beginning debt balance from the company’s balance sheet. Then for the Amortization, we can take the 10% and multiply by this number right here. I’m going to anchor this because we want 10% per year. Yes, in real life it could very well be different from this, but again, this is an example of a simplification we might make. You might be looking at this and saying, “Well, what about a minimum formula? Because in an LBO and other debt models, we have to check to make sure that we don’t amortize more than the total amount.” But look at these numbers, 10% over 5 years only adds up to 50%. So again, it’s just not a good use of time to add in those bells and whistles in this type of speed test.

[06:04]

So, let’s just add up everything here and for the beginning balance, we can take that down, copy everything across with control R, and so we have that.

For the Interest Expense, let’s keep it simple and just use the Beginning Debt Balance times the Interest Rate on Debt of 4% right here. Then for the Interest Income, we don’t have the cash balance here. However, we do have the beginning cash balance from this year or the ending cash balance from the last historical year, however you want to think about it. We can multiply that by the interest rate on cash of 0.5%.

This is going to be blank because we don’t have the cash numbers, but that’s okay. We’re going to come back later on and when we finish everything, this will be completely filled in. We’re using the beginning balance here because if we run into an error or a problem with the model, it’s going to be much harder to fix if we use an average balance because then we get circular references.

[07:01]

So, it’s another example of a simplification that you make in these extreme time pressure case studies and modeling tests. It’s not going to make a huge difference and it’s going to make our lives a whole lot easier later on.

Now if the instructions have told us explicitly that we need to use the average balances, then okay, sure, we might do that or we might save it to the end and change it, or something like that. But we were given no such instructions. We were given almost nothing in the way of instructions actually so it’s perfectly fine to do this. If they ask us about it later on, we can just explain that in a time pressure case study, you want to make your life as easy as possible by avoiding possible problems like this one.

We have nothing else to fill in for the Income Statement Drivers, so let’s go down to the Balance Sheet Drivers. With this part, we are given almost no instruction. The only point here that they really tell us about is that some of these items should be percentages of SG&A Revenue and then others should be projected based on the number of days, Days Sales Outstanding, Days Sales of Inventory, Days Payable Outstanding.

[08:05]

So, for the Days Sales Outstanding, this is just going to be the Accounts Receivable divided by the company’s revenue times 365, copy that across. For the Days Sales of Inventory, we are going to take their inventory and divide by Cost of Goods Sold and multiply by 365. For the Days Payable Outstanding, we are going to take the Accounts Payable and the typical assumption is to divide it by Cost of Goods Sold, probably perfectly valid for an industrial company like this one. We have all those assumptions.

At this point, we should probably finish filling in all this historical data because once again, we don’t want to be jumping between schedules too much. So, if we’re already filling out the historical data, let’s finish doing that and then when we’re done later on we can fill in the future assumptions over here.

[08:59]

So, for the Prepaid Expenses and Other as a percent of SG&A, let’s just take this number and then we’ll divide by the SG&A figure right here. It’s in cell E103. This may save us a bit of time later on. I’m going to actually going to copy this right now, so we don’t have to keep jumping back and forth as much as we get to the other assumptions that are also based on SG&A.

Then let’s take the Other Long-Term Assets right here and divide by the company’s revenue. Then for Accrued Expenses as a percent of SG&A, let’s go down and once again, I’ve copied it to save some time, so I just pasted it in there. Then Other Current Liabilities and Other Long Term Liabilities are both going to be percentages of SG&A. So, let’s take this one and then . . . and I copied that incorrectly by accident. Then Other Long Term Liabilities as a percent of SG&A, let’s go down and copy and paste that in once again. Then let’s just copy across everything here with control R, so we have that.

[09:59]

Now for these future assumptions, the key is that you really don’t want to over think things. The main point is that if an item jumps around a fair amount, then usually you want to use an average. Or if it doesn’t jump around, if it follows a clear trend, then you want to reflect that trend and continue it over time. One trick for these questions is you can just enter a number and see how the cell is color coded, and if it’s black, it’s a pretty good indication that you should use an average. Because if it were blue instead, it would mean that it’s a hard-coded cell and that you want to continue some type of trend there.

So, for Days Sales Outstanding, we can clearly tell that they want us to use an average for this one. For Days Sales of Inventory, here, there’s been a bit of a following trend over time, so we can say 50, 49, 49, 48, 48. For Days Payable Outstanding, again, it’s in blue, we can say 20, 19, 19, 18 and 18, reflecting the following trend.

[10:58]

For Prepaid Expenses and Other as a percent of SG&A, we have one big percentage here in historical year but that seems to be an outlier. The other ones are much lower. It seems like it’s falling by right about 1% per year. So, we can say 13%, 13%, 12%, 12%, 11%. We don’t want to rock the boat too much, so I’m making this decline slowly over time. Other Long-Term Assets as a percent of SG&A, here we can just start entering the number, we can see that it’s black. So, we’re going to use an average for this one because it’s jumped around a little bit within a pretty tight range here. So, we have that.

Then for Accrued Expenses as a percent of SG&A, we can see that once again, we have a black cell and this one has jumped around a fair amount, so we’re going to use a simple average. It’s been a pretty tight range, so that seems like a safe assumption. For Other Current Liabilities as a percent of SG&A, it’s been around 10% the past year, so we can keep it at 10% going all the way across.

[11:55]

Then for Other Long-Term Liabilities as a percent of SG&A, we do have an increasing trend here. However, if you start entering numbers in the cell, you’ll see that you get black for the color. So, we’re just going to use an average here. With all these assumptions, what I would emphasize is that you don’t want to over think them and I have something up here in the notes. Often times in real life, you will check the working capital trends. So, when you get done projecting all these items, you’ll say, “Let’s look at the change in working capital and see how it compares to the change in revenue or to revenue and make it a percent of revenue,” or something like that. You can use those tricks to sanity check what you’ve done.

In this case, we just don’t have the time and we can’t put in that much effort if we’re only given 30 minutes to do this. So, we are just going to go with pretty simple assumptions here and we’re not going to worry too much about getting it exactly right. Who knows? Maybe this assumption about Other Long-Term Liabilities as a percent of SG&A is off, maybe it should be increasing actually over time.

[12:57]

But we’re just not going to worry about it too much because it’s going to make a pretty small impact on the model overall and it’s not going to affect our conclusions at all. So, let’s keep going down.

Capital Expenditures were given, no need to do any extra work there. Depreciation as a percent of Revenue. Let’s go down to our cash flow statement and get this and divide by the revenue right here. While we’re at it, let’s go down and get a lot of these other numbers such as Stock Based Compensation as a percent of Revenue. Deferred Taxes as a percent of Taxes. You have to be careful here because taxes are shown with a negative sign on the income statement. Normally you want to look at these as a positive portion of the company’s taxes, so we’re flipping the sign right here. It doesn’t really matter what you do as long as you keep the signs consistent though.

[13:53]

Then for Dividends as a percent of Net Income, same issue. Normally you want to look at this as a positive percentage, but dividends are shown with a negative on the cash flow statement. So, we’re going to flip the sign negative and make it a percentage of net income right there.
Stock Repurchases, we already have. Other Non-Cash Items and all these other items, we already have. So, FX Rate Effects as a percent of Revenue is really the last one here. This one, the sign doesn’t really matter, but it’s clearly negative historically, so we probably want to continue that trend going forward. So, we have that.

Now, for all these numbers going into the future, for Depreciation it dips a little bit, but the company is clearly spending more on CapEx going forward. We can reflect a bit of a dip here, CapEx actually stays the same in the last historical year in the first projected one, and we can make it rise gradually over time. So, we have that. Stock-Based Compensation as a percent of Revenue, we could really just take a simple average here because the numbers are staying in almost exactly the same range.

[15:00]

For Deferred Taxes as percent of Taxes again, I would say an average is probably the best approach. It’s jumping around. We don’t know clearly why any of this is happening. Dividends as a Percent of Net Income, we’d say an average is also appropriate right here, given that the number drops and then rises again. All these others we already have. FX Rate Effects, once again, we can take an average here. I’ll just copy my formula down and so we have that.

So, we’re done with Part 1 and we have all our assumptions set up. At this point in real life you would be about 10 minutes or so into the case study. The timestamp in this video is beyond that. But that’s because I’ve been giving explanation and I spent a few minutes in the beginning explaining what to do, and giving commentary around other things. So, with 10 minutes left, your goal at this point would be to finish everything else here in 10 minutes so that you would give yourself some time at the end to check your work and to possibly go back in and answer those case study questions.

[15:55]

So, in real life, at the 10-minute mark, you would now go to the income statement, fill out everything here. Then fill out what you can on the balance sheet. Fill out what you can on the cash flow statement, which should be everything at this stage, and then go back and flush out the balance sheet. If you do it quickly, you should be able to do all that in 10 minutes or less. So, let’s go through and see how we can do that.

For Total Revenue, let’s take our old number and multiply by one plus the Revenue Growth Rate right up here. For Cost of Goods Sold, let’s take our Total Revenue and multiply by the Gross Margin. Except it should actually be one minus the Gross Margin because the way we set this up. For Gross Profit, Total Revenue minus Cost of Goods Sold. Notice how I’m filling out everything in this one column here to save time. We don’t want to be copying and pasting things across quite yet. We want to fill out everything in this one column and then copy and paste this all the way across. It seems like a small detail, but in extreme time pressure case studies, it’ll actually save you a lot of time.

[17:00]

For SG&A, we already know this is going to be Percent of Revenue. So, let’s take our Revenue and multiply by the percentage up there. Amortization of Intangibles, we already have our number. We know Goodwill is right below that, so we can add these up. Operating Income is just Gross Profit minus these Operating Expenses. The margin, it seems like we actually need a margin, let’s take this and divide by Revenue. For Interest Expense, we have it up here and then the one below it is for Interest Income. We have that, let’s add those up.
Pre-Tax Income is just our Operating Income plus our Total Other Income or Expense, so we have that. Then for taxes, we can take our Pre-Tax Income and multiply by the Tax Rate, Effective Tax Rate of 30% right there. We can add these up. We have nothing for Income from Discontinued Operations, so let’s link up here.

[17:59]

Then Net Income we can take our Income from Continuing Operations and add Income from Discontinued Operations, and we have that. So, it was very quick, but I am trying to illustrate the speed at which you have to complete these types of case studies in real life.
Let’s highlight everything, copy it across with control R to save time. Once again, quickly checking our numbers here, we see our Operating Margin increases over time. Which is what we’d expect because we know our Gross Margin is going up and we know SG&A as a percent of Revenue is going down. So, we’d expect our Operating Margin here to increase and we have that.

Let’s now go into the next step and start projecting the balance sheet. For accounts receivable, this one, remember is projected based on Days Sales Outstanding, so let’s divide by 365 and multiply by the company’s revenue. For Inventory, let’s take our Days Sales of Inventory, divide by 365 and multiply by Cost of Goods Sold right there. The formatting isn’t getting messed up. In real life, you would not even bother to fix that. I just fixed it here to make the formatting a bit cleaner in the finished version.

[19:05]

For Prepaid Expenses and Other Assets, this one, if you go up, is a percentage of SG&A. So, let’s take that and multiply it by the SG&A right here. Once again, I’m going to copy this part, the each one of three because it’ll be useful to us later on and it’ll save us a bit of time as we go through the rest of this exercise. Net PP&E, we cannot project. Goodwill, we can’t project. Other Intangibles, we can’t project. Other Long-Term Assets, we can actually project.

So, for this one, let’s go up and get our Other Long-Term Assets as a percent of Revenue and take that down. At this point we have most of what we can project on the asset side, so let’s just add up everything here and copy across these numbers. Then do the same thing on this side.

[20:01]

I add up Total Current Assets and the Non-Current Assets right here to get to our Total Assets.

Let’s move to the other side now, Accounts Payable. Once again, this is projected based on Days Payable Outstanding. So, we need to take this, divide by 365 and then multiply by Cost of Goods Sold down here. As we’re doing this we can also quickly check our numbers and see that. For items like Accounts Receivable and Inventory Accounts Payable, these should really be staying at about the same range over time because the company’s COGS and revenue don’t change by that much each year. So, if we got some number here that we’re really, really off from the historical numbers, we’d know that we probably made a mistake.

Now for Accrued Expenses, let’s go up and this one is a percent of SG&A, remember that I copied that H103 part before so that we could save a little bit of time with setting up these types of formulas, and that’s exactly what I just did.

[21:01]

Then Other Current Liabilities, let’s go up and multiply this by that line. I copied across Accounts Payable there, but actually I probably should have left it until we get to this part. We have everything here filled out and now we can copy this across. Long-Term Debt, we will leave it blank for now because we want to link that to the cash flow statement. Deferred Tax Liability, same issue. Other Long-Term Liabilities, we can actually fill out, so let’s go up. This is also going to be a percent of SG&A and we’ll copy this across. We can also set up this formula for Total Liabilities, copy that across. I probably should’ve combined that with the last step to save a bit of time, but it doesn’t really matter. Total Liabilities, we can just take our Total Current and Total Non-Current Liabilities here, copy this across.

[21:59]

Equity, we will leave blank for now. Total Liabilities and Equity, we will leave blank for now. Let’s move on to the cash flow statement and finish everything here, and then go back and link everything on the balance sheet.

So, for Net Income, let’s go up and link to our income statement. I’m not going to copy anything across quite yet. For Depreciation, this is going to be a simple Percent of Revenue. So, we take our revenue here and we multiply by Depreciation as a percent of revenue. Impairment of Goodwill and Amortization of Other Intangibles, these are both going to come from the income statement, so we have that.

Then for Deferred Taxes right here, we need to flip the sign. So, let’s take our provision for income taxes and multiply by Deferred Taxes as a percent of taxes. Stock-Based Compensation is also going to be linked to revenue. So, let’s take our revenue and multiply by the Stock-Based Compensation right there.

[22:59]

Other Non-Cash Items, this is just a hard coded zero, so let’s go up and link that in. Then for all these working capital items, the rule is that if you’re dealing with an asset, you take the old asset and you subtract the new asset. If you’re dealing with a liability, then you take the new liability and you subtract the old liability. Because when an asset increases, it will reduce your cash flow. When a liability increases, it will increase your cash flow.

So, for accounts receivable, let’s do that. Inventory and Prepaid Expenses are right below, so to save some time, let’s just copy those formulas down. Accounts Payable, Accrued Expenses and Other Liabilities. Accounts Payable, we can copy all those down. Now one thing to be careful of here is that when you do this, you want to make sure that you’ve factored in all the working capital items. So, in other words, all the balance sheet items that are linked to something on the company’s income statement. Here we’ve forgotten to link in other long term assets, so let’s go in and factor that in as well.

[24:00]

Then for Other Liabilities, we have Other Current Liabilities, but then we also have Other Long Term Liabilities, so we want to factor that in as well. One quick way to check yourself with this is to go through everything here when you finish this partial balance sheet and make sure that you actually have links to all the items that you filled in so far on the balance sheet. If you don’t have those links, then you’ve probably done something wrong.

Sometimes in real models, you will create highlights and use yellow highlighted backgrounds to track this. I think it’s a waste of time in a case study like this under extreme time pressure.
So, the better solution is to fill in what you can and at each step of the way, like we’re doing here, make sure that you’ve properly filled in everything in the section and linked it.

For the Net Cash provided by Operating Activities, let’s take everything here and then copy this across with control R, so we have that. Capital Expenditures is easy because we already have all the numbers they want to use.

[25:02]

Other Investing Items, we know this is just going to be set to zero, so let’s copy that across. Dividends Paid, this is going to be a negative and it’s going to be a percent of net income. Stock Repurchases, we will go up and link to the negative 1,000 or to the 1,000 and make it negative rather. Then for the Debt Issuances and Repayments, we have no debt issuances here. So, what we have to do is go up to where we projected the amortization and link in this amortization which is a negative right here. Then Other Financing Items is just set to zero per the assumptions up here.

With all these set up, we can now copy across everything in the financing section. Then for the FX Rate Effects here, we can take this as a percent of revenue, multiply by revenue.

[26:04]

For the Change in Cash and Cash Equivalents, we add up cash from Operating Activities, Investing Activities, Financing Activities and the FX Rate Effects. For the Beginning Cash, we just take the Ending Cash from the prior year, then we add these up and then we can copy across this whole area. So, we have that. You could have saved some time here by not doing exactly what I did, and waiting to copy across some of these instead. But that is just the method that we use in this case.

I should also fix the sign on Capital Expenditures that pops out as an error. Looking at the rest of these, the signs all seem to be correct. The Debt Issuances and Repayment stands out as a little bit unusual, but that’s just because we’re assuming the company doesn’t issue any debt in these projections. The case studies didn’t tell us to, so we left it out completely. So, with all that set up and in place, let’s now go back to the balance sheet and fill out what we can.

[27:00]

Cash is going to come from the very bottom right here, the ending cash balance. Then everything else, we can take the old number and then the rule is that, when you’re on the asset side, you subtract links on the cash flow statement. When you’re on the liabilities and equity side, you add links. So, for Net PP&E, we can go down and we can take our Depreciation, subtract that and then subtract Capital Expenditures, and then subtract Other Investing Items and we have that. Goodwill, the only item that should link in is the Goodwill Impairment right here. Then for the Amortization of Intangibles, we take the old number and we subtract the actual amortization right there to get our new number. So, we have that.

On this side, everything in current liabilities is complete. For Long-Term Debt, we’ll take our old number and then we’ll just factor in the Debt Issuances and Repayments here.

[28:00]

We’re on the Liabilities and Equity side, so we’re going to use plus signs and we’re going to add all these links on the cash flow statement. So, we have that. Then for the Deferred Tax Liability, we took our old item, once again, we’re on the Liabilities and Equity side, so we’re going to add anything on the cash flow statement, we’ll get our Deferred Taxes right there. So, we have that.

Now, Equity in these types of case studies is typically the item that you use for everything else that doesn’t fit in elsewhere. So, don’t think too hard about where to put different items. If you don’t know where to put something, put it under Equity and that’s the simplest way to complete this linking exercise.

So, take the old number, we will add Net Income. We’ve already linked in Deprecation, Impairment, Amortization, Deferred Taxes. However, we have not yet linked Stock-Based Compensation and other Non-Cash Items. So, let’s go and link those. All our balance sheet links are already correct. We’ve already linked the Investing Activity section.

[28:58]

We need to link in Dividends. We need to link in Stock Repurchases. We’ve already linked our Debt Repayments. We haven’t linked our other financing items, so let’s include that. Then let’s include FX Rate Effects and that should give us everything on the balance sheet. We have that. Let’s add up our Total Liabilities and Equity right here. Then for our balance check, the moment of truth and our balance sheet here balances. So, we have that and we’ve completed this process of linking our statements.

It took me a little bit more than 10 minutes to do that but that’s because I was explaining what I was doing along the way. In real life if you were fast enough at Excel, you could probably do this in well under 10 minutes. You might even be able to do it in five minutes depending on how fast you are or who knows, maybe even faster than that.

So, we’re done and at this point we could go in and check our work, we can see that our balance sheet balances. Other things to check would be the trends for the company’s margins over time. We can see their EBIT margin is going up for the reasons we mentioned before. Interest Expense is going down because the company’s repaying debt.

[30:04]

Interest Income is also going down because the company’s cash balance, as you can see here, is falling to very low levels by the end of the period. Other things to check would be the direction of cash flow from operations, which is generally increasing here. As we’d expect, margins are going up, working capital isn’t really changing, items like Depreciation and Amortization, and Stock-Based Compensation as percentages of Revenue aren’t changing by that much. So, if the company’s margins are increasing and all those other conditions are true, we would expect their cash flow from operations to also increase.

Now, if you look at Free Cash Flow, so Cash Flow from Operations minus Capital Expenditures, this is also increasing. CapEx does go up, yes, but as a percent of revenue, it’s not really changing by that much. So, as we’d expect, if a company is growing and its margins are increasing, then cash flow from operations and free cash flow and other items like that should be going up.

[31:02]

If you want to go up and answer those case study questions then, for the main reason why the company’s cash balance changes the way it does over the five years, it’s pretty simple. If free cash flow is going up and cash flow from operations is going up, but cash is going down, something must be happening in the financing activities section. Sure enough, it is. Specifically, the company keeps repurchasing stock and keeps repaying debt without issuing any new debt. That’s not necessarily a problem, but what it does mean is that the company probably needs to issue additional capital to keep funding these activities.

It probably makes the most sense to issue more debt given the low funding costs for debt. We don’t know what their cost of equity is, but cost of debt is almost always below the cost of equity. Clearly this type of company, given the low cost of borrowing and other factors, can afford to take on more debt. So, we would say that’s probably their best option.

[31:58]

Another option would be to cut the Stock Repurchases here and if you do that, then you can see that their cash balance actually goes up by quite a bit toward the end. So, that’s one option. Cutting Dividends is probably not quite as plausible because they’ve been issuing dividends for a long time historically and investors have probably come to expect it. Whereas Stock Repurchases are often viewed as more of a one-time event. So, in this case, we think issuing more debt is probably the easiest way to deal with this issue of the cash balance falling to a very low level.

So, those are the brief answers for questions numbers one and two. If you look at the finished version of this model, I have written answers for those there as well. Then this last one, would it benefit the company more to focus on sales growth or margin improvement? What would you recommend to management? This type of company is already growing pretty slowly, 3 to 5% sales growth per year, and most of its expenses are in the form of Cost of Goods Sold and SG&A, both of which are tied to revenue.

[32:59]

It appears that the company has almost no fixed costs. Given that scenario, we would say that improving margins is probably better than boosting revenue. One way to think about it is that if the company’s revenue growth goes up by say 1% in the second year here, their net income increases just a little bit. It goes up from 2,169 to 2,193. But if the same company, instead of getting that point of revenue growth, can instead boost SG&A as a percent of Revenue or rather reduce SG&A as a percent of Revenue by even 1% right here, their net income goes up by a whole lot more.

Now this isn’t conclusive evidence and usually when you have a company like this, that always tends to happen. But the point is here that if a company has primarily variable costs, it’s probably best for it to focus on improving its margins, especially when it’s a very low growth company like this one. So, that would be our short advice to management.

[34:04]

That’s it for this case study. I know I’ve gone on for longer than 30 minutes here, but that was intentional because I wanted to give you an explanation in the beginning and point out some things as we went along. If you have any questions or you want to look back at anything here, take a look at my notes on the side of the page. But I hope you have a better idea of how to answer these time pressured case studies now that you have an example of an actual walkthrough of someone completing this type of model in a relatively time-pressured setting.

How to Download These Files:

  • In Firefox, Chrome, and Internet Explorer:
    Right click the link and click Save Link As...

    Save link as...
  • In Safari:
    Right click the link and click Download Linked File As...

    Save link as...