Sales by Branch

Create a sales by branch report showing target amounts, actual amounts, and achievement rates with real-time year filtering.

What You'll Learn

  • Understand V/D/P sheet conventions
  • Set variables and connect data with SUMIFS
  • Configure year filters and charts
  • Save and verify report

~30 min

Intermediate

6 Steps

1

Prepare Your Excel File

Download the sales by branch sample file

Download the sales by branch sample file from Learning Experience Samples. This file contains branch-level sales data including target amounts, actual amounts, and achievement rates.

Step 1 - Prepare Your Excel File
2

Excel Sheet Naming

Understand the V/D/P sheet naming conventions

The V Sheet is the display sheet showing branch sales performance.

Step 2 - V Sheet

The D Sheet contains the raw data with all branch transaction data.

Step 2 - D Sheet

The P Sheet defines the parameters and filter variables.

Step 2 - P Sheet

Review the complete sheet structure overview to understand how the V/D/P sheets connect.

Tip: V = View (display), D = Data (source), P = Parameters (variables). This naming convention is standard across i-AUD reports.

Step 2 - Sheet Overview
3

Set Variables & Connect Data

Configure Name Manager and SUMIFS formulas

Use Excel's Name Manager to define variables for the year filter.

Step 3 - Name Manager

Create a derived column to separate the date into year and month components for filtering.

Step 3 - Date Derived Column

Set up SUMIFS formulas to aggregate sales data by branch, connecting the data sheet to the display sheet.

Step 3 - SUMIFS Formulas

The charts and achievement rates are auto-calculated based on the SUMIFS formulas you configured.

Step 3 - Auto-calculated Charts

Set up SUMIFS formulas for monthly performance tracking to aggregate branch sales data by month.

Step 3 - Monthly Performance SUMIFS

Verify the data connection is working correctly between the sheets.

Step 3 - Data Connection
4

Convert to Web

Drag & drop Excel file and configure docking

Open i-AUD Designer and drag & drop your prepared Excel file into the workspace.

Step 4 - Drag and Drop

Configure the Docking settings to ensure the report layout fills the workspace correctly.

Step 4 - Configure Docking
5

Configure Search Filter

Add Year label, calendar, refresh button, and chart colors

Place a Year label to identify the filter area on the report.

Step 5 - Year Label

Add a Calendar picker linked to the VS_YYYY variable for year selection.

Step 5 - Calendar VS_YYYY

Add a Refresh button so users can reload the data after selecting a new year.

Step 5 - Refresh Button

Review the Process Bot objects list to see all available components in the designer.

Step 5 - Process Bot Objects

Connect the button to the Refresh module with the MXGrid component to enable data reload.

Step 5 - Refresh Module Connection

Connect the Color module for chart styling configuration.

Step 5 - Color Module Connection

Select the chart object from the objects list to customize its appearance.

Step 5 - Chart Selection

Apply a custom color palette to differentiate between target and actual sales amounts in the chart.

Step 5 - Chart Palette Configuration

Tip: Using distinct colors for target vs. actual amounts makes it easy to spot branches that are over- or under-performing at a glance.

6

Save Your Report

Save to My Folder and verify the report

Save your completed report to My Folder.

Step 6 - Save Report

Verify the report displays correctly, the year filter works as expected, and the charts accurately visualize target vs. actual sales data.

Step 6 - Verify Report