| Cox Cable & Wire |
| Midwest Contract |
| Machine Capacity | | | | | | Income Summary |
| | | General | National | | | | Plastic | Teflon | Total |
| Hours Required Per Reel | Plastic | 2.0 | 2.5 | | | Revenue | $72,000 | $56,000 | $128,000 |
| | Teflon | 1.5 | 2.0 | | | Costs | $0 | $0 | $0 |
| Hours Available: | June | 140 | 250 | | | Profit | $72,000 | $56,000 | $128,000 |
| | July | 60 | 80 |
| | August | 150 | 100 | | | Profitability |
| | | | | | | 100.0% |
| Revenue Per Reel |
| Plastic | 360.00 |
| Teflon | 400.00 | | | | | (a) |
| Customer Demand | June | July | August | Total | | (b) | Material cost increase (%): |
| Reels - Plastic | 50 | 100 | 50 | 200 | | | (Use Goal Seek) |
| Reels - Teflon | 30 | 60 | 50 | 140 |
| | | | | | | (c) Use Data Sensitivity | Plastic | Profit | | Teflon | Profit |
| Costs |
| Material Cost Per Reel | Plastic | 160.00 |
| | Teflon | 200.00 |
| Packaging Cost Per Reel | Plastic | 40.00 |
| | Teflon | 40.00 |
| Inventory Cost/Reel/Month | Plastic | 10.00 |
| | Teflon | 10.00 |
| | | General | National |
| Machine Cost Per Hour | | 30.00 | 30.00 |
| Machine Cost Per Reel |
| | Plastic | 60.00 | 75.00 |
| | Teflon | 45.00 | 60.00 |
| Production Plan | | | | | | Cost Accounting |
| | | General | National | Total |
| Reels To Produce: | Plastic - June | | | | | Machine Costs | Total | General | National |
| | Plastic - July | | | | | Plastic |
| | Plastic - August | | | | | Teflon |
| | Plastic - Total | | | | | Total |
| | Teflon - June | | | | | Material Cost |
| | Teflon - July | | | | | Plastic |
| | Teflon - August | | | | | Teflon |
| | Teflon - Total | | | | | Total |
| | Total - June | | | | | Packaging Cost |
| | Total - July | | | | | Plastic |
| | Total - August | | | | | Teflon |
| | Grand Total | | | | | Total |
| Hours Scheduled: | Hours - June | | | | | Inventory Cost |
| | Hours - July | | | | | Plastic |
| | Hours - August | | | | | Teflon |
| | Hours - Total | | | | | Total |
| Shipping |
| | June | July | August | Total |
| Plastic Customer Demand: | 50 | 100 | 50 | 200 |
| Plastic Reels Produced: |
| Plastic from Initial Inventory: |
| Plastic to Final Inventory: |
| Plastic Shipped |
| Teflon Customer Demand: | 30 | 60 | 50 | 140 |
| Teflon Tires Produced: |
| Teflon from Initial Inventory: |
| Teflon to Final Inventory: |
| Teflon Shipped |
azoz9192
lecture_4_problem_template_with_parameters.xlsx
