Docstoc

Calculating a Contribution Format Income Statement

Document Sample
Calculating a Contribution Format Income Statement Powered By Docstoc
					                                            Introduction to the SROI Excel Model


What is Social Return on Investment (SROI)?
SROI, or Social Return on Investment, is a framework which seeks to analyze the total value of a social
purpose enterprise -- an enterprise which pursues both the business goal of generating profits and the social
mission goal of its parent organization. The SROI framework considers the value created by the social
purpose enterprise in its pursuit of each of these two goals, then combines these values to arrive at a
complete understanding of the social purpose enterprise's entire impact on society. It then compares the
value created for society to the value invested by society, from both private and public sources, arriving at a
Social Return on Investment.

This model measures return on investment from three distinct perspectives. First, it examines the financial
value created (or cash generated) by the social purpose enterprise. This model also examines social
savings resulting from the increased tax revenues and the reduction in use of public assistance programs by
target employees. These savings are compared to the total investment in the social purpose enterprise to
determine the return on investment.

What does this model do?
This model seeks to bridge theory and practice by providing concrete instructions on how to calculate the
SROI metrics of your social purpose enterprise using your information. Although the tool will outline some of
the SROI theory and calculations, a complete discussion of SROI theory is beyond the scope of this tool.
For a more detailed explanation of the SROI framework, we recommend that you refer to our SROI
Methodology Paper. Chapters 2 and 3 in particular, provide guidance on how to calculate SROI.



What do you need to know to use the model?
The SROI Excel Model provides the basic framework to calculate SROI metrics. Users of the model must be
comfortable with Microsoft Excel, familiar with financial statements, and comfortable doing a small bit of
economic and industry research. Specifically, users will need to:
      Enter data into a spreadsheet
          If you have the specific data required in the model, you will simply need to enter the values into
            the spreadsheet.
          Typically, your information will be in a slightly different format than that needed for the model. In
            this case, you will need to manipulate the data in order to put it into the needed format.
    Develop financial projections (preferably based on historical financial statements for your social
     purpose enterprise)
        Project an income statement forward 10 years.
        Project investment in working capital (accounts receivable, inventory, and accounts payable)
          forward 10 years.
    Conduct basic research in order to make assumptions relevant to your industry and region of the
     country.
        Consult industry ratio reports like Robert Morris Associates Annual Report Studies for debt/equity
          ratios and industry betas.
        Determine municipal bond rates and the market rate of return for the time frame of your analysis.




   12496035-3c11-4ecc-8563-d35bc7d9d534.xls                   Page 1 of 16               The Roberts Enterprise Development Fund
                                           Introduction to the SROI Excel Model


How does this model work?
   Enter data on the 5 input sheets
       Background Information (information on your enterprise, the industry, the economy and expected
         growth of your business)
       Historical investment in your enterprise
       Historical and projected income statement for your enterprise
       Social impact of your enterprise
       Cash Items (working capital, long term debt, and capital expenditure information for your
         enterprise)
      SROI results will be shown on a summary page. SROI results include:
          Social Purpose Value
          Enterprise Value
          Blended Value
          Social Purpose Index of Return
          Enterprise Index of Return
          Blended Index of Return
          Other Social Purpose Results
          Enterprise Financial Results
          Projected Performance/Impact
      To better understand how the results were calculated, you can look at the detailed calculation pages
          Calculating Enterprise Value
          Calculating Social Purpose Value
          Calculating Indices of Return
          Calculating Projected Values (contribution to parent, investment required, social savings and new
            taxes, and social operating expenses)
A completed SROI Excel Model Spreadsheet for Enterprise ABC is available. This shows you an example of
how the spreadsheet can be used.

Further Information
Given the small number of staff members here at REDF, we request that before you contact us
directly with questions that you first consult the following SROI publications on our web site at:
             http://www.redf.org/pub_sroi.htm

          To understand how and why SROI is calculated the way it is, please review our SROI
           Methodology Paper at:
           http://www.redf.org/pub_sroi.htm#methodology

          To get a brief conceptual understanding of SROI, please review the Overview of SRO I at:
            http://www.redf.org/download/sroi/red.overview.pdf


          To learn how to interpret SROI results, please consult the Guide to SROI at:
            http://www.redf.org/download/sroi/red.guide.pdf


