Question

answer using excel please

DDD is debating the purchase of a new digital scanner. The scanner they acquired 3 years ago for $500,000 is worth $150,000 today, and will have a salvage value of $45,000 after 7 more years. The current scanner generates revenues of $300,000 per year. The costs of operating the scanner are $180,000 per year. The company currently has $50,000 invested in operating net working capital. The new scanner will cost $760,000. The new scanner will generate revenues of $485,000 per year. In addition, the costs of operating the new scanner will be $195,000. The new scanner will allow the company to immediately reduce its investment in operating networking capital to $20,000. At the end of 7 years, the new machine will have a salvage value of $140,000. The company's corporate tax rate is 30%, the CCA rate is 20% and the required rate of return is 8%. Assume the asset class remains open. Using net present value (NPV) calculation, determine if the company should purchase the new scanner. Show all work. Most importantly, you need to determine the incremental cash flows. Note: All of Calculations should be rounded to the nearest dollar.

Answer 1

ANS : (a)NPV of Scanner 1 if completes a tenure of 10 years

NPV=$ 1,30.051.54

(b) NPV of scanner 1 if sold in year 3

NPV=$ -1,86,575.98.

(C) NPV of scanner 2 with a shelf life of 7 years

NPV= $ 4,77,004.02

As the total NPV of selling the machine 1 in 3 year and machine 2 is more so we can purchase the new scanner and sell the old one

Increamental NPV= NPV of Machine 1-NPV of Machine2

= $-186575.98+$477004.02= $ 2,90,428.04

Calculation of NPV

• The calculation excel is given below here we will find the following and present the data in excel as per the same in the corresponding year.
• In cash flow we will deduct the 20% of CCA of the scanner value in written down method i.e

= Machine Value*CCA %= CCA Value

Which will be again reduced from the machine value (Machine value - CCA Value)=New Machine Value*CCA Value

It will be calculated year on years

• After calculating the tax we will add the CCA value again so as to get the accurate value of cash year on year.
• for calculating the present value we will use the formula of
• PV= Cash flow of the year/(1+rate of interest)^year
• After Getting the Present value we will add all the present value year on year and we will get the Net Present Value.

- The Excel is as follows for all the Scanners

11) DATA 1

Calculation of machine 1 IF COMPLETES ITS 10 YEARS						INTEREST RATE	8%
YEAR	0	1	2	3	4	5	6	7	8	9	10
COST PRICE OF SCANNER	$ -5,00,000.00
RESIDUAL VALUE											$       45,000.00
REVENUE		$   3,00,000.00	$   3,00,000.00	$   3,00,000.00	$   3,00,000.00	$   3,00,000.00	$   3,00,000.00	$   3,00,000.00	$   3,00,000.00	$   3,00,000.00	$   3,00,000.00
COST OF OPERATING		$ -1,80,000.00	$ -1,80,000.00	$ -1,80,000.00	$ -1,80,000.00	$ -1,80,000.00	$ -1,80,000.00	$ -1,80,000.00	$ -1,80,000.00	$ -1,80,000.00	$ -1,80,000.00
WORKING CAPITAL	$     -50,000.00
CCA@20%		$ -1,00,000.00	$     -80,000.00	$     -64,000.00	$     -51,200.00	$     -40,960.00	$     -32,768.00	$     -26,214.40	$     -20,971.52	$     -16,777.22	$     -13,421.77
GROSS CASH FLOW	$ -5,50,000.00	$       20,000.00	$       40,000.00	$       56,000.00	$       68,800.00	$       79,040.00	$       87,232.00	$       93,785.60	$       99,028.48	$   1,03,222.78	$   1,51,578.23
TAX	$                      -	$         6,000.00	$       12,000.00	$       16,800.00	$       20,640.00	$       23,712.00	$       26,169.60	$       28,135.68	$       29,708.54	$       30,966.84	$       45,473.47
ADD CCA		$   1,00,000.00	$       80,000.00	$       64,000.00	$       51,200.00	$       40,960.00	$       32,768.00	$       26,214.40	$       20,971.52	$       16,777.22	$       13,421.77
NET TAX CASH FLOW	$ -5,50,000.00	$   1,14,000.00	$   1,08,000.00	$   1,03,200.00	$       99,360.00	$       96,288.00	$       93,830.40	$       91,864.32	$       90,291.46	$       89,033.16	$   1,19,526.53
PRESENT VALUE	$ -5,50,000.00	$   1,05,555.56	$       92,592.59	$       81,923.49	$       73,032.57	$       65,531.99	$       59,129.07	$       53,601.95	$       48,781.66	$       44,538.75	$       55,363.91
											NPV	$   1,30,051.54

