Ribble Kitchen Company’s Product Mix

Introduction

In an optimization problem, the nature of mathematical relationship between the objective and constraints as well as the interconnection between the decision variables determines the optimum level of any operation (Solver.com, 2009). If the objective and all of the constraints involved are convex in nature – that is adjustable according to the needs – then it becomes possible to find a feasible solution. “To optimize a manufacturing system means that the effort to find best solutions focuses on finding the most effective use of resources over time.” (Yu-Lee, n.d.)

Main Body

In the case of the Ribble Kitchen Company the product mix that will generate optimum profits using the resources subject to the constraints has been worked out using excel solver function and the results are shown as appendix to this report. This report analyzes the results shown by the solver.

It can be observed from the results that the final values of the objective function (profit as shown by the target cell) and the decision variables (products as shown in the adjustable cells) are clearly indicated. The report also shows the status of the usage and slack in respect of each of the constraint. The final product mix and the resulting profit are shown in the following table.

Product Units Profit/unit Profit
Chairs 130 55 7150.00
Tables 0 60 0.00
Wall Units 90 50 4500.00
Cupboards 20 72 1440.00
Cabinets 10 50 500.00
Total 13590.00

The answer sheet showing the calculation of optimum profit and slacks is shown in appendix 1.

It may be noticed that the constraint of the minimum production quantities in respect of chairs and cabinets and the maximum production quantities in respect of tables and cupboards have been met. The optimum profit has been arrived at 13,590.

The status of usage and the slacks in the resources is represented by the following table adopted from the results of the solver exercise.

Resource Usage Available Spare
Wood 390 530 140
Corian 380 420 40
Machine 380 380 0
Labor 400 400 0

From the results it can be observed that the resources of machine and labor have been completely utilized and there is a slack of 140 in respect of wood and 40 in respect of Corian which can be effectively used in other production.

Suggestions for Improving Profitability

Although the optimum profit from the operations of the company subject to the production constraints has been calculated, if there is no constraints on arranging for more labor and machines the company can profitably use the remaining wood and corian.

The other option available is to reduce the margin on the tables and try to get a contract for some of the tables which may increase the profits for the company. The company can work on increasing the quantities on contract in respect of chairs and cabinets especially cabinets where the profitability is more. Based on the revised product mix the resources can be rearranged so that the company would be able to maximize its profits. However on these variables the management has no control. Therefore the sensitivity is worked out on different levels of increase in the resources to use the slack in the wood and corian.

Sensitivity Analysis

The sensitivity analysis is worked out in three scenarios assuming that the management has no constraint in arranging for additional resources.

Scenario 1

  • Increase in Machines 10%
  • Increase in Labor 10%
  • The answer report of excel solver with the increase of 10% in machine and labor is shown as appendix 2. In this case with the original constraints on the production, the profit has increased to 14,960.

Scenario 2

  • Increase in Machines 20%
  • Increase in Labor 20%
  • The answer report of excel solver with the increase of 20% in machine and labor is shown as appendix 3. In this case with the original constraints on the production, the profit has increased to 15,930.

Scenario 3

  • Increase in Machines 30%
  • Increase in Labor 30%
  • The answer report of excel solver with the increase of 30% in machine and labor is shown as appendix 4. In this case with the original constraints on the production, the profit has increased to 16,700.

Conclusion

Subject to the feasibility the management may decide to increase the labor and machines by 20% as at this level there is the optimum utilization of all resources and a profit of 15,930. If the labor and machines are increased by 30% even though the profit increases to 16,700, there is a slack of 26 wood and 20 labor remaining unutilized. With the increase of 10% in both the resources, still there is a slack of 102 wood. When the machines and labor is increased by 20% the only slack is 64 in wood which scenario appears to be the best possible, of course subject to the feasibility of increasing the labor and machines to the required extent.

Reference

Solver.com, 2009. Problem Types – Overview. [Online] Web.

