Using the Return on Investment Model This Return on Investment (ROI / Payback) model template has several tabs already built in with cross-tab links and formulas to facilitate data entry: Savings Detail
Perhaps the most important tab is Savings Detail. Here, the user delineates where the collaborative initiative will achieve cost savings or avoidances, the latter of which may be more important than the former. For instance, suppose there are three expanding communities looking to jointly provide a service. By collaborating, one may avoid having to build a new facility; two others may be able to set aside plans for costly new capital asset Other tangible benefits of collaboration should also be included under the Savings Detail tab, such as increasing revenue or cost savings (e.g., longer-term attrition, efficiencies in service provision, joint purchasing resulting in scale economies, etc.). Generally, savings result from Scale economies are realized when a proportional change in inputs (e.g., product purchases) results in a greater than proportional change in outputs (e.g., pooled product purchases provide volume discounts to all communities involved). Economies of scale can result when: Greater specialization and division of labor is achievable (e.g., staff with the proper level of education, s Volume discounts for products and services are achievable There are large, up-front fixed costs (or significant replacement costs) that can be shared. Thus, many services produced and provided by government, especially those that are capital intensive, may benefit from economies of scale that can be achieved through ILC/JPS. Size economies are realized when an increase in outputs (e.g., volume of water treated by an under utilized plant) leads to a decrease in average total costs of production. Excess capacity (underutilization of an asset/resource) is often the case in communities with modest demand and facilities designed to handle greater capacity (e.g., a community with a decreasing population base). Thus, broadening the user base, increasing transactions, and/or enlarging Scope economies are realized when the range of services a community produces or provides is expanded and average total costs are reduced. Assets, including facilities, are purchased or built for anticipated local transaction / user volume. Likewise, a certain level of administrative overhead exists by necessity. Thus, broadening services through ILC/JPS leverages administrative overhead (i.e., spreads costs over a larger base) and allows assets to be more fully utilized (e.g., a shared office building). Related to scope economies are Intangible benefits, such as improving customer satisfaction, reducing errors, or enhancing control, should be part of the business case for collaborating, but would not be included in the Use the “Units” and “Rate per Unit” columns to quantify the Savings Detail. An annual multiplier column accommodates inflationary factors. Place an „X‟ in the columns under Cost Detail The Cost Detail tab works essentially like the Savings Detail tab. Project Summary
The Project Summary tab automatically calculates and presents the payback period. Assumptions List any and all assumptions, restrictions, exclusions, or risks that could impact the model and potentially the success of the endeavor.
r level of education, skills, and experience can function more efficiently and effectively within specialized areas)
within specialized areas)
PeopleSoft ROI three phase scenario.xls
Affects Project Benefit/Savings Description Enhanced revenues Budget Category/Funding Source Unit Desc ANN Units 1 Rate per Unit $1,000,000 Total Savings $1,000,000 Annual Multiplier 1.100
EA
1
$0
$0
1.000
EA
1
$0
$0
1.000
EA Subtotal Cost Avoidances: Reallocation of staff time ANN
1
$0
$0 $1,000,000
1.000
1
$250,000
$250,000
1.030
EA
1
$0
$0
1.030
EA
1
$0
$0
1.000
EA EA EA EA Subtotal Total Cumulative Total
1 1 1 1
$0 $0 $0 $0
$0 $0 $0 $0 $250,000 $1,250,000 $7,825,212
1.050 1.050 1.050 1.030
PeopleSoft ROI three phase scenario.xls
PeopleSoft ROI three phase scenario.xls
Affects Project ROI? Benefit/Savings Description Tangible Benefits: Enhanced revenues Y1 Y2 Y3 Y4 Y5 Y6 x x x x x Y1 Y2 $1,100,000
Potential Savings Extensions Y3 $1,210,000 Y4 $1,331,000 Y5 $1,464,100
x
x
x
x
x
x
$0
$0
$0
$0
$0
x
x
x
x
x
x
$0
$0
$0
$0
$0
x Subtotal Cost Avoidances: Reallocation of staff time
x
x
x
x
x
$0 $0
$0 $1,100,000
$0 $1,210,000
$0 $1,331,000
$0 $1,464,100
x
x
x
x
$265,225
$273,182
$281,377
x
x
x
x
x
x
$0
$0
$0
$0
$0
x
x
x
x
x
x
$0
$0
$0
$0
$0
x x x
x x x x
x x x x
x x x x
x x x x
x x x x
$0 $0 $0
$0 $0 $0 $0 $0 $1,100,000 $1,100,000
$0 $0 $0 $0 $265,225 $1,475,225 $2,575,225
$0 $0 $0 $0 $273,182 $1,604,182 $4,179,407
$0 $0 $0 $0 $281,377 $1,745,477 $5,924,884
Subtotal Total Cumulative Total
$0 $0 $0
PeopleSoft ROI three phase scenario.xls
PeopleSoft ROI three phase scenario.xls
Potential Savings Extensions Benefit/Savings Description Tangible Benefits: Enhanced revenues Y6 $1,610,510
$0
$0
$0 Subtotal Cost Avoidances: Reallocation of staff time $289,819 $1,610,510
$0
$0
$0 $0 $0 $0 $289,819 $1,900,329 $7,825,212
Subtotal Total Cumulative Total
PeopleSoft ROI three phase scenario.xls
<>
Oakland County -- <>
Return on Investment Analysis
As Of: <>
Savings Summary
Benefit/Savings Description Tangible Benefits: Tangible Benefits Subtotal: Cost Avoidances: Cost Avoidance Subtotal: Savings Total: Year 1 0 Year 2 1,100,000 Year 3 1,210,000 Year 4 1,331,000 Year 5 1,464,100 Year 6 1,610,510 Total 6,715,610
0 0
0 1,100,000
265,225 1,475,225
273,182 1,604,182
281,377 1,745,477
289,819 1,900,329
1,109,602 7,825,212
02af1fde-5a1c-48f2-9c63-df10866e5f8a.xls/Savings Summary Date Printed: 11/10/2009 Page 9
REV: April 7, 2003
PeopleSoft ROI three phase scenario.xls
Affects Project ROI? Cost Description Capital acquisition New facility Unit Desc EA EA EA EA EA EA EA Total Units 1 1 1 1 1 1 1 Rate per Unit $250,000 $2,000,000 $0 $0 $0 $0 $0 Total Cost $250,000 $2,000,000 $0 $0 $0 $0 $0 $2,250,000 Annual Multiplier 1.100 1.030 1.000 1.000 1.000 1.000 1.000 Y1 Y2 Y3 Y4 Y5 Y6 x x x x x x x x x x x x x
Potential Cos
x x x x x
x x x x x
x x x x x
x x x x x
02af1fde-5a1c-48f2-9c63-df10866e5f8a.xls/Cost Detail Date Printed: 11/10/2009 Page 10
REV: April 7, 2003
PeopleSoft ROI three phase scenario.xls
Potential Cost Extensions Cost Description Capital acquisition New facility Y1 $250,000 $0 $0 $0 $0 $0 Total $250,000 Y2 $275,000 $2,060,000 $0 $0 $0 $0 $0 $2,335,000 Y3 Y4 Y5 Y6
$0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0
02af1fde-5a1c-48f2-9c63-df10866e5f8a.xls/Cost Detail Date Printed: 11/10/2009 Page 11
REV: April 7, 2003
PeopleSoft ROI three phase scenario.xls
Affects Project ROI? Cost Description Capital acquisition New facility Unit Desc EA EA EA EA EA EA EA Total Units 1 1 1 1 1 1 1 Rate per Unit $250,000 $2,000,000 $0 $0 $0 $0 $0 Total Cost $250,000 $2,000,000 $0 $0 $0 $0 $0 $2,250,000 Annual Multiplier 1.100 1.030 1.000 1.000 1.000 1.000 1.000
02af1fde-5a1c-48f2-9c63-df10866e5f8a.xls/Cost Detail Date Printed: 11/10/2009 Page 12
REV: April 7, 2003
PeopleSoft ROI three phase scenario.xls
02af1fde-5a1c-48f2-9c63-df10866e5f8a.xls/Cost Detail Date Printed: 11/10/2009 Page 13
REV: April 7, 2003
<>
Oakland County -- <>
Return on Investment Analysis
As Of: <>
Cost Description Capital acquisition New facility
Year 1 250,000 0 0 0 0 0 250,000
Year 2
Cost Summary
Year 3 0 0 0 0 0
Year 4
Year 5
Year 6
Total:
275,000 2,060,000 0 0 0 0 0 2,335,000
Total 525,000 2,060,000 0 0 0 0 0 2,060,000
0 0 0 0 0
0 0 0 0 0
02af1fde-5a1c-48f2-9c63-df10866e5f8a.xls/Cost Summary Date Printed: 11/10/2009 Page 14 REV: April 7, 2003
PeopleSoft ROI three phase scenario.xls
Description Benefits/Savings: Tangible Benefits Subtotal: Cost Avoidance Subtotal: Annual Total Savings Cumulative Total Savings Costs: Annual Costs Cumulative Costs Statistics: Annual Return on Investment Cumulative Return on Investment Annual Cost/Savings Ratio Cumulative Cost/Savings Ratio Year Positive Payback Achieved State or Federal Mandate? Signatures:
Year 1 0 0 0 0 250,000 250,000 250,000
Year 2 1,100,000 0 1,100,000 1,100,000 2,335,000 2,335,000 2,585,000
Year 3 1,210,000 265,225 1,475,225 2,575,225 0 0 2,585,000
Year 4 1,331,000 273,182 1,604,182 4,179,407 0 0 2,585,000
Year 5 1,464,100 281,377 1,745,477 5,924,884 0 0 2,585,000
Year 6 1,610,510 289,819 1,900,329 7,825,212 0 0 2,585,000
Total 6,715,610 1,109,602 7,825,212 7,825,212 2,585,000 2,585,000 2,585,000
(250,000) (250,000) 0.00% 0.00%
(1,235,000) (1,485,000) 212.27% 235.00%
1,475,225 (9,775) 0.00% 100.38%
1,604,182 1,594,407 0.00% 61.85% Year 4
1,745,477 3,339,884 0.00% 43.63%
1,900,329 5,240,212 0.00% 33.03%
5,240,212 5,240,212
33.03% Year 4
Date:
Date:
Date:
02af1fde-5a1c-48f2-9c63-df10866e5f8a.xls/Project Summary Date Printed: 11/10/2009 Page 15
REV: April 7, 2003
<>
Oakland County -- <>
Return on Investment Analysis
As Of: <>
Assumptions
Date Assumption, Restrictions, Exclusions, or Risks
02af1fde-5a1c-48f2-9c63-df10866e5f8a.xls/Assumptions Date Printed: 11/10/2009 Page 16
REV: April 7, 2003