Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2024-09-06
Example: Create Variance Reports Using Report Templates

Example: Create Variance Reports Using Report Templates

This example illustrates how to use a report template to create a variance report that displays the difference between different versions (actuals, budget, and forecast) for an account. For a use case related to this topic, see Use Case: Build Variance Reports Using an Excel Report Template in the Use Case Library.
You want to provide these details to your manager for the QA organization for the current year:
  • Actual rent for January through March.
  • Budget rent for the month of August.
  • Forecast rent for the month of November.
  • The difference between the budget and forecast rent from the actual rent.
You create a matrix report using the Rent account, required time periods, and versions. You also attach an Excel report template that includes a variance calculation across the different versions.
  • The actuals version and the budget and forecast plan versions exist in Modeling.
  • Security:
    • Access Reports
       permission
    • Create Personal Reports
       permission
    • Create Shared Reports
       permission
  1. Select
    Reports
    from the main navigation menu.
  2. Click
    Add New
    Matrix
    .
  3. In the report columns, add segments for actuals, budget, and forecast:
    1. From the
      DESIGN ELEMENTS
       section, drag a segment to the report columns.
    2. Add 2 more segments to the report columns.
  4. Populate the first segment to display actual data from January to March of FY 2025:
    1. Expand the Versions element and then drag the 
      Actuals
       version to the first segment. 
    2. Add time elements from the quarters of the current year.
    3. Drag the 
      Jan-Mar
       monthly time elements to the first segment and then drop them below the actuals version.
  5. Populate the second segment to display budget data for August of the current year:
    1. Drag the budget plan version to the second segment.
    2. Drag the 
      Aug
       monthly time element to the second segment and then drop it below the budget plan version.
  6. Populate the third segment to display forecast data for November of the current year:
    1. Drag the forecast plan version to the third segment.
    2. Drag the 
      Nov
       monthly time element to the third segment and then drop it below the forecast plan version.
  7. Expand the Accounts, GL Accounts element and then locate and drag the
    Rent
     account to the rows.
    To locate the Rent account, expand GL Accounts, PL Expense, and then Operational Expenses.
  8. Expand Levels and then drag the
    QA
     level to each of the 3 segments in the columns. Drop it above the version elements.
  9. Save and run the report.
  10. Attach a template to the HTML report:
    1. Go to the reports overview page.From the
      Actions
      menu of the report you just created, select
      Attach Template
      .
    2. Select
      Blank Template
       and click
      OK
      .
      Refresh the page. The report output changes to Template.
    1. Run the report to download it as a template in Excel file format.
  11. Edit the report template in Excel:
    1. Open the Excel file that you downloaded. The report includes these
      Report Data
       and
      Report Info
       tabs.
    2. Open a new tab. Copy the data from the
      Report Data
       tab into the new tab.
    3. To avoid conflicts, rename the new tab to
      Variance Analysis Report
      something else besides
      Report
      . 
    4. In the new tab, add a report column named Variance. 
    5. In the Variance column, add an Excel calculation that subtracts the actual data from the budget and forecast data.
      Variance = actuals - (budget + forecast).
    6. Save the Excel file.
    7. Navigate back to the report overview page.
  12. Upload the updated report template:
    1. From the
      Actions
      menu of the HTML report, select
      Modify Template
      . 
    2. Select
      Choose File
       and browse to and select the Excel file that you updated with the variance calculation. 
    3. Click
      OK
      .
    4. Run the report with the modified Excel template attached.
      The report downloads to Excel from where you can open it and view the variance column.