This paper is committed to determining the optimal plan of product of two shelves types, S and LX. It highlights which combination of shelves to allocate optimally the capacity available to help the organization maximize its total profits. Besides, it undertakes to carry out sensitivity analysis to help in understanding how total profits changes as selected inputs are changed, that is, the selling price and the material cost.
Executive summary
This paper is concerned with the determination of the optimal decision variables, that is, the number of production units for every shelve model that helps the company maximize its profits and work in line with the resource constraints like hours available for stamping and forming, and the capacity of the assembly. Excel Solver has been used to help optimize the maximum profit using the programming (LP) model. The model helps optimize allocation of resources so that the company’s total profit is maximized. Total profit is computed as the sum of every product’s profit less fixed costs. Optimal profit is realized when its production have 1,900 model S units and 650 units for Model LX as this combination gives a monthly profit of $268,250. Direct materials, overhead cost and direct material cost per unit constitute the company’s variable costs while the assembly process costs, forming costs, and stamping costs are its fixed costs. The aim is to draw an optimal product mix decision for optimal profit maximization under the available resource constraints.
Analysis
As per the information on limited resources, cost calculation, price and process analysis, it is evident that model S contribution’s margin is higher than that of model LX (Table 1). Accordingly, for production mix optimization, the company should allocate all the available resources for production of model S to the maximum capacity of production. The resources left should be allocated for model LX production. Thus, production plan should have 1,900 model S units and 650 units for Model LX for total profit maximization. This combination gives a monthly total profit of $268,250.
Table 1
Contribution margin( $/unit)
Optimal production(units)
Total profit
Percentage contribution margin change from the base
% Profit Change from base
model S
model Lx
model S
model Lx
Base case
Increase model S material cost by $100
Model S -38%
-71%
Increase model Lx selling price by $300
Model LX +122%
170%
Table 2
Resource allocation
Resources
Capacity
Spent on model S
Spent on model LX
Total resources spent
Excess capacity
Stamping(hrs)
Forming(hrs)
-
Assembly model S (sets)
1900 sets
-
1900 sets
-
Assembly model LX (sets)
650 sets
650 sets
750 sets
Due to reason that model S contribution’s margin ($260/set) is higher than that of Model LX ($245/set), maximizing model S production is very appropriate for the company to maximize its total profit. Model S does not have excess capacity. Its process restricts the company from increasing its profit and production. Essentially, its forming step is a major block as it precedes assembly step. Increasing the constraint limits from 800 to 801 hours results in an increase in the company’s total profits from $268,250 to $268,740. This is a $490 profit increment. This results from increasing production of model LX to 652 from 650 times the contribution margin (2 sets X $245/set = $490). As shown in table 1, increasing the cost of material for Model S by $100/set leaves the production constant, i.e. Model LX =650 units and Model S =1900 units. Nevertheless, total monthly profit decreases from $268,250 to $78,250. An increase in material cost of model S reduces its contribution margin to $160/unit from $260/unit. If selling price of model LX is increased from $2,100 to $2,400, monthly production plan changes to be: model S, 400 units and Model LX, 1,400 units resulting in total monthly profit increase from $268,250 to $482,000. This is because Model LX price of $300/set increases its contribution margin from $245/unit to $545/unit. Therefore, it is applicable to maximize production of Model LX due to its higher contribution margin.
