ROI Calculator

-You Must Enter Data In The Light Green Boxes -You Can Enter Data (or Replace Data) In The Light Yellow Boxes -The Fields With No Shading Are Automatically Calculated -DO NOT ENTER DATA! Go Through The Entire Workbook, Sheet By Sheet and Enter Data Using The Following Rules: If You Don't Know An Exact Value For A Field Enter your best estimateApplication Description Automation System Investment $125,000 (Total system price including all automation, peripheral equipment, controls, engineering, build, set-up & run-off , and installation supervision at end users) Plant Startup Expenses (if not included in automation system investment) Installation $500 Automation Systems Training $2,000 Internal Labor for Startup and Debug $3,000 Production Support/Service Contracts $0 Combined State and Federal Income Tax Rate 37.3% Corporate Hurdle Rate for Investment Decisions 15.0% Capital Evaluation Duration In Years (Enter "5" or "7") 5 Corporate Discount Factor /Weighted Average Cost of Capital 5.0% Corporate Payback Period (Months) for this Application 24 Financial Inputs Proposal 10xxxx Page 1 of 9 The information contained herein is proprietary and confidential, and should not be shared outside "nickname" 2/2/2008 -5:21 AMApplication Description Current Number of Laborers per Shift: 1.0 Operators 1.0 Maintenance Technicians Material Handlers Automation System Number of Laborers per Shift: 0.1 Operators Maintenance Technicians 0.1 Material Handlers Number of Shifts: 2.0 Annual Hours Per Laborer 2,000 Production Hours Per Day 8.0 Production Days Per Week 5.0 Production Weeks Per Year 50.0 Current Annual Labor Hours: 4,000 Automation System Annual Labor Hours: 400 Annual Labor Savings (Hours): 3,600 Burdened Hourly Rate for Laborers: Operators $14.00 Maintenance $20.00 Technicians $26.00 Material Handlers $18.00 Current Annual Labor Cost: $56,000 Automation System Annual Labor Cost: $10,400 Annual Labor Savings (Dollars): $45,600 Annual Lost Time and Disability Savings: $2,280 Annual Average Percentage Rate: 5% Labor Savings Proposal 10xxxx Page 2 of 9 The information contained herein is proprietary and confidential, and should not be shared outside "nickname" 2/2/2008 -5:21 AMApplication Description Total Additional Number of PM Personnel Assigned: 0.2 Burdened Hourly Rate for PM: $20.00 Automation PM Man-Hours per Year 96 Annual PM Labor Savings/(Cost): ($1,920) Preventive Maintenance (PM) Labor Cost Proposal 10xxxx Page 3 of 9 The information contained herein is proprietary and confidential, and should not be shared outside "nickname" 2/2/2008 -5:21 AMApplication Description Current Systems Annual Production 3,206,400 Production Rate Per Hour 960.0 Total Available Production Hours Per Year 4,000 Less Scheduled Downtime per shift (lunches, breaks, etc. -hrs) 1.00 (500) Less Annual Unscheduled Downtime (avg. percentage) 4.0% (160) Less Annual Change-Over (hours) 0 Net Annual Production Hours 3,340 Automation Systems Annual Production 3,321,600 Production Rate Per Hour 960.0 Total Available Production Hours Per Year 4,000 Less Daily Scheduled Downtime (lunches, breaks, etc. -hrs) 1.00 (500) Less Annual Unscheduled Downtime (avg. percentage) 1.0% (40) Less Annual Change-Over (hours) 0 Net Annual Production Hours 3,460 Increased Annual Production 115,200 Profit Per Unit $1.00 Increased Annual Profit $115,200 Production Increase Proposal 10xxxx Page 4 of 9 The information contained herein is proprietary and confidential, and should not be shared outside "nickname" 2/2/2008 -5:21 AMApplication Description Current Systems Annual Scrap 167 Scrap Rate 5.0% Net Annual Production Hours (Current System) 3,340 Automation Systems Annual Production 35 Scrap Rate 1.0% Net Annual Production Hours (Automation System) 3,460 Increased Annual Production 132 Profit Per Unit $1.00 Scrap Reduction Savings $132 Scrap Reduction Savings Proposal 10xxxx Page 5 of 9 The information contained herein is proprietary and confidential, and should not be shared outside "nickname" 2/2/2008 -5:21 AMApplication Description Miscellaneous Saving #1 $0 Miscellaneous Saving #1 $0 Miscellaneous Saving #1 $0 Total Miscellaneous Savings $0 Miscellaneous Saving This Sheet To Be Used For Unique Savings Opportunities Specific To Your Application Proposal 10xxxx Page 6 of 9 The information contained herein is proprietary and confidential, and should not be shared outside "nickname" 2/2/2008 -5:21 AMApplication Description Operator Labor Savings : $45,600 Lost Time and Disability Savings : $2,280 Workers Compensation Premiums ($1.3 per $100 of payroll) $593 System Preventive Maintenance: ($1,920) Production Increase Value: $115,200 Scrap Reduction Savings: $132 Miscellaneous Savings: $0 Total Annual Profit Increase: $161,885 Annual Savings Summary Proposal 10xxxx Page 7 of 9 The information contained herein is proprietary and confidential, and should not be shared outside "nickname" 2/2/2008 -5:21 AMApplication Description Total Initial Investment: $130,500 Total Annual Profit Increase (Before Income Taxes): $161,885 Weighted Average Cost of Capital /Discount Factor: 5.0% Corporate Hurdle Rate: 15.0% 5 Year IRR Generated From Project: 74.7% (True Interest Return Generated by the Project) (If IRR > Hurdle Rate, Accept the Project) (If IRR < Hurdle Rate, Reject the Project) 5 Year NPV: $346,397 (Present Value of Net Annual Cash Flows) (IF NPV > $0, Accept the Project) (IF NPV < $0, Reject the Project) Payback In Months (Before Income Taxes): 9.7 Investment Summary Proposal #10xxxx Page 8 of 9 T he information contained herein is proprietary and confidential, and should not be shared outside "nickname" 2/2/2008 -5:21 AMApplication Description Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 5 Year (-) (130,500) 0 7 Year (-) 0 0 Total Equipment Investment (130,500) 0 0 0 0 0 0 0 Cash Flow Data: Operator Labor Savings 45,600 45,600 45,600 45,600 45,600 45,600 45,600 Annual lost time and disability savings 2,280 2,280 2,280 2,280 2,280 2,280 2,280 Workers Comp Premiums 593 593 593 593 593 593 593 System Preventive Maintenance (1,920) (1,920) (1,920) (1,920) (1,920) (1,920) (1,920) Increased Annual Profit (from Production Increase) 115,200 115,200 115,200 115,200 115,200 115,200 115,200 Scrap Savings 132 132 132 132 132 132 132 Miscellaneous Savings 0 0 0 0 0 0 0 Depreciation (MACRS 7 on purchases made after 9/10/01 and Before 9/11/04) (52,204) (22,372) (15,977) (11,410) (8,158) (8,148) (8,158) Net Pre-Tax Profit: 109,681 139,513 145,908 150,475 153,727 153,736 153,727 Income Taxes @37.3% (40,856) (51,969) (54,351) (56,052) (57,263) (57,267) (57,263) Add Depreciation (+) 52,204 22,372 15,977 11,410 8,158 8,148 8,158 Net Annual Cash Flow (after tax): (130,500) 121,029 109,916 107,534 105,833 104,621 104,618 104,621 Cumulative Cash Flow: (130,500) (9,471) 100,445 207,979 313,812 418,433 523,051 627,673 Weighted Avg Cost of Capital @5.0% 1.00000 0.95238 0.90703 0.86384 0.82270 0.78353 0.74622 0.71068 Present Value of Annual Cash Flows (130,500) 115,265 99,697 92,892 87,069 81,974 78,068 74,352 Cumulative Discount Cash Flow (130,500) (15,235) 84,463 177,355 264,424 346,397 424,465 498,817 Time Period NPV IRR 5 Years $346,397 74.7% 7 Years $498,817 77.5% 1.09 Years After Tax Payback Financial Analysis Today (Year 0) Proposal 10xxx Page 9 of 9 The information contained herein is proprietary and confidential, and should not be shared outside "nickname" 2/2/2008 -5:21 AMApplication Description Financial Analysis (continued) Payback 1.00 0.00 0.00 0.00 0.00 0.00 0.00 Calculation Detail: FALSE 0.09 0.00 0.00 0.00 0.00 0.00 Formula Checks: 0 (Should = 0 ) 0 7,332 7,712 IRR 7YR #NUM! 0.7747 GUESS 0.50 0.50 Depreciation Rates: 0.40003 0.17143 0.12243 0.08743 0.06251 0.06244 0.06251 (0.0312) (Straight-Line -Change if Applicable) Formula Checker Should = 0 Deprec. Deprec. Deprec. Deprec. Deprec. Deprec. Deprec. Investment Year 1 (52,204) (22,372) (15,977) (11,410) (8,158) (8,148) (8,158) 4,074.2100 Investment Year 2 0 0 0 0 0 0 0.0000 Investment Year 3 0 0 0 0 0 0.0000 Investment Year 4 0 0 0 0 0.0000 Investment Year 5 0 0 0 0.0000 Investment Year 6 0 0 0.0000 Investment Year 7 0 0.0000 Total Depreciation (52,204) (22,372) (15,977) (11,410) (8,158) (8,148) (8,158) Inflation Factors Enter Rate 0.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% Proposal 10xxx Page 9 of 9 The information contained herein is proprietary and confidential, and should not be shared outside "nickname" 2/2/2008 -5:21 AM