Question

please complete the question

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 For table 2: determine the impact of the change identified in Column A and enter the impact in Column 8 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 Sales price (per unit) Variable costs (per unit) Actuals $90 $63 Fixed costs $810,000 $135,000 Desired pretax profit Tax rate 35% Table 1: A B 1 Calculation Amount 2 Breakeven point (in units) 123 3 Breakeven point (in dollars) 4 Units needed to eam desired profit 5 Sales dollars needed for desired profit MacBook Pro Q Search or type URL $ % & 3 4 5 6 7 W E R T Y U 1P D F S G H J K CO

Answer 1

Table 1-

Break even point in units- 810000/27 = 30000 units.

Break even point in dollars - 30000*90 = 2700000 dollars.

Units needed to earn desired profit - (810000+135000)/27 = 35000 units

Sales dollars needed for desired profits - 35000*90 = 3150000.

Table 2-

No impact on contribution margin by any change in fixed cost.

Current contribution margin ratio - 27*100/90 = 30%

If sales & variable cost both decrease by $ 10, estimated contribution ratio-

27*100/80 = 33.75% (increase by 3.75%)

No impact on variable cost if pretax profit increases.

No impact on break even in units, if tax rate decreases.