HomeDISE ReportingReadymade Analytic ModulesTally Business IntelligenceNavision Business IntelligenceData Capture ServicesData CleaningDashboard and Report Design-Data MiningCustomersManagement TeamContact Us

1      Reports

1.1    Layout and Titles

 

 Sales Analysis Report: as of now we have assumed that this shall be a single report with multiple tabs for weekly channel, weekly web, monthly channel and monthly web sales. In case the refresh cycles for the weekly and the monthly sales are different then we may have to consider them as separate reports.  

Report (Tab)  #

Page Layout

Report (Tab) Name

Description

Other Considerations

Landscape

Portrait

Letter Size

Legal Size

 

 

x

 

 

Monthly customer sales analysis(sales officer view)

This particular report gives the profitability analysis for each customer based on channel sales. Channel sales shall include all the class codes including the web sales. The analysis shall be carried out in terms of each sales area. The purpose of this report is to get a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis. In this report we shall not be reporting for the corporate entities.

 

 

 

x

 

 

Monthly web  sales analysis(sales officer view)

This particular report gives the profitability analysis for each customer based on web sales. The analysis shall be carried out in terms of each sales area. The purpose of this report is to get a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis. In this report we shall not be reporting for the corporate entities.

 

 

 

x

 

 

Monthly customer sales analysis(Regional Manager’s view)

This particular report gives the profitability analysis for each customer based on channel sales. Channel sales shall include all the class codes excluding web sales. The analysis shall be carried out in terms of each sales area. The purpose of this report is to get a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis. In this report we shall not be reporting for the corporate entities.

 

 

 

x

 

 

Monthly web sales analysis(Regional Manager’s View)

This particular report gives the profitability analysis for each customer based on web sales. The analysis shall be carried out in terms of each sales area. The purpose of this report is to get a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis. In this report we shall not be reporting for the corporate entities.

 

 

Daily Sales Report

Report (Tab)  #

Page Layout

Report (Tab) Name

Description

Other Considerations

Landscape

Portrait

Letter Size

Legal Size

1

 

x

 

 

Regional

This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of regions. Comparisons shall be made from the present year with the figures of last year.

 

2

 

x

 

 

Shipping demand branch

This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of shipping demand branch. Comparisons shall be made from the present year with the figures of last year.

 

3

 

x

 

 

Original demand branch

This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of original demand branch. Comparisons shall be made from the present year with the figures of last year.

 

4

 

x

 

 

Customer type

 

 

This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of customer type. Comparisons shall be made from the present year with the figures of last year.

 

5

 

x

 

 

Sales Area

This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of sales area. Comparisons shall be made from the present year with the figures of last year.

 

6

 

x

 

 

Order method

This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of order method. Comparisons shall be made from the present year with the figures of last year.

 

 

 


1.2    Report Fields

 

Throughout this document we shall use Sales and cost under various denominations. For the purpose of this report sales and cost have been defined in the following manner:

  • Sales is defined as value of sales order+ value of returned merchandise +value of returned defective orders+ value of goodwill orders.
  • Reporting cost can be obtained from the line item entry.
  • Actual Cost is the cost from the inventory
  • Gross Profit(Actual)=sales-actual cost
  • Net profit(Actual)=Gross Profit(Actual)-freight cost
  • Gross Profit(Reporting)=sales-reporting cost
  • Net Profit(Reporting)=sales-reporting cost-freight cost
  • Freight as % age of sales=Freight cost/Sales
  • Return Rate= value of returned merchandise +value of returned defective orders+ value of goodwill orders
  • Return(%age sales)= Return Rate/Sales
  • ‘Percent’ is Profit/Sales.
  • Previous fiscal month is defined as current fiscal month-1
  • Order : this refers to the order line entries.
  • ‘Profit’[for the respective case scenario] is Sales-Cost
  • ‘Percent’ is Profit/Sales.

 

 

                                                                                                        

Report Name: Sales Analysis report

 

Report #:                      1                                   

 

Field Name:                  Customer number

 

Business Description:    the number of the customer

 

Field Name:                  Name

 

Business Description:    Customer name

 

 

 

Field Name:                  Sales (previous Month)

 

Business Description:    It is the total sales for the previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month.

 

 

 

Field Name:                  Net Sales 12 month average

 

Business Description:    it gives the average of the total sales over last 12 fiscal months. The 12 fiscal month period is calculated as the number of business days between the first fiscal day of the current fiscal month-13 and the last fiscal day of the previous fiscal month. In case we reach the first fiscal month of the current fiscal year during the calculation then we shall continue by doing a backward calculation from the last fiscal month of the previous fiscal year.

 

 

 

Field Name:                  Net Sales 3 month average

 

Business Description:    it gives the average of the total sales over last 3 fiscal months. The 3 fiscal month period is calculated as the number of business days between the first fiscal day of the current fiscal month-4 and the last fiscal day of the previous fiscal month. In case we reach the first fiscal month of the fiscal year during the calculation then we shall continue by doing a backward calculation from the last fiscal month of the previous fiscal year

 

 

Field Name:                  Gross Profit 3 month average[reporting]

 

Business Description:    it gives the average of the total gross profit(reporting) over last 3 fiscal months. The 3 fiscal month period is calculated as the number of business days between the first fiscal day of the current fiscal month-4 and the last fiscal day of the previous fiscal month. In case we reach the first fiscal month of the fiscal year during the calculation then we shall continue by doing a backward calculation from the last fiscal month of the previous fiscal year. Gross profit (reporting) is defined as sales-reporting cost.

 

 

Field Name:                  Gross Profit 12 month average (reporting)

 

Business Description:    it gives the average of the total profits(reporting) over last 12 fiscal months. The 12 fiscal month period is calculated as the number of business days between the first fiscal day of the current fiscal month-13 and the last fiscal day of the previous fiscal month. In case we reach the first fiscal month of the fiscal year during the calculation then we shall continue by doing a backward calculation from the last fiscal month of the previous fiscal year. Gross profit (reporting) is defined as sales-reporting cost.

 

 

Field Name:                  Gross Profit previous month (reporting)

 

Business Description:    It gives the gross profit (reporting) for the previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. Gross profit (reporting) is defined as sales-reporting cost.

 

Field Name:                  Net Profit previous month (reporting)

 

Business Description:    It gives the net profit (reporting) for the previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. Net profit (reporting) is defined as sales-reporting cost-freight cost.

 

Field Name:                  %age freight sale previous month

 

Business Description:    It gives the % ratio of the freight costs with regards to the total sales. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month.

 

 

 

Field Name:                  Return Rate previous month

 

Business Description: It gives the return rate for previous month. Return rate is defined as value of returned merchandise +value of returned defective      orders+ value of goodwill orders. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month.

 

Field Name:                  Customers Lost-previous month

 

Business Description:    It gives the number of customers lost between the first fiscal day and the last fiscal day of the previous month.  Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. A lost customer shall have 0 sales for the previous fiscal month and non-zero and greater than 0 sales for the current fiscal month-2.

 

 

Field Name:                  Customers gained-previous month

 

Business Description:    It gives the number of customers lost within the first fiscal day and the last fiscal day of the previous month.  Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. A gained customer shall have 0 sales for the current fiscal month-2 and greater than 0  sales for the previous fiscal month.

 

Field Name:                  Growth Rate by customer segment

 

Business Description:    This gives the sales(sales order+ value of returned merchandise +value of returned defective orders+ value of goodwill orders) per number of active customers for the previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. Active customers are defined as those customers with sales greater than 0. This measure’s slice and dice parameters include month, customer segment and sales area.

 

Field Name:                  Average order size

 

Business Description:    This gives the $ volume per  order for the previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. The $ value of the order is given by the sale volume. The orders selected should be such that the order line items should have 0 return rate (value of returned merchandise +value of returned defective orders+ value of goodwill orders).

 

 

 

 

 

Break/Sort:                   Salesman Number and Sales Man Name(SB)

 

 

Totaling Options:           Not specified in the word document as of now

 

 

Field Name:                  Region

 

Business Description:    Region name

 

 

Field Name:                  Sales-this date

 

Business Description:    Sales as of a given date [sale as of date the report has been refreshed]

 

 

Field Name:                  Cost-this date

 

Business Description:    Actual Cost as of a given date [Actual cost as of date the report has been refreshed]

 

Field Name:                  Profit-this date

 

Business Description:    Actual Profit as of a given date[profit as of date the report has been refreshed. Profit shall be defined as Sales-Actual Cost]

 

 

Field Name:                  Percent-this date

 

Business Description:    Percentage Actual profit as of a given date

 

 

 

Field Name:                  Sales-this date (last year)

 

Business Description:    Sales as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]

 

Field Name:                  Cost-this date (last year)

 

Business Description:    Actual Cost as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]

 

 

Field Name:                  Profit-this date (last year)

 

Business Description:    Actual Profit as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]

 

 

