Smithton Metal Products has recently accepted a contract to deliver three types of flywheels (Economy, Durabilt, and Superfly) to a new customer. Quantities required are as follows:
Economy– 1000 units
Durabilt– 3100 units
Superfly– 1600 units
The flywheels are manufactured through a three step process as follows:
|
Production Time Required (hours) |
Available Capacity |
|||
|
Economy |
Durabilt |
Superfly |
||
|
Cuttting |
1.5 |
1.5 |
2.5 |
8000 hours |
|
Grinding |
2 |
3 |
3.5 |
10,000 hours |
|
Finishing |
1 |
2 |
4 |
6,000 hours |
The order must be delivered by December 31, or Smithton is subject to a significant late penalty. Given the production time requirements, management is concerned that the deadline can’t be met using in-house resources only. Fortunately, Smithton has a long-time relationship with another manufacturer, East Cleveland Fabricating, who may be able to produce some of the flywheels. Respective manufacturing costs are shown below:
|
Economy |
Durabilt |
Superfly |
|
|
Smithton |
$52.00 |
$83.00 |
$128.00 |
|
East Cleveland |
$61.00 |
$96.00 |
$145.00 |
I need help solving these problems in excel. Thanks.
Step 1 - Identifying the bottleneck activity
| Particulars | Economy | Durabit | Superfly | Total | Notes | Remarks | |
| a | Demand in units | 1000 | 3100 | 1600 | |||
| b | Cutting time / unit | 1.5 | 1.5 | 2.5 | Bottleneck activity | ||
| c | Total time Required | 1500 | 4650 | 4000 | 10,150.00 | a x b | |
| d | Available time | 8,000.00 | Given | ||||
| e | Grinding time /unit | 2 | 3 | 3.5 | |||
| f | Total time Required | 2000 | 9300 | 5600 | 16,900.00 | a x e | |
| g | Available time | 10,000.00 | Given | ||||
| h | Finishing time /unit | 1 | 2 | 4 | |||
| i | Total time Required | 1000 | 6200 | 6400 | 13,600.00 | a x h | |
| j | Available time | 6,000.00 | Given |
As the Cutting time required to fulfill the demand ( 10150hr) is less than the available time (8000hr),the bottleneck activity is Cutting and the decision regarding how many products to be produced in inhouse and how many to be outsourced to East Clevelar has to decided according to the time available in Cutting.
Step II Setting the prefernce of production
As only the cost incurr for production and purchase ( incase of outsourcing ) is available set the prefernce of production, ie., which product, at what quantity should be produced by Smithton or outsourced to East Clevelar.
| Particulars | Economy | Durabit | Superfly |
| Smithton Cost / unit in $ | 52 | 83 | 128 |
| East Cleveland in Cost / unit in $ | 61 | 96 | 145 |
| Cost Difference | 9 | 13 | 17 |
| Preference of allocation in inhouse production | 3 | 2 | 1 |
As the cost differnce is high Superfly , it will be the 1st prefernce for inhouse production.
Step III Identifying the number of flywheels to be produced and out sourced at minimum cost
| Particulars | Economy | Durabit | Superfly | Total | Notes | |
| a | Demand in units | 1000 | 3100 | 1600 | ||
| b | Preference of allocation of cost | 3 | 2 | 1 | ||
| c | Total time Required in cuttting | 1,500.00 | 4,650.00 | 4,000.00 | 10,150.00 | |
| d | Allocation of available time in cutting | - | 4,000.00 | 4,000.00 | 8,000.00 | |
| e | Extra time required | 1,500.00 | 650.00 | - | 2,150.00 | c-d |
| f | Cutting time / unit | 1.50 | 1.50 | 2.50 | ||
| g | Inhouse Production Units (Smithton) | - | 2,666.00 | 1,600.00 | d/f | |
| h | Outsourced to East Cleveland | 1,000.00 | 434.00 | - | e/f |
Smithton Metal Products has recently accepted a contract to deliver three types of flywheels (Economy, Durabilt,...