Custom Formatting

[00:00:00.00] [MUSIC PLAYING]

[00:00:08.73] IAN SCHNOOR: Welcome back. In the last video, we had a chance to look at the
assumption page. And we talked about the fact that a powerful, well-designed assumption page
allows you to clearly communicate all the ideas and inputs that you used to generate your
forecast. In this lesson, I want to share with you a really important and powerful technical skill
that allows you to build some of the numbers that you may use on the assumption page or even
in other places within your model.

[00:00:40.11] Let's go take a look in the model at what I'm talking about and then give you a
chance to build it up yourself. So I'm going to go ahead here and share my screen. You can see
here, I'm back in the model. And I'm on the cover sheet. I'm going to go to the Assumption tab.
Talked about this in the last video.

[00:00:57.87] As I roll down, I want to point out this particular box, the depreciation box. You
can see here that for this company, Blu Containers, this company uses straight line depreciation.
That's the methodology they use to depreciate their assets. You can also see that they are
assuming that they have 25 years left to depreciate their existing assets. And any new assets that
they purchased will be depreciated over 30 years.

[00:01:28.54] It's very clear. Self-explanatory because it says it. It says 25 years. It says 30 years
right in the cell. So there's no guessing. You know exactly what these numbers mean. But let's go
into my notes file because I want to show you exactly how you can do this. This is a skill you
can use in any spreadsheet that you ever build but particularly in models. So we're going to talk
about a really great feature called custom formatting.

[00:01:54.06] And as I just mentioned, it is often very, very helpful for values in your model to
have some text at the beginning or the end so that the values are clear. As example, we just
looked at the fact that for depreciation, it told us that the assets were being depreciated over 25
years, or the other cell, the next cell, said 30 years. It's possible that, let's say, for receivables,
accounts receivable, you may be told that accounts receivable, the assumption is that they will
collect in 40 days. I'm just making it up.

[00:02:30.06] You may have been told that there's a certain ratio, a debt covenant. And a certain
ratio has to be held, maintained, let's say, below four and a half times. So let's pretend you found
out that the bank has told the company that a certain ratio has to stay below four and a half times.
You might type this in. It would be helpful to see that it. You might know that a piece of debt
carries interest rate at a certain benchmark plus 400 basis points. So here, I've typed in 400 BP.

[00:03:01.53] I think you would agree, each of these cells is very, very clear. I would like these
cells to be inputs, so I made them blue as inputs. But there's a big problem. What's the problem
with each of these cells, 25 years, 40 days, et cetera? Well, obviously, the problem is that these
values all contain text. They are text values. I'll type it in here. They're text. They cannot be used
anywhere in your formulas in your model.
[00:03:32.70] They're not helpful. They're just dead. They're just dummy cells. They're clear to
the reader, but they're not usable. So obviously, the value I showed you in my model, I didn't
type this in. I didn't type in 25 and then years because then it's not usable at all in a formula later
on. So let's take a look at this. So what did I really want in the cell? Well, the only thing that I
really want in a cell would be-- let's say I wanted a debt covenant, a certain debt ratio covenant
that's less than four and a half.

[00:04:06.57] The only thing I actually want in that cell would be the number of 4.5. By the
same token, if a company depreciates its assets over 25 years, the only thing that should be in the
cell is the number 25 because now it's a number. Now these are numeric values. I'll center them.
Of course, I can use these in formulas. So these, of course, these two are numbers. They are
usable in formulas.

[00:04:34.38] But the problem is-- what the problem is, that they're not clear. I'll type this in
here. These two values are not clear. It's not obvious what they mean. If I told you that this
particular cell was a particular debt ratio that a company had to maintain, you wouldn't know.
Does it mean they have to stay less than four and a half? Is it less than four and a half? Or is it
greater than four and a half? I don't know. It's not obvious.

[00:05:01.35] And if you were just told that this was-- I'll type in Dep'n. That's often a shorthand
for depreciation. And if this was a certain debt covenant-- if this was a certain debt covenant, I
don't know exactly what this means. And if you were told that depreciation was simply 25, you
wouldn't know. Is that 25 years? Is it 25 days? Is it 25%? I don't know. It's not clear.

