| Production Planning | ||||
| A company has a technology and capacity to make three different products, say, A, B, and C. Three production departments are involved in manufacturing of the products. | ||||
| The following table specifies the unit production times: | ||||
| Given Values | ||||
| Time Per Product Unit | ||||
| Available Time | A | B | C | |
| Department 1 | 860 | 2 | 1 | 4 |
| Department 2 | 728 | 3 | 2 | 1 |
| Department 3 | 794 | 0 | 3 | 3 |
| Unit Profit | 170 | 90 | 160 | |
| Manipulated Values | ||||
| Number of Units Produced | 0.0 | 0.0 | 0.0 | |
| Calculated Values | ||||
| Departmental Resources Used | ||||
| A | B | C | Totals | |
| Department 1 | 0 | 0 | 0 | 0 |
| Department 2 | 0 | 0 | 0 | 0 |
| Department 3 | 0 | 0 | 0 | 0 |
| Profit | 0 | 0 | 0 | 0 |
| Test For Allowed Values | ||||
| Department A | True | |||
| Department B | True | |||
| Department C | True | |||
| All Values OK? | True | |||
| Based on the above information, determine profitability and maximum production levels for the following production scheduling scenarios: | ||||
| Profit | ||||
| Scenario 1: | Product A Only | |||
| Scenario 2: | Product B Only | |||
| Scenario 3: | Product C Only | |||
| Scenario 4: | Equal Production Levels for Products A, B, and C | |||
| Scenario 5: | Production Levels that Utilize Entirely the Times Available in All Departments | |||
| Scenario 6: | The Optimal Production Schedule | |||
| Production A | ||||
| Production B | ||||
| Production C | ||||
This table was created by a free Excel macro written by Charles Balch.
Here's the code. It is care ware - it's yours for free if do something nice for anyone (anyone includes you).