2)DATA 2

			INTEREST RATE	8%
NPV Calculation of machine 1 IF SOLD ON 3RD YEAR
YEAR	0	1	2	3
COST PRICE OF SCANNER	$ -5,00,000.00
RESIDUAL VALUE				$   1,50,000.00
REVENUE		$   3,00,000.00	$   3,00,000.00	$   3,00,000.00
COST OF OPERATING		$ -1,80,000.00	$ -1,80,000.00	$ -1,80,000.00
WORKING CAPITAL	$     -50,000.00
CCA@20%		$ -1,00,000.00	$     -80,000.00	$     -64,000.00
GROSS CASH FLOW	$ -5,50,000.00	$       20,000.00	$       40,000.00	$   2,06,000.00
TAX	$                      -	$         6,000.00	$       12,000.00	$       61,800.00
ADD CCA		$   1,00,000.00	$       80,000.00	$       64,000.00
NET TAX CASH FLOW	$ -5,50,000.00	$   1,14,000.00	$   1,08,000.00	$   2,08,200.00
PRESENT VALUE	$ -5,50,000.00	$   1,05,555.56	$       92,592.59	$   1,65,275.87
				NPV	$ -1,86,575.98

3)DATA 3

NPV Calculation of machine 2						INTEREST RATE	8%
YEAR	0	1	2	3	4	5	6	7
COST PRICE OF SCANNER	$ -7,60,000.00
RESIDUAL VALUE								$   1,40,000.00
REVENUE		$   4,85,000.00	$   4,85,000.00	$   4,85,000.00	$   4,85,000.00	$   4,85,000.00	$   4,85,000.00	$   4,85,000.00
COST OF OPERATING		$ -1,95,000.00	$ -1,95,000.00	$ -1,95,000.00	$ -1,95,000.00	$ -1,95,000.00	$ -1,95,000.00	$ -1,95,000.00
WORKING CAPITAL	$     -20,000.00
CCA@20%		$ -1,52,000.00	$ -1,21,600.00	$     -97,280.00	$     -77,824.00	$     -62,259.20	$     -49,807.36	$     -39,845.89
GROSS CASH FLOW	$ -7,80,000.00	$   1,38,000.00	$   1,68,400.00	$   1,92,720.00	$   2,12,176.00	$   2,27,740.80	$   2,40,192.64	$   3,90,154.11
TAX	$                      -	$       41,400.00	$       50,520.00	$       57,816.00	$       63,652.80	$       68,322.24	$       72,057.79	$   1,17,046.23
ADD CCA		$   1,52,000.00	$   1,21,600.00	$       97,280.00	$       77,824.00	$       62,259.20	$       49,807.36	$       39,845.89
NET TAX CASH FLOW	$ -7,80,000.00	$   2,48,600.00	$   2,39,480.00	$   2,32,184.00	$   2,26,347.20	$   2,21,677.76	$   2,17,942.21	$   3,12,953.77
PRESENT VALUE	$ -7,80,000.00	$   2,30,185.19	$   2,05,315.50	$   1,84,315.15	$   1,66,371.95	$   1,50,870.16	$   1,37,340.56	$   1,82,605.52
								NPV	$   4,77,004.02