Video #5 -"Financial Comparison" (III): Payback and ROI
In this video, we will work on calculating payback and ROI.
The last two things we need to work on is "Payback period for proposed system (year)" and "Return on Investment (%)". To figure out payback and ROI, we need to know the total savings from the proposed system on operating costs. To calculate 'Total", we can take advantage of the "Auto Sum" function in EXCEL. To do this,
a) Click on the cell where data for "Total" will appear, such as cell "B20" for total operating cost of existing system.
b) Click on the "AutoSum" icon located on the upper right corner. Highlight all cells where data need to be added, in this case, B12 through B18. Note in the formula bar, we see "=SUM(B12:B18)", as shown below.
c) Press "Enter" key! Use the same approach, we can find "total of proposed system" on operating costs and "total of savings from proposed system" on operating cost.
d) To figure out payback, since we already figured out "total capital cost" (in cell D9) and "total savings from proposed system" (in cell F20"), we just need to enter the formula "=D9/F20".
e) To figure out ROI, since we already figured out payback (in cell D29), we just need to enter the formula "=1/D29". The completed financial comparison should look like below:
The above completed Excel worksheet for Example #1 can be found under Unit 3 learning resources by the name of "Lighting Example 1".
An interesting question to think about....
Is it possible to have a negative operational saving for a proposed P2 option? Yes, absolutely! It is important to remember not every P2 alternative will make financial sense, that is why we have to do a financial analysis for each P2 alternative! If we do come across with a P2 alternative that resulted in a negative operational saving, EXCEL will tell us we have a negative payback! (payback = capital cost (a positive number)/annual savings (a negative number)). A Friendly Note On EXCEL: When you get a negative number in EXCEL, it automatically puts the number in parentheses.
What does a negative payback in EXCEL mean? Let's go back to the basic...when we have a negative operational saving, it means after we spend some money up front as capital, we continue to lose money each year afterwards, are we ever going to offset the initial capital investment? ......give you 2 seconds..... NEVER!!!
If you encounter such a situation on your job, make sure you write a note next to the negative payback calculated from EXCEL that "There will never be a pay-back moment for this P2 alternative!". EXCEL will also tell us that we have a negative ROI implying we are losing money with this investment and unfortunately it is TRUE!