Question

Learning objective: This project is designed for you to compute contribution margin, and conduct breakeven and what-if analyses by using Excel spreadsheets.

Requirements: Please complete the calculations for the following problem in Excel spreadsheets. You can use Project 1 Format.xlsx to work on the project. A lot formulae are inserted in the comments of the spreadsheet file for your reference. Setting the formulae by referencing to the data in Excel spreadsheets is required.

Problem set A computer store has sale revenue of $120,000 from selling 2,000 one-terabyte (1 TB) portable hard drives. The selling price per 1 TB portable hard drive is $60. This computer store purchases hard drives from a whole sale supplier for $42 per piece. This computer store has administrative expenses of $17,000 in fixed costs. Additionally, it has $5,000 in depreciation. The total fixed costs include administrative and depreciation expenses.

1. Please use the format of a contribution margin income statement to calculate the contribution margin (in dollars), unit contribution, and contribution margin ratio for this computer store. You can refer to the format of Example 1 in the textbook or in the class notes.

	Total	per Unit	Percentages
Sales(xxx unit)
Less: Variable costs
Contribution margin
Less: Fixed costs
Net income

2. What will be the break-even point in units and in dollars if we assume that this computer store sells the portable hard drives only?

3. How many units must this computer store sell to earn a pre-tax income of $68,000?

4. If the company’s tax rate is 35 percent, how many units must be sold to earn an after-tax profit of $86,000?

5. What is the company’s margin of safety based on the sales of 3,000 portable hard drives?

6. Assume that there is no change in fixed costs. What will be the expected net income if this company has a sales revenue of $390,000?

Answer 1

Answer 1:

Contribution margin (in dollars) = $36,000,

Unit contribution = $18, and

Contribution margin ratio = 30%

Workings:

Answer 2:

Break-even point in units = Fixed costs /Contribution per unit = 22000 /18 = 1222.22 units

Break-even point in dollars = Fixed costs /Contribution margin ratio = 22000 /30% = $73,333.33

Answer 3:

Units be sold to earn pretax profit of $68000 = (Fixed costs +Pretax profit) / Contribution per unit = (22000+ 68000) /18

= 5,000 units

Answer 4:

Tax rate is = 35%

Units must be sold to earn an after-tax profit of $86,000 = (Fixed costs + after tax profit / (1 - Tax rate)) /Contribution per unit

= (22000+ 86000 / (1 - 35%)) /18

= 8,572.65 units

Answer 5:

Sales Value of 3,000 portable hard drives = 3000 * 60 = $180,000

Break-even point in dollars = $73,333.33

Margin of safety = 180000 - 73333.33 = $106,666.67

Answer 6:

Expected net income = Sales revenue * contribution margin ration - Fixed costs = $390,000 * 30% - 22,000 = $95,000

Expected net income = $95,000