Basic.OpenStocks


Basic.OpenStocks is the worksheet where you set the Initial On Hand Balances of your SKUs for the Key Time Buckets. 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 out, 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 Initial On Hand Balances for, is January, 2014 (2014-M01).

You may define Open Stocks for several different types of Key Time Buckets within the same Basic.OpenStocks table. For example, if you would like to plan the Year 2014 in Half Year, Quarterly, Monthly, and Weekly periods, you can keep the Open Stocks for the corresponding Key Time Buckets (2014-H01, 2014-Q01, 2014-M01, and 2014-W01) on the same worksheet.


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. Value changes will not have a significant negative impact on the rollingPlan functionality, but may lead to confusions while reviewing Open Stocks.

4. Stocks. Numeric  values describing Open Stocks. Units of Measure of Open Stocks must comply with the Units of Measure of Production Rates defined in the Basic.WorkCentersAndSkus worksheet. For example, if your Production Rates are specified in pieces per hour, Open Stocks must be set in pieces. In case Production Rates are set in kilos per minute, enter your Open Stocks in kilos.


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 Open Stock 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 key values. Similar to above, if contents of a certain cell are cleared, corresponding Open Stock Quantity 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 Stocks. In this case, Open Stock quantity 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 Open Stock.

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.OpenStocks worksheet is related to incorrect retrieval of Open Stock quantities.

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.OpenStocks 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 Stocks. In this case, Open Stock quantity will be considered as zero.

10. Any combination of above.


Available Functions.

1. Build Template. This function rebuilds Basic.OpenStocks 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. The list of parameters that are supposed to be set depends on the selected type of Time Buckets as follows:

  • If you choose Year, 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, 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 Open Stocks is generated. 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 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.OpenStocks 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 pops up:

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

2.1. Bucket Type. In this Combo you select 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. 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 Open Stocks is generated. The Upper Limit of this parameter is set to 1000.

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


3. Clear. As its name says, this function clears all data as well as cell formatting in Time Bucket ID, SKU ID, SKU Description, and Stock columns, but leaves the column headers unaffected. 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 anything which is normally done on a Microsoft Excel® worksheet including filtering, sorting, formatting, etc. You cannot insert rows, and insert or delete columns, however.


Customization.

Like the rest of the worksheets in the Basic Group, Basic.OpenStocks’ 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.OpenStocks


Important things to remember.

  1. OpenStocks represent the Initial On Hand Balances of your SKUs for the Key Time Buckets.
  2. Time Bucket IDs, SKU IDs, and SKU Descriptions are automatically generated and must be kept as they are.
  3. You can keep Open Stocks defined for different types of Time Buckets in the Basic.OpenStocks 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.Demand Basic.Param >>