Question

Fill out the income statements for year 1 through year 3

FINC 3310 - Fall 2019 N Learning Objectives on 1. Understand how to use EXCEL Spreadsheet 6 (a) Develop proforma Income Statement Using Excel Spreadsheet 7. (b) Compute Net Project Cashflows, NPV, and IRR 8 (c) Develop problem-solving and critical thinking skills and make long-term investment decisions 11 1) Life Period of the Equipment - 4 years 8) Sales for first year (1) $ 12 2) New equipment cost $(200,000) 9) Sales increase per year 13 3) Equipment ship & install cost $ (35,000) 10) Operating cost (60% of Sales) $ 14 4) Related start up cost $ (5,000) (as a percent of sales in Year 1) 15 5) Inventory increase $ 25,000 11) Depreciation (Straight Line)/YR S 16 6) Accounts Payable increase $ 5,000 12) Marginal Corporate Tax Rate (T 17 7) Equip. salvage value before tax $ 15,000 13) Cost of Capital (Discount Rate) 200,000 5% (120,000) -60% (60,000) 21% 10% 21 ESTIMATING Initial Outlay (Cash Flow, CFO, T=0) CFO CF4 CF1CF2CF3 1 2 3 24 Year 25 Investments: 26 1) Equipment cost 27 2) Shipping and Install cost 28 3) Start up expenses 29 Total Basis Cost (1+2+3) 30 4) Net Working Capital 31 Total Initial Outlay 33 Operations: Sheet1 Sheet2 Sheets

Answer 1

		Present Value(PV) of Cash Flow:
		(Cash Flow)/((1+i)^N)
		i=discount rate=cost of Capital=10%=0.10
		N=Year   of Cash Flow
		Inventory increase	-$25,000
		Accounts payable increase	$5,000
		Net Working Capital Cash flow	-$20,000
			CF0	CF1	CF2	CF3	CF4
	N	Year	0	1	2	3	4
		INVESTMENTS:
	1)	Equipment Cost	-$200,000
	2)	Shipping and installation cost	-$35,000
	3)	Start up expenses	-$5,000
	4)=1)+2)+3)	Total Basis cost	-$240,000
	5)	Net Working Capital	-$20,000
	I=4)+5)	Total Initial Outlay	-$260,000
		OPERATIONS
	a	Sales(Sales in Year(N+1)=1.05*Sales in Year(N)		$200,000	$210,000	$220,500	$231,525
	b=a*60%	Operating Cost		-$120,000	-$126,000	-$132,300	-$138,915
	c	Depreciation		-$60,000	-$60,000	-$60,000	-$60,000
	d=a+b+c	Before Tax Profit		$20,000	$24,000	$28,200	$32,610
	e=d*21%	Taxes		-$4,200	-$5,040	-$5,922	-$6,848
	f=d+e	After tax Operating Profit		$15,800	$18,960	$22,278	$25,762
	g	Add: depreciation (non cash expenses)		$60,000	$60,000	$60,000	$60,000
	H=f+g	Operating Cash Flow		$75,800	$78,960	$82,278	$85,762
		TERMINAL CASH FLOWS:
		Release of working capital					$20,000
		After tax salvage Value=15000*(1-0.21)					$11,850
	T	Total Terminal Cash Flow					$31,850
	CF=I+H+T	Net Cash Flow	-$260,000.00	$75,800.00	$78,960.00	$82,278.00	$117,611.90	SUM
	PV=CF/(1.1^N)	Present Valure	-$260,000.00	$68,909	$65,256	$61,817	$80,331	$16,312.48
	NPV =Sum of PV	Net Present Value(NPV)	16,312.48
		It is Financially acceptable
		NPV is positive
		Internal Rate of Return (IRR)	12.67%	(Using IRR function of excel over the Net Cash Flow)
				Clipboard Font N Alignment Number Styles E40 X f =IRR(E35:135) A B C F G H J | 1) 2) 3) Ε -$200,000 -$35,000 $5,000 -$240,000 -$20,000 -$260,000 4)=1)+2)+3) I =4)+5) b=a*60% d=a+b+c e=d*21% f=dte Equipment Cost Shipping and installation cost Start up expenses Total Basis cost Net Working Capital Total Initial Outlay OPERATIONS Sales(Sales in Year(N+1)=1.05*Sales in Year(N) Operating Cost Depreciation Before Tax Profit Taxes After tax Operating Profit Add: depreciation (non cash expenses) Operating Cash Flow TERMINAL CASH FLOWS: Release of working capital After tax salvage Value=15000*(1-0.21) Total Terminal Cash Flow Net Cash Flow Present Valure Net Present Value(NPV) It is Financially acceptable NPV is positive Internal Rate of Return (IRR) $200,000 -$120,000 $ 60,000 $20,000 -$4,200 $15,800 $60,000 $75,800 $210,000 $126,000 -$60,000 $24,000 $5,040 $18,960 $60,000 $78,960 $ 220,500 $ 132,300 $ 60,000 $28,200 $5,922 $22,278 $60,000 $82,278 $231,525 $ 138,915 -$60,000 $32,610 -$6,848 $25,762 $60,000 $85,762 H=f+g $20,000 $11,850 $31,850 $117,611.90 SUM $80,331 $16,312.48 CF=l+H+T PV=CF/(1.14N) NPV =Sum of PV -$260,000.00 -$260,000.00 16,312.48 $75,800.00 $68,909 $78,960.00 $65,256 $82,278.00 $61,817 | 12.67%! (Using IRR function of excel over the Net Cash Flow) 11