This report provides a clear and intuitive view of sales performance and target achievement across all branches.
It visualizes each branch’s target amount, actual amount, and achievement rate, while the monthly performance table helps you analyze sales trends and quickly identify each branch’s contribution to total revenue..
The report supports real-time collaboration, allowing multiple managers to access, share, and analyze data simultaneously.
Use the provided sample Excel file to convert the Sales by Branch report to the web and explore your data interactively.
Download the sample Excel file from the Learning Experience Samples.
Create the Sales by Branch report by utilizing the data in the downloaded Excel file.
Let’ start by understanding the role of each sheet in this report.
Sheet V : V_SalesByBranch
When multiple sheets exist in Excel, the sheet to be displayed on the web must follow the format ‘V_(letters)’ or ‘V(number)’.
Sheet P : P1
Create variables to be used when querying data. You can define cell values as variables by using Excel’s ‘Name Manager’ feature.
In this exercise, we’ll use ‘Year’ as a variable to view the Sales by Branch for the selected year.
The naming convention for Excel sheet is as follows:
|
Set the ‘Year’ variable in the P Sheet using Excel’s ‘Name Manager’ feature.
The variable can be used as a filter condition on the web screen. Once the condition is set, only data matching the variable’s value will be retrieved.
To aggregate data in the V sheet, create a derived column that separates the year and month using the Date column.
Use Excel’s SUMIFS function to calculate the ‘Target Amount’ and ‘Actual Amount’ corresponding to the selected year for each item.
* Year : VS_YYYY
* Branch : NYC, LA, WA …
* Value : Target Amount, Actual Amount
Example) Cell C13 : =SUMIFS('D1'!$C:$C, 'D1'!$E:$E, VS_YYYY, 'D1'!$B:$B, 'V_Sales Performance Report'!$B$13)
When you enter 'Target Amount' and 'Actual Amount', the related charts, Variance, and achievement rate are displayed automatically.
The table at the bottom showing monthly performance by branch also calculates each branch’s monthly values using Excel’s SUMIFS function.
* Year : VS_YYYY
* Month : Jan, Feb ...
* Branch : NYC, LA, WA …
* Value : Actual Amount
Once you have finished entering the data, the Sales by Branch report is complete. Save the completed Excel file.
From the EPA main screen, go to [Menu] > [Tool] and launch i-AUD Designer.
Drag and drop the saved Excel file into the i-AUD Designer window.
The Excel report now appears in the web view, preserving the original layout.
Ensure the report automatically resizes to fit the web browser window.
In the Properties pane on the right, check Docking: Left, Right, Bottom.
To remove the border from the report on the designer screen, set the Line Type property under Border to ‘None’.
In the Activities list, double-click ‘Refresh’ module.
In the Process Bot window, select the 'Button' placed on the window and drag it to the 'Refresh' action link them, so that clicking the 'Button' triggers the Refresh action.
In the Refresh module details, select the MXGrid component to refresh, and drag a connection line from the Button to Refresh Module.
(Note: Dragging and dropping the Excel file onto the web screen automatically creates the MXGrid component.)
This time, connect the color module so that the chart below is displayed in the desired colors.
In the Objects list, double-click the chart to which you want to apply colors.
When the ‘chart’ is connected to data, select ‘OnDataBindEnd’ on the chart placed in the Process Bot window, then drag and connect it to the ‘[Chart] Change Chart Palette’ module, and enter the chart and color values to apply.
* Color values : #195BAF, #2797FA, #66D8BD, #E2E6EA, #F98A45
