Locked lesson.

Upgrade

About this lesson

Go over the elements that should be incorporated into a workbook.

Exercise files

There are no related exercise files for this lesson.

Quick reference

Workbook Structure

Understand how to structure a workbook.

When to use

When building a financial model, developers need to ensure that the workbook is structured in a logical manner so that users may follow it easily.

Instructions

  • Ensure the groundwork is complete before starting the new workbook build – map out how the model should be structured
  • Don’t use an old model to build the new one. Always create a new one bearing in mind the end users
  • Use different worksheets for a particular purpose (i.e. Revenue) which are short and succinct – this will make it easier for users to find information
  • Where possible, keep it simple:
    • Comprehension, named cells, arrays, “megaformulae”
    • All data entry should only occur once and be clearly defined
    • No hard coded data in formulae
    • Input conventions should be consistent
    • Model should “read” from left-to-right, front-to-back
    • Treatment of repeatable sections
Login to download
  • 00:04 It's time for a little interlude, just to set the scene for
  • 00:07 what is pretty much going to be a whole load of Excel coming up.
  • 00:11 I need to talk about Workbook Structure.
  • 00:13 To summarize, I've been saying already, keep it simple, stupid.
  • 00:20 Put your data entries in once, no hard coded data.
  • 00:24 Make sure it's reading from left to right, yadda, yadda, yadda.
  • 00:27 You must be bored of that by now.
  • 00:29 But that's why the last point is quite ironic, treatment of repeatable sections.
  • 00:35 Again, this slide, it's important to understand your workbook structure.
  • 00:41 You need to understand how each sheet fits, what its purpose is,
  • 00:45 what its content is going to be.
  • 00:48 We've already talked about the idea of keeping inputs separate
  • 00:51 from calculations separate from outputs.
  • 00:55 But we need to sketch it out more than that.
  • 00:57 We need to understand the general layout for a financial model.
  • 01:00 Typically, you'll have an input sheet that stands alone.
  • 01:05 This will work in conjunction with a timing sheet.
  • 01:08 Why do we need a timing sheet?
  • 01:10 Well, it will stand back from the rest,
  • 01:12 hence the perceived gap in the graphic, here.
  • 01:14 In that, this is where all the dates, the time series will be.
  • 01:18 Because normally, we're looking to forecast in a financial model.
  • 01:22 Depending on how we're then calculating our model,
  • 01:24 we may split up our revenue from our costs, from our finance and so on.
  • 01:29 And have different sheets that are specific to a particular purpose so
  • 01:33 it's easier to find.
  • 01:35 Some people like to have one big sheet called calculations.
  • 01:38 I'm very much against this idea.
  • 01:41 The trouble is you can be scrolling down to row 17,465 to
  • 01:45 find something quite quickly.
  • 01:47 And it's just not ideal when you're trying to figure out where the heck
  • 01:51 everything goes.
  • 01:52 Keep it simple, keep it stupid, keep the sheets short and brief.
  • 01:57 People can navigate round quite quickly.
  • 01:59 It used to make a difference in Excel to have more worksheets.
  • 02:02 It did slow down calculations but not anymore.
  • 02:06 When you've got all that sorted out, you can have a summary financial sheet.
  • 02:10 And the executive summary or plots or charts to actually have as key outputs.
  • 02:16 This will keep it separate and
  • 02:17 works in with the idea of inputs separate from calculations, separate from outputs.
  • 02:22 The reason there's no Excel example in this case here.
  • 02:25 Is because this is something you'll often create a mind map, maybe PowerPoint and
  • 02:30 bubbles.
  • 02:30 Or, I'll be honest, something I do quite regularly is working with my clients.
  • 02:35 It's actually taking a whole load of Post-It notes,and
  • 02:38 sticking them on the wall.
  • 02:40 And then linking them together with pencils to work out,
  • 02:42 what's the ideal template for the model to work in this instance?
  • 02:46 You can't just take an old Excel file and use it for a new one.
  • 02:50 I know a lot of people do this, but it's not the right way to do it.
  • 02:54 Split it out.
  • 02:55 How do people think?
  • 02:57 There's no point doing it in the way you think it makes sense.
  • 03:00 Go and talk to the customers,
  • 03:02 the end users of what they're expecting to see why.
  • 03:05 And then lay it out accordingly.
  • 03:07 Don't just think you can take an old model, dust it down, and re-use it.
  • 03:12 It doesn't work like that.
  • 03:14 Square pegs often seem to fit in round holes in modeling.
  • 03:17 But they get caught up later, and
  • 03:18 often at crunch time when people are trying to make key decisions.
  • 03:22 Don't be caught outyourself.
  • 03:24 This is a preamble to what we're going to do next.
  • 03:27 I'm sorry it's just PowerPoint slides for this one,
  • 03:29 but it's just an important point.
  • 03:31 When we get to the next section, we're going to talk about laying out a model.
  • 03:35 And how we should set it out with the four key principles of craft.
  • 03:39 And I'm going to give you a dummy template in the next example.
  • 03:43 So just think how you would put together one of your models.
  • 03:46 Then how it might look, when we put together our first
  • 03:49 example of a financial modeling template, coming up next.

Lesson notes are only available for subscribers.

Gift this course