Linear Programming Model for maximizing net profit with input constraints

Input values for the model

1. If we still use the product mix model with some revisions here. Now, there are six available models, not just two. Each computer must be assembled and then tested, but there are now three lines for testing.

The inputs for the model are same here as before: (1) the hourly labor costs for assembling and testing, (2) the required labor hours for assembling and testing any computer model, (3) the cost of component parts for each model, (4) the selling prices for each model, (5) the maximum sales for each model, and (6) labor availabilities. These input values are listed in the file HW_09. xlsx. Similarly, the company needs to determine the product mix that maximizes its total net profit.

Pleaseuse LP to find the mix of computer models that maximizes total net profit and stays within the labor hour availability and maximum sales constraints.conduct the sensitivity analysis test for the model and choose two values from the Variable Cells and Constraints respectively to interpret.

Cost per labor hour assembling    $11                    
Cost per labor hour testing, line 1    $19                    
Cost per labor hour testing, line 2    $17                    
Cost per labor hour testing, line 3    $14                    
                        
Inputs for assembling and testing a computer                        
    Model 1    Model 2    Model 3    Model 4    Model 5    Model 6
Labor hours for assembly    4    5    5    5.5    5.5    5.5
Labor hours for testing, line 1    1.5    2    2    2.5    2.5    2.5
Labor hours for testing, line 2    2    2.5    2.5    3    3    3.5
Labor hours for testing, line 3    1    1    1.5    2    2.5    1
Cost of component parts    $150    $225    $225    $250    $250    $250
Selling price    $350    $460    $470    $500    $525    $530
Unit margin, tested on line 1                        
Unit margin, tested on line 2                        
Unit margin, tested on line 3                        
                        
Assembling, testing plan (# of computers)                        
    Model 1    Model 2    Model 3    Model 4    Model 5    Model 6
Number tested on line 1                        
Number tested on line 2                        
Number tested on line 3                        
Total computers produced                        
                        
Maximum sales    1500    1250    1250    1000    1000    1000
                        
Constraints (hours per month)    Hours used    Hours available                
Labor availability for assembling        20000                
Labor availability for testing, line 1        5000                
Labor availability for testing, line 2        6000                
Labor availability for testing, line 3        7000