Analyse Sales & Margins - Deep Dive

To create your own fully customisable Workbook:

• Run the Analyse Sales & Margins [base] which is a View Only Workbook
• Read the article Analyse Sales & Margins - Quick Start first to become familiar with the basics and learn how to make simple changes
• Create a Copy, Rename and Save the new Workbook, this will now be fully customisable:
  • Click on Workbook Tab (Purple book icon)
  • Click Save As then Rename the new Workbook and Save
• Change from View Mode to Edit Mode
  • Click on Eye icon on Top line; or
  • Click on View Mode on the the Workbook Tab (Purple book icon)

View Mode

Edit Mode

This Workbook runs over Sales Invoices and includes multiple Reports/Workheets which allow you to instantly understand your sales trends and margins. Run across any date range, Identify best sellers and investigate issues.

Each Worksheet showcases a different report or chart style. Select the one that best matches the report style you want then simply copy it and customise to show the exact data you need.

• Customer - a Standard list type report that can be used to report Sales & Margins by Customer and almost any other sales related field e.g. Items, Salesperson, Customer Group etc.
• Monthly Sales - a Pivot type report, see data in Rows versus Columns
• Headline Sales - Sales Dashboard, combine multiple report widgets together
• 3M Dashboard - 3 Months - Sales Dashboard over past 3 months
• Sales Chart - Monthly Bar Chart

Customer

This is a Standard list type report that can be used as a template to report sales & margins by Customer and almost any other sales related field e.g. Items, Salesperson, Customer Group, Departments etc. 

In Edit Mode the Customer Report can now be fully customised as follows:

• Copy, Move, Rename or Delete Worksheets
• Edit to Access the Report Designer
• Change Fields - move, delete or add new fields 
• Group by - to add multiple levels of subtotals 
• Field Formatting - control decimals, colours, arrows etc
• Report Settings - control look and feel 

Copy, Move or Rename Worksheets

• Copy the Customer Report to preserve the original
• Rename the new one to Item for example 
• Change the Background Colour to make it stand out
• Learn more about Worksheets - Rename, Copy, Move or Delete

Edit to Access the Report Designer

Click on the Edit icon to access the advanced Report Designer

Change Fields

• Change the order of fields using Drag & Drop
• Remove fields by hovering over and field and clicking the X e.g. remove Cost fields
• Add New Fields 
  • Choose new Mapping Fields to add to the report
  • Fields are grouped together under headings
  • Search for fields by name e.g. Search for Item 
  • Select Item Number and Item Name instead of Customer Name to see Sales by Item and Drag and Drop these fields into position

• Change Field Name by clicking on the "..." to open field settings then change name to display
• Use Visible option to Hide of Show each field 

 

• Metrics - The Metrics fields are always the first ones listed and Metrics dimensions are grey. Reports must include at least one Metric. Metrics are the numbers so Qty, $Sales, Ave Price etc.

 

Group by

• Add New Fields to Group by
  • Choose new Mapping Fields to add as a Subtotal to the report
  • Move Customer Name to Group
  • Report now shows a list of Items sold to each Customer
  • Add a second Group by e.g. Location
  • indent the Customer Name to create a sub-group. This will first Group by the Location then show all Customers under that Location. 

 

Field Formatting

Control every aspect of each of the Metrics columns individually. Hover over a field and click on the "..." to open the Formatting pop up window:

• Visible - On/Off - Set to Off to Hide this column. Column can still be used to Sort by or in Calculations
• Format - Defaults or change to select Currency ($), Numeric or Percentage
• Accounting Style - On/Off - Set to On for Negatives in brackets or Off for Negatives as minus
• Decimal Places - None, Two etc
• Background Colour - Highlight a column with shading
• Fore Colour - Change font colour on a column
• Threshold - Set colour change if Threshold not met, e.g. on the margin column to show margins below 30% as Red font enter Threshold as .3 and colour as Red.
• Formatting Rule - Add Arrows/Colours to highlight problems e.g. low margins
• Show Total Value - On/Off - Set to display a Grand Total on this column or not
• Show Subtotal Value - On/Off - Set to display a Subtotal Total on this column or not

The Resulting Report will look as follows:

 

Report Settings

Click on the "hamburger" icon to display the Side Bar

In Edit Mode the Settings tab appears

 

• Grand Total Position - Change position of Grand Total to None/Bottom/Top
• Sub Totals Position - Change position of Sub Totals  to None/Bottom/Top
• Grand Total Text - Change the label name
• Sub total Text - Change the label name
• Show Row Count - Use to show number of rows e.g. Customers in report
• Filter Zeros - Rows where all Report metrics are zero are removed from the Report
• Keep Metrics on drill-down -When turned on, the metrics chosen on the Report will be the only metrics visible when you drill into the next level.
• Fixed Columns -For wide Reports keep some of the non-metric columns and metric on display permanently for ease of Report viewing when scrolling. Like Freeze Pane in excel.
• Enable Search Box - Permanently display a Search box to enable quick find option.
• Font Size - Change Font size
• GL Report - Used to produce GL P&L and Balance sheet format
• Expand All Rows - On/Off Default to Show all detail Rows or Collapse and only display Summary results

