Data Review: Templates & Questions
"Computing is not about computers any more. It is about living."-- Nicholas Negroponte
It's been a few tough years for everyone at the Underworld Symphony. It's not that they've done everything wrong, they've just taken a long time to realize what's going on, and to begin to think their way strategically out of their difficulties. There's an essential human story behind the development of the Underworld Symphony, and a huge range of artistic experiences by composers, conductors, musicians and audiences that we need to appreciate before we can say we fully know what the life of this Symphony has comprised. But reviewing and analyzing everything about the Symphony that can properly be represented in numerical form is going to tell us a great deal of the story, more than we realize, and will serve to identify and articulate the key issues in a way that no other approach is able.
For one thing, this review will allow us to look past the barrage of everyone's emotions, opinions and memories to a different perspective based on measurable outcomes and achievements. This is an important place to get to if we want to make well-informed judgments on the Symphony's current position, and plan reasonably for the future. If the Symphony then decides to depart from reason in its decisions about future initiatives (there is some room here, after all, for inspiration) we shall all know that, and be able to measure the risks.
Whether your organization is thriving or struggling at present, what's true for the Underworld Symphony is true for you. So these Data Templates are both a series of formats for numerical data about your organization--to adapt for your use in Advancement--and the outlines of a case study. Thinking about what the numbers tell you about the Underworld Symphony may help you and your Planning Consultant to see parallels or contrasts in your own work, whatever artforms you work in, and no matter how large or small your organization. Exploring the data formats in action may help you to identify more useful (and better!) formats to analyze your own figures.
What follows is therefore divided into two sections:
The ten Data Templates themselves are available as downloadable files and/or on disk by mail by contacting the Bay Consulting Group. [Download]
COMMENTARY ON THE DATA TEMPLATES
The templates are intended to offer sample formats for the arrangement of data on your operating budget and balance sheet, and on aspects of earned and contributed income. They do not cover all areas that you may want to present and analyze, but should provide an approach which can be utilized in the presentation of additional information as you need it. Although it is obviously very important, analysis of earned income (particularly ticket sales, enrollment, attendance, income from services) is included only in one sample template as it is the most idiosyncratic of sources of income and needs to be addressed organization by organization.
The templates concentrate on presenting and analyzing financial data, as this is considered to be at the heart of the review process. The information presented branches out a little from the financial, to cover membership numbers and donors by category, member and subscriber renewal rates, and so on. Building on these analyses, a lot more can be done to review non-financial data, such as audience, student or donor demographics and preferences. These essentially qualitative and attitudinal reports are vital to well-focused marketing and development efforts. Because they usually depend upon individuals providing specific additional information (and cannot be completed using only data held in-house), they are typically carried out as discrete surveying exercises and are therefore not included here. But use of these templates will point up where such surveys would crucially inform planning the development of new strategies.
The templates also concentrate by-and-large on analysis of income, rather than expense. This is because your income derives from a multitude of external sources that need to be tracked and understood in detail over extended periods; while it is equally important to be clear and purposeful in allocating your financial resources, the expenditure side is more readily organized as a matter of course via your Chart of Accounts, and reported on regularly as part of your accounting system.
Please note that, although the templates are filled out for the fictitious Underworld Symphony, they are not designed to fit exactly with any particular artistic discipline or single organization. What the templates offer is a standardized presentation format that you and your Planning Consultant can adapt and develop to meet the needs of your organization in summarizing and interpreting historical data. Replace the Underworld Symphony's figures with your own. From these analyses you can then start to build up future projections in a realistic fashion, in the light of recent trends. This approach--of "trying out" future scenarios and testing their implications well before committing to a particular budget--should become an integral part of the way you do business.
These templates are all spreadsheets created in Microsoft Excel 5.0 for Windows. They are extensively formulated, in that all the "totals" rows, all the percentages, and many of the analyses are calculated by formula. Note that each template identifies the date on which it was prepared, and the person responsible. This is essential in keeping track of progress!
If you use the templates on diskette, it would be well to add "protection" to all formulated cells after downloading, so that you cannot overwrite them. If you want to use a template as it is, go through it cell by cell before inputting data, to see how it is composed. Minor changes to row or column headings, for instance, can then be easily made. If you want to make more significant changes, please be aware that you may disturb the references in some formulae ("REF!") and have to reformat them. Also, you may have to copy and adjust formulae if you add rows or columns.
Comments on each of the templates are provided below. Feedback and ideas for additional useful analyses would be welcomed.
TEMPLATE 1: Summary of Operating Results over 4 fiscal years (DT1)
All the rows for data input are lettered, and these letters (together with their respective row headings) are the same in this template and the following two. This ensures that all three match up and facilitates the addition of notes. The second column for each year expresses all income and expense items as percentages of total operating expense for that fiscal year. This provides a useful trend analysis on sources of income and resource allocation.
It is envisaged that the row headings will in effect be a summary of the arrangement in your Chart of Accounts for the allocation of income and expense. You may wish to increase or decrease the amount of detail given here; templates 1, 2 and 3 have deliberately been organized to fit one standard page (it reduces copying and enormously helps understanding), but you may have to spill over to two pages. The changes in row headings that you may want to make to suit your circumstances will probably occur most in "Earned Income" and in "Program Expense."
Row E: Income from membership subscriptions/fees is viewed as earned income. This is because it comprises (at least in part) fees for services provided. It also distinguishes this income from individual gifts. You may want to consider whether this distinction is useful to you, and exactly how it is made.
Rows P and AA: Special promotional and fundraising events are "grossed up" with full income and expenses shown.
Row Q: Only in-kind support that has an objectively verifiable monetary value is included here; it is expensed as part of the relevant expense line. It is advisable to maintain a separate budget detail showing the precise expense items that are being fully or partially underwritten by in-kind support.
Row CC: As the provision of (or lack of) benefit packages to staff can be a subject of much discussion, you may wish to separate "taxes/benefits" from the basic "salaries" by adding an extra row.
Organizations adopt differing policies in displaying Depreciation. As a non-cash item, it is not included here, although a due allowance for depreciation (where appropriate) would be shown in putting together a complete picture of your annual financial results.
As part of planning, you should get into the habit of comparing your board-approved budget projection with the actual results. As you plan your future activities, the comparison between your projection and the actuals will guide you toward more accurate budgeting, as well as making clear its limits.
TEMPLATE 2: One-year summary of Operating Results by Program (DT2)
This template takes one year of operating results and displays them by program, with a final column for "General Management." The first column of figures ("Operating Summary") is therefore the same as for the corresponding year on Template 1. The columns "% of total expense" show each figure as a percentage of total operating expense (not total expense for that program alone). This provides a strong indicator of priorities in resource allocation.
The program headings ("Romantic Series," "Classical Series," etc.) should be altered to describe your actual programs. It is your decision as to how many of these you can usefully distinguish. Your decision should relate closely to the allocations you maintain on your Chart of Accounts by means of a "department" code.
The General Management column is the place for unrestricted income and for administrative expenses that are not program-related. You may develop a policy of allocating some unrestricted income to specific programs -- a note to this template should explain that.
Not only is this template valuable in analyzing historical data, it is the essential starting-place in building up future organizational operating budgets.
TEMPLATE 3: Distribution of salaries/fees by Program (DT3)
Template 2 adopts the common policy of allocating direct year-round personnel costs across programs according to the percentage of time expended on each. This can be seen in Rows R and CC (Row S shows fees to artists retained only for a series of specific events or separate productions, with costs allocated to each relevant program). If this policy is adopted, the balance of salaries/fees is placed in the final (General Management) column.
Template 3 provides the rationale for the figures given for direct personnel costs in Template 2 and should always accompany any version of Template 2. It identifies all relevant personnel and their total pay, and then allocates percentages of their time and cost across the same set of columns as in Template 2. Because the balance of time spent naturally varies by position and job responsibilities, this allocation cannot be done in a single percentage across personnel as a whole, but must be built up person by person. The last line of Template 3 gives an "effective" percentage allocation by program across all personnel, derived from the cost implications expressed in Template 2.
This allocation of personnel time and cost is an important exercise for three reasons: first, in constructing the allocations from tracking of time actually spent, staff will be led to a useful comparison with their stated, or understood, roles in your organization; second, the board will come to understand better how your organization actually utilizes its human resources; and third, board and staff will gain a clearer picture of the true cost of each program.
TEMPLATE 4: One-year summary Cashflow Analysis by month (DT4)
Unless it has been customary in the past, it is unlikely that you will be able to construct this summary for completed fiscal years. However, it provides as important an indicator of the financial feasibility of your operations as does the customary operating budget, so all future budgeting should be accompanied by a cashflow projection of this type.
As the chart deals only with cash (in the bank) "In-kind" income should be excluded, as should the balancing expense item. If you account for your business on an accrual (rather than a cash) basis, the final column ("Total") will also be different from the operating budget for the same year, because the accrued sums will here be laid out as income or expense in the actual month received or incurred.
It is advisable to be cautious in estimating cashflow: place income later than you might think, and expense earlier. This will show up more clearly those times when cash may be in short supply. You can then review the monthly figures (and your policies on payables and receivables) to ameliorate the position. You may also have to consider adjusting programming and fundraising schedules.
Use the cashflow analysis as a "what if" tool to model different financial scenarios, depending on timing of events, size of staff and scheduling of income and expense items. Such models help to clarify the levels of risk you might be taking in differing circumstances. Use the "header" to identify with care the various versions of the cashflow projection that you may maintain.
You should get into the habit of notating actual monthly cashflows after the event in a similar format (as with the Summary Operating Budget). As you plan your future activities, the comparison between your projection and the actuals will guide you toward more accurate forecasting.
TEMPLATE 5: 4-year Historical Summary of Financial Data (DT5)
This template is based on one developed by the National Arts Stabilization Fund. Its distinctive feature is that it provides an analysis of key elements in the balance sheet as well as the operating account. It is therefore of greatest use to organizations that already have, or plan to develop, a capital base beyond their current assets and liabilities.
The analysis of the balance sheet organizes the data into various tiers -- first, "net current position," (a measure of liquidity), then "working capital reserve," (a revolving internal loan fund applied to meet cashflow exigencies), then other designated reserves (to meet unexpected operating loss, or to invest in artistic development, for instance) and finally "permanent endowment." Expressed as percentages of the operating budget (at the bottom of the template), these tiers give an indication of the overall financial strength of the organization.
TEMPLATE 6: 4-year Analysis of Ticket Sales by Program (DT6)
This template is the only one to address income earned from sales of services. This is the area that naturally varies most according to the nature of your organization's work. You will need to decide how best to organize and analyze the income you derive from all your services (such as entry fees, tickets for performances, student fees, publishing, advertising, merchandising, contracted income).
This template is based on an orchestra giving a number of different concert series. It identifies subscribers, single ticket sales, and complimentaries separately, and concentrates on a few key indicators of performance (ticket yields, subscriber renewal rate, total occupancy and percent of capacity sold). Data is recorded for each ticket price, so that trends within the overall sales per event can be seen. In addition to admission prices of this sort, you may have discounts and special packages, which should be treated as separate price bands. You should also keep separate production reports by event. This template aggregates that data (adding new information) to give a broader overview and to permit comparison between different program areas.
TEMPLATE 7: 4-year Analysis of Individual Contributions (DT7)
This template analyzes individual giving by size of gift, simultaneously separating out board giving from other donors. The category titles and amounts are no more than an example and should be modified to fit your organization, as appropriate.
At the bottom of the template various analyses are given as assistance in determining trends. You may wish to add other indicators according to your circumstances.
The template is useful in ensuring that future projections for individual giving are properly related to recent actual trends. By analyzing past performance, and then building budgets from the bottom up, you can turn number-plugging into a much more scientific process, and relate fundraising strategies to quantifiable results.
TEMPLATE 8: 4-year Analysis of Membership Revenue by Category (DT8)
Some organizations derive all or part of their individual support from the maintenance of a membership scheme, wherein Members at various levels receive a defined set of benefits in return. As with Template 7, this form presents Members by category (including a Corporate Membership scheme), and then offers some additional analyses at the bottom of the page. Membership development campaigns are particularly suited to planned increment, with specific types of solicitation aimed at new members, renewing members, lapsed members, and upgrades to higher categories. This template provides a simple way to target such campaigns and to track the results.
If your board giving takes the form of purchasing Membership in your organization, you will want to give that its own column each year on this template (rather than on Template 7).
TEMPLATE 9: 4-year Analysis of Membership Benefits and Costs by Category (DT9)
Linked to the previous template, this one provides important information on the return on membership for the organization--the expense side of this equation is easily overlooked. This form also helps to guide organizations towards the refinement of benefits (so they consume a lower percentage of the gross income) and helps to identify the high-yielding categories to which Members can most effectively be upgraded. The benefits listed here, as well as the costs per member and average gifts, are all fictitious.
TEMPLATE 10: Sample Analyses of 4-year Trends
These final three pages offer a broad range of trend analyses, using information from the previous templates. Some of the analyses relate to year-on-year changes in a single category of income or expense, others track comparative ratios between different elements of the operating account. Notice the ones that track "returns"--the ratio of expense to the income it is intended to produce. These are important dynamics of organizational performance.
The idea here is no more than to suggest some possible angles of inquiry, from which you can develop your own analyses which speak to emerging issues. With all the other templates filled, you should have plenty of data on which to base your analysis.
"Don't pay any attention to the critics. Don't even ignore them."
DATA REVIEW QUESTIONS
SO, WHAT DO WE DO NOW? CAN WE DO SOMETHING ELSE?
The most important part of the Data Review starts when you've arranged all the relevant data in suitable formats and done some basic analysis. Even if this exercise has been carried out by one person, the rest of the Review should involve a larger planning group. The central question is: "What do we notice here?" and the involvement of a group will provide a much richer set of insights. It will also build a genuine understanding of the issues your organization is facing and result in better-informed responses to the challenges ahead. If good questions that point to key pressure points are laid out in advance, you will find that a meeting of your planning group--devoted to considering the Fact File you have created about your organization--will be a lively occasion. It is very important to complete the Data Review process by compiling a narrative report on the findings of the planning group, once you've decided what the factual history of your organization actually means. This point-by-point description of the main features and trends, identifying the strategic issues that demand resolution, will do a great deal to focus attention on critical areas and identify priorities for action. It will also serve as an invaluable reference point in evaluating your plan, and an orientation tool for newcomers to your organization.
Here are some questions that you may find relevant. They are intended to stimulate your own thinking and should not be used slavishly. You know your organization better than anyone, and will be able to come up with more specific and more searching questions. As you explore these questions, try to reduce the menu of questions in each area of analysis to the three or four most important. Once you have reached a firm, shared understanding of "what?" and "why?" (i.e., the contours of your organizational life to date, and the factors that have affected it), you will turn naturally toward "how?" (i.e., questions about the ways in which you can strengthen your development in future years). In other words, your questions will become strategic. It is these questions that you will need to prioritize and address through planning.
The questions below have been arranged to follow the ten Data Templates, but some issues overlap and Templates have been brought together.
DT1: 4 years of Operations in summary
DT2: Each year's Operations by Program
DT3: Time spent by personnel on Programs
DT4: Each year's Cashflow
DT5: Balance Sheet and Operating Activity
DT6: Income earned from Programs
DT7: Individual Contributions
DT8: Membership Revenue
Please send us your comments on this Essay.
National Endowment for the Arts · an independent federal