A company assembles and then tests two models of drones, aerial and land-based. For the upcoming month, the company wants to decide how many of each model to assemble and then test. No drones are in inventory from the prior month, and because these models are going to be changed after this month, the company doesn’t want to hold inventory after this month.
It believes the most it can sell this month are 600 aerial and 1100 land-based drones. Each aerial drone sells for $300 and each land-based drone sells for $450. The cost of components for aerial is $150; for land-based is $225. Labor is required for assembly and testing.
There are at most 9,000 assembly hours and 3500 testing hours available. Each labor hour for assembling costs $14 and each labor hour for testing costs $13.
Each aerial requires 7 hours for assembling and 2 hours for testing, and each land-based requires 5 hours for assembling and 3 hours for testing.
Now the company would like to add a third drone called marine that the company can produce in addition to aerial and land. Each marine requires 8 hours for assembling, 3 hours for testing, $275 for component parts and it sells for $470. The most marine drones that can be sold are 50.
The company wants to know how many of each model it should produce (assemble and test) to maximize its net profit.
Use Excel linear programming to find the best mix of drones that stays within the company’s labor availability and maximum sales constraints