Easy Transfer Pricing Excel Exercise-Accounting

Please see attached,

Need help filling in the missing cell values with formulas & explanations as to how to arrive at the calculations, submit conclusions back within this xls file.

• Metals Division, total contribution margin:$28,800,000
• Outlay cost: $156

XXXX has several divisions. However, only two divisions transfer products to other divisions. The Mining Division refines toldine, which is then transferred to the Metals Division. The toldine is processed into an alloy by the Metals Division, and the alloy is sold to customers at a price of $450 per unit. The Mining Division is currently required by XXXX to transfer its total yearly output of 400,000 units of toldine to the Metals Division at total actual manufacturing cost plus 10 percent. Unlimited quantities of toldine can be purchased and sold on the open market at $270 per unit. While the Mining Division could sell all the toldine it produces at $270 per unit on the open market, it would incur a variable selling cost of $15 per unit. Brian Jones, manager of the Mining Division, is unhappy with having to transfer the entire output of toldine to the Metals Division at 110 percent of cost. In a meeting with the management of XXXX, he said, "Why should my division be required to sell toldine to the Metals Division at less than market price? For the year just ended in May, Metals' contribution margin was over $57 million on sales of 400,000 units, while Mining's contribution was just over $15 million on the transfer of the same number of units. My division is subsidizing the profitability of the Metals Division. We should be allowed to charge the market price for toldine when transferring to the Metals Division."
The following table shows the detailed unit cost structure for both the Mining and Metals divisions.

1. Explain why transfer prices based on total actual costs are not appropriate as the basis for divisional performance measurement.

2. Using the market price as the transfer price, determine the contribution margin for both the Mining Division and the Metals Division.

3. If XXXX Company were to institute the use of negotiated transfer prices and allow divisions to buy and sell on the open market, determine the price range for toldine that would be acceptable to both the Mining Division and the Metals Division. Explain your answer.

4. Use the general transfer-pricing rule to compute the lowest transfer price that would be acceptable to the Mining Division. Is your answer consistent with your conclusion in requirement (3)? Explain.

5. Identify which one of the three types of transfer prices (cost-based, market-based, or negotiated) is most likely to elicit desirable management behavior at XXXX. Explain your answer.