Field Name:                  Percent-this date (last year)

 

Business Description:    Percentage actual profit as same day last year[same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]

 

 

 

Field Name:                  Sales-cumulative this month

 

Business Description:    Cumulative sales as of this month [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal month]

 

Field Name:                  Sales (%age)-overall sales (this month)

 

Business Description:    Percentage Cumulative sales as of this month per region [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal month]

 

 

 

 

Field Name:                  Cost-cumulative this month

 

Business Description:    Cumulative actual cost as of this month [cumulative cost for the number of business days between the date the report is refreshed

and the first day of the current fiscal month]

 

 

Field Name:                  Profit-cumulative this month

 

Business Description:    Cumulative actual profit as of this month [cumulative profit for the number of business days between the date the report is refreshed

and the first day of the current fiscal month]

 

 

 

 

Field Name:                  Percent-cumulative this month

 

Business Description:    Percentage actual profit as of this month [percentage profit for the number of business days between the date the report is refreshed

and the first day of the current fiscal month]

 

 

Field Name:                  Sales-cumulative this month (last year)

 

Business Description:    Cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Sales (%age)-overall sales(this month last year)

 

Business Description:    Percentage (Region/customer type/shipping demand/original demand) cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

Field Name:                  Cost- cumulative this month (last year)

 

Business Description:    Cumulative Actual cost as of this month last year [cumulative cost for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

Field Name:                  Profit- cumulative this month (last year)

 

Business Description:    Cumulative actual profit as of this month last year [cumulative profit for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

Field Name:                  Percent- cumulative this month (last year)

 

Business Description:    Cumulative percent actual profit as of this month last year [cumulative percent profit for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

 

Field Name:                  Sales-cumulative this year

 

Business Description:    Cumulative sales as of this year [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal year]

 

 

Field Name:                   Daily Average (this year)-Yearly

 

Business Description:    Cumulative sales as of this year [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal year] per number of business days beginning from the first business day of the current fiscal year to the date of report refresh.

 

 

 

Field Name:                  Sales (%age)-overall sales (this year)

Business Description:  Percentage (Region/customer type/shipping demand/original demand) cumulative sales as of this year [cumulative percentage sales for the number of business days between the date the report is refreshed and the first day if the current fiscal year]

 

 

 

Field Name:                  Cost-cumulative this year

 

Business Description:    Cumulative actual cost as of this year [cumulative cost for the number of business days between the date the report is refreshed and the first day of the current fiscal year]

 

 

Field Name:                  Profit-cumulative this year

 

Business Description:    Cumulative actual profit as of this year [cumulative profit for the number of business days between the date the report is refreshed and the first day of the current fiscal year]

 

 

Field Name:                  Percent-cumulative this year

 

Business Description:    Percentage Actual profit as of this year [percentage profit for the number of business days between the date the report is refreshed and the first day of the current fiscal year]

 

Field Name:                  Daily Average this year (monthly)

 

Business Description:    Cumulative sales as of this month [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal month] per total number of business days from the first fiscal day of the current fiscal month in the current fiscal year to the date of refresh.

 

 

Field Name:                  Sales-cumulative last year

 

Business Description:    Cumulative sales as of last year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

 

Field Name:                  Sales (%age)-overall sales (last year)

 

Business Description:    Percentage (Region/customer type/shipping demand/original demand) as of last year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Cost- cumulative last year

 

Business Description:    Cumulative Actual cost as of last year [cumulative cost for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

Field Name:                  Profit- cumulative last year

 

Business Description:    Cumulative Actual profit as of last year [cumulative profit for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

Field Name:                  Percent- cumulative last year

 

Business Description:    Cumulative percent Actual profit as of last year [cumulative percent profit of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day (the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                   Daily Average (last year)-Yearly

 

Business Description:    Cumulative sales as of last year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year] per the number of business days beginning from the first fiscal day of the previous fiscal year to the date of report refresh last year [the same business day for the same fiscal month and week for previous fiscal year].

 

 

 

 

Field Name:                  Daily Average last year (monthly)

 

Business Description:    Cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year] per

                                       the number of business days beginning from the first fiscal day for the same fiscal month in the previous fiscal year to the same fiscal day as the date of report refresh last year[The same fiscal day as the date of report refresh last year is the same fiscal day as the date of report refresh in the same fiscal week and month of the previous fiscal year] .

 

Field Name:                  Days-month

 

Business Description:    The number of business days beginning from the first business day of the month to the date of report refresh in the current fiscal month of the current fiscal year.

 

Field Name:                  Days-month (last year)

 

Business Description:    The number of business days beginning from the first fiscal day for the same fiscal month in the previous fiscal year to the same fiscal day as the date of report refresh last year[The same fiscal day as the date of report refresh last year is the same fiscal day as the date of report refresh in the same fiscal week and month of the previous fiscal year] .

 

Field Name:                  Days-year

 

Business Description:    The number of business days beginning from the first business day of the current fiscal year to the date of report refresh.

 

 

Field Name:                  Days-year (last)

 

Business Description:    The number of business days beginning from the first fiscal day of the previous fiscal year to the date of report refresh last year [the same business day for the same fiscal month and week for previous fiscal year].

 

 

Field Name:                  Change Sales daily

 

Business Description:    The difference between Sales as of a given date [sale as of date the report has been refreshed] and Sales as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous  fiscal year]

 

Field Name:                  Change Sales daily-%age

 

Business Description:    The %age change in daily sales(The difference between Sales as of a given date [sale as of date the report has been refreshed] and Sales as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]) with respect to Sales as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year])

 

 

Field Name:                  Change Profit Daily

 

Business Description:    The difference between Actual Profit as of a given date[given date is the date of report refresh] and  Profit as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]

 

Field Name:                  Change Profit Daily-%age

 

Business Description:    The %age change in daily Actual profit (The difference between Actual Profit as of a given date[given date is the date of report refresh] and  Profit as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year] with respect to Profit as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year])

 

 

Field Name:                  Change Sales monthly

 

Business Description:    The difference between cumulative sales as of this month [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal month] and cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Change Sales monthly-%age

 

Business Description:    The %age change in monthly sales(The difference between cumulative sales as of this month [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal month] and cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]) with respect to cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year

 

Field Name:                  Change Profit Monthly

 

