This is what happens in the video
When you work with demand forecasting in Business Central, exporting your forecast to Excel is a practical way to do calculations and adjustments outside the system. The key to getting your changes back into Business Central correctly is to follow a few simple rules about which columns and rows you can touch and which you must leave alone.
The system only reads the column called New Forecast when you import the data back. Everything else you add in Excel, such as extra columns or your own formulas, is ignored during import.
Do not insert rows within the three header rows at the top, and do not insert columns before the first date column. If you break these rules, Business Central cannot match your data correctly.
If you delete an entire dimension column, such as the customer column, Business Central interprets the remaining lines as the same line and adds the numbers together.
How the exported Excel sheet is structured
When you export items to Excel, the layout follows a fixed pattern. The number of columns at the beginning depends on how many dimensions you split on. If you split on location, salesperson, and customer, for example, the export adds those three columns at the start.
After the splitting columns come the date columns, and this is where your forecast values live.
Rules for editing the Excel sheet safely
The first column is the one the system uses to figure out which line is which, so leave it alone. Don’t insert any columns there and don’t change anything in it.
You can freely insert columns in the middle of the sheet without any problems, and you can do all kinds of calculations there. The system focuses only on the column called New Forecast, so anything else you add does not affect the import.
Stick to two simple rules:
- Don’t add any rows within the three header rows at the top.
- Don’t add any columns before the first date column.
What happens when you delete a column
If you remove a complete column, such as the customer column, the system no longer sees that dimension. It then interprets lines that were previously separate as the same line and adds the numbers on top of each other. Keep this in mind if you only want to view a subset of dimensions rather than change how the data is aggregated.
Using your own formulas and worksheets
You can apply all the manual formulas you want in another worksheet. Build whatever calculations or data layout suits you, then make sure the final values you want imported end up in the New Forecast column. That is the only column Business Central reads when you bring the data back.
Q&A
Which column does Business Central read when you import a forecast from Excel?
Business Central only reads the column called New Forecast when importing. Any other columns or formulas you add in Excel are ignored during import.
What determines the number of columns at the start of the exported Excel sheet?
The number of starting columns depends on how many dimensions you split on. If you split on location, salesperson, and customer, the export adds those three columns at the beginning.
What rules must you follow when editing the forecast Excel sheet?
Don’t insert any rows within the three header rows at the top, and don’t insert any columns before the first date column. You can add columns and formulas in the middle of the sheet without any problems.
What happens if you delete a dimension column in the Excel sheet?
If you delete an entire column, such as the customer column, the system interprets the remaining lines as the same line and adds the numbers together.
Can you use your own formulas in the forecast Excel sheet?
Yes. You can apply any manual formulas you want, including in a separate worksheet. Just make sure the final values you want imported end up in the New Forecast column.