Optimum Product Mix Excel Solver
Layton Machining Company (LMC) manufactures two versions of a basic machine tool. One version is a standard model and one is a custom model, which requires some additional work and slightly higher grade materials. The manufacturing process at LMC requires that each product go through two departments, Grinding and Finishing. The process in each department uses a single type of machine. Total machine capacity in Grinding is 50,000 hours, and in Finishing, total machine capacity is 30,000 hours. (Each department has multiple machines.) Total market demand is limited to 100,000 standard units and 120,000 custom units monthly. LMC is currently producing 90,000 standard units and 50,000 custom units each month. Cost and machine usage data for the two products follow:
|
|
A |
B |
C |
D |
E |
F |
G |
H |
|
|
1 |
|
|
Standard |
|
|
Custom |
|
|
|
|
2 |
Price |
|
$6.00 |
|
|
$8.00 |
|
|
|
|
3 |
Less variable costs per unit |
|
|
|
|
|
|
|
|
|
4 |
Material |
|
1.50 |
|
|
2.00 |
|
|
|
|
5 |
Labor |
|
1.25 |
|
|
1.50 |
|
|
|
|
6 |
Overhead |
|
1.75 |
|
|
2.50 |
|
|
|
|
7 |
Contribution margin per unit |
|
$1.50 |
|
|
2.00 |
|
|
|
|
8 |
|
|
|
|
|
|
|
|
|
|
9 |
Fixed costs |
|
|
|
|
|
|
Total |
|
|
10 |
Manufacturing |
|
|
|
|
|
|
$76,000 |
|
|
11 |
Marketing and administrative |
|
|
|
|
|
|
$37,000 |
|
|
12 |
|
|
|
|
|
|
|
$ 113,000 |
|
|
13 |
|
|
|
|
|
|
|
|
|
|
14 |
Grinding machine hours per unit |
|
0.2 |
|
|
0.3 |
|
|
|
|
15 |
Finishing machine hours per unit |
|
0.1 |
|
|
0.4 |
|
|
|
|
16 |
Grinding machine hours used |
|
|
|
|
|
|
33,000 |
|
|
17 |
Grinding machine hours available |
|
|
|
|
|
|
50,000 |
|
|
18 |
Finishing machine hours used |
|
|
|
|
|
|
29,000 |
|
|
19 |
Finishing machine hours available |
|
|
|
|
|
|
30,000 |
|
|
20 |
|
|
|
|
|
|
|
|
|
|
21 |
Quantity produced |
|
|
|
|
50,000 |
|
|
|
|
22 |
Maximum demand |
|
|
|
|
120,000 |
|
|
|
|
23 |
Profit |
|
|
|
|
|
|
|
|
Required
a. What is the optimal production schedule for LMC? In other words, how many standard units and custom units should the company produce each month to maximize monthly profit t?
b. If LMC produces at the level found in requirement (a), how much will monthly profit t increase over the current production schedule?
[[Integrative Cases]]