JUST PART C I have done A and B and all need it the excel equations. Thanks!

2 3 4 5 6 8 9 10 11 12 Month Possible Cost Drivers: Distance Shipped (miles) 27 07:021 Weight Shipped (pounds) 65,000 Units Sold 35,000 420,000 25,000 37,500 450,000 70,000 38,000 375,000 72,580 34,000 550,000 85,000 58,000 510,900 76,000 55,000 562,000 58,900 29,000 176,000 84,300 33,000 476,000 90,000 36,000 980,000 54,000 59,000 784,000 62,000 56,000 356,000 98,000 50,000 COSTS (all in USD) Cost of Goods Advertising Selling & Commissions | Admin. Salaries Packaging Shipping costs 945,000 154,8501 185,000 120,000 230,000 214,300 1,012,500 1,026,000| 918,000 1,566,000 154,500 154,332 154,080| 157,380 195,000 202,400 186,000 283,000| 120,000 120,000 120,000 120,000 250,000 260,000 220,000 340,000 95,250 230,400 234,313 279,950 1,485,000 156,600 265,000 120,000 330,000 251,265 783,000 153,480 166,000 130,000 200,000 203,033 891,000| 972,000 1,593,000 1,512,000 1,350,000 154,630 154,077 157,490 156,720 155,859 182,000 177,000 245,000 273,000| 257,000, 130,000 130,000 130,000 130,000 130,000 210,000 240,000 380,000 350,000 290,000 259,171 291,100 209,380 223,340 308,860 R2 Distance R2 Weight R2 Units 0.390) 0.013 1.000 0.354 0.0101 0.947 0.2011 0.040 0.922 0.052 0.058 0.002 0.548 0.000 0.963 0.036 0.969 0.056 Slope Intercept 27.000 0.00 0.122 150058.18 3.671 58794.98 0.006 124139.73 5.342 | 43275.64 2.785 38206.53 Cost Formula y=27x y=150058.2 + 0.112x y=58794.98 +3.671x y=121782 +0.006x y=43275.64 + 5.342x y=38206.53 +2.785x 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 sixty-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 shipping weight, 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. You will also need to use Excel's RSQ function to determine what is the cost driver for those costs that are variable and/or mixed. These costs may be dependent on shipping weight, unit sales, or distance shipped. B. Once the cost driver is determined for each of the mixed or variable costs you will need to develop the cost formula using the Excel SLOPE and INTERCEPT functions. It can be included in same area you used for requirement "A". Regarding 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. C. The Coco Lamela Company wants to estimate profitability for the upcoming month. The following estimates are to be used: unit sales: 67,000; weight shipped: 97,000 pounds; 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. 2 3 4 5 6 8 9 10 11 12 Month Possible Cost Drivers: Distance Shipped (miles) 27 07:021 Weight Shipped (pounds) 65,000 Units Sold 35,000 420,000 25,000 37,500 450,000 70,000 38,000 375,000 72,580 34,000 550,000 85,000 58,000 510,900 76,000 55,000 562,000 58,900 29,000 176,000 84,300 33,000 476,000 90,000 36,000 980,000 54,000 59,000 784,000 62,000 56,000 356,000 98,000 50,000 COSTS (all in USD) Cost of Goods Advertising Selling & Commissions | Admin. Salaries Packaging Shipping costs 945,000 154,8501 185,000 120,000 230,000 214,300 1,012,500 1,026,000| 918,000 1,566,000 154,500 154,332 154,080| 157,380 195,000 202,400 186,000 283,000| 120,000 120,000 120,000 120,000 250,000 260,000 220,000 340,000 95,250 230,400 234,313 279,950 1,485,000 156,600 265,000 120,000 330,000 251,265 783,000 153,480 166,000 130,000 200,000 203,033 891,000| 972,000 1,593,000 1,512,000 1,350,000 154,630 154,077 157,490 156,720 155,859 182,000 177,000 245,000 273,000| 257,000, 130,000 130,000 130,000 130,000 130,000 210,000 240,000 380,000 350,000 290,000 259,171 291,100 209,380 223,340 308,860 R2 Distance R2 Weight R2 Units 0.390) 0.013 1.000 0.354 0.0101 0.947 0.2011 0.040 0.922 0.052 0.058 0.002 0.548 0.000 0.963 0.036 0.969 0.056 Slope Intercept 27.000 0.00 0.122 150058.18 3.671 58794.98 0.006 124139.73 5.342 | 43275.64 2.785 38206.53 Cost Formula y=27x y=150058.2 + 0.112x y=58794.98 +3.671x y=121782 +0.006x y=43275.64 + 5.342x y=38206.53 +2.785x 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 sixty-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 shipping weight, 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. You will also need to use Excel's RSQ function to determine what is the cost driver for those costs that are variable and/or mixed. These costs may be dependent on shipping weight, unit sales, or distance shipped. B. Once the cost driver is determined for each of the mixed or variable costs you will need to develop the cost formula using the Excel SLOPE and INTERCEPT functions. It can be included in same area you used for requirement "A". Regarding 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. C. The Coco Lamela Company wants to estimate profitability for the upcoming month. The following estimates are to be used: unit sales: 67,000; weight shipped: 97,000 pounds; 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