Pedders iQ Training and User Guide

FRANCHISEE REPORTING

Module 4: Cognos Franchise Reports Description and Functionality

Version 1: Friday 15th October 2021

Table of Contents

Sales Reports

1a) Lost Sales Reports Contained here are several reports (in different formats with different available selections) that will show you quotes and estimates from Workshop iQ and Pedders iQ that are yet to be invoiced. A great set of reports to help you identify those quotes and estimates that you could turn into $$$.

1b) CUS.SALE02, SALE02 and SALE03 Reports These reports are your main “slice and dice” sales reports and act in a similar way to the Sales Analysis Report in Merlin Cobalt. Please note that the intention is to have CUS.SALE02 act as the primary report and as at 16th October 2021 this report is being developed further with a revised version due for release in November 2021. Below is a brief description of the 3 reports with a focus on the functionality and selections within CUS.SALE02 below.

    CUS.SALE02: This report is a custom-built report designed to be the “go to” sales report. 

    This report is not “live” and updates with previous day data at approximately 4am 

    (Melbourne Time) each day. Further details relating to this report below.  If any bugs or 

Issues are uncovered with this report please log a ticket with Pedders Support and attempt your report using SALE02.

SALE02: This is a standard Cognos Report and is “live”

SALE03: Similar to SALE02 but provides more detail

CUS.SALE02 Attributes

Data collected via attributes connected to Customer and Stock records etc within Pedders iQ/ Pronto Xi have been used as selections for you to “slice and dice” your reports within all reports in Cognos – with CUS.SALE02 being a great example of this with numerous selections available. TIP – By holding down the Ctrl key you can select multiple items to report on

Please note: A new CUS.SALE02 version (expected November 2021 release) will provide a “multi-selection” option where, for example, you will be able to report sales by customer and by product group within the same report.

“Brand”: All Pedders products have been allocated into brands such as “SportsRyder” and “TrakRyder” so that you can report on sales by brand. Note – “Unspecified” will be either Pedders (eg Pedders Black Shock) or PZ.

“Class”: Class is the top of the family tree for categorising our parts list. These are BIG buckets eg Suspension, Brakes etc. TIP: Class contains LABOUR.  But now with Labour in Class and Price Levels in Customer Type you can separate LABOUR by Retail vs Trade for example.

“Product Group”: Product Group a lower level of the parts family tree. For example, Shock Absorbers, Leaf Springs, U-Bolts

“Item Code”: Is simply part number. You will note the following feature with item code.

By typing a part number or series  string in the search bar, then hitting search it will produce a list to choose from in the results table on the left then by selecting from this results table (hold ctrl key down for multiple selections) and then hitting the insert button will report on the item code selection and give you the ability to “slice and dice” sales data just for one or item codes/part numbers – for example 9564 only….or you could do all the 95 series shocks etc.  

“Rep Code”: is the salesperson the either made the sale or is responsible for the customer

“Territory” (sometimes referred to as “Customer Territory”): This will be rarely used in a store environment but is where the debtor account is held which can be different sometimes to where the sale/invoice is made or where the parts are distributed from

“Marketing Flag”: Again not really relevant for stores but from a Head Office perspective we can report on sales by global location.

“Customer”: An obvious one – by customer code. You will note the following feature with customer.

By typing a letter or word string in the search bar, then hitting search it will produce a list to choose from in the results table on the left then by selecting from this results table (hold ctrl key down for multiple selections) and then hitting the insert button will report on the customer selection and give you the ability to “slice and dice” sales data just for one or multiple customers – for example one or all the MYCAR customers you service.  

“Customer Type”: This is a widely used parameter and is essentially PRICE LEVEL. This is where you would select to see Retail vs Trade for example.  TIP: If you are wanting to compare old-fashioned Trade vs Retail as it used to be aggregated in Merlin – then Retail is obtained by JUST the Retail Customer Type line and Trade is ALL customer type lines except retail.

