FERC Roll Forward Chain
With every Workiva FERC Solution, Workiva’s customers are given a chain to help automate the roll forward process. The primary function of the roll forward chain is to take the data from the current year in each report and move that data to the section that holds any historical data that is needed.
This process is automated using a control sheet that is located in your reporting spreadsheet. After your spreadsheet has been copied and is ready to use for the new year, you will want to check that your control sheet is accurate. Below, I have outlined each of the columns in the control sheet and how they should be populated:
- SheetName - the name of the sheet that will be updated during the roll forward process. If any sheet names are updated, you will want to make sure this column is populated with the exact name of the sheet, paying special attention to spaces and capitalization.
- Grab Cell Start - this column is populated with the first cell that needs to be copied over to the historical area for the sheet in that row. For instance - the Balance Sheet report will grab data starting in column H, row 3. It’s recommended to start with the header of the column so that when the data is moved over to the historical section, you can reference where it is from.
- Grab Cell End - this column is populated with the last cell in the report that contains data. This requires you to find the bottom cell from the prior year column that you will need. The chain will pay attention to the start cell and end cell together, so in this case, the chain will grab column H rows 3 through 15 and column I rows 3 through 15 for the Balance Sheet to be copied over to the historical section.
- Historical Grab Start - similar to grab cell start, this column will be populated with the cell in the report where the historical data starts. It will also be the cell that the new prior year data will be moved to and all data currently in the historical area will be shifted to the right. Generally, this starts and ends in the same rows as the grab cell start, but the columns will be quite a bit further into the report.
- Historical Grab End - pay special attention to the end cell. As explained above, the row will likely be the same as the grab cell end, however, the column may go quite a bit further depending on how much historical data needs to be saved in the report. In the Balance Sheet example we have been looking at, the historical grab end is U, so the chain will copy all data in columns O through U and move it over one column, to make space for the new prior year data that will be populated in column O.
If you have multiple areas of a report that need to be moved into the historical area, you can do that by adding multiple lines in the control sheet. For example, you can see below that the Income Statement is listed multiple times in our control sheet. Pay special attention to the historical grab start - this cell increases by one column each row down. This means that the historical data for each column will be pasted into the column that is listed in the row. By increasing the columns by one each line, the data will stay in the same order as it is listed in the report. If you were to have each column with the historical grab start as R7, where the historical data begins, then the order would be reversed and you may need to update your historical data manually to reorder it.
Once you have checked your control sheet, you will want to run the Roll Forward chain. To do that you will navigate to Wdata in the panel on the left, then click on chains on the left, and navigate to Chain Builder. Once you’re in chain builder, you will click on chains on the left and find the Roll Forward chain.
To execute the chain, you will click on the three dots on the right and click the execute button.
Then click run with inputs.
You will be asked to provide the spreadsheet ID and sheet ID for your primary reporting spreadsheet and control sheet. To do that - navigate to the control sheet and copy the IDs from the URL. For example: https://app.wdesk.com/a/QWNjb3VudB8yMDkwNzExMDQ0/spreadsheet/bd108952b91f4b6e8879071451175eef/-1/sheet/8002d62e65484778b5520defb29f6ee4
After "spreadsheet/" is the Spreadsheet ID and after "sheet/" is the Sheet ID. Once you have populated the IDs, click start and watch your chain perform the roll forward!
If you come across any errors when running your chain, first make sure that all of the column headers on your control sheet are accurate. The column headers need to match the example provided above, paying special attention to spaces and capitalization.
If you continue to experience problems, our support team is available to assist you!
Please sign in to leave a comment.
Comments
0 comments