Thl Requirements Re TH an The controller for the Zillinger Corporation wants to use the What-if Analysis tool in Excel to analyze the sales price, volume, and profit related to the graphics card it manufactures. The sales price of the graphics card is $90.00, the variable cost is $72.50, and monthly fixed expenses are $10,500. The company is currently selling 900 of the graphic cards per month. The company has a requirement that each product generate monthly operating income (profit) of $15,500 or more. Requirements for the Excel analysis 1. Assume that the sales price is $90.00 per unit, variable costs are $72.50, monthly fixed expenses are $10,500, with a volume of 900 units. Use formulas to calculate the contribution margin per unit, total contribution margin, and operating income. Also add $1 less than the target profit to a cell below the profit calculation. Create a data table in Excel using the What-if Analysis tool. Use volumes of 1,200 units down to 600 units in 50-unit increments and sales prices of $90.00 up to $125.00 in $5 increments. Use conditional formatting to shade the cells in the data table that meet or exceed the company's target profit Using this data table, answer the following questions: a. How many graphics cards does the company need to sell in a month to break even if the selling price remains at $90.00? b. Can the company reach its target profit goal? If so, approximately how many graphics cards does the company need to sell to reach its monthly target profit if the selling price remains at $90.00 per graphics card? c. Approximately how many graphics cards does the company need to sell to reach its monthly target profit if it increases the selling price to $105.00 per graphics card? d. What would the company's monthly profit be if it increases the selling price to $115.00 and sells 900 graphics cards? qu US the ard US gre 2. Now assume that the variable cost has decreased to $60.00 and the monthly fixed expenses have increased to $18,000. All other data remains the same. Create a new profit calculation in Excel (as you did in Requirement 1) with the new variable cost and the new monthly fixed expenses. Using this new data table, answer the following questions: a. How many graphics cards does the company need to sell in a month to break even if the selling price remains at $90.00? b. Can the company reach its target profit goal? If so, approximately how many graphics cards does the company need to sell to reach its monthly target profit if the selling price remains at $90.00 per graphics card? c. Approximately how many graphics cards does the company need to sell to reach its monthly target profit if it increases the selling price to $105.00 per graphics card? d. What would the company's monthly profit be if it increases the selling price to $115.00 and sells 900 graphics cards? Using the Excel file you created for Zillinger and looking at requirement 1, how many graphics cards does the company need to sell in a month to break even if the selling price is $90.00 per unit, variable costs are $72.50, monthly fixed expenses are $10,500, with a volume of 900 units? A. 650 B. 1.200 C. 700 D. 600 Using the Excel file you created for Zillinger and looking at requirement 1, approximately how many graphics cards does the company need to sell to reach its monthly target profit if it increases the selling price to $105.00 per graphics card, variable costs are $72.50, and monthly fixed expenses are $10,500? A. 900-950 B. Cannot C. 750 - 800 D. 600 - 650 Using the Excel file you created for Zillinger and looking at requirement 2, how many graphics cards does the company need to sell in a month to break even assuming that the selling price is $90.00 per unit, the variable cost has decreased to $60.00 and the monthly fixed expenses have increased to $18,000, with a volume of 900 units? A. 600 B. 700 C. 1.200 D. 650 Using the Excel file you created for Zillinger and looking at requirement 2, approximately how many graphics cards does the company need to sell to reach its monthly target profit if it increases the selling price to $105.00 per graphics card, the variable cost has decreased to $60.00 and the monthly fixed expenses have increased to $18,000? A. 700 - 750 B. 800 - 850 C. 600 - 650 OD. 1.150 - 1,200 Using the Excel file you created for Zillinger and looking at requirement 2, what would the company's monthly profit be if it increases the selling price to $115.00, the variable cost has decreased to $60.00, the monthly fixed expenses have increased to $18,000, and it sells 900 graphics cards? A $34.250 B. $27,000 C. $27.750 OD. $31,500 AI A B $90.00 $72.50 900 $10,500.00 1 Sales price per unit 2 Less: Variable cost per unit 3 Contribution Margin per unit 4 X Volume 5 Total Contribution Margin 6 Less: Fixed Expenses 7 Operating Income 8 9 10 Monthly Target Profit 11 12 13 14
