+1-817-968-5551
+61-488-839-671
+44-7480-542904About Expert
Key Topics
Requirement
The owner of a hardware store wants to design a decision support system to predict how many and which type of nails she should sell and what information she needs to do so. The scenario is described below:
Consider that you offer six types of nails and can make as many as you need of each. These are:
4-inch nails
3.5-inch nails
3-inch nails
2.5-inch nails
2-inch nails
1.5-inch nails
The cost of making each type of nail depends on the size of the nail. The costs and selling prices are listed in the table below, along with the weights. The nails will be sold in boxes of up to 30. There must be no more than 10, but no less than five, of each of three types of nails in each box. The nails in each box should weigh no more than 20 ounces. You’re looking for the combination with the highest profit using a trial-and-error method. A spreadsheet would
be helpful for completing this project. You’ll most likely find that you identify some promising paths to follow right away and will concentrate on those to reach the best one.
(1)Justify at least three different considerations you looked at while attempting to come up with the most profitable solution.
(2)What would go into such a system if you were the owner of the store and your business profits were at stake?
Solution
Introduction
The assignment asks to find the configuration of the product in order to increase the total profitability. The owner of the hardware store is selling 6 types of nails in different sizes. The nails all have different weights and are sold in at different prices. The profit margins on each type of nails increases with the weights but the total weight of the nail box has to be kept below a max of 30 ounces. Each box may contain three different type of nails. But each different type of nails should be greater than 5 and less than equal to 10.
Require reliable marketing assignment help? Sign up and place your order at Allassignmenthelp.com. Our professional writers are experts in providing strategic marketing assignment help at an affordable price. We have helped thousands of students with their marketing management assignments help.
Methodology
In order to find the optimal number of each type of nails to be sold in a box, the problem is formulated as an optimization problem with a view to maximize the total profit coming from a box of nails (Antoniou, & Lu, 2007). The optimization problem is solved using the “Solver” function in MS Excel.
The per unit profit of the nail box is calculated using as the sum of the profit from each type of nails.
Profit from a Nail Box= ∑_(i=1)^3??P_i*N_i ?
In the above equation i=Type of nails,P_i=Profit per nail for i^th nail and N_i=Number of i^th nail.
The profit per nail for each given type of nail is calculated using the following formula.
Profit from a nail=Sale Price-Cost
Spread Sheet setup in Excel
The problem is solved using MS Excel. The first step involves calculating the profit for each type of the nail (Lawler, E. L. 1972). The profit coming from the nail is calculated using the formula given above.
The combination with the maximum profit is found out using the Trial and Error Method. First of all, any three types of nails are selected at random. The profit for the nail box is calculated for each trial. Then the problem is solved using the “Solver” function in MS Excel.
The information of each configuration of the box is given as shown below.
In the above example, three types of nails namely 4-inch, 3-inch and 2-inch nails are selected at random and the profits corresponding to each type of nail is found in the last column. The total profit for a given type of nail is found out using the formula
Profit=Profit per unit of Nail*Number of Nails
Optimization Problem Setup
As mentioned earlier, after selecting the type of the nails to be put in the box, the number of each different type of nails is solved as a maximization problem in Excel. The objective function of this maximization problem is to maximize the Total Profit from the box of nail. The details of the optimization problem is given below.
The objective function is max?(Total Profit from a nail box)
The changing variables for the optimization is given as number of each type of nails.
The constraints are given as
Maximum number of nails in a box = 30
Maximum number of one type of nail in a box = 10
Minimum number of one type of nail in a box = 5
Maximum weight of a nail box = 20 ounces
The implicit constraint in this optimization problem is that the number of the nails has to an integer.
In the excel sheet the constraints are given as shown in the figure.
In order to solve the optimization problems “GRG Nonlinear” method is used in the excel solver function.
Results
In order to arrive at the most profitable combination, 4 trails were conducted. The type of nails for each configuration is selected at random and then optimization is done to arrive at the number of each selected nail type. The results for each trial is shown in the following figures.
Trail one
The total profit for Trial 1 was 70 cents per box of nail.
Trial 2
The total profit for Trial 2 is 60 cents per box.
Trial 3
The total profit is 69 cents per box.
Trial 4
The total profit for trial 4 is 71.5 cents per box.
As evident from the Trial and Error method, the maximum profit is obtained in the case of Trail 4.
The best combination of the products in a box is given as
10 units of 4-inch nails, 9 units of 3.5-inch nails and 9 units of 2-inch nails.
The total weight of the nail box is 19.9 ounces.
Place Order For A Top Grade Assignment Now
We have some amazing discount offers running for the students
Place Your OrderReferences
-
Antoniou, A., & Lu, W. S. (2007). The Optimization Problem(pp. 1-26). Springer US.
-
Lawler, E. L. (1972). A procedure for computing the k best solutions to discrete optimization problems and its application to the shortest path problem. Management science, 18(7), 401-405.
Give your grades a boost
Just share your requirements and get customized solutions on time.
-
1,168,768 Orders
-
4.9/5 Overall Rating
-
5,052 Experts