Yu-Lee, R.T., n.d. Manufacutring Optimization: It’s about Time. [Online] Web.

Appendices

Appendix 1

The Optimum Profit with the available Resources and Constraints on Production.

Target Cell (Max)
Cell Name Original Value Final Value
$D$19 Profit 10890 13590
Adjustable Cells
Cell Name Original Value Final Value
$D$9 Chairs 20 130
$E$9 Tables 60 0
$F$9 Wall units 85 90
$G$9 Cupboards 20 20
$H$9 Cabinets 10 10
Constraints
Cell Name Cell Value Formula Status Slack
$C$11 Wood 390 $C$11<=$B$11 Not Binding 140
$C$12 Corian 380 $C$12<=$B$12 Not Binding 40
$C$13 machine 380 $C$13<=$B$13 Binding 0
$C$14 Labor 400 $C$14<=$B$14 Binding 0
$D$9 Chairs 130 $D$9>=0 Not Binding 130
$E$9 Tables 0 $E$9>=0 Binding 0
$F$9 wall units 90 $F$9>=0 Not Binding 90
$G$9 cupboards 20 $G$9>=0 Not Binding 20
$H$9 cabinets 10 $H$9>=0 Not Binding 10
$G$9 cupboards 20 $G$9>=0 Not Binding 20
$D$9 Chairs 130 $D$9>=20 Not Binding 110
$E$9 Tables 0 $E$9<=60 Not Binding 60
$E$9 Tables 0 $E$9>=0 Binding 0
$G$9 cupboards 20 $G$9<=20 Binding 0
$H$9 cabinets 10 $H$9>=10 Binding 0

Appendix 2

Sensitivity Report – Scenario 1 – Increase in Machine and Labor by 10%.

Target Cell (Max)
Cell Name Original Value Final Value
$D$19 Profit 13390 14960
Adjustable Cells
Cell Name Original Value Final Value
$D$9 Chairs 117 144
$E$9 Tables 20 0
$F$9 Wall units 77 102
$G$9 Cupboards 20 20
$H$9 Cabinets 10 10
Constraints
Cell Name Cell Value Formula Status Slack
$C$11 Wood 428 $C$11<=$B$11 Not Binding 102
$C$12 Corian 420 $C$12<=$B$12 Binding 0
$C$13 Machine 418 $C$13<=$B$13 Binding 0
$C$14 Labor 440 $C$14<=$B$14 Binding 0
$D$9 Chairs 144 $D$9>=0 Not Binding 144
$E$9 Tables 0 $E$9>=0 Binding 0
$F$9 Wall units 102 $F$9>=0 Not Binding 102
$G$9 Cupboards 20 $G$9>=0 Not Binding 20
$H$9 Cabinets 10 $H$9>=0 Not Binding 10
$G$9 Cupboards 20 $G$9>=0 Not Binding 20
$D$9 Chairs 144 $D$9>=20 Not Binding 124
$E$9 Tables 0 $E$9<=60 Not Binding 60
$H$9 Cabinets 10 $H$9>=10 Binding 0
$G$9 Cupboards 20 $G$9<=20 Binding 0
$E$9 Tables 0 $E$9>=0 Binding 0

Appendix 3

Sensitivity Report – Scenario 2 – Increase in Machine and Labor by 10%.

