Advanced Accounting Database Training – Nashville/2006
Who am I? Database User Ten Years Johnstone Supply Santa Ana/Laguna Hills Ten Years Database Consultant Three Years Conference Instructor Four Years Nashville, April 2006 Kathleen Graw Undergrad B. A. Texas Tech University Grad School MBA w/Acctg Specialty Cal State University Adv. Accounting, Kathleen Graw
Ground Rules ASK Questions Come & Go As you Please Keep ? ’s Relevant To all No Name Calling NO ? Is stupid Do. Not Do Run with Scissors Nashville, April 2006 Simple Rules Adv. Accounting, Kathleen Graw
Ground Rules Cont’d MY WAY Nashville, April 2006 Isn’t The ONLY Way Adv. Accounting, Kathleen Graw
Topics Nashville, April 2006 1 Quick Tips & Tricks 2 Accounts Payable 3 Average Cost & Balancing Inventory 4 Warranty 5 General Ledger Adv. Accounting, Kathleen Graw
Topics Nashville, April 2006 6 Aging 7 End of Year Close 8 Miscellaneous Accounting Functions Adv. Accounting, Kathleen Graw
First Topic 1 Nashville, April 2006 Quick Tips & Tricks – Ways to make your job easier! Adv. Accounting, Kathleen Graw
Favorite Menu Paths 4. 1. 9 13. 5 19. 7 b Nashville, April 2006 Adv. Accounting, Kathleen Graw
File Transfer ft Create a folder on C drive” pick Enter “ft” at any printer select prompt Will be called “ft. txt” in C: \pick Text file or convert to Excel format Nashville, April 2006 Adv. Accounting, Kathleen Graw
Accuterm Settings If users available – multiple ports Turn on Scroll Bar Nashville, April 2006 Adv. Accounting, Kathleen Graw
Second Topic 2 Nashville, April 2006 Accounts Payable – Making paying your vendors easier! Adv. Accounting, Kathleen Graw
Menu 13. 20 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 13. 20. 8 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Reoccurring Vouchers Enter vouchers in 13. 20. 8 Listing 13. 20. 9 (Monthly) Update 13. 20. 10 (Monthly) Nashville, April 2006 Adv. Accounting, Kathleen Graw
Steps to Voucher Merchandise Batch created in 13. 20. 2 or 13. 20. 3 Sequential Voucher Register 13. 20. 5 Merchandise Vouchered Handcheck Register (if applicable) In 13. 20. 6 / 13. 20. 27 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 13. 20. 3 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 13. 20. 3 Nashville, April 2006 Adv. Accounting, Kathleen Graw
13. 20. 2 versus 13. 20. 3. Drawbacks 3 13. 20. 3 Inventory Valuation 2 1 No Corp Discrepancies Only works on DC invoices Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 13. 20. 2 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 13. 20. 2 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 13. 20. 2 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Questions? Nashville, April 2006 Adv. Accounting, Kathleen Graw
Third Topic 3 Average Cost & Balancing Inventory – Accounting for your biggest asset. Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 13. 20. 2 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Average Cost Impact BUT Nashville, April 2006 Adv. Accounting, Kathleen Graw
Average Cost Impact 122*22. 97 + (120*24. 417 - 120*22. 952) = 2802. 34 + (2930. 04 -2754. 24) = 2978. 14/122 = 24. 410 for average cost 122*24. 410 + (120*24. 417 - 120*22. 952) = 2978. 02 + (2930. 04 -2754. 24) = 3153. 82/122 = 25. 850 122*25. 85 + (120*23. 69 - 120*22. 952) = 3153. 70 + (2842. 80 -2754. 24) = 3242. 26/122 = 26. 578 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Inventory Valuation Quantity on Hand Average Cost $$ Purchase Price Variance (PPV) Nashville, April 2006 Adv. Accounting, Kathleen Graw
Average Cost Nashville, April 2006 Adv. Accounting, Kathleen Graw
Calculation QOH x Average Cost Diff. on Inv v. PO New Average Cost Step One Step Two End Result Nashville, April 2006 Adv. Accounting, Kathleen Graw
Average Cost Example QOH = 300 jugs Average Cost = $60. 00 ea PO = 200 @ $65. 00 ea Vendor Invoice = 200 @ $70. 00 ea Nashville, April 2006 Adv. Accounting, Kathleen Graw
Average Cost Example QOH x Average Cost (500 * $60) $30, 000. 00 PLUS Difference on Invoice vs. PO (200*$70) – (200*65) $1000. 00 _____________ QOH (500) Nashville, April 2006 Adv. Accounting, Kathleen Graw
Average Cost Example $62. 00 New Average Cost Nashville, April 2006 Adv. Accounting, Kathleen Graw
Inventory Valuation Impact • Overstate Inventory $ • Overstate Profits • Overpay Commissions Widespread Impact • Understate Inventory $ • Understate Profits • Underpay Commissions Balance Sheet & Income Statement Nashville, April 2006 Adv. Accounting, Kathleen Graw
Average Cost Impact 500 jugs Inventory Value Net Income Commissions @ $60 = $30, 000 Sell 500 @ $80: Commissions @ 3% of GP: @ $62 = $31, 000 @ $60 = $10, 000 Understated by $1, 000 @ $62 = $9, 000 Overstated by $1000 Nashville, April 2006 @ $60 = $300 @ $62 = $270 Overpaid by $30 Adv. Accounting, Kathleen Graw
Average Cost Impact Bad Avg. Cost Inventory Value Devalued your Company by understating your inventory. Nashville, April 2006 Net Income Paying taxes on income that you didn’t really realize. Commissions Paid out more to sales people than you needed to and inaccurately reduced your bottom line & affected cash flow. Adv. Accounting, Kathleen Graw
Average Cost Impact Accurate reflection of Gross Profit by month Accurate reflection of Gross Profit by part Accurate reflection of Net Income No large inventory adjustments at year end Better handle on inventory value and turns Average Cost reflects true cost Nashville, April 2006 Adv. Accounting, Kathleen Graw
Inventory Valuation Nashville, April 2006 Adv. Accounting, Kathleen Graw
Detail vs. Summary Detail Summary Nashville, April 2006 Adv. Accounting, Kathleen Graw
General Ledger Implications Impact General Ledger (Detail)? Stock Adjustments Receive in Inventory Voucher/Accounts Payable Month End Journal Entries YES X X NO X X Detail Nashville, April 2006 Adv. Accounting, Kathleen Graw
General Ledger Implications Impact General Ledger (Summary)? Stock Adjustments Receive in Inventory Voucher/Accounts Payable Month End Journal Entries YES X X NO X X Summary Nashville, April 2006 Adv. Accounting, Kathleen Graw
Average Cost Impact Voucher Widget for $100 / Billed $100 Inventory 100. 00 Su ry a mm Accounts Payable 100. 00 il a t e D Nashville, April 2006 Adv. Accounting, Kathleen Graw
Average Cost Impact Voucher Widget for $100 / Bill for $125 / No QOH Inventory 100. 00 ry a mm Accounts Payable 125. 00 Su PPV 25. 00 il a t e D Nashville, April 2006 Adv. Accounting, Kathleen Graw
Average Cost Impact Voucher Widget for $100 / Bill for $125 / QOH Inventory 125. 00 ry a mm Accounts Payable 125. 00 Su Average Cost of product is updated D il a t e Nashville, April 2006 Adv. Accounting, Kathleen Graw
EOM Inventory Procedures Nashville, April 2006 Adv. Accounting, Kathleen Graw
Inventory Journal Entry #1 Use Menu 13. 20. 2 and 13. 20. 3 Run Menu 13. 20. 7 at month end and use for reversing Journal Entry Inventory XXXXX Inv. Accr Nashville, April 2006 XXXXX Adv. Accounting, Kathleen Graw
Inventory Journal Entry #2 Multi Stores: EOM Account for inventory transfers S 01/S 02/S 03 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Inventory Journal Entry #2 Scenario One J/E for Store #1 Debit Inventory 02 Credit Inventory 01 Nashville, April 2006 $20960. 79 Adv. Accounting, Kathleen Graw
Inventory Journal Entry #2 Scenario Two J/E for Store #1 Debit Inventory 02 $20960. 79 Credit Intercompany 01 $20960. 79 Debit Intercompany 02 $20960. 79 Credit Intercompany 02 $20960. 79 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Inventory Journal Entry #3 Stock Adjustments Nashville, April 2006 Adv. Accounting, Kathleen Graw
Nashville, April 2006 Adv. Accounting, Kathleen Graw
Reopening Purchase Orders Reopening Receivers If you do it – have to reopen the first & only receiver on the PO OR It throws out your AP/GL Reconciliation Nashville, April 2006 Adv. Accounting, Kathleen Graw
Reopening Purchase Orders Nashville, April 2006 Adv. Accounting, Kathleen Graw
Physical Inventory Be sure to make note of the final variance amount that you update since you will need to make the journal entry on the books to balance the detail to the summary. You debit/credit inventory and an income statement account with this variance amount. Nashville, April 2006 Adv. Accounting, Kathleen Graw
Other Issues to Consider Warranty’s Impact Store Returns Buyback Dropships Non-Inventory Items (Z 95 items) Nashville, April 2006 Adv. Accounting, Kathleen Graw
Pieces to Valuing Inventory Average Cost EOM Journal Entries Time & Effort Monthly Reconciling Nashville, April 2006 Adv. Accounting, Kathleen Graw
Documentation • Inventory Reconciliation Handout – Reconciling Inventory Detail & Summary • Buyback Handout – Buyback Processes & Procedures Nashville, April 2006 Adv. Accounting, Kathleen Graw
Documentation • Dropships Handout – Dropships Nashville, April 2006 Adv. Accounting, Kathleen Graw
Questions? Nashville, April 2006 Adv. Accounting, Kathleen Graw
Fourth Topic 4 Nashville, April 2006 Warranty – What goes on behind the scenes Adv. Accounting, Kathleen Graw
Warranty Accounts Nashville, April 2006 Adv. Accounting, Kathleen Graw
Warranty & the General Ledger Widget Avg. cost = $10 Sale Price = $12 Credit from Vendor = $25 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Warranty Reconciling EOM Balancing • Unclaimed Warranties • Claimed Warranties – AR • Inventory? Nashville, April 2006 Adv. Accounting, Kathleen Graw
Warranty & the General Ledger Warranty AR Warranty Clearing Nashville, April 2006 Adv. Accounting, Kathleen Graw
Warranty & the General Ledger Nashville, April 2006 Adv. Accounting, Kathleen Graw
Documentation • Warranty Handout – Warranty Nashville, April 2006 Adv. Accounting, Kathleen Graw
Questions? Nashville, April 2006 Adv. Accounting, Kathleen Graw
Fifth Topic 5 Nashville, April 2006 General Ledger – Reporting the money you made Adv. Accounting, Kathleen Graw
Menu 14 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Setting up a GL account 12. 4. 3 14. 1. 1 (for each company) Add to financial statements Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 12. 4. 3 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 14. 1. 1 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Setting up Financial Statements Nashville, April 2006 Adv. Accounting, Kathleen Graw
Journal Entries 1. Enter JE 14. 1. 3 or 14. 1. 4 2. Print & Review JE 14. 1. 13 & 14. 1. 14 3. Update Trial Balance 14. 2. 4 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 14. 1 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 14. 1. 3 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 14. 2 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 14. 3 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 14. 5 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Sixth Topic 6 Nashville, April 2006 Aging – Ways to look at the money customers owe you Adv. Accounting, Kathleen Graw
Menu 35. 6, Screen #2 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Option #1 by Month End Nashville, April 2006 Adv. Accounting, Kathleen Graw
Option #1 by Month End Nashville, April 2006 Adv. Accounting, Kathleen Graw
Option #1 by Month End Nashville, April 2006 Adv. Accounting, Kathleen Graw
Option #1 by Month End Nashville, April 2006 Adv. Accounting, Kathleen Graw
Option #2 by Due Date Nashville, April 2006 Adv. Accounting, Kathleen Graw
Option #2 by Due Date Nashville, April 2006 Adv. Accounting, Kathleen Graw
Option #2 by Due Date Nashville, April 2006 Adv. Accounting, Kathleen Graw
Option #2 by Due Date Nashville, April 2006 Adv. Accounting, Kathleen Graw
Option #3 by Invoice Date Nashville, April 2006 Adv. Accounting, Kathleen Graw
Option #3 by Invoice Date Nashville, April 2006 Adv. Accounting, Kathleen Graw
Option #3 by Invoice Date Nashville, April 2006 Adv. Accounting, Kathleen Graw
Option #3 by Invoice Date Nashville, April 2006 Adv. Accounting, Kathleen Graw
Seventh Topic 7 Nashville, April 2006 End of Year – Putting one year behind you and starting another one. Adv. Accounting, Kathleen Graw
End of Year procedures 1. AR Month-end close rolls into Year End *Confirm with DST 2. Close 12 th month AP 3. Close year AP *MUST happen before entries for new year 4. Close 12 th month GL *Manually Rollover NI 5. Close year GL *NO 13 th month Nashville, April 2006 Adv. Accounting, Kathleen Graw
Documentation • EOY Procedures Handout – EOY Close Nashville, April 2006 Adv. Accounting, Kathleen Graw
Eighth Topic 8 Nashville, April 2006 Miscellaneous – Things you may/may not know… Adv. Accounting, Kathleen Graw
Service Charges Menu 4. 3 Nashville, April 2006 Adv. Accounting, Kathleen Graw
System Purge Parameters Menu 28. 0 Nashville, April 2006 Adv. Accounting, Kathleen Graw
System Purge Parameters Menu 28. 0 Nashville, April 2006 Adv. Accounting, Kathleen Graw
System Purge Parameters Menu 28 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Menu 11 Reporting Variances Different Sales files capture different things *Customer detail file Variances *Freight *Misc Charges *Branch Sales *Returns Nashville, April 2006 Adv. Accounting, Kathleen Graw
Product/GL Control Only works with Product CLASS in Product MASTER Nashville, April 2006 Adv. Accounting, Kathleen Graw
Product/GL Control Menu 12. 3. 3 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Accounting Upgrades • Documentation Handout – Accounting Upgrades 2006. 1 Nashville, April 2006 Adv. Accounting, Kathleen Graw
Upgrade Suggestions Nashville, April 2006 Adv. Accounting, Kathleen Graw
Session Evaluations Nashville, April 2006 Adv. Accounting, Kathleen Graw
Database Training – Nashville/2006