A very handy guided spreadsheet from The Wise Owl Partnership to establish a reserves amount or range and support the development of your reserves policy. It has been designed to be used in conjunction with CC19 reserves guidance from the Charity Commission

– NCVO

 

The Wise Owl Partnership contacted Age UK with regard to their reserves calculator.  They gave us the opportunity to test out the calculator and explore how it might support our work.  Through testing out the tool we identified some additional features and changes that we felt would optimise the tool for our use.  The Wise Owl Partnership responded very quickly and positively to our feedback and was able to make the changes that we suggested.  We will now use the calculator as a tool in the work that we do to support our network with financial governance and sustainability

– ageUK

 

 

Download Your FREE Reserves Calculator

Click on the image to download your FREE Reserves Calculator (requires MS Excel)

Scroll down the page and follow the instructions on how to fill in and use the calculator.

The Reserves Calculator will guide you through the steps required for setting a reserves policy using a risk based approach. It takes into account all the factors set out in the Charity Commission’s guidance and helps you to work out the amount or range of reserves needed for your charity.  Use the information from the calculator to create your Reserves Policy to meet the specific needs of your charity.  The Calculator can be updated as your charity’s financial circumstances change, so you can monitor your charity’s reserves and update your policy at any time.

 

What are Reserves and Why Should a Charity Hold Them?

Reserves, sometimes known as Free Reserves are the unrestricted funds held by a charity that it may spend on any of its charitable purposes. A charity might look to maintain a level of reserves in order to have a financial buffer against unexpected falls in income or increases in expenditure.

One simple method of setting a reserves level is to look at a charity’s total expenditure over say a 12 month period and hold reserves that would cover that expenditure for a given number of months.

For some charities this might be a perfectly adequate approach, but it doesn’t take account of the reliability of the charity’s different sources of income or fluctuations in expenditure. Taking a simple approach can therefore leave a charity either holding too little in reserve, leaving it potentially exposed, or too much in reserve, which is money that could otherwise be put to better use in funding its charitable purposes.

There is also increasing evidence that potential funders want to see that charities have a robust and well thought out reserves policy in place before they will donate or commission services. In an uncertain climate, therefore, you may wish to take a more considered approach to setting your charity’s reserves level.

In its guidance Charity reserves: building resilience the Charity Commission for England and Wales stresses that trustees need to explain their charity’s reserves by showing that:

a) the level of reserves is based on a reasoned policy with a clear understanding of how and when reserves will be drawn upon;

b) the charity has sufficient reserves to avoid financial difficulties; and

c) the charity is being transparent about the level of reserves it holds.

The Reserves Calculator has been designed to incorporate all of the principles contained in the guidance into a 5 page spreadsheet which will enable you to set your charity’s reserves level, monitor your reserves and, if necessary, adjust them over time.

Use the information you have entered into the Calculator to create a Reserves Policy that fully complies with Charity Commission guidelines and meets the specific needs of your charity.

 

How to Complete the Reserves Calculator

The Reserves Calculator is designed to help you to work out how much your charity needs to keep in reserves according to the level of expenditure commitment you want to cover. It will help you to identify the level of risk associated with your charity’s forecast income and expenditure and to consider other factors that increase or decrease the need for your charity to keep reserves.

Follow these instructions to fill in each of the tables. The calculator will then work out the amount of reserves you need to put aside for your charity depending on the level of expenditure commitment you want to cover, taking into account other available funds.

Bear in mind that the more accurate the information you enter, the more meaningful the overall results will be.

 

Unrestricted Income (click on the Income tab)

Dates
In the boxes labelled dd/mm/yy enter the dates for which you are forecasting your income and expenditure (e.g. 01/04/17 to 31/03/18). This will commonly be a period of 12 months although you can choose whatever period of time you wish. Remember however that your results are likely to be less accurate the further you forecast into the future.

Charity Name
Enter the name of your charity here.