If upon review of these sections you still have questions, please feel free submit an information request form at:
http://www.redf.org/form.htm, or email us at info@redf.org.




   12496035-3c11-4ecc-8563-d35bc7d9d534.xls                   Page 2 of 16              The Roberts Enterprise Development Fund
General Instructions
    You must select “Enable Macros” when you open Excel in order to use all of the features of the model.
    Spreadsheets are color coded by Title Line to show whether they are Input, Summary or Caculation Sheets.

        Green Shading reflects Input Pages (You are required to enter data into these 5 worksheets).
        Tan Shading reflects SROI Summary Sheet (This is the end result of all the calculations).
        Yellow Shading reflects Calculation Sheets (These 4 sheets show the detailed calculations behind the
         Summary Sheet).
    Input Spreadsheets are further color coded to guide your data input.
        Blue Shading indicates cells in which you MUST input information in order to calculate SROI metrics. If
         blue shaded cells remain empty, you will receive an error message. Therefore you must enter a zero if
         the value is zero or “NA” if the particular item is not relevant to your social purpose enterprise.
        Gray shading indicates cells where it is OPTIONAL to enter information. You will be able to calculate
         SROI results without these numbers. However, you will not be able to calculate some of the
         supplementary information without them.
        Blue Text shows cells in which you have input data.
        Black Text signifies cells where calculations are performed. You do not need to do anything with these
         cells. However, you can look at the formulas within them to better understand how SROI metrics are
         calculated.
        Purple Text denotes cells that include information pulled from other parts of the model. You do not need
         to do anything with these cells. However, you can look at the formulas to better understand where the
         data is coming from.

        Orange Text indicates a reminder to input specific data.

        Red Text indicates an error. Read the specific message for details.
    All worksheets are protected – this means that you can only input data in designated areas. You cannot
     accidentally change a formula in the spreadsheet
        However, if you wish to adjust the formulas you can unprotect each sheet (Tools  Protect Document 
         Unprotect Sheet). No password is required. Changed formulas will not yield the intended SROI results.


        If you need to do additional calculations, add new sheets to the model. If you change existing sheets you
         may accidentally compromise the integrity of the model.

    Further instructions on how to input specific types of information is included on each input page. If this
     information is not sufficient, review the SROI Methodology Paper for additional detail.




  12496035-3c11-4ecc-8563-d35bc7d9d534.xls                 3 of 16           The Roberts Enterprise Development Fund
Inputs -- Background Information


Guidelines:
The data input on this page provides the backbone of the model. The information will be used to determine discount rates and terminal periods.
The glossary of the SROI Methodology Paper (found in the appendix) gives more precise definitions of the financial, economic and industry data.
Financial, economic and industry data from Enterprise ABC is included for your information. Consider the following as you enter your data:
    - The financial data for Enterprise ABC is from 1999. This data provides a baseline but more current information will yield better results.
    - The industry data reflects the restaurant/food service industry. Your data should reflect your specific industry.
It is critical that each of the inputs described on this page are input into the model as accurately as possible.
However, it is not necessary that you have a detailed understanding of these numbers -- if you can obtain the correct numbers, the model will do the rest of the work.


All information in shaded blue boxes must be completed in order to calculate SROI metrics!!
Social Purpose Enterprise Background
Name of Business:
Name of Parent Organization:

SROI Analysis Background
Valuation Year                                                 - The most recent year for which you have complete financial and social impact data for your business.
Periods to discount                                       10   - The number of years forward that you will project financial and social results. This must be between 5 and 10 years. The model
                                                                 defaults at 10 years.

Financial Data                Enterprise ABC Data
10 Year Treasury                    6.58%                      - Used to approximate the "risk free rate".
Market Risk Premium                 3.50%                      - The difference between the "risk free rate" and the "market rate". Market rate can be based on the Dow Jones Index.
Small Business Premium              3.00%                      - Additional risk due to the fact that the businesses are small and illiquid.
Muni Bond Rate                      5.32%

Economic Data
Tax Rate:                            15%                       - Use the tax rate most relevant to the population you employ.
Wage Growth Rate :                  1.50%                      - Consider economic projections for wage growth.

