Question

The demand for widgets has been determined to be
10,000 per year. Each widget sells for $4.00 and
costs $2.25 to produce. Using breakeven analysis
and an interest rate of 12%, determine the maximum
purchase price that could be paid for the required
machinery. The machine is expected to last 8 years and
to have a salvage value of 5% of the purchase price.

Please show the answer in excel if possible

Answer 1

Cash Flow from Widget selling for year = No. of widgets * (Selling price - Cost price)

= 10,000 * ($4.00 - $2.25)

= $17,500

Present Value of Cash Flow for 8 years = $17,500 * PV factor @12% for 8 years

= $17,500 * [1 - (1+12%)^-8] / 12%

= $17,500 * 0.596116772 / 0.12

= $86,933.6958

Let Value of Machinery = X

Salvage value = X* 5% = 0.05X

Present Value of Salvage Value = 0.05X / (1+12%)^8 = 0.0201941614 X

At break even point Value of Machinery would be equal to Present Value of Cash Flows and Salvage Value

X = $86,933.6958 + 0.0201941614 X

0.979805839 X = $86,933.6958

X = $88,725.4314

Therefore, value of the machinery is $88,725.43

Calculation of NPV of the Project
Particulars	0	1	2	3	4	5	6	7	8
Initial Investment
Cost of Machinery (A)	-X
Operating Cash Flows
Sales Revenue (B = 10,000 * $4)		40000	40000	40000	40000	40000	40000	40000	40000
Cost of Production (C = 10,000 * $2.25)		22500	22500	22500	22500	22500	22500	22500	22500
Cash Flow from Production (D = B-C)		17500	17500	17500	17500	17500	17500	17500	17500
Terminal Value
Salvage Value (E = -A*5%)									0.05X
Total Cash Flows (F = A+D+E)	-X	17500	17500	17500	17500	17500	17500	17500	17500 +0.05X
Discount Factor @12% (G) 1/(1+12%)^n n=0,1,2,3,4,5,6,7,8	1	0.8928571	0.7971939	0.7117802	0.6355181	0.567427	0.506631	0.452349	0.403883228
Discounted Cash Flows (H = F*G)	-X	15625.0000	13950.8929	12456.1543	11121.5664	9929.9700	8866.0446	7916.1113	7067.95649 + 0.020194161X
NPV	-X + 86933.6959 + 0.020194161X
At break even point, NPV is Zero
-X + $86,933.6958 + 0.0201941614 X = 0
0.979805839 X = $86,933.6958
X = $88,725.4314
Therefore, value of the machinery is $88,725.43