can you explain how to make the table its asking me too on B. its telling me to make it similar to ex.9.9 which I posted a picture of. I'm posting additional info the prof gave us just to make sure you have everything

heres additional information just in case you need the previous table it was based off of.

Cost Allocation and Environmental Processes-Ethical Issues California Circuits Company (3C) manufactures a variety of components. Its Valley plant specializes in two electronic components used in circuit boards. These components serve the same function and perform equally well. The difference in the two products is the raw material. The XLD chip is the older of the two components and is made with a metal that requires a wash prior to assembly. Originally, the plant released the wastewater directly into a local river. Several years ago, the company was ordered to treat the wastewater before its release, and it installed relatively expensive equipment. While the equipment is fully deprecated, annual operating expenses of $250,000 are still incurred for wastewater treatment Two years ago, company scientists developed an alloy with all of the properties of the raw materials used in XL-D that generates no wastewater. Some prototype components using the new material were produced and tested and found to be indistinguishable from the old components in every way relating to their fitness for use. The only difference is that the new alloy is more expensive than the old raw material. The company has been test marketing the newer version of the component, referred to as XL-C, and is currently trying to decide its fate. Manufacturing of both components begins in the Production Department and is completed in the Assembly Department. No other products are produced in the plant. The following provides Information for the two components: XL-D XL-C Units produced 100,000 25,000 Direct material costs per unit $12 $14 Direct Labor Information Direct labor hours per unit --Production Direct labor-hours per unit--Assembly Direct labor rate per hour all labor $20 Other Activity Information Machine hours per unit-Production Machine-hours per unit-Assembly Testing hours per unit Shipping weight per unit (pounds) Wastewater generated per unit (gallons) 0.1 0.1 0.4 0.4 $20 1.6 0.4 3.0 1.0 10.0 1.6 0.4 3.0 1.6 0.0 Annual overhead costs for the two departments follow: Production Department Assembly Department $1,050,000 $500,000 The company president believes that it's foolish to continue producing two essentially equivalent products. At the same time, the corporate image is somewhat tarnished because of a toxic dump found at another site (not the Valley plant). The president would like to be able to point to the Valley plant as an example of company research and development (R&D) working to provide an environmentally friendly product. The controller points out to the president that the company's financial position is shaky, and it cannot afford to make products in any way other than the most cost-efficient one. Required: A. 3C's current cost accounting system charges overhead to products based on direct labor cost using a single plantwide rate. What per unit product costs (material, labor, and overhead) will It report for the two products if the current allocation system is used? Use the tab "Requirement A" in the Excel template to answer this question. Set up a chart similar to Exhibit 9.7 in the textbook (see page 358) to show your answer (adjust the names of the parts of the chart in the textbook to fit with the details of the project). Do not round intermediate calculations, which means you need to use cell referencing for calculations. B. The controller recently completed an executive education course describing the two-stage allocation procedure. Assume that the first stage allocates costs to departments and the second stage allocates costs to products. The controller believes that the costs will be more accurate if machine-hours are used to allocate Production Department costs and direct labor-hours are used to allocate Assembly Department costs. What per unit product costs will be reported for the two products if the two-stage allocation process is used? Use the tab "Requirement B* in the Excel template to answer this question. Set up a chart similar to Exhibit 9.9 in the textbook (see page 359) to show your answer. Do not round intermediate calculations, which means you need to use cell referencing for calculations. Budgeted overhead $810,000 $1,350,000 = 60% of direct labor cost Packaging: Budgeted direct labor cost Based on these overhead rates, Nancy calculated the revised product costs (see Q Exhibit 9.9). For the Sport, the Assembly costs per unit were $2.70 [($45 x 6,000 MH)/ 100,000 units). Note that the 6,000 machine-hours (MH) were shown in Exhibit 9.6, row 5. Also for the Sport, the Packaging costs per unit were $5.94 (= 60% * $9.90), where the $9.90 is the Packaging direct labor cost shown in Exhibit 9.9, row 7. We can use the same methods to find the unit overhead costs for the Pro. Exhibit 9.9 Third Quarter Unit Cost Report-CenterPoint Manufacturing Facility A B 1 Sport Pro 2 Units produced 100,000 40,000 3 4 Direct material $ 15.00 $ 60.00 3 Direct labor 6 Assembly $ 7.50 $ 15.00 7 Packaging 9.00 9.00 8 Total direct labor s 17:40 S 24.00 9 Direct costs S 32.40 S 841.00 10 Applied overhead 11 Assembly ( 545 per machine-hour) $ 2.70 $ 33.75 12 Packaging (609% of packaging direct labor cost) 5.04 5.40 13 Total overhead $ 864 $19.15 14 Unit costs 5 41.04 5123:15 Two-Stage Cost Allocation and the Choice of Cost Drivers Let's now consider IVC, Inc. At its CenterPoint manufacturing facility, it makes two models of drones used primarily by hobbyists. The Sport Model is a basic drone with a simple camera and few high-end features. It is often sold in large quantities to discount stores, sporting goods stores, and other outlets that appeal to budget buyers. The Pro Model is a higher end drone with a better camera and more sophisticated navigational capabilities. It is sold mostly online and at some hobby stores CenterPoint's production managers had a conference call with the marketing managers in Los Angeles. Company policies called for initial price guidelines for products to include a 30 percent markup over full cost. The marketing managers complained that, although the Pro was selling very well even with prices quoted above the guidelines, the Sport was meeting very houvy price resistance and was being discounted well below the guidelines to well. The marketing managers had arranged the call to determine why IVC's costs on the cheaper drone were apparently so much higher than its competitors Production at the CenterPoint facility takes place in two buildings, Assembly and Packaging The drone production process consists of delivering materials (plastic, rotors, cameras, and so on) to the assembly line, assembling drones by employees working in production cells, and then transferring them to the Packaging building. Only one drone model at a time is produced, and the machines have to be recalibrated and tested before beginning the other model. The Packaging Department tests the drones, packages them, and then ships them to customers Sce Exhibit 9.6 for data on the operations of the CenterPoint facility for the third quarter. The facility's cost system is a traditional product costing system that allocates manufacturing overhead to products based on direct labor costs. Page 358 Exhibit 9.6 Third Quarte Production and Contato Center Paint Manufacturing Facility Exhibit 9.6 Third Quarter Production and Cost Data-CenterPoint Manufacturing Facility A B D 1 Sport Pro Total 2 3 Number of units 100,000 40,000 140.000 4 5 Machine-hours-Assembly 6,000 30,000 36.000 6 7 Direct materials $1,500,000 $ 2,400,000 $3,900,000 8 Direct labor-Assembly $ 750,000 $ 600,000 $ 1.350,000 9 Direct labor--Packaging 990,000 360,000 1.350,000 10 Direct labor-Total $ 1,740,000 $ 960,000 $2.700.000 11 Total direct cost $3.240.000 $ 3,360,000 $6,600,000 12 13 Overhead costs 14 Assembly building $ 1.620,000 15 Packaging building 810.000 16 Total overhead $ 2.430.000 17 Total costs $ 9,030,000 The overhead allocation rate at the Center Point facility is 90 percent (- $2.430,000 $2,700,000). See Exhibit 9.7 for the unit product cost report. Based on the reported costs, marketing set initial prices on the two drones at $62.48 (- $48.06 130%) for the Sport and $137.28 (-$105.60 130%) for the Pro The overhcad allocation rate at the CenterPoint facility is 90 percent (- $2,430,000 - $2.700,000). See Exhibit 9.7 for the unit product cost report. Based on the reported costs, marketing set initial prices on the two drones at $62.48 (- $48.06 130%) for the Sport and $137.28 ($105.60 x 130%) for the Pro. Pro 40,000 $ 60.00 Exhibit 9.7 Third Quarter Unit Cost Report-CenterPoint Manufacturing Facility A B 1 Sport 2 Units produced 100,000 3 4 Direct material S 15.00 5 Direct labor 6 Assembly $7.50 7 Packaging 9.90 8 Total direct labor S 17.40 9 Direct costs S 32.40 10 Applied overhead ( 90% of direct labor costs) 15.66 11 Unit costs $ 48,06 $15.00 9.00 $ 24.00 $ 84.00 21.60 $ 105.60 The production managers claimed that Center Point's operations were among the most efficient in the business Furthermore, when they studied the cost report, they were surprised that the Pro was only about twice as costly to produce as the Sport That seemed too low The production managers explained that the Pro requires much more complex equipment and special handling in Assembly. In addition, it requires much shorter production runs, requiring more setups "Something." they said, "doesn't seem right in the way the costs came out Cost Allocation and Environmental Processes-Ethical Issues California Circuits Company (3C) manufactures a variety of components. Its Valley plant specializes in two electronic components used in circuit boards. These components serve the same function and perform equally well. The difference in the two products is the raw material. The XLD chip is the older of the two components and is made with a metal that requires a wash prior to assembly. Originally, the plant released the wastewater directly into a local river. Several years ago, the company was ordered to treat the wastewater before its release, and it installed relatively expensive equipment. While the equipment is fully deprecated, annual operating expenses of $250,000 are still incurred for wastewater treatment Two years ago, company scientists developed an alloy with all of the properties of the raw materials used in XL-D that generates no wastewater. Some prototype components using the new material were produced and tested and found to be indistinguishable from the old components in every way relating to their fitness for use. The only difference is that the new alloy is more expensive than the old raw material. The company has been test marketing the newer version of the component, referred to as XL-C, and is currently trying to decide its fate. Manufacturing of both components begins in the Production Department and is completed in the Assembly Department. No other products are produced in the plant. The following provides Information for the two components: XL-D XL-C Units produced 100,000 25,000 Direct material costs per unit $12 $14 Direct Labor Information Direct labor hours per unit --Production Direct labor-hours per unit--Assembly Direct labor rate per hour all labor $20 Other Activity Information Machine hours per unit-Production Machine-hours per unit-Assembly Testing hours per unit Shipping weight per unit (pounds) Wastewater generated per unit (gallons) 0.1 0.1 0.4 0.4 $20 1.6 0.4 3.0 1.0 10.0 1.6 0.4 3.0 1.6 0.0 Annual overhead costs for the two departments follow: Production Department Assembly Department $1,050,000 $500,000 The company president believes that it's foolish to continue producing two essentially equivalent products. At the same time, the corporate image is somewhat tarnished because of a toxic dump found at another site (not the Valley plant). The president would like to be able to point to the Valley plant as an example of company research and development (R&D) working to provide an environmentally friendly product. The controller points out to the president that the company's financial position is shaky, and it cannot afford to make products in any way other than the most cost-efficient one. Required: A. 3C's current cost accounting system charges overhead to products based on direct labor cost using a single plantwide rate. What per unit product costs (material, labor, and overhead) will It report for the two products if the current allocation system is used? Use the tab "Requirement A" in the Excel template to answer this question. Set up a chart similar to Exhibit 9.7 in the textbook (see page 358) to show your answer (adjust the names of the parts of the chart in the textbook to fit with the details of the project). Do not round intermediate calculations, which means you need to use cell referencing for calculations. B. The controller recently completed an executive education course describing the two-stage allocation procedure. Assume that the first stage allocates costs to departments and the second stage allocates costs to products. The controller believes that the costs will be more accurate if machine-hours are used to allocate Production Department costs and direct labor-hours are used to allocate Assembly Department costs. What per unit product costs will be reported for the two products if the two-stage allocation process is used? Use the tab "Requirement B* in the Excel template to answer this question. Set up a chart similar to Exhibit 9.9 in the textbook (see page 359) to show your answer. Do not round intermediate calculations, which means you need to use cell referencing for calculations. Budgeted overhead $810,000 $1,350,000 = 60% of direct labor cost Packaging: Budgeted direct labor cost Based on these overhead rates, Nancy calculated the revised product costs (see Q Exhibit 9.9). For the Sport, the Assembly costs per unit were $2.70 [($45 x 6,000 MH)/ 100,000 units). Note that the 6,000 machine-hours (MH) were shown in Exhibit 9.6, row 5. Also for the Sport, the Packaging costs per unit were $5.94 (= 60% * $9.90), where the $9.90 is the Packaging direct labor cost shown in Exhibit 9.9, row 7. We can use the same methods to find the unit overhead costs for the Pro. Exhibit 9.9 Third Quarter Unit Cost Report-CenterPoint Manufacturing Facility A B 1 Sport Pro 2 Units produced 100,000 40,000 3 4 Direct material $ 15.00 $ 60.00 3 Direct labor 6 Assembly $ 7.50 $ 15.00 7 Packaging 9.00 9.00 8 Total direct labor s 17:40 S 24.00 9 Direct costs S 32.40 S 841.00 10 Applied overhead 11 Assembly ( 545 per machine-hour) $ 2.70 $ 33.75 12 Packaging (609% of packaging direct labor cost) 5.04 5.40 13 Total overhead $ 864 $19.15 14 Unit costs 5 41.04 5123:15 Two-Stage Cost Allocation and the Choice of Cost Drivers Let's now consider IVC, Inc. At its CenterPoint manufacturing facility, it makes two models of drones used primarily by hobbyists. The Sport Model is a basic drone with a simple camera and few high-end features. It is often sold in large quantities to discount stores, sporting goods stores, and other outlets that appeal to budget buyers. The Pro Model is a higher end drone with a better camera and more sophisticated navigational capabilities. It is sold mostly online and at some hobby stores CenterPoint's production managers had a conference call with the marketing managers in Los Angeles. Company policies called for initial price guidelines for products to include a 30 percent markup over full cost. The marketing managers complained that, although the Pro was selling very well even with prices quoted above the guidelines, the Sport was meeting very houvy price resistance and was being discounted well below the guidelines to well. The marketing managers had arranged the call to determine why IVC's costs on the cheaper drone were apparently so much higher than its competitors Production at the CenterPoint facility takes place in two buildings, Assembly and Packaging The drone production process consists of delivering materials (plastic, rotors, cameras, and so on) to the assembly line, assembling drones by employees working in production cells, and then transferring them to the Packaging building. Only one drone model at a time is produced, and the machines have to be recalibrated and tested before beginning the other model. The Packaging Department tests the drones, packages them, and then ships them to customers Sce Exhibit 9.6 for data on the operations of the CenterPoint facility for the third quarter. The facility's cost system is a traditional product costing system that allocates manufacturing overhead to products based on direct labor costs. Page 358 Exhibit 9.6 Third Quarte Production and Contato Center Paint Manufacturing Facility Exhibit 9.6 Third Quarter Production and Cost Data-CenterPoint Manufacturing Facility A B D 1 Sport Pro Total 2 3 Number of units 100,000 40,000 140.000 4 5 Machine-hours-Assembly 6,000 30,000 36.000 6 7 Direct materials $1,500,000 $ 2,400,000 $3,900,000 8 Direct labor-Assembly $ 750,000 $ 600,000 $ 1.350,000 9 Direct labor--Packaging 990,000 360,000 1.350,000 10 Direct labor-Total $ 1,740,000 $ 960,000 $2.700.000 11 Total direct cost $3.240.000 $ 3,360,000 $6,600,000 12 13 Overhead costs 14 Assembly building $ 1.620,000 15 Packaging building 810.000 16 Total overhead $ 2.430.000 17 Total costs $ 9,030,000 The overhead allocation rate at the Center Point facility is 90 percent (- $2.430,000 $2,700,000). See Exhibit 9.7 for the unit product cost report. Based on the reported costs, marketing set initial prices on the two drones at $62.48 (- $48.06 130%) for the Sport and $137.28 (-$105.60 130%) for the Pro The overhcad allocation rate at the CenterPoint facility is 90 percent (- $2,430,000 - $2.700,000). See Exhibit 9.7 for the unit product cost report. Based on the reported costs, marketing set initial prices on the two drones at $62.48 (- $48.06 130%) for the Sport and $137.28 ($105.60 x 130%) for the Pro. Pro 40,000 $ 60.00 Exhibit 9.7 Third Quarter Unit Cost Report-CenterPoint Manufacturing Facility A B 1 Sport 2 Units produced 100,000 3 4 Direct material S 15.00 5 Direct labor 6 Assembly $7.50 7 Packaging 9.90 8 Total direct labor S 17.40 9 Direct costs S 32.40 10 Applied overhead ( 90% of direct labor costs) 15.66 11 Unit costs $ 48,06 $15.00 9.00 $ 24.00 $ 84.00 21.60 $ 105.60 The production managers claimed that Center Point's operations were among the most efficient in the business Furthermore, when they studied the cost report, they were surprised that the Pro was only about twice as costly to produce as the Sport That seemed too low The production managers explained that the Pro requires much more complex equipment and special handling in Assembly. In addition, it requires much shorter production runs, requiring more setups "Something." they said, "doesn't seem right in the way the costs came out