Industry Specific Data
Average Total Debt ($)           $ 60,634                      - Industry specific data can be obtained from sources such as Robert Morrris Associates Reports, which are available at your local
Debt to Equity Ratio                 2.88                        public library. If you cannot find data for your specific industry , use the most similar industry you can find.
Weighted Avg cost of debt          10.00%
LTD ($)                          $ 11,213
Industry Beta                        1.03




   12496035-3c11-4ecc-8563-d35bc7d9d534.xls                                                    4 of 16                                                The Roberts Enterprise Development Fund
Inputs -- Historical Investment in the Enterprise


Guidelines:
 - Information from the "Total Historical Investment" row will be used to calculate the "Investment to Date" later in the model.
 - The "Parent Agency" line accounts for all funding provided by the agency itself (i.e., from the agency's reserves).
 - The "Historical Funder" lines represent external sources of funding.
 - If you have more than 2 sources of external funding, click on "Add a Historical Funder" for additional rows.
 - You can also consolidate funders in a single row if you have information in that form.
 - Try to project back as far as possible -- ideally this should reflect ALL investment in the enterprise from its start.
 - If you do not include any investment you will receive an error message and you will be unable to calculate SROI metrics.
 - The "Investment in the Enterprise" should reflect money used for the enterprise -- not grants used in other ways for the parent agency

 You must enter Investment Information to calculate SROI!!
                                                                            Investment in the Enterprise
Investor                          0             -1           -2            -3          -4           -5              -6           -7             -8           -9
Parent Agency
Historical Funder A
Historical Funder B
     Total Historical
         Investment $         -       $     -        $   -        $    -      $         -   $     -     $       -        $   -        $     -        $   -




   12496035-3c11-4ecc-8563-d35bc7d9d534.xls                                   5 of 16                                The Roberts Enterprise Development Fund
Inputs -- Income Statement Projections


Guidelines
- Notice the unique format of this income statement -- expenses and revenues are separated depending on whether they are related to the business or to the social mission. REDF calls this True Cost Accounting Analysis. See Chapter 2 of the SROI
Methodology Paper for further information.
- The historical data (in the shaded gray area) should be based on actual numbers. Although it will provide a baseline for your future projections, it is not required for the calculation of SROI metrics.
- See Chapter 3, Figure 3-3 of the SROI Methodology Paper for more information on making projections.
- Net Income before S&S, as well as social expenses, will be used in calculating SROI values later in the model.
- The terminal value should represent the "steady state" of the business. Assuming the business continues indefinitely, the business should perform at that level.



You must enter data in all blue shaded cells -- even if the value is zero!
                                                            Historical Performance
                                                                   (Optional)                                                           Projected Financial Performance (Required)                                                               Terminal
                                                                       -1          0       1P        2P                3P                 4P            5P            6P           7P                8P              9P              10P           Period

                      Sales
   Business Related




                      Cost of Goods Sold
                      Gross Profit                                    0           0         0         0                 0                  0            0            0               0               0                0                0               0

                      Gross Margin                               #DIV/0!     #DIV/0!   #DIV/0!   #DIV/0!          #DIV/0!             #DIV/0!      #DIV/0!      #DIV/0!        #DIV/0!          #DIV/0!         #DIV/0!          #DIV/0!          #DIV/0!

                      Operating Expenses
                      Depreciation
                        Net Income Before Subsidies and
                        Social Operating Expenses (S&S)               0           0         0         0                 0                  0            0            0               0               0                0                0               0

                      Subsidies/ Grants
Social Mission




                        Parent Agency                                 0           0
                        Historical Funder A                           0           0
   Related




                        Historical Funder B                           0           0
                        New Funder
                           Total Subsidies and Grants                  0           0         0         0                0                   0            0            0              0                0                0               0                0

                      Social Expenses

                                 Net Income After Social
                                 Expenses and Subsidies               0           0         0         0                 0                  0            0            0               0               0                0                0               0




                      12496035-3c11-4ecc-8563-d35bc7d9d534.xls                                                              6 of 16                                                                       The Roberts Enterprise Development Fund
Inputs -- Social Impact


Guidelines
- The "Projected # of Target Employees" should include the total number of target employees -- regardless of whether they are full time or part time.
- The "Change in Annual Income" should reflect the average difference between each target employee's income at the time of hire, to his or her income one year later.
- When calculating "Criminal Convictions Savings", consider the following:
  - The cost of conviction is the weighted average cost of carrying out the sentence (probation or incarceration) -- other costs are excluded.
- Consider the following in the "Calculation of Average Social Cost Savings Per Employe" table:
  - The "Total decrease" in annual visits or annual costs reflects the sum of usage for all target employees. These values are not averages.
  - Cost savings values should be relevant for your geographic area and the time period of your analysis.
  - The "# of Target Employees Responding" provides the basis for averaging the cost savings.
- See the appendix of the SROI Methodology Paper for more information on specific sources of cost savings data.


You must fill in all blue shaded cells -- even if the value is zero!!
Projected Number of Target Employees

              Year                       1                2                  3                4                 5              6                 7        8             9      10    Terminal Period
# of Target Employees


Change in Annual Income


Calculating Criminal Conviction Savings

1) Calculation of Cost of Conviction

                                   Average Yearly
                                                          X            % of People           =         Cost of Conviction
                                        Cost
