Prepare the following problem as a contribution margin income statement in Excel using
spreadsheet formulas to calculate the elements of the income statement. The Excel file must
show your calculations for the requirements of the problem in the order asked. Put your name in
the upper left-hand corner of the spreadsheet. Submit your file to DropBox by April 15.
Background: Text chapters 1-3 and 5 present manufacturing cost classifications. Processing
costs are manufacturing costs for a raw material that is processed into predictable kinds and
amounts of end products. An example is chicken processing. One chicken will yield 2 legs, 2
wings, etc. One chicken will not yield 4 legs, however desirable or profitable that might be. Raw
materials such as metal ores or oil seeds have similarly stable and predictable yields.
Processing costs, as well as manufacturing costs, have three component costs: raw or direct
materials (DM), direct labor (DL), and processing or manufacturing overhead (MOH).
Conversion costs (CC) include DL and MOH. Because of waste or processing losses, the total
quantity (tons) sold will be less than the total quantity (tons) processed.
Finally, in a contribution margin income statement, costs are segregated as total variable
(regardless of function) and total fixed. See chapter 3 for many examples.
Project: Memphis Cottonseed Company has engaged you to assist in the development of
information to be used for managerial decisions. The company has the capacity to process 20,000
tons of cottonseed per year. The yield of a ton of cottonseed is as follows:
Table 1: Revenues
Product Average Yield Per Ton
of Cottonseed (in pounds)
Oil
300
Meal
600
Hulls
800
Lint
100
Waste
200<\/p>\n
Average Selling Price
per Trade Unit
$0.55 per pound
$225.00 per ton
$210.00 per ton
$10.00 per cwt<\/p>\n
Conversion factors: 1 ton = 2,000 lbs. Cwt = 100 lbs.<\/p>\n
A special marketing study revealed that the company can expect to sell its entire output for the
coming 2013 year at the listed average selling prices. You have determined the companys costs
to be as follows:
Table 2: Annual Cottonseed Costs
Processing Costs:
Variable
$27.00 per ton of cottonseed put in process
Fixed
$308,000 per year
Marketing Costs:
100% variable
$60.00 per ton sold
Administrative Costs:
100% fixed
$270,000 per year<\/p>\n
2
From the preceding information you need to submit to management a detailed report on the
companys breakeven point. In view of the conditions in the cottonseed market, management
told you that it would also like to know the average maximum amount that the company can
afford to pay for a ton of cottonseed.
Required:
1. (CVP Analysis and Target Costing). Assume that the revenues in Table 1 are the annual
breakeven sales dollars. At the breakeven point, what is the highest amount that Memphis
can pay per ton of raw cottonseed? Show your answer and calculations in an Excel
spreadsheet with formulas for a contribution margin income statement.
2. (CVP Analysis and Target Costing). The stockholders consider the minimum satisfactory
return on their equity investment (ROE = Pretax net income\/ average equity) in the
business to be 20% before corporate income taxes. The stockholders equity in the
company is $1,500,000. Compute the average amount that the company can pay for a ton
of cottonseed to realize the minimum satisfactory return on the stockholders investment
in the company. Show your answer and calculations in an Excel spreadsheet with
formulas for a contribution margin income statement.
3. (Process Costing). As a part of your financial projections for 2013, you need to estimate
the value of Work-in-Process inventory at December 31, 2013. Use the data in Table 3
below to show the total inventory valuation for products and byproducts of the cottonseed
process. Calculate equivalent units (EU) of DM and CC for each product, the ending
inventory value for each product, and the combined inventory value for all five items.
Show this in a spreadsheet with formulas.
Table 3: Ending Work in Process Inventory
Meal
Hulls
Ending Inventory (Lbs.)
36,000
48,000
DM % complete
100%
100%
CC % complete
95%
95%
DM unit cost per EU
$0.123
$0.123
CC unit cost per EU
$0.008
$0.002<\/p>\n
Lint
6,000
100%
95%
$0.123
$0.002<\/p>\n
Oil
18,000
100%
35%
$0.123
$0.019<\/p>\n
*Processing produces waste or Soapstock. Although it has no sale value, it does have a cost.<\/p>\n
Waste*
12,000
100%
50%
$0.123
$0.011<\/p>\n","protected":false},"excerpt":{"rendered":"
Prepare the following problem as a contribution margin income statement in Excel using spreadsheet formulas to calculate the elements of the income statement. The Excel file must show your calculations for the requirements of the problem in the order asked. Read More …<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-181326","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/181326","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/comments?post=181326"}],"version-history":[{"count":0,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/181326\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/media?parent=181326"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/categories?post=181326"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/tags?post=181326"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}