Case Study 1: AOP (Production Side) of a mid-size FMCG company. Analysis and Set up.


In this Case Study, we will find out how to use rollingPlan for building the production side of an Annual Operational Plan (AOP) for a mid-size FMCG company. We will start with a brief analysis of the Portfolio (the Product Mix), and the Annual Sales Plan. Next, we will set Capacity Constraints, Initial On Hand Balances, Demanded Quantities, Target Stock Levels, and Inventory Position Targets. Finally, we will build the AOP in Year, Half Year, Quarterly, and Monthly Time Buckets by combining Automated, and Manual Planning.

1. Introducing Fine FMCG Industries, ltd.

Fine FMCG Industries, ltd. is a fictitious company producing Fast Moving Consumer Goods with Annual Revenue of approximately $70,000,000. It operates a manufacturing facility which consists of a Workshop with several Production Lines, a Raw and Packaging Materials Warehouse, and a Finished Goods Warehouse. The company uses three main channels to reach consumers both in the country of operation, and abroad namely Traditional Trade Channel, Modern Trade Channel, and Export Channel.

Traditional Trade Channel (TT) is essentially a Network of Non-Exclusive Distribution Partners each of which owns, or rents one, or several regional warehouses. Distributors deliver goods to small or mid-size local stores, gas stations, etc.

Modern Trade (MT) Channel consists of chains of supermarkets, or hypermarkets. Some of the chains use Centralized Logistical Model, i.e. they own, rent, or outsource to a Logistical Partner a Central Warehouse. Suppliers deliver to the Central Warehouse, and then goods are distributed among various stores. Other chains prefer De-Centralized Logistics, i.e. their hypermarket stores operate more or less independently. In this case, suppliers are obliged to deliver to each and every supermarket or hypermarket store.

Fine FMCG Industries, ltd. does not own trucks, and employs a Logistical Partner to carry Finished Goods to Distributors’ warehouses, Central Warehouses of two of the big store chains, as well as to a number of hypermarkets located in various regions.

In addition to selling to the local market, Fine FMCG Industries, ltd. manufactures products for export. Its International Partners represent and distribute brands of the company in several countries, and take care of meeting local regulations, Customs Clearance, and transportation of goods within each of the countries. Exported goods are picked Ex-works by International Logistical Partners.

2. Portfolio and Annual Sales Plan.

Download Case_Study-1(1).xlsx

2.1. Description.

Disclaimer: Portfolio structure and the Sales Plan discussed below are generated for the purpose of this Case Study. Any resemblance to real products and actual sales volumes is purely coincidental.

In Fine FMCG Industries, ltd., Sales and Marketing Department is responsible for creation of the Annual Sales Plan for the Year 2015. Sales Volumes are presented in boxes of various Finished Goods.

Here what it looks like:

Annual Sales Plan

By using certain criteria, Sales and Marketing split the 45 items of the Product Mix into 6 groups namely: Group A, Group B, Group C, Group D, Group E, and Group F.  In addition to this classification, various SKUs can be categorized as: Traditional Products, New Products, Limited Edition Products, Re-branded Products, Exported Products, Products manufactured for chains of supermarkets or hypermarkets, and Giraffe Products.

In Fine FMCG Industries, ltd. , product categories are interpreted the following way:

Traditional Products are considered to be the backbone of sales. They are well known to consumers, and competitors. These items have more or less predictable Market Share Dynamics and Demand. SKUs are manufactured in accordance with well-established BOMs, and Routings.  Standard Costs can be calculated relatively easy.

New Products come from innovation. They are supposed to surprise customers in a pleasant way, to propel sales, and to give hard time to both competitors, and Production.  For planning purposes, BOMs, Routings, and Standard costs are estimated by Production, and Financial Department. SKU IDs are coded in a special way.

