Question

please answer this question on excel

Instructions: Using the information below, complete the tables for B.E. Now Industries in excel. You will need to create the table in excel. To complete the requirements you must use proper cell referencing, formulas, and formatting. . For table 1: calculate the required amounts identified in Column A and enter your associated response in units or dollars in Column B - Fortable 2: determine the impact of the change identified in Column A and enter the impact in Column B Information: The costing manager for B.E. Now Industries is tasked with forecasting expected breakeven and profitability levels for its Cost Cover Division. Although the expectation is that the division will be profitable next year, the fear is that continued slow growth in the local economy will cause the division to struggle to reach breakeven. As part of the forecast, the manager must be prepared to explain the impact on various cost components of changes in certain assumptions The manager has received the following information from the Accounting Department: B.E. NOW INDUSTRIES Cost Cover Division Variable Costing Components Actuals Sales price (per unit) $90 Variable costs (per unit) $63 Fixed costs $810,000 Desired pretax profit $135,000 Tax rate 35% Table 1: 1 Calculation Amount 2 Breakeven point (in units) 3 Breakeven point (in dollars) 4 Units needed to earn desired profit 5 Sales dollars needed for desired profit MacBook Pro

Answer 1

Table 1

Variable costing Components
Sales price(per unit)	$                90.00
	$                63.00
Fixed costs	$      810,000.00
Desired pretax profit	$      135,000.00
Tax	35%

Contribution/Unit	Sales price per unit - Variable Cost per unit	90-63	$                27.00
Units At Break Even Point	Fixed Cost/Contribution	810,000/27	30,000
Sales At break Even point	Sales price per unit x Units at break even point	90x30000	$ 2,700,000.00
Extra Units to have desired profit	Desired pretax profit/Contribution	135000/27	5,000
Total Units to earn desired profit	Break even point unit + Extra profit units	30,000 + 5,000	35,000
Sales dollars needed for desired profit	Total unit at desired profit x Sales price per unit	35,000 x 90	$ 3,150,000.00

Breakeven point(in units)	30,000
Breakeven point (in dollars)	2,700,000
Units needed to earn desired profit	35,000
Sales dollars needed for desired profit	3,150,000

Table 2:

Below table answers and provide explanation behind answers

Impact on the contribution margin if fixed costs increase by $40,000	No impact	Contribution margin depends on the Sales price and variable cost of each unit. i.e = Sales price per unit - Variable cost per unit. It do not depends on Fixed costs
Impact on the contribution margin ratio if per unit sales and variable costs both decreases by $10	It will increase to .3375 from 0.3	Contribution margin ratio per unit = (Sales price - Variable cost)/ Sales price = 27/80 = 0.3375 Previous = 27/90 = 0.3
Impact on total variable costs if the desired pretax profit increases by $50,000	No impact	pretax profit and variable cost are not related
Impact on break even in units if tax rate decreases to 30 percent	No impact	tax is calculated on profit. Profit comes only after break even point