Target Cell (Max)
Cell Name Original Value Final Value
$D$19 Profit 14960 15930
Adjustable Cells
Cell Name Original Value Final Value
$D$9 Chairs 144 131
$E$9 Tables 0 40
$F$9 Wall units 102 87
$G$9 Cupboards 20 20
$H$9 Cabinets 10 10
Constraints
Cell Name Cell Value Formula Status Slack
$C$11 Wood 466 $C$11<=$B$11 Not Binding 64
$C$12 Corian 420 $C$12<=$B$12 Binding 0
$C$13 Machine 456 $C$13<=$B$13 Binding 0
$C$14 Labor 480 $C$14<=$B$14 Binding 0
$D$9 Chairs 131 $D$9>=0 Not Binding 131
$E$9 Tables 40 $E$9>=0 Not Binding 40
$F$9 Wall units 87 $F$9>=0 Not Binding 87
$G$9 Cupboards 20 $G$9>=0 Not Binding 20
$H$9 Cabinets 10 $H$9>=0 Not Binding 10
$G$9 Cupboards 20 $G$9>=0 Not Binding 20
$D$9 Chairs 131 $D$9>=20 Not Binding 111
$E$9 Tables 40 $E$9<=60 Not Binding 20
$H$9 Cabinets 10 $H$9>=10 Binding 0
$G$9 Cupboards 20 $G$9<=20 Binding 0
$E$9 Tables 40 $E$9>=0 Not Binding 40

Appendix 4

Sensitivity Report – Scenario 3 – Increase in Machine and Labor by 30%.

Target Cell (Max)
Cell Name Original Value Final Value
$D$19 Profit 13590 16700
Adjustable Cells
Cell Name Original Value Final Value
$D$9 Chairs 130 119
$E$9 Tables 0 60
$F$9 Wall units 90 93
$G$9 Cupboards 20 20
$H$9 Cabinets 10 10
Constraints
Cell Name Cell Value Formula Status Slack
$C$11 Wood 504 $C$11<=$B$11 Not Binding 26
$C$12 Corian 420 $C$12<=$B$12 Binding 0
$C$13 Machine 494 $C$13<=$B$13 Binding 0
$C$14 Labor 500 $C$14<=$B$14 Not Binding 20
$D$9 Chairs 119 $D$9>=0 Not Binding 119
$E$9 Tables 60 $E$9>=0 Not Binding 60
$F$9 Wall units 93 $F$9>=0 Not Binding 93
$G$9 Cupboards 20 $G$9>=0 Not Binding 20
$H$9 Cabinets 10 $H$9>=0 Not Binding 10
$G$9 Cupboards 20 $G$9>=0 Not Binding 20
$D$9 Chairs 119 $D$9>=20 Not Binding 99
$E$9 Tables 60 $E$9<=60 Binding 0
$E$9 Tables 60 $E$9>=0 Not Binding 60
$G$9 Cupboards 20 $G$9<=20 Binding 0
$H$9 Cabinets 10 $H$9>=10 Binding 0

Cite this paper

Select style

Reference

StudyCorgi. (2021, November 11). Ribble Kitchen Company’s Product Mix. https://studycorgi.com/ribble-kitchen-companys-product-mix/

Work Cited

"Ribble Kitchen Company’s Product Mix." StudyCorgi, 11 Nov. 2021, studycorgi.com/ribble-kitchen-companys-product-mix/.

* Hyperlink the URL after pasting it to your document

References

StudyCorgi. (2021) 'Ribble Kitchen Company’s Product Mix'. 11 November.

1. StudyCorgi. "Ribble Kitchen Company’s Product Mix." November 11, 2021. https://studycorgi.com/ribble-kitchen-companys-product-mix/.


Bibliography


StudyCorgi. "Ribble Kitchen Company’s Product Mix." November 11, 2021. https://studycorgi.com/ribble-kitchen-companys-product-mix/.

References

StudyCorgi. 2021. "Ribble Kitchen Company’s Product Mix." November 11, 2021. https://studycorgi.com/ribble-kitchen-companys-product-mix/.

This paper, “Ribble Kitchen Company’s Product Mix”, was written and voluntary submitted to our free essay database by a straight-A student. Please ensure you properly reference the paper if you're using it to write your assignment.

Before publication, the StudyCorgi editorial team proofread and checked the paper to make sure it meets the highest standards in terms of grammar, punctuation, style, fact accuracy, copyright issues, and inclusive language. Last updated: .

If you are the author of this paper and no longer wish to have it published on StudyCorgi, request the removal. Please use the “Donate your paper” form to submit an essay.