Source of Income
List all of your charity’s forecast sources of Unrestricted income for the time period you have chosen. Note that unrestricted means that you may spend the income on whatever meets your charity’s objectives without any restrictions.

List every source of unrestricted income, even if you do not anticipate spending the funds within the time period you have chosen.

Income
For each source of income, enter the amount you expect to receive.

Number of Donors (see pop-up box)
Enter a number between 1 & 4 to score the number of donors for each source of income.

1= 21 or more donors
2= 5 to 20 donors
3= 2 to 4 donors
4= single donor

Certainty of Income (see pop-up box)
Enter a number between 1 & 4 to indicate the certainty of receiving each source of income.

1= Guaranteed
2= Highly likely
3= Quite likely
4= Uncertain

Stability of Income (see pop-up box)
Enter a number between 1 & 4 to indicate the relative stability of each source of income.

1= Has risen each year for the last 3 years
2= Has shown an upward trend but has fluctuated over the last 3 years
3= Has remained static for the last 3 years, OR New source of income / charity has less than 3 years income to compare
4= Has shown a downward trend over the last 3 years

 

What the Calculator Will Do

Based on your scores, the calculator will evaluate the reliability of each source of unrestricted income for your charity and provide you with a visual representation of the aggregated reliability of your income using the following colour code:

Green= High Reliability
Amber= Medium Reliability
Red= Low Reliability

 

Expenditure from Unrestricted Income (click on the Expenditure tab)

Item of Expenditure
List all of your charity’s anticipated expenditure from Unrestricted income for the time period you have chosen.

Impact of Cutting Expenditure (see pop-up box)
Enter a number between 1 & 4 to score the impact on the charity if you had to cut each item of expenditure in terms of how many people it would affect. Include beneficiaries, staff members and volunteers in your assessment.

1= 5 or less people
2= 6 to10 people
3= 11 to 20 people
4= 21 or more people

Priority of Expenditure (see pop-up box)

Enter a number between 1 & 4 to score the priority of each item of expenditure using the following scale:

1= Not necessary to meet the charity’s objectives
2= Quite important to meet the charity’s objectives
3= Very important to meet the charity’s objectives
4= Essential to meet the charity’s objectives

Source of Funding (see pop-up box)
Enter a number between 1 & 4 to score the source of funding for each item of expenditure using the following scale:

1= Funded by unrestricted general funds
2= Funded by unrestricted grant funding
3= n/a (do not use this number)
4= Obligations under contract

What the Calculator Will Do

Based on your scores, the calculator will evaluate the commitment level associated with each item of expenditure from unrestricted funds for your charity and provide you with a visual representation of the aggregated commitment level for your expenditure using the following colour code:

Green= Low commitment
Amber= Medium commitment
Red= High commitment

The calculator will calculate the level of funds you will need to set aside to cover your High, Medium and Low Commitment expenditure from unrestricted income and transfer the aggregated amounts to the Reserves table.

 

Restricted Income (click on the Restricted tab)

Restricted income has to be assessed separately, in parallel with the expenditure that it funds. This is because, unless the restriction allows, you cannot use that source of income to fund an alternative item of expenditure, as you can with Unrestricted Income.

Source of Income
If the Income carries any restriction on how the charity may spend it, enter the details and amount of the income you are forecasting. Follow the same instructions as you did for scoring your Unrestricted Income in order to calculate the Reliability Score of your Restricted Income.

List all income even if you do not expect to spend it within the period you are forecasting.

 

Expenditure from Restricted Income

Enter the details and amounts for all planned expenditure from restricted income within your forecast time period, on the same row as the income which funds it. Follow the same instructions as you did for scoring your expenditure from unrestricted income but with the following variation:

Source of Funding (see pop-up box)
1= n/a (do not use this number)
2= n/a (do not use this number)
3= Not under contract
4= Under contract

Restricted Funds Already Held
If you already hold restricted income (eg. carried over from a previous financial year) which may be used to finance any of your items of expenditure, enter the amount in this column against the item of expenditure it is funding.

