Share
Explore BrainMass

Total costs for each division and the sales price

As part of an effort to increase sales and profit margin. Music Makers' management is adopting a transfer pricing plan. The copying and packaging departments have been placed under divisional managers. As an incentive to cut costs, each division is expected to earn a gross margin of 10% on its divisional costs. The copying division will sell its output to the corporation. the company expects to produce and sell 535,000 units per month, and the maximum it is willing to pay to the packaging division for the units is $1,075,000.

Variable manufacturing costs per unit in the copying division include $.70 for materials, $.09 for labor, and $.04689 for overhead. Fixed costs in the copying division are $268,000 per month. Variable manufacturing costs per unit in the packaging division include $.20 for materials, $.11 for labor, and $.04 for overhead. Fixed costs in the packaging division are $76,000 per month.

Required:
Use a spreadsheet to determine the total costs for each division and the sales price each division will need to charge to earn a 10% gross margin. The sales price can be calculated as total cost / (1-profit margin).

Provide data for the copying division in column B and the packaging division in column C. Captains should appear in column A. Enter headings in row 1, the number of units to produce in row 2, and gross profit margin in row 3. In row 4, enter a heading "variable costs per unit." Then enter variable unit costs in row 8. Calculate total variable costs in row 9. Enter fixed cost in row 10. Calculate total manufacturing costs in row 11. Calculate the price including required margin in row 12. In the following rows, calculate the total price of the products transferred from the packaging division to the corporation and the amount under (over) the maximum transfer price of $1,075,000. Use cell references and formulas where possible. Use appropriate formatting throughout.

If you were manager of the copying division, what price would you want for the units sold to the packaging division? If you were manager of the packaging division, what price would you want to pay for the goods purchased from the copying division?

How would your answer change if the gross margin expected of each division were 8%?

Solution Summary

The solution explains the use of spreadsheet in calculating total costs for each division and the sales price

$2.19