Locked lesson.
About this lesson
General tips and options to change in Excel when laying out a typical worksheet in a financial model.
Exercise files
Download this lesson’s related exercise files.
Layout Tips Part 3.xlsx10.1 KB Layout Tips Part 3 - Solution.xlsx
10.3 KB
Quick reference
Layout Tips Part 3
Layout tips explained using the CRaFT methodology
When to use
Layout tips can assist in building a financial model in excel which is easy to understand and navigate through.
Instructions
Layout of Headings
- Headings should start in column B, not A, and then move out a column or two for sub headings and sub sub headings respectively. Data labels can be put directly beneath sub sub headings:
- Enter the main heading in a cell i.e. B10
- Leave a row and enter a sub heading in C12
- Leave a row and enter a sub heading in D14
- Enter your data labels consistently i.e. in Column D
- The narrowing of the columns effectively indents the headings and makes worksheets easier to read and navigate (especially if the gridlines, ALT + W + VG, are toggled off).
- Take special note of the spacing: one blank row between headings; two lines between sections. It isn’t necessary to be identical to what is being suggested but be consistent.
- Blank columns H and I are in existence in case of any calculations, inputs or referred values that do not refer to a particular time period.
- 00:04 >> In this third part of layout tips,
- 00:07 I'm going to revisit putting together the actual template.
- 00:12 And now let's have a look what is the line.
- 00:17 What do I mean about below the line?
- 00:19 I'm talking about the line here in rows 4 and
- 00:22 5, the frozen pane we put in last time.
- 00:25 Below the line is where we put the main part of the model.
- 00:28 The calculations, the inputs, the outputs, below the dates,
- 00:31 which should always be consistently positioned.
- 00:35 I'm going to leave column A blank, there's a reason for
- 00:37 this that I will come back to later, don't worry I won't forget.
- 00:42 I'm going to put my Main Heading here, I'm leaving
- 00:47 blanks all around it, this idea that I call moat theory.
- 00:51 I will put a Sub Heading here.
- 00:59 And a Sub Sub Heading here.
- 01:05 So imagine the main heading might be financial statements.
- 01:08 Sub heading might be income statement and
- 01:11 the subheading could be revenues, something like that for example.
- 01:16 Notice I'm leaving this blank row deliberately,
- 01:18 it's important to think about space.
- 01:21 There's a reason again why I would do that, and
- 01:23 I promise I'll come back to that too.
- 01:25 Now I'm gonna change my terminology.
- 01:27 Whilst we may all understand what Main Heading, Sub Heading and
- 01:30 Sub Sub Heading are maybe it's simpler if I call this Heading 1.
- 01:37 This one can be called Heading 2, and
- 01:42 no prizes for guessing what this one would be called, Heading 3.
- 01:47 Then I need to put my data in.
- 01:52 Little data labels coming down here.
- 01:54 Now, you can't leave another bland row.
- 01:58 It's my way of doing it this way, again, it's more about concepts.
- 02:03 Please don't get me wrong,
- 02:05 you must do it exactly the way I do it, it really is a case of just being consistent.
- 02:11 And so this might be my different types of revenue stream,
- 02:13 product a, product b, product c, etc.
- 02:16 You can widen these columns if you need,
- 02:18 the reason I've actually got the narrow columns is becoming apparent now.
- 02:22 It's because I want to indent.
- 02:24 So if I were to copy this down, Ctrl + C to copy, and
- 02:29 I'm gonna leave two rows to paste down here.
- 02:31 That will be my second section, I could shrink this down a little bit.
- 02:36 You can see it's starting to become a little bit clearer what's going on.
- 02:40 Now, I can't indent without using the columns.
- 02:44 If I actually go to the Home tab, I can go to Format and
- 02:48 I can go to format cells, which is down at the bottom.
- 02:50 Control one is the keyboard shortcut I'll use from now on.
- 02:54 And, I can actually go to alignment, and look at indenting here.
- 02:59 The problem with that, is as formatting, that can again lead to file corruption.
- 03:04 It's better to do it this way, and there's a good reason why.
- 03:08 If I'm in this cell here, and I actually want to get to this cell here.
- 03:13 I can navigate very,
- 03:14 very easily using those keyboard shortcuts we were talking about earlier.
- 03:19 If I go CTRL+Down arrow once, watch what happens.
- 03:24 It takes me to the next heading down.
- 03:27 If I go CTRL+Down arrow again, wee,
- 03:29 I'm right down at the very bottom of the sheet.
- 03:34 Now I could make a 20 video here as a scroll up, but
- 03:38 that's not going to be the fastest way to do it.
- 03:40 To reset a sheet, hold the Ctrl button down and press Home, Ctrl+Home.
- 03:47 Notice it resets the sheet and notice where it resets it to.
- 03:52 It sets it to the actual cell we used as the freeze pain cell earlier.
- 03:57 It has to be that one because if I am down at the bottom again,
- 04:01 let's hover right down to the bottom, and I select cell A1 instead
- 04:05 It doesn't reset the sheet and that's where a lot people think you should go.
- 04:10 So, if we're going to set up pipe pullings, and
- 04:12 we are going to do that later, we should, actually,
- 04:15 be looking to set a hyperlink that will actually take me to cell A1 on a sheet.
- 04:20 It should always take you to the control home cell, more on that later.
- 04:26 So, we've got this here.
- 04:28 Now another thing we can do I can actually put in here as well.
- 04:32 A shift command as well as the down arrow key, Ctrl+Shift+Down, that only
- 04:37 takes me to the end of the block, it actually highlights everything between.
- 04:43 So CTRL+Down arrow again will take me to the very bottom, CTRL+Up to the top,
- 04:48 CTRL+Shift+Down arrow takes
- 04:52 all the mean here CTRL+Right arrow takes me to the very end, see?
- 04:57 Very easy to navigate around.
- 04:59 So we've got our little box here, and I put another block in just below.
- 05:06 Now notice I'm actually leaving two rows between each section, this is deliberate.
- 05:12 It's new paragraph time so think of this as sentence, full stop,
- 05:17 you put a space sentence, full stop, you put a space and so on.
- 05:21 The reason we do this is going back to my idea of moat theory.
- 05:25 If I do this, if I select a (inaudible) here and I press either Ctrl+A or Ctrl+*.
- 05:32 If you have a full key back it's the asterisk on the number pad,
- 05:36 not above the number 8 or where it might be on your keyboard.
- 05:39 Ctrl+*, Ctrl+A will highlight that whole block.
- 05:44 But be careful, if I have something touching like this and
- 05:48 I were to do that, If I go Ctrl+A now, do you see it selects that as well,
- 05:53 because it selects things that are touching.
- 05:56 You've got to be careful, okay, that's it for this session.
- 05:59 When we come back, we'll look at putting data in, too.
Lesson notes are only available for subscribers.