Useful Tip
If any of your restricted income finances more than one item of expenditure, you may wish to apportion the income according to its corresponding expenditure and list it on separate rows. This means that you will be able to score each item of expenditure separately.

What the Calculator Will Do
The calculator will calculate the level of funds you will need to set aside to cover your High, Medium and Low Commitment Expenditure from Restricted Funds (taking into account any funds already held) and transfer the aggregated amounts to the Reserves table.

 

Designated & Additional Funds (click on the Additional tab)

List any additional funds that you may need to set aside to cover forecast expenditure that either;

i)  falls outside of your income and expenditure forecast period OR
ii) might be needed in addition to the planned expenditure which you have already listed under Expenditure from Unrestricted Income.

Description of Funds & Reason for Keeping
Give a brief description of the additional funds you need to keep in reserve and the reason for keeping them. As a guide, here are some reasons why you might want to keep additional funds:

Additional funds needed to be held for contingent operational costs
Consider whether your charity needs to keep aside additional funds for extraordinary day-to-day operational costs. For example, might you need to employ agency staff to cover sick absence, maternity leave or a member of staff leaving?

Additional funds needed to be held for other contingency purposes
Consider whether your charity needs to keep funds aside in case of an unexpected emergency or other unforeseen need for funds. For example, what is the risk of a sudden repair bill, or an urgent need to help beneficiaries, or to fund a project at short notice?

Additional funds needed to fund short term deficit in cash flow
Look at your cash flow forecast to see whether there is likely to be a need to fund short term deficits in cash flow. For example, will money need to be spent before grant funding is received?

Additional funds needed to cover any other known liabilities
Consider whether your charity has any specific liabilities that will need funding in the future. For example, will you have to pay for dilapidations on a lease?

Additional funds needed to cover planned commitments or future designations that cannot be met by future income
Consider whether your charity needs to set aside funds to pay for future commitments, projects or capital spending that cannot be met from anticipated future income alone.

Additional funds needed to fund future opportunities that may present themselves
Consider whether your charity needs to set aside funds in preparation for new opportunities as they arise. For example, is it possible that the charity could apply for a future grant that requires match funding?

Other Expenditure
List any other future items of expenditure that you consider reserves will be needed to cover.

Designated Funds
For each additional item of expenditure, consider whether the funds you put aside should be Designated.

Designated Funds are Unrestricted Funds (i.e. may be spent on any of the charity’s objectives) that the charity has decided to earmark for future spending.

Designated Funds are excluded from the calculation for the amount of reserves that the charity should hold. The charity does however need to explain the nature and amount of the Designated Funds and give an indication as to when they are likely to be spent.

Consider whether any of the funds you have listed as being needed are currently, or will be Designated Funds. If so, enter the following information in the relevant columns under Designated Funds:

i)   A date when you anticipate spending the funds
ii)  The total amount of Designated Funds that the charity requires
iii) The amount of Designated Funds that the charity currently holds

Additional Unrestricted Funds Needed
Enter an amount in this column for all other funds you have listed that need to be kept that will not be Designated by the charity. (Nb you will be given opportunity on the last table to enter the amount of reserves you already hold).

Risk Scoring Designated & Additional Unrestricted Funds
Although it is not strictly necessary to risk score Designated Funds for the purpose of your overall reserves calculation, it does give a guide as to the importance of this future expenditure. This could be helpful if the charity ever wishes to reassess its spending commitments and reallocate all or some of its designated funds to financing something else.

Impact of not Keeping Funds (see pop-up box)
Enter a number between 1 & 4 to score the impact of not spending (should the need arise) the funds you have identified in terms of how many people it would affect. Include beneficiaries, staff members and volunteers in your assessment.

1= 5 people or fewer
2= 6 to 10 people
3= 11 to 20 people
4= 21 or more people

