Sign in or start a free trial to avail of this feature.
16. Restrict Model Inputs
Our model inputs can't accept every type of number. Therefore when you give the model to a new user, it's important to restrict the input values he can enter. We'll do this with data validation.
Why restrict model inputs?
- Our model inputs cannot accept all types of data
- If we share our model with a new user, its better to restrict the entries they can enter
- This avoids model errors that may arise after we've left the project
How do we restrict model inputs
- Apply a list of values through Data Validation to your inputs
- This works better than other validation methods which just flag errors
ALT + A , V, V: Open Data Validation
SHIFT + →: Select next cell
When we build a model, we intuitively know the format and range of reasonable values for each input. For example, we know that the ref plant start date must be a whole number most likely 2014 but potentially up to two years after. In addition, other time-related inputs such as ref plan duration, operating life, and construction duration need to be whole numbers. For new users of the model who are not involved in this construction, these constraints might not be immediately apparent. If a new user entered a value of 2.5 for the ref plant duration, the model wouldn't support this and an error would occur. To fix this potential problem, we can restrict the values that are entered in our control panel using data validation. To bring up the data validation dialogue box, I'll use the shortcut, Alt + A, V, V. The Allow drop-down allows us to limit users to a set of values. In the case of the ref plant start date, I'll limit users to a whole number. I'll set the minimum value to 2014 and the maximum value to 2016. Then I'll press OK. Now when I try and enter a value in the ref plant start year, let's say 2017, Excel raises an error. While this technically works, it can become annoying for the new user when no explanation is given as to why 2017 doesn't work as an input. A much better way of restricting inputs is to use lists. If I go back to the data validation dialogue box, I'll switch Allow to List. And in the Source, I'll enter 2014, 2015 and 2016. Then I'll press OK. 16 00:02:00.05 --> 00:02:06.17 And then in my dropdown, I can simply select the year that I want.
I'd like to have similar validation for the Base and Optimistic scenarios so I'll select these cells, Alt + A, V, V, again select List and the source will be 2014, 2015, and 2016. And as you can see, the dropdowns have now been applied to these cells as well. Similarly, I'd like to impose a constraint on the ref plan duration and limit the potential inputs to 1, 2 and 3 years. So I'll select all the cells, Alt + A, V, V and then select List. Then the source will be 1, 2, 3. And for each of these cells, we now have a drop-down that restricts us to the three selected values. As an exercise try to repeat data validation for the operating life, construction duration, depreciation and target time duration leaving a reasonable range of values in the source dialogue box for each input. I'll leave my answer in the after file below the video. Before we hand over our model, we should also check to make sure that our cells are formatted according to the legend at the top of the sheet. In the control panel when we added scenarios, my Live column moved from hard coded inputs to formulas, so we should change its format accordingly. I'll do this off camera. In addition, the project cash flows are derived from another sheet, and so as a result this text should be green. And again, I'll make this change off camera. After inspecting the other cells, I am happy with our cell formatting and we are ready to deliver our recommendations to our client.