At the bottom of the attached problem, the professor wants us to create a data table to represent sensitivity analysis. Are you able to provide guidance on how this should be setup?

Computer Graphics (CG) is a small manufacturer of electronic products for computers with graphics capabilities. The company has succeeded by being very innovative in product design. As a spin-off of a large electronics manufacturer (ElecTech), CG management has extensive experience in both marketing and manufacturing in CG's main product is a circuit board (CB3668) used in computers with enhanced graphics capabilities. Prices vary depending on the terms of sale and the size of the purchase; the average price for CB3668 is $95.90. If the firm is able to take off, it might be able to raise prices, but it might have to reduce the price because of incre to sell 120,000 units in the coming year, and sales are expected to increase in the following years. The future for CG looks very bright indeed, but it is new and has not developed a strong financial base. Cash flow management is a critical feature of the firm's financial management, and top management must watch cash flow num At present, CG is manufacturing the CB3668 in a plant leased from ElecTech using some equipment purchased from ElecTech. CG manufactures about 70 percent of the parts in this circuit board. CG management is considering a significant reengineering project to significantly change the plant and manufacturing process. The project's objective is to increase the number of purchased parts (to about 55 percent) and to reduce the complexity of the manufacturing process. This would also permit CG to remove some lease most expensive equipment in the plant. The per unit manufacturing costs for 120,000 units of CB3668 follow. General, selling, and administrative costs are $9.97 variable cost per unit and $1,227,000 fixed; these costs are not expected to differ for either the current or the proposed manufacturing plan. Data Expected selling price per unit Expected sales volume (in units) $95.90 120,000 Materials and purchased parts Direct labor Variable overhead Fixed overhead Manufacturing information for CB3668: Number of setups Batch size Cost per setup Machine hours General, Selling, and Administrative (GSA) Costs: Variable cost (per unit sold) Fixed (per year) Per-Unit Costs at Above Volume Current Proposed $6.01 $15.17 $12.29 $14.29 $23.60 $31.25 $50.27 $25.13 2,500 48 $296 83,400 2,000 60 $296 56,300 $9.97 $1,227,000 $10.23 1. Breakeven points (in units) Current Per Unit Units - breakeven units - use GoalSeek Selling price per unit Total Revenue Variable Costs Unit level costs Materials and purchased parts Direct labor Variable overhead Variable SG&A costs Total Variable cost per unit Batch level costs Setup Number of setups Cost Total Variable Costs CM per unit Total Fixed Cost: Fixed SG&A Fixed manufacturing: Fixed cost/unit # of units Total Total Fixed Cost per Year Total 191,731 $95.90 Proposed Per Unit $95.90 $18,387,012 $6.01 $12.29 $23.60 $9.97 $51.87 $6.17 $58.04 $37.86 Total 209,138 $20,056,318 $15.17 $14.29 $31.25 $9.97 $70.68 3,995 $1,182,520 $4.93 3,486 $1,031,856 $7,259,400 $75.61 $20.29 $4,242,600 $1,227,000 $50.27 120,000 $1,227,000 $25.13 120,000 $6,032,400 $7,259,400 $0 Operating Income $3,015,600 $4,242,600 $0 2. Indifference Point: The indifference point in volume, Q, is defined as the point where operating income is the same under both plans. Since total revenue is assumed not to vary by choice of production plan, this is operating profit equality is achieved when the total relevant cost is the same for each decision alternative. Total Relevant Cost, Current Plan = Total Relevant Cost, Proposed Plan Indifference point 396,600 Use GoalSeek Current Per Unit Units Selling price per unit Total Revenue Total 396,600 $95.90 Proposed Per Unit Total 396,600 $95.90 $38,033,940 $38,033,940 Variable Costs Unit level costs Materials and purchased parts Direct labor Variable overhead Variable SG&A costs Total Variable cost per unit $6.01 $12.29 $23.60 $9.97 $51.87 Batch level costs Setup Number of setups Cost $15.17 $14.29 $31.25 $0.00 $60.71 $6.17 Total Fixed Cost: Fixed SG&A Fixed manufacturing: Fixed cost/unit # of units Total Total Fixed Cost per Year $4.93 6,611 $1,956,856 $58.04 $37.86 Total Variable Costs CM per unit 8,263 $2,445,848 $15,016,450 $65.64 $30.26 $11,999,498 $1,227,000 $50.27 120,000 $1,227,000 $25.13 120,000 $6,032,400 $7,259,400 Operating Income $3,015,600 $4,242,600 $7,757,050 $7,756,898 Difference $152 3. Sensitivity analysis: since uncertainty is important in this case, CG Graphics could use a spreadsheet analysis as illustrated below. Note that the current method looks good if projected demand rises. This is a direct result of increased operating leverage associated with the current method. Current Per Unit Units Selling price per unit Total Revenue Total 120,000 $95.90 Proposed Per Unit $95.90 $11,508,000 Variable Costs Unit level costs Materials and purchased parts Direct labor Variable overhead Variable SG&A costs Total Variable cost per unit $6.01 $12.29 $23.60 $9.97 $51.87 Batch level costs Setup Number of setups Cost $6.17 Total Variable Costs CM per unit $58.04 $37.86 Total Fixed Cost: Fixed SG&A Fixed manufacturing: Fixed cost/unit # of units Total Total Fixed Cost per Year Total 120,000 $11,508,000 $15.17 $14.29 $31.25 $9.97 $70.68 2,500 $740,000 $4.93 2,000 $592,000 $4,543,600 $75.61 $20.29 $2,434,400 $1,227,000 $50.27 120,000 $1,227,000 $25.13 120,000 $6,032,400 $7,259,400 $2,715,800 Operating Income $3,015,600 $4,242,600 $1,808,200 Data Table - refer to Data->What-If Analysis->Data Table Operating Income Sensitivity Analysis Levels of Demand Current $2,715,800 Proposed $1,808,200 g in the electronics industry. A long list ofExcel Instructions creased competition. The firm expects numbers closely. ased equipment and to sell some of the Chart(s) - charts have many properties, you will set only the following properties. Chart # 1 Name Chart 1 ChartType Location ChartWidth ChartHeight Series Name XValues Values PlotOrder Format.Line/Border.ForeColor: Format.Fill.ForeColor: ChartTitle Caption Font.Name Font.Size Font.Bold ChartLegend Position Font.Name Font.Size Font.Bold Axis - Horizontal AxisTitle AxisTickLabelsFormat Axis - Vertical AxisTitle AxisTickLabelsFormat ChartAreaColor PlotAreaColor Line $J$140 8.25 4 Series 1 Current $F$141:$F$158 $G$141:$G$158 Not this semester Red 0, Green 176, Blue 240 N/A Series 2 Proposed $F$141:$F$158 $H$141:$H$158 Not this semester Red 190, Green 75, Blue 72 N/A Operating Income at Various Levels of Demand Calibri 18 True Below the chart Calibri 10 False Levels of Demand #,##0_);[Red](#,##0) Operating Income $#,##0;[Red]$#,##0 Red 191, Green 191, Blue 191 Red 242, Green 242, Blue 242