Sentence
Incarceration                                             x                                  =          $           -
Parolee                                                   x                                  =          $           -
                                                                                                        $           -

Cost of Conviction                 $         -




        12496035-3c11-4ecc-8563-d35bc7d9d534.xls                                                      7 of 16                                        The Roberts Enterprise Development Fund
Inputs -- Social Impact
Reduction in Expected Criminal Convictions
                    Expected Repeact Convictions
                                                                                            Actual Number
                                                                                                                            First Time                        Reduction in Expected
   # of Employees w/Conviction at                                               -          of Repeat Convic-         -                           =
                                          X             Recidivism Rate                                                    Convic-tions                           Convictions
   Baseline                                                                                      tions


                                          X                                     -                                    -                           =                   0.00

Average Per Person Cost Savings for Criminal Convictions
                                                       # Target
                                                                                                                           Cost Savings
 Reduction in Expected Criminal                                                                Cost of
                                          
                                                     Employees
                                                                                X                                    =      for Criminal
 Convictions                                        Responding to                             Conviction
                                                                                                                           Convictions
                                                      Question

             0.00                                                              X           $         -              =      #DIV/0!


Calculation of Average Social Cost Savings Per Employee
                                                                                                                                                                      # of Target
                                                                          Total decrease                                                   Total decrease                                  Average Cost
                                                                                                                                                              
                                                                                                               Cost Per                                               Employees
                                                                           (increase) in          X            Visit/Use        =           (increase) in
                                                                                                                                                                     Responding to    =     Savings per
                                                                           annual visits                                                     annual cost                                  Target Employee
                                                                                                                                                                       Question

Public Assistance Programs*
 TANF                                                                          NA                                NA             =                                                    =      #DIV/0!
 General Assistance                                                            NA                                NA             =                                                    =      #DIV/0!
 Food Stamps                                                                   NA                                NA             =                                                    =      #DIV/0!
 SSI                                                                           NA                                NA             =                                                    =      #DIV/0!
Social Service Programs
 Food Banks                                                                                       x                             =          $         -                               =      #DIV/0!
 Case Management                                                                                  x                             =          $         -                               =      #DIV/0!
 Community Clinics                                                                                x                             =          $         -                               =      #DIV/0!
 Mental Health Treatment                                                                          x                             =          $         -                               =      #DIV/0!
 Housing Services (shelter, trans. housing, grp home)                                             x                             =          $         -                               =      #DIV/0!
 Emergency Room                                                                                   x                             =          $         -                               =      #DIV/0!
 Legal Services                                                                                   x                             =          $         -                               =      #DIV/0!
 Substance Abuse Trmt.                                                                            x                             =          $         -                               =      #DIV/0!
 MediCal (includes employee and dependents)                                                       x                             =          $         -                               =      #DIV/0!
Criminal Conviction Savings (see below)                                                                                                                                                      #DIV/0!
                                                                                                                                       Average Social Cost Savings Per Employee              #DIV/0!
*For individual employees, NOT other members of household.

Average Social Cost Savings Per Employee                                                   #DIV/0!

        12496035-3c11-4ecc-8563-d35bc7d9d534.xls                                                           8 of 16                                       The Roberts Enterprise Development Fund
Inputs - Cash Items


Comments
- Not all financial information needed for valuation is included on the income statement.
- Current long term debt should be debt solely for the enterprise -- not debt used to finance other activities of the agency.
- Investment in working capital can be easily calculated based on information typically found in a business balance sheet.
    - If a balance sheet asset (like Accounts Receivable) increases, you must have invested cash in the business to support that increase.
    - If a balance sheet liability (like Accounts Payable) decreases, you must have invested cash in the business to support that change.
    - Therefore, to calculate the "investment" (or cash required) you will have to compare the current year to the previous year.
