Yes, if you have at least two years of weekly or monthly counts. Excel's Forecast Sheet or a chat assistant analysing your spreadsheet can project seasonal patterns, such as winter peaks and holiday dips, as a likely range. They cannot foresee shocks like a nearby service closing, so pair the numbers with what referral partners tell you.
The useful output is a range with trigger points, not a single number. "Between 180 and 230 food parcels a week in January" lets you plan volunteer shifts and stock orders. "207 parcels" invites false confidence, and the first week it is wrong, people stop trusting the forecast. Most of the value comes from being ready for the high end.
What to count, and how much history you need
Forecasting needs a consistent count at a regular interval. Many small charities have the data but spread across referral forms, sign-in sheets and a case-management system. Decide on one unit per service first:
| Service | Unit to count | Interval | Minimum history |
|---|---|---|---|
| Food bank | Parcels issued (and people fed) | Weekly | Two full years, to see two winters |
| Advice line | Calls answered plus calls missed | Weekly | Two years; missed calls matter as much as answered ones |
| Youth club | Attendances per session | Weekly, term time only | Two school years |
| Befriending | New referrals | Monthly | Three years, since monthly data has fewer points |
| Emergency grants | Applications received | Monthly | Three years |
Two traps to catch now. Count demand, not just what you delivered: if you turned 20 families away in a busy week, a forecast built only on parcels issued will underestimate every future peak. And mark weeks when you were closed or short of stock, so they aren't read as quiet weeks.
Getting clean counts out of messy records
Few small charities have a tidy weekly column waiting. More often there are paper sign-in sheets typed up by volunteers, a referral spreadsheet and a case system that exports one row per visit. AI is genuinely useful here, as long as you check its assumptions. An illustrative request to a chat assistant, with names already removed from the export:
This export has one row per food bank visit: visit date, household
ID, parcel size (S/M/L), referral source. Produce a weekly table
(week starting Monday) with: parcels issued, distinct households,
new households (first ever visit). Tell me how you handled any
blank dates or duplicate rows, and show week 1 worked by hand.The first answer counted a household twice in weeks when it collected two parcels, so "distinct households" was inflated by about a tenth. Asking it to show week 1 worked by hand exposed the problem: two rows for the same household ID on the same day were counted separately. Once corrected, the weekly table became the base for everything that follows. Check any AI-built table against a week you can count yourself from the original sheets before you forecast anything from it.
Three ways to build the forecast
Excel's Forecast Sheet (the quickest)
Put dates in one column and counts in the next, select both, and on the Data tab choose Forecast Sheet in the Forecast group. Excel uses a method called exponential smoothing (the FORECAST.ETS function), which weights recent weeks more heavily and detects repeating seasonal cycles on its own. It draws a chart with a shaded band, by default the range where it expects 95% of future points to fall. Two practical details from Microsoft's documentation: the dates need consistent intervals, and if fewer than 30% of points are missing Excel fills gaps by interpolating from neighbouring points. That second point matters for closure weeks: remove the count for a week you were shut rather than entering zero, so Excel interpolates instead of learning a false dip.
A chat assistant with data analysis
Upload a CSV of your counts to a chat assistant that can run analysis, such as ChatGPT or Claude, and ask it to build a seasonal forecast, show its method and give a range. This route is better when you want explanations ("why is February higher than December?") or want to test what happens if a factor changes. Remove names and case details first; the file should contain only dates and counts.
Google Sheets with a seasonal index
Google Sheets' built-in FORECAST and TREND functions fit a straight line and don't handle seasonality. You can still do it by hand: work out each month's average as a percentage of the yearly average (January might be 125%, August 85%), forecast the underlying trend, then multiply by the month's percentage. It takes an hour to set up and is easy to explain to trustees.
A catch for term-time services
Services that only run in term time, such as a youth club or a homework club, have long gaps in their weekly data. Excel's Forecast Sheet copes with gaps only when fewer than 30% of points are missing, and roughly a quarter of the year's weeks are holidays, so you are close to that limit before a single closure is counted. Worse, interpolating across a six-week summer break invents sessions that never happened. A cleaner approach: number the sessions in order (session 1, 2, 3 and so on through each school year) instead of using calendar dates, forecast by session number, and then map the forecast back onto next year's term dates. Picture a youth club that sees attendance climb through the autumn term and drop in the first weeks after each holiday; forecasting by session keeps that pattern intact, where forecasting by date blurs it.
A worked example: a food bank's winter forecast
Take a hypothetical food bank with two years of weekly parcel counts. Averages sit around 140 parcels a week, with winter peaks near 190 and a smaller rise in the long summer holiday, when families lose school meals. The coordinator runs Forecast Sheet in September to plan December to February. A simplified extract of the output (illustrative):
| Week | Forecast | Low | High |
|---|---|---|---|
| First week of December | 172 | 146 | 198 |
| Week before the holiday | 201 | 172 | 230 |
| First full week of January | 196 | 165 | 227 |
| Mid-February | 168 | 139 | 197 |
The coordinator then turns numbers into decisions:
- Volunteers: one packing volunteer handles about 25 parcels a session. Planning to the high end of 230 means nine or ten packers for the pre-holiday week, against six in a normal week, so recruitment for those shifts starts in October.
- Stock: each parcel uses about eight tins. Six peak weeks at up to 230 parcels need roughly 11,000 tins, so the harvest appeal targets tins specifically, and the shortfall goes to a supermarket partner in November.
- Triggers: if any December week goes above 198 (the top of that week's range), the coordinator phones referral partners that week to find out why, rather than waiting for the monthly review.
The forecast then feeds a volunteer request. A draft from the assistant, lightly edited (illustrative):
We're expecting our busiest weeks from 1 December to mid-January:
up to 230 parcels a week, against about 140 now. We need 4 extra
packers on Tuesday and Thursday mornings (9.30-12) for those weeks.
If you can do even one session, reply with your dates and we'll
build the rota by 15 November.A request tied to a number gets a better response than "we'll be busy over winter", because volunteers can see the gap they're filling.
A quick sum shows why planning for the high end is worth it. If each parcel's contents cost around $20 to replace at short notice, being 40 parcels short in a peak week means $800 of emergency buying or 40 households turned away. Over-preparing by the same margin means some stock carried into February, most of which keeps. The two mistakes aren't equal, and the range lets trustees see that.
The same approach works for services that aren't about stock. A debt advice line staffed by volunteer advisers shows the same logic: each adviser covers a three-hour shift and handles about nine calls. Its history shows calls climbing through January as bills from the holiday period arrive. If the January forecast is 260 to 320 calls a week, including the missed calls it logs, the rota needs roughly 29 to 36 adviser shifts a week, compared with 24 in autumn. The forecast tells the volunteer manager, in October, how many extra shifts to ask for, and which returning volunteers to contact first. Without it, the line discovers the gap in the second week of January, when missed calls are already piling up.
None of this needed the forecast to be exactly right. It needed the range to be believable enough to prepare for the top end, which is where a food bank running out of stock does most harm.
A prompt, and what to correct in the answer
Attached: weekly food parcels issued, Sept 2024 to Aug 2026.
Weeks marked CLOSED were closures, not low demand.
1) Describe the seasonal pattern in plain words.
2) Forecast weekly parcels Dec 2026 to Feb 2027 with a low-high range.
3) Explain your method in three sentences.
4) List the assumptions a trustee should know about.
Show a table, and tell me which weeks you're least confident about.An illustrative answer might say demand "is growing by about 12% a year, so January 2027 will reach 240 parcels". Before accepting it, check three things:
- Is the growth real? If one hard winter drives the whole trend, the model will project it forward. Ask it to compare each winter separately and show the numbers.
- Did it honour the closures? Look at the weeks you marked. If they appear as zeros in its working, re-run and tell it to exclude them.
- Is the range honest? A range of plus or minus five parcels from two years of data is too narrow to believe. Ask how the range was calculated.
Ask it to show every figure it used, then spot-check a few against your sheet. Chat assistants occasionally misread a column or miscount rows, and the error is easy to catch at this stage, hard to catch once numbers are in a trustee paper.
What the model can't see
A forecast built on your history assumes next year behaves like the last two. The biggest swings in charity demand often come from outside:
- A neighbouring service cutting hours or closing, which sends its users to you.
- Changes to welfare payments or eligibility rules, and the timing of payment dates.
- Energy price changes, which tend to push up winter demand for food and advice.
- A new referral partner, such as a school or health centre, starting to send people.
- Your own changes: new opening hours, a publicity push, a waiting list being cleared.
The practical fix is leading indicators: counts that move before your demand does. For an advice line, referral enquiries from partner organisations two or three weeks ahead are often a good early signal. For a food bank, the number of new households registering matters more than total parcels. Add a column for the indicator and ask the assistant whether it moves ahead of demand in your data; if it does, watch it weekly.
When you know about a change, adjust the forecast openly: "The forecast is 165 to 227 a week in the first full week of January. The food bank across town is closing Tuesdays from November, and we expect about 20 of their families to come to us, so we are planning for 185 to 250." Trustees trust an adjusted forecast with a stated reason far more than one that silently changes.
When the forecast looks wrong
Three symptoms come up often, each with a usual cause:
- A flat line with no winter peak. The tool didn't detect the seasonal cycle, often because there's only one full year of data or the peak weeks vary. In Excel's Forecast Sheet you can set seasonality by hand under Options; for weekly data with a yearly pattern, that is 52 points per cycle. With less than two years of history, treat any forecast as a rough guide.
- A band so wide it's useless, such as 60 to 320 parcels. The history is erratic, usually because of closures entered as zeros, a period when stock ran out, or a change in how you counted. Clean those weeks before blaming the method.
- A sudden step up or down in the forecast that you can't explain. Look for a change in recording, such as switching from counting parcels to counting households, or a new partner that started referring. Split the history at that point or adjust the older figures so they're comparable.
Checking the forecast before you rely on it
Test it on the past. Hide the last 12 weeks of data, build the forecast from the rest, and count how many of those 12 real weeks fell inside the forecast range. If nine or more did, the method is reasonable for planning. If fewer than nine did, look for the reason: a missed closure, a change in how you counted, or a genuine shift in demand. Then refresh the forecast monthly with the new weeks added, and keep last month's version so you can see how it moved.
Keep a simple log beside the forecast: week, forecast range, actual, and one line on anything unusual. After a winter, that log is the most useful document you have for next year's plan and for funding bids that ask how you anticipate need.
Presenting it to trustees and funders
Lead with the range and what you'll do at each end. A paragraph like this works in a trustee report:
Based on two years of weekly figures, we expect between 140 and 230 parcels a week from December to February, peaking the week before the holiday. We are recruiting four extra packing volunteers for December, have asked partners for 11,000 tins, and will review weekly if demand passes the top of the range. The forecast doesn't account for changes in other local services; we are in contact with partners to hear of any early.
The same forecast, with its log, strengthens funding applications and impact reports, covered in turning charity data into impact reports. For the mechanics in a business setting, forecasting next quarter from your history goes deeper, and why AI stock forecasts go wrong lists traps that apply equally to donated stock. If forecasting is new to your trustees, the plain-English guide to forecasting explains the ideas without jargon. For more on analysing spreadsheets with Microsoft's assistant, see data analysis with Copilot, and for tools that cost nothing, how small charities can use AI with almost no budget.
Further reads
- How to Build a Restaurant Staff Rota With AI Demand Forecasts — Turning a demand forecast into a rota; the method fits volunteers.
- How to Forecast Covers and Cut Food Waste With AI — Forecasting food volumes to cut waste, from another sector.
- AI Inventory Forecasting for Small Businesses: How It Works — How stock forecasting works when donations are your stock.
- How to Clean Up Customer Records Before You Add AI — Tidy your service records before any forecast.
- How to Build a 13-Week Cash Flow Forecast With AI Help — Pair a demand forecast with a short cash forecast.
- Using AI to Spot Donors Who Are About to Lapse — The supply side: spotting donors likely to stop giving.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: Microsoft Support, Create a forecast in Excel for Windows (Forecast Sheet, FORECAST.ETS, confidence interval and missing-data handling); Google Sheets function documentation for FORECAST and TREND.