Essentially, Limited Edition Items are New Products sold in limited quantities for limited periods of time at special prices. They may be used to add value to the brands, to test response of the market to certain ideas, or to boost sales in periods when Traditional Products do not perform very well.  Like in the previous case, for planning purposes, BOMs, Routings, and Standard Costs are estimated, and SKU IDs have specific coding.

Re-branded Products can be viewed as a result of restructuring of some of the Product Lines.  Latter is related to changes in packaging, and slight modifications in the BOMs. Quite often, consumers perceive Re-branded Products as New Products (at least in the beginning) which may lead to increased sales.

Market Specific Products (Exported Products) are versions of Traditional Products manufactured in accordance with certain market -specific regulations. They have special packaging, components (ingredients), and production recipes which make them unsalable in countries other than the one they are intended for. Fine FMCG Industries, ltd. requires Binding Forecasts for such products well in advance in order to procure the market-specific components (ingredients) on time, and to plan production accordingly.  Manufacturing of the Market Specific Products involves special equipment set ups, and proper cleaning, as even traces of some of the ingredients used in the Traditional Products may lead to contamination.

Products manufactured for chains of supermarkets or hypermarkets are Traditional Products, packed in units of 2, 4, 6, etc. They can be sold to customers outside the Modern Trade Channel after repackaging. However, latter is not desirable as it requires additional labor intensive operations.

And last, but not least, Giraffe products are produced for Planned Marketing Activities. These are Traditional Products in bigger packages. Like Limited Edition items they are sold in limited quantities for limited periods of time, and quite often, to a limited group of customers. Manufacturing follows traditional BOMs, and Routings, except for the packing materials and operations.

2.2. Brief Sales Plan Analysis.

A quick look at the volumes presented in the Annual Sales Plan reveals that Fine FMCG Industries, ltd. will rely on a rather conservative strategy to achieve its Sales Targets in 2015:

Sales Plan Analysis

It can be easily seen that Traditional Products are expected to make 85.7% of the Annual Sales Volume. Group A products will deliver more than 1/3 of the Sales, Group E and Group F combined will bring the second third, and Group B, C, and D are expected to fill out the rest.

New Products

There are three New Products to be introduced. Sales of the “New Product 1” are expected to start in H1 (Q1, Jan), “New Product 13” will hit the market also in H1 (Q2, May) while “New Product 2” will make its debut in H2 (Q4, Oct). In total, New Products will contribute 4.4% to the Annual Sales Volume.

Limited Edition Products

There are four Limited Edition Products in the plan, and they go in pairs. “Limited Edition 1” and “Limited Edition 3” will be sold from February till April (Q1, Q2, H1).”Limited Edition 5”, and “Limited Edition 8” will be channeled to Traditional Trade Customers from May through July(Q2,Q3,H1,H2), and then to Modern Trade from October till December (Q3,H2).  “Limited Edition 8” is expected to bring substantial increase in the sold quantities in May, and October. This group of items is planned to make 8.7% of the Annual Sales Volumes.

Girafee Products

“Giraffe Product 1” and “Giraffe Product 2” are extended versions of “Product 122”, and “Product 123”. They will be marketed in February (Q1, H1) while “Giraffe Product 7” (which is the extended version of “Product 53”) will strengthen the sales at the end of Q3 (Sept, H2).  To avoid cannibalization Traditional and Giraffe products will not be shipped at the same time. It is worth mentioning, however, that cannibalization cannot be completely avoided, as, most probably, some Traditional Products will remain in the pipeline.

3. Setting up the Basic Group Tables.


Basic.WorkCentersAndSkus is the most important table in the Basic group, as it holds information necessary to generate most of the templates in the tool. Here, we have to describe at what Production Rates, Efficiencies, and Minimum Lot Sizes various SKUs are produced on various Work Centers/Production Lines.

