XYZ Yogurt Company is considering developing a new flavor of yogurt- root beer f
ID: 2761009 • Letter: X
Question
XYZ Yogurt Company is considering developing a new flavor of yogurt- root beer flavored yogurt. the following are the base case assumptions for this product:
Life = 3 years
Capital Outlay = $1,000,000
Depreciation = 3 straight line with $100,000 salvage
Unit Sales= 20,000 (year 1); 50,000 (year 2); 70,000 (year 3)
Price = $45 per case year 1 with $5 increase per year thereafter
Root beer cost = 20% of revenue
yogurt costs = 25% of revenue
labor costs = $4 per case
taxes = 40%
cost of capital = 25%
Using Excel, calculate a) annual cash flows b) npv c)irr d)payback period e)discounted paback
please show all the formulas used!
Explanation / Answer
Answer
Answer (a) & (b)
Figures in $
Year
Unit Sales
Sales price
Sales
Root Beer Cost
Yogurt Cost
Labor costs
Net profit After tax
Depreciation Tax Benefit
Capital outlay
Cash Flow
Disc Rate : 25%
Present Value
A
B
C
D
E
F
G
H
I
J
K
A*B
C*0.2
C*0.25
A *4
(C+D+E+F)*0.6
((1000000-100000)/3)*0.4
G+H+I
J*K
0
-1000000
-1000000
1
-1000000
1
20000
45
900000
-180000
-225000
-80000
249000
120000
369000
0.8
295200
2
50000
50
2500000
-500000
-625000
-200000
705000
120000
825000
0.64
528000
3
70000
55
3850000
-770000
-962500
-280000
1102500
120000
100000
1322500
0.512
677120
Net Present value
500320
Answer (c)
Internal rate of return is a discount rate that makes the net present value (NPV) of all cash flows from a particular project equal to zero.
We have to Find IRR by trial and error method by assuming different discount rates
Suppose Discount Rate is 50.31567%
Figures in $
Year
Unit Sales
Sales price
Sales
Root Beer Cost
Yogurt Cost
Labor costs
Net profit After tax
Depreciation Tax Benefit
Capital outlay
Cash Flow
Disc Rate : 50.31567%
Present Value
A
B
C
D
E
F
G
H
I
J
K
A*B
C*0.2
C*0.25
A *4
(C+D+E+F)*0.6
((1000000-100000)/3)*0.4
G+H+I
J*K
0
-1000000
-1000000
1
-1000000.00
1
20000
45
900000
-180000
-225000
-80000
249000
120000
369000
0.665267
245483.39
2
50000
50
2500000
-500000
-625000
-200000
705000
120000
825000
0.44258
365128.25
3
70000
55
3850000
-770000
-962500
-280000
1102500
120000
100000
1322500
0.294434
389388.31
Net Present value
0
IRR
50.31567%
Answer (d)
Year
Cash Flow As per Answer (b)
balance cash flow
Year
0
-1000000
1
369000
-631000
1
2
825000
(631000/825000)
0.76
3
1322500
Payback Period (Years)
1.76
Answer (e )
Year
Discounted cash flow As Per Answer (b)
balance cash flow
Year
0
-1000000
1
295200
-704800
1
2
528000
-176800
1
3
677120
(176800/677120)
0.261106
Discounted Payback Period (Years)
2.26
Figures in $
Year
Unit Sales
Sales price
Sales
Root Beer Cost
Yogurt Cost
Labor costs
Net profit After tax
Depreciation Tax Benefit
Capital outlay
Cash Flow
Disc Rate : 25%
Present Value
A
B
C
D
E
F
G
H
I
J
K
A*B
C*0.2
C*0.25
A *4
(C+D+E+F)*0.6
((1000000-100000)/3)*0.4
G+H+I
J*K
0
-1000000
-1000000
1
-1000000
1
20000
45
900000
-180000
-225000
-80000
249000
120000
369000
0.8
295200
2
50000
50
2500000
-500000
-625000
-200000
705000
120000
825000
0.64
528000
3
70000
55
3850000
-770000
-962500
-280000
1102500
120000
100000
1322500
0.512
677120
Net Present value
500320
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.