ROI of a weekly sales forecast screen in the ERP: when the forecast sits next to what shipped

Move the weekly forecast spreadsheet into an ERP screen that puts the forecast beside actual shipments and drafts the week's purchase orders: the money comes from three streams, the largest is forecast error — not yet measured — and the piece shows how much ROI is left without it.

Every week, at a fresh-fruit distributor, the sales manager sits down and writes a table: items down the side, days across the top, and each day split into three channels — supermarket chain A, chain B, and "everyone else". The table goes to purchasing, and purchasing orders from suppliers on the strength of it. With fresh goods it is the most expensive decision of the week: buy too much and it spoils, buy too little and the sale is lost.

That table used to be an Excel file. Now it is a screen inside the ERP. This piece works out the ROI of the move — and says plainly which parts have been measured and which have not.

1. Where the forecast file costs money

Not because Excel is bad. The file does exactly what it is asked to: one person writes numbers, another reads them. The cost sits in the work around it.

1. Writing, merging, sending. A chain's orders arrive separately and have to be copied into that chain's column. Last week's file has to be copied to make this week's frame. The file travels by email or chat, and whichever copy purchasing holds is the one that counts.
2. Nobody compares the forecast with what sold. Actual shipments live in the ERP, the forecast lives in the file. Finding out how far last week was off means joining the two by hand — so usually nobody does. An error that is never measured cannot get smaller.
3. Purchasing copies the numbers into purchase orders. Item by item, day by day, supplier by supplier — and every time the sales manager edits the file, the copying starts again.
4. The lunar calendar is not in the file. Measured at this business: shipments peak on the eve of the 1st and the 15th of the lunar month, at 1.4 to 3.7 times an ordinary day; the offering day itself runs at only 0.2 to 0.9 times. No single factor is right for every item — a forecast written as for an ordinary day is most wrong on exactly the days that sell the most.

2. What the screen is

The same table — item × day × channel — but sitting next to the real numbers.

1. A familiar grid, in kilograms, saved as you type. The sales manager types as in Excel; there is no save button to forget.
2. Past days show actual shipments. Taken straight from submitted delivery notes — the same notes the dispatch grid produces — with the forecast in small type in the corner of the cell. Every cell of last week tells you how far off it was.
3. A chain's column fills itself from the real order once that chain has sent it. No more copying by hand.
4. Lock an item or the whole week. Items that are certain can be locked first, so purchasing can order early.
5. "Create planned purchase orders". Purchasing presses it once and gets DRAFT purchase orders for the whole week. Press it again and the orders are updated in place, and an order purchasing has edited by hand is not overwritten.
6. A "Sales opportunities" panel and an "AI suggestion" button. The AI lists full moons, holidays and events in the week, and proposes a number for each cell. A suggestion is only a suggestion: whoever presses apply owns the number.
7. Newly added: a ready-formatted Excel export (week title, lunar dates, totals row, frozen columns), a "+ Item" button, and a tick box to drop an item from the table — hidden, not deleted, so its numbers survive.

The Excel file does not disappear. It becomes something the screen exports for whoever needs it, rather than the place the numbers live. In its first seven weeks the screen has held about 1,700 forecast cells for 50 items.

3. First stream: hours spent writing, merging and sending the file

How to measure it. Ask the sales manager how many sessions the file took each week, then time one week on the screen. This is the only stream already running for real: the screen is in use every week.

How far to trust it: high, but small. And as the piece on keyboard entry put it, freed hours become money only when they go into work that has a price — for a sales manager that is usually time on the phone with customers, not salary cut.

4. Second stream: hours copying numbers into purchase orders

What has to be said plainly: as of today no purchase order has been created through the screen. The button exists; purchasing has not used it. So "share of buying done through the screen" is an input in the calculator, not a measurement — set it to zero to see what is already certain.

5. Third stream: less spoiled stock and fewer shortages

This is the largest stream, and the softest.

The two kinds of miss are not equally expensive. A kilo over-bought loses the part that spoils, less what salvage sales recover: with 50% spoiling and a salvage price of 30%, that is 35% of cost. A kilo under-bought loses the gross margin on the sale that did not happen: 25%. With fresh goods, buying too much costs more than buying too little — unless the chain penalises short deliveries, in which case add the penalty to the short side. Where the unsold surplus goes is the story of the piece on customer returns: a salvage warehouse with a ledger.

Put differently, at this scale every percentage point of forecast error is worth about 1.8 million VND a week.

Why the error might fall. Not because the screen is cleverer than a person. Because three things that used to live in three places now sit in one cell: the forecast, last week's actual shipments, and the lunar date. Whoever writes the forecast sees at once where they were off — above all on the days before the 1st and the 15th.

Why it cannot be trusted yet. 15% and 13% are assumptions. Nobody has measured the old Excel file's error, because the file itself gave no way to measure it. The chart in the calculator draws the two arms of the cost curve: the right arm is over-buying, the left arm is under-buying, the hollow dots are today's error and the filled dots the error after. The grey bands under the axis show where a forecast written as for an ordinary day lands on peak days and offering days — far beyond the average error.

6. Adding it up

With 40 million VND to build the screen and benefits ramping over the first 2 months, payback comes in about month 3.2, with a first-year benefit of about 169 million VND. A handsome number — and 85% of it is stream C, the one not yet measured.

7. What this ROI stands on

Lower each assumption in turn, softest first:

• The error does not fall at all: 2.25 million a month is left, payback in about month 18.
• The error does not fall, and purchasing never uses the order button: 1.56 million a month, payback in about month 26 — only the sales manager's hours remain.
• The error falls by just one point, and only half of buying follows the screen: 5.9 million a month, payback in about month 7.3.

The third scenario is the number to take into the meeting. And set the hours to zero as well and every stream goes to zero — as an honest model should.

The "AI suggestion" button has no line in the calculator, on purpose. If its suggestions make the error smaller, that shows up in stream C once measured; counting it separately would count the same kilo twice.

8. Measuring it in your own business

The largest stream is also, from now on, the easiest to measure — because the screen keeps both the forecast and the actual shipment for every cell.

1. Baseline from the old files. Take four to eight weekly forecast files that were sent, put them beside actual shipments for the same weeks, and compute the mean absolute error by item × week. That is the real "today", in place of 15%.
2. Measure "after" with the screen itself. After six to eight weeks, run the same calculation on the numbers in the screen.
3. Compare the same position in the lunar calendar. A week with a full moon against a week with a full moon, a week with a 1st against a week with a 1st — otherwise one big festival week gets counted as the result of the change.
4. Count purchase orders created through the screen each week. While that number is zero, stream B and most of stream C remain a promise.

9. The thing to remember

A forecast that is never compared with what happened is not a forecast; it is a purchase request. The old Excel file was not wrong — it was simply never marked. Putting the forecast next to the actual shipment in the same cell does not make anybody guess better that day; it lets every week teach the next one.

Do not ask how accurate the forecast screen is. Ask: how far off were we last week — and did we know before we ordered for this one?

The peak-day and offering-day ratios were measured on a real distributor's system and anonymised. Volumes, costs, errors and every assumption in the calculator are illustrative — replace them with your own.