Main.RollingPlan


Main.RollingPlan is the worksheet where planning is done. The Planning Template has a predefined structure. It is automatically generated on a basis of a list of Time Buckets (Years, Half Years, Quarters, Months, Weeks, or Days), over and above data specified in the worksheets of the Basic Group, and the Dictionary.

Technically speaking, in the Main.RollingPlan, for every SKU produced on each of the Work Centers/Production Lines in each and every Time Bucket, you are supposed to place a quantity that brings its corresponding Closing Stock to the Target Stock Level taking into account the Open Stock, Demanded Quantity, Capacity Constraint, and the Top Line Parameter Constraint.

Complexity of the Planning Problem comes from many directions:

  1. You may want to produce more than one SKU on each of your Work Centers/Production Lines. Meaning that Available Production Time has to be split between several products.
  2. Different SKUs are often manufactured at different Production Rates, and with different Efficiencies. In other words, it takes different time to produce same number of units of different SKUs on the same Work Center/Production Line.
  3. You may have the opportunity to make the same SKU on more than one Work Center/Production Line. Hence, you have to balance quantities coming from various sources to meet your Target Stock Levels.
  4. You need to manage your Inventory Position properly in order not to tie up too much cash in stocks. In this case, all your Production Processes with their constraints and complexities must be directed towards meeting your Inventory Targets.

Main.RollingPlan is designed to help you create Multiple Constraint Rolling Plans either manually or automatically. This is by far the most complex worksheet in the tool, as it pulls out information from the rest of the tables. It is very important to fill in the worksheets of the Basic Group properly before generating the Main.RollingPlan template.

Data in the Main.RollingPlan can be viewed in different ways, but roughly the template can be divided in two areas:

1. Planning Area. In this part of the worksheet you can enter quantities per SKU, per Time Bucket in order to satisfy the demand, and to build Closing Stocks taking into account Stock Constraints, Available Capacities, and Top Line Parameter’s constraints.

2. Constraints Area. In this region, you can see Stock Constraints, Available Times, and Top Line Parameters’ Constraints specified in Basic.Demand, Basic.WorkCentersAndSkus, and Basic.ParamConstraints tables. In addition to these, here you can find calculated values of the times required to manufacture Planned Quantities on your Work Centers/Production Lines, as well as calculated Top Line Parameter’s values.


Important Strips and Fields.

1. Time Buckets Strip. This part of the Planning Template shows Time Buckets of the Planning Horizon. There are two copies of this set: one for the Planning Area, and one for the part of the Constraints Area above Required Times, and Stock Constraints. In all rollingPlan worksheets Time Bucket names are automatically generated in accordance with a certain naming convention.

2. Bucket Type Fields. These cells designate the type of Time Buckets used in the Planning Template, namely Year, Half Year, Quarter, Month, Week, or Day.

3. Plan Name Fields. Rolling Plans can be saved in the Main.Plans worksheet, in order later to be retrieved either fully or partially. Plan names must be unique, i.e. it is not possible to save two plans sharing the same name. In one of these cells you define the name of the plan you are willing to save, or retrieve.

4. Column Names Strip. In this area, you can find names of the columns as defined in the Dictionary. It should be noted that for each Time Bucket there is a pair of Planned Quantities and Closing Stocks, as well as a corresponding pair of Required Times, and Stocks Constraints columns.

5. Remaining capacity, Total Required Time, Available Time Strip. A strip like this is generated after each set of rows corresponding to the same Work Center/Production Line. It serves two purposes:

  1. Separates Planning Areas bound to various Work Centers/Production Lines.
  2. Provides cells to present Remaining Capacities, sums of Required Times to produce Planned Quantities, Available Times per Time Bucket per Work Center/Production Line, etc.

Static Data.

Some parts of the data presented on the Main.RollingPlan worksheet remain unchanged during planning. These are either keys used to pull out information from the Basic Group Tables (Work Centers, SKU IDs), or initial/basic values specified for the Key Time Bucket (Open Stocks, Param.Values) , or constraints (Stock Constraints, Param.Constraints, Available Times).