Business Description:    The difference between Cumulative Actual profit as of this month [cumulative profit for the number of business days between the date the report is refreshed and the first day of the current fiscal month] and cumulative actual profit as of this month last year [cumulative profit for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Change Profit Monthly-%age

 

Business Description:    the % age change in monthly Actual Profit(The difference between Cumulative profit as of this month [cumulative profit for the number of business days between the date the report is refreshed and the first day of the current fiscal month] and cumulative profit as of this month last year [cumulative profit for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]) with respect to cumulative profit as of this month last year [cumulative profit for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Change Sales Yearly

 

Business Description:    The difference between cumulative sales as of this year [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal year] and cumulative sales as last year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Change Sales Yearly-%age

 

Business Description:    The %age change in yearly sales(The difference between cumulative sales as of this year [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal year] and cumulative sales as last year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]) with respect to cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year

 

Field Name:                  Change Profit Yearly

 

Business Description:    The difference between Cumulative Actual Profit as of this year [cumulative profit for the number of business days between the date the report is refreshed and the first day of the current fiscal year] and cumulative Actual Profit for last year [cumulative profit for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Change Profit Yearly-%age

 

Business Description:    the % age change in yearly  Actual profits(The difference between Cumulative actual profit as of this year [cumulative actual profit for the number of business days between the date the report is refreshed and the first day of the current fiscal year] and cumulative actual profit for last year [cumulative actual profit for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]) with respect to cumulative actual profit for last year [cumulative actual profit for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Change Daily Average (Monthly)

 

Business Description:    Difference between cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year] per

                                       the number of business days beginning from the first fiscal day for the same fiscal month in the previous fiscal year to the same fiscal day as the date of report refresh last year[The same fiscal day as the date of report refresh last year is the same fiscal day as the date of report refresh in the same fiscal week and month of the previous fiscal year] and cumulative sales as of this month [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal month] per total number of business days from the first fiscal day of the current fiscal month in the current fiscal year to the date of refresh.

 

 

Field Name:                  Change Daily Average (Yearly)

 

Business Description:    Difference between cumulative sales as of last year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year] per the number of business days beginning from the first fiscal day of the previous fiscal year to the date of report refresh last year [the same business day for the same fiscal month and week for previous fiscal year] and cumulative sales as of this year [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal year] per number of business days beginning from the first business day of the current fiscal year to the date of report refresh.

 

                                                                                                                                              

Break/Sort:                   Date of the report

 

 

Totaling Options:           sum sales, cost, Profit. We need to take an average of %.

 

In the other tabs of the report all the other fields besides the first one shall remain the same. We shall be listing only the first field name for the description of the remaining tabs.

 


1.3    Prompts

 

Sales Analysis Report

 

Prompts are the filter options. For each of the tabs 1 to 4 they need to be specified by the client.

 

Tab #1: Monthly customer sales analysis (sales officer view). There shall be no prompts in this case. The sales officer shall be given a static report.

 

Tab #2: Monthly web sales analysis (sales officer view). There shall be no prompts in this case. The sales officer shall be given a static report.

 

Tab #3: Monthly customer sales analysis (Regional Manager’s view): The filter criteria are as following:

 

Prompt #:                     1

 

Tab #:                            3

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono                                                                      

 

 

Prompt #:                     2

 

Tab #:                            3

 

Prompt Message:          ‘Please select the region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

                                                                  

Tab #4: Monthly web sales analysis (Regional Manager’s View):

 

 

Prompt #:                     3

 

Tab #:                           4

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

 

Prompt #:                     4

 

Tab #:                            4

 

Prompt Message:          ‘Please select the region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

Daily Sales Report:

 

We  shall be selecting only the following  as the filtering criteria.

 

Prompt #:                     1

 

Tab #:                            1

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

Prompt #:                     2

 

Tab #:                            2

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

Prompt #:                     3

 

Tab #:                            2

 

Prompt Message:          ‘Please select the region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

Prompt #:                     4

 

Tab #:                            3

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

Prompt #:                     5

 

Tab #:                           3

 

Prompt Message:          ‘Please select the region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

Prompt #:                     6

 

Tab #:                           4

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

Prompt #:                     7

 

Tab #:                            4

 

Prompt Message:          ‘Please select the region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

 

Prompt #:                     8

 

Tab #:                           5

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

Prompt #:                     9

 

Tab #:                            5

 

Prompt Message:          ‘Please select the Region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

Prompt #:                     10

 

Tab #:                           6

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

Prompt #:                     11

 

Tab #:                           6

 

Prompt Message:          ‘Please select the Region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

                             

 

 

2      Technical Details

2.1    Data Provider Details

 

Each of the tabs should have a distinct query running for the same. There shall be 4 different queries running to get the desired report from the same universe. No query operators (such as union and intersection) shall be required between the queries. All the queries shall be run from the single universe that has been designed.

 

Please see the SQL section to get further clarification on the same.

 

It is quite likely that the team shall use aggregate tables/aggregate awareness within Business Objects  to run these reports. That is we shall be generating the required aggregates sum(sales), sum(cost) at the database level, grouped at the required level of hierarchy as specified by the client. This shall restrict additional group by, drill through and drill down options for the reports.


 

2.1.1     Result Objects

 

 

Sales Analysis Report

The following are the result objects for the ‘Sales Analysis Report’.

Sales: measure

Cost: measure

Gross Profit: measure

Net profit: measure

Month: dimension

Customer: dimension

Sales area: dimension

Sales person (shall be used as a segment): dimension

Sales channel: dimension

Item class: dimension                              

Product class: dimension

Product Line: dimension

Geographic code: dimension.

 

 

Sort details: the sorting needs to take place by the value of a measure object. Hence we cannot specify the conditions for the same in the query.

 

 

Daily Sales Report: The following are the objects as per our anticipation as of now.

Region: dimension

Shipping branch: dimension

Original demand branch: dimension [both original demand branch and shipping branch form part of the supply chain dimension. Should we add additional filtering criteria for the same?]

Sales order method (web based, channel): dimension

Fiscal calendar: dimension

Sales Area: dimension

Customer: dimension

Days: Measure

Sum of Sales: Measure

Sum of Cost: Measure object

 

 

Sort details: N/A

 

Query Name: N/A.

 

 

The client has clarified that it does not require additional grouping options such as geographic and product dimensions for this report.

 

 

 


2.1.2     Condition Objects

 

On a preliminary glance this report can be made in one of the 4 ways:

 

Method 1: run a single query with the following date range

 

Date in the range between date of refresh and the 1st fiscal day of the current fiscal month-2.

 

 

Method 2: use of the summary tables. I would recommend this course of action in case you wish to run the reports on an adhoc basis. We shall have to discuss the structure of the summary tables with Sid.

 

 

 

Method 3: use of derived tables.

 

                                   

Method 4: use of correlated sub queries

 

 

Method 5: use of aggregate awareness within the universe

 

 

The option chosen by the technical team shall depend on the following factors:

  • The data load for the queries
  • The slice and dice capabilities expected for each view by the client
  • Whether the reports shall run on an ad hoc basis or they shall run on a pre scheduled basis.

 


SQL

 

Sales analysis report

The SQL will involve inner join between dimension and Fact with the filter conditions for the dimensions.

We are presenting the approximate SQL for each of the tabs in consideration. However in case aggregates are produced then the sql shall change.

 

Tab #1:

Select

sum(Sales), sum(cost), sum(profit),

month, customer, sales area, sales person, sales channel, product groups, geographic code

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the (fiscal month-13) of the date of refresh

 and sales channel= specify sales channel

group by  month, customer, sales area, sales channel, geographic code, item class, product class, product line

 

                                                      

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • The sorting criteria specified by the client involves the sum of sales. This is a measure attribute. We shall have to specify the same in the report.

 

Tab #2:

Select

sum(Sales), sum(cost), sum(profit),

month, customer, sales area, sales person, sales channel, product groups, geographic code

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the (fiscal month-13) of the date of refresh

 and sales channel= specify web channel

group by  month, customer, sales area, sales channel, geographic code, item class, product class, product line

 

                                                      

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • The sorting criteria specified by the client involves the sum of sales. This is a measure attribute. We shall have to specify the same in the report.

 

 

Tab #3:

Select

sum(Sales), sum(cost), sum(profit), count(distinct(customers))

month, customer, sales area, sales person, sales channel, product groups, geographic code

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the (fiscal month-13) of the date of refresh

 and sales channel= specify web channel

group by  month, customer, sales area, sales channel, product groups, geographic code, item class, product class, product line

 

                                                      

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • The drill down/drill through shall be as per the dimensions specified in the group by option namely: date, customer, sales area, geographic code,  sales channels, item class, product class and product line.
  • The client expects no additional ‘group by’ options with any of the other dimensions.
  • The sorting criteria specified by the client involves the sum of sales. This is a measure attribute. We shall have to specify the same in the report.

 

 

 

Daily Sales Report

Tab #1:

 

Select region,

sum(Sales), sum(cost), date, month, year

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the previous fiscal year

group by  region, date, month, year

                                                      

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • No sorting criteria have been given by the client.

 

                                                               

Tab #2:

Select shipping demand branch, region

sum(Sales), sum(cost), date, month, year

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the previous fiscal year

 and prompt for region

group by  shipping demand branch, date, month, year,  region

 

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects to add ‘region’ as a segment in the reports. That is why we have had to group by region. However the client does not expect to drill down by the region parameter.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • No sorting criteria have been given by the client.

 

 

Tab #3:

Select original demand branch, region,

sum(Sales), sum(cost), date, month, year

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the previous fiscal year

 and prompt for region

group by  original demand branch, date, month, year, region

 

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects to add ‘region’ as a segment in the reports. That is why we have had to group by region. However the client does not expect to drill down by the region parameter.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • No sorting criteria have been given by the client.

 

 

Tab #4:

Select order method, region,

sum(Sales), sum(cost), date, month, year

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the previous fiscal year

 and prompt for region

group by  order method, date, month, year, region

 

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects to add ‘region’ as a segment in the reports. That is why we have had to group by region. However the client does not expect to drill down by the region parameter.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • No sorting criteria have been given by the client.

 

 

Tab #5:

Select customer type, region,

sum(Sales), sum(cost), date, month, year

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the previous fiscal year

 and prompt for region

group by  customer type, date, month, year, region

 

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects to add ‘region’ as a segment in the reports. That is why we have had to group by region. However the client does not expect to drill down by the region parameter.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.

 

  • No sorting criteria have been given by the client.

 

 

 

Tab #6:

Select sales area, region,

sum(Sales), sum(cost), date, month, year

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the previous fiscal year

 and prompt for region

group by  sales area, date, month, year, region

 

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects to add ‘region’ as a segment in the reports. that is why we have had to group by region. However the client does not expect to drill down by the region parameter.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • No sorting criteria have been given by the client.

 

 

/* details about the join conditions between dimensions and facts*/

 

The way in which shall design the universe shall entail an inner join between dimensions and fact. A sample of the same(from one of the author’s previous projects) is attached below:

 

 From (("ETMC"."dbo"."fact_ledger2" "fact_ledger2"

INNER JOIN "ETMC"."dbo"."dim_account" "dim_account" ON

"fact_ledger2"."acct_dim_id"="dim_account"."acct_dim_id")

INNER JOIN "ETMC"."dbo"."dim_company" "dim_company" ON

"fact_ledger2"."comp_dim_id"="dim_company"."comp_dim_id")

INNER JOIN "ETMC"."dbo"."fiscal_calender" "fiscal_calender" ON

"fact_ledger2"."CAL_id"="fiscal_calender"."cal_id"

inner join "ETMC"."dbo"."dim_dept" "dim_dept" on

"fact_ledger2"."dept_dim_id"="dim_dept"."dept_dim_id"

 

 


2.2    Document Variables

 

 

1.     %age freight sale previous month= Return Rate previous month/sales(previous month)

2.     Average order size=sum(sales)/count(clean order)

3.     Profit-this date= (sales-this date)-(cost-this date)

4.     Percent-this date=(profit-this date)/(Sales-this date)

5.     Profit-this date(last year)= [sales-this date(last year)]-[cost-this date(last year)]

6.     Percent-this date(last year)= Profit-this date(last year)/ sales-this date(last year)

7.     Percent-cumulative this month=Profit-cumulative this month/Sales-cumulative this month

8.     Percent- cumulative this month (last year)=Profit- cumulative this month (last year)/Sales-cumulative this month (last year)

9.     Daily Average (this year)-Yearly=Sales-cumulative this year/Days-year

10.  Daily Average this year (monthly)=Sales-cumulative this month/Days-month

11.  Percent- cumulative last year=(Profit- cumulative last year)/(Sales-cumulative last year)

12.  Daily Average (last year)-Yearly=Sales-cumulative last year/Days-year (last)

13.  Daily Average last year (monthly)= Sales-cumulative this month (last year)/Days-month (last year)

14.  Change Sales daily= (Sales-this date) - (Sales-this date (last year))

15.  Change Profit daily= (Profit-this date)-(Profit-this date (last year))

16.  Change Sales daily-%age= Change Sales daily/(Sales-this date (last year))

17.  Change Profit daily-%age= Change Profit daily/( Profit-this date (last year))

18.  Change Sales Yearly= (Sales-cumulative this year)-(Sales-cumulative last year)

19.  Change Profit Yearly= (Profit-cumulative this year)-(Profit-cumulative last year)

20.  Change Sales monthly= (Sales-cumulative this month)-( Sales-cumulative this month(last year))

21.  Change Profit monthly=( Profit-cumulative this month)-( Profit-cumulative this month(last year))

22.  Change Profit Monthly-%age= Change Profit monthly/ Profit-cumulative this month (last year)

23.  Change Sales Monthly-%age= Change Sales monthly/( Sales-cumulative this month(last year))

24.  Change Profit Yearly-%age= Change Profit yearly/ (Profit-cumulative last year)

25.  Change Sales Yearly-%age=Change Sales Yearly/(Sales-cumulative last year)

26.  Percent- cumulative this year= (Profit- cumulative this year)/(Sales-cumulative this year)

27.  Change Daily Average (Yearly) = (Daily Average (last year)-Yearly)-( Daily Average (this year)-Yearly)

28.  Change Daily Average (Monthly) = (Daily Average (last year)-Monthly) - (Daily Average (this year)-Monthly)


2.3    Report Layout    

Specifications need to be given by the client.

 


 

 

3      Efficiency Analysis

/* this will have to be worked out with the client*/

 

The efficiency analysis objects below are pulled from different areas that will help determine how efficient the report is in processing and in presentation.  (Example - Will the report complete if its YTD and will it file out to Excel successfully)

 

BOR #  =                                  The BusObj Report Number issued by IT when the report has been Implemented.  If the report is new then type “NEW”.

Periocity =                                How often the report is scheduled to run. (Monday, First of Month, etc,.)

Time Code =                             The time period code that indicates the time period that was use while testing the report. (Daily, WK, MTD, QTR, YTD, etc,.)

File Size (KB) WO/Data =           The size of the rep file without any data in KB

File Size (MB) WO/Data =          The size of the rep file without any data in MB (take the KB size and divide it by 100000)

File Size (KB) W/Data =             The size of the rep file with test data in KB 

File Size (MB) W/Data =             The size of the rep file with test data in MB (take the KB size and divide it by 100000)

Comments =                            

DP Name =                               Name of Data Provider (if variable created from Data Provider, if not, will be blank?)

Run Time =                               Time it took for the DP to complete for the time period that was tested

Number of Rows =                    Number of Rows processed/retrieved

Tab Number =                           Number assigned to the report tab

Tab Name =                              Name of the report tab

Number of Pages =                   The number of pages the on the report tab.

 

BOR #

Periocity

Time Code

File Size (KB) WO/Data

File Size (MB) WO/Data

File Size (KB) W/Data

File Size (MB) W/Data

Comments

 

 

 

 

 

 

 

 

 

 

DP Name

Run Time

Number of Rows

 

 

 

 

 

Tab Number

Tab Name

Number of Pages

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4      Run Time Options

Documents can be run on the Web, on the Client, or by the Scheduler.  Ad-Hoc reports are executed on the Web using Web Intelligence (WEBI) or using Business Objects on the Client.  The following shall be the mode of running the reports:

 

  • Daily Sales Report: Scheduled
  • Sales Analysis Report(Sales Officer’s view): scheduled
  • Sales Analysis Report(Regional Manager’s view): executed on the web using WEBI. Open for slicing and dicing.

 

With this we have specified the run time environment for the reports.

 

 

4.1    Scheduler Options

 

Report Name

Periocity

Mon

Tue

Wed

Thr

Fri

Sat

Sun

Other Frequency

Run Time

Run Time Parameters

Intranet Navigation

BO Category

File

Type

Sales Analysis Report

Monthly

Y

 

 

 

 

 

 

N/A

Not yet decided

Please see the list of prompts

Details not available

N/A

Xls

Daily Sales Report

weekly

Y

 

 

 

 

 

 

N/A

Not yet decided

Please see the list of prompts

Details not available

N/A

pdf

 

 

 


5      Examples

Insert the object file containing the layout of the report.  This may be an Adobe .pdf, Excel worksheet .xls, Business Object Report .rep, or a text .txt file.  Double clicking the icon will open the report layout file. 

 

(From the Toolbar: Insert / Object / Create from File / Browse for file / OK.)

 

 

 

6      Change log

 

Enter a summary of the changes that have taken place.  If new document, enter “New” in Detail of Change.

 

Date

Name

Detail of Change

 

 

 

 

 

 

 

8.   Adhoc Reporting Requirements

The following ad hoc reporting requirements shall be fulfilled by the data mart.

In this section of the document we shall be taking a closer look at the ad hoc  requirements that were gathered form the sales officers. Based on the model that has been prepared  and the gathered report requirements we shall seek to document whether the particular ad hoc reporting requirement shall be met or not. We shall not be looking into the exact nature of the sql that shall be used to generate the reports. However in whatever case possible we shall elaborate on how the required information shall be furnished.

 

 

 

 

 

 

 

 

 

 

 

Report requirement

Possible(P)/Not Possible(NP) with the data mart

Measures

Possible dimensions

Any other description

Analysis on sales ranking

 

P

 

sum(Sales)

Region, sales area

Ranking to be carried out in reports

Sales figures by product group

P

Sum(sales)

Product group[Product_Line_Dim]

 

Sales figures by product warehouse

P

Sum(sales)

 

 

Sales figures by mover code

NP

Sum(sales)

We need to add the mover code attribute in Item dimension

 

Customer history change-Sales by price group

NP

 

Price group is not captured in the data mart. However the list price is captured as a dimension attribute[ITEM_IMC_PRICE in Item dimension]

 

Customer history change-Sales by class code

P

Sum(sales)

Date, Item_Class_Desc(Item_class_Dim)

 

Customer history change-Sales by markup

 

NP

 

 

In the fact Item_sales_IMC_Price and Item_sales_Selling _Price are measure objects. We shall not be able to group by them

Customer history change-Sales by sales price

NP

 

 

In the fact Item_sales_IMC_Price is a measure object.

Pricing analysis over period of time

P

Average(Sale_IMC_Price)

Date, product, product group

 

Product profile of a customer

P

Sales, pricing factor

Product group, months

 

Sales analysis by customer category

P

Sales

Customer category(TBL_Customer_type_dim)

 

Count of customers changed in 3, 6 months

P

Count(Customer_ID)

Date, Month, Year

 

Frequency of buying parts

P

Count(Item_ID)

date, month, year

 

 

 

 

1      Reports

1.1    Layout and Titles

 

 Sales Analysis Report: as of now we have assumed that this shall be a single report with multiple tabs for weekly channel, weekly web, monthly channel and monthly web sales. In case the refresh cycles for the weekly and the monthly sales are different then we may have to consider them as separate reports.  

Report (Tab)  #

Page Layout

Report (Tab) Name

Description

Other Considerations

Landscape

Portrait

Letter Size

Legal Size

 

 

x

 

 

Monthly customer sales analysis(sales officer view)

This particular report gives the profitability analysis for each customer based on channel sales. Channel sales shall include all the class codes including the web sales. The analysis shall be carried out in terms of each sales area. The purpose of this report is to get a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis. In this report we shall not be reporting for the corporate entities.

 

 

 

x

 

 

Monthly web  sales analysis(sales officer view)

This particular report gives the profitability analysis for each customer based on web sales. The analysis shall be carried out in terms of each sales area. The purpose of this report is to get a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis. In this report we shall not be reporting for the corporate entities.

 

 

 

x

 

 

Monthly customer sales analysis(Regional Manager’s view)

This particular report gives the profitability analysis for each customer based on channel sales. Channel sales shall include all the class codes excluding web sales. The analysis shall be carried out in terms of each sales area. The purpose of this report is to get a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis. In this report we shall not be reporting for the corporate entities.

 

 

 

x

 

 

Monthly web sales analysis(Regional Manager’s View)

This particular report gives the profitability analysis for each customer based on web sales. The analysis shall be carried out in terms of each sales area. The purpose of this report is to get a trend analysis for sales and profits for each of the accounts of the sales officer on a monthly and 6 monthly basis. In this report we shall not be reporting for the corporate entities.

 

 

Daily Sales Report

Report (Tab)  #

Page Layout

Report (Tab) Name

Description

Other Considerations

Landscape

Portrait

Letter Size

Legal Size

1

 

x

 

 

Regional

This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of regions. Comparisons shall be made from the present year with the figures of last year.

 

2

 

x

 

 

Shipping demand branch

This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of shipping demand branch. Comparisons shall be made from the present year with the figures of last year.

 

3

 

x

 

 

Original demand branch

This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of original demand branch. Comparisons shall be made from the present year with the figures of last year.

 

4

 

x

 

 

Customer type

 

 

This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of customer type. Comparisons shall be made from the present year with the figures of last year.

 

5

 

x

 

 

Sales Area

This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of sales area. Comparisons shall be made from the present year with the figures of last year.

 

6

 

x

 

 

Order method

This particular tab gives the daily, monthly and the yearly analysis for sales, cost, profit and % of profitability in terms of order method. Comparisons shall be made from the present year with the figures of last year.

 

 

 


1.2    Report Fields

 

Throughout this document we shall use Sales and cost under various denominations. For the purpose of this report sales and cost have been defined in the following manner:

  • Sales is defined as value of sales order+ value of returned merchandise +value of returned defective orders+ value of goodwill orders.
  • Reporting cost can be obtained from the line item entry.
  • Actual Cost is the cost from the inventory
  • Gross Profit(Actual)=sales-actual cost
  • Net profit(Actual)=Gross Profit(Actual)-freight cost
  • Gross Profit(Reporting)=sales-reporting cost
  • Net Profit(Reporting)=sales-reporting cost-freight cost
  • Freight as % age of sales=Freight cost/Sales
  • Return Rate= value of returned merchandise +value of returned defective orders+ value of goodwill orders
  • Return(%age sales)= Return Rate/Sales
  • ‘Percent’ is Profit/Sales.
  • Previous fiscal month is defined as current fiscal month-1
  • Order : this refers to the order line entries.
  • ‘Profit’[for the respective case scenario] is Sales-Cost
  • ‘Percent’ is Profit/Sales.

 

 

                                                                                                        

Report Name: Sales Analysis report

 

Report #:                      1                                   

 

Field Name:                  Customer number

 

Business Description:    the number of the customer

 

Field Name:                  Name

 

Business Description:    Customer name

 

 

 

Field Name:                  Sales (previous Month)

 

Business Description:    It is the total sales for the previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month.

 

 

 

Field Name:                  Net Sales 12 month average

 

Business Description:    it gives the average of the total sales over last 12 fiscal months. The 12 fiscal month period is calculated as the number of business days between the first fiscal day of the current fiscal month-13 and the last fiscal day of the previous fiscal month. In case we reach the first fiscal month of the current fiscal year during the calculation then we shall continue by doing a backward calculation from the last fiscal month of the previous fiscal year.

 

 

 

Field Name:                  Net Sales 3 month average

 

Business Description:    it gives the average of the total sales over last 3 fiscal months. The 3 fiscal month period is calculated as the number of business days between the first fiscal day of the current fiscal month-4 and the last fiscal day of the previous fiscal month. In case we reach the first fiscal month of the fiscal year during the calculation then we shall continue by doing a backward calculation from the last fiscal month of the previous fiscal year

 

 

Field Name:                  Gross Profit 3 month average[reporting]

 

Business Description:    it gives the average of the total gross profit(reporting) over last 3 fiscal months. The 3 fiscal month period is calculated as the number of business days between the first fiscal day of the current fiscal month-4 and the last fiscal day of the previous fiscal month. In case we reach the first fiscal month of the fiscal year during the calculation then we shall continue by doing a backward calculation from the last fiscal month of the previous fiscal year. Gross profit (reporting) is defined as sales-reporting cost.

 

 

Field Name:                  Gross Profit 12 month average (reporting)

 

Business Description:    it gives the average of the total profits(reporting) over last 12 fiscal months. The 12 fiscal month period is calculated as the number of business days between the first fiscal day of the current fiscal month-13 and the last fiscal day of the previous fiscal month. In case we reach the first fiscal month of the fiscal year during the calculation then we shall continue by doing a backward calculation from the last fiscal month of the previous fiscal year. Gross profit (reporting) is defined as sales-reporting cost.

 

 

Field Name:                  Gross Profit previous month (reporting)

 

Business Description:    It gives the gross profit (reporting) for the previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. Gross profit (reporting) is defined as sales-reporting cost.

 

Field Name:                  Net Profit previous month (reporting)

 

Business Description:    It gives the net profit (reporting) for the previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. Net profit (reporting) is defined as sales-reporting cost-freight cost.

 

Field Name:                  %age freight sale previous month

 

Business Description:    It gives the % ratio of the freight costs with regards to the total sales. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month.

 

 

 

Field Name:                  Return Rate previous month

 

Business Description: It gives the return rate for previous month. Return rate is defined as value of returned merchandise +value of returned defective      orders+ value of goodwill orders. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month.

 

Field Name:                  Customers Lost-previous month

 

Business Description:    It gives the number of customers lost between the first fiscal day and the last fiscal day of the previous month.  Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. A lost customer shall have 0 sales for the previous fiscal month and non-zero and greater than 0 sales for the current fiscal month-2.

 

 

Field Name:                  Customers gained-previous month

 

Business Description:    It gives the number of customers lost within the first fiscal day and the last fiscal day of the previous month.  Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. A gained customer shall have 0 sales for the current fiscal month-2 and greater than 0  sales for the previous fiscal month.

 

Field Name:                  Growth Rate by customer segment

 

Business Description:    This gives the sales(sales order+ value of returned merchandise +value of returned defective orders+ value of goodwill orders) per number of active customers for the previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. Active customers are defined as those customers with sales greater than 0. This measure’s slice and dice parameters include month, customer segment and sales area.

 

Field Name:                  Average order size

 

Business Description:    This gives the $ volume per  order for the previous month. Previous month is defined as the number of fiscal days between the first fiscal day of the previous fiscal month and the last fiscal day of the previous fiscal month. The $ value of the order is given by the sale volume. The orders selected should be such that the order line items should have 0 return rate (value of returned merchandise +value of returned defective orders+ value of goodwill orders).

 

 

 

 

 

Break/Sort:                   Salesman Number and Sales Man Name(SB)

 

 

Totaling Options:           Not specified in the word document as of now

 

 

Field Name:                  Region

 

Business Description:    Region name

 

 

Field Name:                  Sales-this date

 

Business Description:    Sales as of a given date [sale as of date the report has been refreshed]

 

 

Field Name:                  Cost-this date

 

Business Description:    Actual Cost as of a given date [Actual cost as of date the report has been refreshed]

 

Field Name:                  Profit-this date

 

Business Description:    Actual Profit as of a given date[profit as of date the report has been refreshed. Profit shall be defined as Sales-Actual Cost]

 

 

Field Name:                  Percent-this date

 

Business Description:    Percentage Actual profit as of a given date

 

 

 

Field Name:                  Sales-this date (last year)

 

Business Description:    Sales as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]

 