- If your enterprise does not have a separate balance sheet there are two ways to estimate and project this information:
    - Estimate values based on experience and an understanding of the industry (Accounts receivable and accounts payable may be estimated based on a % of sales. Inventory can be based on COGS.)
    - Sometimes amounts spent for inventory can be extracted directly from the accounting system.
- The SROI Methodology Paper gives further information on techniques for projecting capital expenditures and investment in working capital.

You must input data into all blue shaded cells -- even if the value is zero!!
Current Long Term Debt

Projected Investment in Working Capital
                                                                                                                                                                                                         Terminal
                                                  0            1P            2P            3P                4P            5P            6P            7P             8P            9P         10P         Period
  Accounts receivable (% sales)
  Inventory (% sales)
  Accounts payable (% COGS)
  Net Working Capital                  $      -       $    -        $    -        $    -        $        -        $    -        $    -        $    -        $     -        $    -        $     -     $       -
Investment in Working Capital                         $    -        $    -        $    -        $        -        $    -        $    -        $    -        $     -        $    -        $     -     $       -


Projected Capital Expenditures
                                                                                                                                                                                                     Terminal
                                                      1P            2P            3P            4P                5P            6P            7P            8P             9P            10P         Period
Annual Capital Expenditures




    12496035-3c11-4ecc-8563-d35bc7d9d534.xls                                                         9 of 16                                                     The Roberts Enterprise Development Fund
                                                                        Data Entry INCOMPLETE
                                                                  Click to See Which Category is Incomplete

        SROI Results
        For detail on how these metrics were calculated, see the Calculation Worksheets.

                                                                                0
        SROI Metrics                                              Index of Return
        Enterprise Value                        #DIV/0!              #DIV/0!
        Social Purpose Value                    #DIV/0!              #DIV/0!
        Blended Value                           #DIV/0!              #DIV/0!

        Investment to Date                                              #DIV/0!

        Social Purpose Results (Per Target Employee)                              1999
        Public Savings                                                  #DIV/0!
        New Taxes                                                                   $0
        Wage Improvement                                                            $0
        Financial Improvement                                           #DIV/0!

        Enterprise Financials                             1998                    1999                2000P
        Sales                                     NA                      NA                     NA
        Gross Margin                              NA                      NA                     NA
        Operating Margin (Before S&S)             NA                      NA                     NA
        Operating Margin (After S&S)              NA                      NA                     NA

        Projected Values                                         ( Into Perpetuity)
        Total Projected Investment                                    #DIV/0!
        Total Projected Social Savings and New Taxes                  #DIV/0!
        Total Projected Social Expenses                               #DIV/0!
        Total Projected Contribution to Parent                        #DIV/0!




                                         Revenues and Expenses
         1

         1                                                                               Revenues
                                                                                         Social Subsidies
         1                                                                               Sales

         1                                                                               Expenses
                                                                                         Social Operating
         1                                                                               Expenses
                                                                                         Enterprise
         1
                                                                                         Expenses
         0

         0

         0

         0
               $0      $0
         0     $0      $0                $0      $0                $0          $0




12496035-3c11-4ecc-8563-d35bc7d9d534.xls          10 of 16          The Roberts Enterprise Development Fund
Calculating Enterprise Value


Step 1: Project Enterprise Cash Flow


                                                              1P                     2P                  3P                   4P                   5P                 6P                   7P                  8P                 9P               10P
 Net Income Before S&S                          $         -            $         -        $          -          $         -        $           -        $         -        $           -         $         -        $         -          $         -
 Add Back Depreciation                          $         -            $         -        $          -          $         -        $           -        $         -        $           -         $         -        $         -          $         -
 Less: Investment in Working Capital            $         -            $         -        $          -          $         -        $           -        $         -        $           -         $         -        $         -          $         -
 Less: Capital Expenditures                     $         -            $         -        $          -          $         -        $           -        $         -        $           -         $         -        $         -          $         -
 Business Cash Flow                             $         -            $         -        $          -          $         -        $           -        $         -        $           -         $         -        $         -          $         -


