To create your own customisable Workbook:
- Run the Analyse Sales & Margins [base] which is a view only Workbook
- Create a Copy and Save the new Workbook, this will now be fully customisable
- Read the article Analyse Sales & Margins - Quick Start first to become familiar with the basics
This Workbook runs over Sales Invoices and includes multiple Tabs which allow you to instantly understand your sales trends and margins. Run across any date range, Identify best sellers and investigate issues.
Each Tab showcases a different report or chart style. Select the Tab that best matches the report style you want then simply 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 Tab
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.
The Customer Tab can now be fully customised using the Edit and Settings icons.
Edit Report - Pencil Icon
Click on the Pencil icon to access advanced report Edit features including:
Columns
- Change column order using Drag & Drop
- Remove columns by clicking the red x
- Change column name by double clicking on the field name then edit it
- Change row data by selecting other Metrics or Fields from the available list e.g. Select Item Number and Item Name instead of Customer Name to see Sales by Item
- Metrics - The Metrics Table is always the first table 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
- Select any Fields from the available list to add a Group by Subtotal e.g. Move Customer Name to Group by
- Report now shows a list of Items sold to each Customer
- Add a second Group by e.g. Location and indent the Customer Name to create a sub-group. This will first Group by the Location then show all Customers under that Location.
Row Formatting
Control every aspect of each of the Metrics columns individually. Hover over a Column field to display Formatting pop up:
- Format - 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
- Visible - On/Off - Set to Off to Hide this column. Column can still be used to Sort by or in Calculations
- 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 - Hamburger Icon
Click on the Settings "hamburger" icon to access the Report Settings popup menu.
- Aggregate -This option is by default set to On for all but Detail Level Reports
- 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.
The Monthly Sales Tab can now be fully customised using the Edit and Settings icons.
Edit Report - Pencil Icon
Click on the Pencil icon to access advanced report Edit features. Note the layout for a Pivot type report is different from the List type report (above).
Rows
- Change row data by selecting other Fields from the available list e.g. Select Item Number and Item Name instead of Customer Name
- Change feild name by double clicking on the field name then edit it
- 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
- 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