Creating a planning model

Execute the following steps to create a planning model:

1. Click on the main menu icon, and go to Create | Model:

You can create a planning model from scratch in which you enter values manually in the cells or copy and paste data from a spreadsheet. You can also create this model type by importing data from a variety of sources. To keep things simple, you will fetch data from an Excel file named StoresData.xlsx in this exercise.

1. On the New Model page, click on Import a file from your computer:

1. On the Import Model From File dialog, click on the Select Source File button (A), and in the Windows Open dialog, browse for the StoresData.xlsx file (B) that comes with the book's source. Note that this file has three sheets carrying data for the Actuals, Budget, and Forecast categories. Start the proceeding and import data by selecting the Actuals option (C) from the Sheet list. Data for the other two categories will be imported in the subsequent steps. Click on Import to start the process:

After a while, you will see the Data Integration screen, as illustrated in the following screenshot. The screen should be familiar to you as you worked with it in the previous chapter. Here, the only thing that needs your attention is the Planning Enabled option, which must be turned on for this model.

1. Type StoresData for the model name (A), and select the Planning Enabled option (B). Click on the Create Model button (C), and confirm the model creation in the subsequent dialog:

The model will be created and its definitions will appear in the Modeler interface, as illustrated in the following screenshot. The Store dimension carries information about the five stores that you imported from the Excel file. For every planning model, a Version dimension is created automatically, which reveals what type of data the model holds.

Since you opted to import data for the Actuals category in step 3, the Version tab displays that the model currently holds data for the Actual category. Additional version categories (budget and forecast) will be added to this grid when you upload data into the model in subsequent steps:

The Time & Categories tab displays the Lowest Granularity section, which you went through in the previous chapter while creating the analytics model there. It now has a Categories section, which carries some categories that define what types of data you will be managing in your model. Note that analytics models contain only a single category to show the actual results. The drop-down lists beside the categories in a planning model let you define the planning horizon for each category separately. For example, you can set Budget to year, Planning to quarter, and Forecast to month. You can set a horizon by entering values in the Look Back and Look Ahead boxes. Do not forget to save your model whenever you make any type of change to it, whether it is the addition of a new dimension or the switching of categories.

1. Click on the Modeler breadcrumb link to move back and access the main Models page, as illustrated in the next screenshot.

In the first four wizard-led steps, we created a planning model and added data for the actual category from an Excel file. In the following steps, we will append the data for the remaining two categories.

1. On the Models page, select the StoresData model (A). Then click on the Import Data icon (B) on the toolbar. In addition to importing data from a file, you have several other options to populate your model with data. All these available options are displayed when you click on the Import Data icon. For this example, click on the Import Data From File option (C):

1. Once again, click on the Select Source File button (A) in the Import Data From File dialog. This time, select the Budget option (B) from the Sheet list to import data for the Budget category from the second sheet of the same Excel file:
   
   When you click on the Import button in the Import Data From File dialog, the Budget category data from the Budget sheet in the StoresData.xlsx file will be imported, and once again, you will see the Data Integration page, as illustrated in the following screenshot.

1. Click on the Date column header (A) to see its details on the Column Info tab (B). Right now, the column is not correctly mapped. In order to align the data in the file with the dimensions defined in the model, you need to map the column correctly, and this can be done by selecting Date (C) from the Map To list:

1. Click on the Model Info tab (D). Here, you have to specify the category to which you want to import the data. To incorporate the budget data, set Category to Budget, and enter Budget for the Version Name.

1. In the Import Method section, you have to select one of the three options. The Update option (A) updates the existing data and adds new entries to the target model. Clean and Replace (B) deletes the existing data and adds new entries to the target model. Append (C) keeps the existing data as is and adds new entries to the target model. For this exercise, choose the Append option to append the budget category data to the Actuals category data you already have in the model. Click on Finish Mapping to complete the mapping process and import the data:

1. Import the forecast data from the same Excel file using the instructions provided in the previous steps and shown in the following screenshot. In the Import Data From File dialog, select the Forecast sheet (A) from the Excel file. On the Model Info tab, select Forecast (B) for Category, and type Forecast (C) for the Version Name. Finally, select the Append option (D) to avoid overwriting. Click on Finish Mapping to complete the process.

If you look at the Version tab after importing data for the three categories, you see that this tab is now showing all three versions (A), as illustrated in the following screenshot:

These are the public versions for each category that are currently being used in the model. You can also set restrictions on these versions. To add user restrictions, open the dimension preferences (B) and enable the Data Access Control option (C). Once we enable this option, the Read and Write columns (D) are added to the dimension, enabling you to specify the users you want to give read and write access to in each of the available versions. For further details on access privileges, refer to the Setting security for models section in Chapter 10, System Administration.