Wateraid

Regular Giving (RG) has been a key element for WaterAid’s fundraising income and has nearly doubled over a 5 year period.  Forward growth and continued forecasting of RG levels was seen as vital at several levels, including overall strategic planning, understanding trends and performance of channels in recruitment, and to tune annual performance.

Prior to Adroit’s involvement, WaterAid had 3 separate models that were used for different forms of forecasting regular giving income across different time periods, and with some varying assumptions.

– Income Forecasting (Actual income over forward 5 years for strategic budgeting)
– Lifetime Value Model (5 Year)
– Monthly forecasting for current financial year

The objective of the project was to replace these tools with a more sophisticated solution for forecasting, incorporating WaterAid’s own data and providing improved features for “what if” and scenario planning.

A large component of the project was to take existing data structures (held as a SQL database), extract all relevant fields and tables, and load the data and engineer it in IBM SPSS for direct import to our ExcelEnt reporting engine.

The key advantage of this was that the latest data could easily be loaded into the ExcelEnt tool with minimal intervention from users. Only one or two assumptions needed to be coded by hand; the rest were based on actual data, correct as at the point of loading.

The implication of this approach is that data will be updated, accurate and consistent with internally reported metrics; having ‘known’ single-truth results ensures that greater time can be dedicated to interpretation than assessing the underlying accuracy of the data and assumptions.

Once the WaterAid data had been extracted, engineered and imported into ExcelEnt (see screenshots below, we were able to undertake top-level analysis and feedback these results for sense-checking. This was only needed on the original load – once proven accurate, this step could be by-passed on further updates.

The tool was delivered to WaterAid and supported by face-to-face workshops to ensure team members had a robust understanding of the tool, and were confident n how to use the ‘what-if’ functionality. This involved the following:

1. Run-through of the data we had extracted.
2. Summary of the data engineering we had undertaken, including definitions and business rules we had created (all of which had been communicated to the client team across the project anyway).
3. Brief training walk-through in terms of broad reporting capabilities so WaterAid were armed to take the ExcelEnt application and use it themselves.
4. Summary of the key findings in terms of future income, strengths and weaknesses of different recruitment methods, the efficacy of different recruitment channels, (charted) attrition rates for key supporter demographics, and LTV of key supporter groupings.
5. Implications for their strategic planning in the year ahead.