Pitt Motors manufactures small engines. The engines are sold to manufacturers

who install them in products like lawn movers, snow blowers etc. The company

currently makes all the parts used in these engines, but is considering a proposal

from an external supplier $-$ Ohio Electronics $$ to supply the starter assembly for

the engines.

The starter assembly is currently manufactured in Division $3$ of Pitt Motors. The

costs relative to Division $3$ for the last $12$ months are as follows:

Direct Materials $ $200,000$

Direct Manufacturing Labor $150,000$

Manufacturing Overhead $400,000$

Total $$750,000$

Over the last year, Division $3$ manufactured $150,000$ starter assemblies; the

average cost for starter is computed as $$5(750,000/150000).$ Further analysis of

manufacturing overhead revealed the following information. Of the total

manufacturing overhead, only $25\%$ is considered variable fixed portion, $$150,000$

is an allocation of general overhead that would remain unchanged for the

company as a whole if production is discontinued. A further $$100,000$ of the fixed

overhead is avoidable if self$-$manufacture of the starter assembly is discontinued.

The balance of the current fixed overhead, $$50,000$ is the division manager salary.

If self$-$manufacture of the starter assembly is discontinued, the manager of

Division $3$ will be transferred to Division $2$ at the same salary. This move will allow

the company to save the $$40,000$ salary that would otherwise be paid to attract

an outsider to this.

Ohio Electronics, a nearby supplier has offered to supply starter assembly at $$4$

per unit. Since this price is less than the current average unit cost of $$5$ per unit,

the VP of Manufacturing is eager to accept this offer. Should the outside offer

from Ohio Electronics be accepted? Explain your reasoning. $($ Hint: Production

output in the coming year may be different from production output last year$)$

How at all, would your response change if the company could use the vacated

plant space $($ from going to the outside supplier Ohio Electronics$)$ and, in doing so

avoid $$50,000$ of outside storage charges currently incurred for general storage

needs? Why is this information relevant or irrelevant?

Please show me what are the constraints and the decision varaibles $,$ and how my excel spredsheet shuld look like when you insert all this information, after using Solver.