Field Name:                  Cost-this date (last year)

 

Business Description:    Actual Cost as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]

 

 

Field Name:                  Profit-this date (last year)

 

Business Description:    Actual Profit as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]

 

 

Field Name:                  Percent-this date (last year)

 

Business Description:    Percentage actual profit as same day last year[same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]

 

 

 

Field Name:                  Sales-cumulative this month

 

Business Description:    Cumulative sales as of this month [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal month]

 

Field Name:                  Sales (%age)-overall sales (this month)

 

Business Description:    Percentage Cumulative sales as of this month per region [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal month]

 

 

 

 

Field Name:                  Cost-cumulative this month

 

Business Description:    Cumulative actual cost as of this month [cumulative cost for the number of business days between the date the report is refreshed

and the first day of the current fiscal month]

 

 

Field Name:                  Profit-cumulative this month

 

Business Description:    Cumulative actual profit as of this month [cumulative profit for the number of business days between the date the report is refreshed

and the first day of the current fiscal month]

 

 

 

 

Field Name:                  Percent-cumulative this month

 

Business Description:    Percentage actual profit as of this month [percentage profit for the number of business days between the date the report is refreshed

and the first day of the current fiscal month]

 

 

Field Name:                  Sales-cumulative this month (last year)

 

Business Description:    Cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Sales (%age)-overall sales(this month last year)

 

