Business

Forecast versus actual cash for a shop: a monthly comparison

Compare forecast and actual cash for a shop each month: a simple table by line, a variance formula, and the habit of fixing the biggest miss.

Editorial illustration for Forecast versus actual cash for a shop: a monthly comparison

To compare forecast and actual cash, list each cash line in a table with the forecast, the actual and the difference, and then look at the line that was off the most. Do it once a month. In the made-up example below, the forecast said the shop would gain ₹5,500 and it lost ₹17,500, and two lines explain most of the ₹23,000 gap.

What does the monthly table look like?

Use one row per line of cash, with forecast and actual side by side. For the last column use a single rule so every number reads the same way: make a negative number mean the month was worse than planned. For money in, that is actual minus forecast. For money out, that is forecast minus actual.

LineForecastActualEffect on cash
Sales receipts₹1,70,000₹1,62,000−₹8,000
Supplier payments₹1,12,000₹1,21,000−₹9,000
Wages₹12,000₹12,000₹0
Rent₹15,000₹15,000₹0
Electricity₹3,500₹4,300−₹800
Loan EMI₹10,000₹10,000₹0
Owner withdrawals₹8,000₹14,000−₹6,000
Other costs₹4,000₹3,200₹800

Forecast net cash is ₹1,70,000 minus ₹1,64,500 of payments, which is ₹5,500. Actual net cash is ₹1,62,000 minus ₹1,79,500, which is −₹17,500. The difference is −₹23,000, which is the sum of the last column: −8,000 − 9,000 − 800 − 6,000 + 800.

Which line should I fix first?

Pick the line with the largest negative number that you can influence. Here, supplier payments (−₹9,000) and sales receipts (−₹8,000) are the biggest, and owner withdrawals (−₹6,000) are next. Do not try to fix everything. Pick one line and find out why it missed.

  • Supplier payments up by ₹9,000: did prices rise, did you order early for a festival, or did you pay an old bill that you had left out of the forecast?
  • Sales receipts down by ₹8,000: was it a slow week, a holiday, a competitor, or a delayed UPI settlement that will arrive next month?
  • Withdrawals up by ₹6,000: a one-off family expense or a new habit?

Why was my forecast wrong?

Most misses come from a short list of reasons. Timing is the commonest: a payment you expected in March arrived in April, which makes one month look bad and the next look good. A forecast that is wrong by timing will correct itself, so check whether the next month's table shows the opposite number.

  1. Timing: money moved to another month. Look for the opposite miss next month.
  2. Missing item: a bill that was not on the forecast. Add it as a line for next time.
  3. Estimate too hopeful: sales or collections assumed higher than you usually get. Use the lower of the last few months.
  4. Real change: prices, wages or demand moved. Update the assumption.

How do I build this in a sheet?

Put the lines in column A, forecast in B and actual in C. In D2, for a money-in line, use =C2-B2. For a money-out line, use =B3-C3. Add a total at the bottom with =SUM(D2:D9), and a check that the total equals actual net cash minus forecast net cash. If it does not, a line is missing.

To highlight the biggest miss, put =MIN(D2:D9) in a cell and use conditional formatting to colour any D cell equal to it. This formula works in both Excel and Google Sheets.

Getting the actual figures quickly

Actual numbers come from your cash book and bank statement. If you import the statement into SpendixAI and keep your categories consistent, you can download a CSV of a report and paste the totals into column C. Cash spent from the till still needs to be added by hand, and the app can take several such expenses at once.

A short monthly routine

  • Fill in the actual column within three days of month end.
  • Mark the largest negative lines and write one sentence on why.
  • Change next month's forecast for any lasting reason.
  • Pick one line to improve and note it at the top.
SPENDIXAI JOURNAL

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

Explore your reports