In: Math
MAXIMIZING PROFIT: CUSTOM GAMING PCs
You are the owner of a mid-sized computer manufacturing and assembling facility – what started as a small business building and customizing computers for friends and family has grown into a much larger operation, and you want to make sure you are using your labor hours in the most efficient way to maximize your profits. Your company manufactures and assembles three types of custom gaming PCs, and the amount of manufacturing and assembling times required for each model along with the profit you make on each unit are given below:
Model Name |
Manufacturing Time (in hours) |
Assembling Time (in hours) |
Profit |
Dendrite Ice |
2 |
2 |
$280 |
Neuron Pro |
3 |
2 |
$320 |
Axon Glacier Pro |
2 |
4 |
$400 |
Your labor budget each week is 1,000 hours of total manufacturing time, and 1,600 hours of assembly time. How many of each type of custom gaming PC should you task your teams with creating each week to maximize your profit?
1. Provide the solution to your completed tableau, listing the values for each variable (including any slack variables) and the objective function. Finally, state the solution in terms of our original problem: how many of each type of custom gaming PC should your teams manufacture each week in order to maximize your profits?
We will help this problem with the help of Excel Solver.
We are given with the constraints and the function which is to be maximized.
Let us load the data into Excel which is as follows:
Model Name | Manufacturing Time (in hours) | Assembling Time (in hours) | Profit (per unit sold) | Limits |
Dendrite Ice | 2 | 2 | $280 | ≤ |
Neuron Pro | 3 | 2 | $320 | ≤ |
Axon Glacier Pro | 2 | 4 | $400 | ≤ |
Decision Variables | Total | |||
Maximize | 1000 | 1600 |
Let us use the functions for the required profit with the help of the table below:
Model Name | Manufacturing Time (in hours) | Assembling Time (in hours) | Profit (per unit sold) | Limits | Required Profit (per unit sold) |
Dendrite Ice | 2 | 2 | $280 | ≤ | =SUMPRODUCT(B2:C2,B7:C7) |
Neuron Pro | 3 | 2 | $320 | ≤ | =SUMPRODUCT(B3:C3,B7:C7) |
Axon Glacier Pro | 2 | 4 | $400 | ≤ | =SUMPRODUCT(B4:C4,B7:C7) |
Decision Variables | 0 | 0 | Total | ||
Maximize | 1000 | 1600 | =SUMPRODUCT(B7:C7,B8:C8) |
where B7:C7 is the cell of decision variable.
B2:C2 are manufacturing time and assembly time respectively.
Using Excel to solve the problem, we will do the steps as follows:
Go to Data>Solver.
Use the commands as below:
1. Provide the solution to your completed tableau, listing the values for each variable (including any slack variables) and the objective function. Finally, state the solution in terms of our original problem: how many of each type of custom gaming PC should your teams manufacture each week in order to maximize your profits?
We have to get the profit per unit from Dendrite Ice of $260 per unit and profit per unit from Neuron Pro of $320 per unit and profit per unit from Axon Glacier Pro of $400 per unit.
The objective function is to Maximize profit:
1000x1 + 1600x2
Thus, we have to use 60 manufacturing and 70 assembly units to get a maximum profit of $172,000.
Maximum Profit = 1000*60 + 1600*70 = $172,000.