Fine FMCG Industries, ltd. owns, and operates a Workshop with 8 Production Lines: “Production Line CW”, “Production Line CW1”, “Production Line UCW”, “Production Line CB”, “Production Line CBS”, “Production Line UCB”, “Production Line UCB1”, and “Production Line SC”. Production Lines can be viewed as chains of interconnected Work Centers used to manufacture salable Finished Goods by transforming and combining Raw and Packaging Materials.

Here, what this table is supposed to look like:

Production Lines and SKUs
As we can see, each Production Line is specialized in manufacturing specific products, but some of them like “Product 1”, “Product 2”, “Product 53”, “Product 54 MT”, and “Product 55 Exp” can be made on more than one Production Line:

Items Produced On More Than One Production Line

Finished Goods come out of the Production Lines in boxes. Therefore, SKUs are defined as boxes, and Production Rates are set in boxes per hour.


In the Basic.WorkCentersConstraints table, we store Available Times for each of the Production Lines in various Time Buckets. As the Annual Sales Plan of Fine FMCG Industries, ltd. is presented in Year, Half Year, Quarterly, and Monthly Time Buckets, it is reasonable to build the Annual Production Plan accordingly. In order to do that, we have to set Time Constraints for each Time Bucket type.

At first, we may start with the most general Time Constraints assuming that in 2015 all Production Lines will operate in three shifts (24×7) except for the Company Holidays. Since Production Rates are set in boxes per hour, Available Times must be also defined in hours.

Here is a simple table to help us out:

Fine FMCG Industries Availalbe Times Y2015

The next step would be to build automatically the Basic.WorkCentersConstraints template by triggering “Build Template”, and “Add” functions from the Rolling Plan Ribbon Tab. Finally, we can transfer the Number of Available Hours from the table above to the Time Constraint column of Basic.WorkCentersConstraints table with a standard Microsoft Excel® function like SUMIF().

The end result should look like this:

Time Constraints (Available Times) in various Time Buckets


Basic.Demand is the table where we define Demanded Quantities and Target Stock Levels for each SKU ID in various Time Buckets. Our Annual Sales Plan is presented in boxes. Hence, Demanded Quantities, and Stock Constraints must be also defined in boxes.

Demanded Quantities in various Time Buckets can be transferred directly from the Annual Sales Plan, but in order to set the Target Stock Levels (Stock Constraints) properly, some additional work must be done. To calculate Closing Stocks for each of the time periods of the Annual Sales Plan, we will create an Annual Target Stock Levels Plan on a template similar to the one used for the Annual Sales Plan. We will apply a mixed approach when building the Annual Target Stock Levels Plan. For most of the Traditional and New Products, Target Stock Levels will be calculated based on Stock Constraint Coefficients. For some of the New Products, Limited Edition Products, Giraffe Products, Exported Products, and Products sold in Modern Trade, we will set the Closing Stocks manually following a set of simple rules:

1. New Products must be available in quantities sufficient to cover completely the first month of sales at the end of the month preceding it. For the months succeeding the month of introduction, Target Stock Levels will be calculated similar to Traditional Products.

2. Limited Edition and Giraffe Products must be available in quantities sufficient to cover completely the first month of sales at the end of the month preceding it. The last month of sales must be closed at zero stock levels.

3. Quantities of Exported Products must be manufactured, and shipped in the month of demand.

4. Products sold in Modern Trade must be available in quantities sufficient to cover entirely the month of sales in the month preceding it.

Values of Stock Constraint Coefficients can be set by using ABC analysis by volume on Traditional Products based on Projected Average Monthly Demand (AMD):

ABC Analysis by volume

After we apply the rules described above, and state the Monthly Stock Constraint Coefficients, we may come up with an Annual Target Stock Levels Plan like this:

