Basic.WorkCentersConstraints


Basic.WorkCentersConstraints is the worksheet where you define Available Times for each Work Center/Production Line per Time Bucket. Template is  automatically generated on a basis of two inputs: a list of Work Centers/Production Lines which is extracted from the Basic.WorkCentersAndSkus worksheet, and a range of Time Buckets (Years, Half Years, Quarters, Months, Weeks, or Days). Practically speaking, if Basic.WorkCentersAndSkus worksheet is filled out properly, there should not be a problem to create the Basic.WorkCentersConstraints table.

Time Constraints (Available Times) are considered to be the total times Work Centers/Production Lines are planned to work in the selected Time Buckets. For instance,  if you decide to plan in Monthly buckets, and your Work Center/Production Line is planned for 24/7 operation in March,2014, your available time can be calculated as (Work Days) x (Work Time Units).  In order to get correct results, it is a must to use the same Work Time Units your Production Rate is defined for in the Basic.WorkCentersAndSkus worksheet. For example, if you define your Production Rate in pieces per hour, your Work Time Units must be Hours. Considering this, Available Time is calculated as (31) x (24) = 744 hours.  In case you do not work on Saturdays and Sundays,  and your shop operates in two 8 hour shifts per day, your Available Time in March, 2014 will be (5+5+5+5+1) x (2 x 8) = 336 hours.  However, if you spend 1 hour to clean and start up your equipment each work day, it is up to you to decide whether to subtract this time from your Available Time, i.e. to calculate it as (5+5+5+5+1) x (7 + 8) = 315.


Columns.

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

2. Work Center. Alphanumeric Unicode values. This column is 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. Time Constraint. Numeric positive values. Available Times for the corresponding Time Buckets should be entered in this column. Work Centers/Production Lines may have different Time Constraints in different Time Buckets. As mentioned earlier, Units of Measure of Available Times must be the same as the Units of Measure of your Production Rates defined in the Basic.WorkCentersAndSkus worksheet, i.e. if Production Rate is defined as kilos or pieces per minute, Time Constraints must be defined in minutes too. In case, Production Rate is defined as kilos or pieces per hour, Time Constraints must be also defined in hours. It is not required Work Centers/Production Lines to have the same Available Times.


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  if you decide to clear the contents of a cell. Latter may cause at least two effects:

  • corresponding Time Constraint will not be retrieved, and
  • in case you would like to add automatically more rows, the new ones will overlap with the old.

2.Missing Work center. This is also an automatically generated column containing key values. If contents of a certain cell are cleared, corresponding Time Constraint will not be pulled out.

3.Negative or missing Time Constraint. Values like this will be considered as zeroes.

4.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. Otherwise, corresponding Time Constraints will not be pulled out correctly.

5.Inconsistent Work Center. As mentioned before, values in this column are extracted from the Basic.WorkCentersAndSkus worksheet. If you are not satisfied with the name of a given Work Center/Production Line, it is better to change it in the Basic.WorkCentersAndSkus table.

6.Non numeric Time Constraint. Values like this will be considered as zeroes.

7.Missing block of values. If you would like to clear the contents of several Time Buckets, Work Centers, and Time Constraints all together, the best way is to delete the rows, and not leave gaps in the table.


Available Functions.

 1.Build Template. This function builds Basic.WorkCentersConstraints 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 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 as follows:

  • If you specify Year as the type of the Time Buckets, only one item will appear in the Group: Year.
  • If you select Half Year, Quarter, Month, or Week, two items will show up: the Year, and the corresponding type of Time Buckets.
  • 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. It depends both on the number of available rows in the worksheet, and on the number of Work Centers/Production lines. For each Time Bucket a complete set of Work Centers is generated. The Upper Limit of this parameter is set to 1000.

1.4. Default Value. In this Text Box you specify some initial value for the Time Constraints. The Units of Measure must corresponding to the Units of Measure of Production Rates in the Basic.WorkCentersAndSkus table.

1.5. OK. By pressing 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.WorkCentersConstraints worksheet. In other words, it is perfectly acceptable to create a set of constraints generated in Quarterly Buckets, and later to add more sets created in Monthly, Weekly, or Daily buckets, etc. Just,do not forget to recalculate the Available Times accordingly. Another important thing to be mentioned is that combination of Time Bucket ID and Work Center 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 by the function.

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.

2.4. Default Value. In this Text Box you define some initial value for the Available Times. Units of Measure must corresponding to the Units of Measure your Production Rates are defined for in the Basic.WorkCentersAndSkus table.

2.5. OK. By pressing this button you start the process of adding more rows to the table.


 3.Clear. As its name says, this function deletes all data including cell formats from Time Bucket ID, Work Center, and Time Constraint columns. Column headers are not affected. Microsoft Excel® Undo function will not work after Clear.


4.Standard Microsoft Excel® Worksheet Functions. In addition to the rollingPlan Ribbon Tab functions, you can do almost everything 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 in the rest of the worksheets of the Basic group, Basic.WorkCentersConstraints’ column headers can be customized by changing the contents, and the formats of the corresponding cells in the Dictionary. You can also apply most of the 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.WorkCentersConstraints


Important things to remember.

  1. Time Bucket ID, and Work Center columns serve as keys to pull out information from this table. They are automatically generated, and must be kept as they are.
  2. Time Constraint values must be defined in the same Units of Measure your Production Rates are defined  in the Basic.WorkCentersAndSkus worksheet.
  3. You can keep Time Constraints specified for different types of Time Buckets in the Basic.WorkCentersConstraints table.
  4. Do not leave blanks or gaps in the table, especially in Time Bucket ID, and Work Center 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 work after Clear.

    << Basic.WorkCentersAndSkus Basic.Demand >>