Priority of Funding (see pop-up box)
Enter a number between 1 & 4 to score the priority of each item of expenditure.

1= Not necessary to meet the charity’s objects
2= Quite important to meet the charity’s objects
3= Very important to meet the charity’s objects
4= Essential to meet the charity’s objects

Likelihood of Needing Funds (see pop-up box)
Enter a number between 1 & 4 to score the likelihood of needing to spend these funds. Bear in mind that if the expenditure is certain then the item of expenditure should instead be shown in your Forecast Expenditure table.

1= Unlikely, but funds may be needed in exceptional circumstances
2= Not expected but a possibility that funds will be needed
3= Likely that funds will be needed
4= Very likely that funds will be needed

What the Calculator Will Do

The calculator will calculate the level of funds you will need to set aside to cover your High, Medium and Low Commitment Expenditure from Additional Unrestricted Funds and transfer the aggregated amounts to the Reserves table.

The amounts you have entered as Designated Funds are not transferred to the Reserves table as designated funds are not included in Reserves.

 

 Reserves (click on the Reserves tab)

The Reserves Calculator transfers the amounts from the other tables and displays them under the following headings.

(A) Totals from UNRESTRICTED Income & Expenditure
(B) Totals from RESTRICTED Income & Expenditure
(C) Totals from ADDITIONAL Unrestricted Funds (does not include Designated Funds)

(D) Sub-total of Reserves Needed (A plus B plus C)
The calculator will give you a sub-total of the reserves you will need to hold according to the commitment level you wish to cover.

Funds That Could Be Used Instead of Holding Reserves
This section of the Reserves Calculator asks you to decide whether the charity has other funds and assets available that could be used to meet the charity’s expenditure commitments as an alternative to keeping reserves. Before entering the amounts available, you should think very carefully about the positive and negative impact of using other available funds instead of keeping reserves.

The calculator provides you with a list of types of funds that a charity might commonly hold.

Enter an amount in the Amount column and a brief description of the available funds (max 1 line of text) in the box below each one.

Leave blank if the charity has no funds that fit the description.

Designated funds that could be reallocated
Designated funds are unrestricted funds that have been set aside for a particular future item of expenditure. Although they have been earmarked for a particular purpose, the trustees still have discretion to use the funds for something else.

Reserves Held by Subsidiary Undertakings
Charities may carry out activities or fund raising through one or more trading subsidiary. A trading subsidiary might itself hold reserves that can be drawn on instead of the charity keeping reserves.

Expendable Endowment
Expendable endowment is funds that have been given to be held as capital, but which the trustees have discretion to use as income. When invested, expendable endowment can give a relatively secure income stream to the charity, but trustees have the option of spending some or all of the fund. This flexibility might reduce the need to keep reserves.

Unused Overdraft Facility
Many charity bank accounts allow trustees to make use of an overdraft facility. This can be useful to cover expenditure if the trustees know that funds will be coming into the account in the future.

Permanent endowment that could be spent
Permanent endowment is assets (cash, investments or property such as land and buildings) that the trustees cannot use as income. It must be held permanently to be used by the charity (such as a property) or to produce an income, although sometimes property can be sold and the proceeds reinvested. The trustees cannot normally spend permanent endowment, but sometimes it is possible to get permission from the Charity Commission to use it as income.

Functional assets that could be sold
Functional assets are assets such as land, buildings or equipment that are used operationally by the charity. Where the trustees consider functional assets to be essential to the operation of the charity, they can be excluded from the calculation of reserves. However, you should consider carefully whether functional assets really are essential to your charity’s operations, or whether they could be sold and the proceeds used instead of keeping reserves.

Other funds
Enter the amount of any other funds available to the charity that could be used instead of keeping reserves that do not fall into any of the above categories.

If you are unclear whether a particular source of funding may be used, we would suggest that you consult your financial adviser.

(E) Total Funds That Could Be Used Instead of Reserves
This is the total of any amounts you have entered above

