70.2K

Verified Solution

Link Copied!

The Coco Lamda Company (CLC) owns the global rights to sell a very fragle glass ormament called "Jack Wilshere's Tears". The cost of goods sold for the ornaments are shown ca the second page CLC secs its selling prioes so that each unit sold returns a seventy--ive percent gross margin. Data regarding product costs, and administration and seling expenses for the past twelve months have been accurrulated in onder develop a budget estimute for an upoeming month. Coes lamela's Marketing Departinent has reviewod this data and could not reach a condhislon as to what ts the most appropriate independeat variable for predicting variable and mixed costa. These couts may be dependent on labor hours, unit sales, or dintance shipped Martiecting fust doesn't know since they never had to take this accounting course. It is up to you to figure this out. REQUTRE: Note: Please have the data from the second page of chts assignment and your work for Requirements A-Con a single warkshet. A. Get the information from the second pago into your Excol workbook Analyze the data to determine the behavlor ef cach cast The variable and miked costs may be dependent on labor hours, unit sales, er distance shipped. Use Excel's RSQ functien to determine what the cost drtver is for variable coeaponent of those costs that are varlable or mixed So, you will have to run the RSQ function three times for each of the costs. 1. After the cost drwer is determined for each of the mixed or varisble costs you will noed to determine the cost elements using the Bxcel SLOPE and INTERCET functions using that cost driver. c. The Coeo tamela Company wants to estimate prefitability for the upcoming month. The following estimates are to be used unit sales: 70,500, total labor hours worked: 93,000 bours distance shipped 322,000 miles. Frst, prepare a Coetribution Format lacome Statement for the upcoming month. Next, construct a Traditional tncome Satement for the upcomirg month. Make certain to presecit each cost/expense ifen separately on iss own line - do not apgregate the coats. Negarding the selling price this should finst be calculated on a per ueit basis, then use the ROUND function to limit this amount to a whole dollar amount. determise the Break Fves. Point for the company and then prove it with a conkribution formst inconte statement. did sot huve Units Sold as their cast driver recalculate as if they did use Units as sthe lndependent Variable (cost driver) and determine the floed and variable cost components. The neat this worksheet ising your HEP units as the variable cost driver. If dene correctly the Net Operating Income should be iero. Posible Cont Divers

Answer & Explanation Solved by verified expert

See Answer