COCO LAMELA COMPANY Excel Assignment 3 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 seling prices so that each unit sold returns a sixty percent gross margin. Data regarding administration and selling expenses for the past t this data and could not reach a 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! welve months have been accumulated in order develop a budget estimate for an upcoming month. Coco Lamela's Marketing Department has reviewed as to what is the most appropriate independent variable for predicting variable and mixed costs. These costs may be dependent on shipping 2 50 A. Get the information from the second page into your Excel workbook. Analyze the data to determine the behavior of each cost. Specifically, you need to determine whether the costs Advertising, Commissions, Salaries, Pa of Goods Sold-are purely fixed, purely variable, or if they're mixed. 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. Shipping cost is parely variable with two cost drivers, and is based upon both miles and weight; Se per mile and $2.97 per pound 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" C. The Coco Lamela Company wants to estimate profitability for the upcoming month. The following estimates are to be used: unit sales: 77,000; weight shipped: 67,000 pounds; distance shipped: 647,000 miles. First, prepare a pro forma Con Then 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. Format Income Statement for the upcoming month. Regarding selling price, use the ROUND function to limit this amount to two decimal places. D. While the Coco Lamela Company is based in the USA, and conducts business in dollars (USD), ts two principle investors, Lewis &Levy, are Englisth. To make the affairs of the le to its investors Coco Lamela presents its financial information both in USD and British pounds (GBP). Copy and paste the conversion factor from Use the February 20, 2019 close rate Once you paste the factor into you Using the pro forma income you co r spreadsheet use the ROUND function to limit the value to the fourth decimal place. statements that you constructed for requirement C as your input, recreate those statements in British pounds (GBP) and the initial sales amount on the You must use Excel for this case. All non-input cells will either reference another cellor be a calculated (formula) cell Format all the cells as income statements as a 'currency. Any variable cost elements that you discover should be displayed to four decimal places. The purpose of this case is to reinforce behavior, and to give you some Excel experience. Remember that readability and neatness count when you create your This assignment is worth twenty-five poins While others may give you verbal assistance, this is to be your own work Make sure to disclose who you helped, who helped you, or that you solved this completely alone 5 Cost Drivers: Distance shipped (miles) 425.000 420,000 450,000 375.000 soooo s10,900 s62,000 176.000 476,000 9M,000 784,000 356,000 72S80 85.000 76,000 sa900 84300 90,000 54,000 62,000 9soo 35,000 37S0 38000 34,000 seoo s5,000 29,000 33,000 36,000 59,000 56,000 50000 Weight Shipped (pounds) 65,000 25,000 70,000 Units Sold Cost of Goods 945,000 1,012.500 1026,000 918,000 1.566,000 1.485,000 783,000 891,000 972,000 1.593,000 1.512,000 1,30,000 548S0 54.500 54.332 54,00 57,380 56,500 SA80 54630 54,077 57A90 56720 SSB59 Selling & Commissions 190,000 195,000 202,000 186,000 283,000 265,000 166,000 182.000 197,000 286,000 273,000 257,000 1200000 120.co 120000 120,000 120, 120,000 120,000 120000 120,000 Packaging Shipping costs 120,000 120,000 120,000 230,000 250,000 260,000 220,000 340,000 214.300 95.250 230,400 234,313 251,265 203,033 259,171 291,100 209,380 223,340 308-860 279.950 COCO LAMELA COMPANY Excel Assignment 3 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 seling prices so that each unit sold returns a sixty percent gross margin. Data regarding administration and selling expenses for the past t this data and could not reach a 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! welve months have been accumulated in order develop a budget estimate for an upcoming month. Coco Lamela's Marketing Department has reviewed as to what is the most appropriate independent variable for predicting variable and mixed costs. These costs may be dependent on shipping 2 50 A. Get the information from the second page into your Excel workbook. Analyze the data to determine the behavior of each cost. Specifically, you need to determine whether the costs Advertising, Commissions, Salaries, Pa of Goods Sold-are purely fixed, purely variable, or if they're mixed. 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. Shipping cost is parely variable with two cost drivers, and is based upon both miles and weight; Se per mile and $2.97 per pound 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" C. The Coco Lamela Company wants to estimate profitability for the upcoming month. The following estimates are to be used: unit sales: 77,000; weight shipped: 67,000 pounds; distance shipped: 647,000 miles. First, prepare a pro forma Con Then 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. Format Income Statement for the upcoming month. Regarding selling price, use the ROUND function to limit this amount to two decimal places. D. While the Coco Lamela Company is based in the USA, and conducts business in dollars (USD), ts two principle investors, Lewis &Levy, are Englisth. To make the affairs of the le to its investors Coco Lamela presents its financial information both in USD and British pounds (GBP). Copy and paste the conversion factor from Use the February 20, 2019 close rate Once you paste the factor into you Using the pro forma income you co r spreadsheet use the ROUND function to limit the value to the fourth decimal place. statements that you constructed for requirement C as your input, recreate those statements in British pounds (GBP) and the initial sales amount on the You must use Excel for this case. All non-input cells will either reference another cellor be a calculated (formula) cell Format all the cells as income statements as a 'currency. Any variable cost elements that you discover should be displayed to four decimal places. The purpose of this case is to reinforce behavior, and to give you some Excel experience. Remember that readability and neatness count when you create your This assignment is worth twenty-five poins While others may give you verbal assistance, this is to be your own work Make sure to disclose who you helped, who helped you, or that you solved this completely alone 5 Cost Drivers: Distance shipped (miles) 425.000 420,000 450,000 375.000 soooo s10,900 s62,000 176.000 476,000 9M,000 784,000 356,000 72S80 85.000 76,000 sa900 84300 90,000 54,000 62,000 9soo 35,000 37S0 38000 34,000 seoo s5,000 29,000 33,000 36,000 59,000 56,000 50000 Weight Shipped (pounds) 65,000 25,000 70,000 Units Sold Cost of Goods 945,000 1,012.500 1026,000 918,000 1.566,000 1.485,000 783,000 891,000 972,000 1.593,000 1.512,000 1,30,000 548S0 54.500 54.332 54,00 57,380 56,500 SA80 54630 54,077 57A90 56720 SSB59 Selling & Commissions 190,000 195,000 202,000 186,000 283,000 265,000 166,000 182.000 197,000 286,000 273,000 257,000 1200000 120.co 120000 120,000 120, 120,000 120,000 120000 120,000 Packaging Shipping costs 120,000 120,000 120,000 230,000 250,000 260,000 220,000 340,000 214.300 95.250 230,400 234,313 251,265 203,033 259,171 291,100 209,380 223,340 308-860 279.950