1 / 64

USING SCRIPTS FOR A BIO-DIESEL AUDIT

USING SCRIPTS FOR A BIO-DIESEL AUDIT. CHARLES R GAUNTT ACL USERS GROUP FRIDAY, DECEMBER 12, 2008. BASIC OUTLINE. WHY WE DID THE AUDIT - RENEWABLE FUELS REGULATIONS QUICK AND DIRTY DEVELOPMENT USING SCRIPT RECORDER STREAMLINE LOGIC YEAR 2 AFTER SCRIPTS CLASS

zinna
Download Presentation

USING SCRIPTS FOR A BIO-DIESEL AUDIT

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. USING SCRIPTS FOR A BIO-DIESEL AUDIT CHARLES R GAUNTT ACL USERS GROUP FRIDAY, DECEMBER 12, 2008 BIO-DIESEL SCRIPTS

  2. BASIC OUTLINE • WHY WE DID THE AUDIT - RENEWABLE FUELS REGULATIONS • QUICK AND DIRTY DEVELOPMENT USING SCRIPT RECORDER • STREAMLINE LOGIC YEAR 2 AFTER SCRIPTS CLASS • ADVANCED FEATURES WE ARE STUMBLING THROUGH NOW BIO-DIESEL SCRIPTS

  3. RENEWABLE FUELS I WORK FOR SOUTHERN STATES BECAUSE I DO NOT KNOW WHAT AGRICULTURE, THE FASB, THE STATE, THE COOP, OR THE FEDERAL GOVERNMENT WILL DO TO ME NEXT. IN THIS CASE THE FEDERAL GOVERNMENT GOT ME. BIO-DIESEL SCRIPTS

  4. The United States enacted legislation to promote and control the generation of renewable fuels by Energy Companies. Companies that produce or sell Renewable fuels must conduct an audit and file it with the EPA. Internal Audit Departments may conduct these audits. BIO-DIESEL SCRIPTS

  5. Southern States Petroleum Divisions buys a 100% Soybean Based Diesel additive from local energy companies located in Maryland and Virginia. The additive is mixed with Diesel fuel to create a 1%, 5% or 20% mixture for Trucks, Tractors, and Homes. A RIN is assigned to Southern States when we buy the additive. We must report the number of RIN’s bought and then blended on a quarterly basis to the EPA. BIO-DIESEL SCRIPTS

  6. BIO-DIESEL SCRIPTS

  7. WHY USE ACL? • WE ARE A DOWNSTREAM RETAIL BLENDER OF BIODIESEL RENEWABLE FUELS. • WE MUST PROVE WE ARE A DOWNSTREAM BLENDER THAT DOES NOT SELL 100% PRODUCT. • WE USE ACL AUDIT SOFTWARE TO DEMONSTRATE WE ONLY SELL BLENDS LESS THAN 100%. • WE USE AUDIT SOFTWARE TO DEMONSTRATE WE DO NOT SELL BLENDS OF 100%. BIO-DIESEL SCRIPTS

  8. DESIGN ASPECTS • USED A DATA WAREHOUSE RETAIL SALES FILE. • PIGGY BACKED ON SYSTEMS DEVELOPED BY TAX DEPARTMENT TO FILE FOR FEDERAL TAX REBATES. • CREATED THE DEMAND FOR A RETAIL RECEIVING DATA WAREHOUSE. BIO-DIESEL SCRIPTS

  9. FIRST SHOT AT THE PROBLEM • PROCESSED DATA FOR SEPTEMBER THROUGH DECEMBER. • USED SEPTEMBER TO DEVELOP PILOT SCRIPTS. • REPLICATED FOR REMAINING QUARTERS. • USED THE SCRIPT RECORDER FOR QUICK AND DIRTY DEVELOPMENT • FOUND OUT EDIT TABLE COMMANDS ARE NOT CAPTURED ON SCRIPTS BIO-DIESEL SCRIPTS

  10. Use Script Recorder for Quick Development BIO-DIESEL SCRIPTS

  11. OPEN THE SEPTEMBER FILE BIO-DIESEL SCRIPTS

  12. USE EDIT TABLE COMMAND TO CREATE AN EXPRESSION TO CLASSIFY SKU’S AS BIODIESEL. BIO-DIESEL SCRIPTS

  13. USE DUPLICATE CONDITION AND EDIT CONDITION TO TEST FOR MULTIPLE BIO SKUS BIO-DIESEL SCRIPTS

  14. ADDED BIO MARKER EXPRESSION AS COLUMN ON DATA FILE BIO-DIESEL SCRIPTS

  15. TURN OFF SCRIPT RECORDER BIO-DIESEL SCRIPTS

  16. CREATE SEPTEMBER PILOT SCRIPT BIO-DIESEL SCRIPTS

  17. REVIEWED SEPTEMBER SCRIPT I FOUND OUT THAT NONE OF THE FILTER COMMANDS EXECUTED IN EDIT TABLE LAYOUT WERE CAPTURED IN THE SEPTEMBER SCRIPT. THE SCRIPT HAD TO BE REDESIGNED USING THE SET FILTER ON COMMAND. BIO-DIESEL SCRIPTS

  18. I CREATED A FILTER CALLED BIO SKU BIO-DIESEL SCRIPTS

  19. EXTRACT DATA COMMAND AFTER SETTING THE SCRIPT RECORDER ON AND OPENING SEPTEMBER AGAIN, I EXECUTED THE EXTRACT DATA COMMAND. BIO-DIESEL SCRIPTS

  20. I INSERTED A FILTER IN THE IF PARAMETER OF THE EXTRACT COMMAND BIO-DIESEL SCRIPTS

  21. USING BIO_SKU BIO-DIESEL SCRIPTS

  22. THE RESULTING FILE LOOKS REASONABLE. BIO-DIESEL SCRIPTS

  23. I TURNED SCRIPT RECORDER OFF AND LOOKED AT THE RESULTING SCRIPT. I FOUND FOR THE COMMAND TO WORK FOR EACH MONTHLY FILE, THE BIO_SKU FILTER WOULD NEED TO BE DEFINED IN THE OCTOBER, NOVEMBER, AND DECEMBER FILES. THEREFORE I TOOK THE EASY WAY OUT AND COPIED THE FILTER DIRECTLY INTO THE SCRIPT COMMAND LINE. BIO-DIESEL SCRIPTS

  24. I RAN SEPTEMBER_SCRIPT2 BIO-DIESEL SCRIPTS

  25. AND THE RESULTING FILE APPEARED REASONABLE, TO CONTAIN ONLY BIO DIESEL SKUS BIO-DIESEL SCRIPTS

  26. AGAIN I TURNED SCRIPT RECORDER ON AND THEN BIO-DIESEL SCRIPTS

  27. I NOW START TO CAPTURE A CROSSTAB COMMAND THAT WILL ANALYZE STORE SALES OF THE BIO-DIESEL SKUS BIO-DIESEL SCRIPTS

  28. THE RESULTS ARE STORED IN A FILE SO THEY CAN BE EXPORTED TO EXCEL FOR FURTHER REVIEW. BIO-DIESEL SCRIPTS

  29. THE FILE LOOKS GOOD, THE STORES AND SKUS ARE REASONABLE. BIO-DIESEL SCRIPTS

  30. SO WE PROCEED TO EXPORT TO EXCEL. BIO-DIESEL SCRIPTS

  31. A FULL VIEW IS EXPORTED TO EXCEL. BIO-DIESEL SCRIPTS

  32. THE STANDARD OUTPUT BIO-DIESEL SCRIPTS

  33. HERE IS THE RESULTING SPREADSHEET. NOTE THE TOTALS BY STORE THAT COULD BE USED FOR REPORT PREPARATION. ALSO, THE 65254300 SKU HAD NO SALES, WHICH MEANS SSC IS NOT A DISTRIBUTOR, BUT JUST A DOWNSTREAM BLENDER. BIO-DIESEL SCRIPTS

  34. THE EXPORT COMMAND IS CAPTURED BY SCRIPT RECORDER IN SCRIPT EXPORTCOMMAND WHICH IS THEN COPIED INTO SCRIPT SEPTEMBER_SCRIPT2. BIO-DIESEL SCRIPTS

  35. HERE IS THE COMPLETE SCRIPT FOR SEPTEMBER BIO-DIESEL SCRIPTS

  36. HERE ARE THE SCRIPT COMMANDS IN ENGLISH • OPEN rsssept07 • EXTRACT RECORD IF sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" TO "SEPTEMBERBIODIESEL" OPEN • CROSSTAB ON store_num COLUMNS sku SUBTOTAL trans_amt qty COUNT TO "SEPTEMBERBIODIESELCROSSTAB.FIL" OPEN • OPEN "SEPTEMBERBIODIESELCROSSTAB" • EXPORT FIELDS store_num AS 'store_num' trans_amt_65246540 AS 'trans_amt sku 65246540' qty_65246540 AS 'qty sku 65246540' Count_65246540 AS 'Count sku 65246540' trans_amt_65246550 AS 'trans_amt sku 65246550' qty_65246550 AS 'qty sku 65246550' Count_65246550 AS 'Count sku 65246550' trans_amt_65254600 AS 'trans_amt sku 65254600' qty_65254600 AS 'qty sku 65254600' Count_65254600 AS 'Count sku 65254600' EXCEL TO "SEPTEMBERBIODIESELCROSSTAB" BIO-DIESEL SCRIPTS

  37. THE SEPTEMBER SCRIPT WAS USED AS A FOUNDATION FOR A QUARTERLY SCRIPT BIO-DIESEL SCRIPTS

  38. REPLACE COMMANDS ARE USED TO EDIT THE MONTH. BIO-DIESEL SCRIPTS

  39. CREATING AN OCTOBER SCRIPT. THE OCTOBER SCRIPT IS COPIED AND MODIFIED TWICE TO CREATE A QUARTERLY SCRIPT. BIO-DIESEL SCRIPTS

  40. FIND AND REPLACE BIO-DIESEL SCRIPTS

  41. ARE USED TO CONSTRUCT A QUARTERLY SCRIPT BIO-DIESEL SCRIPTS

  42. THE QUARTERLY SCRIPT WAS EXECUTED TO ANALYZE 2007 BIODIESEL REPORTING. BIO-DIESEL SCRIPTS

  43. USING THE RUN SCRIPT COMMAND BIO-DIESEL SCRIPTS

  44. BIO-DIESEL SCRIPTS

  45. OCTOBER LOG • Log File C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\salesrss2008_LOG Opened at 15:35:28 on 12/08/2008 • @ DO QUARTERLY_SCRIPT • 15:35:55 - 12/08/2008 • @ OPEN rssoct07 • 15:35:55 - 12/08/2008 • 12 fields activated • Opening file name rssoct07.fil as supplied in the table layout. • @ EXTRACT RECORD IF sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" TO "octOBERBIODIESEL" OPEN • 15:35:55 - 12/08/2008 • 89 of 1083931 met the test: sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" • 89 records produced • Extraction to table C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\octOBERBIODIESEL.FIL is complete • Opening file "octOBERBIODIESEL" • @ OPEN "octOBERBIODIESEL" • 15:35:57 - 12/08/2008 • 12 fields activated • Opening file name octOBERBIODIESEL.FIL as supplied in the table layout. • @ CROSSTAB ON store_num COLUMNS sku SUBTOTAL trans_amt qty COUNT TO "octOBERBIODIESELCROSSTAB.FIL" OPEN • 15:35:57 - 12/08/2008 • 10 records produced • Output to C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\octOBERBIODIESELCROSSTAB.FIL is done • Opening file "octOBERBIODIESELCROSSTAB" • @ OPEN "octOBERBIODIESELCROSSTAB" • 15:35:57 - 12/08/2008 • 13 fields activated • Opening file name octOBERBIODIESELCROSSTAB.FIL as supplied in the table layout. • @ OPEN "octOBERBIODIESELCROSSTAB" • 15:35:57 - 12/08/2008 • 13 fields activated • Opening file name octOBERBIODIESELCROSSTAB.FIL as supplied in the table layout. • @ EXPORT FIELDS store_num AS 'store_num' trans_amt_65246540 AS 'trans_amt sku 65246540' qty_65246540 AS 'qty sku 65246540' Count_65246540 AS 'Count sku 65246540' trans_amt_65246550 AS 'trans_amt sku 65246550' qty_65246550 AS 'qty sku 65246550' Count_65246550 AS 'Count sku 65246550' trans_amt_65254600 AS 'trans_amt sku 65254600' qty_65254600 AS 'qty sku 65254600' Count_65254600 AS 'Count sku 65254600' EXCEL TO "octOBERBIODIESELCROSSTAB" • 15:35:57 - 12/08/2008 • 10 records produced • Output to C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\octOBERBIODIESELCROSSTAB.XLS is done BIO-DIESEL SCRIPTS

  46. NOVEMBER LOG • @ OPEN rssNOV07 • 15:35:57 - 12/08/2008 • 12 fields activated • Opening file name rssnov07.fil as supplied in the table layout. • @ EXTRACT RECORD IF sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" TO "NOVEMBERBIODIESEL" OPEN • 15:35:57 - 12/08/2008 • 68 of 1015988 met the test: sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" • 68 records produced • Extraction to table C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\NOVEMBERBIODIESEL.FIL is complete • Opening file "NOVEMBERBIODIESEL" • @ OPEN "NOVEMBERBIODIESEL" • 15:35:59 - 12/08/2008 • 12 fields activated • Opening file name NOVEMBERBIODIESEL.FIL as supplied in the table layout. • @ CROSSTAB ON store_num COLUMNS sku SUBTOTAL trans_amt qty COUNT TO "NOVEMBERBIODIESELCROSSTAB.FIL" OPEN • 15:35:59 - 12/08/2008 • 6 records produced • Output to C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\NOVEMBERBIODIESELCROSSTAB.FIL is done • Opening file "NOVEMBERBIODIESELCROSSTAB" • @ OPEN "NOVEMBERBIODIESELCROSSTAB" • 15:35:59 - 12/08/2008 • 13 fields activated • Opening file name NOVEMBERBIODIESELCROSSTAB.FIL as supplied in the table layout. • @ OPEN "NOVEMBERBIODIESELCROSSTAB" • 15:35:59 - 12/08/2008 • 13 fields activated • Opening file name NOVEMBERBIODIESELCROSSTAB.FIL as supplied in the table layout. • @ EXPORT FIELDS store_num AS 'store_num' trans_amt_65246540 AS 'trans_amt sku 65246540' qty_65246540 AS 'qty sku 65246540' Count_65246540 AS 'Count sku 65246540' trans_amt_65246550 AS 'trans_amt sku 65246550' qty_65246550 AS 'qty sku 65246550' Count_65246550 AS 'Count sku 65246550' trans_amt_65254600 AS 'trans_amt sku 65254600' qty_65254600 AS 'qty sku 65254600' Count_65254600 AS 'Count sku 65254600' EXCEL TO "NOVEMBERBIODIESELCROSSTAB" • 15:35:59 - 12/08/2008 • 6 records produced • Output to C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\NOVEMBERBIODIESELCROSSTAB.XLS is done • @ OPEN rssDEC07 • 15:36:01 - 12/08/2008 • 12 fields activated BIO-DIESEL SCRIPTS

  47. DECEMBER LOG • Opening file name rssdec07.fil as supplied in the table layout. • @ EXTRACT RECORD IF sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" TO "DECEMBERBIODIESEL" OPEN • 15:36:01 - 12/08/2008 • 63 of 988687 met the test: sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" • 63 records produced • Extraction to table C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\DECEMBERBIODIESEL.FIL is complete • Opening file "DECEMBERBIODIESEL" • @ OPEN "DECEMBERBIODIESEL" • 15:36:04 - 12/08/2008 • 12 fields activated • Opening file name DECEMBERBIODIESEL.FIL as supplied in the table layout. • @ CROSSTAB ON store_num COLUMNS sku SUBTOTAL trans_amt qty COUNT TO "DECEMBERBIODIESELCROSSTAB.FIL" OPEN • 15:36:04 - 12/08/2008 • 7 records produced • Output to C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\DECEMBERBIODIESELCROSSTAB.FIL is done • Opening file "DECEMBERBIODIESELCROSSTAB" • @ OPEN "DECEMBERBIODIESELCROSSTAB" • 15:36:04 - 12/08/2008 • 13 fields activated • Opening file name DECEMBERBIODIESELCROSSTAB.FIL as supplied in the table layout. • @ OPEN "DECEMBERBIODIESELCROSSTAB" • 15:36:04 - 12/08/2008 • 13 fields activated • Opening file name DECEMBERBIODIESELCROSSTAB.FIL as supplied in the table layout. • @ EXPORT FIELDS store_num AS 'store_num' trans_amt_65246540 AS 'trans_amt sku 65246540' qty_65246540 AS 'qty sku 65246540' Count_65246540 AS 'Count sku 65246540' trans_amt_65246550 AS 'trans_amt sku 65246550' qty_65246550 AS 'qty sku 65246550' Count_65246550 AS 'Count sku 65246550' trans_amt_65254600 AS 'trans_amt sku 65254600' qty_65254600 AS 'qty sku 65254600' Count_65254600 AS 'Count sku 65254600' EXCEL TO "DECEMBERBIODIESELCROSSTAB" • 15:36:04 - 12/08/2008 • 7 records produced • Output to C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\DECEMBERBIODIESELCROSSTAB.XLS is done • Execution of QUARTERLY_SCRIPT completed BIO-DIESEL SCRIPTS

  48. AS YOU CAN SEE OCTOBER, NOVEMBER, AND DECEMBER BIO-DIESEL FILES EXIST BIO-DIESEL SCRIPTS

  49. OF COURSE 2008 BRINGS A NEW YEAR. ARMED WITH THE 2007 RESULTS, AN ACL SCRIPT CLASS, AND SEVERAL ACL USER GROUP MEETINGS I TRIED TO IMPROVE THE ACL SCRIPTS. BIO-DIESEL SCRIPTS

  50. YEAR 2 IMPROVEMENTS • MATCH COMMAND • SET OFF COMMAND • COMMENT COMMAND • SUMMARIZE COMMAND • MODULAR SUBPROGRAM SCRIPTS BIO-DIESEL SCRIPTS

More Related