Locked lesson.

Upgrade

About this lesson

How to use Work in Progress (WIP) Flagging.

Exercise files

There are no related exercise files for this lesson.

Quick reference

Layout Tips Part 6

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

WIP Flagging

  • While working on a workbook it is common to get distracted and forget which part of the workbook you are working on. A way of keeping track of what section of the model you are working on is as follows:
    • Before walking away from your worksheet or moving to another sheet in the workbook select the Home button – this will take you to column A of the worksheet
    • Type something in this column i.e. ‘W’ for work in progress
    • Once you come back to this sheet hold CTRL + Home – this will take you back to the cell just underneath the page break
    • If you hold CTRL + Arrow Down button – this will take you to the next cell in that column with data in it (the ‘W’ that was typed)

Login to download
  • 00:04 Time for one final session on layout tips.
  • 00:08 Talked about number formatting, conditional formatting, styles.
  • 00:14 We've talked about laying it out and what makes a good presentation.
  • 00:20 There's one thing I said I'd talk about which I haven't done yet.
  • 00:25 Back to my layout example for one last time.
  • 00:29 Does seem like it was awhile ago but I have been putting together
  • 00:34 the rudiments of a model explaining how it might be set out.
  • 00:38 Now, I've explained these columns now.
  • 00:41 This is where you put numbers in that don't relate to a particular date.
  • 00:46 I've explained that we have our labels.
  • 00:48 We have our layout for our headings and so on.
  • 00:51 But one thing I didn't mention was column A.
  • 00:54 I said, let's keep column A blank.
  • 00:56 I said I'd come back to you later with why.
  • 01:00 You're probably thinking I'd forgotten, haven't you?
  • 01:03 Let me explain.
  • 01:04 When you model, you don't model in isolation.
  • 01:09 You don't have some ivory to work out of.
  • 01:11 The phone rings.
  • 01:12 You have to get to a meeting.
  • 01:14 Somebody comes to talk to you about the football hairstyles, whatever it may be.
  • 01:18 But you get interrupted.
  • 01:19 That's just the day to day issue of working with models.
  • 01:23 Now, one of the problems can be, I could be in the middle of doing a calculation.
  • 01:29 Maybe I was supposed to be adding this to something else.
  • 01:33 And I couldn't remember what it is that I was going to do when the phone rings.
  • 01:38 Now, I have to pick up the phone.
  • 01:41 If I pick up the phone and it happens to be somebody who is a customr of this
  • 01:44 model, I might have to go somewhere else and completely forget what I was doing.
  • 01:49 This is called life, it just happens and you have to deal with it.
  • 01:55 How can I remember whether or not I was working on something
  • 02:00 on a particular cell in a particular sheet at a particular time?
  • 02:06 Well, shall I just write it down on a piece of paper before I answer the phone?
  • 02:10 No, because it's probably going to voicemail.
  • 02:13 So I do this.
  • 02:15 You may recall I said, press Home to take you to column A.
  • 02:21 Takes me to column A.
  • 02:23 And just type anything at all in this cell,
  • 02:26 I tend to use the letter w for work in progress.
  • 02:30 And press Enter.
  • 02:32 Now pick up the phone, talk to whoever it is, and if they've
  • 02:35 got a question on the second sheet about such and such and talk through.
  • 02:39 And completely forget what you were doing, which is what most of us do.
  • 02:44 The thing is, before we save this model, there's lots of things we can do.
  • 02:49 Like test for prima facie error checks, which I showed in an earlier session.
  • 02:53 But one of the things I'd like to do is reset each sheet, so that whoever opens up
  • 02:58 the model next isn't down in cell DD348, thinking where am I in this model?
  • 03:05 To do that, I actually go on each sheet, and I go Ctrl+Home to reset the sheet.
  • 03:11 That takes me to column A below all the labels, the other side of the frozen pane.
  • 03:17 I said, keep column A blank.
  • 03:21 Therefore, if I were to use Ctrl and the down arrow,
  • 03:26 I should go here, to cell A-1048576.
  • 03:30 If that's fine, I just press Ctrl+Home, that sheet is done.
  • 03:34 Now I go to the next sheet and I can do either Ctrl+Page Up, Ctrl+Page Down,
  • 03:38 depending on whether I'm going to a sheet to the left or to the right.
  • 03:41 So if I go Ctrl+Page Up to get to the first sheet.
  • 03:44 Ctrl+Home first of all.
  • 03:46 Down arrow with the Control button pressed down takes me to A13.
  • 03:51 Yes, I was working on this.
  • 03:54 I need to fix this before I save it.
  • 03:56 So you'll go and fix whatever it is you were doing.
  • 03:59 So it might be K13+L11 is what you meant to have in there, don't know what.
  • 04:06 And once it's finished you can get rid of the w.
  • 04:08 Then Ctrl+down arrow again, nothing else.
  • 04:11 Ctrl+Home, ready to go, save.
  • 04:15 Column A is for work in progress.
  • 04:18 It doesn't require macros or anything clever.
  • 04:21 It's just a simple trick using the arrow keys, the control button, and
  • 04:24 a little bit of ingenuity.
  • 04:27 That's all I wanted to say then about layout tips.
  • 04:31 We are going to now concentrate at looking at various functions and
  • 04:35 features that are required to build a financial model.
  • 04:38 And we're going to start off with some of the common Excel features.
  • 04:42 And the first one that we'll look at next time is range names.

Lesson notes are only available for subscribers.

Gift this course