1. Work Center, SKU ID, SKU Description. Values in these three columns are extracted from the Basic.WorkCentersAndSkus table. They serve as keys to retrieve information placed in the rest of the columns of the Main.RollingPlan. This area is locked for editing, but can be filtered. Cell formats are copied from the Dictionary.

2. Open Stocks. Values in this column are pulled out from the Basic.OpenStocks worksheet with a formula. They represent the Initial On Hand Stock Balance for the first planning period of the Planning Horizon (the Key Time Bucket). Any change you make in the Basic.OpenStocks table is directly transferred to the Main.RollingPlan. Like above, this area is locked for editing, but can be filtered. Cell formats are taken from the Dictionary.

3. Stock Constraints. Values in these columns correspond to the Target Stock Levels defined for each Time Bucket in the Planning Horizon. They are extracted from the Basic.Demand table with a formula which uses Work Centers, SKU IDs, and Time Bucket IDs as keys. Similar to the previous case, Main.RollingPlan is directly impacted by any change in the Basic.Demand worksheet. These areas are also locked for editing, but can be filtered. Cell formats are copied from the Dictionary.

4. Available Times. These values are placed on the Remaining Capacity, Total Required Time, Available Time Strips in Stock Constraints columns. The location is a bit counter-intuitive, but you can get used to it quickly taking into account that these important constraints are positioned next to the corresponding sums of Required Times. Numbers that appear in the cells come from the Basic.WorkCentersConstraints table. Cell formats are defined in the Dictionary.

5. Param.Values. This column contains Basic Top Line Parameter’s values defined for the Key Time Bucket. They are used to calculate Top Line Parameter’s components which are summed up to get Top Line Parameter’s values. Basic Top Line Parameter’s values are pulled out from the Basic.Param worksheet with a formula. You may notice, that some of the cells in the column are empty. This is done on purpose to avoid double valuation of Closing Stocks for the SKUs produced on more than one Work Center/Production Line. Param Values column is locked for editing, can be filtered, and inherits the formats defined in the Dictionary.

6. Param.Constraints. This row contains Top Line Parameter’s Constraints for each Time Bucket within the Planning Horizon. A formula draws out the numbers from the Basic.ParamConstraints worksheet. Cells of the row are locked for editing, but they can be formatted in the Dictionary.


Dynamic Data.

From a practical prospective, if all Static Data in the worksheets of the Basic Group, and respectively in the Main.RollingPlan are properly filled in, the only thing that should be done to create a plan is to enter either manually or automatically the Planned Quantities for each Time Bucket. In order to evaluate how these quantities stand against Stock Constraints (Target Stock Levels), Capacity Constraints (Available Machine Times), and Top Line Parameter’s Constraints, some calculations must be done. All fields directly related to the Planned Quantities such as Required Times, Remaining Capacities, or the sums of Top Line Parameter’s components, change during planning, and form Dynamic Data areas. 

1.Planned Quantities and Closing Stocks. Cells in Planned Quantities columns are the only ones open for editing. When you enter numbers in these cells, Closing Stocks (End On Hand Stock Balances) for each SKU in every Time Bucket are calculated the following way:

Where:

  • n is the number of Work Centers/Production Lines the SKU is manufactured on, and
  • Demanded Quantity is the one specified in the Basic.Demand worksheet for the corresponding Time Bucket.

In general, rollingPlan assumes that each SKU can be produced on more than one Work Center/Production Line. Therefore, in order to get the Closing Stock right, Planned Production of all quantities on all Work Centers/Production Lines capable of manufacturing a given SKU must be summed up. To put it differently, if a given SKU is produced on more than one Work Center/Production Line:

  1. In the Open Stocks column you will see same numbers in each row containing the SKU.
  2. You may have different numbers in the corresponding cells of the Planned Quantities column, but
  3. You will end up with same numbers in the Closing Stocks column.

When sum of the Open Stock, and the Planned Quantity is not sufficient to cover Demanded Quantity, corresponding Closing Stock becomes negative to signify deficiency, and related Closing Stock cell turns red.

