Question

The Ironworks Pegs Corporation, facing a market that is requiring more and more of the square-type pegs as opposed to the round one, is considering a project to start producing square pegs to meet the expected growth in the market demand. In order to produce the new pegs, the company needs to replace an existing old machine that produces round pegs with a new one. The new machine costs $150,000 (including shipping and handling). The old machine has been fully depreciated and the new one would be depreciated on a straight-line basis over its estimated useful life of 15 years. If the decision is made to go ahead with the project the old machine will be sold for $10,000. Annual revenues are expected to be $132,000; cost of goods sold $41,000; operating costs (excluding depreciation) $35,000. The existing operating profit (EBIT) from the old machine is $5,000 per year (which is assumed to continue for the following ten years if the new project does not get the green light). The company estimates the actual productive life of the project at 10 years, after which the new machine would be sold for a salvage value of $80,000. The initial net working capital needed for the expanded operations is estimated at $25,000. The NWC will rise to $35,000 by the end of year one, then to $50,000 by the end of year two. No additional changes in NWC are expected for years three through eight. By the end of year 9, the NWC would be reduced to $30,000 (no theft, spoilage, or obsolescence is assumed to have occurred by the end of year ten). The way the company made all these estimates is by conducting a technical and economic feasibility study that cost $35,000. It also cost $15,000 to market-test the new widgets. The company’s marginal tax rate is 40%. The required rate of return on this investment is 15%.

1. Calculate the net initial investment needed for the new peg machine.

2. Calculate the expected after-tax salvage value of the new machine when the time comes for it to be sold.

3. Estimate all the relevant annual free cash flows and show them on a timeline using the Excel spreadsheet.

4. Use the six capital budgeting decision criteria to make a decision as to whether to go ahead with the project. Use the Excel finance functions. Assume a 6-year acceptable payback period.

Answer 1

	NET INITIAL INVESTMENT NEEDED:
	Cost of Feasibility study and market test are Sunk Costs and not relevant for this analysis
	New Machine Costs	$150,000
	Less:Salvage value of old machine	$10,000
	Net investment for new machine	$140,000
	Add:Initial net working capital	$25,000
	Net Initial Investment Needed	$165,000
	EXPECTED AFTER TAX SALVAGE VALUE OF NEW MACHINE
	Annual Depreciation of new machine	$10,000	(150000/15)
	Accumulated depreciation in 10 years	$100,000	(10000*10)
	Book Value at the end of 10 years	$50,000	(150000-100000)
	Salvage Value at end of 10 years	$80,000
	Gain on salvage=80000-50000=	$30,000
	Tax on Gain =40%*30000=	$12,000
	After tax Salvage Value =80000-12000	$68,000
	YEARWISE CASH FLOW:
	Present Value of Cash Flow=(Cash Flow)/((1+i)^N)
	i=discount rate =required retur =15%=0.15
	N=Year of cash flow
N	YEAR	0	1	2	3	4	5	6	7	8	9	10
A	Initial Cash Flow	($165,000)
B	Annual Revenue		$132,000	$132,000	$132,000	$132,000	$132,000	$132,000	$132,000	$132,000	$132,000	$132,000
C	Cost of goods sold		($41,000)	($41,000)	($41,000)	($41,000)	($41,000)	($41,000)	($41,000)	($41,000)	($41,000)	($41,000)
D	Operating Costs(excluding depreciation)		($35,000)	($35,000)	($35,000)	($35,000)	($35,000)	($35,000)	($35,000)	($35,000)	($35,000)	($35,000)
E	Depreciation Expense		($10,000)	($10,000)	($10,000)	($10,000)	($10,000)	($10,000)	($10,000)	($10,000)	($10,000)	($10,000)
F	Earning Before Taxes		$46,000	$46,000	$46,000	$46,000	$46,000	$46,000	$46,000	$46,000	$46,000	$46,000
G	Loss of Earning from old machine		($5,000)	($5,000)	($5,000)	($5,000)	($5,000)	($5,000)	($5,000)	($5,000)	($5,000)	($5,000)
H	Net Earning Before Taxes		$41,000	$41,000	$41,000	$41,000	$41,000	$41,000	$41,000	$41,000	$41,000	$41,000
I=H*40%	Tax Expenses		($16,400)	($16,400)	($16,400)	($16,400)	($16,400)	($16,400)	($16,400)	($16,400)	($16,400)	($16,400)
J	After tax operating income		$24,600	$24,600	$24,600	$24,600	$24,600	$24,600	$24,600	$24,600	$24,600	$24,600
K	Add: Depreciation (non cash expenses)		$10,000	$10,000	$10,000	$10,000	$10,000	$10,000	$10,000	$10,000	$10,000	$10,000
L	Operating Cash Flow		$34,600	$34,600	$34,600	$34,600	$34,600	$34,600	$34,600	$34,600	$34,600	$34,600
M	Net Working Capital (NCW)Required	$25,000	$35,000	$50,000	$50,000	$50,000	$50,000	$50,000	$50,000	$50,000	$30,000	$0
P	Additional Cash Flow for change in NCW		($10,000)	($15,000)	$0	$0	$0	$0	$0	$0	$20,000	$30,000
Q	Terminal Cash Flow due to salvage											$68,000
R=A+L+P+Q	NET CASH FLOW	($165,000)	$24,600	$19,600	$34,600	$34,600	$34,600	$34,600	$34,600	$34,600	$54,600	$132,600
	CUMULATIVE NET CASH FLOW	($165,000)	($140,400)	($120,800)	($86,200)	($51,600)	($17,000)	$17,600	$52,200	$86,800	$141,400	$274,000	SUM
PV=R/(1.15^N)	Present Value of NET CASH FLOW	($165,000)	$21,391	$14,820	$22,750	$19,783	$17,202	$14,959	$13,007	$11,311	$15,521	$32,777	$18,521
NPV=Sum of PV	NET PRESENT VALUE	$18,521
	Pay back period =Period when Cumulative cash flow=0
	Payback Period =5+(17000/34600)=	5.49	YEARS
	Profitability Index=(NPV+Initial Investment)/Initial Investment
	Profitability Index=(18521+165000)/165000=	1.11
	Internal Rate of Return	17.25%	(Using IRR function of excel over NET CASH FLOW)