![]() ![]() 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: 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 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |