Automation ROI calculator excel

Written By

Guntapudi Udayravichandra

Automation ROI calculator excel is a very important topic since every Manufacturing Factories turning their head to Automation in place of Manual/Semi Automation and also investment to automation to be made wisely.

Because automation will provide Maximum of Efficiency, Quality, Reduction of Manpower, and Process time to meet customer requirements. Everything we are talking about after the Deployment of Automation into the Factory/Shop floor.

To implement or deploy of Automation requires Investment, New Technical persons, Space, Power consumption and more over approval from the management to deploy this Automation.

Mostly Deployment of Automation is based on VOC(Voice of Customer), VOB(Voice of Business), Production requirements and Mostly IE/ME people use for Efficiency improvements.

The new Automation may replace old Manual/Semi Automation.

Here IE People should work on the Calculation of “ROI”. Which is Return On Investment.

Automation ROI calculator excel helps on decision on investment on automations easily. Also how this calculations are done is also explained.

In this Article we shall discuss about the following

1. What is ROI?
2. What all need to Consider in ROI Calculations
3. And and Excel template for calculating ROI

What is ROI?

ROI (Return on Investment), is a measurement system which will provide, within how many years or period of time that we can get back Investment and go into profit.

For Example,

Mr. X bought a Car for Transport business which costs Rs.10 Lacs.

How Mr. X will Calculate ROI for this Car.

Mr. X calculated all Expenses (Petrol, Spares, Accessories, Repairs)  and he will have a profit of Approx. 3,60,000rs/year.

So he can Calculate by 10,00,000/3,60,000= 2.7Years.

By Approx. 3yrs he will get his ROI back and from then he will be in Profits.

This is how a basis ROI Calculation can be done. But Considering, Production Machinery this calculation varies on yearly.

What Elements need to Consider in ROI Calculations and Calculation Method

• Precise Data: Data is one of the key elements in calculation of ROI for anything. One should have clear data before starting this ROI Calculations.

• Machine Proposition: Based on the company policy i.e., how many years a machine can be used for fixed assets and then it will be scrapped or not use. We need to calculate the machine proportional cost.

Calculation example: If any Machine costs 20lakhs and company will consider lifetime of machine is 10years. Then Proportional cost is 2lakhs per year. This should be consider while calculation.

• Power Consumption: Power Consumption is another key element which need to consider in Automation ROI. If we are considering one ore more suppliers for any Automation procurement this will help in Differentiating b/w existing and new machinery.

Calculation example: We are using a existing machine which will consume “X” kilowatts of Power and for new automation how much power it is consuming we need to calculate. Then Everything we need to convert into money by calculating present power charges.

• Spares/Tools Cost: Considering the machine utilization. In this we must know some key things. 

Calculation:

What is the lifetime of key spares…?

What is the lead time to receive of key spares…?

Cost of Key Spares of all Suppliers if we have more than one suppliers.

By this need to calculate how many spares we need to have stock and what will be the cost of overall. This will also impact OEE, if the spares are not available for long periods.

• Manpower Requirement:  Another Key Element in this Considerations. It should be calculated based on previous utilization and present reduction in Manpower. And need to calculate the cost of before and after manpower.

Calculation: Manpower Cost based on the Monthly Salary of Operator (Present cost) and no of operators used before and after. For Example we are using 5 operators per Manual machine and 2operators for Automation. Then we need to calculate per year how much will be saved.

Manual: 5 operators: 5 * 10000(Min Salary) * 12(Months)= Rs.6,00,000/-

Automation: 2 operators: 2 * 10000(Min Salary) * 12(Months) = Rs.2,40,000/-

Monthly Saving: 3,60,000/-

• Others:

Tools Cost for Manual: What are the excess tools used before i.e., Molds, Dies, Tools and other Manual tools which are used before need to calculate the cost of that.

Output: What is before and after output, The hourly output should be not less 2 times the manual machine. If it is less than 2 times of Manual machine, at first we can reject the automation.

Machine Replacement: How many Manual machines are replaced by the Auto machine. For example, Manual/Semi-Auto produced 200pcs/hour. If Automation can produce 400pcs/hour. Then 1 machine can replace 2 Manual machines. But here we need to consider Automation for 2shifts since Manual machine can be operated only 1shift. So 1 Automation can replace 4manual machines/Day.

Service: Service period also one of the other category which need to be considered in Comparison. If any Breakdown happens how much time it is taking to complete onsite service. If there is much days to complete we need to calculate the cost of down time of Operators, Production loss. This is also one of the considerations but most of the factories won’t consider during ROI Calculations.

By all above considerations we can get clear idea on how many years the ROI is Calculated for new machine. Please find the attached Excel to know the ROI Calculations. This can be calculated by 1Supplier, 2 Suppliers, 3Suppliers or more. To know which is best.

ROI Calculator Excel Template

Which Automation to Select from ROI?

Automation with least ROI to be selected. In our example shown in the image above, its Automation 2.

Thankyou.

Guntapudi Udayravichandra @guntapudi

View Profile