Monthly Sales Tab

This is a Pivot Type report that allows you to show and manipulate data in both Row and Column dimensions.

This report can be used as a template to report sales by Customer/Item versus almost any other sales related field e.g. Items, Salesperson, Months, Departments etc.

In Edit Mode the Monthly Sales report can now be fully customised using the Edit and Settings icons.

Edit to see Report Designer

Click on the Edit icon to access the advanced Report Designer

Note the layout for a Pivot type report is different from the List type report (above).

Fields (Rows)

• Add New Fields 
  • Choose new Mapping Fields to add to the report
  • Search for fields by name, e.g. Location
• Group by - in Pivot Type reports add the Group by Field as a Row and Indent Row data below, see below setup to show Customers, Grouped by Location

Columns (Pivot On)

• Change column data by selecting other Fields from the available list e.g. Select Item Group instead of Invoice date by Month to appear across the page, this shows what Item groups each Customer has bought
• Change column name by double clicking on the field name then edit it
• Group by - in Pivot Type reports you can also Group Columns together, add the Group by Field as a Column and Indent Column data below E.g. Item Group then by Item Sub-group. This will display Item Groups as a Column but also allows users to click to expand out to see  Item Sub-groups.

Metrics

Metrics are the numbers in your report. The Metrics Table is always the first table listed on the left and Metrics dimensions are displayed as grey. You are able to select multiple Metrics to display.

• Required - Reports must include at least one Metric but you are able to select multiple Metrics to display
• Metrics - Enter into separate Metrics box in Pivot style reports
• Row Formatting - See  Row Formatting above to learn more

Report Settings - Hamburger Icon

Click on the Settings "hamburger" icon to access the Report Settings popup menu. The Settings options for Pivot style reports are slightly different from List style reports abovle.

• Grand Total Text - Change the label name
• Sub total Text - Change the label name
• Filter Zeros - Rows where all Report metrics are zero are removed from the Report
• Keep Metrics on drill-down -When turned on, the metrics chosen on the Report will be the only metrics visible when you drill into the next level.
• Font Size - Change Font size
• GL Report - Used to produce GL P&L and Balance sheet format
• Row Header Layout - Tree or Standard, see below for more examples
• Show Column Grand Totals - On/Off
• Show Column Totals - On/Off
• Show Row Grand Totals - On/Off
• Show Row Totals - On/Off
• Expand All Columns - On/Off Default to Show all detail Columns or only display Summary results, see below for more examples
• Expand All Rows - On/Off Default to Show all detail Rows or Collapse and only display Summary results, see below for more examples
• Show Metrics in Rows - On/Off, see below for more examples

Row Header Layout

Select either Tree or Standard

Standard Layout - Displays each Group by dimension as a separate Column. Easy to see the data hierarchy.

Tree Layout - Displays each Group by dimension as indented. Best to use this option when using multiple Group by's or when Group by data names are long.

Expand All Columns

Multiple Column levels can be setup e.g. Group Months together into Quarters. Set the default view to show all columns Expanded or Collapsed. Then click on a heading to Expand or Collapse.

Expand All Rows

Multiple Row levels can be setup e.g. Group Item Type by  Item Sub-type. Set the default view to show all rows Expanded or Collapsed. Then click on the heading to Expand or Collapse.

Show Metrics in Rows 

When displaying more than one Metric e.g. Sales $ and Qty it is possible show these metrics on separate:

• Rows which displays the data across the page
• Columns which displays the data down the page

Show Metrics in Rows (On) - Each Metric appears on a separate row. This style is good to use when analysing data trends across the page, e.g. monthly sales trends. It also reduces the number of columns on display.

Show Metrics in Rows (Off) - Each Metric appears in a separate column. This style is good to use when analysing data trends down the page.

Headline Sales

Sales Dashboard built up of a number of elements (widgets) that can be fully customised.

This Dashboard includes the following elements (widgets):

• KPI widgets - showing highlight boxes for YTD and MTD Sales and Margins with comparison to the prior year or prior month
• Pivot report widget - sales and margins pivot to show Monthly Sales
• Report widget - comparison style report comparing last year to this year with variances, this report includes a horizontal bar chart format to also visually highlight the size of each number.
• Charts - Dashboards can also be created and customised to include different report formats including charts.

The Dashboard can now be fully customised using the Edit icon in the top right corner:

 

Add new Visualisation elements