This is what happens in the video
When you work with demand forecasts in Business Central, you can export them to Excel, adjust the numbers, and import them back. This works through the Flexible Forecast page. You add a starting date, select the number of periods, and choose which data to base the export on, such as the current forecast, last year’s forecast, or last year’s sales.
You must export and import using the same regional settings. If you export with American or English date settings and import on a computer with different settings, the dates can get mixed up and the import will fail.
You can adjust the forecast in Excel using a forecast factor. For example, multiply the current forecast by 1.25 to increase it by 25%, then import the updated numbers back into Business Central.
Exporting a demand forecast to Excel from the Flexible Forecast page
Working with demand forecasts often means you want the numbers in Excel, where it’s easier to manipulate and work with them. From the Flexible Forecast page, you can export the forecast directly to Excel.
The export is straightforward. You add a starting date and define how many periods you want to include. Then you decide which data the export should use as the basis for the new forecast. You can leave the default column or choose to base the new forecast on:
- The current forecast
- Last year’s forecast
- Last year’s sales
You can also filter on locations, items, or whatever else is relevant before you run the export. When you select OK, the system creates an Excel worksheet with your data.
Regional settings determine how dates are handled
There’s one thing you need to be aware of before you start moving data between Excel and Business Central. The dates in the worksheet reflect the regional settings of the system you’re running.
If you export with regional settings set to American or English, you need to import the file with those same settings. Mismatched settings cause problems, especially if you export the file on one computer and import it on another. When the date formats don’t match, the import can go wrong.
Adjusting the forecast in Excel before importing back
Once the data is in Excel, you can manipulate it using a forecast factor. If you based the export on the current forecast, the new forecast starts from those numbers. You can then multiply the figures by a factor to adjust them.
For example, if you want to increase the forecast by 25%, you multiply the current numbers by 1.25. The figures update accordingly, and the worksheet is then ready to import back into Business Central.
Q&A
How do I export a demand forecast to Excel in Business Central?
Use the Flexible Forecast page. Add a starting date, select the number of periods, choose which data to base the export on (current forecast, last year’s forecast, or last year’s sales), apply any filters on locations or items, and select OK. Business Central creates an Excel worksheet with your data.
Why do my forecast dates get mixed up when I import from Excel?
The dates reflect the regional settings of the system you exported from. You must import the file using the same regional settings. If you export with American or English settings and import with different settings, or move the file between computers with different settings, the dates can get mixed up and the import can fail.
How do I increase a forecast by a percentage in Excel?
Use a forecast factor in Excel. To increase the forecast by 25%, multiply the current figures by 1.25. Once you’ve adjusted the numbers, the worksheet is ready to import back into Business Central.