Data validation

Overview

This is an extract from our pre-course videos for our renewable energy and infrastructure project finance modeling course.

Video

Video Transcript 

So one of the biggest causes of model error is us. We make the mistakes. I certainly do, and sometimes it’s pretty embarrassing. Sometimes those mistakes are just from really, really silly things. Maybe even it’s just because we input something totally ridiculous, nonsensical and the calculations themselves may actually be correct. Thankfully we can limit them all inputs to predefine values to reduce the risk of crazy inputs. Let’s for a moment say we’re in the business of selling fruit. We’re a fruit retailer and after an extensive business case, we’ve decided that we are going to sell one new fruit, something we don’t already sell. And we’ve narrowed it down to one of the following two fruits. Ackee?

Oh well I thought this one was interesting. Although it’s known as the ackee apple or I, it’s the national fruit of Jamaica. You will have trouble buying it here though because it’s banned.

Okay, look, clearly I’m a technical guy. I don’t know my exotic fruits. I’m really sorry. So we’re looking at ackee and kiwi…. Not that type of kiwi. The kiwi fruit please. So we’re deciding between ackee and kiwi and we only have enough space in our store to sell one of these, and each has a different price and of course a different cost. Our financial model will calculate our business value based on which fruit we actually decide to sell. So our model needs the ability to pick…get it…pick? Anyway, going to pick one of the two fruits to add it to our total revenue. And in our example you can see we’ve got a single price against each fruit. Beneath that we have the ability for the model user to type in the fruit they want to select. Based on the selection chosen, the model will calculate new results. And we talked about silly, and someone here could actually type in “pencil.”

Clearly that input is absolute nonsense and luckily Excel has a way to limit the inputs a user can select. This is called data validation. So what is data validation? It’s simply a way to limit inputs to predetermined values or formats that we want such as the name of a fruit. At this stage of the course, we’re going to focus on creating a type of data validation called a list. It’s a fancy way of saying we will create a drop down menu where users will be able to select the relevant fruit from the list.

What’s great about this is it avoids someone mistyping something silly like pencil or more realistically misspelling ackee. It also provides clarity to the user as to what the options are that they are permitted to enter. You can see on screen, I can now see an option of which fruit I want to select with a menu in cell B5. Ultimately we will reduce errors and make it easier for a user. So what’s not to love? Kind of like a kiwi.

So how do we create a list? Well, it’s a standard function in Excel, and to access it I go to the toolbar and to data, data validation, and once we’re in data validation we can see this box appear on our screen. There are three tabs in the box: settings, input message, and error alert. The input message and error alert are not important now at this stage.

We aren’t going to select list within settings. The only input we need to concern ourselves now is with source, meaning what are the options going to be in my dropdown menu? And I have three different options for source, I could type in kiwi, ackee. That’s option one. Option two: I could just select the cells, A2 and A3, and Excel will automatically use those cells as inputs. Or number three, I could add a name range for the cells, A2 to A3, such as fruit, and we’ll cover how to do that shortly. For now, we’re going to highlight cells A2 and A3 as our inputs for the list here. Now being the silly person I am, I’m going to test “pencil” as an input.

Yeah, oops. I get an error message. The value doesn’t match the data validation restrictions defined for the cell. I can no longer type pencil. It’s great. If I instead click on the arrow of the cell, I can select either ackee or kiwi and using an IF formula that we learned earlier. I can now pick which price to apply to my model.

Here I can say if B5=ackee, then give me $10 otherwise, 2. In this case, kiwi is selected, so the price is $2. This is a really easy way to limit silly inputs and avoid embarrassment to you. It’s also a great way to help the user know what inputs they can select. It’s a simple and effective way to improve your model.