8. Prepare Your Dataset for Modeling


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.


  1. Naming Columns (00:04)

    Before we apply a model to our data set, we want to prepare the data so that it can be easily accessed in formulas. Naming ranges of data can help with this. We can name columns so they can easily be referred to in formulas. To name a column, we select the column and open the Name Manager with Ctrl + F3. We press Alt + N to create a name, which should be in uppercase by convention.

  2. Adding Inputs to the Data Set (01:04)

    When adding inputs to the data set, we need to consider if an input has the same value for each row, or a different value for each row. For example, in our Zippy Airways data, each row of data represents a flight. The capacity of each flight is identical, because the size of the aircraft is fixed. However, the demand for each flight is different. We should add a column to the data set to represent each input with a different value for each row. Inputs with the same value for each row should be added to a control panel instead.

  3. Creating a Control Panel (01:50)

    We create a sheet containing the control panel for our large model. This sheet contains the decision variable and fixed assumptions as inputs. It also contains the outputs of the model. To help creating formulas, we name the cells representing input values. 


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.

Excel Excel for Business Analytics Learning Plan
Modeling for Business
Build Your First Model