Basic.ParamConstraints is the worksheet where you enter Top Line Parameter’s Upper Limits per Time Bucket. Template is built automatically on a basis of a list of Time Buckets (Years, Half Years, Quarters, Months, Weeks or Days). You may define Top Line Parameter’s Constraints’ values for several different types of Time Buckets within the same Basic.ParamConstraints worksheet. For example, if you wish to plan the Year 2014 in Half Year, Quarterly, Monthly, and Weekly periods, you can keep Top Line Parameter’s Constraints’ values for the corresponding Time Buckets in the same table.
Top Line Parameter is used to valuate Closing Stocks of each Time Bucket in the Planning Horizon. Its values are calculated as sums of Top Line Parameter’s components. These components are computed as products of basic Parameter’s values, and corresponding Closing Stocks.
Top Line Parameter’s Constraints define upper limits for the Top Line Parameter per Time Bucket. For example, let us say that valuation of your Closing Stocks at the end of each quarter is done on a basis of Standard Costs. You have your Quarterly Inventory Targets, and in order not to disappoint your Management Team, you would like to plan your production volumes in such a way that your inventory at the end of each quarter is lower than the corresponding target. To get the job done, you may start with a rough cut Annual Plan in Quarterly Buckets. If you assume that your Standard Costs per SKU do not vary significantly throughout the year (which normally is not the case), you may consider the Standard Costs per SKU for the Q1 as your basic Top Line Parameter’s values, and your Quarterly Inventory Targets as Top Line Parameter’s Constraints.
1. Time Bucket ID. Alphanumeric Unicode values. This column is automatically generated, and formats of Time Bucket IDs are predefined. Since values of this column are used as keys to retrieve information, it is better to leave them as they are.
2. Param.Constraint. Numeric positive values describing Top Line Parameter’s Constraints.
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 Top Line Parameter’s Constraint value will not be retrieved.
- In case you would like to add automatically more rows, the new rows will overlap with the old ones.
2. Missing Param.Constraint. In this case, Param.Constraint will be considered as zero.
3. Inconsistent Time Bucket ID. Names of 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 Top Line Parameter’s Constraint.
4. Non numeric or negative Param.Constraint values. In this case, Top Line Parameter’s Constraint values will be considered as zeroes.
5. 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.ParamConstraints worksheet. If you would like to clear a block of values, it is better to delete the rows instead.
6. Any combination of above.
1. Build Template. This function builds Basic.ParamConstraints 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:
1.1. Bucket Type. In this Combo you select Time Bucket Types. 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: the Year and the corresponding Time Bucket type.
- 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 on the number of available rows in the worksheet. Upper limit of this parameter 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,and problematic areas will be marked 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.ParamConstraints worksheet. In other words, it is perfectly acceptable to create a set of constraints generated in Quarterly Buckets, and then to add another set created in Monthly, Weekly, or Daily buckets, etc.
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 on the number of available rows in the worksheet. The Upper Limit of this parameter is set to 1000.
2.4. Default Value. In this Text Box you define initial values for the Top Line Parameter’s Constraints.
2.5. OK. By pressing this button you initiate the process.
3. Clear. As its name says, this function deletes all data as well as cell formatting from Time Bucket ID, and Param.Constraints columns, but it does not affect column headers in any way. Microsoft Excel® Undo function will not work after Clear is applied.
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.
Like in the rest of the worksheets in the Basic group, Basic.ParamConstrints’ 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.
Important things to remember.
- Top Line Parameter’s Constraints represent the Upper Limits of the Top Line Parameter.
- Time Bucket IDs are automatically generated and must be kept as they are.
- You can keep Top Line Parameter’s Constraints defined for different types of Time Buckets in the Basic.ParamConstraints table.
- Do not leave blanks or gaps in the table, especially in Time Bucket ID column.
- Column headers can be customized by changing the contents, and the formats of the corresponding cells in the Dictionary.
- Microsoft Excel® Undo function will not be able to bring the old values back after Clear.