Uploading Assumptions via Excel
Basic Overview
You can download all data of an Assumption as an Excel file with one sheet per Line-Item. You can edit the data and upload the file to the same Assumption again.
In some cases you want to upload the data to new or other Assumptions, either in the same or other workspaces. Refer to the following tables about supported use cases:
Task | Supported? |
|---|---|
Creating your own Excel file in the same structure | Yes, the system matches the Sheet and Assumption Line-Item name. |
Uploading the downloaded Excel Sheet to another Assumption with the same structure (for example a duplicated Assumption) | Yes, the system will match the Assumption Line-Items by the Sheet names |
Filling in the data of a downloaded file via formulas | Yes |
Changing Level Values to existing Level Values | Yes, if they are included in the Assumption Line-Item Filter |
Having additional Sheets in the upload file | Yes, but they must start with the number symbol (#) |
Data format
Time in rows:
Use a standard upload format with years in rows for your Assumptions.
Country | City | Year | Change |
|---|---|---|---|
USA | Boston | 2021 | 100 |
Germany | Berlin | 2021 | 200 |
USA | Boston | 2022 | 300 |
Germany | Berlin | 2022 | 600 |
USA | Boston | 2023 | 500 |
Germany | Berlin | 2023 | 1000 |
Target upload (Line-Item configuration)
If your data is already available as a Target Value, you don't need to calculate the respective delta for the Valsight import.
Download the Excel Sheet of your particular Line-Item and change the header from "Change" to "Target".
("Zielwert" in German, both are recognized independent of the language setting).
Afterwards your Target Values are imported and changes to your existing base line calculated automatically.
Missing rows from your upload which are present in the base line will result in 0 as final (target) value, i.e. your absolute change will be the negative value of your original value.
Be aware that a target upload is only supported for absolute values.
Time in columns:
AVAILABLE SINCE 4.7.0
Use an upload format with years in columns.
Tables like this are typically used in financial data tables.
Country | City | 2021 | 2022 | 2023 |
|---|---|---|---|---|
USA | Boston | 100 | 300 | 500 |
Germany | Berlin | 200 | 600 | 1000 |
Correct data upload for years in columns.
Engine | 2020 | 2021 | 2022 | 2023 |
|---|---|---|---|---|
Jet engine | 1 | 1 | 1 | 1 |
Ottomotor | 1 | 1 | 1 | 1 |
Possible error messages when uploading data
Error message: "Parameter Table must contain exactly one measure column, but contains: '[Paris, Augsburg, Berlin, Hannover]'"
Error: When this error message appears, a different level than time has been used in columns (horizontally).
Example:
Year | Paris | Berlin | Munich | Hamburg |
|---|---|---|---|---|
2020 | 12 | 12 | 12 | 12 |
2021 | 12 | 12 | 12 | 12 |
2022 | 12 | 12 | 12 | 12 |
Error message: "Parameter table must contain exactly one measure column, but contains: "[]"
Error: When this error message appears, a merged column has been used as highest column horizontally.
Example:
2020 | |||
2020-Q1 | 2020-Q2 | 2020-Q3 | 2020-Q4 |
1 | 1 | 1 | 1 |
1 | 1 | 1 | 1 |
Error message: "Column '2020.0' could not be identified. Use a level name or Measure/Change or Comment."
Error: When this error message appears, either data is uploaded into a year that is not within the project horizon or the uploaded excel contains multiple (time) levels as column headers.
Examples:
2020 | 2021 | ||||||
|---|---|---|---|---|---|---|---|
2020-Q1 | 2020-Q2 | 2020-Q3 | 2020-Q4 | 2021-Q1 | 20201-Q2 | 2021-Q3 | 2021-Q4 |
12 | 12 | 12 | 0 | 0 | 0 | 0 | 0 |
2020 | |||
2020-Q1 | 2020-Q2 | 2020-Q3 | 2020-Q4 |
1 | 1 | 1 | 1 |
1 | 1 | 1 | 1 |
Error message: "Missing data on 'City' levels 'Profit' which are required by Assumption '{2}' / New parameter data contains duplicate values for '[2020]'"
Error: When this error appears, the uploaded Excel file is missing a column header for a level.
Example:
2020 | 2021 | 2022 | 2023 | |
|---|---|---|---|---|
Paris | 12 | 12 | 12 | 12 |
Berlin | 12 | 12 | 12 | 12 |
Munich | 12 | 12 | 12 | 12 |
Hamburg | 12 | 12 | 12 | 12 |
Not supported uploads
Uploading Excel Sheets with merged cells within the data is not supported and an error message will appear.
When uploading new data in this format, always upload the "change" and not Target Values.
Tables may only contain upper case letters (e.g. 2021-Q1 instead of 2021-q1)