Software Efficiencies Training DST Training Conference Nashville, TN, -April 21st and 22nd Trainer: Jeff Dobyns
Software Efficiencies • Interfacing Your Data with Excel • Upgrades U2005.1, U2005.2, U2005.3, U2006.1 • Shortcuts • FAQ’s • Q & A • Evaluations
Interfacing data with Excel • Why do it? • System Requirements • Creating the Reports • Finding the Files • Excel Import Wizard • Working with the Data • Save-As for Future Reference • Hands-on (common reports that really pay off to FT)
Interfacing Data with Excel • Why you want to take advantage of this functionality • Integration Possibilities (EX: mail merge Corp/Vendor pricing programs -EVAP05 , SPS06 & M-5.9 , M-5.33), LPC multipliers • Saves Paper/Printer/Piece of Mind • Reporting Flexibility • Change sorts • Add calculations • Sample Report
Interfacing Data with Excel • System Requirements • Connectivity to Network/Telnet • Setting Up the Folder on the PC • Report Requirements
Interfacing Data with Excel • AccuTerm 2K2* • Must run on PC • PC must have folder on C:\ drive named <<PICK>> • EX: C:\PICK System Requirements
Interfacing Data with Excel • Creating the PICK folder • Right-click my computer • Click explore • Click and expand the C:\ drive • Right-click on the contents of the C:\ drive and then click NEW, then New Folder • Name the Folder “PICK”
Interfacing Data with Excel • Creating the Reports • What gets created • Where to find it • C:\PICK\FT.TXT
Interfacing Data with Excel • Instead of selecting printer or taking the default - use ‘FT’
Interfacing Data with Excel • Sample Transfer
FT- Find the file • Browse to your PICK folder on the C:/drive and find the file ‘FT.TXT’ • Open in notepad, word or excel. • May need to use import wizards built into programs.
Interfacing Data with Excel • Finding the Files • What to do with it • Notepad • Word • Excel
Interfacing Data with Excel • Excel Import Wizard
IDwE –Sample Import • Excel
Interfacing Data with Excel • Working with the data • Save-As for future reference • Sorting the Data • Calculations
Interfacing Data with Excel • Hands On (some common reports to FT) • M-11.17 Customer Average Sales Per Invoice • M-11.10 List of items sold for a customer (by vendor) • M-12.2.23 Customer list • M-184.108.40.206 Mailing List • M-12.3.22 Product Pad • M-19.7a or 7B Inquiry by Product or Customer (hardcopy option) • M-12.2.42 Mail Merge
Interfacing Data with Excel • Menu 20.29 –Download Hold Entries (FT)
Interfacing Data with Excel • Mail Merge Doc/List pricing notification • M-12.2.42 List Price Multiplier Report • FT’s cust#, name, address, city, state, zip phone, fax and multiplier • Sample Letters to review • Letter 1 Letter 2 Letter 3 • Merging with letters • Review Mail Merge Doc • http://dstdocs.sitemax.com/utilities.asp
Break Time! 15 min break
Upgrades • U2005.1, .2, .3 • U2006.1 • Upgrade Docs • http://dst.sitemax.com/upgrades.asp • Shortcuts
Upgrades U2005.1 • U2005.1 • Open quote notification • AR • Invoice totals by operators • File Maintenance • EOD Reports • Inquiries • Parameters
Open Quote Notification –U2005.1 • There is now a way in which counter or sales people will be alerted when an item has been quoted to a customer and put into the Database system. • When accessing a customer account, if that item has been entered in as a quote, a message saying, "This item is still open on quote number (8 digit number)" will appear at the bottom of the screen. • At this point, two things can occur. You can back out of the order, look up that quote, convert that quote to an order and proceed, or you can continue in order entry with the order. However, if you do not go back afterwards and cancel the quote, every time that one part number is entered in that specific account, the message will come up telling you that it is still on a quote. • **Refer to http://dstdocs.sitemax.com/param_35_9.asp for instructions on turning this message off.
Accounts Receivable –U2005.1 • M-4.2.39 • The purpose of this new menu path is to all users access to AR batches from pervious periods (within the last 120 days). • Option for summary or detailed data. Summary data will produce output limited to the batch number, the batch total and the write off amounts: If you choose detailed output, the batch number, the invoice number, the customer, number, the customer name, the reference number, and the posted amount will display. This is best viewed on the printer instead of the screen. If you choose screen display, CTRL-X will terminate the report.
Invoicing –U2005.1 • Menu 11.18 Invoice Totals by Operator • This menu displays invoice totals by operator or salesman#, by company and selectable by beginning and ending period.
File Maintenance –U2005.1 • 220.127.116.11 Product Detail Maintenance • To allow your system to accept the Corp transmitted information for either the Serialized Product or the Taxable Flag, set the parameter to "Y" on m-35.6 screen 2, Fields 15 and 16. Go to menu • 35.6 - Parameter File Maintenance to view the complete instructions.
File Maintenance –U2005.1 • 18.104.22.168 Flagging of Customers that do not want prices printed on Pick packs or Invoices • Use Menu 22.214.171.124, field 16 if you have customers that prefer not to have pricing on their pick packs or invoices. Enter a 'P' in field 16 if you do not want pricing to appear on a customer pick packs. Enter 'I' if you do not want prices to print on invoices. Enter 'B' if you do not want prices to print on pick packs or invoices.
EOD Reports –U2005.1 • 16.5 EOD Report X-ref New EOD report #154 "UNFINALIZED ORDERS - REINVOICED AT A DIFFERENT TOTAL" • This new report not only shows you the invoices that were unfinalized the day before, but also if they were re-invoiced with a different dollar amount. • Each release is separated by the line (***************************)The first time the release is shown is the re-finalized release.The second time the release is shown is the originally finalized release.As you can see on this example, after the first release was unfinalized, several items were added to it before it was re-finalized.
Inquiries –U2005.1 • 19.6 View Quotes from Stock Status • Able to view quotes from Menu 19.6 Stock Status Input the part number in Stock Status…Input 'Q' at the prompt and a list of all open quotes for that part will appear.
Parameters –U2005.1 • 35.6 Parameter File Maintenance (screen 2) • New Parameter to control new Corp PTRANS Fields: Serialized Product (m-126.96.36.199 Field 14. SERIALIZED INV) &Taxable flag (m- 188.8.131.52 Field 17. TAX FLAG) • 15. Update serialized inventory flag from Corp price trans and 16. Update taxable product flag from Corp price trans. • If you want to accept Corp.'s transmission, enter a Y. If you want to ignore Corp's transmission, enter an N (or leave blank.)
Parameters –U2005.1 • 35.10 Automatic Monthly FTP of Complete Mailing List • This will automatically transmit your mailing list to corp. Begin by setting your parameters in menu 35.10. Set Field 7 to “Y” and in Field 8 enter the day of the month you want the mailer to transmit. The mailer will automatically transmit to corp during EOD processes the night after the day entered. Keep in mind that you still need to meet corps deadlines for accepting the mailing list. The date entered needs allow for weekend/holidays situations. DST recommends the 20th.
Parameters –U2005.1 • Menu 35.19.8 Event Notification of customer back orders or special orders to purchasing agent • This program will notify the person you choose (purchasing agent) to receive an email notification of any items back ordered for your customer from anyone taking a customer’s order. The notification is immediate so the purchasing agent can react immediately to fill the order. • Begin by going to Menu 35.19.8. Enter the company store number (i.e. 01, 02 or 03) Select the line number and enter the email address of the person you would like notified. You can have multiple recipients. An email will be sent in the following format.
Upgrades U2005.2 • U2005.2 • Order Entry/Invoicing • Accounts Receivable • Inventory Control • Vendor Sales Analysis • File maintenance • EOD/EOM • Inquiries • Lock Maintenance • Parameters
Order Entry/Invoicing –U2005.2 Menu 1 - Order Entry & Invoicing • Menu 1.2 - Order Entry - New Prompt for MSDS sheets. • Menu 1.2 - Order Entry - New Standard Pack (SP) display. • Menu 1.2 - Order Entry - Collecting money from customers paying at the counter for prior days invoices. • 1.36 - Email Invoice (No Linux Server) - Will now be able to email invoices to customers, for stores currently not on Signature Capture. Enter release number or invoice number (11-digit). Enter an email address to send the invoice to or return for the customer's email addressed listed 184.108.40.206, field 17. Sample invoice
Accounts Receivable –U2005.2 Menu 4 - Accounts Receivable • Menu 4.1.2 - A/R Adjustment Entry - Trans Type field can no longer be adjusted • Menu 4.1.3 - AR Posting - alert added for a journal entry to accompany any Misc. AR posting • Menu 4.1.9 - Credit Manager Inquiry - Aging Buckets - Users can chose whether their system ages based on invoice date, due date or as is (from end of month). This will be parameter driven and will affect the aging buckets in 4.1.9 and aging reports. • Menu 4.1.9 - Credit Manager Inquiry - Additional fields have been added to the header. Signature Capture users now have the ability to send a special message when emailing an invoice. • Menu 4.1.10 - This is a non-functional menu path and has been deleted • Menu 4.1.13 - Search AR History by Check # - Listings of paid invoices can be viewed by check for a particular customer. • Menu 4.3.2 - This is a non-functional menu path and has been deleted • Menu 4.4.4 - Dunning Letter Form Print - invoice detail is now attached
Inventory Control –U2005.2 Menu 8 - Inventory Control • Menu 8.4A - Inventory Adjustment Value Report - This report has been formatted for FT to be imported into Excel. • Also now an EOM report which lists all the stock adjustments made for a month. EOM.XREF 01-126
Vendor Sales Analysis –U2005.2 Menu 11 - Sales Analysis • Menu 11.4.20 - Vendor Sales Inquiry • Menu 11.4.21 - Combine Product Sales Inquiry
File Maintenance –U2005.2 • Menu 12 - File Maintenance • Menu 12.10 - Port Control - If there is a printer number in the LABEL column, all the bar code label print programs will now use this printer number instead of the default printer by company in Menu 34.9. • Menu 12.1.18 - ROP Password Maintenance - Maintain ROP password for Menu 27.5, field 5 and Menu 220.127.116.11, field 3. • Menu 12.2.11 - Terms Maintenance • Menu 12.2.13 - Ship Code Maintenance - New version will prompt to update COMPANY record Will Call Ship Method, if the Ship Type is '1' (counter). Will also prompt for the specific company number to update. • Menu 18.104.22.168 - Product Detail Maintenance - Re-order points are now password protected. • Menu 12.3.35 - List of Possible Duplicate Items- Replaces the Galaxy report on your system. This report will list "potential" duplicate items based on the vendor description.
EOD/EOM –U2005.2 • Month-End • Month-End - Composite Sales Tax Jurisdiction Report • EOD/EOM Cash Reconciliation Report • sample
Inquiries –U2005.2 • Menu 19.6 - Stock Status & Menu 19.14.10 Vendor Buying Information - Vendor website link (VL) shortcut from stock status • Menu 19.6 - Stock Status - Freight Terms now appears in Vendor Buying Information Inquiry. • Menu 19.6 - Stock Status - "D" now appears next to Order Qty to indicate Dropships
Lock Maintenance –U2005.2 • Menu 20.2 - Unlock a locked receiver from Menu 34.1 Purchase Order Receiving.Now, when a receiver becomes locked, use m-20.2 to unlock. Only use m-20.2 if you know that this receiver is not open on another terminal and has not been updated through menu 34.2. Go to menu 19.7b to confirm that items from the receiver have not been updated.
Parameters –U2005.2 Menu 35 - Parameter • Menu 35.3, Field 5 - Clear 100 Lot on B92, B93, B94,B95, L96, L97, N98 • Menu 35.6, Screen 2, Field 18 - Parameter File Maintenance - Users can chose to have their system age based on invoice date, due date or from end of month. • Menu 35.6, Screen 2, Field 20 - Menu 4.1.9 1)<Enter> = Next Page 2) <Enter> = Totals
Upgrades U2005.3 • U2005.3 • Order Entry/Invoicing/OE Reports • Accounting Registers • AR • Price Maintenance • Inquiries • Utilities • Parameters
OE/Inv./OE Rpts –U2005.3 • When an item is assigned to multiple warehouse locations, all locations will print on the Pick Ticket. This feature (only) of the upgrade will need to be manually installed for your store. • The Customer Name character space has been extended in the Customer Lookup Screen. • When printing a laser quote, the disclaimer "This quote will be honored for XX days from date shown above" now prints on the bottom of the quote. • Customer certification information is now printing on invoices that list a product requiring certification.
OE/Inv./OE Rpts –U2005.3 • Menu 2.23 Open Quotes by Customer - There is now the option to select a printer in menu 2.23, this allows for greater flexibility in printing this report. • Menu 2.27 - Order Entry Audit - When a credit override is authorized the system will now capture the name of the person who is making the override.
Accounting Registers –U2005.3 • Menu 3.1 - Daily Invoice Register - A date prompt has been added to select a range of invoices based on date.
AR –U2005.3 • Menu 4.1.9 - Credit Manager Inquiry • When using the 'L' Lookup option in 4.1.9, the Lookup screen now displays the AR balance in the last column. • AD -Users can now get to the AR Adjustment Menu (4.1.2) from within Menu 4.1.9 by typing in 'AD' at the last screen. • Menu 4.4.2 - Dunning Letter Edit Report - An additional column has been added to the report that shows customer ranking as either A, B, or C as an additional tool to use in assigning Dunning letters.