Step 2: Determine Appropriate Discount Rate

2a) Calculate LTD to Equity Ratio                                                         2b) Calculate Cost of Equity

                                                                                                                                                                               Market Risk                          Small Business
                                                                                           Risk free Rate             +                 Beta                  X                                        +                                       =
                                                                                                                                                                                Premium                               Premium
Total Debt ($)                                  $         -
Total Debt to Equity (x)                                          0                           0.00%                   +                 0.00                  X                 0.00%                  +                0.00%                  =
Equity ($)                                        #DIV/0!
LTD ($)                                         $      -
LTD to Equity (x)                                 #DIV/0!                                 2c) Calculate Weighted Average Cost of Capital (WACC)
% Debt                                            #DIV/0!                                      % Debt                 X            Cost of Debt               +                 % Equity               X            Cost of Equity             =
                                                                                              #DIV/0!                 x                0.00%                  X                #DIV/0!                 +                0.00%                  =
Appropriate Discount Rate (WACC)                #DIV/0!

Step 3: Discount the Enterprise Cash Flow
A) Determine the Present Value of the Enterprise Cash Flow
                                                                  1P                 2P                  3P                   4P                   5P                 6P                   7P                  8P                 9P                   10P
     Time                                                          0                  1                     2                  3                    4                  5                     6                  7                    8                   9
     Present Value of Cash Flow                     #DIV/0!                #DIV/0!            #DIV/0!               #DIV/0!            #DIV/0!              #DIV/0!             #DIV/0!              #DIV/0!            #DIV/0!              #DIV/0!



B) Determine the Present Value of the Terminal Value                                                            C) Calculate Social Purpose Value
     Enterprise Cash Flow for Terminal Period   $             -                                                        Terminal Value                                           #DIV/0!
     Terminal Value                                 #DIV/0!                                                            Sum of PV of Cash Flow                                   #DIV/0!
     Present Value of Terminal Value                #DIV/0!                                                            Social Purpose Value                                    #DIV/0!




     12496035-3c11-4ecc-8563-d35bc7d9d534.xls                                                                   11 of 16                                                          The Roberts Enterprise Development Fund
Calculating Social Purpose Value



Avg. Social Cost per Employee                         #DIV/0!                    Income Growth Rate                              0.0%
Avg. Change in Annual Income per Employee         $             -                Tax Rate                                             0%



Step 1: Project Social Cash Flow
                                                                                                                                                                                                                                                                         Terminal
                                                                    1P                     2P                      3P                 4P                 5P                 6P                     7P                     8P                 9P             10P            Period
Background Information
Time                                                                 0                      1                       2                  3                  4                  5                      6                      7                  8                 9                 10
# of Employees                                                       0                      0                       0                  0                  0                  0                      0                      0                  0                 0                 0
Change in income per employee                     $             -        $             -        $              -        $         -        $         -        $         -        $             -        $             -        $         -        $         -       $         -
Total wages                                       $             -        $             -        $              -        $         -        $         -        $         -        $             -        $             -        $         -        $         -       $         -


Cash Flow Projections
Total new taxes                                   $             -        $             -        $              -        $         -        $         -        $         -        $             -        $             -        $         -        $         -       $         -
Total social cost savings                             #DIV/0!                #DIV/0!                 #DIV/0!                #DIV/0!            #DIV/0!            #DIV/0!            #DIV/0!                #DIV/0!                #DIV/0!            #DIV/0!           #DIV/0!
Total social purpose inflows                          #DIV/0!                #DIV/0!                 #DIV/0!                #DIV/0!            #DIV/0!            #DIV/0!            #DIV/0!                #DIV/0!                #DIV/0!            #DIV/0!           #DIV/0!
Less: social operating expenses                   $             -        $             -        $              -        $         -        $         -        $         -        $             -        $             -        $         -        $         -       $         -
Social purpose cash flow                              #DIV/0!                #DIV/0!                 #DIV/0!                #DIV/0!            #DIV/0!            #DIV/0!            #DIV/0!                #DIV/0!                #DIV/0!            #DIV/0!           #DIV/0!



Step 2: Determine Appropriate Discount Rate
       Municipal bond rate                                               0.00%
       Appropriate Discount Rate (Tax Adjusted Muni Rate)                0.00%                  (Rate adjusted to reflect higher return due to tax free status of municipal bonds)