[00:05:30.05] And as a result, I want to share with you a powerful way to get the best of both
worlds. What we're going to do is we're going to put the 4.5 into a cell. And I'm going to put the
25 into a cell. But I'm going to use custom formatting now so that it's clear. And what we do is
we're going to put our cursor on top of this cell. And I'm going to press Control and the number
1. We looked at this previously. I'm going to go Control-1. That opens up the Formatting Cells
dialog box.

[00:06:01.22] And I want to go here, to the Number tab. So I can press Control-Tab to move
between the tabs. Or you can use the arrow keys. If you see the Number tab is highlighted with a
box around it like this, you can use the arrow keys. Might also work. And on the Number tab, I
want to go to the very bottom here where it says Custom. I can click Custom, or I can press Alt
and the letter C. By the way, these Alt keys only work on a PC, not a Mac.

[00:06:32.34] So I'm going to press Alt and the letter C and then jump to the bottom. Use your
mouse if that's all that works. That's great. I want to go to the Custom option here. And you're
welcome to do this in a blank sheet with me. And then later, we'll do this in the model. So I'm
going to click Custom. And now I want to show you the basics. The basics are as follows. When
you build your own custom number format, you'll either type into this skinny row here, where it
says Type. You'll either type in a 0 or the number sign, the pound sign.

[00:07:02.72] And you can see, when you look down, some of the samples that are here, there's
lots of 0's, and there's lots of pound keys. Each of them represents a placeholder for a digit. So if
you want a value with one decimal spot, you can either type in 0.0-- 0.0 means you want a
number with one decimal spot. You can see it's showing me what it will look like at the top. It
will now look like 4.5 because I typed in 0.0.

[00:07:31.31] Or I could have typed in pound dot pound. It's the same thing. Now, there's a slight
difference. You can take a look at that on your own. There's a slight difference between the 0 and
pound. Not going to get into that right now. What I want you to see is that if you type in 0.0,
you're saying that you want a number with one decimal spot. That's very easy. Well, the rest is
very simple. If you ever want one letter, meaning one character, one text character, at the
beginning or end, you just type it in.

[00:08:00.96] So I can say less than 0.0, and then I'll put an x. Do you see that? Less than 0.0x.
And when I press Enter now, watch, please, what happens. This cell looks like it says less than
four and a half times. Now it's very, very clear to the reader what it means. But the only thing in
the cell is the number 4.5. You can look at it in my formula bar. You can look at it in the cell.
The only thing here is the number, but it looks like it says less than four and a half times. It's
very, very clear.

[00:08:34.81] You could have also added a space. I could have said less than and a space, 0.0,
and then a space and press x. That should work. In some regions of the world, it may not work to
have a blank space. So if it doesn't, you'll have to remove it. I'll show you what I mean in the
next example. But here, I've now adde-- I said less than a space, 0.0, and then another space and
then my x. It now looks like it says less than four and a half times.

[00:09:06.95] Let's look at it one more time. I simply put the piece of text in the little row here
that says Type. That only works-- this methodology works if you want one letter before or after.
But what if I want to put the word years at the end? Well, obviously, I can't type it in. That
makes no sense. I just shared that with you. So what I'm going to do now is I'm going to go back
into my Control-1, back into my custom formatting.

[00:09:33.63] And I'm going to type in a 0 and the word years. But if you simply type 0 and the
word years, it won't work. And as you see, it doesn't show me anything. It doesn't understand yet
what I'm trying to do. If you want more than one letter after your value, you must surround it
with quotation marks, the double quotation marks. So now you can see, if I type in the word
years surrounded by quotation marks, it's going to look like 25 years.

[00:10:03.76] And when I click OK, take a look. It looks like it says 25 years. But the only thing
actually in this cell is the number 25. So for both of these, I can use-- I can use these, can use in
formulas. These are usable in calculations because the only thing actually in the cells are numeric
values. But I custom formatted the text at the beginning and at the end.

