Share
Explore BrainMass

Use Excel solver to create a model for a tire manufacturer

See attached Excel file.

A tire manufacturer produces two types of heavy truck tires (standard and custom) using three operations. The time per operation to produce the tires is given as follows:

Operation standard custom total operation time per week
compounding 2.5 2.0 1500
building 3.5 5.5 2500
finishing 2.5 1.5 1500

Standard tires sell for $350 and custom tires sell for $450. Management has determined that at least 25% of the tires made should be standard and at least 35% should be custom.

a. How many of each type of tire should the company make if it wants to maximize its revenue?

b. If the company could get 50 more units of building capacity, should they do so? If so, how much should they be willing to pay for it?

c. If the company could get 50 more units of finishing capacity, should they do it? if so, how much should they be willing to pay for it?

d. Suppose the finishing time on standard tires could be recduced from 2.5 to 2.0 units per tire. How much should the company be willing to pay to achieve this improvement in efficiency?

e. The sales director knows from experience that the critical price point for standard tires is $299.99; any price over that will meet with market resistance. What counsel would you offer?

Attachments

Solution Summary

The expert uses Excel solver to create a model for a tire manufacturer.

$2.19