Question

NEW PROJECT ANALYSIS Holmes Manufacturing is considering a new machine that costs $240,000 and would reduce pretax manufacturing costs by $90,000 annually. Holmes would use the 3-year MACRS method to depreciate the machine, and management thinks the machine would have a value of $25,000 at the end of its 5-year operating life. The applicable depreciation rates are 33%, 45 % , 15 %, and 7%. Net operating working capital would increase by $22,000 initially, but it would be recovered at the end of the project's 5-year life. Holmes's marginal tax rate is 40 %, and a 13% WACC is appropriate for the project. a. Calculate the project's NPV. Round your answer to the nearest cent. Calculate the project's IRR. Round your answer to two decimal places. % Calculate the project's MIRR. Round your answer to two decimal places. % Calculate the project's payback. Round your answer to two decimal places. years minus 20%. What would the NPV be under each of these situations? Round your b. Assume management unsure about the $90,000 cost savings-this figure could deviate by as much as plus answers to the nearest cent. Negative amount should be indicated by a minus sign. 20% savings increase. $ 20% savings decrease. $

Answer 1

As per rules I am answering the first 4 subparts of the question

NPV	$23,981.36
IRR	16.80%
MIRR	15.00%
Payback	3.18

The Operating cash flows are

OCF		MACRS 3 year
Year	Cash flows	Depreciation	EBIT	Tax	PAT	OCF
1	90000	-79200	10800	-4320	6480	85680
2	90000	-108000	-18000	7200	-10800	97200
3	90000	-36000	54000	-21600	32400	68400
4	90000	-16800	73200	-29280	43920	60720
5	90000	0	90000	-36000	54000	54000

Net and cumulative cash flows are

Year	Initial cash flow	OCF	Working capital	Salvage	Net cash flows	Cumulative CF
0	-240000		-22000		-262000	-262000
1		$85,680.00			85680	-176320
2		$97,200.00			97200	-79120
3		$68,400.00			68400	-10720
4		$60,720.00			60720	50000
5		$54,000.00	22000	15000	91000	141000

WORKINGS

Book1- Excel X AutoSave ( Off Sign in Tell me what you want to do File Formulas Review View Help Share Home Insert Draw Page Layout Data 8 General A A aWrap Text Calibri Conditional Format as Formatting BIU Cell Insert Delete Format Sort & Find & Paste A Merge & Center $% , Filter Select Table Styles Clipboard Alignment Number Styles Cell Editing Font 240000 B2 C F G A H K 1 INPUTS 2 Initial cost 3 Tax rate 240000 0.4 Year Initial cash flow OCF Working capital Salvage 22000 Cumulative C Net cash flows SUMID3:G3) -SUM(D4:G4) 40000 H3 4 Selling price Working capital 6 1 =l1 25000 -13+ H4 SUMID6:G6 5+H6 0.13 -SUMID7:G77 - 25000 ( 1-0.4) -SUM(D8 :G8 ) 7Annual cash flow 90000 120 =16 H7 121 22000 17+H8 10 -NPV(B6,H4 :H8)+H3 NPV MIRR(H3:H8.B6.B6) 12 13 Payback -C6-16/HZ 14 15 Year 16 Depreciation rate OCF MACRS 3 year Year Cash flows OCF 0.33 Depreciation EBT Tax PAI SUMID18:E18)F195400 -F18+G18 -F19 G19 =F20+G20 -F21+G21 18 3 SDS3817 H18-F18 0.15 19 4 -H19-E19 = H20- E20 -H21-E21 - SDS3 . 818 -SUMID19:E19)F19 40 % =SUM(D20: E20) F20"40% 0.07 90000 20 5 90000 90000 -$D$3*B19 -SD$3 B20 21 -SUMID21: E21 )-F21 40 % 22 23 Sheet1 Sheet2 X Sheet3 (+ Listening Ready x P ENG 12:39 AM