Step 3: Discount the Social Cash Flow

A) Determine the Present Value of the Social Cash Flow


                                                                    1P                     2P                      3P                 4P                 5P                 6P                     7P                     8P                 9P             10P
       Time                                                          0                      1                       2                  3                  4                  5                      6                      7                  8               9
       Present Value of Cash Flow                           #DIV/0!              #DIV/0!                   #DIV/0!             #DIV/0!            #DIV/0!            #DIV/0!             #DIV/0!                #DIV/0!               #DIV/0!            #DIV/0!



B) Determine the Present Value of the Terminal Value                                                                        C) Calculate Social Purpose Value
       Social Cash Flow for Terminal Period           #DIV/0!                                                                    Present Value of Terminal Value                     #DIV/0!
       Terminal Value                                 #DIV/0!                                                                    Sum of PV of Cash Flow                              #DIV/0!
       Present Value of Terminal Value                #DIV/0!                                                                    Social Purpose Value                                #DIV/0!




    12496035-3c11-4ecc-8563-d35bc7d9d534.xls                                                                                   12 of 16                                                                      The Roberts Enterprise Development Fund
Calculating Indices of Return


Step 1: Determine Present Value of Investment to Date

                                                               Weight of      Discount    Contribution
                                              Value               Value         Rate        to Rate
Enterprise                                   #DIV/0!             #DIV/0!       #DIV/0!      #DIV/0!
Social Purpose                               #DIV/0!             #DIV/0!           0.00%    #DIV/0!
                                                           Weighted Average Discount Rate   #DIV/0!




                                                                                                 Investment in the Enterprise
Investor                                                    0            -1               -2             -3          -4          -5         -6         -7        -8          -9
Time                                                        0            -1               -2             -3          -4          -5         -6         -7        -8          -9
Historical Value of Investment           $          -    $             -    $         -    $           -    $      -     $     -    $      -    $    -    $    -    $    -
Present Value of Historical Investment           #DIV/0!            #DIV/0!        #DIV/0!          #DIV/0!     #DIV/0!     #DIV/0!     #DIV/0!   #DIV/0!   #DIV/0!   #DIV/0!


PV of Last 10 years of Investment            #DIV/0!




Step 2: Calculate Indices of Return

                                                                              Investment                    Index of
                                              Value                 /           to Date           =          Return
Enterprise Index of Return                   #DIV/0!                /           #DIV/0!           =          #DIV/0!
Social Purpose Index of Return               #DIV/0!                /           #DIV/0!           =          #DIV/0!

Long Term Debt                           $             -

Blended Index of Return                      #DIV/0!                /           #DIV/0!           =          #DIV/0!




   12496035-3c11-4ecc-8563-d35bc7d9d534.xls                                           13 of 16                                     The Roberts Enterprise Development Fund
Calculation of Projected Values


Investment Required & Contribution to Parent
                                                                                                                                                                                                                                                                                                   Terminal
                                                                       1P                      2P                     3P                     4P                     5P                     6P                     7P                      8P                     9P                 10P              Period
Enterprise Cash Flow                                 $             -         $             -        $             -        $             -        $             -        $             -        $             -        $              -        $             -        $             -       $             -
Social Operating Expenses                            $             -         $             -        $             -        $             -        $             -        $             -        $             -        $              -        $             -        $             -       $             -
Cash Available                                       $             -         $             -        $             -        $             -        $             -        $             -        $             -        $              -        $             -        $             -       $             -


Future Value of Contribution to Parent*              $             -         $             -        $             -        $             -        $             -        $             -        $             -        $              -        $             -        $             -       $             -
Future Value of Investment Required*                 $             -         $             -        $             -        $             -        $             -        $             -        $             -        $              -        $             -        $             -       $             -
*If excess cash is available, it is contributed to the parent agency. If cash is needed, this is the additional investment required.


