please provide solution by excel operation and please provide working process
Calibri Light (Head... V 18 ' I III Paste B I U v y 2 2x fx Problem Set 5. (20 points) A B D Dana Corporation, based in Toledo, Ohio, is a global manufacturer of highly enigeered products that serve industrial, vehicle, construction, commercial, aerospace, and semiconductor markets. It frequently subcontracts work to other manufacturers, depending on whether Dana's facilities are fully occupied. Suppose Dana is about to make some financial decision regarding the use of its manufacturing facilities for the coming year. The following are the costs of making part EC113, a key component of an emissions control system: Cost per unit 8.00 Manufacturing Costs for 65,000 units Direct material Direct labor Variable factory overhead (OH) Fixed factory overhead (OH) Total manufacturing costs 11.00 Total costs 520,000 715,000 585,000 195,000 2,015,000 9.00 3.00 31.00 Another manufacturer has offered to sell the same part to Dana for $28 each. The fixed factory overhead consists of f depreciation, property taxes, insurance, and supervisory salaries. $100,000 out of $195,000 fixed factory overhead costs would continue, which is unavoidable, even if Dana bought the component. Requirement 1. Assume that the capacity now used to make parts will become Idle if the parts are purchased. Construct the decision model with the relevant cost Information to compare Make case and Buy case Make Buy Relevant (Avoidable) costs Total per Unit Total per Unit Requirement 2. Assume that the capacity now used to make parts will either (a) be rented to a nearby manufacturer for the rental Income of $35,000 for the year but Dana corp. should pay a brokerage fee of $15,000 for the deal, or (b) be used to make all filters that will yield a profit contribution of $20,000 but require $10,000 additional fixed costs. Construct Incremental Analysis Model with the relevant information in the box. Buy and Use Incremetal Analysis. Make Buy and Leave Buy and Rent out facilities idle Facilities facilities for oil filters Requirement 3. Which operational decision among the four alternatives should you as a manager make? Why? Calibri Light (Head... V 18 ' I III Paste B I U v y 2 2x fx Problem Set 5. (20 points) A B D Dana Corporation, based in Toledo, Ohio, is a global manufacturer of highly enigeered products that serve industrial, vehicle, construction, commercial, aerospace, and semiconductor markets. It frequently subcontracts work to other manufacturers, depending on whether Dana's facilities are fully occupied. Suppose Dana is about to make some financial decision regarding the use of its manufacturing facilities for the coming year. The following are the costs of making part EC113, a key component of an emissions control system: Cost per unit 8.00 Manufacturing Costs for 65,000 units Direct material Direct labor Variable factory overhead (OH) Fixed factory overhead (OH) Total manufacturing costs 11.00 Total costs 520,000 715,000 585,000 195,000 2,015,000 9.00 3.00 31.00 Another manufacturer has offered to sell the same part to Dana for $28 each. The fixed factory overhead consists of f depreciation, property taxes, insurance, and supervisory salaries. $100,000 out of $195,000 fixed factory overhead costs would continue, which is unavoidable, even if Dana bought the component. Requirement 1. Assume that the capacity now used to make parts will become Idle if the parts are purchased. Construct the decision model with the relevant cost Information to compare Make case and Buy case Make Buy Relevant (Avoidable) costs Total per Unit Total per Unit Requirement 2. Assume that the capacity now used to make parts will either (a) be rented to a nearby manufacturer for the rental Income of $35,000 for the year but Dana corp. should pay a brokerage fee of $15,000 for the deal, or (b) be used to make all filters that will yield a profit contribution of $20,000 but require $10,000 additional fixed costs. Construct Incremental Analysis Model with the relevant information in the box. Buy and Use Incremetal Analysis. Make Buy and Leave Buy and Rent out facilities idle Facilities facilities for oil filters Requirement 3. Which operational decision among the four alternatives should you as a manager make? Why
