PLEASE SHOW ME THE FORMULAS ON EXCEL NOT JUST THE SOLUTIONS! TAKE YOUR TIME WITH IT I RATHER HAVE IT RIGHT AND IT TAKES LONGER THAN 30 MIN!

Possible Cost Drivers
Month	1	2	3	4	5	6	7	8	9	10	11	12
Cost Drivers:
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	62,000	75,000	70,000	72,580	85,000	76,000	58,900	64,300	90,000	54,000	60,500	98,000
Units Sold	35,000	37,500	38,000	34,000	58,000	55,000	29,000	33,000	36,000	59,000	56,000	50,000
ACTUAL MONTHLY COSTS (USD)
Cost of Goods Sold	980,000	1,050,000	1,064,000	952,000	1,624,000	1,540,000	812,000	924,000	1,008,000	1,652,000	1,568,000	1,400,000
Advertising	153,750	154,375	154,500	153,500	159,500	158,750	152,250	153,250	154,000	159,750	159,000	157,500
Selling & Commissions	208,750	219,375	221,500	204,500	306,500	293,750	183,250	200,250	213,000	310,750	298,000	272,500
Admin. Costs	1,750,500	2,075,500	1,950,500	2,015,000	2,325,500	2,100,500	1,673,000	1,808,000	2,450,500	1,550,500	1,713,000	2,650,500
Packaging	237,500	251,250	254,000	232,000	364,000	347,500	204,500	226,500	243,000	369,500	353,000	320,000
Shipping costs	871,250	862,500	915,000	783,750	1,090,000	1,021,575	1,111,000	435,500	960,500	1,842,500	1,499,500	750,500

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: 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 Excel's RSQ function to determine what the cost driver is for variable component of those costs that are variable or mixed. So, you will have to run the RSQ function three times for each of the costs. B. After 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 using that cost driver. C. The Coco Lamela Company wants to estimate profitability for the upcoming month. The following estimates are to be used: unit sales: 70,500; total labor hours worked: 93,000 hours; distance shipped: 322,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. Delete the Traditional Income Statement from this tab's worksheet - you will only need the Contribution Format version for Requirement D. Next, for those mixed or variable costs that did not have Units Sold as their cost driver 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 Break Even Point in units. Make sure that this cell is labeled and obvious. Finally, update the Contribution Format Income Statement in this worksheet using your BEP units as the variable cost driver. If done correctly the Net Operating Income should be zero. You must use Excel for this case. All non-input numerical cells will either reference another cell or be a calculated (formula) cell. Format all the cells as "accounting" and the initial sales amount on the income statements as a "currency". Any variable cost elements that you discover should be displayed to two decimal places, and any fixed cost element is to be shown as a whole number. The amounts in the income statements shall be displayed in whole numbers. The purpose of this case is to reinforce the concept of cost behavior, and to give you some Excel experience. - Remember that readability and neatness count when you create your solution. - Keep the Requirements A,B,C, and the data from this document's second page a single spreadsheet, and then duplicate that spreadsheet for Requirement D. - Make the area that contains the input data obvious and easy to locate. You may find that using named ranges in your data section for the cost drivers and various costs will be helpful. - This assignment is worth fifteen points. - While others may give you verbal assistance, this must be your own work - Make sure to disclose who you helped, who helped you, or that you solved this completely alone