Business Description:    Percentage (Region/customer type/shipping demand/original demand) cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

Field Name:                  Cost- cumulative this month (last year)

 

Business Description:    Cumulative Actual cost as of this month last year [cumulative cost for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

Field Name:                  Profit- cumulative this month (last year)

 

Business Description:    Cumulative actual profit as of this month last year [cumulative profit for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

Field Name:                  Percent- cumulative this month (last year)

 

Business Description:    Cumulative percent actual profit as of this month last year [cumulative percent profit for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

 

Field Name:                  Sales-cumulative this year

 

Business Description:    Cumulative sales as of this year [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal year]

 

 

Field Name:                   Daily Average (this year)-Yearly

 

Business Description:    Cumulative sales as of this year [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal year] per number of business days beginning from the first business day of the current fiscal year to the date of report refresh.

 

 

 

Field Name:                  Sales (%age)-overall sales (this year)

Business Description:  Percentage (Region/customer type/shipping demand/original demand) cumulative sales as of this year [cumulative percentage sales for the number of business days between the date the report is refreshed and the first day if the current fiscal year]

 

 

 

Field Name:                  Cost-cumulative this year

 

Business Description:    Cumulative actual cost as of this year [cumulative cost for the number of business days between the date the report is refreshed and the first day of the current fiscal year]

 

 

Field Name:                  Profit-cumulative this year

 

Business Description:    Cumulative actual profit as of this year [cumulative profit for the number of business days between the date the report is refreshed and the first day of the current fiscal year]

 

 

Field Name:                  Percent-cumulative this year

 

Business Description:    Percentage Actual profit as of this year [percentage profit for the number of business days between the date the report is refreshed and the first day of the current fiscal year]

 

Field Name:                  Daily Average this year (monthly)

 

Business Description:    Cumulative sales as of this month [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal month] per total number of business days from the first fiscal day of the current fiscal month in the current fiscal year to the date of refresh.

 

 

Field Name:                  Sales-cumulative last year

 

Business Description:    Cumulative sales as of last year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

 

Field Name:                  Sales (%age)-overall sales (last year)

 

Business Description:    Percentage (Region/customer type/shipping demand/original demand) as of last year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Cost- cumulative last year

 

Business Description:    Cumulative Actual cost as of last year [cumulative cost for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

Field Name:                  Profit- cumulative last year

 

Business Description:    Cumulative Actual profit as of last year [cumulative profit for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

 

Field Name:                  Percent- cumulative last year

 

Business Description:    Cumulative percent Actual profit as of last year [cumulative percent profit of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day (the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                   Daily Average (last year)-Yearly

 

Business Description:    Cumulative sales as of last year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year] per the number of business days beginning from the first fiscal day of the previous fiscal year to the date of report refresh last year [the same business day for the same fiscal month and week for previous fiscal year].

 

 

 

 

Field Name:                  Daily Average last year (monthly)

 

Business Description:    Cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year] per

                                       the number of business days beginning from the first fiscal day for the same fiscal month in the previous fiscal year to the same fiscal day as the date of report refresh last year[The same fiscal day as the date of report refresh last year is the same fiscal day as the date of report refresh in the same fiscal week and month of the previous fiscal year] .

 

Field Name:                  Days-month

 

Business Description:    The number of business days beginning from the first business day of the month to the date of report refresh in the current fiscal month of the current fiscal year.

 

Field Name:                  Days-month (last year)

 

Business Description:    The number of business days beginning from the first fiscal day for the same fiscal month in the previous fiscal year to the same fiscal day as the date of report refresh last year[The same fiscal day as the date of report refresh last year is the same fiscal day as the date of report refresh in the same fiscal week and month of the previous fiscal year] .

 

Field Name:                  Days-year

 

Business Description:    The number of business days beginning from the first business day of the current fiscal year to the date of report refresh.

 

 

Field Name:                  Days-year (last)

 

Business Description:    The number of business days beginning from the first fiscal day of the previous fiscal year to the date of report refresh last year [the same business day for the same fiscal month and week for previous fiscal year].

 

 

Field Name:                  Change Sales daily

 

Business Description:    The difference between Sales as of a given date [sale as of date the report has been refreshed] and Sales as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous  fiscal year]

 

Field Name:                  Change Sales daily-%age

 

Business Description:    The %age change in daily sales(The difference between Sales as of a given date [sale as of date the report has been refreshed] and Sales as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]) with respect to Sales as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year])

 

 

Field Name:                  Change Profit Daily

 

Business Description:    The difference between Actual Profit as of a given date[given date is the date of report refresh] and  Profit as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year]

 

