Blending Problem using Excel Solver
Abotte Products produces three products, A, B, and C. The company can sell up to 300 pounds of each product at the following prices (per pound): product A, $10; product B, $12; product C, $20. Abotte purchases raw material at $5 per pound. Each pound of raw material can be used to produce either one pound of A or one pound of B. For a cost of $3 per pound processed, product A can be converted to 0.6 pound of product B and 0.4 pound of product C. For a cost of $2 per pound processed product B can be converted to 0.8 pound of product C.
Determine how Abotte can maximize its profit.
Determine how much of A to make, how much of A to reprocess into B and C, how much of B to make, and how much of B to reprocess into C. That would be 4 changing cells. Remember that how much you make won't always be how much you have to sell. If I make 200 pounds of A but reprocess change 50 pounds, I will only have 150 pounds of A to sell (200 less the 50 reprocessed) but I get some B and C to sell.
Here are some check figures:
If I make 20 pounds of A and 20 pounds of B, I should use 40 pounds of raw material. Then if I convert half of each, I should have these quantities to sell:
Product A 10
Product B 16
Product C 12
The profit with this plan would be: $282
Hint: The optimal profit I got was: $6,975
Create a spreadsheet in excel. Run Solver to get the optimal result.