Locked lesson.
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)
- 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.