Note, that for the Key Time Bucket, Open Stocks are located in the Open Stocks Column. For the rest of the Time Buckets, Closing Stocks of the previous Time Bucket serve as Open Stocks.

2. Time Required. From a theoretical view point, there are various ways to calculate the time required to produce a given quantity of a certain SKU on a particular Work Center/Production Line. rollingPlan uses the most straightforward approach:

Normally, Efficiency is presented with a number between 0 and 1. The higher the Efficiency, and Production Rate, the lower the time required to produce a given quantity of a certain SKU.

3. Total Required Times. In each of the Time Buckets within the Planning Horizon, Machine Times of Work Centers/Production Lines are split between various SKUs produced. As mentioned earlier, Available Machine Times per Time Bucket serve as constraints. They are defined in the Basic.WorkCentersConstraints table. To find out how your plan utilizes Available Machine Times, Required Times to produce Planned Quantities per SKU must be summed up.

4. Remaining Capacity %. This parameter shows what percentage of the Available Machine Time of a certain Work Center/Production Line remains to be utilized in each Time Bucket of the Planning Horizon. It is calculated as follows:

 

When Planned Quantities require more time to produce than it is available in the Time Bucket, this parameter turns negative, and the corresponding cell turns red.

5. Top Line Parameter’s Component Sums. Top Line Parameter’s components are calculated as products of basic Parameter’s values (located in the Param.Values column), and corresponding Closing Stocks for each Time Bucket within the Planning Horizon. Top Line Parameter’s Component Sums are nothing but subtotals of Top Line Parameter’s components computed per Work Center/Production line. Components corresponding to negative Closing Stocks are not counted in the sum.

6. Param.Sums (Top Line Parameter’s Values). Contents of these cells are calculated as totals of Top Line Parameter’s Component Sums for each of the Time Buckets within the Planning Horizon.


Common mistakes that may cause troubles later on.

In the Main.RollingPlan worksheet, all the cells except for those related to the Planned Quantities,and the current Rolling Plan Name are locked for editing. Latter leaves quite a few opportunities to make mistakes:

1. Non-numeric values of Planned Quantities. If you enter such values as Planned Quantities, you will get a “#VALUE!” error in all related cells, and corresponding Required Times will be zeroed down. If SKUs are planned on more than one Work Center/Production Line, this effect will spread out via the Closing Stocks’ cells.


Available Functions.

1. Build Rolling Plan File. By Clicking on this button you initiate the rollingPlan Worksheet Collection Building Process. All worksheets in the current Microsoft Excel® Workbook will be deleted, and a brand new collection of rollingPlan tables will be created.

2. Build Rolling Plan Template. This function deletes all the data, as well as cell formats from the Main.RollingPlan worksheet. It creates a new Planning Template on a basis of a list of Time Buckets, and information specified in the worksheets of the Basic Group, and the Dictionary. In order to build a Rolling Plan Template, it is a must to have some Work Centers/Production Lines defined in the Basic.WorkCentersAndSkus worksheet.

Template Generation Process pops up a dialog in which you define the type of Time Buckets, and the span of the Planning Horizon:

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 Key Time Bucket). The list of parameters to be set depends on the selected type of Time Buckets:

  • 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: Year, and selected 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.

2.3. Number of Buckets. In this Text Box you enter the number of Time Buckets in the Planning Horizon. It should be noted that the upper limit of this parameter is set to 1000.

2.4. OK. By pressing 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.  

Watch How to build a Rolling Plan template


3. Save Plan. Rolling Plans can be stored in the Main.Plans worksheet. By clicking on this button you initiate a Save Process.  An Important thing to be mentioned is that Plan Names must be unique. In other words you cannot save two plans with the same name.

4. Load Plan. You can load either fully or partially a plan saved in the Main.Plans table into the current Rolling Plan by clicking on this button.  There are two things to pay attention to:

  • Time Buckets’ Type of the plan to be loaded must be the same as the one of the current Rolling Plan, and
  • The name of the plan to be loaded must be written in the Plan Name field of the current Rolling Plan.      

