Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2024-09-06
Example: Build a Variance Report Using a Report Template

Example: Build a Variance Report Using a Report Template

This example illustrates how to build a report to calculate the variance between actuals and budget and forecast versions using an Excel report template. 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 create a variance report for FY 2025 for your QA organization to display:
  • Actual rent for January through March.
  • Budget rent for the month of August.
  • Forecast rent for the month of November.
  • A calculation that subtracts the budget and forecast rent from the actual rent.
  • Security:
    • Access Reports
       permission
    • Create Personal Reports
       permission
    • Create Shared Reports
       permission
  1. Verify that the actuals version and the budget and forecast plan version exist in Modeling.
  2. Select
    Reports
    from the main navigation menu. Then, click
    Add New
    Matrix
    .
  3. In the report columns, add 3 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. Then, drag the actuals version to the first segment. 
    2. Expand the FY2025 time element. Then expand the Q1, Q2, Q3, and Q4 time elements.
    3. Drag the Jan-Mar 2025 monthly time elements to the first segment and drop them below the actuals version.
  5. Populate the second segment to display budget data for August of FY 2025:
    1. Drag the budget plan version to the second segment.
    2. Drag the Aug monthly time element to the second segment and drop it below the budget plan version.
  6. Populate the third segment to display forecast data for November of FY 2025:
    1. Drag the forecast plan version to the third segment.
    2. Drag the Nov monthly time element to the third segment and drop it below the forecast plan version.
  7. Expand
    GL Accounts
    PL Expense
    Operational Expenses
     and drag the
    Rent
     account to the rows.
  8. Expand
    Levels
     and 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
      .
      After you 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 2 tabs:
      Report Data
       and
      Report Info
      .
    2. Open a new tab. Then, copy the data from the
      Report Data
       tab into this new tab. To avoid conflicts, name this new tab something else besides Report. Example: Variance Analysis Report.
    3. In the new tab, add a report column and name it Variance. In the variance column, add an Excel calculation that subtracts the actual data from the budget and forecast data. Example: Variance= actuals - (budget + forecast).
    4. Save the Excel file and 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
      . Then select
      Choose File
      and browse and select the Excel file that you updated with the variance calculation. Click
      OK
      .
    2. 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.