“Warehouse Group”: Not relevant in stores (unless you also have a warehouse).  Groups sites together. E.g. Domestic Warehouses separate to Company Stores separate to Export.

“Warehouse”: This is where the parts are distributed from or the work is done which in a store will produce identical results to “Invoice Territory” below in most instances.

“Invoice, Sales Order or SO Territory”: This is the sales invoiced by the company and is the most similar to those sales that will be posted to your financials so would be the preferred definitive amount over using “Warehouse” or “Customer Territory”.  TIP: It is very possible that sales in your Cognos will be different to revenue in your profit and loss as some revenue in Cognos could be split to other income or other accounts within your ledger.

CUS.SALE02 Select Report Time Range

You also have the ability to “slice and dice” data with varying time range options. TIP: Remember the information contained in module 1 of this training information – any data in Cognos prior to your go live date will be inaccurate because some transactional data (especially PZ) was not extracted from Merlin and brought across to your Pedders iQ/Pronto Data Set.

“By Month”: Selecting this load another drop down where you choose the END month you want then the number of months PRIOR to this end month you want to report on. TIP: A great report time to use is 13 months from your current or just finished month so that you get comparison to last year and also trend over the previous 12 months.

“By Days”: Similar to By Month.  This will give you X number of days prior to yesterday (remembering that this CUS.SALE02 report is not live and only gives you up until yesterday data). Selecting “1” for days will give you yesterday only.

“Entered Dates”: This gives you the ability to be VERY specific with the day you want. Simply select the start date (By Financial Year, then month, then day) and then the end date (By Financial Year, then month, then day). In the below example Friday October 1, 2021, to Monday November 1, 2021, has been selected. TIP: This may be a time function that you will use a lot in the first year of using Pedders iQ/Pronto as you will be reporting sales from you GO LIVE date which will probably be in the middle of a month. Remember again – data in Cognos (e.g. sales) is not accurate before your go live date because not all transactional data (especially PZ) was brought across.

“Rolling 6 & 3 months”: Similar reports to above but shorter periods to compare and focus

“Current Month to Date”: A VERY well used time selection that will give you by day for the current month.

CUS.SALE02 Select Measure

This report provides the following measures to select from.

“Sales Value”: Sales EXCLUDING GST

“Sales Quantity”: Items Sold

“Gross Profit”: Gross Profit in $

“Gross Profit Percentage”: Gross Profit in %

CUS.SALE02 Finished Report and Quick Run Selections

1. To Run a Report from the Selection Screen hit the FINISH button at the bottom of the page

Once a report is finished it will appear in a grid format as per below

With CUS.SALE02 report there is a feature when the report is run that you have some Quick Run Selections where you can change the report and run quickly rather than having to go back to the main selection screen.  Simply change any of the selections up the top of this screen and hit APPLY CHANGES.  Please note if any bugs and errors occur when selecting from this report screen simply hit the Play arrow  at the top left of the screen and then select “Reset Prompts and Run” to go back to the main selections screen.

Please refer to Module 3 of Reporting Training for other reporting functionality including how to export this report to excel and pdf, how to print, how to email and how to schedule these reports to arrive in your email daily etc.

1c) Daily Sales Report: This custom report is similar to Merlin’s Branch Summary report and provides a view of current daily sales by transaction and shows labour separate. TIP: This report shows sales INCLUDING GST

1d) Retail – Store and Warehouse Daily Sales: This great visual custom report shows daily, week to date, month to date and calendar year to date sales for TOTAL, RETAIL, TRADE and LABOUR sales TIP : Remember due to data extraction from Merlin to Pedders iQ that didn’t include all transactions (especially PZ) that MTD and YTD will only be accurate when these are full Pronto periods.

Inventory Reports

2a) INV03 – Item Transactions: A great report for showing transactions of stocked items over a period by transaction type.

