capital budgeting template
DESCRIPTION
CBTRANSCRIPT
IMCIM&C GUANO PROJECT
The following worksheet contains the same information as Tables 6-2 through 6-7 in Brealey-Myers. You can use this worksheet as a template for most capital budgeting problems.
Cost of capital20%
Year0.01.02.03.04.05.06.07.0
Depreciation (for taxes)20.00%32.00%19.20%11.52%11.52%5.76%Net working capital5501,2893,2614,8903,5832,002Change in net working capital5507391,9721,629(1,307)(1,581)(2,002)Net property, plant & equipment10,0008,0004,8002,8801,7285760.0
Sales52312,88732,61048,90135,83419,717Less COGS(837)(7,729)(19,552)(29,345)(21,492)(11,830)Less depreciation(2,000)(3,200)(1,920)(1,152)(1,152)(576)Less other costs(4,000)(2,200)(1,210)(1,331)(1,464)(1,611)(1,772)EBIT(4,000)(4,514)7489,80716,94011,5795,539Less taxes @ 35%1,4001,580(262)(3,432)(5,929)(4,053)(1,939)EBIAT(2,600)(2,934)4866,37511,0117,5263,600Plus depreciation0.02,0003,2001,9201,1521,1525760.0Less change in net working capital0.0(550)(739)(1,972)(1,629)1,3071,5812,002Cash flow from operations(2,600)(1,484)2,9476,32310,5349,9855,7572,002Less capital investment(10,000)0.00.00.00.00.00.00.0Plus capital disposal0.00.00.00.00.00.00.01,949Less taxes on capital disposal0.00.00.00.00.00.00.0(682)Net cash flow(12,600)(1,484)2,9476,32310,5349,9855,7573,269Present value factor100%83%69%58%48%40%33%28%PV of cash flow(12,600)(1,237)2,0473,6595,0804,0131,928912Cumulative PV(12,600)(13,837)(11,790)(8,131)(3,051)9622,8903,802Net present value3,802