[00:10:29.20] Now, as I mentioned a moment ago, for some of you around the world, it won't
work if you have this space after the 0. So in my computer, it does. In mine, it will work just
fine. In some of your versions of Excel, you may not be allowed to have a space. You'll have to
put the space inside the quotation marks, and that works just as well. So that is the basics. That's
the premise. It's the basics. Very, very powerful because this is the best of both worlds.
[00:10:59.27] It's clear on the screen, and it's clear on paper. But the only thing actually in the
cell is a numeric value, so you can use it within your formulas. I strongly encourage you to do
this in all your spreadsheets but in particular, all of your models. Reminder, these ones are text.
They're not usable anywhere. All of these are text, but they look good. These ones look good, but
they're not usable. These ones are usable, but they're not clear. And these ones are both. They're
numeric values and clear to the reader. The number is still a number, but you can use it in a
calculation.

[00:11:36.61] Let me show you one last tip here that I think you'll like before we wrap up this
lesson, and that is this. What you may not-- you might have seen that. Some people are aware of
that, when I show it. But what you might not have seen is that every single cell in a spreadsheet
can be formatted four different ways. Yes. Every single cell can have four different formats. And
it works as follows.

[00:11:57.76] You can tell every cell how to format a number if the number is positive. Then you
can tell it how to format that same value, that same cell, if the number in the cell is negative. You
can tell it what to do if the value is 0 or if the cell contains text, always in that order, positive,
negative 0, text. So for example, let's pretend you wanted positive numbers to have a plus sign.
Don't ask me why, but let's pretend you wanted positive values to have a plus in front.

[00:12:30.67] And let's pretend you wanted your negative numbers to be surrounded by
parentheses. So you wanted a negative value to be surrounded by parentheses, like that. And by
default-- and now we're into the 0 section. By default, when you enter 0's in Excel, you see 0.
But let's pretend you wanted your 0 values to be a dash. Some people don't like seeing 0's. They
want a dash on the screen instead.

[00:12:55.30] Look what I can do. I will type in a number here. Let's say it's 65. I'll make it blue
here just for a second. So let's go in and build our own custom number format. Here's how it
works. I am going to put a plus sign in and then 0.0. That means if there's a positive value, look
what it's doing. It will put a plus in front. Now I put in a semicolon. In most regions of the world,
this should be a semicolon. And I'll put in a semicolon, and that locks it in.

[00:13:23.55] Now it wants to know, how would you like it to format a negative number? Well,
I'm going to put in parentheses, open parentheses, 0.0, and then close it off. So now what I've
done is I've put in-- I've told it how to format a number if there's a positive value in the cell. And
I just told it how to format a value if there's a negative value in the cell. For the third section, I
am going to simply type in a dash, like that. This is the 0 section, the third section. Now I'm
saying, if there's a 0 in this cell, display for me, please, a dash.

[00:13:58.50] Finally, the very last section says, what should we do if we see text? And I'm
going to put a note that says-- put it in lowercase-- this cell should contain a value. Do you see
that? I've put in a note. It says this cell should contain a value. So if it sees any text, it will
deliver that message. When I press Enter, watch what happens. It's really powerful. Now, if I
type in any positive number, it displays it with a plus. If I type in any negative value, negative
45, it displays it with parentheses. Negative 12.
[00:14:34.62] If I put a 0 in this cell, it displays it as a dash. What's actually in the cell is a 0, but
it looks like a dash. And finally, if I type in any text at all, it says this. Cell should contain a
value. Very, very powerful, but very simple. By the way, people sometimes ask me, well, what if
text is allowed? What if I want to actually see the text that I just typed in? Well, that's easy.

[00:15:00.51] Then you can just go back into the fourth section here. And instead of giving it a
message, you can use the @ sign, like in an email address. The @ sign means you are allowing
the text. And if I press OK now, it will allow it. It will permit it. If I type in my name, you can
see here, type in my name, and it will allow it because I had a @ sign at the back.

[00:15:23.49] So that is what I wanted you to see. It's so powerful to understand not only that we
need to build an assumption page, but I want to make sure you know the appropriate skills to use
so that all your numbers are clear, that your reader understands what the numbers mean, and that
they can use your numbers. Your numbers can be used effectively in calculations.

[00:15:48.18] Our next couple of videos and the next one in particular will deal with scenarios,
one of the most important sections in the model. It's the last part of our front end, and we'll talk
all about that in the next video.

[00:16:00.66] [MUSIC PLAYING]