2b) INV04 – Valuation: A pivotal stock report showing your current stock. A summary view will provide you current stock by Product Group and a Detailed view will provide stock by Product Group and then by individual item code. Multiple cost options exist (Note PSAS uses Warehouse Average Cost as its stock cost type).  You will also note that ABC Class categorisation has been included. ABC Class is PSAS’s categorisation of parts nationally (including N for New) with A being fast moving product. TIP: This report does not provide time range options so is your current stock. Schedule to run this report at the end of each month (the night of the last day of the month) so that you have a record of your stock level at the end of each month.

2c) Item – by Class code-Bar chart:

 A visual representation of your current stock by major class

Finance Reports

3a) Payroll Reports: Various Pronto Payroll reports exist and are included to provide information on your payroll. Please take the time to go through each report and familiarise yourself with what each can provide.

3b) Accounts Receivable – Aged Trial Balance by Customer Territory: A standard report that provide you the ability to “slice and dice” and have very detailed analysis of your current debtors – in groups (eg all 05 wholesale customers) or by individual customer code

3c) AR02 and AR03: The main standard reports for debtors (Accounts Receivable). AR03 is a more detailed version (includes by transaction type) than AR02. Between these two reports and 3b above you have every possible way to report on your current and past debtors/AR situation.

3d) AP02 and AP03: The main standard reports for creditors (Accounts Payable). AP03 is a more detailed version (includes by transaction type) of AP02. These two reports provide you with every possible way to report on your current and past creditors/accounts payable.

The following reports relate to your general ledger. A standardised general ledger structure across the Pedders store group (based on the one PSAS uses) has been provided to you so that we can better provide financial benchmarking. For support and group benchmarking purposes it is requested that you maintain this standardised general ledger structure. For any GL structure changes or requests or queries please log a ticket withing the Pedders Support Portal that will be directed to Pedders Head Office Finance Team.

3e) GL02 Statement of Financial Position: This standard report is your Balance Sheet and provides you a summary or detailed view option, current and historical time periods and multiple columns to compare over differing periods eg current vs last month vs last year etc. TIP : By saying YES to the Display Ratios you will receive four industry standard ratios at the top of your balance sheet that provide a quick snapshot of your financial position.  Current Ratio, Quick Ratio, Cash Ratio and Working Capital. Google them to understand what they are!

3f) GL03 Statement of Financial Performance: This standard report is your Profit and Loss and provides you a summary or detailed view option, current and historical time periods and multiple columns to compare over different periods including the ability to report actuals against budgets (noting that Pronto has the ability to enable MULTIPLE budgets and forecasts to be loaded).

3g) GL04 Trial Balance: This standard report is your trial balance which is both your profit and loss and your balance sheet combined.  This report provides you with the option of reporting against your NATURAL ACCOUNT or GL ACCOUNT CODE and time range options from current to historic. Note – Z ACCOUNTS are those that need a home so it is good practice to report on your Z accounts only to see what needs to be correctly allocated.  TIP: With this report please make sure you select the actual period year, period month and select the company. 

3h) GL05 General Ledger Transactions: This is a highly detailed report for your general ledger/trial balance to enable you to report on general ledger transactions and detailed tracking within your ledger.

Purchasing Reports

PSAS uses SEVERAL purchasing and supplier related reports and analysis for its own purchasing activities and has provided these reports that are relevant at store level.

4a) Open PO Report by Selected Supplier: This is a custom report. Type in your supplier code in VALUE hit the right arrow and then select the supplier and finish the report. This will then provide you open purchase order information for that supplier.  You can report on all open purchase orders by simply finishing the report without any supplier selections. TIP: The next report (4b) is a better report format for open purchase orders

4b) Open PO Report by Selected Suppliers or ALL: This custom report provides you a list of all of your suppliers to choose from and also an order status. For example below is all backorders from supplier PEDDERS for a store.

4c) PO02 – Purchase Orders: This standard report provides various selection criteria for your to really “slice and dice” your purchase orders.