Claims Automation – Spencer’s

• Note:
• Go the folder where report generator and raw data files are stored. Right Click on the report generator file –> Properties –> Unblock –> Ok.
• A one time privacy setting is to be done on any system before running the automation. Refer steps in this link.
• Also, ensure “Enable Content” button should be pressed, in case you get a security warning bar on opening the file as show in this link.

Before Stage 1 #

1. Keep the raw data files having the following sheet names in the respective customer folder

Rawdata files	Sheet Name	File Format
IAR file	Sheet1	xlsx
SOL file	Sheet1	xlsx
Claims & POS file	Claim	xlsx
	POS

2. Open the report generator file and update the folder path in “FolderPath” sheet

3. Update the date in the field below Folder path, Primary data will be considered from the date as specified in the field.

Stage 1 – Check Sheet Name #

• Click on “Check File” button. Exception Report – Table containing file in which sheet name is not as per requirement  will be generated.
• Click on “Check Sheet Name” button and update the sheet names in the source files as required in Point 1 (Before Stage1)

• Once the sheet name is updated in the source files, check if all the required columns exist in the files.
  Exception Report – Table containing file name, columns required will be generated.
• Click on “Check Column Name” button in “Sheet,Columns, Errors” sheet. Exception Report containing the list of required columns but not found in the source files will be generated. Update the column names in the source files.
• Once the sheet and column names are updated in the source files, check if #N/A errors exist in the source files.
• Click on “Check Errors” button in “Sheet,Columns, Errors” sheet. Exception Report – Table containing file name and number of rows with errors will be generated. Remove the errors from the source files.

Points to note #

• Rows containing errors will not be considered in report generation process
• Proceed to generate report only after the above exceptions are dealt with

Stage 2 – Check if MRP is found in IAR #

• Click on “Check MRP in IAR” button. Exception Report – MRP not found in IAR will be generated
• Click on “MRP not found in IAR” button and update “MRP as per HUL” column. The user can also update the IAR source file and re-perform the above step.

Points to note #

• Separator used for SOL codes is “&”, “,”, “sol – “, “sol -“. In case “,” separator occurs anywhere else in the text, it will generate incorrect report.
• For deriving basepack wise MRP, negative values have been filtered, it will be calculated for the period as contained in the IAR file.
• In case “MRP as per HUL” is not filled, it will be taken as “0”

Stage 3 – Check Promo p.u #

• Click on “Check promo p.u” button. Exception Report for % and Rs not found in “Promotion Name” will be generated.
• Click on “% promo p.u” button and update “Promo p.u from text” column.
• Click on “Rs – promo p.u” button and update “Promo p.u from text” column.

Points to note #

• We have extracted mrp from promotion name using “rs” as separator and In case of top ups, amount is fetched from promotion name using ” off” as separator
• For promo type – where the promotion name contains “%”, promo type is “%” else it is “Rs.”. They are mutually exclusive

Stage 4 – Budget at SOL level #

• Click on “Check Budget” button. Click on “SOL-Budget” to check total budget derived from “SOL” file.
• User can update the budget amount in “Total Budget” column for all the SOL codes and amount updated in “Total Budget” column will be used to calculate overrun.

Stage 5 – Generate Topsheet #

• Click on “Topsheet” button. Click on “Topsheet” to see the final claim summary generated.
• Click on “Claim Working” button in “Topsheet ” sheet to see the deductions made at transaction level.
• Click on “Overrun” to see the overrun deduction at sol level.

Points to note #

• For comparison of POS data with Claim file, basepack in pos file has been derived using article code. Assuming that one article code has one basepack
• SOL file – only those transactions with Promotion status as “amend” or “amendapproved” will be used for processing
• For marking transactions under “Visibility SOL Codes”, Promotion name should contain “di-vi”
• In case of one article code has multiple basepacks, POS & Primary deduction will generate incorrect results

Exception Reports, Interpretation and User action #

Sheet Name	Particulars	User Action
MRP not found in IAR	List of transactions where MRP is not found in IAR file	User has to update “MRP as per HUL” colmn in the sheet
%Promo Check	List of transactions where promo p.u derived using (NetClaim/MRP/Claim Qty) cannot be found in “Promotion name” column	User has to update “Promo p.u from text” colmn in the sheet
Check Promo p.u	List of transactions where promo p.u derived using (NetClaim/Claim Qty) cannot be found in “Promotion name” column	User has to update “Promo p.u from text” colmn in the sheet
Budget Amount from SOL	SOL Code level Budget amount derived from PPM file	User can change the budgeted amount, if required