Calculation model v2 (pro version) - Production plan block

21.11.2018

Let’s look at the production planning block (highlighted in red). This block is used for calculation of the required volume of production, taking into account demand forecast, stock balances, rejected percentage, management decisions and other factors. As a result, model makes a shipping plan for each market and product “Production plan”. In general, calculation chain is as follows:

  1. Demand forecasting block forms a plan for orders for the next period in the table “Orders next quarter”.
  2. Taking into account backlog of orders in the previous period “Backlog of orders” (which we must complete in the next period), model calculates total number of sales in the table “Sales next quarter”.
  3. Then we take quantity of products that are ready and in stock “Stocks previous quarter” from sales value in the next period “Sales next quarter” and add planned quantity of products that we want to keep in stock “Stocks next quarter” (J26:L29).
  4. As a result, we get minimum required production plan in the table “Production plan next quarter” = “Orders next quarter” + “Backlog of orders” - “Stocks previous quarter” + “Stocks next quarter”.

 

Containers are used to ship products to agents and distributors, where each product occupies a certain free space. Current number of containers to be sent to each market is calculated in cells M22:M24. Almost always part of the container remains empty, but at the same time the company pays for transportation of container at a fixed rate, regardless of its load.

In order to minimize cost of transporting containers, model automatically adds necessary amount of products to the production plan for loading containers by 100% and displays the result of calculation in the table “Production plan”. In this case, required number of containers with maximum load is calculated in cells E2:E4.

Table “Production plan” has a switch in cell “D1” with a value of 0, 1, and 2:

  1. 0 - production plan is calculated taking into account the maximum container load. It is used in the baseline decision preparation scenario.
  2. 1 - production plan is calculated without taking into account the maximum container load. It is used if it is required to deliver exactly specified amount of products to the market or when fine-tuning the volume of production when preparing a decision.
  3. 2 - production plan is equal to actual from the management report in previous period, calculation of the production plan in model is disabled. It is applied at plan-fact analysis of the decision.

 

In the next step, model calculates the required number of operating hours for production details “Production details”, assembly details “Assembly details” and required amount of raw materials “Raw materials”.

Calculation number of hours for production details “Production details” takes into account the number of available machines, stock components, number of shifts, efficiency of machines, downtime and percentage of rejected products. Total required number of hours is displayed in cell H2, load of machines in cell I2. Loading machines at 100% means maximum output at 5-day work week. Value greater than 100% means that machines and machinists are employed for working on weekends.

Similarly, calculation number of hours for assembling details “Assembly details” takes place, taking into account number of available workers, percentage of rejected products, number of strikes and hours of sickness. Loading workers at 100% means maximum production at 5-day work week. A value greater than 100% means that workers are involved in working on weekends.

When calculating required raw materials “Raw materials”, model takes into account production plan of products, high-quality raw materials and stock of components. Further, according to previously made orders for supply of raw materials and current balance in stock, the required volume of materials for production in next period is calculated.

A few more important points:

  1. Current balance of free space of the factory is calculated in cell E13. If it is negative and highlighted in red, then it is necessary to expand area of the factory, otherwise the production plan will not be fulfilled.
  2. Conditional reduced maintenance hours for 1 machine is calculated in cell I12. Calculation based on its load and number of shifts. For example, if you specify in decision 20 hours of maintenance with machine load of 100% and machine load of 137%, then reduced value of maintenance in 1 case is 7 and in 2 case is 5, i.e. machine with less load receives more maintenance based on the time worked and its efficiency in the future will be higher. When value of conditional reduced maintenance is less than 10 machine efficiency will decrease, and cell will be highlighted in red.
  3. Current salary for 1 worker is calculated in cell I26. If value is less than salary for 1 machinist (in cell I27), then value will be highlighted in red, and you will pay extra to workers, because their salary cannot be lower than machinist’s salary.

 

You can purchase calculation model v2 (pro version) in our store.