At this point, we have all the necessary prerequisites (Demanded Quantities, Stock Constraint Coefficients, and Target Stock Levels) to fill out the Basic.Demand template. Since we intend to roll our Production Plan in Year, Half Year, Quarterly, and Monthly buckets, we have to create all the corresponding records in the table. This can be done by clicking on “Build Template”, and “Add” buttons located in the Constraints Section of the Rolling Plan Ribbon Tab. Once all the necessary records are added, we can transfer Demanded Quantities, and Target Stock Levels from the Annual Sales Plan, and the Annual Target Stock Levels Plan. Stock Constraint Coefficients will not be used in this case, so we can set their values to 1.

Here what the final result is supposed to look like:

Demanded Quantities and Target Stock Levels



There are two important details to pay attention to in the Basic.OpenStocks table:

1. Open Stocks (or Initial On Hand Balances) must be defined for the Key Time Buckets only, and
2. The Units of Measure of Open Stocks must match the Units of Measure of Demanded Quantities. Since Demanded Quantities in the Annual Sales Plan are set in boxes, Open Stocks must be defined in boxes also.

Annual Sales Plan is rolled in four types of Time Buckets: Year, Half Year, Quarter, and Month. Hence, the Key Time Buckets are: 2015, 2015-H1, 2015-Q1, and 2015-M1. The Initial On Hand Balances for all types of Key Time Buckets are the same. They are equal to the quantities found in the Finished Goods Warehouse during the Annual Inventory Count.

As a first step, we need to build the Basic.OpenStocks template by clicking on the “Build Template”, and/or “Add” button located in the Constraints Section of the Rolling Plan Ribbon Tab. Then, we have to transfer the Initial On Hand Balances from the Annual Stock Levels Plan to the Stocks Column. 

The result is supposed to look like this:

Initial On Hand Balance



In the Basic.Param table we define the so called Basic Top Line Parameter’s Values for each of the Key Time Buckets. Top Line Parameter is used for valuation of Closing Stocks over the Planning Horizon. When building the Annual Production Plan for Fine FMCG Industries, Ltd., we must keep an eye on the Inventory Position at the end of each of the Planning Periods. If we consider Inventory Position as the Top Line Parameter, and we calculate it on a basis of Standard Costs, then Basic Top Line Parameter’s Values for each of the Key Time Buckets will be equal to the Standard Cost Values of our SKUs.

Like in the previous case, we may start with building the Basic.Param template for the Key Time Buckets (2015, 2015-H1, 2015-Q1, and 2015-M1) only. Then, we have to enter Standard Costs for each SKU in each Key Time Bucket. To keep things simple, we assume that Standard Cost Values do not change through the year (which quite often is no the case). 

Here what the final table should look like:

Standard Cost Values per SKU per Key Time Bucket



Top Line Parameter’s Constraints serve as upper limits for the Top Line Parameter’s Values over the Planning Horizon. Since we assumed that for Fine FMCG Industries, Ltd. Inventory Position would play the part of the Top Line Parameter, then Inventory Position Targets are supposed to act as Top Line Parameter’s Constraints. Top Line Parameter’s Values are calculated by summing up Top Line Parameter’s Components for each Time Bucket over the Planning Horizon. Hence, Top Line Parameter’s Constraints must be defined for all Time Buckets we are willing to plan.

Like in all the cases described so far, before we set Top Line Parameter’s Constraints, we have to build the Basic.ParamConstraints template. Latter can be accomplished by triggering “Build Template”, and “Add” functionality from the Rolling Plan Ribbon Tab.

Here what the final table is supposed to look like:

Top Line Parameter's Constraints (Inventory Position Targets) in various Time Buckets

Note that in this case, we enter same values for the Top Line Parameter’s Constraints which refer to the same time period in different Time Buckets. For example, at the end of the Year 2015, Closing Stocks must be the same regardless of the type of the Time Bucket. Hence,  Inventory Position Targets must be also the same for 2015-M12, 2015-Q4, 2015-H2, and 2015.

At this point, we have all the tables in the Basic group set, and we can proceed with building the Annual Production Plan.

 Case Study 1.Build the Annual Production Plan >>