Rought Cut Capacity RCCP with MS Excel Explained (Hindi/Urdu)

Let's find what is rough cut capacity and how optimal product mix can increase profit margin and maximize use of available time for production.

We calculate production plant's "available" production capacity in "total minutes". This is done irrespective of what product we are making at initial stage and later, we decide to produce product mix that gives us maximum profits for a given production capacity.

This video attempts to discuss the case using an excel sheet. We initially setup a simple sheet that takes important values as input and then use ms solver to find the optimal solution.

We start by calculating total minutes available to us for single machine using following formula:

Total minutes available for single machine = 60 minutes * Hours worked * Efficiency assumed

The total minutes for all the machines is product of total machines times minutes available for single machine, thus:

Total Minutes available = minutes for single machine * total machines

The calculation does not stop here, the profit margins must take into account as well. The total profit obtained is product of respective profit margin for a product and quantity of it produced thus:

Total Profit = Item_1 Quaintity x Item_1 Contribution + Item_1 Quaintity x Item_1 Contribution + ... + Item_n Quaintity x Item_n Contribution

We used SUMPRODUCT() formula for this purpose in the video.

Finally, we have used SOLVER to optimize our sheet.

Target Cell: Profit Margin that need to be maximized
Variable Cells: Quantity to be produced for all three products
Constrains: Non negativity and integer type for all quantities, minimum quantity for certain type of products and total minutes used less then total minutes available.

The result is an optimized solution to our problem.

#supplychain
#productionplanning
#rccp
#solver
#excel
#productmix
#roughtcut
#capacity
#productionplanning
#solver #optimization #rccprogram


Thank you for watching, please give your feedback in comments.

Related:
SUMPRODUCT explained from Leila Gharani
   • How to Use SUMPRODUCT in Excel