Question

You have been hired as a consultant for Medicals Inc., manufacturer of medical devices. The company projects unit sales for a new dental implant as follows:

Year                 Unit Sales

1                     73,000

2                      86,000

3                      97,000

4                      68,000           

Additional information:

• Production of the implants will require €1,500,000 in net working capital immediately, all of which will be recovered at the end of the project.

• Total fixed costs are €4,200,000 per year, variable production costs are €255 per unit, and the units are priced at €375 each.

• The equipment needed to begin production has an installed cost of €8,500,000. This equipment qualifies as three-year MACRS property (depreciation rates are 33.33% for Year 1, 44.45% for Year 2, 14.81% for Year 3, and 7.41% for Year 4).

• In four years, this equipment can be sold for about 20 percent of its acquisition cost.

• The tax rate is 21 percent and the required return is 24 percent.

Instructions:

1. Complete the pro forma below and determine total cash flows for each year of project’s life. (20 points)

2. Would you recommend to accept or reject the project? Explain your decision. (5 points)

Year	0	1	2	3	4
Sales revenues
Variable costs
Fixed costs
Depreciation
EBIT
Taxes
Net income
Operating Cash Flow
Capital spending
Net Working Capital
After-tax salvage value
Total Cash Flow

Answer 1

Solution:

1. Pro forma is as follows using the MS-Excel.

Pro forma to determine the cash flow of each year of the project
Particulars	0	1	2	3	4
Sales unit		73000	86000	97000	68000
Sales Value		=73000*375	=86000*375	=97000*375	=68000*375
Variable Cost		=C3*255	=D3*255	=E3*255	=F3*255
Fixed cost		4200000	4200000	4200000	4200000
Depreciation		=C18	=D18	=E18	0
EBIT		=C4-C5-C6-C7	=D4-D5-D6-D7	=E4-E5-E6-E7	=F4-F5-F6-F7
Taxes		=C8*21%	=D8*21%	=E8*21%	=F8*21%
Operating cash flow		=C8-C9	=D8-D9	=E8-E9	=F8-F9
Capital Spending	-8500000
Net working capital	-1500000				1500000
After tax salvage value					=B28

The result of the above table is as follows:

Pro forma to determine the cash flow of each year of the project
Particulars	0	1	2	3	4
Sales unit		73000	86000	97000	68000
Sales Value		27375000	32250000	36375000	25500000
Variable Cost		18615000	21930000	24735000	17340000
Fixed cost		4200000	4200000	4200000	4200000
Depreciation		2833050	3778250	1258850	0
EBIT		1726950	2341750	6181150	3960000
Taxes		362659.5	491767.5	1298041.5	831600
Operating cash flow		1364290.5	1849982.5	4883108.5	3128400
Capital Spending	-8500000
Net working capital	-1500000				1500000
After tax salvage value					845418.5

2.

The computation of the free cash flow is as follows using the MS-Excel.

Computation of the free cash flows
Particulars	0	1	2	3	4
Sales value	0	=C4	=D4	=E4	=F4
Less: Variable cost	0	=C5	=D5	=E5	=F5
Contribution	0	=K3-K4	=L3-L4	=M3-M4	=N3-N4
Less: Fixed Cost	0	4200000	4200000	4200000	4200000
Less: Depreciation	0	=C18	=D18	=E18	0
EBIT	0	=K5-K6-K7	=L5-L6-L7	=M5-M6-M7	=N5-N6
Less: Interest	0	0	0	0	0
EBT	0	=K8-K9	=L8-L9	=M8-M9	=N8-N9
Less: Taxes	0	=K10*21%	=L10*21%	=M10*21%	=N10*21%
EAT	0	=K10-K11	=L10-L11	=M10-M11	=N10-N11
Add: Depreciation	0	=K7	=L7	=M7	=N7
Net Cash Flows	0	=K12+K13	=L12+L13	=M12+M13	=N12+N13

The result of the above table is as follows:

Computation of the free cash flows
Particulars	0	1	2	3	4
Sales value	0	27375000	32250000	36375000	25500000
Less: Variable cost	0	18615000	21930000	24735000	17340000
Contribution	0	8760000	10320000	11640000	8160000
Less: Fixed Cost	0	4200000	4200000	4200000	4200000
Less: Depreciation	0	2833050	3778250	1258850	0
EBIT	0	1726950	2341750	6181150	3960000
Less: Interest	0	0	0	0	0
EBT	0	1726950	2341750	6181150	3960000
Less: Taxes	0	362659.5	491767.5	1298041.5	831600
EAT	0	1364290.5	1849982.5	4883108.5	3128400
Add: Depreciation	0	2833050	3778250	1258850	0
Net Cash Flows	0	4197340.5	5628232.5	6141958.5	3128400

Computation of depreciation is as follows:

Calculation of depreciation as per MACRS
Particulars	0	1	2	3
Depreciation rate	0	33.33%	44.45%	14.81%
Depreciation amount	0	2833050	3778250	1258850

Computation of the net salvage value is as follows:

Computation of net salvage value
Particulars	Year 4
The total cost of the asset	8500000
Less: Depreciation	7870150
WDV	629850
Salvage Value (20% of the cost)	1700000
Profit on sales	1070150
Tax paid on profit @ 21%	224731.5
Net Salvage Value	845418.5

Computation of the net present value (NPV) is as follows using the MS-Excel.

Computation of the net present value
Particulars	0	1	2	3	4
Cash flows	=-8500000-1500000	=K14	=L14	=M14	=N14+1500000+845419
Discount rate @ 24%	1	0.806	0.65	0.524	0.423
Present Value	=J19*J20	=K19*K20	=L19*L20	=M19*M20	=N19*N20
NPV	=SUM(J21:N21)

The result of the above table is as follows:

Computation of the net present value
Particulars	0	1	2	3	4
Cash flows	-10000000	4197340.5	5628232.5	6141958.5	5473819
Discount rate @ 24%	1	0.806	0.65	0.524	0.423
Present Value	-10000000	3383056.443	3658351.125	3218386.254	2315425.437
NPV	2575219.259

Since the NPV of the project is positive. Therefore, it is advisable to accept this project.

Note: The formula to calculate the present value factor (PVF) of the discount rate is as follows:

PVF = 1 / (1 + interest rate)ⁿ

n is the number of the year.