100K Executive Summary Template - Excel

Document Sample
100K Executive Summary Template - Excel Powered By Docstoc
					                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                                                                     12/1/2010
                                                                          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 that               5 - 7.5 minutes per call
                                                                 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: 12/1/2010
                                                                 Assumptions List for Research Discussion



                            Routing Table Setup          Routing tables are basically databases that enable the             $10,000 setup
                                                         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, including               28% of base
                                                         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: 12/1/2010
                                                                                                           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                                                                                                                     c69ada06-1656-468c-bf5c-76609c4bd09f.xls
           Prepared by: Venture Architects                                                                        Page 5 of 20                                                      12/1/2010
                                                          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                                                                        c69ada06-1656-468c-bf5c-76609c4bd09f.xls
   Prepared by: Venture Architects                                 Page 6 of 20                                                12/1/2010
                                                       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                                                                     c69ada06-1656-468c-bf5c-76609c4bd09f.xls
   Prepared by: Venture Architects                             Page 7 of 20                                                 12/1/2010
                                                                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                                                                                c69ada06-1656-468c-bf5c-76609c4bd09f.xls
    Prepared by: Venture Architects                                      Page 8 of 20                                                   12/1/2010
                                                                                                                  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                                                                                                                                                                              c69ada06-1656-468c-bf5c-76609c4bd09f.xls
Prepared by: Venture Architects                                                                                        Page 9 of 20                                                                                                12/1/2010
                                                                                            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                                                                                                                                  c69ada06-1656-468c-bf5c-76609c4bd09f.xls
    Prepared by: Venture Architects                                                                        Page 10 of 20                                                                  12/1/2010
                                                                                         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                                                                                                                      c69ada06-1656-468c-bf5c-76609c4bd09f.xls
   Prepared by: Venture Architects                                                                       Page 11 of 20                                                       12/1/2010
                                                                  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                                                                                 c69ada06-1656-468c-bf5c-76609c4bd09f.xls
Prepared by: Venture Architects                                    Page 12 of 20                                                     12/1/2010
                                                                  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                                                                                 c69ada06-1656-468c-bf5c-76609c4bd09f.xls
Prepared by: Venture Architects                                    Page 13 of 20                                                     12/1/2010
                                                     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                                              c69ada06-1656-468c-bf5c-76609c4bd09f.xls
     Prepared by: Venture Architects                  Page 14 of 20                                    12/1/2010
                                                                    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                                                                                      c69ada06-1656-468c-bf5c-76609c4bd09f.xls
      Prepared by: Venture Architects                                     Page 15 of 20                                                         12/1/2010
                                                       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                                                                         c69ada06-1656-468c-bf5c-76609c4bd09f.xls
    Prepared by: Venture Architects                          Page 16 of 20                                                       12/1/2010
                                                                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                                                                            c69ada06-1656-468c-bf5c-76609c4bd09f.xls
     Prepared by: Venture Architects                                 Page 17 of 20                                                   12/1/2010
                                                 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                                                   12/1/2010
                                                      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.




                                                                            c69ada06-1656-468c-bf5c-76609c4bd09f.xls
Prepared by: Venture Architects                        Page 19 of 20                                      12/1/2010
                                                    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                                                        12/1/2010

				
DOCUMENT INFO
Description: 100K Executive Summary Template document sample