Memphis Cottonseed Company

Prepare the following problem as a contribution margin income statement in Excel usingspreadsheet formulas to calculate the elements of the income statement. The Excel file mustshow your calculations for the requirements of the problem in the order asked. Put your name inthe 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. Processingcosts are manufacturing costs for a raw material that is processed into predictable kinds andamounts of end products. An example is chicken processing. One chicken will yield 2 legs, 2wings, etc. One chicken will not yield 4 legs, however desirable or profitable that might be. Rawmaterials 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 directmaterials (DM), direct labor (DL), and processing or manufacturing overhead (MOH).Conversion costs (CC) include DL and MOH. Because of waste or processing losses, the totalquantity (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 ofinformation to be used for managerial decisions. The company has the capacity to process 20,000tons of cottonseed per year. The yield of a ton of cottonseed is as follows:Table 1: RevenuesProduct Average Yield Per Tonof Cottonseed (in pounds)Oil300Meal600Hulls800Lint100Waste200Average Selling Priceper Trade Unit$0.55 per pound$225.00 per ton$210.00 per ton$10.00 per cwtConversion factors: 1 ton = 2,000 lbs. Cwt = 100 lbs.A special marketing study revealed that the company can expect to sell its entire output for thecoming 2013 year at the listed average selling prices. You have determined the companys coststo be as follows:Table 2: Annual Cottonseed CostsProcessing Costs:Variable$27.00 per ton of cottonseed put in processFixed$308,000 per yearMarketing Costs:100% variable$60.00 per ton soldAdministrative Costs:100% fixed$270,000 per year2From the preceding information you need to submit to management a detailed report on thecompanys breakeven point. In view of the conditions in the cottonseed market, managementtold you that it would also like to know the average maximum amount that the company canafford to pay for a ton of cottonseed.Required:1. (CVP Analysis and Target Costing). Assume that the revenues in Table 1 are the annualbreakeven sales dollars. At the breakeven point, what is the highest amount that Memphiscan pay per ton of raw cottonseed? Show your answer and calculations in an Excelspreadsheet with formulas for a contribution margin income statement.2. (CVP Analysis and Target Costing). The stockholders consider the minimum satisfactoryreturn on their equity investment (ROE = Pretax net income/ average equity) in thebusiness to be 20% before corporate income taxes. The stockholders equity in thecompany is $1,500,000. Compute the average amount that the company can pay for a tonof cottonseed to realize the minimum satisfactory return on the stockholders investmentin the company. Show your answer and calculations in an Excel spreadsheet withformulas for a contribution margin income statement.3. (Process Costing). As a part of your financial projections for 2013, you need to estimatethe value of Work-in-Process inventory at December 31, 2013. Use the data in Table 3below to show the total inventory valuation for products and byproducts of the cottonseedprocess. Calculate equivalent units (EU) of DM and CC for each product, the endinginventory 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 InventoryMealHullsEnding Inventory (Lbs.)36,00048,000DM % complete100%100%CC % complete95%95%DM unit cost per EU$0.123$0.123CC unit cost per EU$0.008$0.002Lint6,000100%95%$0.123$0.002Oil18,000100%35%$0.123$0.019*Processing produces waste or Soapstock. Although it has no sale value, it does have a cost.Waste*12,000100%50%$0.123$0.011Prepare the following problem as a contribution margin income statement in Excel usingspreadsheet formulas to calculate the elements of the income statement. The Excel file mustshow your calculations for the requirements of the problem in the order asked. Put your name inthe 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. Processingcosts are manufacturing costs for a raw material that is processed into predictable kinds andamounts of end products. An example is chicken processing. One chicken will yield 2 legs, 2wings, etc. One chicken will not yield 4 legs, however desirable or profitable that might be. Rawmaterials 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 directmaterials (DM), direct labor (DL), and processing or manufacturing overhead (MOH).Conversion costs (CC) include DL and MOH. Because of waste or processing losses, the totalquantity (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 ofinformation to be used for managerial decisions. The company has the capacity to process 20,000tons of cottonseed per year. The yield of a ton of cottonseed is as follows:Table 1: RevenuesProduct Average Yield Per Tonof Cottonseed (in pounds)Oil300Meal600Hulls800Lint100Waste200Average Selling Priceper Trade Unit$0.55 per pound$225.00 per ton$210.00 per ton$10.00 per cwtConversion factors: 1 ton = 2,000 lbs. Cwt = 100 lbs.A special marketing study revealed that the company can expect to sell its entire output for thecoming 2013 year at the listed average selling prices. You have determined the companys coststo be as follows:Table 2: Annual Cottonseed CostsProcessing Costs:Variable$27.00 per ton of cottonseed put in processFixed$308,000 per yearMarketing Costs:100% variable$60.00 per ton soldAdministrative Costs:100% fixed$270,000 per year2From the preceding information you need to submit to management a detailed report on thecompanys breakeven point. In view of the conditions in the cottonseed market, managementtold you that it would also like to know the average maximum amount that the company canafford to pay for a ton of cottonseed.Required:1. (CVP Analysis and Target Costing). Assume that the revenues in Table 1 are the annualbreakeven sales dollars. At the breakeven point, what is the highest amount that Memphiscan pay per ton of raw cottonseed? Show your answer and calculations in an Excelspreadsheet with formulas for a contribution margin income statement.2. (CVP Analysis and Target Costing). The stockholders consider the minimum satisfactoryreturn on their equity investment (ROE = Pretax net income/ average equity) in thebusiness to be 20% before corporate income taxes. The stockholders equity in thecompany is $1,500,000. Compute the average amount that the company can pay for a tonof cottonseed to realize the minimum satisfactory return on the stockholders investmentin the company. Show your answer and calculations in an Excel spreadsheet withformulas for a contribution margin income statement.3. (Process Costing). As a part of your financial projections for 2013, you need to estimatethe value of Work-in-Process inventory at December 31, 2013. Use the data in Table 3below to show the total inventory valuation for products and byproducts of the cottonseedprocess. Calculate equivalent units (EU) of DM and CC for each product, the endinginventory 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 InventoryMealHullsEnding Inventory (Lbs.)36,00048,000DM % complete100%100%CC % complete95%95%DM unit cost per EU$0.123$0.123CC unit cost per EU$0.008$0.002Lint6,000100%95%$0.123$0.002Oil18,000100%35%$0.123$0.019*Processing produces waste or Soapstock. Although it has no sale value, it does have a cost.Waste*12,000100%50%$0.123$0.011