Creating a Timing Sheet

Quick reference

• 00:04 It's time to do some calculations.
• 00:08 Let's go and create a timing sheet.
• 00:12 Back to the template than.
• 00:14 In the template,
• 00:15 we're back on the front cover cuz that's how we save it, that's how we roll.
• 00:19 But let's use the navigator, click on this hyperlink here, and
• 00:23 it takes us to our table of contents.
• 00:26 So we're only ever one click further away from our model.
• 00:29 So two clicks from anywhere in the model will
• 00:32 bring this back to wherever we want to go.
• 00:34 First click to the table of contents.
• 00:36 Second click to wherever we want to go.
• 00:38 Here, timing that we need to populate.
• 00:41 Notice here I've got my sections all set up,
• 00:43 I just need to populate the data and I've got a frozen pane in.
• 00:47 One thing I didn't mention last time, did you notice this formula here,
• 00:50 the max function, which takes the maximum from row 10 all the way up to
• 00:54 the row above the row you're on?
• 00:56 So in this first case, it's row 10 to row 10.
• 00:58 The beauty of this little formula is if I want to copy this section down, let's just
• 01:03 put it down here for instance, you see it automatically numbers it number 2.
• 01:08 And if I go here, it'll be number 3, as well.
• 01:11 That's what's going on, so
• 01:13 just a simple way of putting these things in should you wish.
• 01:17 Okay, just so I can have enough real estate on the page, and
• 01:21 I don't want to shrink this down to 90%,
• 01:24 I'm going to hide the ribbon here by using the keyboard shortcut Ctrl+F1.
• 01:29 To toggle brings it back again.
• 01:32 Let's get going.
• 01:33 I need to put something in Model Start Date.
• 01:34 This is a white cell.
• 01:35 That means that once I've actually put a date in here, it should be fixed for
• 01:40 the duration, that is the long life of the model.
• 01:43 It shouldn't change again, because it's going to be our start date of our model.
• 01:47 And I'm going to go for the first of July, 19 in this case.
• 01:52 Now if you're in the US you might have to put 7/1/19 in.
• 01:57 I'm based in Australia so that's how I type it in, so
• 02:00 don't get confused by the way I enter dates.
• 02:02 That is the 1st of July, 19.
• 02:05 We need the number of months in a full period.
• 02:08 And I've already set this up,
• 02:10 I've used data validation to bring up the numbers 1, 2, 3, 4, 6, and 12.
• 02:14 And it's important that for this to work,
• 02:17 the only numbers that can be in here are numbers that perfectly divide into 12.
• 02:21 So 5 can't, because 12 divided by 5 isn't an integer.
• 02:25 That's how this works.
• 02:26 And I'm going to give this a range name.
• 02:30 I'm going to call this the Periodicity.
• 02:34 So using the name box to put in Periodicity and
• 02:38 I'm gonna call this one here Model Start Date.
• 02:44 Remember, no spaces in a range name.
• 02:49 So just check, always check this again that they're there.
• 02:51 Because sometimes they don't register in Excel, because you don't press Enter
• 02:54 carefully enough, you forget, or sometimes there's just a little glitch in Excel.
• 02:58 So, I'll make that 12.
• 03:00 We'll have an annual model here.
• 03:02 The Example Reporting Month, that was to be a number between 1 and 12,
• 03:07 so we can use data validation here.
• 03:09 So remember keyboard shortcut, Alt+DL brings up Date Validation, and
• 03:14 we're going to say okay, we want this to be a list.
• 03:17 I will put the numbers 1, 2, 3, 4, 5,
• 03:22 6, 7, 8, 9, 10, 11 and 12 in.
• 03:28 And then they're the only things to allow.
• 03:31 So we're still gonna have a June year end, and we're gonna call that, surprise,
• 03:36 surprise, Example Reporting Month.
• 03:45 Now, we need an actual function in here for
• 03:50 the Reporting Month Factor.
• 03:54 I'm going to call it Reporting Month Factor before anything else,
• 03:57 otherwise I tend to forget these things.
• 04:04 Typed that right, now this is a calculation so
• 04:08 it's not formatted like a constant here.
• 04:11 This is actually going to be a calculation.
• 04:15 And what I'm actually going to do here is say, okay,
• 04:20 I want it to calculate a number based on the periodicity.
• 04:25 So it's not too clear when this is 12, but imagine I've put this to 3, for example.
• 04:34 Example Reporting Month 6, when you actually got three
• 04:39 months in a quarter, would be the number 3.
• 04:43 Because it's actually 6 divided by 3,
• 04:47 is 2 remainder naught, so it will the third month of a quarter.
• 04:52 So we're going to go okay, it's going to be 1, 2, 3, 1, 2, 3, 1, 2, 3 is how we go.
• 04:58 If we have this as 6, then it will go 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1,
• 05:03 2, 3, 4, 5, 6, and so on.
• 05:05 You'll recall that the function we need to actually
• 05:11 put in here then is going to be the mod function.
• 05:16 So I'm going to write in here =mod(Example_Reporting_Month,
• 05:23 and we're going to subtract 1 from it for
• 05:27 the trick that I showed in the preparation,
• 05:31 ,the actual Periodicity)+1.
• 05:35 So, month 6 on an annual model will be month 6.
• 05:40 Month 6 on a quarterly model will be period 3.
• 05:44 That's what we want, and we're going to be using that to calculate the dates.
• 05:48 And in the months per year, we've already got that on the parameter sheet in here.
• 05:52 If you recall here the months per year here, we've got it there Months in Year.
• 05:58 So I'm going to go back to timing and type in months.
• 06:02 And we can see here, here's months_in_year down at the bottom.
• 06:05 Press Tab to bring it in.
• 06:07 Press Enter, done.
• 06:09 We've got some calculations all ready to go.
• 06:12 What we need to do now is build the actual formulas in the headings in the next
• 06:16 session.

Lesson notes are only available for subscribers.