how would you make the table its asking me too on (A)? i also put a picture of ex. 9.7 from the textbook. (ex. 9.6 is to help with 9.7 I'm assuming) I'm also giving additional information the prof gave us to help solve the problem.

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 depreciated 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 100,000 $12 XL-C 25,000 $14 0.1 0.4 $20 Units produced Direct material costs per unit Direct Labor Information Direct labor-hours per unit-Production Direct labor-hours per unit-Assembly Direct labor rate per hour--all labor 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.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 $1,050,000 Assembly Department $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. 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. Center Point'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 heavy price resistance and was being discounted well below the guidelines to sell. 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 droncs, 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.5 Third Quarter Production and CostData-Center Paint Manufacturing Facility Exhibit 96 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 overheud 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 x 130%) for the Pro. The overhead 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 x 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 $ 17.40 9 Direct costs $ 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." Overhead Cost Pool Supervision Material Handling Testing Wastewater treatment Equipment depreciation Shipping Cost Pool Total Cost Pool Driver $340,000 Direct labor hours $133,000 Direct material cost $150,000 Testing hours $300,000 Wastewater generated (gal) $500,000 Machine hours $127,000 Weight (pounds) 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 depreciated 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 100,000 $12 XL-C 25,000 $14 0.1 0.4 $20 Units produced Direct material costs per unit Direct Labor Information Direct labor-hours per unit-Production Direct labor-hours per unit-Assembly Direct labor rate per hour--all labor 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.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 $1,050,000 Assembly Department $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. 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. Center Point'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 heavy price resistance and was being discounted well below the guidelines to sell. 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 droncs, 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.5 Third Quarter Production and CostData-Center Paint Manufacturing Facility Exhibit 96 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 overheud 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 x 130%) for the Pro. The overhead 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 x 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 $ 17.40 9 Direct costs $ 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." Overhead Cost Pool Supervision Material Handling Testing Wastewater treatment Equipment depreciation Shipping Cost Pool Total Cost Pool Driver $340,000 Direct labor hours $133,000 Direct material cost $150,000 Testing hours $300,000 Wastewater generated (gal) $500,000 Machine hours $127,000 Weight (pounds)