Basic.Param


Basic.Param is the worksheet where you enter Basic Top Line Parameter’s Values per SKU for each of your Key Time Buckets. These basic values are necessary to calculate Top Line Parameter’s components in each of the time periods over the Planning Horizon. Basic.Param template is built automatically on a basis of two inputs: a list of SKUs which is extracted from the Basic.WorkCentersAndSkus worksheet, and a list of Time Buckets (Years, Half Years, Quarters, Months, Weeks or Days). If SKU ID, and SKU Description columns in the Basic.WorkCentersAndSkus are properly filled in, there should not be a problem to create this table.

Key Time Buckets define initial periods you intend to roll your plan from. Let us say, you would like to plan the Year 2014 in monthly buckets starting from January. In this case, the Key Time Bucket, you are supposed to provide basic Top Line Parameter’s values for, is January, 2014 (2014-M01). You may define basic Top Line Parameter’s values for several different types of Key Time Buckets within the same Basic.Param table. For example, if you wish to plan the Year 2014 in Half Year, Quarterly, Monthly, and Weekly periods, you can keep the basic Top Line Parameter’s values for the corresponding Key Time Buckets (2014-H01, 2014-Q01, 2014-M01, and 2014-W01) in the same worksheet.

Top Line Parameter is used to valuate Closing Stocks of the Time Buckets over the Planning Horizon. Its values are calculated as sums of Top Line Parameter’s components. These Components are computed as products of the Basic Top Line Parameter’s Values, and corresponding Closing Stocks. If we consider the example above in which we decided to build an Annual Plan for the Year 2014 in Monthly Buckets, basic Top Line Parameter’s values have to be specified per SKU for the Key Time Bucket ( January, 2014, or 2014-M01). In order to calculate Top Line Parameter’s components, each of the Basic Top Line Parameter’s Values must be multiplied by each of the corresponding Closing Stocks’ values for each of the months. In turn, Top Line Parameter’s components have to be summed up to get the Top Line Parameter’s values for the Time Buckets over the Planning Horizon (2014-M01 to 2014-M12).


Columns.

1. Time Bucket ID. Alphanumeric Unicode values. This column is automatically generated, and formats of Time Bucket IDs are predefined. Since values in this column are used as keys to retrieve information, it is better to leave them as they are.

2. SKU ID. Alphanumeric Unicode values. This column is automatically extracted from the Basic.WorkCentersAndSkus worksheet. Like above, it contains keys, and for the sake of consistency, it is OK not to change them.

3. SKU Description. Alphanumeric Unicode values. This column is also automatically extracted from the Basic.WorkCentersAndSkus worksheet. It does not contain keys. Changes will not have a significant negative impact on the rollingPlan functionality, but may lead to confusions while reviewing basic Top Line Parameter’s values.

4. Param.Values. Numeric positive basic values for the Top Line Parameter.


Common mistakes that may cause troubles later on.

1. Missing Time Bucket ID. Values in this column are automatically generated. You may run into a situation like this only if you decide to clear the contents of a cell. Latter may cause at least the following effects:

  • corresponding basic Top Line Parameter’s value will not be retrieved, and
  • in case you would like to add automatically more rows, the new rows will overlap with the old ones.

2. Missing SKU ID. This is also an automatically generated column containing keys. Similar to above, if contents of a certain cell are cleared, corresponding basic Top Line Parameter’s value will not be pulled out.

3. Missing SKU Description. One more automatically generated column. Unlike the above two, a missing value here will cause nothing but confusion.

4. Missing Param.Value. In this case, the basic Top Line Parameter’s value will be considered as zero.

5. Inconsistent Time Bucket ID. Names of the Time Buckets are generated on a basis of a predefined format. In order to avoid confusions, naming convention must be followed strictly. If you rename a Time Bucket, you may not be able to retrieve the corresponding basic Top Line Parameter’s value.

6. Inconsistent SKU ID. As mentioned before, values in this column are extracted from the Basic.WorkCentersAndSkus worksheet. If you are not satisfied with the ID of a given SKU, it is better to modify it in the Basic.WorkCentersAndSkus table. Like in the previous case, changing of SKU IDs in the Basic.Param worksheet is related to incorrect retrieval of the basic Top Line Parameter’s values.

7. Inconsistent SKU Description. This column does not contain critical information, but because SKU IDs, and SKU Descriptions normally go together, it is better to keep them as they are, or to edit them in the Basic.WorkCentersAndSkus worksheet.

8. Missing block of values. This situation occurs when you clear contents of one or more rows, and it may cause a lot of trouble especially when using the Add function. It is very important to avoid gaps in the Basic.Param worksheet. That is why, If you would like to clear a block of values, it is better to delete the rows instead.

