Basic.Demand


Basic.Demand is the worksheet where you set your Demand, and Stock Constraints (Target Stock Levels). 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). In case SKU ID, and SKU Description columns in the Basic.WorkCentersAndSkus are properly filled in, there should not be a problem to create the template.

Demand could be viewed either from historical prospective, i.e. it may contain records of past sales, shipments, etc., or it could be a projection of future needs in a form of Demand Forecasts, or real orders. It is important to note that:

  • Units of Measure of Demanded Quantities must be the same as the Units of Measure of Production Rates specified in the Basic.WorkCentersAndSkus worksheet. For example, if you set Production Rates in pieces per minute, your Demand must be defined in pieces. In case Production Rates are set in kilos per hour, Demand must be entered in kilos.

Columns.

1. Time Bucket ID. Alphanumeric Unicode values. This column is automatically generated, and formats of the 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. SKU ID. Alphanumeric Unicode values.  Data in this column are automatically extracted from the Basic.WorkCentersAndSkus worksheet. Like above, this column contains keys, and it is not recommended to change them.

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

4. Qtty(Units). Numeric Values describing Demanded Quantities in the Units of Measure your Production Rate is defined in. You can put either positive, or negative numbers in this column, as negative Demand can be used in some special cases.

5. Stock Constraints Coeff. Numeric values which are used to calculate Stock Constraints (Target Stock Levels, TSL). Like above,  positive, and negative numbers are acceptable. Theoretically speaking, this coefficients determine the fraction of the next period’s Demand to be used as a Closing Stock in the current period.

6. Stock Constraints (Target Stock Levels, TSL). Numeric values defining the Closing Stock Levels per SKU, per Time Bucket. Like in the previous case, either positive, or negative numbers could be entered in this column.

The best way to explain how Stock Constraint Coefficients turn the next Time Buckets’ Demanded Quantities into Closing Stocks is to review an example.

Let us consider the following case:

(i) We would like to define Demanded Quantities of four products: Product 1, Product 4, Product 5, and Product 7  in both Monthly, and Yearly Time Buckets.

(ii) We start with creating records for the first two months of the Year 2014. Then, we compliment already entered data with a group of Yearly Buckets for the Year 2014. Next, we decide to add another set of Monthly buckets, this time for the third month of 2014. And,finally, we generate four more rows in Yearly Buckets for the Year 2015.

(iii) Here is how Stock Constraints (Target Stock Levels) are calculated:

Basic.Demand_StockCalculation

1. In Time Bucket 2014-M01, which corresponds to the first month of the Year 2014, Product 1 has a value of 1 in the Stock Constraints Coeff. column. Meaning that Closing Stock of the period 2014-M01 (or the Open Stock of the period 2014-M02) is supposed to cover the total Demanded Quantity of Product 1 (210) in the next period (2014-M02).

2. In Time Bucket 2014-M02, which corresponds to the second month of the Year 2014, Product 1 has a value of 0.5  in the Stock Constraints Coeff. column. In other words, Closing Stock of the period 2014-M02 (or the Open Stock of the period 2014-M03) has to cover half of the Demanded Quantity in the next period (2014-M03). In the table, we can find that this quantity is 310, and the corresponding calculated Stock Constraint value is 155.

3. In Time Bucket 2014-M03, which corresponds to the third month of the Year 2014, Product 1 again has a value of 1 in the Stock Constraints Coeff. column, but since there is no other Monthly bucket defined after it, the corresponding Stock Constraint value is set to 0.

4. In the Yearly Bucket 2014, Product 1 has a value of 1 in the Stock Constraints Coeff. column. Hence, Closing Stock of the period 2014 (or the Open Stock of the period 2015) must cover the total Demanded Quantity of 2015. It can be easily found that this quantity is equal to 2010.

5. In the Yearly Bucket 2015, the value of the Stock Constraint Coefficient is 0.25, but because this is the last Yearly Bucket, and there is no Demanded Quantity available for a next period of the same type, the corresponding value of the Stock Constraint is set to 0.

It should be noted that you are not obliged to follow the calculation rules above. This is just the way the first version of the rollingPlan computes the Stock Constraints (TSL). Each company has its own ways of calculating Target Stock Levels. Therefore, feel free to enter whatever numbers you consider appropriate in this column.


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 Demanded Quantity, and Stock 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 SKU ID. This is also an automatically generated column containing key values. Similar to above, if contents of a certain cell are cleared, corresponding Demanded Quantity, and Stock Constraint 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 Qtty(Units). In this case, Demanded Quantity will be considered as zero.

