Did I do traditional income statement right?

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 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 that each unit sold returns a sixty-five percent gross margin. Data regarding product 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 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. . 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 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. 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. While the Coco Lamela Company is based in the USA, and conducts business in dollars (USD), its two principle investors, Lewis & Levy, are based in England. To make the affairs of D. the company relatable to its investors Coco Lamela presents its financial information both in USD and British pounds (GBP). Copy and paste the conversion factor from: http://www.xe.com/currencytables/ Use the October 7, 2019 close rate. Once you have pasted the factor into your spreadsheet use the ROUND function Using the pro forma income statements that you constructed for requirement C as your input, recreate those statements in British pounds (GBP) limit the value to the fourth decimal place. You must use Excel for this case. All non-input 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 is to be displayed to three 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. Whether you use a single spreadsheet or multiple sheets is up to you. Define the area that will contain the input data that is given in items C and D. Make this area obvious and easy to locate. Use named ranges in your data section for the cost drivers and various costs whenever possible. This assignment is worth twenty-five 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 thatyou solved this completely alone Month 1 2 3 4 5 6 7 9 10 11 12 Possible Cost Drivers: Distance Shipped (miles) 425,000 420,000 375,000 550,000 562,000 476,000 784,000 450,000 510,900 176,000 980,000 356,000 Weight Shipped (pounds) 25,000 72,580 62,000 56,000 65,000 70,000 85,000 76,000 58,900 84,300 90,000 54,000 98,000 55,000 Units Sold 38,000 29,000 33,000 35,000 37,500 34,000 58,000 36,000 59,000 50,000 COSTS (all in USD) Cost of Goods 918,000 1,566,000 783,000 972,000 154,077 1,593,000 1,512,000 945,000 1,012,500 1,026,000 1,485,000 891,000 1,350,000 154,080 153,480 Advertising 154,630 157,490 155,859 154,850 154,500 154,332 157,380 156,600 156,720 Selling & Commissions 185,000 195,000 186,000 265,000 166,000 177,000 245,000 202,400 283,000 182,000 273,000 257,000 Admin. Salaries 120,000 130,000 120,000 120,000 120,000 120,000 120,000 130,000 130,000 130,000 130,000 130,000 200,000 Packaging 220,000 340,000 350,000 230,000 250,000 260,000 330,000 210,000 240,000 380,000 290,000 Shipping costs 214,300 230,400 279,950 203,033 291,100 223,340 308,860 95,250 234,313 251,265 259,171 209,380 Unit Sales: 67,000 Weight Shipped: Distance shipped: 97,000 347,000 Packaging Advertising Salaries Shipping Costs COGS Commisions Units 1 0.9 0.9 0.0 1.0 Miles 0.4 0.4 0.2 0.1 0.6 Pounds 0.0 0.0 0.0 0.1 0.0 Salaries Advertising Packaging 0.02 $ COGS Commisions $ 27.00 $ $ 7.79 $ 150,058.18 $ 58,794.98 $ 3.67 $ 5.34 $ 1.16 $ Variable 44.98 124,139.73 $ 43,275.64 $ 183,034.81 $ 559,303.34 Fixed |Selling Pric $ 559,303 Pro Forma Contribution Income Statement Revenue (A) Variable Costs (B) $ 2,936,002.16 Less: Total contribution (A)-(B) Fixed Costs Less: $150,058.18 $ Advertising Selling & Commisions 58,794.98 $ 124,139.73 $ Admin Salaries Packaging 43,275.64 $183,034.81 Shipping costs $559,303.34 $ 2,376,698.83 Net Income Traditional Income Statement $ 2,936,002.16 $ Revenue COGS $ 2,936,002.16 Gross Margin Less: |Selling Expenses $ $ 58,794.98 Administrative Expenses 124,139.73 Net Operating Income $ 2,753,067.45 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 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 that each unit sold returns a sixty-five percent gross margin. Data regarding product 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 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. . 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 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. 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. While the Coco Lamela Company is based in the USA, and conducts business in dollars (USD), its two principle investors, Lewis & Levy, are based in England. To make the affairs of D. the company relatable to its investors Coco Lamela presents its financial information both in USD and British pounds (GBP). Copy and paste the conversion factor from: http://www.xe.com/currencytables/ Use the October 7, 2019 close rate. Once you have pasted the factor into your spreadsheet use the ROUND function Using the pro forma income statements that you constructed for requirement C as your input, recreate those statements in British pounds (GBP) limit the value to the fourth decimal place. You must use Excel for this case. All non-input 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 is to be displayed to three 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. Whether you use a single spreadsheet or multiple sheets is up to you. Define the area that will contain the input data that is given in items C and D. Make this area obvious and easy to locate. Use named ranges in your data section for the cost drivers and various costs whenever possible. This assignment is worth twenty-five 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 thatyou solved this completely alone Month 1 2 3 4 5 6 7 9 10 11 12 Possible Cost Drivers: Distance Shipped (miles) 425,000 420,000 375,000 550,000 562,000 476,000 784,000 450,000 510,900 176,000 980,000 356,000 Weight Shipped (pounds) 25,000 72,580 62,000 56,000 65,000 70,000 85,000 76,000 58,900 84,300 90,000 54,000 98,000 55,000 Units Sold 38,000 29,000 33,000 35,000 37,500 34,000 58,000 36,000 59,000 50,000 COSTS (all in USD) Cost of Goods 918,000 1,566,000 783,000 972,000 154,077 1,593,000 1,512,000 945,000 1,012,500 1,026,000 1,485,000 891,000 1,350,000 154,080 153,480 Advertising 154,630 157,490 155,859 154,850 154,500 154,332 157,380 156,600 156,720 Selling & Commissions 185,000 195,000 186,000 265,000 166,000 177,000 245,000 202,400 283,000 182,000 273,000 257,000 Admin. Salaries 120,000 130,000 120,000 120,000 120,000 120,000 120,000 130,000 130,000 130,000 130,000 130,000 200,000 Packaging 220,000 340,000 350,000 230,000 250,000 260,000 330,000 210,000 240,000 380,000 290,000 Shipping costs 214,300 230,400 279,950 203,033 291,100 223,340 308,860 95,250 234,313 251,265 259,171 209,380 Unit Sales: 67,000 Weight Shipped: Distance shipped: 97,000 347,000 Packaging Advertising Salaries Shipping Costs COGS Commisions Units 1 0.9 0.9 0.0 1.0 Miles 0.4 0.4 0.2 0.1 0.6 Pounds 0.0 0.0 0.0 0.1 0.0 Salaries Advertising Packaging 0.02 $ COGS Commisions $ 27.00 $ $ 7.79 $ 150,058.18 $ 58,794.98 $ 3.67 $ 5.34 $ 1.16 $ Variable 44.98 124,139.73 $ 43,275.64 $ 183,034.81 $ 559,303.34 Fixed |Selling Pric $ 559,303 Pro Forma Contribution Income Statement Revenue (A) Variable Costs (B) $ 2,936,002.16 Less: Total contribution (A)-(B) Fixed Costs Less: $150,058.18 $ Advertising Selling & Commisions 58,794.98 $ 124,139.73 $ Admin Salaries Packaging 43,275.64 $183,034.81 Shipping costs $559,303.34 $ 2,376,698.83 Net Income Traditional Income Statement $ 2,936,002.16 $ Revenue COGS $ 2,936,002.16 Gross Margin Less: |Selling Expenses $ $ 58,794.98 Administrative Expenses 124,139.73 Net Operating Income $ 2,753,067.45