Forecasts often come in a format that's different than your financial transactions. That's because forecasts aren't typically generated by a system, but created manually via Excel at a high level of detail.

This exercise guides you through step-by-step how you can map a typical forecast into Datarails.

Try it now: Mapping a forecast

1. Download the Excel file "Consulting Income Forecast 2022 (Company Two)" here. If the link opens in Google Sheets, go to File --> Download --> Microsoft Excel to download the Excel version of the file. It contains the forecast of a new (fictional) company.
2. Access your Datarails practice environment (click here for instructions).
3. Create a new folder called "Forecasts". In it, create a new filebox and upload the Excel file.
4. As we have done in the previous exercise, let's use the Data Mapper from Company One as a starting point. Navigate to the Data Mapper of Company One, and apply it to the forecasting filebox we have just created. 
5. Remember to break the connection before proceeding so future changes don't apply to both mappers (remove tick next to the forecast file under "Publish and Apply To").
6. Navigate to the Data Mapper of the forecast file and start by assigning Headers as we have covered in the previous exercise. Notice that the green columns populate correctly (if your green columns are empty, go back to the instructions for exercise one (linked here) and follow the steps in the second set of instructions).
7. We didn't include the column "2022 Total" as a header. That's because Datarails can easily add up the months to arrive at the annual total. 
8. As is, we have a separate column for every month. That's not ideal, because we have a date column in our existing database. To make sure this new forecast data has the same structure as our existing database with actuals, we need to turn the columns into rows. This process is called "dimensionalizing" the data. In other words, we are creating a new dimension for our monthly data. 
9. So, let's remove our headers again (trash icon next to Header) and only add the column "Account #" as a Header.
10. Next, we go to Advanced mappings in the lower left corner of the screen and select Dimensions.
11. Add the months as Dimensions, change the Name to "Date" and click Publish & Scan Latest Version.
12. We need to tell Datarails that this forecast is for the legal entity "Company Two". We already have a Custom Column called "Entity" so we can simply edit it to show "Company Two" as the formula.
13. Lastly, we need to tell Datarails that this is a Forecast to make sure it doesn't get consolidated with Actuals. Let's edit the existing Custom Column "Scenario" and change the formula to "Forecast", as shown in this video:
14. Click Publish & Scan Latest Version
15. To check if it works correctly, we can again consult the Dashboards. 
16. Make a copy of the widget "Income: Trend over last 12 months", edit it, and change the widget filter for Scenario to "Forecast" as shown here:
17. It didn't work! There are no numbers for the forecast scenario. It's time to troubleshoot.

Troubleshooting

1. Take a look at the columns of the existing data. You can do that by drilling down in the original widget as shown here. Note that the column with values is called "Amount".
2. In the Data Mapper, on the other hand,  the column with the forecast amounts is called "value". That's why it's not being picked up correctly by the Database.
3. To fix this, we can simply edit the value name under Dimensions in the Data Mapper and change it to "Amount".
4. Once we click Publish & Scan Latest Version, we can go back to the Dashboard and see the forecast values populate correctly. 

Next: Mapping Exchange Rates

Previous: Mapping a new data source

Updated July 21, 2023 13:26