Modeling Credit Scenarios Part 2 Workout
- 04:13
Modeling Credit Scenarios Part 2 Workout
Transcript
Picking up where I left off, I now need to create the same architecture for the international sales growth so I'm gonna go ahead and create some rows and I can just go ahead and copy these labels down here and I can go ahead and copy my international percentage growth label above. And I'm gonna do the same thing. I want to keep the architecture so that this international growth feeds into my income statement below. But I also want to acknowledge the fact that row 17 is no longer going to be my assumption row. It's actually going to be a result row. Now what I need to do is I need to copy this bottom row which is the international percentage sales growth and copy that up here because that's now gonna be the company case. And this row 17 is no longer gonna be an assumption row. So I'm just gonna copy the formats in from the white background, black font sales. It's gonna be alt HV S for special, T for format. And now that looks good. So I can go ahead and delete those for right now. And I also just want to copy down my assumption formats for the bank case and the downside case. Again, I don't have anything specific to go on here because this is a fabricated case. I just want to take a haircut. So I think what I would feel comfortable with is a haircut saying, let's just assume that this is 7% and then let's just assume the international growth is 3%. We'll copy them over as far as year five and then I'll bring my bank case down in line with the company case in the outer years.
And I will keep my downside case at 3% throughout. So the bank case where that's most critical obviously would be within the years of the bank loan which is one through five or six. And beyond that it's not that we're not, we don't care it's just that it's very far out. It's beyond the time period for which we would loan. Now, again, we need to put in our match, our choose and our match functions. So it's gonna be the same as this up here. And, in fact, we should actually be able to copy this because it's the correct row structure. It's taking the labels from above but that's okay because the labels aren't changing. We're still using these three cases. So I'm just going to for the sake of simplicity copy that from above and then copy it over. And now lastly, we need to do the same thing for our cost of sales.
This here, I'm gonna copy these three down and then I'm going to copy my company case above and copy that formatting down and then copy this formatting across.
For cost of goods sold, we see that it's equal to what it was last year but it has been going up each year. So for the bank case, something that we might want to consider is that it's still going up. And so we would keep that say at negative 62%.
And let's say for the downside case, we'll keep it at negative 64%.
And we'll copy these over.
And now again, we need to copy from above and it looks as though this is working. So now we have the architecture built in for the scenarios. We're just gonna go ahead and test them. So if I choose the bank case, it looks as though it's picking up the bank case properly. If I test the downside case, it's bringing in the downside case properly. One thing we might want to do is just build a label in here that says to be equal to this cell so that we know that we're in the downside case or the bank case. And just to check what's happening in the model. The active cells, which are the white cells in this case for cost of sales, is feeding my income statement below. And that's what we want.