Question

Dime a Dozen Diamonds makes synthetic diamonds by treating carbon. Each diamond can be sold for $100. The materials cost for a standard diamond is $40. The fixed costs incurred each year for factory upkeep and administrative expenses are $208,000. The machinery costs $1.7 million and is depreciated straight-line over 10 years to a salvage value of zero.

a. What is the accounting break-even level of sales in terms of number of diamonds sold? Answer is 6,300

b. What is the NPV break-even level of diamonds sold per year assuming a tax rate of 21%, a 10-year project life, and a discount rate of 12%?

Answer 1

a]

Accounting breakeven = total fixed costs / contribution per unit

total fixed costs = factory and administrative fixed costs + depreciation per year

depreciation per year = cost of machinery / depreciable life = $1,700,000 / 10 = $170,000

total fixed costs = $208,000 + $170,000 = $378,000

contribution per unit = sale price per unit - variable cost per unit

contribution per unit = $100 - $40 = $60

Accounting breakeven = $378,000 / $60 = 6,300 units

b]

NPV break-even level is the sales at which NPV is zero

NPV is calculated using NPV function in Excel

First, we assume the number of diamonds sold yearly is 7,000.

At this level of sales, NPV is -$551,988

C D E B 7,000 F G H I J K L $1,700,000 1 Diamonds sold per year 2 3 4 Initial Investment 5 Cost of machinery 6 7 OCF 8 9 Revenues 10 - Variable Costs 11 - Fixed Costs 12 - Depreciation 13 Income before tax 14 - Taxes 15 Income after tax 16 + Depreciation 17 OCF 18 19 NPV Analysis 20 Project Cash Flows $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 ($1,700,000) $203,180 $203,180 $203,180 $203,180 $203,180 $203,180 $203,180 $203,180 $203,180 $203,180 22 NPV ($551,988)

7000 1 Diamonds sold per year 2 3 15 8 9 10 4 Initial Investment 5 Cost of machinery 1700000 7 OCE 8 9 Revenues 10 - Variable Costs 11 - Fixed Costs 12 - Depreciation 13 Income before tax 14 - Taxes 15 Income after tax 16 + Depreciation 17 OCF 18 19 NPV Analysis 20 Project Cash Flows =$B$1*100 =$B$1*100 =$B$1*100 =$B$1*100 =$B$1*100 =$B$1*100 =$B$1*100 =$B$1*100 =$B$1*100 =$B$1*100 =$B$1*40 =$B$1*40 =$B$1*40 =$B$1*40 =$B$1*40 =$B$1*40 =$B$1*40 =$B$1*40 =$B$1*40 =$B$1*40 208000 208000 208000 208000 208000 208000 208000 208000 208000 208000 =$B$5/10 =$B$5/10 =$B$ 5/10 =$B$5/10 =$B$5/10 =$B$5/10 =$B$5/10 =$B$5/10 =$B$5/10 =$B$5/10 =C9-C10-C11-C12 =D9-D10-D11-D12 =E9-E10-E11-E12 =F9-F10-F11-F12 =G9-G10-G11-G12 =H9-H10 H11-H12 =19-110-111-112 = 19-J10-111-112 = 9-K10-K11-K12 =L9-L10-L11-L12 =C13*21% =D13*21% =E13*21% =F13*21% =G13*21% =H13*21% =113*21% = 13*21% =K13*21% =L13*21% =C13-C14 =D13-D14 =E13-E14 =F13-F14 =G13-14 =H13-H14 =113-114 =J13-J14 =K13-K14 =L13-L14 =C12 =D12 =E12 =F12 =G12 =H12 =112 =J12 =K12 =L12 =C15+C16 =D15+D16 =E15+E16 F15+F16 =G15+G16 =H15+H16 =115+116 =J15+16 =K15+K16 =L15+L16 --B5 =C17 =D17 =E17 =F17 =G17 =H17 =117 =J17 =K17 =L17 22 NPV =NPV(12%,C20:L20)+B20

Now, we use GoalSeek function in Excel to find the number of diamonds sold yearly at which NPV is zero