9. Non numeric basic Top Line Parameter’s values. In this case, basic Top Line Parameter’s values will be considered as zeros.

10. Any combination of above.


Available Functions.

1. Build Template. This function rebuilds the Basic.Param template from scratch. It clears completely the worksheet area, and places column headers on the second row in accordance with the formats defined in the Dictionary. It also pops up a dialog to define the range of Time Buckets.

Here what it looks like:

Basic.Param_Build

 1.1. Bucket Type. In this Combo you select the type of Time Buckets. There are several options to choose from: Year, Half Year, Quarter, Month, Week, and Day.

1.2. Initial Values. In this context sensitive Group you describe your initial Time Bucket. The list of parameters that are supposed to be set depends on the selected type of Time Buckets as follows:

  • If you specify Year, only one item will appear in the Group: Year.
  • If you choose Half Year, Quarter, Month, or Week, two items will show up. To identify the initial Time Bucket in these cases, you will need to set both the Year and the corresponding Time Bucket.
  • If you decide to build the template in Daily buckets, you will have to provide the Year, the Month and the Day.

1.3. Number of Buckets. In this Text Box you enter the number of Time Buckets in the range. It should be noted that this number is not infinite, and it depends both on the number of available rows in the worksheet, and the number of SKUs, as for each Time Bucket a complete set of basic Top Line Parameter’s values is generated. The Upper Limit of this attribute is set to 1000.

1.4. OK. By clicking on this button you initiate the Template Generation Process. The very first step in this method is the Input Data Validation. If non-numeric, or out of range values are entered, the process will stop marking problematic areas with a red background.

 2. Add. This function is very similar to Build Template. It even uses the same dialog, but it is named differently. Unlike Build Template, Add does not clear or modify anything. It simply attaches a new set of rows at the end of the table. There is no problem to mix rows built for different Time Bucket types on the same Basic.Param worksheet. In other words, it is perfectly acceptable to create a set of records generated in Quarterly Buckets, and then to add another set created in Monthly, Weekly, or Daily buckets, etc. Another important thing to be mentioned is that combination of Time Bucket ID and SKU ID is unique in this table. To put it differently, if you decide to build a set of rows for March, 2014 twice, only one set will remain on the sheet, as repetitive rows are ignored.

When you click on Add, the following dialog appears:

Descriptions of the various controls in the dialog are the same as above:

2.1. Bucket Type. In this Combo you select the type of Time Buckets. There are several options to choose from: Year, Half Year, Quarter, Month, Week, and Day.

2.2. Initial Values. In this context sensitive Group you describe your initial Time Bucket. The list of parameters that are supposed to be set depends on the selected type of Time Buckets.

2.3. Number of Buckets. In this Text Box you enter the number of Time Buckets in the range. It should be noted that this number is not infinite, and it depends both on the number of available rows in the worksheet, and the number of SKUs, as for each Time Bucket a complete set of basic Top Line Parameter’s values is generated. The Upper Limit of this attribute is set to 1000.

2.4. OK. By clicking on this button you initiate the process.


3. Clear. As its name says, this function deletes all data, and cell formats from Time Bucket ID, SKU ID, SKU Description, and Param.Values columns. Column headers remain unchanged. Microsoft Excel® Undo function will have no effect after Clear is executed.


4.Standard Microsoft Excel® Worksheet Functions. In addition to the rollingPlan Ribbon Tab functions, you can do almost anything which is normally done on a Microsoft Excel® worksheet including filtering, sorting, formatting, etc. You cannot insert rows, and insert or delete columns.


Customization.

Like the rest of the worksheets in the Basic group, Basic.Param’s column headers can be customized by changing the contents and the formats of the corresponding cells in the Dictionary, or by using standard Microsoft Excel® formatting techniques.

You can use regular Microsoft Excel® cell formatting to give the area around column headers the look you like.

 Watch How to fill out Basic.Param


Important things to remember.

  1. Basic Top Line Parameter’s Values are used to valuate your Closing Stocks. They must be specified per SKU for each of your Key Time Buckets.
  2. Time Bucket IDsSKU IDs, and SKU Descriptions are automatically generated and must be kept as they are.
  3. You can keep Basic Top Line Parameter’s Values defined for different types of Time Buckets in the Basic.Param table.
  4. Do not leave blanks or gaps in the table, especially in Time Bucket IDSKU ID, and SKU Description columns.
  5. Column headers can be customized by changing the contents, and the formats of the corresponding cells in the Dictionary.
  6. Microsoft Excel® Undo function will not be able to bring the old values back after Clear.

<< Basic.OpenStocks Basic.ParamConstraints >>