An important tool that we can use to make this computation is the SUMPRODUCT function. Essentially, Solver is an optimization engine that flawlessly performs the trial-and-error search.Ī key to solving the product mix problem is to efficiently compute the resource usage and profit associated with any given product mix. We use Solver in this process only at the trial-and-error stage.
#Fast calc ltd trial
Then we would use trial and error to vary the product mix to optimize profit without using more labor or raw material than is available, and without producing any drug in excess of demand. If we knew nothing about Excel Solver, we would attack this problem by constructing a worksheet to track profit and resource usage associated with the product mix. How can this company maximize its monthly profit? This month, 4500 hours of labor and 1600 pounds of raw material are available. For example, demand for Product 3 is 1041 pounds. The month’s demand for each drug is given in row 8. For example, Product 2 sells for $11.00 per pound, incurs a unit cost of $5.70 per pound, and contributes $5.30 profit per pound. For each drug, the price per pound is given in row 6, the unit cost per pound is given in row 7, and the profit contribution per pound is given in row 9. For example, producing a pound of Product 1 requires six hours of labor and 3.2 pounds of raw material.
Row 4 in Figure 27-1 shows the hours of labor needed to produce a pound of each product, and row 5 shows the pounds of raw material needed to produce a pound of each product. Production of each product requires labor and raw material. Let’s say we work for a drug company that produces six different products at their plant. You can find the solution to this problem in the file Prodmix.xlsx, shown in Figure 27-1. Let’s now solve the following example of the product mix problem. We can’t produce more of a product during a month than demand dictates, because the excess production is wasted (for example, a perishable drug). There is a limited demand for each product. Product mix can’t use more resources than are available. Product mix must usually adhere to the following constraints:
#Fast calc ltd how to
In its simplest form, the product mix problem involves how to determine the amount of each product that should be produced during a month to maximize profits. How can I determine the monthly product mix that maximizes profitability?Ĭompanies often need to determine the quantity of each product to produce on a monthly basis. This article discusses using Solver, a Microsoft Excel add-in program you can use for what-if analysis, to determine an optimal product mix.
0 kommentar(er)
