Locked lesson.

Upgrade

About this lesson

A continuation of how to create a timing sheet to be used in building a Financial Model.

Quick reference

Creating a Timing Sheet Part B

Discover how to create a Timing Sheet.

When to use

When constructing a basic Financial Model.

Instructions

​​

  • Essentially, three lines are necessarily needed when modelling (the rest may be derived as necessary):
  • Start date: This will allow for models where the first period is not a “full” period (often called a ‘stub’ period), e.g. a business may wish to project its profits from now until the end of the calendar year for the first year;
  • End date: This will define the end of the period and will often coincide with reporting dates, e.g. end of financial year or quarter ends. By having both the start date and end date defined, a modeller can determine the number of days / weeks / months in the period, which financial year the period pertains too and so forth;
  • Counter: Start and end dates are insufficient. Constructing calculations based on consideration of a date is fraught with potential issues in Excel. This is because dates are really serial numbers in Excel which may differ depending upon the underlying operating system (e.g. Day 1 for Microsoft Excel for Windows is 1 January 1900, whilst Day 1 is 1 January 1904 for Microsoft Excel for the Macintosh). Further, if you are building a monthly model you may wish to divide an annual figure evenly instead of based on the number of days. This is also the easiest way to identify the first and last periods in a robust manner.
  • The Counter is simply the last period’s number plus one.
  • =N(Previous_Cell)+1, where the N() function takes the numerical value in the previous cell, and more importantly, text is ignored so that #VALUE! errors will not arise;
  • The Start Date is simply the Model Start Date for the first period and the day following the last period’s end date otherwise. This can simply be written as =IF(Counter=1,Model_Start_Date,Previous_Period_End_Date+1).
