Question

The owner of a company is planning to produce one new product that the company can support financially, but is having trouble deciding between two options. Both products will require upfront costs and have projected cash flows for 3 years. The discount rate is 1 4 %.

Annual Cash Flows:	Scotch	Brandy
Year 0	$(9 0 6, 2 5 0)	$(1, 8 1 2, 5 0 0)
Year 1	$ 3 3 7, 5 0 0	$1 0 2 5, 0 0 0
Year 2	$5 2 5, 0 0 0	$8 1 2, 5 0 0
Year 3	$4 7 5, 0 0 0	$6 7 5, 0 0 0

a) Using IRR, determine which product you should produce.

b) You have concerns regarding the scale problem of IRR. How can this be resolved?

c) Determine the NPV for both products.

d) In what ways can you resolve any issues that arise during the decision making process?

Answer 1

S . Book1 - Excel Sign in - 0 X Share File Home Insert Help Tell me what you want to do General Cut Еe Copy Format Painter Wrap Text Page Layout Formulas Data Review View Calibri V 11 VAA ===> I U - . A. 3 Font General 1 X * A Autosum Paste B Merge & Center - r % , 8.09 * Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Fill- Clear Sort & Find & Filter - Select Editing Clipboard Alignment Number Cells A B C D E F G H I J K L M N O P Q R L Year wNon Scotch Brandy -$9,06,250.00-$18,12,500.00 $3,37,500.00 $10,25,000.00 $5,25,000.00 $8,12,500.00 $4,75,000.00 $6,75,000.00 IRR NPV 21% 20% $1,00,337.33 $ 1,46,860.48 As per IRR formula in excel, IRR for Scotch is 21% and IRR for Brandy is 20%, So we should choose Scotch Product as per IRR. Scale problem derives where the project IRR is high but in amount it shows a very less absolute overall return fron the Project, So we can choose NPV to derive the absolute amount of profit instead of Percentage As per NPV formula in excel, NPV for Scotch is $ 100337.33 and NPV for Brandy is $146860.48, So we should choose Brandy Product as per NPV. Issues arising in the decision making process is that different method is different results for project selection. As we have seen IRR D) is giving Scotch and NPV is giving Brandy. We should choose project on the basis of NPV because IRR is just percentage and NPV shows the profit in the amount. Sheet1 + Ready @ - _ + 100%

Book1 - Excel Sign in - 0 x Share BG. File Home Insert Page Layout Formulas Data Review View Help l Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Function - Used - Time - Reference Trigº Functions Manager Function Library fr * 2 A le - O Calculate Now Tell me what you want to do Define Name Ft Use in Formula Create from Selection Defined Names - Trace Precedents Trace Dependents Remove Arrows - Show Formulas ! Error Checking Evaluate Formula Watch Window Calculation Calculate Sheet Options Calculation Formula Auditing F22 DE F Year Scotch Brandy -906250 337500 525000 475000 -1812500 1025000 812500 675000 3 IRR NPV =IRR(D3:06,0.1) =NPV(14%, D3:D6) =IRR(E3:E6,0.1) =NPV(14%,E3:E6) Sheet1 +