Before loading the plan, you have to choose Work Centers/Production Lines, and Time Buckets to retrieve values for:

1. Work Centers/Production Lines to be selected.

2. Time Buckets to be selected.

3. Areas where retrieved values from a plan stored in the Main.Plans should be placed.

There is no need to make any selections on the worksheet, as the Plan Loading Process pops up a two-tab dialog which allows you to choose Work Centers/Production Lines, and Time Buckets:

Work Centers Tab.

4.1. Work Centers/Production Lines Selection List. Work Centers/Production Lines in the current Rolling Plan Template are listed in this box. You can get your desired combinations of Work Centers/Production Lines by making multiple selections (CTRL+Left Click).

4.2. Select/Unselect All Work Centers/Production Lines Button. This button selects/unselects all items on the list.

Parameters Tab.

4.3. Time Buckets Selection List. Periods of the Planning Horizon in the current Rolling Plan are listed in this box. Like above, you can make multiple selections (CTRL+Left Click) to get your desired combination of Time Buckets.

4.4. Select/Unselect All Time Buckets Button. This button selects/unselects all time periods on the list.

4.5. OK Button. Pressing on this button initiates a Data Retrieval Process in accordance with the choices made in the Work Centers/Production Lines Selection List, and the Time Buckets Selection List. This function also takes into account the Time Buckets’ Type, and the name of the Rolling Plan to pull out data from.

The process clears all data in the selected areas, first. Then, it populates desired cells with information from the Main.Plans table. It should be noted that Standard Microsoft Excel® Undo command cannot be used to bring the old values back.


5. Clear Plan. This function is used to delete either fully, or partially values in the Planned Quantities cells of the current Rolling Plan Template. The process is very similar to the one used to Load a Rolling Plan. As a first step, you have to choose the areas to be cleared:

1. Work Centers/Production Lines to be selected.

2. Time Buckets to be selected.

3. Areas to be cleared.

Like in the previous case, there is no need to make any selections in the table, as the Clear Process pops up a two-tab dialog which allows you to choose Work Centers/Production Lines, and Time Buckets: 

Work Centers Tab. 

5.1. Work Centers/Production Lines Selection List. You can get your desired combinations of Work Centers/Production Lines by making multiple selections (CTRL+Left Click) in this List Box.

5.2. Select/Unselect All Work Centers/Production Lines Button. This button selects/unselects all items on the list.

Parameters Tab. 

5.3. Time Buckets Selection List. Similar to above, you can make multiple selections (CTRL+Left Click) to get your desired combination of Time Buckets.

5.4. Select/Unselect All Time Buckets Button. This button selects/unselects all time periods on the list.

5.5. OK Button. Clicking on this button initiates a Data Clear Process in accordance with the choices made in the Work Centers/Production Line Selection List, and the Time Buckets Selection List. It should be mentioned that Standard Microsoft Excel® Undo command cannot be used to recall the old values.


6. Automated Planning. As its name suggests, this special function is designed to make the life of a planner slightly easier. The underlying method is a bit complex. So, it deserves a special page.

Watch How to use a Rolling Plan Template Watch How to customize a Rolling Plan template


Important Things to Remember.

  1. Before you start generating the Main.RollingPlan template, make sure tables of the Basic group are properly populated.
  2. In the Planned Quantities columns, you can enter values either manually or automatically.
  3. You can find: Target Stock Levels per SKU in the Stock Constraints columns, Remaining Capacity Percentages per Work Center/Production Line on the Remaining capacity, Total Required Time, Available Time Strip just beneath the Planned Quantities, and Top Line Parameter’s Values per Time Bucket on the Param.Sum row.
  4. Before you Save your Rolling Plan, you must give it a unique name in the Plan Name cell. Before you Load  a saved Rolling Plan, you must enter its name in the Plane Name cell.
  5. You can change the look, and number formats in the Planning Grid by modifying corresponding cells in the Dictionary.
  6. Microsoft Excel Undo function will not work after Load Plan, or Clear Plan.

<< Basic.ParamConstraints Main.Plans >>