RBC Excel Tool Guideline
Guideline how to use the free-to-download Cost-Benefit Analysis Tool
You can use windows Excel 2007, 2010, and 2013. Never tried Mac's version, any feedback is welcome, but remember I only tested the tool's macros with these 3 versions.
When you open the template file, you had to allow the macros because without it the tool will not work at all. There are macros to ensure the cell references stay correct, to keep formats right, to reduce visible columns to avoid 'bloaty' sheets, update pivot tables on hidden sheets, and to show correct charts on the report sheet. Obviously, without the macros you will get wrong results.
If you are unaware of the risk with enabled macros, please learn the security issues. If you have Excel's security mode set to disable macro, you probably get a security message about that and a button 'Enable Content' or 'Options'.
Excel file include guideline
This is a simple walkthrough for a initial description, the tool itself has a much more detailed instruction built in. The design itself makes it intuitive for most who already know much about business case and cost-benefit analyses.Cells marked with the red triangle in their right top corner have notes and there are a lot of text boxes with instructions.
1 Project Set-up
With the tools first worksheet, you put in the data about the different alternatives you want to evaluate.
Define the name of project, and set the number of calculation years in the drop-down menu. Common is 3, 5, and 7 years, but the organisation should have a policy about this, use that if you intend to compare with other alternative investments. However if not, consider to improve the policy to follow the RBC policy and ensure the business change fully is implemented within the time span. For complex investments, where many processes and people are affected, it takes time. Interest rate - ask the finance department.
You got six alternative scopes to define, but be careful. Each alternative multiplies the analysis' time and effort. Conduct at strategic analysis first to ensure you only calculate what really is a choice you are prepared to take.
Define in short form the alternatives label/name, and write a longer definition and limitation of the scopes content. Remember, it is always the alternative's results under the full time span you calculate, i.e., you might compare 'To-Be' alternative running for five years (plus implementation year 0) with to continues for same number of years 'As-Is'.
Optimise the scope
Also, following the methods phases, you start with evaluating if the alternatives have a decent chance to give Payback, or you need to separate the implementations into several, etc.
2 Benefits Screening
Search for all improvements and differences between the defined scopes. If something is equal in all alternatives, you do not need to calculate it because it will be zero. However, if your intention is not only to do a business case, but a Total Cost of Ownership analysis in the same time, you add all.
Give the possible benefits short definitions and write them in the Benefits column. In column Definition & Formula, you define both why you consider it is a benefit, and how it will arise. You also use buttons 'Alt-Enter' to do new lines within the cell, and add a clear formula how to calculate the value. The formula has a special guideline how to define it, but more about that in the coming RBC handbook.
Remember, is should always be the same as in the calculation's annual columns to the right in the sheet. Define clearly what the variable is.
During the analysis, fill in with data in the 'Notes column'. See more instructions directly in the tool and its example template, but in short tell: Information data, Source of information and data, Risk descriptions, Dependencies, Open Questions, and your own Notes.
Also, define what reliability category each row and benefit can be considered to have - Reliable, Visible, Assumed, Not Estimated. More about that also in the coming Handbook, but read some more on this web site's page about the method.
Eventually, you calculate your benefits value as cash flow occur. Remember you rarely can expect benefits to occur the same year as you implement a larger IT solutions.
As a last thing to do, is to add a 75% reduction in the 'T2M' column to the very right in the Benefits Screening Sheet. That is only used when you have calculated time savings based on minutes and hours, rather than Full Time Employees (FTE's). The method defines 'Portions of Time Savings' (SPOTS) as not only always reduced 75%, but also always in the Assumed category.
3 Cost Screening
Similar procedure as for benefits, with additional three columns for:
- CapEx/OpEx - Capital or Operational expenditure
- Internal/External - used external or internal exsources
- Expense Types - add your own cost types in the table you find in the worksheet 'Customise'
As for both of the Screening Worksheets, you have buttons at top, extending and reducing how many years you see, and if you only want to have a single line per row visible, i.e. a condensed mode.
4 Report Analysis
With all benefits and all expenses found and defined, you are ready to use the Report Sheet. It can take some seconds to run the macros when you activate the report, The macros update pivots, renew the charts and tables etc. When you change alternatives, you start the update with the 'Recalculate!' button. You can also copy-paste all charts and tables to the 'Charts' worksheet, to easily use the results in external Word or PowerPoint reports etc.
You find following information in the report:
- Executive summary part with Benefit-Cost Analysis
- All results include net present value adjustments
- Cost-benefit chart
- Total Benefit-Cost results also as chart with reliability categories
- Discounted Cash flow and Payback
- Up to 10 years calculation time span
- Discounted ROI, as value and chartsDiscounted Cost-Benefit Ratio, as value and charts
- Time-To-Money adjustments (T2M), as value and pie charts
- Separation of benefit calculations into three reliability categories; Reliable, Visible, Assumed
- Separation of cost calculations into three reliability categories; Reliable, Visible, Assumed
- A fourth category as a separate list; Not Estimated (into monetary values, still, there could be other key performance indicators defined in the screening table)
- A cash flow diagram with the two selected alternatives separate benefits, and cost
- Bubble diagram with top 10 benefits, and totals of each reliability categor
- Bubble diagram with top 10 expenses, and totals of each reliability category
- Tables, and pie diagram with Capital expenditure (CapEx) and Oerational Expenditure (OpEx)
- Tables, and pie diagram with Internal cost and External cost
- Table, and pie diagrams with your customised selection of cost types
- A superb table showing all benefits and expenses, their reliability categories, and their relative impact on the results as percentage, and in total
- Short guidelines for each section of the results, explaining how to interpret the results and what it comes from
What you notice directly is the three reliability levels included in the calculations and charts. The fourth 'Not Estimated' only sees as a list.
Examples of evaluation of the report: Business benefits, reliable cost and types,project prioritizing support and follow-up table:
The Report sheet has explanations integrated that support you to understand and interpret the results. Remember the full report is nothing you show to the decision makers. You pick from it what is needed to explain your analysis and your recommendations.