Or say due to change in market dynamics product A can give a profit of 40K instead of 30 K. Now there can be situations like due to some operational issue we lost 100 hrs in unit 1 or there is a way we can borrow 100 hours for unit 2 from another factory, what is the impact on our profit. The solution says that we should produce 2000 units of product A and 1000 units of product B with a maximized profit of 110000000. When you click on solve, you will get an optimal solution from E5 to E8 is Choose Solving method as Simplex LP. Go to Data -> Solver -> Object (choose column E2 where we calculate total profit) -> For “To”, let the default max be selected as we want to maximize profit -> For Changing variable cells choose B3 and C3 where we have units manufactured for A and B -> Add constraints by selecting Hours available cell reference i.e. Once we have an excel setup, the next steps are easy. For example, E5 has Time spent by product A in unit 1 * units manufactured Product A + Time spent by product B in unit 1 * units manufactured Product B or B5 * B3+ C5 *C3.
number of units for product A * per unit profit product A + number of units for product B * per unit profit product B or = SUMPRODUCT(B2:C2, B3:C3)Ĭolumn E5 to E8 is also dynamically calculated. We have added data for Product A and B, Profit data for per unit, units manufactures is just a placeholder for now, and then we have given the number of hours spent in each unit by both the products.Ĭolumn E2 has total profit, i.e. Let’s try to understand the data here before moving ahead.
This is available off the shelf in Microsoft Excel, so we will set up the data in an excel sheet. To solve this problem, we are going to use the Simplex Linear Programming method. As given constraints, we know that unit 1 can operate 4000 hrs, unit 2 can operate 6000 hrs, unit 3 can operate 5000 hrs and unit 4 can operate 4500 hrs in a month. For product-B, it generates 50000 in profit and its manufacturing needs 2 hr in unit 1, 2 hr in unit 2, and 3 hrs in unit 4. Product A generates 30000 in profit and manufacturing needs 1 hr in unit 1, 2 hr in unit 2, and 2 hr in unit 3. Say in a factory, we are building 2 products, Product A and B. To understand the concept let’s take the problem of optimizing resource utilization and maximizing profit, where we have all the details on how much resources are being used by the products. There can be cases where we have all the needed data is available, and we need to make decisions such that a given objective is achieved in the best possible manner while satisfying conditions imposed.
0 kommentar(er)
