Question

Tampa Company is analyzing a proposed 3-year project using standard sensitivity analysis. The company expects to sell 14,000 units, ±4 percent. The expected variable cost per unit is $10 and the expected fixed costs are $45,000. The fixed and variable cost estimates are considered accurate within a ±5 percent range. The sales price is estimated at $16 a unit, ±5 percent. The project requires an initial investment of $120,000 for equipment that will be depreciated using the straight-line method to zero over the project's life. The equipment can be sold for $42,000 at the end of the project. The project requires $15,600 in net working capital for the three years. The discount rate is 10 percent and tax rate is 34 percent. What is the operating cash flow under the optimistic case scenario?

A $62,346.72
B $55,535.08
C $66,194.76
D $58,576.42
E $72,545.80

Answer 1

Formula sheet

A	B	C	D	E	F	G	H
2
3		Under optimistic scenario, revenue shold be higher side and cost should be lower side i.e.
4		Expected Sales	=14000*1.04
5		Expected Variable cost	=10*0.95
6		Expected Fixed cost	=45000*0.95
7		Sales Price	=16*1.05
8		Tax rate	0.34
9		Initial investment	120000
10		Salvage Value	0
11		Expected Life	3	years
12
13		Depreciation per year	=(Initial investment - Salvage Value) / Expected Life
14			=(D9-D10)/D11
15
16		Operating cashflow can be calculated as follows:
17		Operating cashflow = EBIT*(1-Tax Rate)+Depreciation
18
19		Year	0	1	2	3
20		Revenue		=$D$4*$D$7	=$D$4*$D$7	=$D$4*$D$7
21		Variable Cost		=$D$4*$D$5	=$D$4*$D$5	=$D$4*$D$5
22		Fixed cost		=$D$6	=$D$6	=$D$6
23		Depreciation		=$D$14	=$D$14	=$D$14
24		EBIT		=E20-SUM(E21:E23)	=F20-SUM(F21:F23)	=G20-SUM(G21:G23)
25		Tax Expense		=E24*$D$8	=F24*$D$8	=G24*$D$8
26		EBIT*(1-Tax Rate)		=E24-E25	=F24-F25	=G24-G25
27		Plus Depreciation		=E23	=F23	=G23
28		Operating Cash Flow		=E26+E27	=F26+F27	=G26+G27
29
30		Hence operating cash flow under optimistic scenario is	=E28
31		Thus option (B) is correct.
32