(F) Target Level of Reserves Required (D minus E)
The calculator deducts the total funds that could be used instead of reserves from the sub-total of reserves needed to give you a TARGET amount of reserves according to the commitment level you wish to cover.

This figure defaults to zero if the total of funds that the charity could use instead of holding reserves (E) is greater than the sub-total of reserves needed (D).

(G) Reserves (Unrestricted Funds) Currently Held
Enter an amount here if the charity currently holds reserves (unrestricted funds that have not been designated).

Reserves Currently Held LESS Target Level of Reserves Required (G minus F)
The calculator deducts your Target Level of Reserves from any reserves that you currently hold to give you a final total that indicates whether the charity has a reserves surplus or deficit according to the commitment level you wish to cover.

Reserves Needed to Cover Expenditure for a Fixed Number of Months or Range of Months
Reserves levels have so far been calculated to cover your expenditure commitments for the total number of months that you originally entered in your Forecast Income analysis.

You may however not wish to cover either your High, Medium/High, or All Expenditure commitments for the whole of this period of time.

You may instead wish to set your reserves level to cover expenditure for a fixed number of months, or alternatively to allow your reserves level to fluctuate within a range of months, say 3 to 6 months expenditure.

The final two tables allow you to do this.

Reserves Needed to Cover Minimum Months
If you wish to allow your reserves level to fluctuate to cover your expenditure commitments for a RANGE of months enter the lower number of months in the range in the Months column, beside the level of Expenditure Commitment you wish to cover.

If you wish to hold reserves to cover your expenditure commitments for a FIXED number of months, leave this column blank.

Reserves Needed to Cover Maximum or Fixed Months
If you wish to hold reserves to cover your expenditure commitments for a RANGE of months, enter the upper number of months in the range in the Months column, beside the level of Expenditure Commitment you wish to cover.

If you wish to hold reserves to cover your expenditure commitments for a FIXED number of months, enter that number of months beside the level of Expenditure Commitment you wish to cover.

What The Calculator Shows

In the Amount columns, the Calculator will now show the level of reserves needed to cover your minimum & maximum number of months expenditure (if you have selected a range), or maximum only if you have selected a fixed number of months.

The Calculator apportions the Totals from UNRESTRICTED Income & Expenditure (A) and the Totals from RESTRICTED Income & Expenditure (B), according to the minimum and/or maximum number of months you have selected.

The Totals from ADDITIONAL Unrestricted Funds (C) are NOT apportioned, but are included in the Reserves calculation in their entirety. This is because they are often going to be fixed items of expenditure which are generally either needed in full, or not at all. Therefore if you only wish to cover a proportion of this expenditure in your reserves, ensure that you have entered only the amount you wish to cover in your Designated & Additional Funds Risk Analysis.

Remember, Designated Funds are excluded from reserves, therefore any amounts that you have earmarked as Designated Funds are not transferred to the Reserves table.

Finally, the Calculator deducts the total of any Reserves that you currently hold from the amount needed to cover the maximum number of months you have selected, to indicate whether you are projecting a reserves Surplus or Deficit during the forecast period.

 

Using the Reserves Calculator

Writing Your Reserves Policy
Now that you have completed the Reserves Calculator, you can use the information it contains to prepare your charity’s Reserves Policy. Ultimately it will fall to the trustees’ judgement to decide how much should be kept in reserve at any time. We recommend that you familiarise yourself with the Charity Commission’s guidance Charity reserves: building resilience (CC19) when writing your policy.

The Reserves Calculator, is designed to form an appendix to your Reserves Policy, as it contains much of the information that you need to include in your policy.

Reviewing Your Reserves Policy
The Reserves Calculator enables you to monitor your reserves level(s) at intervals of your choosing.

If your charity’s actual income and expenditure levels vary from those that you originally forecast, by updating these entries in the appropriate tables, the Calculator will automatically re-calculate your Reserves level(s) for you.

Comments are closed.