5. Missing Stock Constraints Coeff. Normally, these values are automatically filled in when you build the Demand Template, or when new Time Buckets are added. A missing value will be considered as zero, but it will impact only calculation of Stock Constraints. If you provide your own values for the Stock Constraints, you do not have to worry about Stock Constraint Coefficients.

6. Missing Stock Constraints (Target Stock Levels, TSL). A missing value in this column is considered as zero.

7. 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 data regarding corresponding Demanded Quantity, and Stock Constraint.

8. 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 the SKU ID in the Basic.Demand worksheet will lead to incorrect retrieval of the corresponding Demanded Quantity, and Stock Constraint.

9. Inconsistent SKU Description. This column does not contain critical information, but because SKU ID, and SKU Description normally go together, it is better to keep them as they are, or to change them in the Basic.WorkCentersAndSkus table.

10. Missing block of values. You will run into a situation like this, if you clear the contents of one or more rows. It may cause a lot of trouble, especially if  you decide to use Add,or Calculate Stocks function. In the first case, you will get overlapping records. In the second case, Stock Constraints will not be calculated properly. It is very important to avoid gaps in the Basic.Demand worksheet. Therefore, If you need to clear row data, simply select and delete the rows.

11. Non-numeric Qtty(Units). In this case, Demanded Quantity is considered as zero.

12. Non-numeric Stock Constraints Coeff. This value is considered as zero, but it will have an impact only on calculation of the Stock Constraints.

13. Non-numeric Stock Constraint. This value is considered as zero.

14. Any combination of above.


Available Functions.

 1.Build Template. This function rebuilds the Basic.Demand 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 Time Buckets interval.

Here how 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 specify Year as a 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: Year, and the selected Time Buckets’ 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, enter the number of Time Buckets you would like to build records for. 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. For each Time Bucket a complete set of SKUs is generated.  The Upper Limit of this parameter is set to 1000.

1.4. Default Value. In this Text Box you define some initial value for the Stock Constraints Coeff. which is used later to calculate Stock Constraints (Target Stock Levels, TCL).

1.5. 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 as shown below:

  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.Demand worksheet. In other words, it is perfectly acceptable to create a set of Demands generated in Quarterly Buckets, and then to add more sets created in Monthly, Weekly, or Daily buckets, etc. Another important thing to be mentioned is that combination of Time Bucket ID and SKU ID must be unique. 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 Time Buckets to be added. 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 to be generated.

2.4. Default Value. In this Text Box you define some initial value for the Stock Constraints Coeff. which is used later to calculate Stock Constraints (Target Stock Levels, TCL).

2.5. 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, Qtty(Units), Stock Constraints Coeff., and Stock Constraints columns, but leaves column headers intact.  Microsoft Excel® Undo function will not be able to bring the old values back after Clear.


4. Calculate Stocks. This function calculates values of the Stock Constraints (Target Stock Levels) by using next periods’ Demanded Quantities, and Stock Constraint Coefficients as described above. The most important prerequisites to compute these values properly are:

  • To make sure that there are no gaps in the table.
  • To keep values of Time Bucket IDs, and SKU IDs as generated, and
  • To enter proper numbers for Stock Constraint Coefficients, as well as for Demanded Quantities.

When calculations are completed, the method will pop up a small Dialog Box.


5. 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 will not be able to insert rows, and to insert, or delete columns.


Customization.

Like in the rest of the worksheets of the Basic Group, Basic.Demand’s column headers can be customized by changing the contents, and the formats of the corresponding cells in the Dictionary. Another option is to apply 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.Demand


Important things to remember.

  1. Time Bucket ID, SKU ID, and SKU Description columns are automatically generated, and must be kept as they are.
  2. Units of Measure of Demanded Quantities must be the same as the Units of Measure of Production Rates specified in the Basic.WorkCentersAndSkus worksheet.
  3. You can store Demanded Quantities defined for different types of Time Buckets in the Basic.Demand table.
  4.  Stock Constraints Coeff. are used to calculate Target Stock Levels (Stock Constraints). They determine the fraction of the next period’s Demand to be used as a Closing Stock in the current period.
  5. Do not leave blanks or gaps in the table, especially in Time Bucket IDSKU ID, and SKU Description columns.
  6. Column headers can be customized by changing the contents, and the formats of the corresponding cells in the Dictionary.
  7. Microsoft Excel® Undo function will not be able to bring the old values back after Clear.

<< Basic.WorkCentersConstraints Basic.OpenStocks >>