ALY 6050 Northeastern University Appliances Company Profit Analysis

1ALY-6050
Module Five Project
Project: Using Linear Programming Models to maximize profits
The submission of each weekly project will consist of an Excel workbook (or an R script file if R
has been used) and a Word document– a minimum of two submissions that have been
submitted as attachments. For each weekly project, students should complete their analytic
work in an Excel workbook, and write a minimum of 1000 words in a Word document
describing their findings. The Word document should be according to the APA standards, i.e., it
consists of a title page (including student’s name, assignment title, course number and title, the
current academic term, instructor’s name, and the assignment completion date), and a
reference page. The Word submission of each project will consist of three sections:
(i)
(ii)
(iii)
Introduction
Analysis
Conclusion
Project:
A northern Appliances company is studying a plan to open a new distribution center in
southeast. The company plans to rent a warehouse and an adjacent office and distribute its
main products to the local dealers. The company has decided to initially start with four of its
main products: refrigerators, dishwashers, water heaters, and pressure washers. The table
below describes how much each of these products will cost the company (including
transportation costs):
Cost
(in Dollars)
869.59
459.59
357.59
622.59
Product
Refrigerator (one unit)
Dishwasher (one unit)
Water heater (one unit)
Pressure washers (a case of 4 pressure washers)
Table 1: Costs of products in dollars
The company has set aside a purchasing monthly budget of $150,000 for the new location. The
selling prices (per unit) for each item are given in the table below:
Product
Refrigerator (one unit)
Dishwasher (one unit)
Water heater (one unit)
Pressure washer (one unit)
Selling Price (in
Dollars)
1299.99
659.99
629.99
369.99
Table 2: Revenues of products in dollars
2
Other than the budget, another of the company’s concern is the available space in the
warehouse. The warehouse has 30 shelves, and each shelf is 34 ft long and 5 ft wide (a total of
5100 square-ft of space). Each refrigerator can be stored on a 5 ft by 5 ft pallet; whereas an 8 ft
by 5 ft pallet is used to store two dishwashers, a 9 ft by 5 ft pallet is used to store three water
heaters, and a 5 ft by 5 ft pallet is used to store two cases of pressure washers. Furthermore,
warehouse managers have estimated that a total of 300 square-ft of the available space in the
warehouse should be considered unusable, in order to accommodate for the empty spaces
needed between any two pallets.
Also for promoting its brand products, the company’s marketing department has decided that
no more than 70% of its inventory should be allocated to water heaters and pressure washers.
Furthermore, there should be at least twice as many water heaters as pressure washers.
Perform a monthly analysis using a linear programming model to maximize the company’s net
profit.
Complete the following in a Word document and in an Excel workbook (or R). Submit both
the Word document and the Excel workbook (or R script file) as attachments.
1. In a Word document, write the mathematical formulation of the problem.
2. Set up the linear programming formulation in an Excel workbook or in R.
3. Use the Excel Solver or R (package lpSolve) to solve the problem and generate a sensitivity
report.
4. Describe the optimal solutions obtained in the Word document. These will consist of the
inventory level for all four products and the optimal monthly profit.
5. One of the decision variables has an optimal value of zero. Use the sensitivity report to
determine the smallest selling price for this item so that this optimal zero solution value changes
to a non-zero value.
6. In the word document explain whether, in addition to the $150,000 allocated to the purchasing
budget during the first month, the company should allocate additional money. If yes, how much
additional investment do you recommend, and how much should the company expect its net
monthly profit to increase as a consequence of this increase?
7. In the word document, explain whether you recommend that the company should rent a
smaller or a larger warehouse. In any case, indicate the ideal size of your recommended
warehouse in square feet, and indicate how much this change in the size of the warehouse will
contribute to the monthly profit.