Profit is easily computed in cell D12 with the formula SUMPRODUCT(D9:I9,$D$2:$I$2). (Drug 6 profit per pound)*(Drug 6 pounds produced) (Drug 2 profit per pound)*(Drug 2 pounds produced) +. (Drug 1 profit per pound)*(Drug 1 pounds produced) + In a similar fashion, our profit is determined by The formula in cell D15 computes raw material usage. This formula computes D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (which is our labor usage) but is much easier to enter! Notice that I use the $ sign with the range D2:I2 so that when I copy the formula I still capture the product mix from row 2. A much easier way to compute labor and raw material usage is to copy from D14 to D15 the formula SUMPRODUCT($D$2:$I$2,D4:I4). Imagine how long it would take if you were working with a company that produced, for example, 50 products at their plant. However, entering these formulas in a worksheet for six products is time-consuming. Similarly, raw material usage could be computed as D2*D5+E2* E5+F2*F5+G2*G5+H2*H5+I2*I5.
We could compute labor usage in a more tedious fashion as D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. (Labor used per pound of drug 6)*(Drug 6 pounds produced) (Labor used per pound of drug 2)*(Drug 2 pounds produced) +. (Labor used per pound of drug 1)*(Drug 1 pounds produced)+ Each cell range used in a SUMPRODUCT evaluation must have the same dimensions, which implies that you can use SUMPRODUCT with two rows or two columns, but not with one column and one row.Īs an example of how we can use the SUMPRODUCT function in our product mix example, let’s try to compute our resource usage. The SUMPRODUCT function multiplies corresponding values in cell ranges and returns the sum of those values. 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.
#How to use microsoft excel to see profits 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:
#How to use microsoft excel to see profits 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)