C D E B 7,000 1 Diamonds sold per year 2 Goal Seek SBS22 $1,700,000 Set cell: To value: By changing cell: SBS1 OK Cancel 3 4 Initial Investment 5 Cost of machinery 6 7 OCF 8 9 Revenues 10 - Variable Costs 11 - Fixed Costs 12 - Depreciation 13 Income before tax 14 - Taxes 15 Income after tax 16 + Depreciation 17 OCF 18 19 NPV Analysis 20 Project Cash Flows $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 $700,000 $280,000 $208,000 $170,000 $42,000 $8,820 $33,180 $170,000 $203,180 ($1,700,000) $203,180 $203,180 $203,180 22 NPV ($551,988)!

The NPV break even level of sales is calculated to be 9,061 diamonds per year

At this level of sales, NPV is zero

B C D E F G H I L 9,061 1 Diamonds sold per year 2 3 4 Initial Investment 5 Cost of machinery D o 1 2 3 4 5 6 7 8 9 10 $1,700,000 7 OCF 9 Revenues 10 - Variable Costs 11 - Fixed Costs 12 - Depreciation 13 Income before tax 14 - Taxes 15 Income after tax 16 + Depreciation 17 OCF 18 19 NPV Analysis 20 Project Cash Flows $906,104 $906,104 $906,104 $906,104 $906,104 $906,104 $906,104 $906,104 $906,104 $906,104 $362,441 $362,441 $362,441 $362,441 $362,441 $362,441 $362,441 $362,441 $362,441 $362,441 $208,000 $208,000 $208,000 $208,000 $208,000 $208,000 $208,000 $208,000 $208,000 $208,000 $170,000 $170,000 $170,000 $170,000 $170,000 $170,000 $170,000 $170,000 $170,000 $170,000 $165,662 $165,662 $165,662 $165,662 $165,662 $165,662 $165,662 $165,662 $165,662 $165,662 $34,789 $34,789 $34,789 $34,789 $34,789 $34,789 $34,789 $34,789 $34,789 $34,789 $130,873 $130,873 $130,873 $130,873 $130,873 $130,873 $130,873 $130,873 $130,873 $130,873 $170,000 $170,000 $170,000 $170,000 $170,000 $170,000 $170,000 $170,000 $170,000 $170,000 $300,873 $300,873 $300,873 $300,873 $300,873 $300,873 $300,873 $300,873 $300,873 $300,873 ($1,700,000) $300,873 $300,873 $300,873 $300,873 $300,873 $300,873 $300,873 $300,873 $300,873 $300,873 22 NPV

1 Diamonds sold per year 9061.03542345432 2 3 1700000 140 4 Initial Investment 5 Cost of machinery 6 7 OCF 8 9 Revenues 10 - Variable Costs 11 - Fixed Costs 12 - Depreciation 13 Income before tax 14 - Taxes 15 Income after tax 16 + Depreciation 17 OCF 18 19 NPV Analysis 20 Project Cash Flows =$B$1.100 =$B$1*40 208000 =$B$5/10 =$B$1.100 =$B$1*40 208000 =$B$5/10 =$B$1.100 =$B$1.40 208000 =$B$5/10 =$B$1*100 =$B$1.100 =$B$1.100 -$B$1.100 =$B$1*100 =$B$1.100 =$B$1.100 =$B$1*40 =$B$1.40 =$B$1640 =$B$1.40 =$B$1*40 =$B$1*40 =$B$140 208000 208000 208000 208000 208000 208000 208000 =$B$5/10 =$B$5/10 =$B$5/10 =$B$5/10 =$B$5/10 =$B$5/10 =$B$5/10 =C9-C10-C11-C12 =D9-D10-D11-D12 =E9-E10-E11-E12 =F9-F10-F11-F12 =G9-G10-G11-G12 =H9-H10-H11-H12 =19-110-111-112 =C13.21% =D1321% =E13.21% =F13.21% =G13.21% =H13.21% =113 21% =C13-C14 =D13-D14 =E13-E14 =F13-F14 =G13-G14 =H13-H14 =113-114 =C12 =D12 =E12 =F12 =G12 =H12 =112 =C15+C16 =D15+D16 =E15+E16 F15+F16 =G15+G16 =H15+H16 =115+116 =J13 21% =J13-J14 =J12 =J15+116 =K13.21% =K13-K14 =K12 =K15+K16 =L13.21% =L13-L14 =L12 =L15+L16 --B5 =C17 =D17 =E17 =F17 =G17 =H17 =117 =J17 =K17 -L17 22 NPV =NPV(12%,C20:L20)+B20