Login to download
  • 00:04 Let's continue creating our timing sheet.
  • 00:06 Its essential for modern forecasting because we need our dates.
  • 00:12 We couldn't get it all done in the first session here.
  • 00:15 So let's go back and look at our timing sheet again.
  • 00:19 Notice, saved it.
  • 00:20 So I am on the front cover again.
  • 00:22 I'm going to click yet
  • 00:23 again on navigator because there might be lots of sheets in this model.
  • 00:26 But timing, back where we were.
  • 00:29 So we put these in last time.
  • 00:31 Now what I want to do is populate these headings in here.
  • 00:35 So we'll start with a counter, bring that in and
  • 00:37 we're going to begin editing column J.
  • 00:40 And I'm going to use the function we've talked about in the preparation course.
  • 00:43 So equals n, open brackets of the cell to the left,
  • 00:47 closed brackets plus one and copy it across.
  • 00:51 So I'm going to have five periods in this model.
  • 00:54 And because I don't want anything to go in here,
  • 00:57 now the n function will pretty much take care of any text anyway.
  • 01:01 I want to actually make this what we call an empty cell.
  • 01:05 I don't want people to type in it so I'm gonna make it clear.
  • 01:08 Leave this cell alone by going into Cell Styles and clicking on Empty.
  • 01:12 And that means, leave this cell alone.
  • 01:17 Now we've done that, I can start putting the other stuff together.
  • 01:21 Right, so the first one we'll do is the Start Date.
  • 01:23 So this is this cell here.
  • 01:26 Cell J6.
  • 01:27 If it's the first period, we want it to be the actual model start date,
  • 01:31 otherwise we want it to be the end date from the period before.
  • 01:34 So we use an if statement.
  • 01:37 Equals if, open brackets, this come to here,
  • 01:40 now I'm going to hit the F4 function key twice so it becomes.
  • 01:45 We're going to anchor it to row nine.
  • 01:47 So if I were to copy this formula somewhere else,
  • 01:49 it is always going to link to the relevant section of a counter.
  • 01:53 So equals if equals one comma, that's saying if it is the first period.
  • 01:59 And if you always have a counter in the same row in every model,
  • 02:02 you'll start to J9 means if it's the first period, it will become second nature.
  • 02:07 I want it to then do the model start date down here, start date.
  • 02:12 Otherwise I want it to take the previous period's end date,
  • 02:14 which will be this cell here plus one.
  • 02:18 43,647.
  • 02:20 We've not talked about this yet.
  • 02:22 We should be looking at styling everything as we go.
  • 02:26 Now, a lot of these cells, I've actually styled already, but I want to put them in.
  • 02:30 So I have a cell style here, and I've created a date style.
  • 02:35 And I'm going to copy that across.
  • 02:36 There's my date style.
  • 02:38 Now at the moment we've got 1st of January, 1900 for the rest, but
  • 02:41 that's fine.
  • 02:42 Because that's actually referring to, if you think about it, that cell there,
  • 02:45 which is blank.
  • 02:47 Now, the end date, typically it's going to be so
  • 02:51 many periods in the future based on the number of months in the full period.
  • 02:55 But we always have an issue with the first period,
  • 02:58 that it might not actually be the start of a period.
  • 03:01 I.e the first period may not be a whole period.
  • 03:05 So we need to get a bit more complicated.
  • 03:07 We need to say, okay, let's take our month that we've got, which here, is July 19.
  • 03:13 How many months do we need to add on to it using the EOMONTH function.
  • 03:17 Remember the EOMONTH function?
  • 03:19 And of month, how many months do we need to go?
  • 03:23 Now it's a full period it's going to be 11 months into the future.
  • 03:26 But let's say I was doing it for the June year end and it's August.
  • 03:30 To get through there I'm gonna have to go 10 months into the future.
  • 03:34 If it's September I'm gonna have to go 9 months and so on.
  • 03:37 We need go so many months into the future.
  • 03:40 And how do we do that?
  • 03:41 We use the following formula that looks a bit yucky to start off with, but
  • 03:45 isn't that horrible really.
  • 03:46 It's EOMONTH, Open brackets,
  • 03:52 take the start_date, which is going to be the cell above.
  • 03:56 And that's always gonna be, that's gonna be J$6.
  • 04:00 And then it's going to be comma, we've got this clock mathematics going so
  • 04:04 that's sort of highlights really the mod function.
  • 04:06 And it's going to be the periodicity, which is the 12,
  • 04:10 plus the reporting month factor that we have down here.
  • 04:15 Which is why we use this, minus the actual month we're in.
  • 04:26 And then, what we need to do is make that the periodicity again,
  • 04:31 close brackets, close brackets.
  • 04:35 And that gives me 44,012.
  • 04:36 So let's just style that like that, so it's 30th of June, 2020.
  • 04:41 Copy it across, done.
  • 04:44 Now, if I changed that to a seven, it would still work.
  • 04:50 You see how its working because what is actual formula is doing it is saying
  • 04:55 count how many months we need to go.
  • 04:57 So if it's a case of we were in August well, that would be twelve.
  • 05:04 Plus six is 18 minus eight is ten, we need to go ten months into the future.
  • 05:08 If it was September, it would be 12 plus six is 18 minus nine is nine and so on.
  • 05:17 Why do we need the mod?
  • 05:17 Well if it were January and we needed to go to the end of the year for instance.
  • 05:23 So if we say it's month one and we had a December year end.
  • 05:26 We'd be going 12,
  • 05:28 plus the reporting factor of one, 13 minus the month is one is 12.
  • 05:31 It'll go 12 months into the future, which we don't want.
  • 05:35 We want it to be the end of the current month.
  • 05:38 That's why we need the mod factor in there.
  • 05:41 Fairly straight forward.
  • 05:43 Now, that gets us up to here.
  • 05:46 What we need to do now is calculate the number of days.
  • 05:49 Now remember the number of days is not that minus that.
  • 05:52 Because that subtracts off the first day.
  • 05:55 We have to add one.
  • 05:56 366 and put that through.
  • 05:59 And the final thing we need to do which might not seem surprising is
  • 06:04 actually again here make this the end date.
  • 06:07 Just make that equal to that.
  • 06:08 And I'm going to format that is an actual date heading, so
  • 06:12 it just has the end month.
  • 06:14 And the reason for this is sometimes you don't want to show these actual rows and
  • 06:19 you just want the dates to appear up here.
  • 06:21 But now I have dates everything is working fine and
  • 06:24 this formula can be copied across quite happily we can have more should we wish.
  • 06:29 We're ready to go we've our date set up

Lesson notes are only available for subscribers.

Gift this course