Production Optimization for Profit: A Business Decision Analysis

This case study provides an analysis of ABCD Ltd's production of basketballs and footballs, aiming to maximize net profit after tax. A linear programming model is formulated and solved using Excel Solver, considering constraints such as machine hours and minimum/maximum production levels. The analysis determines that manufacturing 56,000 basketballs and 40,000 footballs optimizes profit, yielding a post-tax profit of $591,840. The report recommends increasing basketball production by 4,000 units if machine hour constraints are eased, potentially increasing post-tax profit by an additional $25,920. The study includes sensitivity, answer, and limit reports, along with slack and surplus analysis to support these recommendations.

Business Decision Analysis
Case Study
Student Name
[Pick the date]

PURPOSE OF THE REPORT
The main objective of this report is to provide advice to the company in relation to the optimum
number of basketballs and footballs that need to be produced so as to maximise the net profit
after tax for the company. In this regards, the requisite model has been framed for the given
problem which had been described in the following section. The model has been solved using
MS-Excel in order to provide optimum production units for profit maximisation.
DESCRIPTION OF THE PROBLEM
The given problem relates to a sports manufacturer named ABCD Ltd and has multiple
manufacturing plants. A particular plant of the company is dedicated for production of
basketballs and footballs. The company has some flexibility with regards to the production of
footballs and basketballs and hence can alter the production level of these products. These
various costs related to the production of these products on a per unit basis has been provided.
Additionally, the unit selling price for each of the items is known. Further, there are certain
constraints at the manufacturing plant with regards to machine hours available and also the
minimum and maximum production of each product. Considering these constraints, it is
imperative to determine the ideal product mix for the two products which would lead to
maximum profit generation for the company.
METHODOLOGY
In order to resolve the problem at hand, the given problem would be formulated as a linear
programming problem and then solved using Excel function SOLVER considering the key inputs
and resource & production constraints.
Formulation of Linear Programming Model
The given information and data are summarized below.
Minimum Maximum
Total available machine hours 39,000 40,000
Number of basketballs that can be
manufactured
30,000 60,000
1

Number of footballs that can be
manufactured
20,000 40,000
Basketballs Footballs
Time to manufacture balls (hours) 0.5 0.3
Cost of labour (1 machine hour ) ($) 6 6
Cost of material ($) 2 1.25
Basketballs Footballs
Selling price per piece ($) 14 11
Corporate tax rate 28%
The aim is to maximize the net profit of ABCD Ltd by determining the idea number of footballs
and basketballs that needs to be manufactured.
Profit per unit
For basketball = Selling price – (Labour cost + material cost) ¿ 14−{(0.5∗6)+ 2}=$ 9
For football = Selling price – (Labour cost + material cost) ¿ 11−{(0.3∗6)+1.25 }=$ 7.95
Let the decision variables are x and y and profit z.
x=Number of basketballs
y=Number of footballs
Objective function
Max profit ( z )=9 x +7.95 y … … … … … ….. ( before tax )
2

¿
Max profit ( z )=0.72( 9 x +7.95 y )… … … … … … ..( after tax)
Subject to constraints
0.5 x+ 0.3 y ≥ 39,00 0
0.5 x+0.3 y ≤ 40,00 0
x ≥ 30,00 0
x ≤ 60,00 0
y ≥20,00 0
y ≤ 40,000
Non-negativity constraints; x , y ≥ 0 since number of basketballs and footballs cannot be negative.
Also, x and y both would be integers.
FINDINGS AND RESULTS
The above shown linear programming model has been solved by using excel add-ins ‘solver’ and
the output is shown below.
Linear programming model
3

With initial guess
Final LPP model
The ideal number of footballs and basketballs that need to be manufactured is as shown below.
4

x=Number of basketballs=56,000
y=Number of footballs=40,000
Profit before tax ¿ $ 82,2000
Profit after tax ¿ 0.72∗$ 82,2000=$ 591,840
Tax amount = $ 82 2, 000−$ 591,840=$ 230,160
Therefore, the optimum number of basketballs manufactured is 56,000 and footballs are 40,000.
Sensitivity report
Answer report
5

Limit report
In case of non-binding constraints, the amount of slack or surplus is shown below.
6

Surplus Slack
Labour minimum constraints 1000 -
Basketball minimum 26,000 -
Basketball maximum - 4,000
Football minimum 20,000 -
In order to enhance the profitability, ABCD Ltd can enhance the total machine hours and can
manufacture the remaining 4000 basketballs. The additional post tax profit earned would be =
0.72 * (4000*9) =$25,920.
RECOMMENDATIONS OR CONCLUSIONS
Based on the above discussion, it is apparent that in order to maximise the post tax profit, the
company needs to manufacture 56,000 basketballs and 40,000 footballs. At this level of
production, the post tax profit that the company would derive would amount to $591,840.
Further, based on slack and surplus analysis, it is apparent that the company can potentially
increase the production of basketball by 4,000 units since the maximum limit is 60,000 units.
However, in order to implement the machine hours constraint would need to be eased suitably. If
the same is achieved, then incremental post tax profit to the tune of $25,920 can be earned.
7