Locked lesson.

Upgrade

About this lesson

User should consider what inputs should be variable and how they should be able to vary. Example of methods to control how inputs can be varied.

Exercise files

Download this lesson’s related exercise files.

CRaFT - Flexibility.xlsx
10.4 KB CRaFT - Flexibility - Solution.xlsx
10.5 KB

Quick reference

CRaFT - Flexibility

Understand ‘Flexibility’ in the CRaFT methodology.

When to use

A key concept in financial modelling ‘Flexibility’ should be considered when building a model. The user should consider what inputs should be variable and how they should be able to vary. This may force the model builder to consider how assumptions should be entered.

Instructions

  • As a general rule, hard code should not be included in a formula as it makes it harder to change the value, identify the nature of the value or assess what the underlying assumptions are in the value.  There are exceptions though.

  • Ability to change key assumptions within agreed parameters

Data Validation

  • The most common method of data entry in practice is simply typing data into worksheet cells, but this may allow a model’s inputs to vary outside of scoped parameters. To ensure end users only have the ability to change inputs within agreed parameters the use of data validation is useful. To access Data Validation:
    • Select the Data tab
    • In the Data Tools section select Data Validation
    • In the Allow dropdown box, List can be selected which will allow users to only select from the list of options that has been created
Login to download
  • 00:04 Let's look at the third of our four key qualities,
  • 00:08 something I like in models, flexibility, Excel ones as well.
  • 00:13 So yet again, we resort to our ideology of CRaFT, and
  • 00:19 flexibility, this time, is the third of our four key qualities.
  • 00:25 So what do we mean?
  • 00:28 Essentially, there's a reason why we're building in Excel and not PowerPoint and
  • 00:32 not Word.
  • 00:33 We want to be able to change inputs and have the outputs affected accordingly.
  • 00:37 We need to be able to provide what if analysis.
  • 00:41 The inputs need to be able to be flexed within agreed parameters.
  • 00:45 So it's key that the flexibility is sufficient, but
  • 00:49 not more than what we require.
  • 00:52 One way to do this is to avoid the idea of something called hard code.
  • 00:56 Hard code are numbers that are typed into a formula.
  • 01:00 The problem with this is we don't know they're there unless we look at
  • 01:03 the formula.
  • 01:04 Most people who review a model, the key decision makers,
  • 01:08 they'll see a model as appendix four to a Word document, on PowerPoint slides.
  • 01:14 They may not be able to access the formula bar and
  • 01:17 see that there is hard code typed in here.
  • 01:19 It's difficult, therefore, to identify all inputs and assumptions.
  • 01:23 And it makes it hard to change the numbers if necessary.
  • 01:26 It lacks flexibility and transparency that we'll come back and talk about next time.
  • 01:31 Let me illustrate with some Excel examples.
  • 01:36 Let's take a look at a couple of flexibility examples to get the idea.
  • 01:41 In this first example, I have some inputs, here in cells B6 to G12.
  • 01:48 Here in cells K6 to P12, I have the results of each of those numbers
  • 01:54 in turn being multiplied, the corresponding number here, by 50%.
  • 02:01 This is hard code.
  • 02:02 If I wanted to change each of these to another percentage,
  • 02:06 I would actually have to go in and edit the formula.
  • 02:09 This isn't acceptable, especially if it's the end user whose gonna be flexing it and
  • 02:14 doesn't know how our model works.
  • 02:17 It's therefore better to add flexibility.
  • 02:20 How about I add a scalar.
  • 02:26 What I'll do is I'll take the formating from here, let's say.
  • 02:30 I'll add a percentage format, and
  • 02:35 let's go for 50%.
  • 02:38 Now, here in this formula, what I will do is I will go here,
  • 02:43 and I'll take the number, instead of 50%.
  • 02:47 I'll enter here C4, my explosive cell again.
  • 02:50 Make it absolute by pressing the F4 function key once,
  • 02:53 to put a dollar sign before the C to make that constant and before the four,
  • 02:57 to make the row constant as well.
  • 02:59 Press enter, and then I will copy it both down and
  • 03:04 across so that I now have a formula that is flexible.
  • 03:11 If I change this to 75%, voila, nice and easy.
  • 03:18 Second example, in this example, I'm highlighting cell C2,
  • 03:22 which has my scalar in it already.
  • 03:25 I've got some initial values like the other example, just typed in, or
  • 03:28 it could come from a model, could be outputs, inputs, whatever.
  • 03:32 And here's my revised values.
  • 03:34 If I press the F2 function key,
  • 03:36 you can see it's taking the initial number and multiplying it by the scalar.
  • 03:41 So what's the problem here?
  • 03:43 Well there isn't a problem, but it did say agreed inputs, and
  • 03:47 maybe I'm only allowed to pick these as scalars.
  • 03:52 How can I force this number here to not be something like 33%,
  • 03:56 which isn't in that list, because it will do it.
  • 04:01 Well I'm gonna talk about data validation in a later session, but
  • 04:04 to give you a flavor of it, I'm going to show you how you do it.
  • 04:08 It's on the Data tab, funny enough, under Data Validation.
  • 04:13 And here, we just look down here at something called List.
  • 04:18 I'll explain all the rest at a later time, but this is just an introduction now.
  • 04:22 Click on List, and then for the source, I'll link to these cells here.
  • 04:29 I'll just click OK, and look what happens.
  • 04:32 I have a drop down box now in this cell, and
  • 04:36 it gives me the only ones that are allowed.
  • 04:39 Now 33% is in there at the moment,
  • 04:41 because the 33% was there before I put the data validation in.
  • 04:46 Data validation is not reactive, it's only proactive.
  • 04:51 So if a number's already in there, it won't fix it.
  • 04:54 If I try and put 45% in here, I will get an error message that says the value
  • 04:59 doesn't match the data validation restrictions defined for this cell.
  • 05:04 And I'll have to hit Retry and go again.
  • 05:10 Still won't let me, the drop down box isn't sufficient.
  • 05:12 I can't activate this drop down box.
  • 05:14 I've got to click off and then back on again, and then I can go in and change it.
  • 05:20 So just be careful.
  • 05:22 Data validation, very useful for agreed inputs, but
  • 05:25 it needs to be handled with care.
  • 05:27 But great for flexibility.

Lesson notes are only available for subscribers.

Gift this course