With Wdata, you can connect data from various sources and slice-and-dice it as necessary for analysis and reporting in the Workiva platform. To summarize financial accounting—such as balances, income, and cash flow—for a given period, you can connect trial balance data with your entity and financial mappings to create a financial statement.
Step 1. Set up financial statement mapping in Spreadsheets
To get started, create a spreadsheet to map your chart of accounts to a rollup of your financial statement line items and disclosures. At a minimum, include a sheet with columns for:
- Account number
- Account name
- Financial statement line item, such as Property and Equipment, Cash and Cash Equivalents, and Deferred Revenue.
- Financial statement disclosure, to provide more detail about a line item; for example, a Property and Equipment line item may include disclosures for Land, Building, Furniture, Leases, and so on.
- Financial statement type, such as Income statement or Balance sheet.
- Reporting sign—either positive or negative—for income or expense, respectively.
Step 2. Export trial balance and entity data from your sources
In addition to the financial statement mapping, you'll need:
- A trial balance of general ledger accounts, including values and the fiscal year and period
- An entity mapping with the IDs, names, and groupings of your financial accounts
Before you can get this data into Wdata, get it out of your systems of record. One of the easiest ways is to simply export this data as comma-separated values (CSV) files.
Tip: Rather than export from your system of record, you can also build chains to automatically pull data directly from solutions into a table.
Step 3. Add the data to tables
To get the data into Wdata, create a table based on each CSV file. To create a table, click Create, and select Table, upload the CSV, and set up its columns' names, IDs, and data types.
- For the trial balance, select a Table Type of Fact, since its data will continue to grow based on future activity.
- For the entity mapping, select a Table Type of Dimension, since it contains relational data.
To create a table based on the financial statement mapping created in Spreadsheets:
- From Wdata Home, click Create, Table.
- To help identify the table, enter a unique name and description.
- In Table Type, select Dimension.
- In Folder, select where to save the table in Home.
- For each column in the sheet, click Add Column, and set up its name, description, ID, and type. For the column with the account number, select Key to indicate it contains unique values.
- Click Create Table.
- From the Datasets panel, click Add Dataset, and select Spreadsheet.
- Search for the financial statement mapping spreadsheet, and add it as a dataset. When you add the dataset, select Connect to sheet to easily update the table based on changes to the sheet.
Step 4. Create a query based on the data
To slice-and-dice the data and analyze the finances across your entity mappings, create a query using the tables as sources:
- On the Fields tab, choose which columns from the tables to include in the query results for a financial statement:
- From the trial balance, include fiscal year, fiscal period, and value.
- From the financial statement mapping, include financial statement line item, disclosure, and type.
- To combine the data from the tables, on the Relationships tab:
- Move the Account ID column from the trial balance data and financial statement mapping, and join the tables to include all data from the trial balance and only the matching account IDs from the mapping.
- Move the Entity ID column from the trial balance data and entity mapping, and join the tables to include all data from the trial balance and only the matching entity IDs from the mapping.
- On the Sort tab, select which fields to sort the results by, in ascending or descending order.
Step 5. Create parameter values
To focus on different entities or time periods the query runs, you'll use filters based on parameter values. From the Parameters panel, add parameters for the entity name, current year, and current period to the query:
- If your workspace owner set up a global parameter for one of these values, click Add, Select Global Parameter, and select the parameter to add to the query.
- Otherwise, to add a new parameter value to the query, click Add, Create New Parameter, and set up the parameter. To enable calculations, add the parameter for the current year or period with a type of integer.
Step 6. Set up the query filters
After you add the parameters to the query, on the Filters tab, add filters for entity, current year, prior year, and current period. To add a filter, move its column from Sources to the Filters tab, select its operator, and then enter or select its parameter value from the Filter Properties tab.
Filter | Column | Operator | Properties |
---|---|---|---|
Prior Year | Fiscal year from the trial balance | = | Enable Calculated, and then, under Calculation, select the current year parameter, and enter - 1 ; in other words, the prior year equals the current year, minus 1. |
Current Year | Fiscal year from the trial balance | = | In Value, select the current year parameter. |
Current Period | Fiscal period from the trial balance | <= | In Value, select the current period parameter. |
Entity | Entity name from the entity mapping | Equal To | In Value, select the entity name parameter. |
After you add the filters, set up their order of operations in the formula bar. In the formula, each filter is numbered based on its order on the Filters tab; for example, in the order listed here, the prior year filter appears as {1}
, the current year filter as {2}
, and so on. To have the financial statement include activity from either the prior year or the current year and period, for a specific entity, enter:
{1} OR ({2} AND {3}) AND {4}
Step 7. Connect the query to a spreadsheet
To propagate the data throughout the Workiva platform, in Spreadsheets, open the sheet to connect to the query, and connect the query from the Wdata Connections right panel. For example, you can connect the query to a reporting workbook used to create financial statements and other outputs. After you connect the query to a sheet, you can also link its data to documents and presentations.
To update the sheet with the latest query results:
- From the Wdata Connections panel, select the sheet's connection under From Wdata.
- Update the connection's parameters with the entity, fiscal year, and period to report on.
- Click Apply & Refresh.