Question

(a) Develop proforma Income Statement Using Excel Spreadsheet
(b) Compute Net Project Cashflows, NPV, and IRR
1) Life Period of the Equipment = 4 years				8) Sales for first year (1)			$200,000
2) New equipment cost			$(200,000)	9) Sales increase per year			5%
3) Equipment ship & install cost			$(35,000)	10) Operating cost (60% of Sales)			$(120,000)
4) Related start up cost			$(5,000)	(as a percent of sales in Year 1)			-60%
5) Inventory increase			$25,000	11) Depreciation			Use 3-yr MACRIS
6) Accounts Payable increase			$5,000	12) Marginal Corporate Tax Rate (T)			21%
7) Equip. salvage value before tax			$15,000	13) Cost of Capital (Discount Rate)			10%
ESTIMATING Initial Outlay (Cash Flow, CFo, T= 0)
			CF0	CF1	CF2	CF3	CF4
Year			0	1	2	3	4
Investments:
1) Equipment cost
2) Shipping and Install cost
3) Start up expenses
Total Basis Cost (1+2+3)
4) Net Working Capital
Total Initial Outlay
Operations:
Revenue
Operating Cost
Depreciation
EBIT
Taxes
Net Income
Add back Depreciation
Total Operating Cash Flow				XXXXX	XXXXX	XXXXX	XXXXX
Terminal:
1) Change in net WC				$-	$-	$-	$20,000
2) Salvage value (after tax)			Salvage Value Before Tax (1-T)				XXXXX
Total							XXXXX
Project Net Cash Flows			$-	$-	$-	$-	$
NPV =			IRR =			Payback=
Profitability Index =						Discounted Payback =

Answer 1

Hi,

Please find the attched. Assumptions are made as per the information given in the question.

Thanks.