Get documents about "Erlang Template - Excel
Description
Erlang Template document sample
Document Sample
2-1-1 Expense Template
LOCAL MODEL
A template for understanding and projecting the cost of
transitioning to and maintaining a 2-1-1 system
Counties Included:
PREPARED BY:
VERSION/DATE:
DRAFT Version 1.0 / November 2003
Navigation and Use
Mgmt and users:
1 - On each tab in this workbook, you'll see colored numbers. The color code is as follows:
Blue = inputs that you can change. These assumptions drive the financial results.
Black = a formula, whose result is based on the inputs. You should not alter formulas. Rather, alter the inputs (in Blue) to change assumptions. In this way, the integrity of the
model is maintained.
Grey = one of two things. They are either the results of formulas that are used as a reference in other formulas, or they are assumptions based on other 211 findings and
should not be changed. Please ignore (and do not change).
2 - The flow of the model is shown below. The boxes in BLUE (below) represent the "funding" side of the model. The RED boxes below represent the "expense" side of the
model.
Layout of financial model:
Financial Statements
for Stakeholders
Call Volume
Funding Sources
Projections Annual Budget
Specialist Budget
Staffing Expense Model
Budget Analysis
Personnel One-Time
Projections Transition Costs
How to use this workbook:
1 - Read the PowerPoint presentation, contained in the file 211_ExpenseModel_v3.ppt, in order to gain a detailed overview of the model, its purpose, and its components.
2 - Review the layout of the model, as shown above, and familiarize yourself with the flow of the worksheets (tabs) and logic. Please keep in mind that assumptions in one tab
will likely "link" to the next (or subsequent) tab(s).
3 - Review all assumptions in BLUE and change them as appropriate. The outputs of the model (e.g. the Budget) will update automatically.
4 - If preferred, you can simply go directly to the Annual Budget tab and type over the formulas for each year. This is not recommended, but it may be necessary. For examply, if
the projected budget is significantly different from what was planned, then it may be deemed more convenient to simply type in the budget amounts.
5 - Please remember that this model is designed to guide the budgeting process. As such, it is meant to provide a basis for every budget line item by logically connecting call
volume (demand) to a real world cost.
6 - Finally, this model only provides a macro-level view and is not intended to provide every detail of a real budget. Likewise, it does not account for the specific service
architecture of every 211 (e.g. 4 call centers versus 2). Therefore, the value of the model comes from seeing the logic applied to the numbers and extending it to fit a specific
situation.
211 Budgeting Template
Prepared by: Venture Architects Page 2 of 20 2/1/2011
Assumptions List for Research Discussion
Mgmt and users:
1 - Before getting started, you need to know that this workbook will require you to input various assumptions regarding expenses and operations.
2 - In order to facilitate the research of these assumptions, each major assumption is presented below, along with an explanation
and benchmark (where possible). We recommend you use this worksheet to research and determine your assumptions before
completing the model.
3 - Please note that in some cases, to avoid an overly complicated worksheet, we have grouped assumptions by category (e.g.
Personnel Salaries) in order to make you aware that you'll need to research these. In cases where an assumption is
particularly critical or complex, it will be presented by itself.
4 - The last column in this worksheet, "Mgmt's Decision", is meant to be a placeholder for you to input what decision management
has made, if possible. This placeholder is only for convenience and is not linked to the rest of the model/workbook in any way.
You will still need to enter the assumptions in each tab - this table is only an organizational aid.
Tab (& Category) Assumption Description of Assumption Benchmark Cost Mgmt's Decision
Call Volume Projections Population by Region The populations of each distinct region served Variable
Actual # Calls Made If a call center existed in the previous year, type in Variable
the number of calls it received
% of Population Calling 211 Estimated % of the population that will call 211 after 6%, but can vary w/ demographics
the population is fully aware of it
Growth Beyond Yr 3 Based on historical data, it takes up to 3 years for 5% annually
populations to fully utilize 211. Beyond Year 3,
however, annual call volume growth can still be
expected due to population changes, etc. This
variable accounts for that.
Specialist Staffing Average Service Call Time The length of the average call, including any time 5 - 7.5 minutes per call
that Specialists need to enter data. Do not include
time that the caller is on hold.
Hourly Wages of Specialist This may varie by shift (e.g. night or weekend) $8 - 16/hr
Nbr of Specialists Staffed The model provides a recommendation for staffing Variable
levels based on the anticipated call volume. This
must be estimated for each year, as call volume will
increase.
Personnel Projections Personnel Staff positions, # of FTEs, and salaries. The Variable
Specialists are filled in automatically based on the
assumptions made in the "Specialist Staffing" tab,
but the rest are management's decision.
One-Time Transition Costs Training 211-related training for Specialists $1,500/FTE
Certification 211 certification for Specialists $500/FTE
Accreditation 211 accreditation for each call center $20,000/ctr every 3 years
Construction/Build Out Cost of improving the facility to meet demands of $100/sf
211, projected on a per SF basis
Furniture New furniture for each FTE in need $6,000/FTE
ACD Purchase Automatic Call Distribution equipment that will be $50,000 for a smaller system to
needed to route call inside the center. $100,000+ for larger ones.
211 Transition Setup Fee for matching 211 against a local 7-digit # $200/line
211 Budgeting Template
Prepared by: Venture Architects Page 3 of 20 Date: 2/1/2011
Assumptions List for Research Discussion
Routing Table Setup Routing tables are basically databases that enable $10,000 setup
the phone system to route call across multiple
centers. May not be necessary in any case.
Web Development Highly variable one-time expense to establish a web $60,000, but varies greatly
presence for the 211 center
Server(s) Purchase Servers to host the website and/or service the local $15,000/center
call center
Software Upgrade Software that allows the Specialist to work with $1,000 per workstation (Specialist)
databases, website, and/or take advantage of call
management programs
Travel Cost per trip and # of trips for Capacity Planning $1,000/trip
Outside Consulting Consulting on Capacity Planning and operational 15 hrs/wk for 3mths at $150/hr
issues
Database Conversion The cost of converting provider data to a Varies from $50,000 to $150,000
standardized (and possibly web-based) format
Branding Development Developing marketing material, collateral, logo, etc. $15,000, but highly variable
Expense Model (ongoing) Benefits Employee benefits beyond their base salary, 28% of base
including FICA and other taxes, as well
PSTN Lines Phone lines $50/line/mth
800/Long Distance Incoming and outgoing calls $0.08/minute
ACD Maintenance Annual cost, fairly standard 20% (of cost) annual fee
211 Transition Maintenance Maintenance of switch $20/switch/mth
Bandwidth Access to internet $1,000/mth
VPN Security for network $500/mth, but variable
Routing Tables Maintenance of tables Unknown
211 Public Campaign Cost of educating public on the use of 211. $50,000, but varies
Outreach Events Marketing events to educate service providers $2,500/event @ 4/yr
Collateral Materials mailed to service providers and partners $5/provider/yr
Travel - Marketing related Monthly expense $2,000-$4,000/mth
Subscriptions & Dues Monthly expense $1,500/mth
Office lease Determined on a cost/sq ft/yr basis, with total sq ft $14/sf/yr @ 110 sf/FTE
driven by number of FTEs
Non-marketing Travel Employee travel that is subsidized/paid for $160/mth/FTE
Legal Annual cost, fairly standard $5,000
Insurance Annual cost, fairly standard $3,000
Audit Annual cost, fairly standard $3,500
Office Supplies Cost/mth/FTE for standard supplies $15/mth/FTE
Utilities Driven by number of FTEs in the building $10/mth/FTE
Repairs & Maintenance Estimated as a % of the lease amount 2% of lease
Contingency Estimated as a % of the total budget Varies from 0% - 5%
Funding Sources Source of funding A list of all sources of funding Variable
211 Budgeting Template
Prepared by: Venture Architects Page 4 of 20 Date: 2/1/2011
Call Volume Projections
2 6
Current Year ACTUALS Estimated Volume Call Volume Projections
1 3 4 5
Regions Covered Population # Calls % of Pop'n Calling 211 Y3 Max Call Volume Growth Beyond Y3 Y1 Y2 Y3 Y4 Y5
County A 100,000 10,000 7.0% 7,000 5.0% 100% 105% 110% 116% 122%
County B - - 0.0% - 5.0% n/a n/a n/a n/a n/a
County C - - 0.0% - 5.0% n/a n/a n/a n/a n/a
County D - - 0.0% - 5.0% n/a n/a n/a n/a n/a
County E - - 0.0% - 5.0% n/a n/a n/a n/a n/a
Totals 100,000 10,000 7.0% 7,000 5.0% 100% 105% 110% 116% 122%
Y1 Y2 Y3 Y4 Y5
Call Volume Projections 7,000 7,350 7,718 8,103 8,509
NOTES:
1 - Actual number of calls received in the past year.
2 - Much of this table is based on historical data from 211s across the U.S., including CT, TX, ID, NY, and MD.
3 - Historical call volume for 211 centers averages 1 call per year for every 16 people in a covered population (6%).
However, each region of the U.S. has unique demographics that may result in a much different usage rate.
4 - The maximum call volume is determined by multiplying the size of the population times the % that will be calling 211.
Note that this is a Year 3 estimate. Due to population growth and 211 branding, historical data shows a 5% annual growth thereafter.
In some cases, the Year 3 estimate may be reached earlier, particularly if public education and awareness are already high.
In these cases, we still project annual growth at a rate indicated in the next column.
5 - This is the annual growth in call volume that can be expected as a result of demographic/population changes.
6 - Based on historical data from other 211s, a 40% increase in call volume can be expected in Year 1 of implementation.
That is, 40% above the baseline number of calls prior to 211.
We expect to reach the maximum call volume within 2-3 years.
In Years 4 and 5, as stated above, we expect an annual increase in call volume by 5%.
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 5 of 20 2/1/2011
Specialist Staffing Estimates
I&R Specialist Staffing
YEAR 1
Annual Call Volume (YEAR 1) 7,000
Avg Call Service Time (min)1 8.5
M-F SAT & S
Shift: WK1 Shift: WK2 Shift: WK3 Shift: SS1
8am - 11 11 - 2 2-5 5-8 8 - 11pm 11pm - 8am 8am - 4
2
Projected Call Volume Distribution (%) 17.2% 22.6% 20.3% 11.3% 7.1% 9.1% 4.1%
Projected Call Volume (#) 1,202 1,582 1,423 791 497 638 289
Arrival Rate (calls/min) 0.03 0.03 0.03 0.02 0.01 0.00 0.01
Traffic (Erlang)3 0.22 0.29 0.26 0.14 0.09 0.04 0.05
4
Recommended Nbr of Specialists (95%) 1.82 1.95 1.90 1.67 1.57 1.47 1.49
4
Recommended Nbr of Specialists (85%) 1.24 1.34 1.30 1.13 1.05 0.98 0.99
5
Hourly wage of internal specialist $ 10.50 $ 10.50 $ 10.50 $ 10.50 $ 12.00 $ 14.00 $ 12.00
5
Nbr of Specialists to be Staffed 1.5 1.6 1.6 1.4 1.3 1.2 1.2
Salary Equivalent (for use in Personnel tab) $ 21,840 $ 23,400 $ 29,120 $ 24,960
YEAR 2
Annual Call Volume (YEAR 2) 7,350
Avg Call Service Time (min) 8.5
M-F SAT & S
Shift: WK1 Shift: WK2 Shift: WK3 Shift: SS1
8am - 11 11 - 2 2-5 5-8 8 - 11pm 11pm - 8am 8am - 4
Projected Call Volume Distribution (%)2 17.2% 22.6% 20.3% 11.3% 7.1% 9.1% 4.1%
Projected Call Volume (#) 1,262 1,661 1,494 831 522 670 304
Arrival Rate (calls/min) 0.03 0.04 0.03 0.02 0.01 0.00 0.01
Traffic (Erlang)3 0.23 0.30 0.27 0.15 0.09 0.04 0.05
4
Recommended Nbr of Specialists (95%) 1.84 1.98 1.92 1.69 1.58 1.47 1.49
4
Recommended Nbr of Specialists (85%) 1.25 1.36 1.31 1.14 1.06 0.98 1.00
5
Nbr of Specialists to be Staffed 1.5 1.7 1.6 1.4 1.3 1.2 1.2
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 6 of 20 2/1/2011
Specialist Staffing Estimates
YEAR 3
Annual Call Volume (YEAR 3) 7,718
Avg Call Service Time (min) 8.5
M-F SAT & S
Shift: WK1 Shift: WK2 Shift: WK3 Shift: SS1
8am - 11 11 - 2 2-5 5-8 8 - 11pm 11pm - 8am 8am - 4
2
Projected Call Volume Distribution (%) 17.2% 22.6% 20.3% 11.3% 7.1% 9.1% 4.1%
Projected Call Volume (#) 1,325 1,744 1,568 872 548 703 319
Arrival Rate (calls/min) 0.03 0.04 0.03 0.02 0.01 0.01 0.01
Traffic (Erlang)3 0.24 0.32 0.28 0.16 0.10 0.04 0.05
4
Recommended Nbr of Specialists (95%) 1.86 2.01 1.95 1.70 1.59 1.47 1.50
4
Recommended Nbr of Specialists (85%) 1.27 1.38 1.33 1.15 1.07 0.98 1.00
5
Nbr of Specialists to be Staffed 1.6 1.7 1.6 1.4 1.3 1.2 1.2
YEAR 4
Annual Call Volume (YEAR 4) 8,103
Avg Call Service Time (min) 8.5
M-F SAT & S
Shift: WK1 Shift: WK2 Shift: WK3 Shift: SS1
8am - 11 11 - 2 2-5 5-8 8 - 11pm 11pm - 8am 8am - 4
Projected Call Volume Distribution (%)2 17.2% 22.6% 20.3% 11.3% 7.1% 9.1% 4.1%
Projected Call Volume (#) 1,391 1,831 1,647 916 575 738 335
Arrival Rate (calls/min) 0.03 0.04 0.04 0.02 0.01 0.01 0.01
Traffic (Erlang)3 0.25 0.33 0.30 0.17 0.10 0.04 0.06
4
Recommended Nbr of Specialists (95%) 1.89 2.04 1.98 1.72 1.60 1.48 1.50
4
Recommended Nbr of Specialists (85%) 1.29 1.40 1.35 1.16 1.07 0.99 1.01
Nbr of Specialists to be Staffed5 1.6 1.7 1.7 1.4 1.3 1.2 1.3
YEAR 5
Annual Call Volume (YEAR 5) 8,509
Avg Call Service Time (min) 8.5
M-F SAT & S
Shift: WK1 Shift: WK2 Shift: WK3 Shift: SS1
8am - 11 11 - 2 2-5 5-8 8 - 11pm 11pm - 8am 8am - 4
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 7 of 20 2/1/2011
Specialist Staffing Estimates
Projected Call Volume Distribution (%)2 17.2% 22.6% 20.3% 11.3% 7.1% 9.1% 4.1%
Projected Call Volume (#) 1,461 1,923 1,729 961 604 775 352
Arrival Rate (calls/min) 0.03 0.04 0.04 0.02 0.01 0.01 0.01
Traffic (Erlang)3 0.27 0.35 0.31 0.17 0.11 0.05 0.06
4
Recommended Nbr of Specialists (95%) 1.91 2.07 2.00 1.73 1.61 1.48 1.51
4
Recommended Nbr of Specialists (85%) 1.31 1.43 1.38 1.18 1.08 0.99 1.01
5
Nbr of Specialists to be Staffed 1.6 1.7 1.7 1.5 1.3 1.2 1.3
Notes:
1 - The average time per call is based on local historical knowledge, when available. Otherwise, a national average of 7.5 minutes is used.
2 - The distribution of calls is taken from historical studies of 211 Centers in ID, CT, NY, TX, and MD. Distribution is fairly consistent in most cases.
3 - Staffing levels determined by using the Erlang formula (see Erlang Projections tab). The Erlang Formula is a widely accepted formula within the
telecommunications industry. Key assumptions required are service times and availability.
Service times = time per call, which should include data entry time if these staff will be responsible for that function. Talk times average 5.0 minutes.
Data entry can consume another 2.5 minutes per call.
Availability = the percentage of time that a staff person (and phone line) will be available. 95% is a considered outstanding service.
Note: the results can vary significantly by changing the Availability (e.g. decreasing it to 85% could decrease staff by 15-40% depending on volume).
See the Erlang Projections tab for a detailed explanation of the assumptions built into these projections.
4 - The "recommended" number is the result of Erlang projections.
5 - This is an assumption left to management's discretion, based on what the Erlang formula suggests as a guide.
The average of the 95% and 85% levels is used until mgmt inputs its own staffing levels.
6 - Some 211 centers outsource the "overflow" of call to 3rd parties who provide I&R service for a fee per call. If so, it will appear in this model.
7 - 12.4% of calls occur on Saturday and Sunday. The exact distribution of calls across shifts, however, is not known.
This model evenly divides the % of calls on the weekends across the 3 shifts. As data is collected, the % should be updated to reflect reality.
8 - If shown here, the # of overflow calls is only approximate. Beyond the complexities of the non-linear projection, it is difficult to predict
what % of callers will call back at a later time (and get a specialist). Therefore, this is a conservative number.
9 - If calculated in this model, the cost of outsourcing assumes that 100% of all calls will be handled. In the Expense Model,
the actual cost can be adjusted/capped.
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 8 of 20 2/1/2011
Personnel Projections
Personnel Projections
Raise1 Yearly Staffing Levels Yearly Salary Levels
Base Y1 Y2 Y3 Y4 Y5 Factor2 Y1 Y2 Y3 Y4 Y5 Y1 Y2 Y3 Y4 Y5
I&R Staff4
2-1-1 Center Director 50,000 0% 3% 3% 3% 3% - 0.5 0.5 0.5 0.5 0.5 25,000 25,750 26,523 27,318 28,138
Assistant 2-1-1 Director 40,000 0% 3% 3% 3% 3% - 1.0 1.0 1.0 1.0 1.0 40,000 41,200 42,436 43,709 45,020
Hotline Director 35,000 0% 3% 3% 3% 3% - 1.0 1.0 1.0 1.0 1.0 35,000 36,050 37,132 38,245 39,393
I&R Manager 35,000 0% 3% 3% 3% 3% - 1.0 1.0 1.0 1.0 1.0 35,000 36,050 37,132 38,245 39,393
Line Supervisor 30,000 0% 3% 3% 3% 3% - 1.0 1.0 1.0 1.0 1.0 30,000 30,900 31,827 32,782 33,765
Resource Specialist5 30,000 0% 3% 3% 3% 3% ### 2.7 3.0 3.0 3.0 3.0 80,000 92,700 95,481 98,345 101,296
I&R Specialist - WK1 Shift 21,840 0% 3% 3% 3% 3% - 1.6 1.6 1.6 1.7 1.7 34,740 36,263 37,870 39,567 41,359
I&R Specialist - WK2 Shift 23,400 0% 3% 3% 3% 3% - 1.4 1.4 1.4 1.4 1.4 31,753 32,947 34,196 35,503 36,872
I&R Specialist - WK3 Shift 29,120 0% 3% 3% 3% 3% - 1.2 1.2 1.2 1.2 1.2 35,593 36,761 37,972 39,228 40,531
I&R Specialist - SS1 Shift 24,960 0% 3% 3% 3% 3% - 0.5 0.5 0.5 0.5 0.5 12,387 12,802 13,234 13,682 14,148
I&R Specialist - SS2 Shift 24,960 0% 3% 3% 3% 3% - 0.5 0.5 0.5 0.5 0.5 12,387 12,802 13,234 13,682 14,148
I&R Specialist - SS3 Shift 24,960 0% 3% 3% 3% 3% - 0.5 0.5 0.5 0.5 0.5 12,387 12,802 13,234 13,682 14,148
Follow-up Specialist 30,000 0% 3% 3% 3% 3% - 1.0 1.0 1.0 1.0 1.0 30,000 30,900 31,827 32,782 33,765
Business Manager 30,000 0% 3% 3% 3% 3% - 0.2 0.2 0.2 0.2 0.2 6,000 6,180 6,365 6,556 6,753
Fiscal Assistant 30,000 0% 3% 3% 3% 3% - 0.2 0.2 0.2 0.2 0.2 6,000 6,180 6,365 6,556 6,753
Training Coordinator 30,000 0% 3% 3% 3% 3% - 0.2 0.2 0.2 0.2 0.2 6,000 6,180 6,365 6,556 6,753
Coordinator of Volunteers 30,000 0% 3% 3% 3% 3% - 0.2 0.2 0.2 0.2 0.2 6,000 6,180 6,365 6,556 6,753
Technical Systems Manager 30,000 0% 3% 3% 3% 3% - 0.2 0.2 0.2 0.2 0.2 6,000 6,180 6,365 6,556 6,753
Marketing Coordinator 30,000 0% 3% 3% 3% 3% - 0.2 0.2 0.2 0.2 0.2 6,000 6,180 6,365 6,556 6,753
Fund Development Coordinator 30,000 0% 3% 3% 3% 3% - 0.2 0.2 0.2 0.2 0.2 6,000 6,180 6,365 6,556 6,753
Administrative Assistant3 25,000 0% 3% 3% 3% 3% #### 1.0 1.0 1.0 1.0 1.0 25,000 25,750 26,523 27,318 28,138
I&R Staffing and Salary Totals 16.2 16.6 16.6 16.7 16.7 481,247 506,938 523,176 539,983 557,384
TOTAL STAFF 16.2 16.6 16.6 16.7 16.7 481,247 506,938 523,176 539,983 557,384
Notes:
1 - Raises are average amounts. Some positions may be higher or lower than the average.
2 - A "factor" is used to determine staffing levels in some cases. The factor can be the ratio of staff to management, or staff to call volume, etc.
3 - "Factor" here represents the ratio of Support Staff to Total Staff members.
4 - Staff descriptions are:
2-1-1 Center Director: Executive Director of Agency; or I&R Center Director (e.g., within the United Way or larger organization)
Assistant 2-1-1 Director: aka Director of Management/Information Programs
Hotline Director: aka Call Center Manager; responsible for quality assurance (e.g. supervision/monitoring)
Business Manager: aka Finance Director; Office Manager
Fiscal Assistant: assist Business Manager with accounting and business office functions
Administrative Assistant: assists with reports, file management, receptionist duties, special projects
I&R Manager: aka I&R Coordinator; Supervisor of Resource Specialists
Line Supervisor: aka Hotline Supervisor
Resource Specialist: update resource databases and other information resources
I&R Specialist: aka Hotline Counselor
Follow-up Specialist: makes follow up calls; provides advocacy
Training Coordinator: aka Training Manager
Coordinator of Volunteers: recruits volunteers and manages volunteer/ staff retention program
Technical Systems Manager: manages computer system and/or telephone system
Marketing Coordinator: marketing, advertising, promotions, outreach, education
Fund Development Coordinator: raises funds; writes grants; special events coord.
5 - AIRS suggest 1 full-time Resource Specialist for every 750 programs covered.
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 9 of 20 2/1/2011
One-Time Conversion Cost Assumptions
One-Time Investment Needs to Transition to 211
1 2 12 3 3
Service & Support Areas Component Needed? Cost Times Total Cost Start Yr End Yr YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5
Training & Certification Training (211-related) X $ 1,500 2 $ 3,000 1 1 3,000 - - - -
Certification X $ 500 2 $ 1,000 1 1 1,000 - - - -
Accreditation (per center) X $ 10,000 1 $ 10,000 1 3 3,333 3,333 3,333 - -
$ 14,000 7,333 3,333 3,333 - -
4
Office Expansion Build Out (cost/sf; sf) $ 100 - $ - 1 1 - - - - -
5
Furniture X $ 6,000 2 $ 12,000 2 3 - 6,000 6,000 - -
$ 12,000 - 6,000 6,000 - -
Telecommunications ACD Purchase6 X $ 70,000 1 $ 70,000 1 1 70,000 - - - -
211 Transition Setup7 X $ 200 2 $ 400 1 1 400 - - - -
8
Routing Table Setup $ 10,000 1 $ - 1 1 - - - - -
$ 70,400 70,400 - - - -
Software & Technology Web Development X $ 10,000 1 $ 10,000 2 2 - 10,000 - - -
Server(s) Purchase X $ 15,000 1 $ 15,000 3 3 - - 15,000 - -
9
Software Upgrade X $ 1,000 2 $ 2,000 3 3 - - 2,000 - -
$ 27,000 - 10,000 17,000 - -
Capacity Planning Travel X $ 1,000 10 $ 10,000 1 1 10,000 - - - -
Outside Consulting X $ 5,000 1 $ 5,000 1 1 5,000 - - - -
$ 15,000 15,000 - - - -
10
Referral Database Database conversion X $ 35,000 1 $ 35,000 1 2 17,500 17,500 - - -
$ 35,000 17,500 17,500 - - -
11
Marketing Branding Development X $ 5,000 1 $ 5,000 1 1 5,000 - - - -
$ 5,000 5,000 - - - -
Total Transition Investments Over the 5 Year Period: $ 178,400 $ 115,233 $ 36,833 $ 26,333 $ - $ -
NOTES:
1 - The one-time investments listed on this tab are meant to be a comprehensive list, based on the historical needs of other 211s.
The list was created by reviewing the startup budgets of 211 centers in CT, ID, MD, NY, and TX. While there was some variation in the
amount of line item costs (particularly telecommunications), the line items themselves were fairly consistent.
2 - "Needed" indicates whether or not the transition cost has already been incurred. If an "X" is present, the model WILL include the cost.
If there is no "X", then the model will NOT include the cost.
3 - Input a "start" and "end" year in order to control when the one-time expense is incurred. This affects cash flow.
4 - As a rule of thumb, estimate 110 sq ft for each work station in the new floor plan.
5 - Furniture costs are for new and replaced work stations, and may include new PCs.
6 - The cost of an ACD has ranged from $500,000 to $90,000. It is difficult to the great variation in cost without knowing what additional
services might have been included. An average cost of $200,000 is suitable as a benchmark starting point.
7 - 211 transition setup is a fee for matching the 211 number against a local, 7-digit phone number.
8 - Routing tables are necessary ONLY if there are multiple centers across which the phone system must route calls.
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 10 of 20 2/1/2011
One-Time Conversion Cost Assumptions
One-Time Investment Needs to Transition to 211
9 - Software upgrades are usually necessary for each Specialist if they are going to utilize the Internet or access a common database.
10 - This is the cost of converting the referral database to a standard database that meets 211 standards.
Typically, conversion takes 4-5 FTEs a full year to convert and clean data (at $35,000 per FTE).
It has been suggested that the drivers of this cost are the number of fields of data collected on each provider. However, there is no
data from other 211s that can provide a useful benchmark at such a detailed level.
11 - The cost of developing branding (marketing) material (e.g. collateral), can vary widely.
12 - Leave the cost amount in place, even if the item is not needed, because this figure is linked to the annual maintenance fees required.
Annual maintenance fees are 15-20% of the equipment/software cost.
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 11 of 20 2/1/2011
Expense Model
EXPENSES BY SERVICE & SUPPORT AREA Cost Factor YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5
Personnel1
Personnel Salaries 481,247 506,938 523,176 539,983 557,384
2
Benefits (% of salary) 28% 134,749 141,943 146,489 151,195 156,068
Training & Certification (cost/FTE/yr)3 $ 500 8,113 8,299 8,320 8,342 8,365
624,109 657,180 677,985 699,521 721,817
Outsourced Call Center Service (Overflow)
Outsourced Service4 $ - 0 0 0 0 0
0 0 0 0 0
Telecommunications
PSTN Lines (cost/line/mth)5 $ 50 5,861 5,861 5,861 5,861 5,861
800/Long Distance (cost per minute; % of calls) 6 $ 0.08 15% 714 750 787 827 868
11
Translation Services (cost per minute; % of calls) $ 1.60 1% 1,904 1,999 2,099 2,204 2,314
ACD Maintenance7 20% 14,000 14,000 14,000 14,000 14,000
211 Transition Maintenance (cost/switch/mth)8 $ - 0 0 0 0 0
Bandwidth (e.g. T1; cost/mth) $ 200 2,400 2,400 2,400 2,400 2,400
9
VPN (cost/mth) $ - 0 0 0 0 0
Routing Tables (if needed; cost/mth) $ - 0 0 0 0 0
24,879 25,010 25,147 25,291 25,443
Software & Technology
Web Hosting (cost/mth) $ 50 600 600 600 600 600
Database Hosting - IRIS ($3500/yr for DBs on web) $ 3,500 3,500 3,500 3,500 3,500 3,500
Other Database/Software Support ($/yr) $ 1,500 1,500 1,500 1,500 1,500 1,500
Software Licensing (% of software costs) 15% 300 300 300 300 300
5,900 5,900 5,900 5,900 5,900
Marketing
211 Public Educ Campaign (cost/yr/100k population) $ 5,000 5,000 5,000 5,000 5,000 5,000
Outreach Events (cost/event; #/yr) $ 1,000 8 8,000 8,000 8,000 8,000 8,000
10
Newsletters (cost/program; # programs in database) $ 5 2,000 10,000 10,000 10,000 10,000 10,000
23,000 23,000 23,000 23,000 23,000
Administrative Costs
Office lease (cost/sq ft/yr; total sq ft) 12 $ 14 2,600 36,400 37,492 38,617 39,775 40,969
Travel: Local ($/100,000 of population) $ 500 500 500 500 500 500
Travel: Prof Dev & Out of County ($/FTE/yr) $ 200 3,245 3,320 3,328 3,337 3,346
Legal (cost/yr) $ 5,000 5,000 5,000 5,000 5,000 5,000
Insurance-Prof, Liability, General, and D&O (cost/yr) $ 3,100 3,100 3,100 3,100 3,100 3,100
Audit (cost/yr) $ 3,500 3,500 3,500 3,500 3,500 3,500
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 12 of 20 2/1/2011
Expense Model
Office Supplies (cost/month/staff) $ 15 2,921 2,988 2,995 3,003 3,011
Utilities (cost/staff/month) $ 10 1,947 1,992 1,997 2,002 2,008
Printing & Postage ($/100,000 of population) $ 2,000 2,000 2,000 2,000 2,000 2,000
Directory Printing (cost/yr) $ 18,000 18,000 18,000 18,000 18,000 18,000
Memberships, Dues & Subscriptions (cost/yr) $ 200 2,400 2,400 2,400 2,400 2,400
Repairs & Maintenance (% of lease) 2% 728 750 772 796 819
Contingency (% of budget) 1% 6,779 7,111 7,320 7,537 7,762
86,520 88,152 89,530 90,950 92,415
Total Expenses 764,407 799,241 821,562 844,662 868,574
Referrence: Total Full-time Employees (FTE's)
Total employees 16.2 16.6 16.6 16.7 16.7
TRANSITION COSTS Factor 2003 2004 2005 2006 2007
Total one-time investments 115,233 36,833 26,333 - -
Notes:
1 - All line items on this tab are either calculated here or pulled from other tabs.
Calculations on this sheet are indicated by the variables in blue, with the variable names in parentheses.
2 - The full carrying cost of employees is 28% above base salaries to account for taxes and benefits.
3 - This is non-211 training that is a necessary and ongoing part of operations. Accounts for employee turnover, as well.
4 - Calls that cannot be handled (overflow) may be routed to a 3rd party I&R service. These costs are calculated on the Specialist Staffing tab.
5 - PSTN lines are simply the phone lines that come into the center. The number of lines equals 5 times the number of Specialists.
6 - Historical data from 211s shows that 15-20% of calls are either in-bound 800-number callers, or outbound long distance.
7 - The annual cost of maintaining the ACD can range from 10-20% of the original cost of the equipment.
8 - Phone companies charge a recurring fee to maintain the routing of "211" numbers to a local, 7-digit number.
9 - VPN's add security to data and calls crossing over the Internet. Typically only needed at larger call centers.
10 - Newsletters to all licensed providers (health, social) in the community, plus non-profit health and human services organizations.
11 - Translation services via the phone take twice as long as normal calls.
12 - Year 2 and beyond increase with inflation at a rate of 3% per year.
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 13 of 20 2/1/2011
Funding Sources
Funding Sources YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5
Government Grants (Federal) $ - $ - $ - $ - $ -
Government Grants (State) - - - - -
Government Grants (County/City/Local) - - - - -
Project-Based Grants - - - - -
Fund Raising, Contributions & Sponsorships - - - - -
United Way (Local and National) - - - - -
Fees for Service, Contracted Services - - - - -
Sales-Print & Elec Directories, Resrce Lists - - - - -
Total $ - $ - $ - $ - $ -
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 14 of 20 2/1/2011
Annual Budget Projections
For year ending ($) YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5
FUNDING
Government Grants (Federal) $ - 0% $ - 0% $ - 0% $ - 0% $ - 0%
Government Grants (State) - 0% - 0% - 0% - 0% - 0%
Government Grants (County/City/Local) - 0% - 0% - 0% - 0% - 0%
Project-Based Grants - 0% - 0% - 0% - 0% - 0%
Fund Raising, Contributions & Sponsorships - 0% - 0% - 0% - 0% - 0%
United Way (Local and National) - 0% - 0% - 0% - 0% - 0%
Fees for Service, Contracted Services - 0% - 0% - 0% - 0% - 0%
Sales-Print & Elec Directories, Resrce Lists - 0% - 0% - 0% - 0% - 0%
Total Funding $ - 0% $ - 0% $ - 0% $ - 0% $ - 0%
COST OF SERVICES
211 Service
Salaries & Benefits 615,996 0% 648,880 0% 669,665 0% 691,179 0% 713,452 0%
Training & Certification 8,113 0% 8,299 0% 8,320 0% 8,342 0% 8,365 0%
Telecommunications & Technology
Telephone Service 6,575 0% 6,610 0% 6,648 0% 6,687 0% 6,729 0%
Networking/Internet 2,400 0% 2,400 0% 2,400 0% 2,400 0% 2,400 0%
Web Hosting 600 0% 600 0% 600 0% 600 0% 600 0%
Software Upgrades 300 0% 300 0% 300 0% 300 0% 300 0%
211 Translation - 0% - 0% - 0% - 0% - 0%
Client Translation Services 1,904 0% 1,999 0% 2,099 0% 2,204 0% 2,314 0%
Routing Tables - 0% - 0% - 0% - 0% - 0%
Database Hosting (IRIS) 3,500 0% 3,500 0% 3,500 0% 3,500 0% 3,500 0%
ACD Technology 14,000 0% 14,000 0% 14,000 0% 14,000 0% 14,000 0%
Total Cost of Services 653,388 0% 686,589 0% 707,532 0% 729,212 0% 751,660 0%
GENERAL & ADMINISTRATIVE
Marketing
Public Education Campaign 5,000 0% 5,000 0% 5,000 0% 5,000 0% 5,000 0%
Outreach Events 8,000 0% 8,000 0% 8,000 0% 8,000 0% 8,000 0%
Collateral 10,000 0% 10,000 0% 10,000 0% 10,000 0% 10,000 0%
General & Administrative
Office Lease 36,400 0% 37,492 0% 38,617 0% 39,775 0% 40,969 0%
Travel 3,745 0% 3,820 0% 3,828 0% 3,837 0% 3,846 0%
Legal 5,000 0% 5,000 0% 5,000 0% 5,000 0% 5,000 0%
Insurance 3,100 0% 3,100 0% 3,100 0% 3,100 0% 3,100 0%
Audit 3,500 0% 3,500 0% 3,500 0% 3,500 0% 3,500 0%
Supplies 2,921 0% 2,988 0% 2,995 0% 3,003 0% 3,011 0%
Utilities 1,947 0% 1,992 0% 1,997 0% 2,002 0% 2,008 0%
Printing & Postage 2,000 0% 2,000 0% 2,000 0% 2,000 0% 2,000 0%
Directory Printing 18,000 0% 18,000 0% 18,000 0% 18,000 0% 18,000 0%
Memberships, Dues & Subscriptions 2,400 0% 2,400 0% 2,400 0% 2,400 0% 2,400 0%
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 15 of 20 2/1/2011
Annual Budget Projections
Repairs & Maintenance 728 0% 750 0% 772 0% 796 0% 819 0%
Contingency 6,779 0% 7,111 0% 7,320 0% 7,537 0% 7,762 0%
Total General & Administrative 109,520 0% 111,152 0% 112,530 0% 113,950 0% 115,415 0%
ONE-TIME TRANSITION COSTS
Training & Certification 7,333 0% 3,333 0% 3,333 0% - 0% - 0%
Office Expansion - 0% 6,000 0% 6,000 0% - 0% - 0%
Telecommunications 70,400 0% - 0% - 0% - 0% - 0%
Software & Technology - 0% 10,000 0% 17,000 0% - 0% - 0%
Capacity Planning 15,000 0% - 0% - 0% - 0% - 0%
Referral Database 17,500 0% 17,500 0% - 0% - 0% - 0%
Marketing 5,000 0% - 0% - 0% - 0% - 0%
Total Transition 115,233 0% 36,833 0% 26,333 0% - 0% - 0%
NET FUNDING NEED $ 878,141 0% $ 834,575 0% $ 846,396 0% $ 843,163 0% $ 867,075 0%
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 16 of 20 2/1/2011
Annual Budget Projections
For year ending ($) YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5
FUNDING
Government Grants (Federal) $ - 0% $ - 0% $ - 0% $ - 0% $ - 0%
Government Grants (State) - 0% - 0% - 0% - 0% - 0%
Government Grants (County/City/Local) - 0% - 0% - 0% - 0% - 0%
Project-Based Grants - 0% - 0% - 0% - 0% - 0%
Fund Raising, Contributions & Sponsorships - 0% - 0% - 0% - 0% - 0%
United Way (Local and National) - 0% - 0% - 0% - 0% - 0%
Other - 0% - 0% - 0% - 0% - 0%
Total Funding $ - 0% $ - 0% $ - 0% $ - 0% $ - 0%
COST OF SERVICES
211 Service 624,109 0% 657,180 0% 677,985 0% 699,521 0% 721,817 0%
Telecommunications & Technology 29,279 0% 29,410 0% 29,547 0% 29,691 0% 29,843 0%
Total Cost of Services 653,388 0% 686,589 0% 707,532 0% 729,212 0% 751,660 0%
GENERAL & ADMINISTRATIVE
Marketing 23,000 0% 23,000 0% 23,000 0% 23,000 0% 23,000 0%
General & Administrative 86,520 0% 88,152 0% 89,530 0% 90,950 0% 92,415 0%
Total General & Administrative 109,520 0% 111,152 0% 112,530 0% 113,950 0% 115,415 0%
ONE-TIME TRANSITION COSTS
Total Transition 115,233 0% 36,833 0% 26,333 0% - 0% - 0%
NET FUNDING NEED $ 878,141 0% $ 834,575 0% $ 846,396 0% $ 843,163 0% $ 867,075 0%
211 Budgeting Template eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 17 of 20 2/1/2011
Template for Copying Data to State Models
INSTRUCTIONS:
If there is a State expense model being created, it will require inputs from each Local model such as this.
To ensure that these Local projections are accounted for in the State model, please do the following:
1 - Since you will be working with State model, make sure that others who have access to it are
aware that you are making changes. Only one person at a time should update the State model.
2 - Go to the "Local Cost Projections" tab of the State expense model and choose one of the 15 County/Regions
areas in which to paste this data. Type over "County/Region #" with your area's name before pasting
data in order to keep track of your work.
3 - Return to this tab and copy only the boxed and shaded area below by selecting the area and then choosing
"Copy" from under the "Edit" menu.
4 - In the State model, in the space where you typed your area's name, you will "paste" this data by
doing the following:
Select the cell that contains "Call Volume" for Year 1 by clicking on it (e.g. cell D3 if it is not already filled in).
Select "Edit" from the menu bar, then "Paste Special".
A window will appear with several options on it. At the top, in the "Paste" section, choose "Values"
and click OK. This should paste all of the values (not formulas) from the Local model into the State model.
That's it. Save your work and return the model to whoever is maintaining the master copy so that
other Local managers can update the model with their projections.
HIGH-LEVEL SUMMARY: COPY TO STATE TEMPLATE (IF NECESSARY)
YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5
Call Volume 7,000 7,350 7,718 8,103 8,509
Local Funding from State $ - $ - $ - $ - $ -
Local Funding from Other $ - $ - $ - $ - $ -
211 Service $ 624,109 $ 657,180 $ 677,985 $ 699,521 $ 721,817
Telecommunications & Technology $ 29,279 $ 29,410 $ 29,547 $ 29,691 $ 29,843
Marketing $ 23,000 $ 23,000 $ 23,000 $ 23,000 $ 23,000
General & Administrative $ 86,520 $ 88,152 $ 89,530 $ 90,950 $ 92,415
One-Time Transition Costs $ 115,233 $ 36,833 $ 26,333 $ - $ -
211 Budgeting Template
Prepared by: Venture Architects Page 18 of 20 2/1/2011
Budget Analysis
Operating Cost per Call Analysis1, 2 Y1 Y2 Y3 Y4 Y5
Direct Cost per Call $ 93.34 $ 93.41 $ 91.68 $ 89.99 $ 88.34
Indirect Cost per Call $ 15.65 $ 15.12 $ 14.58 $ 14.06 $ 13.56
Total Cost per Call $ 108.99 $ 108.54 $ 106.26 $ 104.05 $ 101.91
Operating Cost per Call Analysis
$120.00
$100.00
$80.00
Indirect Cost per Call
$60.00
Direct Cost per Call
$40.00
$20.00
$-
Y1 Y2 Y3 Y4 Y5
Notes:
1 - Direct Costs exclude one-time costs, but include all other direct costs associated with providing 211 service.
2 - Indirect Costs include items such as marketing, professional services, and supplies.
Budget Cost per Call Analysis1, 2, * Y1 Y2 Y3 Y4 Y5
One-Time Cost per Calls $ 16.46 $ 5.01 $ 3.41 $ - $ -
Direct Cost per Call $ 93.34 $ 93.41 $ 91.68 $ 89.99 $ 88.34
Indirect Cost per Call $ 15.65 $ 15.12 $ 14.58 $ 14.06 $ 13.56
Total Cost per Call $ 108.99 $ 108.54 $ 106.26 $ 104.05 $ 101.91
Buget Cost per Call Analysis*
$140.00
$120.00
$100.00
One-Time Cost per Calls
$80.00
Indirect Cost per Call
$60.00
Direct Cost per Call
$40.00
$20.00
$-
Y1 Y2 Y3 Y4 Y5
Notes:
* - One-time expenses (transition costs) are included in this analysis and shown separately.
1 - Direct Costs exclude one-time costs, but include all other direct costs associated with providing 211 service.
2 - Indirect Costs include items such as marketing, professional services, and supplies.
eddb7bd7-2902-44ba-b555-6928cc4f2d20.xls
Prepared by: Venture Architects Page 19 of 20 2/1/2011
Erlang Formula Projections and Background
Erlang Formulas
AVAILABILITY
95% 85%
2 2
y = -0.0011x + 1.2464x + 2.2239; y = -0.0006x + 1.0356x + 1.4059;
Long Range (Erlang >= 3.5) R2=0.9998 R2=0.9999
y = -0.1384x2 + 2.0077x + 1.3881; y = -0.0835x2 + 1.468x + 0.9226;
Short Range (Erlang < 3.5) R2=0.9993 R2=0.9978
NOTES:
The telecommunications industry uses a mathematical formula called the "Erlang Formula" to determine how many staff (or phone lines) are needed
to service a given call volume. The formula requires assumptions for service levels (how often the service is available) and service times.
Although the Erlang formula is a complex algorithm, we can represent it as a set of algebraic equations within a narrow range of call volumes.
We can then use the algebraic equations to calculate how many specialists (or phone lines) are needed to answer the calls.
The equations listed above can be explained as follows:
y = The projected number of staff (or phone lines) needed to answer 95% or 85% of calls given a certain amount
of call volume. Call volume is represented in Erlang, a non-dimenisional unit represented by "x" in the equations.
x = Traffic in Erlang, which is determined by multiplying the 'arrival rate' times the 'service time'.
We assume a service time of 7.5 minutes and we know the arrival rate based on the historical distribution of calls.
Once Erlang is calculated, the appropriate formula must be chosen based on whether or not Erlang is greater than 3.5.
Further, we use two different formulas for the "95%" and "85%" assumptions about the availability of phone lines.
"95% availability" assumes that 95% of callers will not get a busy signal, and so forth.
Therefore, if Erlang is less than 3.5 and availability needs to be 95%, we would use the formula in the lower-left quadrant.
R2 represents how accurately the formula's result matches the Erlang algorithm. A 0.9993 = 99.93% accuracy.
For more detail, please visit a website that performs Erlang calculations: http://mmc.et.tudelft.nl/~frits/Erlang.htm
211 Budgeting Template
Prepared by: Venture Architects Page 20 of 20 2/1/2011
Related docs
