Business

A 13-week cash forecast for a small shop on one sheet

Build a 13 week cash forecast for a small shop on one sheet: opening cash, expected receipts, known payments and closing cash, with an example.

Editorial illustration for A 13-week cash forecast for a small shop on one sheet

A 13-week cash forecast is one sheet with a row for each week of the next quarter and five columns: week, opening cash, expected receipts, known payments and closing cash. Each week's closing cash becomes the next week's opening cash, so you can see weeks ahead when the balance dips. Below is the layout, the formulas for Excel or Google Sheets, and a filled example for a made-up shop.

What goes in each column?

  • Week: 1 to 13, with the starting date of each week if you like.
  • Opening cash: for week 1, the money you actually have today in drawer, bank and wallet. After that, the previous week's closing cash.
  • Expected receipts: cash, UPI and card sales you expect to bank or hold that week, plus any udhaar you reasonably expect to collect.
  • Known payments: supplier bills, wages, rent, EMI, electricity, insurance and any other bill with a date.
  • Closing cash: opening cash plus receipts minus payments.

Thirteen weeks is a quarter. It is long enough to catch a rent week and an EMI week and a festival, and short enough that your estimates still mean something.

What does a filled example look like?

Say the shop starts with ₹20,000 of cash. Typical receipts are ₹38,000 a week, falling to ₹34,000 from week 9 as a slow patch starts. Suppliers and wages come to ₹29,000 every week. Rent of ₹15,000 falls in weeks 1, 5, 9 and 13, the EMI of ₹10,000 in weeks 2, 6 and 10, and electricity of ₹3,000 in weeks 3, 7 and 11. All of this is made up.

WeekOpening cashExpected receiptsKnown paymentsClosing cash
1₹20,000₹38,000₹44,000₹14,000
2₹14,000₹38,000₹39,000₹13,000
3₹13,000₹38,000₹32,000₹19,000
4₹19,000₹38,000₹29,000₹28,000
5₹28,000₹38,000₹44,000₹22,000
6₹22,000₹38,000₹39,000₹21,000
7₹21,000₹38,000₹32,000₹27,000
8₹27,000₹38,000₹29,000₹36,000
9₹36,000₹34,000₹44,000₹26,000
10₹26,000₹34,000₹39,000₹21,000
11₹21,000₹34,000₹32,000₹23,000
12₹23,000₹34,000₹29,000₹28,000
13₹28,000₹34,000₹44,000₹18,000

The lowest closing balance is ₹13,000 in week 2, when the EMI follows the rent. That is the week to watch, even though the quarter ends healthy.

How do I set up the formulas?

Put headers in row 1 (A1 to E1) and weeks 1 to 13 in A2 to A14. Then:

  • B2: type the cash you have today as a number.
  • E2: =B2+C2-D2
  • B3: =E2 (and copy B3 down to B14)
  • E3: =B3+C3-D3 (and copy E3 down to E14)
  • Lowest balance: =MIN(E2:E14)
  • Week of the lowest balance: =INDEX(A2:A14, MATCH(MIN(E2:E14), E2:E14, 0))

These work the same way in Excel and Google Sheets. If you want, format E2 to E14 so any value below your safety amount turns red, using conditional formatting.

Where do the receipts and payments figures come from?

For receipts, use the last eight to ten weeks of sales deposits and your cash book, not your hopes. Use the lower of the recent weeks unless you have a clear reason to expect a rise, such as a festival. For payments, list every bill with a date: supplier invoices on credit terms, wages, rent, EMI and any annual payment such as insurance that falls inside the quarter. Put each in the week it is due.

How often should I update it?

  1. Every Sunday, replace the forecast numbers of the finished week with the real ones.
  2. Delete or hide the finished week and add a new week 13 at the bottom, so you always look 13 weeks ahead.
  3. Check the lowest balance line and ask if any payment can move.
  4. Once a month, compare forecast and actual by line to improve your estimates.

When a sheet stops being enough

If the real figures come from several bank accounts, cards and UPI, collecting them by hand each Sunday gets tiresome. SpendixAI lets you upload statements as PDF, CSV or Excel, review the rows, and download a CSV from a report to paste into your forecast sheet, so the actual columns are quicker to fill.

Questions to ask when the sheet is done

  • In which week is closing cash lowest, and can I move a payment away from it?
  • Does the quarter include a festival or season where I need to order more stock before sales come?
  • Have I put in all known annual or quarterly payments?
  • Is the opening figure a real balance, not an estimate?
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports