order to create the model for this situation, I first need to identify the
goal, decision variables, and production restraints. The goal is to
identify the optimal capacity utilization for the various product groups, in
order to maximize our profit. Our decision variables, which we
adjust in order to achieve our goal, are the number of each product that we
produce. As for restraints, we have 2 categories, Capacity restraints &
Our capacity Restraints relate to our ability to perform a maximum
number of repetitions of the various steps in the process. In this example,
there are 4 steps, and we are able to perform each step a maximum number of
time in any month, as follows:
A maximum of 1,600 hours of printing is possible each month.
A maximum of 2,600 hours of mounting is possible each month.
A maximum of 1,700 hours of assembly is possible each month.
A maximum of 1,500 hours of inspection is possible each month.
Our demand restraints relate to customer demand for the products. If we
are producing more products than there is demand for, we will be losing money
on each product we make beyond demand. Thus we want to restrict our production
numbers to below the following demand restraints provided.
A maximum of 210 each month.
A maximum of 370 each month.
A maximum of 450 each month.
A maximum of 180 each month.
approach is not very effective without further analysis, because it does not
take into account the resources required to produce these high margin products.
Though it is true that our margins are higher per product on FL & HL, if we
are only able to produce a small number of these products due to heavy production
requirements, our overall profit may be smaller than if we produce a very large
number of products that have a smaller profit margin.
algebraic model formulation is as follows:
Printing Capacity: 1ML + 1.5SL + 1.5FL + 0.5HL =< 1600 Mounting Capacity: 1.5ML + 3SL + 4FL + 5HL =< 2600 Assembly Capacity: 2ML + 1SL + 3FL + 4HL =< 1700 Inspection Capacity: 0.5ML + 0.5SL + 1FL + 0.5HL =< 1500 ML =< 180 SL =< 450 FL =< 370 HL =< 210 Figure 1 shows Excel Solver with restriction formulas input into the Solver parameters. According to my report, the optimal number of each product to produce is as follows: · HyperLink: 0 · FastLink: 245 · SpeedLink: 450 · MicroLink: 180 D. I don't think that the result is intuitive. It is not intuitive to avoid producing the product that provides the highest profit margin. To explain the result in an intuitive rule, I would say, "Prioritize the products that best maximize your resource limitations. By better utilizing your resources, you are able to increase the number of products you can sell to create profit. E. Yes I think it is still valid. As my resource decreased (number of printing hours I have), the number of HyperLink's increase, as it is best able to utilize this resource. The SpeedLink & FastLinks, require the most of this resource, and we see a reduction in FastLinks as a result. We can justify keeping Speedlinks at their current level because they better utilize the other resources comparable to FastLinks. Figure 2 shows that as my printing resource decreases, my Hyperlink production increases as it most efficiently utilizes the decreasing resource. Production of Fastlinks, which are less efficient with the resource, decreases. F. Based on our model and existing "rule" we have created, I do not believe minor fluctuation in pricing will change our production strategy. In order to test this, I have ran the model again reducing the profit margin of our high production items (effectively increasing price) and increasing the contribution on our low production items (effectively decreasing price), and I received the same production recommendations from solver. Figure 3 G. When mounting capacity is at 200, a sensitivity report will show us our "shadow price" of $66.66. This tells us that each additional unit of this resource (one hour of mounting), will increase our maximum objective value by $66.66, but only up to 70 additional units. Figure 4 After 70 hours, we can assume the shadow price will decrease (as scarcity decreases), and we will see a change in the slope of our curve. Looking at the graphical representation of the data, we do in fact notice the shifts in the slope of our chart (Fig. 5). Figure 5 Figure 6 Finally, after 3,000 hours of mounting, we see our line 'flat-line", and not grow in value. This implies that we have maximized another resource, and are unable to produce additional output, regardless of how much further we increase our Mounting variable. Figure 7 illustrates that at 3,000 mounting hours, we have exhausted our assembly hours. Regardless of any increases to mounting, we are no longer able to increase our profits, as we don't have any mounting hours available. H. Below is the Screenshot of my Sensitivity Output. It does correspond with the information in the previous question. At 2600 mounting hours, each hour has a shadow price of $40, with an allowable increase of 206.67. This means I can expect my slope to be the same at 2800 (which is an increase of 200 hours), but there will be some change at 3,000 hours, because it exceeds the allowable increase limit, and the value of each hour will change. I. I believe that the minimum profit contribution should be greater than $40/hour to be financially attractive. Our sensitivity report indicates that we are able to reduce our operations mounting input by 980 hours and maintain our current value. Because the 300 hours is within this allowable range, we can calculate the value of each hour to be worth at least $40. The answer does not change based on the price of existing mounting equipment, because this is a sunken cost that is placed into the fixed cost area, and does not influence my contributions. Appendix (A) The below image shows my solver parameters, which identifies my Goal, Lists all 8 of my constraints, and identifies my decision variables.