Capital Budgeting - Return-On-Investment

Reviews

Shared by: Jeff Sperry

Tags

excel, template, ROI

Stats

views:: 153
rating:: not rated
reviews:: 0
posted:: 1/3/2008
language:: English
pages:: 0

Public Domain

Capital budgeting—return-on-investment (ROI) analysis Data cell key User data entry or item description Formula cells: Totals are calculated and filled in automatically. Investment overview Project name: Project sponsor: Date of request: General description of benefits: Cash flow and ROI statement BENEFIT DRIVERS Greater margin driven by higher production capacity Improved cycle time benefits: Reduced energy cost due to less running time Reduced labor cost due to less running time Fewer accidents, resulting in less workers' compensation Improved quality benefits: Fewer defects, resulting in less rework Fewer customer returns, resulting in less reprocessing costs Reduced time spent handling customer complaints Total annual benefits Implementation filter Total benefits realized 0 YEAR 1 $500,000 125,000 500,000 100,000 250,000 50,000 50,000 2 $1,000,000 125,000 500,000 100,000 300,000 75,000 75,000 3 $1,250,000 125,000 500,000 100,000 300,000 75,000 75,000 $1,575,000 85% $1,338,750 $2,175,000 90% $1,957,500 $2,425,000 95% $2,303,750 Costs Total Year 0 $1,650,000 Year 0 ($1,650,000) (1,650,000) Year 0 $1,650,000 0 (1,650,000) (1,650,000) Year 0 $1,200,000 400,000 0 50,000 0 $1,650,000 Year 1 $125,000 Year 1 $1,213,750 (436,250) Year 1 $108,696 1,164,130 1,055,435 (594,565) Year 1 $0 0 100,000 25,000 0 $125,000 Year 2 $125,000 Year 2 $1,832,500 1,396,250 Year 2 $94,518 1,480,151 1,385,633 791,068 Year 2 $0 0 100,000 25,000 0 $125,000 Year 3 $125,000 Year 3 $2,178,750 3,575,000 Year 3 $82,190 1,514,753 1,432,563 2,223,632 Year 3 $0 0 100,000 25,000 0 $125,000 Benefits Annual benefit flow Cumulative benefit flow Discounted benefit flow Discounted costs Discounted benefits Total discounted benefit flow Total cumulative discounted benefit flow Initial investment Initial investment Implementation costs Ongoing support costs Training costs Other costs Total costs ROI measures Cost of capital Net present value Return on investment Payback (in years) 15% $2,223,632 66% 1.24 143% 215%