Scribd
Upload
17 views10 pages

Optimize Production for Maximum Profit

This document describes using Excel Solver to optimize production levels of mice, keyboards, and USB hubs in order to maximize profit for an electronics company. The problem provides production constraints like maximum labor hours and production time. Excel Solver allows adding these constraints and finds the optimal production mix that fulfills all constraints while maximizing total profit. The solution shows keyboards and USB hubs should be produced at full demand, while mice production is slightly reduced, in order to maximize the utilization of resources and profit.

Uploaded by

SumairHassan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
17 views10 pages

Optimize Production for Maximum Profit

This document describes using Excel Solver to optimize production levels of mice, keyboards, and USB hubs in order to maximize profit for an electronics company. The problem provides production constraints like maximum labor hours and production time. Excel Solver allows adding these constraints and finds the optimal production mix that fulfills all constraints while maximizing total profit. The solution shows keyboards and USB hubs should be produced at full demand, while mice production is slightly reduced, in order to maximize the utilization of resources and profit.

Uploaded by

SumairHassan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Submitted to:

Sir Mushtaq Ahmed

Submitted by:

Sabir Rashid 2015-ME-427


[Link] 2015-ME-428
Bilal Rasheed 2015-ME-429
Qamar islam 2015-ME-430
[Link] HASSAN 2015-ME-431

Mechanical Engineering Department, UET LAHORE


(Sub-campus, RCET Gujranwala)
Problem statement:
An electronic company sells its products of mouse, USB hubs, and keyboards. Details of these
products with constraints is given in following table:

Objective: Company wants to Maximize Profit by Optimizing Production mix of Mice,


Keyboards, and USB Hubs under given constraints.

Solution:
1) Excel Solver is used to fulfil the constraints while maximizing profits for given resources. First,
we calculated the total production per month, monthly profit, labor hours and production hours of
individual product and their total values are given below. But we can see, if we fulfil all monthly
demand as required, we exceed our constraints. So we need optimization.
2) For optimization (maximize profits while maintaining constraints) we used Excel Solver. If
Solver is not visible, then go to file>options > add ins and select the Solver command to enable.
When solver command is enabled then open Solver from Data Tab which appear as
3) Set the Objective which is to maximize the profit by selecting H15 Cell.
4) Add the value which have to be maximize.
5) Now we added the variable cell which have to be vary.
6) For the addition of constraint, we clicked on add. First we added the constraint of labor hour
by providing cell reference, putting less than or equal to symbol and constraint.
7) Add the other constraints which are production hours and production unit per month same as
above and click on ok.
8) Click on solve and then clicking on keep the solver solution we get the production unit of
every product which is suitable for maximum Profit.
Summary:
Excel Solver is great in situations like one demonstrated above where we had to maximize the
profit while maintaining maximum use of resources. Instead of going for hit and trial method, we
can put in our constraints like labor hours, time for production and maximum demand. These
parameters are then optimized by Solver in order to get maximum possible profit value. In our
case, profit can be maximized if Labor hours are fully utilized (10000 hours) and Production time
is utilized near its maximum value (1655 out of 1700) as demonstrated above.
Most importantly, Solver also tells us out of all the products demands, which product are to be
produced within optimum demand along with given constraints, so we can maximize our Profit.
We can see that, if Keyboards and USB Hubs are produced to their full demand (20,000 and 9000
respectively) while Mice are produced at optimum range (4889 out of 12,000), we can maximum
our profits.

Common questions

Powered by AI

Excel Solver helps optimize production by calculating and adjusting the production mix to maximize profits under given constraints such as labor hours and production hours. By using Solver, the company can determine the optimal number of each product to produce, fulfilling as much demand as possible while not exceeding resource limits .

Sole reliance on Solver for production decisions might overlook qualitative factors such as market changes, consumer preferences, and risk management which require human judgment. Additionally, errors in input data or misjudgment of constraints can lead to suboptimal or unrealistic solutions, thus necessitating a blend of automated and manual strategies .

The primary constraints considered are labor hours and production hours. These constraints influence production decisions by limiting the total amount of products that can be made each month. For example, the optimization requires that labor hours be fully utilized (10000 hours) while production time is near its maximum (1655 out of 1700), ensuring that resources are used efficiently to maximize profits .

To set up Excel Solver, first enable it through file>options>add-ins. Then open Solver from the Data Tab. Set the objective to maximize profit by choosing the respective cell. Add values to be maximized. Input variable cells to vary. Add constraints for labor hours, production hours, and monthly units. Finally, solve to get the optimal solution .

Producing keyboards and USB hubs to their full demand ensures that maximum revenue is obtained from these products, likely because they contribute significantly to profit margins or have higher demand. Producing mice at the optimum range (4889 out of 12,000) ensures that production resources are allocated efficiently across all products to achieve the highest possible profit without exceeding constraints .

The Solver add-in assists by enabling the company to input constraints and objectives, such as maximizing profit. It processes these parameters to provide solutions that detail the optimal production levels for each product. This avoids a hit-and-trial approach, offering a structured method to determine the maximum possible profit while respecting constraints .

Excel Solver transforms traditional production planning by eliminating the trial-and-error approach, providing a data-driven strategy for decision-making. By incorporating constraints directly into problem-solving and automating the optimization of resource allocation, it enhances efficiency and accuracy in achieving profitability goals, marking a significant advancement in engineering management .

Labor hour utilization is critical as it directly impacts how many products can be produced. By fully utilizing 10000 hours of labor, the company ensures that it is using its human resource capacity to its fullest potential, directly contributing to maximizing the production output and, consequently, the profit. This optimal utilization is key to aligning production capabilities with demand .

The scenario illustrates that resource constraints such as labor and production hours necessitate a careful balance in production strategy. Maximizing profit requires managing these constraints to determine an optimal product mix that satisfies demand partially while ensuring resources are not exceeded. This reflects the complexity of operational management in aligning capacity with market demand .

The optimization strategy results in an allocation where keyboards and USB hubs are produced at full demand while mice are produced at an optimal level that doesn't exceed constraints. This ensures that the most profitable production mix is achieved without overloading resources. Hence, each product is prioritized based on its contribution to profitability under resource limitations .

You might also like