This is what happens in the video
When you export a forecast to Excel in Business Central, you can split the forecast lines by location, item variant, salesperson, customer, and global dimensions. This lets you send each salesperson or customer their own forecast lines so they can fill in their numbers.
Each split adds more lines and columns to the Excel sheet. If you split by two customers and four salespersons, you get eight lines per item. The more split options you check, the slower the export gets, because Business Central calculates a lot of columns. With thousands of customers and many salespersons, the sheet becomes very large.
Each split also adds a matching column in the Excel sheet. Because the columns line up with your split selections, you can import the file back using the same scenario you exported with.
Splitting forecast lines when exporting to Excel
When you export a forecast to Excel, you can split the lines on several dimensions. You can split per location, per item variant, per salesperson, per customer, and so on.
Here is a concrete example. You export three periods to Excel and select item numbers 1000 and 1010. You apply a filter on the location and choose to split per salesperson. Because you have not set a salesperson filter, the export splits across all salespersons for those two items. You could also filter on a specific salesperson if you only want a subset.
You can combine splits as well. If you add a customer split and filter on customer 10000 and 20000, the export now splits by both customer and salesperson for the two items.
Why the split is useful
The point of splitting the lines is to hand each salesperson or customer their own set of forecast lines. They get a sheet that already contains the relevant combinations, and they can fill in their forecast directly.
Performance when adding multiple split options
Be aware that performance drops the more split options you select. Each split makes Business Central calculate additional columns.
In the example with two customers and four salespersons, the export creates eight lines per item, even though only two items are selected. With two items that is manageable. But if you had a thousand customers and fifteen salespersons, the sheet would be much larger and the export much slower.
So think about how many splitting checkmarks you set. Each one multiplies the number of lines in the result.
Columns and importing the file back
When you split a line, the matching column is added automatically. If you split per item variant or per one of your global dimensions, the corresponding columns appear in the sheet.
Because the columns match your split selections, you can import the file back into Business Central using the same scenario you used when you exported it.
Q&A
What can you split forecast lines by when exporting to Excel?
You can split by location, item variant, salesperson, customer, and global dimensions. You can combine several splits in the same export.
Why does the export get slow when you add more split options?
Each split makes Business Central calculate more columns and creates more lines. Splitting by two customers and four salespersons gives eight lines per item, and the line count grows quickly with more customers and salespersons.
Can you import the split forecast file back into Business Central?
Yes. Each split automatically adds a matching column, so the columns line up with your selections. You can import the file using the same scenario you used to export it.
What happens if you do not set a filter on a split dimension?
The export splits across all values of that dimension. If you split per salesperson without a salesperson filter, you get lines for every salesperson.