8. Prepare Your Dataset for Modelling
Before calculating variables from our raw dataset, let's first create named ranges for our columns and set up a new control panel for decision variables and outputs.
Preparing the dataset
- Naming ranges of data saves a lot of time and makes formulas more readable
- Inputs that are the same value for every flight should be moved to control panel
Keyboard shortcuts used
CTRL + F3: Open name manager
ALT + N: Create a new name
CTRL + Spacebar: Select full column
ALT + E , D: Delete selected column/row
Now that we've built the first iteration of our model and understand the relationships between the inputs and the outcomes, it's time to apply our model to the real data set.
But first, our dataset has to be prepared so that we can easily access this data in our formulas.
I'd also like to build a control panel that will allow me to change the inputs and see the outputs all on the same screen.
We'll accomplish these two tasks in this lesson.
If you've watched my lessons on data lookups, you'll know that I'm a big fan of naming ranges of data.
And we'll do this for the following four columns.
So starting with price quotes, I'll select the column including the title with Ctrl + Shift, down arrow, Ctrl + F3 to open the Name Manager and Alt + N for a new name. And I'll call this Price Quotes.
And off camera, I'll repeat this for the remaining three columns.
Now let's add the titles for the inputs next to our data set.
And again, I'll do this off-camera to save some time.
According to the problem statement, which I've included in the show notes, the booking limit should be the same for all flights. So we don't require a full column for this value.
Instead, we'll create a single cell in a control panel to change the booking limit.
The same is the case for capacity, which will equal to 180 seats for all planes.
So I'm going to remove these columns from our data set.
So I'll select the two cells, select the columns with Ctrl + Space and Alt + E, D to delete.
The remaining three inputs will be flight specific and calculate it from our data set. Now let's create the control panel for our large model.
So I'll create a new sheet with Shift + F11 and I'll name it Control Panel.
So Alt + O, H, R to change the name of a sheet and Control Panel.
Off camera, I'll create cells for our decision variable, fixed assumptions, and our three output values.
Total additional profit, additional cost, and additional revenue.
I've also named the two input cells, booking limit and capacity.
Now that our dataset and control panel are set up correctly, we're ready to calculate our input values in the next lesson.