True Cost Picture Workbook

The following is a preview of the True Cost Picture Workbook. For complete workbook features, please download the workbook file on the left hand side.
 

Table of Contents

About this Workbook

How to use

Step 1: Enter Data from Income Statement (Profit & Loss Statement)

Step 2: Identify All Contributions

Step 3: Identify your social costs

Step 4: Adjusted Income Statement

Summary

Double Bottom Line Report
 

About this Workbook

This Workbook is designed to help you implement some of the concepts covered in Financial Intelligence: A Guide for Social Enterprises.  We strongly recommend that you review the Guide to better understand the objectives of financial reporting and unique circumstances facing social enterprise.  Working through this Workbook will help you clearly see, thus help you to manage, costs that go into pursuing both your financial and mission objectives. 

This Workbook does not replace your accounting system or other financial reports.  Rather, it is an analytical tool to identify adjustments and reporting options that you may choose to incorporate more permanently in your accounting system and reporting package.

The Workbook is presented as an excel spreadsheet with multiple linked tabs where your financial data is accumulated, adjusted for hidden and social costs, and financial information is generated in a useful format to provide further insight into your financial performance.  Walk through the Workbook, step by step, to develop and understand your true cost picture.  Detailed instructions and guidance on how to use the information generated can be found under each tab.

Additional Tools:
 

Worksheet - Identifying and Measuring Hidden Costs and Contributions
Worksheet - Identifying and Measuring Social Costs
Case Study - Stable Roots Landscaping
Guide - Financial Ratio Analysis
 

How to Use

There are five basic steps to this worksheet:

Step 1: Tab 1 - Enter data from your Income Statement (Profit & Loss Statement)

Step 2: Tab 2 - Identify all contributions to your financial picture including the value of discounts, donations, volunteer time and costs covered by a non-profit parent agency (if applicable).

Step 3: Tab 3 - Estimate your social costs.

Step 4: Analyze your results.
 

Tabs 4 and 5 - You can now see the difference between core business performance and the social component of your business.
Tab 5 -  Useful ratios are automatically generated that provide further insight. 
Tab 6 -  Information is presented in the "Double Bottom Line" format, including % of sales analysis
 

Use the worksheet tabs to navigate through the worksheets in the tool. Data entered is automatically linked to other worksheets.

Refer to the Stable Roots Case Study for a complete illustration of the Workbook and related concepts.

 

Enterprise Name 
Period 


Step 1: Enter Data from Income Statement (Profit & Loss Statement)

Select your annual or monthly trial balance or income statement.  Enter the period of the statement in the first cell.  Enter data from your trial balance into the appropriate sections (Sales, Cost of Sales etc.)  If you do not separate out "Cost of Sales", use either section 2 or 3 to record all operating costs.  Notice that gross and net margin ratios are automatically calculated at the bottom of the page.  Data will be automatically linked to subsequent sheets.

Period

 

 

1. Sales 


 
 
Total$
2. Cost of Sales 


 
 
Total$
3. Other Operating Expenses 


 
 
Total$
4. Other Income/Grants 


 
 
Total$
5. Other Expenses 


 
 
Total$
Gross Profit
Net Profit (before Other Income)
Net Profit (including Other Income)
$
$
$
Gross Profit Margin
Net Operating Margin (before Other Income)
Net Operating Margin (including Other Income)
$
$
$


Step 2: Identify All Contributions

Use the accompanying Word Worksheet "Identifying all Hidden Costs and Contributions" to guide you through the identification and quantification of hidden costs and other contributions. A good starting point is to review the line items of your trial balance or income statement. Once you have determined that a hidden cost or contribution is significant enough to be included in your financial picture, enter the description and amount in the appropriate columns below, either as (I )"donations, volunteer, discounts" or (II) "covered by non-profit parent". These adjustments will be carried forward to subsequent tabs. 

Period

 

 

 IIIIII
1. SalesDescription of ContributionMethod of Calculation$ Estimate: Donation, Volunteer, Discounts$ Estimate: Covered by non-profit parentTotal Contributions (I+II)



 
     
Total  $$$
2. Cost of Sales     



 
     
Total  $$$
3. Other Operating Expenses     



 
     
Total  $$$
4. Other Income/Grants     



 
     
Total  $$$
5. Other Expenses     



 
     
Total  $$$


Step 3: Identify your social costs

Use the accompanying Word Worksheet "Identifying Social Costs" to guide you through the identification and quantification of social costs. Again, a good place to start is the line items of your trial balance or income statement. Once you have quantified all social costs, enter the information below. Adjustments will automatically carry forward to subsequent tabs.
 

1. SalesAdjusted TotalDescription of Social CostMethod of CalculationSocial Cost Portion
     
Total    
2. Cost of Sales    


 
    
Total    
3. Other Operating Expenses    


 
    
Total    
4. Other Income/Grants    


 
    
Total    
5. Other Expenses    


 
    
Total    


Step 4: Adjusted Income Statement

 IIIIIIIVV
SalesFrom Statement + All ContributionsAdjusted Total I+IISocial Cost AdjustmentsBusiness Portion III-IV



 
     
Total$$$$$
Cost of Sales     



 
     
Total$$$$$
Other Operating Costs     



 
     
Total$$$$$
Other Income     



 
     
Total$$$$$
Other Expenses     
      
Total$$$$$
Net Income$$$$$


Summary

The summary provides key income statement results, breaking results down between core business and social costs. Financial ratios can be compared to other periods or similar businesses to measure performance trends. To see the full income statement detail according to different views, see the 'full statement' tab.
 

 social & business costsjust social costjust business
1. Sales
2. Cost of Sales
3. Other Operating Expenses
4. Other Income / Grants
5. Other Expenses
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Gross Profit
Net Profit (before Other Income)
Net Profit (including Other Income)
$
$
$
$
$
$
$
$
$
Gross Profit Margin
Net Operating Margin (before Other Income)
Net Operating Margin (including Other Income)
   

Negative social costs in sales refer to revenue not earned due to mission. Comparable business costs depict what would have been earned if not for mission. (assuming revenue would have been earned in market).

Business Cost Recovery Ratio:
(Sales/Total Business Costs)
To what degree business revenues are covering business expenses (1=100%)

Reliance on Grant and other Income:
(Grant Income/Total Revenue)
Shows grant income as a percentage of total revenues from all sources.  This shows to what extent your business is relying on grant income rather than business income

Operating Self-Sufficiency:
(Sales Revenue/Total Costs)
Measures the degree to which all the enterprise's expenses are covered by its core business and is able to function independent of grant income
 

Double Bottom Line Report

The Double Bottom-Line Report is a useful presentation format that can be easily derived from the information generated by this workbook. It clearly delineates between traditional business performance and mission performance. Funders will clearly see the social costs associated with your operations, information they need to determine the extent to which they are willing to subsidize your business. Funders may choose to limit subsidy to mission-related costs or may go beyond that to support actual enterprise-building outlays such as capital investment. You will also clearly see if the cost of achieving your mission objectives are increasing or decreasing over time. This will help managers and stakeholders assess the effectiveness of your programs.

The “Percentage of Sales Analysis” expresses every line item in relation to revenue. This type of analysis provides a good overview of underlying cost trends in relation to revenue levels and provides much better insight than a dollar-to-dollar comparison. Tracking these rates over a number of periods and years is most useful. This analysis can also be applied to balance sheet accounts.

Note that social costs have to be inputted manually from Column IV of the Adjusted Income Statement since these items can come from any line on the income statement.