MBA6204 Quantitative Support of Decision Making Report 2 Sample
Student Guidelines
This is an individual assessment and you must choose one of the managerial problem as listed below that relate to your organization or another organization of your choice. You need to research all the data that will support management in such a way that they will have sufficient data and information that they are able to make the decisions appropriately.
You also need to develop a quantitative model (linear programming) using the data, analyse and interpret the model using excel solver and report your findings.
You need to identify an objective function clearly stating the purpose of the quantitative model. You should be able to define the variables under consideration for the quantitative model development.
Further, you should be to collect relevant information from stakeholders for developing the constraints. The report should outline the stakeholder’s analysis for data collection and developing constraints. The developed model should be analysed using the Excel solver function to generate an optimal solution. The report should outline the step-by-step procedure of the solver function. The final output should be presented to management for decision analysis.
You are required to set up the MS Excel dashboard to assist any user to make decisions. You are to provide quantitative model for managerial decision making with optimal outcomes for the organization by using Excel solver tools and validate the importance of specific analysis and interpretation for the management decisions.
The managerial decisions you make in relation to this assessment must be made with regards to any one of the following:
› 1. Make or buy product or equipment
› 2. Financial decision making: Investment portfolio problem
› 3. Transportation/Assignment problem
› 4. Blending Problem/Marketing Mix problem
› 5. Production/Inventory Planning problem
› 6. Multiperiod Cashflow Problems
› 7. Process/Job Sequencing Problem
› 8. Other specific managerial problems...
You will be assessed as follows:
a. Structure of the written report: Background information is relevant, issues are logically ordered, recommendations clearly relate to the managerial problem.
b. Identify critical managerial problem: Formulate linear programming model using variables, objecttives and constraints.
c. Analyse and test the issues: Able to mathematically analyse and test using Excel solver.
d. Implement the solution: Justify by providing alternatives that the solution generated is the optimal.
e. Write clearly and concisely: Arguments are explicit and succinct, appropriate headings are used, grammar and spelling are accurate.
Solution
Introduction
The Make or Buy decision is an essential strategic choice faced by companies in manufacturing and production. It requires determining the most cost-efficient option between in-house manufacturing or outsourced production from external suppliers. University Assignment Help, The decision-making process involves comparing the full internal manufacturing costs, including direct and indirect costs across material, labor and overhead, against the price quoted by suppliers. Additionally, factors like production capacity constraints, quality control advantages of in-house production, risks of dependency on suppliers, protection of proprietary knowledge etc. need to be considered. Mathematical modeling tools like Linear Programming can assist through quantitative analysis of the make-or-buy trade-offs.
This report illustrates the application of Linear Programming methodology to evaluate the Make or Buy decision for a specific company - PB Enterprises. PB Enterprises manufactures two calculator models - Basic and Scientific. The LP model will be formulated to find the optimal production quantities of each model to be made in-house versus procured externally in order to minimize total costs. The LP constraints will reflect demand, machine hour capacity limits and non-negativity restrictions. The quantitative LP analysis will provide data-driven insights to identify the ideal Make or Buy quantities that result in the lowest overall costs while meeting production requirements. The example demonstrates how the LP technique can be leveraged to facilitate data-based decision making for this crucial supply chain choice.
Background on PB Enterprises
PB Enterprises is a firm that manufactures and sells calculators. It produces two models of calculators:
1. Basic Calculator
2. Scientific Calculator
The company faces a monthly demand of 500 units for Basic Calculator and 700 units for Scientific Calculator. Currently, the company manufactures both these products in-house but has limited production capacity. The in-house manufacturing cost for each unit is $12 for Basic Calculator and $15 for Scientific Calculator.
PB Enterprises has installed machinery capable of producing both calculator models. However, the availability of total machine hours for production is constrained at 3000 hours per month. The Basic Calculator model requires 2 machine hours per unit while the Scientific Calculator model requires 4 machine hours per unit. Given these machine hour requirements, PB Enterprises does not have enough internal capacity to meet the total demand through in-house production.
Therefore, the company must supplement in-house production with external procurement. An external supplier has quoted costs of $15 per unit for Basic Calculator and $19 per unit for Scientific Calculator. PB Enterprises has to evaluate the optimal quantities to produce in-house vs buy from the supplier to meet demand at minimum cost.
Make or Buy Decisions
The Make or Buy decision determines whether a firm should internally manufacture a component or product in-house or outsource the production to a third-party supplier. According to Arora and Kumar (2022), the make-or-buy analysis is a common decision problem faced by firms and involves strategic, tactical and operational considerations.
As highlighted by Pentikäinen (2022), the key factors to consider in Make or Buy analysis are:
• Cost Comparison: In-house production costs vs external purchase costs.
• Production Capacity: Available internal capacity vs additional capacity needed.
• Quality Control: Internal quality systems vs supplier quality assurance.
• Core Competencies: Aligning make-or-buy with strategic capabilities.
• Control over Production and Timing.
• Access to Specialized Knowledge and Technology.
Quantitative techniques like Linear Programming can be leveraged to model the make-or-buy decision and determine optimal solutions (Camm. et. al., 2022). The next section formulates the PB Enterprises Make or Buy problem as a Linear Programming model.
Linear Programming Formulation
Linear Programming (LP) is an optimization technique used to determine the optimum solution based on certain linear objective function and constraints (Alotaibi &
Nadeem, 2021). According to Storm (2023), LP enables mathematical modeling of problems that involves maximizing or minimizing linear objectives by optimally allocating limited resources.
The PB Enterprises Make or Buy problem can be formulated as a LP model in the following manner:
Decision Variables:
X1 = Quantity of Basic Calculator to be produced in-house
X2 = Quantity of Basic Calculator to be procured from supplier
Y1 = Quantity of Scientific Calculator to be produced in-house
Y2 = Quantity of Scientific Calculator to be procured from supplier
Parameters:
P = 12 (In-house unit cost for Basic Calculator)
Q = 15 (In-house unit cost for Scientific Calculator)
R = 15 (Supplier unit cost for Basic Calculator)
S = 19 (Supplier unit cost for Scientific Calculator)
Objective Function:
Minimize Z = PX1 + QY1 + RX2 + SY2
Subject to constraints:
Demand Constraint:
Monthly Demand of Product “Basic Calculator” is 500 Units and for Scientific Calculator is 700 units.
X1 + X2 = 500 Units
Y1 + Y2 = 700 Units,
This equation ensures that the total quantity produced and bought is at least equal to the demand.
Production Capacity Constraint:
Production Capacity Constraint is the availability of the Machine Hours.
X1*2 + Y1*4 <= 3000 hours
This equation ensures that the quantity produced in-house does not exceed the production capacity.
Non-negativity Constraints:
X1, X2, Y1, Y2 >= 0
Using this LP formulation, the Excel Solver tool can determine the quantity of each calculator model to produce in-house and procure from supplier to minimize the total cost while satisfying the demand and machine hour constraints (Winston, et. al. 2003).
Analysis Using Excel Solver
The LP model formulated in the previous section is implemented in Excel Solver to find the optimal Make or Buy quantities for each calculator model.
The figure below shows the Excel model with the input data, decision variables, objective function and constraints:
.png)
The Solver uses the GRG Nonlinear algorithm to determine the optimal values of decision variables X1, X2, Y1 and Y2.
The solution obtained from Solver is as follows:
• X1 = 500 units
• X2 = 0 units
• Y1 = 500 units
• Y2 = 200 units
This implies that PB Enterprises should:
o Manufacture 500 units of Basic Calculator in-house
o Manufacture 500 units of Scientific Calculator in-house
o Procure 200 units of Scientific Calculator from external supplier.
The minimum total cost obtained is $17,300.
This optimal result can be interpreted as follows:
• Since in-house cost is lower for Basic Calculator, Solver maximizes in-house production to 500 units for this model.
• For Scientific Calculator, in-house cost is higher than supplier cost. Therefore, Solver determines lowest cost by producing 500 units in-house (upto machine hour limit) and procuring 200 units from supplier.
• The combined optimal quantities ensure that demand for both models is met at minimal cost while not exceeding machine hour availability.
The optimal solution can be verified by checking if all constraints are satisfied:
• X1 (500) + X2 (0) = 500 meets Basic Calculator demand
• Y1 (500) + Y2 (200) = 700 meets Scientific Calculator demand
• 2*X1 (1000 hrs) + 4*Y1 (2000 hrs) = 3000 hrs meets machine hour limit
• Non-negativity constraints are met as all variables are >= 0
Thus, the Solver has successfully determined the ideal Make or Buy quantities by optimizing the total cost function while adhering to all problem constraints. This LP model provides PB Enterprises with data-driven, optimal decision-making for the Make or Buy analysis.
Recommendations
Based on the in-depth Linear Programming analysis, the following recommendations can be made for PB Enterprises:
• PB Enterprises should manufacture the entire quantity of 500 units of the Basic Calculator model in-house. Since the in-house production cost per unit is lower than external supplier price, it is most economical to produce the full Basic Calculator demand internally.
• For Scientific Calculator, 500 units should be produced in-house to maximize the use of available machine hours. The remaining demand of 200 units should be procured from the external supplier as the supplier price is lower than internal manufacturing cost for this model.
• The make vs buy quantities should be re-evaluated if the demand volume or the quoted supplier prices undergo major changes in the future. The LP model provides a dynamic framework that can be adjusted to determine revised optimal quantities.
• PB Enterprises should negotiate with the supplier to reduce the purchase costs further through volume discounts or bulk order pricing. Lower supplier prices can help increase external procurement quantities thereby reducing in-house production needs.
In addition to the LP analysis, PB Enterprises should develop a broader strategic Make or Buy framework incorporating factors like quality control, core competencies, proprietary knowledge management and supply chain risks. This holistic approach can guide the overall Make or Buy direction for the company.
Conclusion
The Make or Buy analysis is a crucial supply chain decision essential for cost optimization and efficient operations management. This report illustrated the application of Linear Programming to model the Make or Buy decision problem faced by PB Enterprises. The LP formulation and Excel Solver enabled the determination of optimal production quantities to be manufactured in-house and procured externally for each calculator model to minimize total costs while meeting demand and capacity constraints. The model provides data-backed, quantitative insights for informed decision-making. The recommendations suggest producing 500 units of both models in-house while procuring 200 units of Scientific Calculator based on the total cost minimization objective. Overall, the LP approach provides a structured, analytical methodology to optimize Make or Buy choices incorporating all key factors and constraints.
References:
Alotaibi, A. and Nadeem, F., 2021. A Review of Applications of Linear Programming to Optimize Agricultural Solutions. International Journal of Information Engineering & Electronic Business, 13(2).
Arora, M. and Kumar, A., 2022. An Empirical Study on Make-or-buy Decision Making. International Journal of Education and Management Engineering, 12(1), p.19.
Camm, J.D., Cochran, J.J., Fry, M.J., Ohlmann, J.W. and Anderson, D.R., 2022. An introduction to management science: quantitative approaches to decision making. Cengage Learning.
Pentikäinen, A., 2022. Make-or-Buy analysis for suction roll components.
Storm, J., 2023. Quantitative Analysis for Management. In Quantitative Analysis for Management, Global Edition. Pearson Education.
Winston, W.L., Venkataramanan, M. and Goldberg, J.B., 2003. Introduction to mathematical programming (Vol. 1). Duxbury, Pacific Grove, CA: Thomson/Brooks/Cole.
Would you like to schedule a callback?
Send us a message and we will get back to you
Highlights
Earn While You Learn With Us
Confidentiality Agreement
Money Back Guarantee
Live Expert Sessions
550+ Ph.D Experts
21 Step Quality Check
100% Quality
24*7 Live Help
On Time Delivery
Plagiarism-Free
81 Isla Avenue Glenroy, Mel, VIC, 3046 AU
