COCO LAMELA COMPANY The Coco Lamela Company (CLC) owns the global rights to sell a very fragile glass ornament called Jack Wilsheres Tears. The cost of goods sold for the ornaments are shown on the second page. CLC sets its selling prices so that each unit sold returns a seventy-five percent gross margin. Data regarding product costs, and administration and selling expenses for the past twelve months have been accumulated in order develop a budget estimate for an upcoming month. Coco Lamelas Marketing Department has reviewed this data and could not reach a conclusion as to what is the most appropriate independent variable for predicting variable and mixed costs. These costs may be dependent on labor hours, unit sales, or distance shipped Marketing just doesnt know since they never had to take this accounting course. It is up to you to figure this out.

REQUIRED: Note: Please have the data from the second page of this assignment and your work for Requirements A-C on a single worksheet.

A. Get the information from the second page into your Excel workbook. Analyze the data to determine the behavior of each cost. The variable and mixed costs may be dependent on labor hours, unit sales, or distance shipped. Use Excels RSQ function to determine what is the cost driver for variable component of those costs that are variable or mixed. B. Once the cost driver is determined for each of the mixed or variable costs you will need to determine the cost elements using the Excel SLOPE and INTERCEPT functions.

C. The Coco Lamela Company wants to estimate profitability for the upcoming month. The following estimates are to be used: unit sales: 67,000; total labor hours worked: 97,000 hours; distance shipped: 347,000 miles. First, prepare a Contribution Format Income Statement for the upcoming month. Next, construct a Traditional Income Statement for the upcoming month. Make certain to present each cost/expense item separately on its own line do not aggregate the costs. Regarding the selling price; this should first be calculated on a per unit basis, then use the ROUND function to limit this amount to a whole dollar amount. D. For this portion of the assignment you must first duplicate the worksheet for Requirement C. Name this new tab Requirement D. Your goal in this part of the assignment is to determine the Break-Even-Point for the company and then prove it with a contribution format income statement.

Next, delete the Traditional Income Statement from this tabs worksheet you will only need the Contribution Format version. Then, for those variable and mixed costs that did not have Units Sold as their cost drive recalculate as if they did use Units as the Independent Variable (cost driver) and determine the fixed and variable cost components. The next step is to determine the CM and then calculate the BEP in units. Make sure that this cell is labeled and obvious. Finally, update the Contribution Format Income Statement in this worksheet using only Units sold as the variable cost driver. If done correctly the Net Operating Income should be zero.