Break-even and conditional format
Question 1:
Ensco Lighting Company has fixed costs of $100,000, sells its units for $28, and has variable costs of $15.50 per unit.
a. Compute the break-even point.
b. Create a break-even graph in Excel
Units of: 6,000 7,000 8,000 9,000 and 10,000, and 11,000
Hint: calculate the total sales, and the total cost. The intersection should be your break-even in units.
c. Ms. Watts comes up with a new plan to cut fixed costs to $75,000. However, more labor will now be required, which will increase variable costs per unit to $17. The sales price will remain at $28. What is the new break-even point?
d. Under the new plan, what is likely to happen to profitability at very high volume levels (compared to the old plan)?
Question 2: Your manager provided you with the following information in a pivot-table.
Summarize how conditional format might be used in Excel to alert a user when actual dollars spent exceed the budget. Feel free to duplicate an example in Excel.
Break-even and conditional format
Question 1:
Ensco Lighting Company has fixed costs of $100,000, sells its units for $28, and has variable costs of $15.50 per unit.
a. Compute the break-even point.
b. Create a break-even graph in Excel
Units of: 6,000 7,000 8,000 9,000 and 10,000, and 11,000
Hint: calculate the total sales, and the total cost. The intersection should be your break-even in units.
c. Ms. Watts comes up with a new plan to cut fixed costs to $75,000. However, more labor will now be required, which will increase variable costs per unit to $17. The sales price will remain at $28. What is the new break-even point?
d. Under the new plan, what is likely to happen to profitability at very high volume levels (compared to the old plan)?
Question 2: Your manager provided you with the following information in a pivot-table.
Summarize how conditional format might be used in Excel to alert a user when actual dollars spent exceed the budget. Feel free to duplicate an example in Excel.
Related questions
Memofax, Inc., produces memory enhancement kits for fax machines. Sales have been very erratic, with some months showing a profit and some months showing a loss. The company's contribution format income statement for the most recent month is given below:
| Sales (13,500 units at $20 per unit) | $ | 252,000 |
| Variable expenses | 126,000 | |
| Contribution margin | 126,000 | |
| Fixed expenses | 141,000 | |
| Net operating loss | $ | (15,000) |
| Required: | |
| 1. | Compute the company's CM ratio and its break-even point in both units and dollars. (Omit the "%" and "$" signs in your response.) |
| CM ratio | % |
| Break-even point in units | |
| Break-even point in dollars | $ |
| 2. | The sales manager feels that an $62,000 increase in the monthly advertising budget, combined with an intensified effort by the sales staff, will result in a $87,000 increase in monthly sales. If the sales manager is right, what will the revised net operating income or loss? (Use the incremental approach in preparing your answer.) (Omit the "$" sign in your response.) |
| is | $ | |
| 3. | Refer to the original data. The president is convinced that a 10% reduction in the selling price, combined with an increase of $34,000 in the monthly advertising budget, will double unit sales. What will the new contribution format income statement look like if these changes are adopted? (Input all amounts as positive values. Omit the "$" sign in your response.) |
| Contribution Income Statement | |
| $ | |
| $ | |
| 4. | Refer to the original data. The company%u2019s advertising agency thinks that a new package would help sales. The new package being proposed would increase packaging costs by $0.40 per unit. Assuming no other changes, how many units would have to be sold each month to earn a profit of $4,700? (Do not round intermediate calculations.) |
| Sales units |
| 5. | Refer to the original data. By automating, the company could slash its variable expenses in half. However, fixed costs would increase by $119,000 per month. |
| a. | Compute the new CM ratio and the new break-even point in both units and dollars. (Do not round intermediate calculations. Omit the "%" and "$" signs in your response.) |
| CM ratio | % | |
| Break-even point in units | ||
| Break-even point in dollars | $ | |
| b. | Assume that the company expects to sell 20,100 units next month. Prepare two contribution format income statements, one assuming that operations are not automated and one assuming that they are. (Omit the "$" and "%" signs in your response.) |
| Not Automated | Automated | ||||||
| Total | Per Unit | % | Total | Per Unit | % | |
| $ | $ | $ | $ | |||
| $ | $ | |||||
| $ | $ | |||||
| Modern Artifacts can produce keepsakes that will be sold for $70 each. Nondepreciation fixed costs are $2,300 per year and variable costs are $42 per unit. |
| a. | If the project requires an initial investment of $6,000 and is expected to last for 5 years and the firm pays no taxes. The initial investment will be depreciated straight-line over 5 years to a final value of zero, and the discount rate is 10%. What are the accounting and NPV break-even levels of sales? (Do not round intermediate calculations. Round your answers to the nearest whole number.) |
| Accounting break-even levels of sales | units |
| NPV break-even levels of sales | units |
| b. | What will be the accounting and NPV break-even levels of sales, if the firm's tax rate is 40%? (Do not round intermediate calculations. Round your answers to the nearest whole number.) |
| Accounting break-even levels of sales | units |
| NPV break-even levels of sales | units |
| Modern Artifacts can produce keepsakes that will be sold for $70 each. Nondepreciation fixed costs are $2,300 per year and variable costs are $42 per unit. |
| a. | If the project requires an initial investment of $6,000 and is expected to last for 5 years and the firm pays no taxes. The initial investment will be depreciated straight-line over 5 years to a final value of zero, and the discount rate is 10%. What are the accounting and NPV break-even levels of sales? (Do not round intermediate calculations. Round your answers to the nearest whole number.) |
| Accounting break-even levels of sales | units |
| NPV break-even levels of sales | units |
| b. | What will be the accounting and NPV break-even levels of sales, if the firm's tax rate is 40%? (Do not round intermediate calculations. Round your answers to the nearest whole number.) |
| Accounting break-even levels of sales | units |
| NPV break-even levels of sales | units |
