how do you use goal seek tool for part E. the answers are correct
1. Red Sunset Co. manufactures mobile phones and wants to add a new model to its current line of products. The firm has estimated that the new phone's selling price will be $80 and that variable costs would represent 65% of the sale price. Fixed operating costs are estimated to be $15M and the firm's marginal tax rate is expected to remain at 35%. The firm also has forecasted that interest expenses associated with the new chip will reach $5M. The firm has 5M of common shares outstanding and forecasts a total preferred dividend payment of $200,000 for the next year. If Red Sunset Co. expects to sell IM units of the new phone, then: e) Use the Goal Seek tool to determine the number of units that would allow Red Sunset Co, to break even in terms of its net income and the selling price to break even in terms of earnings before taxes. Also, determine with the same tool how much preferred dividends the firm would need to pay to keep a degree of financial leverage of 2. Merge %) 0.0 00 2.0 Condit Forma B D E BI U ste B34 fx 714285.714285714 Red Sunset Co. Projected Income Statement Sales Less: Variable Costs Less: Fixed Costs Earnings Before Interest and Taxes Less: Interest Expense 8. Earnings Before Taxes 9 Taxes 20 Net Income 11 Less: Preferred Dividends 12 Net Income Available to Common 13 Earnings per Share 14 15 Assumptions 16 Price per unit 17 Unit sales 18 Variable Costs as a Percent of Sales 19 Interest Expense 20 Tax Rate 21 Common Shares Outstanding 22 23 Operating break-even points 24 Units 25 Dolls $80,000,000 52,000,000 15,000,000 13,000,000 5,000,000 8,000,000 2,800,000 5,200,000 200,000 5,000,000 1.00 $ S80.00 1,000,000 65% 5,000,000 35% 5,000,000 535.714 $ 42,857,143 27 Target level of EBIT 28 Units needed to reach target EBIT 29 30 Degree of operating leverage 31 Degree of financial leverage $2,000,000 607,143 2.15 1.69 Operating break-even points Units Dollars 535,714 $ 42,857,143 Target level of EBIT Units needed to reach target EBIT $2,000,000 607,143 2.15 1.69 3.64 Degree of operating leverage Degree of financial leverage 2 Degree of combined leverage 3 4 Number of units to break even on net income 5 Price per unit to break even on EBT 6 Preferred dividend payment to keep a financial leverage of 1.5 37 38 714,285.71 57.14 974,379.72 39 40 41 42 43 44 45 46 47 48 1. Red Sunset Co. manufactures mobile phones and wants to add a new model to its current line of products. The firm has estimated that the new phone's selling price will be $80 and that variable costs would represent 65% of the sale price. Fixed operating costs are estimated to be $15M and the firm's marginal tax rate is expected to remain at 35%. The firm also has forecasted that interest expenses associated with the new chip will reach $5M. The firm has 5M of common shares outstanding and forecasts a total preferred dividend payment of $200,000 for the next year. If Red Sunset Co. expects to sell IM units of the new phone, then: e) Use the Goal Seek tool to determine the number of units that would allow Red Sunset Co, to break even in terms of its net income and the selling price to break even in terms of earnings before taxes. Also, determine with the same tool how much preferred dividends the firm would need to pay to keep a degree of financial leverage of 2. Merge %) 0.0 00 2.0 Condit Forma B D E BI U ste B34 fx 714285.714285714 Red Sunset Co. Projected Income Statement Sales Less: Variable Costs Less: Fixed Costs Earnings Before Interest and Taxes Less: Interest Expense 8. Earnings Before Taxes 9 Taxes 20 Net Income 11 Less: Preferred Dividends 12 Net Income Available to Common 13 Earnings per Share 14 15 Assumptions 16 Price per unit 17 Unit sales 18 Variable Costs as a Percent of Sales 19 Interest Expense 20 Tax Rate 21 Common Shares Outstanding 22 23 Operating break-even points 24 Units 25 Dolls $80,000,000 52,000,000 15,000,000 13,000,000 5,000,000 8,000,000 2,800,000 5,200,000 200,000 5,000,000 1.00 $ S80.00 1,000,000 65% 5,000,000 35% 5,000,000 535.714 $ 42,857,143 27 Target level of EBIT 28 Units needed to reach target EBIT 29 30 Degree of operating leverage 31 Degree of financial leverage $2,000,000 607,143 2.15 1.69 Operating break-even points Units Dollars 535,714 $ 42,857,143 Target level of EBIT Units needed to reach target EBIT $2,000,000 607,143 2.15 1.69 3.64 Degree of operating leverage Degree of financial leverage 2 Degree of combined leverage 3 4 Number of units to break even on net income 5 Price per unit to break even on EBT 6 Preferred dividend payment to keep a financial leverage of 1.5 37 38 714,285.71 57.14 974,379.72 39 40 41 42 43 44 45 46 47 48
