ROI Analysis Instructions Oakland County Department of Information Technology
Page 2 Rev: 02/24/2012
Tips and Tricks
The following items are things you should know about the ROI Analysis template:
1. Tab Protection: All tabs in the ROI Analysis are protected so that formulas and automatic calculations
occur. The tabs are not password protected. To unprotect a tab, click Tools…Protection…Unprotect
Sheet on the menu bar. To re-protect a tab after optional formatting changes have been made, click
Tools…Protection…Protect Sheet on the menu bar. The protection features help maintain the
integrity of the automatic formulas and calculations built in to the ROI Analysis template. It is highly
recommended that tab protection be enabled before saving the ROI Analysis document.
2. Formatting: Because all tabs in the ROI template are protected, Excel formatting features may not be
available unless Tab Protection is disabled.
3. Printing: Each tab has been created to print landscape to make most efficient use of 8-1/2” x 11
standard letter paper. It is not recommended that column widths or row heights be changed as such
changes will likely alter the pre-set, desired print settings. To print the entire workbook, click
File…Print on the menu bar. On the Print dialog box, in the Print what section, click Entire
4. Linking: Don’t forget to link the Cost Detail Tab information to the Cost Summary Tab information and
the Savings Detail Tab information to the Savings Summary Tab information.
5. Maintaining Linked Relationships: Don’t forget that if costs or benefits are added to or deleted from the
ROI Analysis after the initial linking, that maintenance of the linking relationships and formulas may
need to occur.
Maintenance Costs: For costs or benefits that have associated, on-going maintenance, it may be
necessary to calculate the Year 1 maintenance amounts. When provided a starting maintenance
amount for Year 2, divide the amount stated by the annual multiplier to obtain the Year 1 amount.
7. Adding or Deleting Rows in the ROI Analysis template: If more rows are needed when entering cost
and savings information or rows need to be deleted, the specified tab must be unprotected before rows
can be added or deleted.
To add rows, place the cursor on an identified row number so the entire row appears highlighted
and click Insert…Rows on the menu bar. Remember that the new row will insert above the row
where the cursor is placed. This is important to know when adding rows that affect pre-
established formulas in the ROI Analysis template. The affected formulas may require checking
to see if formulas need to be modified or copied from one row to another.
To delete rows, place the cursor on an identified row number so the entire row appears
highlighted and click Edit…Delete from the menu bar. Remember that the highlighted row will
be deleted. This is important to know when deleting rows that affect pre-established formulas in
the ROI Analysis template. The affected formulas may require checking to see if formulas need
to be modified or copied from one row to another.
When completed adding and deleting rows, remember to re-protect the tab. Re-protecting the
tab will help maintain the integrity of the formulas built into the ROI Analysis template.
8. Annual Multiplier Usage: When using the Annual Multiplier in the Savings Detail and Cost Detail Tabs,
do not use the whole number “1” as an annual multiplier. In this instance, leave the annual multiplier