Discount Rate (same discount rate as investment to date)                    #DIV/0!


                                                                       1P                      2P                     3P                     4P                     5P                     6P                     7P                      8P                     9P                 10P
      Time                                                              0                       1                      2                      3                      4                      5                      6                       7                      8                     9
      Present Value of Contribution to Parent                #DIV/0!                  #DIV/0!               #DIV/0!                #DIV/0!                   #DIV/0!               #DIV/0!                 #DIV/0!                 #DIV/0!               #DIV/0!                #DIV/0!
      Present Value of Investment Required                   #DIV/0!                  #DIV/0!               #DIV/0!                #DIV/0!                   #DIV/0!               #DIV/0!                 #DIV/0!                 #DIV/0!               #DIV/0!                #DIV/0!


                                                    Contribution to             Investment                                                                                                      Contribution to            Investment
                                                       Parent                    Required                                                                                                          Parent                   Required
      Value in Terminal Period                      $              -        $              -                                                      Terminal Value                                    #DIV/0!                 #DIV/0!
      Terminal Value                                     #DIV/0!                 #DIV/0!                                                          Sum of PV of Cash Flow                            #DIV/0!                 #DIV/0!
      Present Value of Terminal Value                    #DIV/0!                 #DIV/0!                                                          Total Present Value                               #DIV/0!                 #DIV/0!




Projected Social Savings & New Taxes
                                                                                                                                                                                                                                                                                                Terminal
                                                           1P                      2P                     3P                     4P                      5P                    6P                     7P                      8P                     9P                    10P                   Period
Social Savings                                           #DIV/0!                 #DIV/0!                #DIV/0!                #DIV/0!                #DIV/0!                #DIV/0!                #DIV/0!                 #DIV/0!                #DIV/0!                #DIV/0!               #DIV/0!
New Taxes                                           $              -        $              -        $             -        $             -        $             -        $             -        $             -        $              -        $             -        $             -       $             -
Future Value of Social Savings + Taxes                   #DIV/0!                 #DIV/0!                #DIV/0!                #DIV/0!                #DIV/0!                #DIV/0!                #DIV/0!                 #DIV/0!                #DIV/0!                #DIV/0!               #DIV/0!


Discount Rate (Tax Adjusted Muni Rate))                         0.00%


                                                           1P                      2P                     3P                     4P                      5P                    6P                     7P                      8P                     9P                    10P
      Time                                                  0                         1                   2                      3                       4                     5                      6                        7                     8                      9
      Present Value of Social Savings & Taxes            #DIV/0!                 #DIV/0!                #DIV/0!                #DIV/0!                #DIV/0!                #DIV/0!                #DIV/0!                 #DIV/0!                #DIV/0!                #DIV/0!


      Value in Terminal Period                           #DIV/0!                                                                                  Terminal Value                                    #DIV/0!
      Terminal Value                                     #DIV/0!                                                                                  Sum of PV of Cash Flow                            #DIV/0!
      Present Value of Terminal Value                    #DIV/0!                                                                                  Total Present Value                               #DIV/0!




      12496035-3c11-4ecc-8563-d35bc7d9d534.xls                                                                                                    14 of 16                                                                            The Roberts Enterprise Development Fund
Calculation of Projected Values



Projected Social Operating Expenses
                                                                                                                                                                                                                 Terminal
                                                                 1P           2P           3P           4P                    5P           6P                     7P           8P           9P       10P           Period
Future Value of Social Operating Expenses      $             -        $   -        $   -        $   -        $           -         $   -        $             -        $   -        $   -        $   -       $       -


Discount Rate (Tax Adjusted Muni Rate))                 0.00%


                                                                 1P           2P           3P           4P                    5P           6P                     7P           8P           9P       10P
     Time                                                         0            1            2            3                     4            5                      6            7            8           9
     Present Value of Social Savings & Taxes   $             -        $   -        $   -        $   -        $           -         $   -        $             -        $   -        $   -        $   -


     Value in Terminal Period                  $             -                                               Terminal Value                         #DIV/0!
     Terminal Value                                #DIV/0!                                                   Sum of PV of Cash Flow             $             -
     Present Value of Terminal Value               #DIV/0!                                                   Total Present Value                    #DIV/0!




     12496035-3c11-4ecc-8563-d35bc7d9d534.xls                                                                15 of 16                                                      The Roberts Enterprise Development Fund
             Input Sheet                   Status
Background Information                Incomplete
Historical Investment                 Incomplete
Income Statement                      Incomplete
Social Impact                         Incomplete
Cash Items                            Incomplete


Return to SROI Summary Results




12496035-3c11-4ecc-8563-d35bc7d9d534.xls       16 of 16   The Roberts Enterprise Development Fund

				
DOCUMENT INFO
Description: Calculating a Contribution Format Income Statement document sample