The Coco Lamela Company (CLC) owns the global rights to sell a very fragile glass ornament called "Jack Wilshere's 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 Lamela's 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 doesn't know since they never had to take this accounting course. It is up to you to figure this out. REQUIRED: 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 Excel's 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 pro forma 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. Month 1 2 3 5 6 7 8 9 10 11 12 Activities: Distance Shipped 425,000 420,000 450,000 375,000 550,000 510,900 562,000 176,000 476,000 980,000 784,000 356,000 Labor Hours 65,000 25,000 70,000 72,580 85,000 76,000 58,900 84,300 90,000 54,000 62,000 98,000 Units Sold 34,000 35,000 1 37,500 2 38,000 3 58,000 5 55,000 6 29,000 7 33,000 8 36,000 9 59,000 10 56,000 11 50,000 12 MONTHLY COSTS in USD Cost of Goods 945,000 1,012,500 1,026,000 918,000 1,566,000 1,485,000 783,000 891,000 972,000 1,593,000 1,512,000 1,350,000 Advertising Expenses 154,850 154,500 154,332 154,080 157,380 156,600 153,480 154,630 154,077 157,490 156,720 155,859 Selling & Commissions 185,000 195,000 202,400 186,000 283,000 265,000 166,000 182,000 177,000 245,000 273,000 257,000 Admin. Expenses 1,450,000 570,000 1,560,000 1,616,760 1,890,000 1,692,000 1,315,800 1,874,600 2,000,000 1,208,000 1,384,000 2,176,000 Packaging Expenses 230,000 250,000 260,000 220,000 340,000 330,000 200,000 210,000 240,000 380,000 350,000 290,000 Shipping Expenses 450.000 425.000 445.000 370.000 550.000 512.900 567.000 198.000 477.500 981.450 790.010 358.000 The Coco Lamela Company (CLC) owns the global rights to sell a very fragile glass ornament called "Jack Wilshere's 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 Lamela's 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 doesn't know since they never had to take this accounting course. It is up to you to figure this out. REQUIRED: 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 Excel's 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 pro forma 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. Month 1 2 3 5 6 7 8 9 10 11 12 Activities: Distance Shipped 425,000 420,000 450,000 375,000 550,000 510,900 562,000 176,000 476,000 980,000 784,000 356,000 Labor Hours 65,000 25,000 70,000 72,580 85,000 76,000 58,900 84,300 90,000 54,000 62,000 98,000 Units Sold 34,000 35,000 1 37,500 2 38,000 3 58,000 5 55,000 6 29,000 7 33,000 8 36,000 9 59,000 10 56,000 11 50,000 12 MONTHLY COSTS in USD Cost of Goods 945,000 1,012,500 1,026,000 918,000 1,566,000 1,485,000 783,000 891,000 972,000 1,593,000 1,512,000 1,350,000 Advertising Expenses 154,850 154,500 154,332 154,080 157,380 156,600 153,480 154,630 154,077 157,490 156,720 155,859 Selling & Commissions 185,000 195,000 202,400 186,000 283,000 265,000 166,000 182,000 177,000 245,000 273,000 257,000 Admin. Expenses 1,450,000 570,000 1,560,000 1,616,760 1,890,000 1,692,000 1,315,800 1,874,600 2,000,000 1,208,000 1,384,000 2,176,000 Packaging Expenses 230,000 250,000 260,000 220,000 340,000 330,000 200,000 210,000 240,000 380,000 350,000 290,000 Shipping Expenses 450.000 425.000 445.000 370.000 550.000 512.900 567.000 198.000 477.500 981.450 790.010 358.000