Field Name:                  Change Profit Daily-%age

 

Business Description:    The %age change in daily Actual profit (The difference between Actual Profit as of a given date[given date is the date of report refresh] and  Profit as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year] with respect to Profit as of same day last year [same day last year is defined as the same fiscal day for the same fiscal week and month for the previous fiscal year])

 

 

Field Name:                  Change Sales monthly

 

Business Description:    The difference between cumulative sales as of this month [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal month] and cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Change Sales monthly-%age

 

Business Description:    The %age change in monthly sales(The difference between cumulative sales as of this month [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal month] and cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]) with respect to cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year

 

Field Name:                  Change Profit Monthly

 

Business Description:    The difference between Cumulative Actual profit as of this month [cumulative profit for the number of business days between the date the report is refreshed and the first day of the current fiscal month] and cumulative actual profit as of this month last year [cumulative profit for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Change Profit Monthly-%age

 

Business Description:    the % age change in monthly Actual Profit(The difference between Cumulative profit as of this month [cumulative profit for the number of business days between the date the report is refreshed and the first day of the current fiscal month] and cumulative profit as of this month last year [cumulative profit for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]) with respect to cumulative profit as of this month last year [cumulative profit for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Change Sales Yearly

 

Business Description:    The difference between cumulative sales as of this year [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal year] and cumulative sales as last year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Change Sales Yearly-%age

 

Business Description:    The %age change in yearly sales(The difference between cumulative sales as of this year [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal year] and cumulative sales as last year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]) with respect to cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year

 

Field Name:                  Change Profit Yearly

 

Business Description:    The difference between Cumulative Actual Profit as of this year [cumulative profit for the number of business days between the date the report is refreshed and the first day of the current fiscal year] and cumulative Actual Profit for last year [cumulative profit for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Change Profit Yearly-%age

 

Business Description:    the % age change in yearly  Actual profits(The difference between Cumulative actual profit as of this year [cumulative actual profit for the number of business days between the date the report is refreshed and the first day of the current fiscal year] and cumulative actual profit for last year [cumulative actual profit for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]) with respect to cumulative actual profit for last year [cumulative actual profit for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year]

 

Field Name:                  Change Daily Average (Monthly)

 

Business Description:    Difference between cumulative sales as of this month last year [cumulative sale for the number of business days between the 1st fiscal day of the same fiscal month in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year] per

                                       the number of business days beginning from the first fiscal day for the same fiscal month in the previous fiscal year to the same fiscal day as the date of report refresh last year[The same fiscal day as the date of report refresh last year is the same fiscal day as the date of report refresh in the same fiscal week and month of the previous fiscal year] and cumulative sales as of this month [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal month] per total number of business days from the first fiscal day of the current fiscal month in the current fiscal year to the date of refresh.

 

 

Field Name:                  Change Daily Average (Yearly)

 

Business Description:    Difference between cumulative sales as of last year [cumulative sale for the number of business days between the 1st fiscal day in the previous fiscal year and the same fiscal day(the same fiscal day in the same fiscal month and fiscal week) as the date of refresh in the previous fiscal year] per the number of business days beginning from the first fiscal day of the previous fiscal year to the date of report refresh last year [the same business day for the same fiscal month and week for previous fiscal year] and cumulative sales as of this year [cumulative sales for the number of business days between the date the report is refreshed and the first day of the current fiscal year] per number of business days beginning from the first business day of the current fiscal year to the date of report refresh.

 

                                                                                                                                              

Break/Sort:                   Date of the report

 

 

Totaling Options:           sum sales, cost, Profit. We need to take an average of %.

 

In the other tabs of the report all the other fields besides the first one shall remain the same. We shall be listing only the first field name for the description of the remaining tabs.

 


1.3    Prompts

 

Sales Analysis Report

 

Prompts are the filter options. For each of the tabs 1 to 4 they need to be specified by the client.

 

Tab #1: Monthly customer sales analysis (sales officer view). There shall be no prompts in this case. The sales officer shall be given a static report.

 

Tab #2: Monthly web sales analysis (sales officer view). There shall be no prompts in this case. The sales officer shall be given a static report.

 

Tab #3: Monthly customer sales analysis (Regional Manager’s view): The filter criteria are as following:

 

Prompt #:                     1

 

Tab #:                            3

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono                                                                      

 

 

Prompt #:                     2

 

Tab #:                            3

 

Prompt Message:          ‘Please select the region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

                                                                  

Tab #4: Monthly web sales analysis (Regional Manager’s View):

 

 

Prompt #:                     3

 

Tab #:                           4

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

 

Prompt #:                     4

 

Tab #:                            4

 

Prompt Message:          ‘Please select the region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

Daily Sales Report:

 

We  shall be selecting only the following  as the filtering criteria.

 

Prompt #:                     1

 

Tab #:                            1

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

Prompt #:                     2

 

Tab #:                            2

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

Prompt #:                     3

 

Tab #:                            2

 

Prompt Message:          ‘Please select the region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

Prompt #:                     4

 

Tab #:                            3

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

Prompt #:                     5

 

Tab #:                           3

 

Prompt Message:          ‘Please select the region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

Prompt #:                     6

 

Tab #:                           4

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

Prompt #:                     7

 

Tab #:                            4

 

Prompt Message:          ‘Please select the region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

 

Prompt #:                     8

 

Tab #:                           5

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

Prompt #:                     9

 

Tab #:                            5

 

Prompt Message:          ‘Please select the Region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

Prompt #:                     10

 

Tab #:                           6

 

Prompt Message:          ‘Please select the date’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

 

 

 

Prompt #:                     11

 

Tab #:                           6

 

Prompt Message:          ‘Please select the Region’

 

Prompt Conditions:       =

 

Prompt Options:           Mono

                             

 

 

2      Technical Details

2.1    Data Provider Details

 

Each of the tabs should have a distinct query running for the same. There shall be 4 different queries running to get the desired report from the same universe. No query operators (such as union and intersection) shall be required between the queries. All the queries shall be run from the single universe that has been designed.

 

Please see the SQL section to get further clarification on the same.

 

It is quite likely that the team shall use aggregate tables/aggregate awareness within Business Objects  to run these reports. That is we shall be generating the required aggregates sum(sales), sum(cost) at the database level, grouped at the required level of hierarchy as specified by the client. This shall restrict additional group by, drill through and drill down options for the reports.


 

2.1.1     Result Objects

 

 

Sales Analysis Report

The following are the result objects for the ‘Sales Analysis Report’.

Sales: measure

Cost: measure

Gross Profit: measure

Net profit: measure

Month: dimension

Customer: dimension

Sales area: dimension

Sales person (shall be used as a segment): dimension

Sales channel: dimension

Item class: dimension                              

Product class: dimension

Product Line: dimension

Geographic code: dimension.

 

 

Sort details: the sorting needs to take place by the value of a measure object. Hence we cannot specify the conditions for the same in the query.

 

 

Daily Sales Report: The following are the objects as per our anticipation as of now.

Region: dimension

Shipping branch: dimension

Original demand branch: dimension [both original demand branch and shipping branch form part of the supply chain dimension. Should we add additional filtering criteria for the same?]

Sales order method (web based, channel): dimension

Fiscal calendar: dimension

Sales Area: dimension

Customer: dimension

Days: Measure

Sum of Sales: Measure

Sum of Cost: Measure object

 

 

Sort details: N/A

 

Query Name: N/A.

 

 

The client has clarified that it does not require additional grouping options such as geographic and product dimensions for this report.

 

 

 


2.1.2     Condition Objects

 

On a preliminary glance this report can be made in one of the 4 ways:

 

Method 1: run a single query with the following date range

 

Date in the range between date of refresh and the 1st fiscal day of the current fiscal month-2.

 

 

Method 2: use of the summary tables. I would recommend this course of action in case you wish to run the reports on an adhoc basis. We shall have to discuss the structure of the summary tables with Sid.

 

 

 

Method 3: use of derived tables.

 

                                   

Method 4: use of correlated sub queries

 

 

Method 5: use of aggregate awareness within the universe

 

 

The option chosen by the technical team shall depend on the following factors:

  • The data load for the queries
  • The slice and dice capabilities expected for each view by the client
  • Whether the reports shall run on an ad hoc basis or they shall run on a pre scheduled basis.

 


SQL

 

Sales analysis report

The SQL will involve inner join between dimension and Fact with the filter conditions for the dimensions.

We are presenting the approximate SQL for each of the tabs in consideration. However in case aggregates are produced then the sql shall change.

 

Tab #1:

Select

sum(Sales), sum(cost), sum(profit),

month, customer, sales area, sales person, sales channel, product groups, geographic code

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the (fiscal month-13) of the date of refresh

 and sales channel= specify sales channel

group by  month, customer, sales area, sales channel, geographic code, item class, product class, product line

 

                                                      

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • The sorting criteria specified by the client involves the sum of sales. This is a measure attribute. We shall have to specify the same in the report.

 

Tab #2:

Select

sum(Sales), sum(cost), sum(profit),

month, customer, sales area, sales person, sales channel, product groups, geographic code

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the (fiscal month-13) of the date of refresh

 and sales channel= specify web channel

group by  month, customer, sales area, sales channel, geographic code, item class, product class, product line

 

                                                      

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • The sorting criteria specified by the client involves the sum of sales. This is a measure attribute. We shall have to specify the same in the report.

 

 

Tab #3:

Select

sum(Sales), sum(cost), sum(profit), count(distinct(customers))

month, customer, sales area, sales person, sales channel, product groups, geographic code

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the (fiscal month-13) of the date of refresh

 and sales channel= specify web channel

group by  month, customer, sales area, sales channel, product groups, geographic code, item class, product class, product line

 

                                                      

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • The drill down/drill through shall be as per the dimensions specified in the group by option namely: date, customer, sales area, geographic code,  sales channels, item class, product class and product line.
  • The client expects no additional ‘group by’ options with any of the other dimensions.
  • The sorting criteria specified by the client involves the sum of sales. This is a measure attribute. We shall have to specify the same in the report.

 

 

 

Daily Sales Report

Tab #1:

 

Select region,

sum(Sales), sum(cost), date, month, year

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the previous fiscal year

group by  region, date, month, year

                                                      

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • No sorting criteria have been given by the client.

 

                                                               

Tab #2:

Select shipping demand branch, region

sum(Sales), sum(cost), date, month, year

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the previous fiscal year

 and prompt for region

group by  shipping demand branch, date, month, year,  region

 

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects to add ‘region’ as a segment in the reports. That is why we have had to group by region. However the client does not expect to drill down by the region parameter.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • No sorting criteria have been given by the client.

 

 

Tab #3:

Select original demand branch, region,

sum(Sales), sum(cost), date, month, year

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the previous fiscal year

 and prompt for region

group by  original demand branch, date, month, year, region

 

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects to add ‘region’ as a segment in the reports. That is why we have had to group by region. However the client does not expect to drill down by the region parameter.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • No sorting criteria have been given by the client.

 

 

Tab #4:

Select order method, region,

sum(Sales), sum(cost), date, month, year

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the previous fiscal year

 and prompt for region

group by  order method, date, month, year, region

 

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects to add ‘region’ as a segment in the reports. That is why we have had to group by region. However the client does not expect to drill down by the region parameter.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • No sorting criteria have been given by the client.

 

 

Tab #5:

Select customer type, region,

sum(Sales), sum(cost), date, month, year

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the previous fiscal year

 and prompt for region

group by  customer type, date, month, year, region

 

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects to add ‘region’ as a segment in the reports. That is why we have had to group by region. However the client does not expect to drill down by the region parameter.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.

 

  • No sorting criteria have been given by the client.

 

 

 

Tab #6:

Select sales area, region,

sum(Sales), sum(cost), date, month, year

from [specify the join conditions between the dimensions and the fact. /*please see the note at the bottom of the page*/]

where specify the date range between the prompt date and 1st day of the previous fiscal year

 and prompt for region

group by  sales area, date, month, year, region

 

Additional details about this tab[all the points have been finalized in discussion with the client]:

  • There is no drill down/drill through expected through any of the dimensions. This is a static view.
  • The client expects to add ‘region’ as a segment in the reports. that is why we have had to group by region. However the client does not expect to drill down by the region parameter.
  • The client expects no additional ‘group by’ options with any of the other dimensions. No slice and dice capabilities are expected.
  • No sorting criteria have been given by the client.

 

 

/* details about the join conditions between dimensions and facts*/

 

The way in which shall design the universe shall entail an inner join between dimensions and fact. A sample of the same(from one of the author’s previous projects) is attached below:

 

 From (("ETMC"."dbo"."fact_ledger2" "fact_ledger2"

INNER JOIN "ETMC"."dbo"."dim_account" "dim_account" ON

"fact_ledger2"."acct_dim_id"="dim_account"."acct_dim_id")

INNER JOIN "ETMC"."dbo"."dim_company" "dim_company" ON

"fact_ledger2"."comp_dim_id"="dim_company"."comp_dim_id")

INNER JOIN "ETMC"."dbo"."fiscal_calender" "fiscal_calender" ON

"fact_ledger2"."CAL_id"="fiscal_calender"."cal_id"

inner join "ETMC"."dbo"."dim_dept" "dim_dept" on

"fact_ledger2"."dept_dim_id"="dim_dept"."dept_dim_id"

 

 


2.2    Document Variables

 

 

1.     %age freight sale previous month= Return Rate previous month/sales(previous month)

2.     Average order size=sum(sales)/count(clean order)

3.     Profit-this date= (sales-this date)-(cost-this date)

4.     Percent-this date=(profit-this date)/(Sales-this date)

5.     Profit-this date(last year)= [sales-this date(last year)]-[cost-this date(last year)]

6.     Percent-this date(last year)= Profit-this date(last year)/ sales-this date(last year)

7.     Percent-cumulative this month=Profit-cumulative this month/Sales-cumulative this month

8.     Percent- cumulative this month (last year)=Profit- cumulative this month (last year)/Sales-cumulative this month (last year)

9.     Daily Average (this year)-Yearly=Sales-cumulative this year/Days-year

10.  Daily Average this year (monthly)=Sales-cumulative this month/Days-month

11.  Percent- cumulative last year=(Profit- cumulative last year)/(Sales-cumulative last year)

12.  Daily Average (last year)-Yearly=Sales-cumulative last year/Days-year (last)

13.  Daily Average last year (monthly)= Sales-cumulative this month (last year)/Days-month (last year)

14.  Change Sales daily= (Sales-this date) - (Sales-this date (last year))

15.  Change Profit daily= (Profit-this date)-(Profit-this date (last year))

16.  Change Sales daily-%age= Change Sales daily/(Sales-this date (last year))

17.  Change Profit daily-%age= Change Profit daily/( Profit-this date (last year))

18.  Change Sales Yearly= (Sales-cumulative this year)-(Sales-cumulative last year)

19.  Change Profit Yearly= (Profit-cumulative this year)-(Profit-cumulative last year)

20.  Change Sales monthly= (Sales-cumulative this month)-( Sales-cumulative this month(last year))

21.  Change Profit monthly=( Profit-cumulative this month)-( Profit-cumulative this month(last year))

22.  Change Profit Monthly-%age= Change Profit monthly/ Profit-cumulative this month (last year)

23.  Change Sales Monthly-%age= Change Sales monthly/( Sales-cumulative this month(last year))

24.  Change Profit Yearly-%age= Change Profit yearly/ (Profit-cumulative last year)

25.  Change Sales Yearly-%age=Change Sales Yearly/(Sales-cumulative last year)

26.  Percent- cumulative this year= (Profit- cumulative this year)/(Sales-cumulative this year)

27.  Change Daily Average (Yearly) = (Daily Average (last year)-Yearly)-( Daily Average (this year)-Yearly)

28.  Change Daily Average (Monthly) = (Daily Average (last year)-Monthly) - (Daily Average (this year)-Monthly)


2.3    Report Layout    

Specifications need to be given by the client.

 


 

 

3      Efficiency Analysis

/* this will have to be worked out with the client*/

 

The efficiency analysis objects below are pulled from different areas that will help determine how efficient the report is in processing and in presentation.  (Example - Will the report complete if its YTD and will it file out to Excel successfully)

 

BOR #  =                                  The BusObj Report Number issued by IT when the report has been Implemented.  If the report is new then type “NEW”.

Periocity =                                How often the report is scheduled to run. (Monday, First of Month, etc,.)

Time Code =                             The time period code that indicates the time period that was use while testing the report. (Daily, WK, MTD, QTR, YTD, etc,.)

File Size (KB) WO/Data =           The size of the rep file without any data in KB

File Size (MB) WO/Data =          The size of the rep file without any data in MB (take the KB size and divide it by 100000)

File Size (KB) W/Data =             The size of the rep file with test data in KB 

File Size (MB) W/Data =             The size of the rep file with test data in MB (take the KB size and divide it by 100000)

Comments =                            

DP Name =                               Name of Data Provider (if variable created from Data Provider, if not, will be blank?)

Run Time =                               Time it took for the DP to complete for the time period that was tested

Number of Rows =                    Number of Rows processed/retrieved

Tab Number =                           Number assigned to the report tab

Tab Name =                              Name of the report tab

Number of Pages =                   The number of pages the on the report tab.

 

BOR #

Periocity

Time Code

File Size (KB) WO/Data

File Size (MB) WO/Data

File Size (KB) W/Data

File Size (MB) W/Data

Comments

 

 

 

 

 

 

 

 

 

 

DP Name

Run Time

Number of Rows

 

 

 

 

 

Tab Number

Tab Name

Number of Pages

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4      Run Time Options

Documents can be run on the Web, on the Client, or by the Scheduler.  Ad-Hoc reports are executed on the Web using Web Intelligence (WEBI) or using Business Objects on the Client.  The following shall be the mode of running the reports:

 

  • Daily Sales Report: Scheduled
  • Sales Analysis Report(Sales Officer’s view): scheduled
  • Sales Analysis Report(Regional Manager’s view): executed on the web using WEBI. Open for slicing and dicing.

 

With this we have specified the run time environment for the reports.

 

 

4.1    Scheduler Options

 

Report Name

Periocity

Mon

Tue

Wed

Thr

Fri

Sat

Sun

Other Frequency

Run Time

Run Time Parameters

Intranet Navigation

BO Category

File

Type

Sales Analysis Report

Monthly

Y

 

 

 

 

 

 

N/A

Not yet decided

Please see the list of prompts

Details not available

N/A

Xls

Daily Sales Report

weekly

Y

 

 

 

 

 

 

N/A

Not yet decided

Please see the list of prompts

Details not available

N/A

pdf

 

 

 


5      Examples

Insert the object file containing the layout of the report.  This may be an Adobe .pdf, Excel worksheet .xls, Business Object Report .rep, or a text .txt file.  Double clicking the icon will open the report layout file. 

 

(From the Toolbar: Insert / Object / Create from File / Browse for file / OK.)

 

 

 

6      Change log

 

Enter a summary of the changes that have taken place.  If new document, enter “New” in Detail of Change.

 

Date

Name

Detail of Change

 

 

 

 

 

 

 

8.   Adhoc Reporting Requirements

The following ad hoc reporting requirements shall be fulfilled by the data mart.

In this section of the document we shall be taking a closer look at the ad hoc  requirements that were gathered form the sales officers. Based on the model that has been prepared  and the gathered report requirements we shall seek to document whether the particular ad hoc reporting requirement shall be met or not. We shall not be looking into the exact nature of the sql that shall be used to generate the reports. However in whatever case possible we shall elaborate on how the required information shall be furnished.

 

 

 

 

 

 

 

 

 

 

 

Report requirement

Possible(P)/Not Possible(NP) with the data mart

Measures

Possible dimensions

Any other description

Analysis on sales ranking

 

P

 

sum(Sales)

Region, sales area

Ranking to be carried out in reports

Sales figures by product group

P

Sum(sales)

Product group[Product_Line_Dim]

 

Sales figures by product warehouse

P

Sum(sales)

 

 

Sales figures by mover code

NP

Sum(sales)

We need to add the mover code attribute in Item dimension

 

Customer history change-Sales by price group

NP

 

Price group is not captured in the data mart. However the list price is captured as a dimension attribute[ITEM_IMC_PRICE in Item dimension]

 

Customer history change-Sales by class code

P

Sum(sales)

Date, Item_Class_Desc(Item_class_Dim)

 

Customer history change-Sales by markup

 

NP

 

 

In the fact Item_sales_IMC_Price and Item_sales_Selling _Price are measure objects. We shall not be able to group by them

Customer history change-Sales by sales price

NP

 

 

In the fact Item_sales_IMC_Price is a measure object.

Pricing analysis over period of time

P

Average(Sale_IMC_Price)

Date, product, product group

 

Product profile of a customer

P

Sales, pricing factor

Product group, months

 

Sales analysis by customer category

P

Sales

Customer category(TBL_Customer_type_dim)

 

Count of customers changed in 3, 6 months

P

Count(Customer_ID)

Date, Month, Year

 

Frequency of buying parts

P

Count(Item_ID)

date, month, year

 

 

 

 

Enter supporting content here