Presentation Transcript

1. CPA Firm Technology & Differences between School Work and Working in a CPA Firm

2. CPA Firm Technology Many CPA firms are paperless. Workpapers are stored electronically Go File Room & AWP Trial Balance Software Caseware & Engagement Audit Software ACL & IDEA Tax Software Gosystems, Prosystems, & Lacerte Research software: ARM, CCH, RIA
3. CPA Firm Technology Software used by all departments: Adobe PDF & Adobe PDF writer tools Fixed Asset Software: FAS & CCH FA Client Bookkeeping software Quickbooks, Great Plains, Creative Solutions, & Peachtree PDF to Excel Converter Tools Able2Extract & ScanSoft PDF Converter Excel Instant Messenger
4. Caseware
5. Caseware Trial Balance
6. Caseware Journal Entries
7. Fixed Asset Report
8. Gosystems
9. AWP Tax Database
10. AWP Audit Database
11. AWP Audit Workpaper
12. Audit Command Language: ACL Used to manipulate large data sets Excel has 65,536 rows and 356 columns. The software and features are geared toward audit. Similar Software: Interactive Data Extraction & Analysis- IDEA
13. Excel Tips that Save Time Vlookup Left, Right, Mid Concatenate Conditional Formatting Filter Sort
14. Vlookup Use this formula when you have a chart of accounts with account numbers and descriptions, and you want to assign account numbers to a PBC TB.
15. Vlookup Step 1 Step 1: paste chart of accounts in Excel with the account numbers to the right of the descriptions.
16. Vlookup Step 2 Step 2: Paste the current year accounts and amounts in columns B & C.
17. Vlookup Step 3 Step 3: Type “=Vlookup” in cell A2. Step 2: Paste the current year accounts and amounts in columns B & C. If you click the “fx” button, it will open the Function Arguments Box.
18. Vlookup Step 4 Step 4: The lookup value is cell B2: the account description.
19. Vlookup Step 5 Step 5: The Table Array is the entire chart of accounts. Press “F4” after highlighting the table to lock absolute references.
20. Vlookup Step 6 Step 6: The “Col Index Num” is “2” and the “Range Lookup” is “false”. A “preview” of the account number will show below.
21. Vlookup Step 7 Step 7: Press “Enter” or click “Ok” for the box to disappear. The account number will show.
22. Additional Preparation Double click in the lower right corner of the box highlighting the account number. This will make the formula “fill” without having to drag the mouse.
23. Additional Preparation If there is no match, “#N/A” will show.
24. Additional Preparation Use Conditional Formatting to search for Duplicates
25. Additional Preparation After ensuring that there are no duplicate accounts and that all accounts have account numbers, Copy Column A and “Paste Special Values” right on top of itself in Column A. You can now delete columns E & F, and your Excel TB is ready for import!
26. Left, Right, Mid Left, Right and Mid allow you to take text or numbers and extract only a portion of the cell. In this example, we want to remove the “1.” away from the beginning of the account number.
27. Left, Right, Mid Type “=Right(” in cell E4.
28. Left, Right, Mid To tell the formula to take only the 4 digits on the right: Click on Cell A4 to select. Press comma “ , ” Press “4”.
29. Left, Right, Mid Press Enter, and the account number will appear. Double Click the lower right box to fill the formula down Column E. Left, Right and Mid all do the same thing depending on what part of the cell you are trying to capture.
30. Concatenate Concatenate is the opposite of Left, Right and Mid. It joins text together. In this example, we want to put the words “Account Number” in front of each actual account number.
31. Concatenate Type “=Concatenate(“ in cell E2. “Text 1” is the first text you want listed. In this case, we clicked cell B1. Click F4 next to lock the absolute cell reference.
32. Concatenate Next, press comma “ , ” Click cell A2. Press Enter
33. Concatenate Click the lower right box to extend the formula. You can now copy and “Paste Special Values” from column E to A.
34. My favorite Shortcuts Ctl + tilda “~” = shows all formulas Ctl + F = Find Ctl + Z = Undo Ctl + Y = Redo F2 = show the formula in a single cell
35. Excel Shortcuts http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx
36. Excel Shortcuts http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx
37. Excel Shortcuts http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx
38. Excel Shortcuts http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx
39. School vs Work You always hear that the work you will be doing in the real world is nothing like what you do in school…. …That is a scary thought especially since anyone rarely gives an example of what that means…….. ……So we will provide you with some examples of how school work differs from work at a CPA firm!
40. No Solution Manual At school, you read a chapter, work problems using those concepts, and have a solution manual to refer to. At work, there is no solution manual. There is no “chapter” to read ahead of time. The information you are given may or may not be what you need. You need to know what questions to ask to accomplish tasks assigned to you.
41. Documentation Real life has a lot more documentation! Take good notes to help the reviewer. If you experience technology problems, your notes can help you recover your work. Document the things you learn as examples for yourself. If you need help but no one is available to help you, and you have to turn the work in, document what you tried to do to save time.
42. Depreciation Example In school, you calculate depreciation: How much depreciation expense would you have in year 1 if you placed a piece of machinery in service on 6/1/2011 with a cost of $18,000, no salvage value and a life of 7 years using SL depreciation?
43. Depreciation Example In a CPA firm, the software will calculate depreciation for you. You need to know how to enter the asset in the system so that it calculates correctly this year and in future years. Both tax and audit staff have to be aware of any book/tax differences that exist when creating a depreciation schedule.
44. Depreciation Example Instead of being given information and then solving for a variable, you need to know what questions to ask to get the information you need to do it right. Partner says, “Add the machinery to the depreciation schedule.” You may be given an invoice that will provide you with the date of purchase, cost basis and description. You may be aware of other similar assets on the companies books that provide an example for how this new asset will be depreciated. If the asset was new in 2011, it might qualify for 100% bonus depreciation- even though this is a tax concept, often times it will be audit staff that are responsible for setting it up on the depreciation schedule. If you need help, ask.
45. Being a Detective Regardless of if you are in tax or audit, you really signed up to be a detective! A big part of the job is figuring out what the client did and making journal entries to fix it. Successful staff will learn how to identify issues and make necessary corrections.
46. Example Unadjusted TB A receivable with a credit balance my actually be a payable, or something else.
47. Example Unadjusted TB A debit balance in a payable may actually be a receivable, or something else.
48. Example Unadjusted TB Sometimes clients aren’t sure of journal entries, so they’ll plug an “ask my accountant” account.
49. Example Unadjusted TB Assets are not fully depreciated, but A/D didn’t change. Depreciation has not been recorded. It looks like there are some additions too!
50. Example Unadjusted TB It looks like the client acquired some goodwill and amortized it. We don’t amortize goodwill, we test it for impairment. This entry will be reversed.
51. Being a Detective- Use Your Resources! Know how to use your resources to answer your questions. Anticipate what the senior will say if you go to them for help. If the senior is going to tell you to go look for something…go look first before going to the senior. If you have a general ledger that shows every transaction, you can often figure out what the client did.
52. GL Findings By viewing a general ledger you may find the client is debiting depreciation expense, and crediting the asset. You might find that as they make payments on a vehicle, they credit cash and instead of debiting the payable, they debit the asset, or even an expense.
53. Last Piece of Advice Having a great attitude is a major component to succeeding in Public Accounting. Everyone is under pressure- don’t make anyone’s pressure worse. Find an upbeat way of communicating when you’re frustrated- “I feel like I’m taking crazy pills!”