Sua Excel Spreadsheet

Document Sample
Sua Excel Spreadsheet Powered By Docstoc
					                                                      Everything you ever wanted to know
                                                      about...


                                                      The
                                                      Accounting
                                                      Cycle


              Overview                                   The Accounting Cycle
                                                                          SUA Reference p.10


 Review of the manual Accounting Cycle                                    Record in
                                                             Prepare       Journals
 6 Major Subcycles                                          Documents
                                                                                                Post to
                                                                                                Ledgers
 Begin the SUA                               Transactions
                                              Occur
                                                                                                      Prepare
                                                                                                      Unadjusted
                                               Prepare                                                Trial Balance
                                               Closing
                                               Entries       Prepare                            Prepare and
                                                             Financial                          Post Adjusting
                                                                           Prepare
                                                             Statements                         Entries
                                                                           Adjusted Trial
                                                                           Balance




    The Accounting Cycle                                  6 Major Subcycles

    Subcycle (transaction cycle):
                                                                                                Human
      Group of related business activities               Revenue      Expenditure
                                                                                               Resources
      that result in repetitive patterns of               Cycle          Cycle
                                                                                                 Cycle
      transactions
                                                                General
                                                              Ledger and           Production Financing
                                                               Reporting             Cycle      Cycle
                                                                 Cycle
        Revenue Cycle in SUA                                   Revenue Cycle in SUA
                  Instructions pp. 18-21                                 Credit Sale
                                                     1.    Customer Purchase Order
  Consists of:
                                                     2.   Approve Credit
     Credit Sales
                                                     3.   Bill of Lading
     Cash Sales
                                                     4.   Sales Invoice
     Bad Debt Write-offs
                                                     5.   Pack and ship order
     Sales Returns
                                                     6.   Review and approve documents
     Cash Receipts
                                           Revenue   7.   Mail invoice to customer
                                            Cycle    8.   Make the entry in the Sales Journal and then
                                                          update the A/R Subsidiary Ledger




         Introduction to SUA                                    Introduction to SUA
               Waren Sports Supply                                 SUA package materials

 SUA  package materials                              Reference  book
 Course packet supplemental materials                Documents   Folder
 Worksheet                                           Other file tabs
                                                      Journal book
                                                      Instructions/Flowcharts/Ledgers book




        Introduction to SUA                                     Introduction to SUA
   Course packet supplemental materials                                  Worksheet

 Assigment   Schedule                                Worksheet  is already prepared as Excel
 Check Figures                                        spreadsheet
 File System page                                    Download this spreadsheet from the 474

 Grade sheet
                                                       web site under “Homework” (you don’t need
                                                       the spreadsheet worksheet until SUA 3)
 Transactions list
 Price lists
         Alter the SUA package!!!                                Alter the SUA package!!!
 From your Documents File, throw away:
                                                         Also, from your Documents File, throw away:
  the blue Transactions List
                                                          1 green Worksheet
  the green Transactions List
                                                          1 green Post-closing Trial Balance
  the 1 blue Price List page


  Replace them with Transactions List
                                                          We will use the Excel spreadsheet
  and Price List pages from your
                                                          worksheet instead (in SUA 3).
  course packet!!!




            Waren’s Subcycles                                       Waren’s Subcycles
      Revenue cycle (Waren: “Sales and cash receipts         Revenue cycle (Waren: “Sales and cash receipts
       cycle”)                                                 cycle”)
      Expenditure cycle (Waren: “Purchases and cash          Expenditure cycle (Waren: “Purchases and cash
       disbursements cycle”)                                   disbursements cycle”)
      Human resources cycle (Waren: “Payroll cycle”)         Human resources cycle (Waren: “Payroll cycle”)
      General Ledger and Reporting cycle (Waren:             General Ledger and Reporting cycle (Waren:
       not specifically mentioned)                             not specifically mentioned)
      Production cycle (doesn’t apply to Waren)              Production cycle (doesn’t apply to Waren)
      Financing cycle (Waren doesn’t have much               Financing cycle (Waren doesn’t have much
       external financing)                                     external financing)




            SUA Assignments                                          Waren Personnel

 SUA 1 - Sales and cash receipts cycle                  Ray Kramer – Salaried manager
 SUA 2 - Purchases and cash disbursements               Jim Adams – Hourly accounting clerk
          cycle                                          Nancy Ford – Hourly production and billing
 SUA 3 - Payroll cycle and G/L cycle
                                                            Let’s get started...
                                                Label the transactions
                                               9  S&CR, 8 P&CD, 2 Payroll
 Use this semester’s transaction                SUA 1
   set and your own numbers                     SUA reference - chapter 3
                                                Flowcharts - pp. 18-21
                                                Review all S&CR transactions
                                                Do December 19 S&CR transaction – credit
                                                 sale to University of Southern Iowa




        December 19th transaction                      Working with flowcharts
                                                  How do you know how far to go when following
                                                   the flowchart?
                                                  A flowchart may have one or several branches.
                                                   Follow the chart until all of the branches come to
                                                   an end.
                                                  A flowchart branch comes to an end (or a
                                                   temporary end) when you reach a file symbol      .
                                                   A branch comes to a permanent end when you
                                                   come to a terminal symbol          .




          At the end of SUA 1...                          A/R Aged Trial Balance
                                                                (Reference Manual p.45 )
 All documents in S&CR are properly
  processed and filed
 All necessary entries are made in journals
  and Subsidiary Ledgers
 A/R aged trial balance (p. 45) is complete
  (Exclude zero balances!)
 Monthly statement for customer is
  complete
                                                                    Overview
                                                      Review SUA 1
                                                      Review Expenditure Cycle
                                                      Inventory Systems
                                                      Begin SUA 2
                                                      Internal Controls & Auditing




           At the end of SUA 1...                            A/R Aged Trial Balance
                                                                  (Reference Manual p.45 )
 All documents in S&CR are properly
  processed and filed
 All necessary entries are made in Journals
  and Subsidiary Ledgers
 A/R aged trial balance (p. 45) is complete
 Monthly statement for customer is
  complete




        Expenditure Cycle in SUA                          Expenditure Cycle in SUA
                   Instructions pp. 22-24                          Credit Purchase
                                                     1. Prepare purchase order (p.o.)
  Consists of:
         Credit Purchases                           2. Approve purchase order
         Cash Purchases                             3. Send p.o. to vendor
         Purchase Returns                           4. Receive goods, prepare receiving report
         Cash Disbursements                         5. Receive vendor invoice
                                       Expenditure   6. Match documents and approve for payment
                                          Cycle      7. Make the entry in the Purchases Journal
                                                       and then update the A/P Subsidiary Ledger
            Inventory Systems                                          Inventory Systems
                                                                               Perpetual Inventory

There are two basic types of inventory
                                                           In Perpetual Inventory:
 systems:
                                                             1) Use an Inventory Subsidiary Ledger
    1) Perpetual Inventory
                                                             2) No Purchases account
    2) Periodic Inventory
                                                             3) Count inventory to determine shrinkage
                                                                (not necessarily every period)
Which one is becoming more common?
    Perpetual




            Inventory Systems                                           Inventory Systems
                                                                                   Perpetual Inventory
               Perpetual Inventory - Entries
                                                           Advantages:
Purchases Entry:               Sales Entries:
                                                            1) Better control over inventory
  Inventory       xx                                        2) Essential for JIT
                                 A/R           xxx
    A/P                  xx                                 3) Becoming essential to be competitive
                                  Sales              xxx

A/P                xx            COGS     xx               Disadvantages:
  Purchase Returns       xx
                                  Inventory          xx     More costly than periodic, though costs
A/P                xxx
                                                               are decreasing with better
  Cash                    xx                                    technology.
  Purchase Discounts      xx




            Inventory Systems                                          Inventory Systems
                    Periodic Inventory                                    Periodic Inventory - Entries

                                                           Purchases Entry:                 Sales Entries:
In Periodic Inventory (Waren’s method):
  1) No Inventory Subsidiary Ledger                          Purchases       xx                A/R       xxx
  2) Use Purchases account                                     A/P                  xx          Sales          xxx
  3) At end of period count inventory to
     determine COGS at end of every period                 A/P                xx
                                                             Purchase Returns       xx
     (shrinkage will be included)
                                                           A/P                xxx
                                                             Cash                     xx
                                                             Purchase Discounts       xx
           Inventory Systems                                            Inventory Systems
                                                                                 Periodic Inventory
    Periodic Inventory - Calculating Ending Inventory
                                                        Advantages:
1) Count the inventory items (# of units)
2) Calculate ending inventory:
                                                         1) Cheaper recordkeeping
  (# of units)(cost per unit) = Ending Inv. Value
3) In an adjusting entry, replace old inventory         Disadvantages:
      balance with new balance, close out purchases      1) Less control over inventory
      accounts and Freight-In, and record (plug)
      COGS                                               2) Can’t be used with JIT
  (You do this in SUA 3 , an example entry is in the     3) Less competitive strategy in high tech
   Reference book p. 75)                                     environment




             Waren’s System                              Accounts Payable Trial Balance
Notes:                                                                            Waren Sports Supply
 Waren uses the periodic system, they have a                                 Accounts Payable Trial Balance
                                                                                   December 31, 2005
  Purchases account
 The Purchases journal is used for any kind of                  Vendor No.           Name                       Amount
  purchase, (fixed assets, small expenses,                                                                       Payable
  inventory), but only inventory purchases are                    503                Sears                     $ 356.30
                                                                  510                Brown & Root               3,496.00
  debited to the Purchases account                                545                Lapland & Co.              2,450.00
 In the purchase of laptop computers on
                                                                                                     TOTAL     $ 6,302.30
  12/31/09, only process the purchase, do not
  pay the invoice, even though the payment
  terms state: “Due on receipt of invoice”.




                                                                 At the end of SUA 2...
                                                         Alldocuments in P&CD are properly
                                                          processed and filed

                                                         Allnecessary entries are made in journals
                                                          and Subsidiary Ledgers

                                                         A/P   trial balance is complete
                          SUA 3                                              Payroll Cycle in SUA
                                                                                         Instructions p. 24

 Payroll     Cycle - 2 transactions
                                                                     Consists of:
                                                                         Timekeeping
 General       Ledger and Reporting Cycle
                                                                         Payments
     Do end of period procedures
     AJE’s
     Financial Statements
     Closing entries
                                                                                                                    Payroll
                                                                                                                     Cycle
 Prepare       SUA for submission




              Payroll Cycle in SUA                                       Let’s do a Payroll Clinic…
1. Hourly Employees work and fill in time cards
2. Approve hours on time cards and give to
  accounting department
3. Calculate payroll amounts
                                                                                 http://www.irs.gov/pub/irs-pdf/p15.pdf
4. Record in Payroll Journal and Employee Earnings
  Subsidiary Ledger
5. Generate paychecks
6. Review and sign paychecks
7. Distribute paychecks to employees
8. Make the entry in the Cash Disbursements
  Journal




            Notes on Payroll Cycle                                         Notes on Payroll Cycle
   The pay increase on Dec. 20th applies to the                    The withholding amounts for employees’ income
    whole pay period, ie. from Dec. 16th                             taxes are given to you in the Dec. 20th
                                                                     transaction; you don’t have to calculate them.
   Be careful with Time Card calculation
       Hours are calculated in hundredths (1/100) of an hour,      The payroll check numbers can be in any order as
        not minutes (1/60).                                          long as you have all of them.

   SUTA & FUTA percentages (2.7%, 0.8%) are                        Ray Kramer signs all the checks, including his own
    given in instruction manual page 13. Apply to only               paycheck.
    first $7000 of annual income.
    (NF only – Employee Subsidiary Ledger)
            Notes on Payroll Cycle                                   End of Period Procedures
   FICA tax is 6.2% + 1.45% = 7.65%.                         Follow instructions in project - do all procedures
      (p. 8 of Instructions book)                             not done in previous parts of SUA (follow the
     6.2% Social Security – wages up to $102,000              Assignment Schedule)
     1.45% Medicare - all wages
                                                                   Month-end procedures 1-7
So, per employee:                                                       Bank reconciliation, unemployment taxes, etc.
     For first $102,000 x 7.65%                                        Note: These are not “adjusting entries”, they are normal
     For amounts over $102,000 x 1.45%                                  entries of the period
                                                                   Year-end procedures 1-7
                                                                      worksheet
No Waren employee has earned over $102,000, so                        Adjusting entries – 5 AJE’s (4 + 1)
 use combined rate of 7.65%                                           Closing entries – 3 Closing entries (no Dividends)




    Adjusting Journal Entry Notes                                            AJE for Inventory
     Depreciation   policy is the half-year convention.                                                DR    CR
                                                                         Inventory                      xx or xx
     Bad   Debts method is given on last page of                        Purchase Discounts             xx
        Transaction List                                                 Purchase Returns               xx
                                                                         Cost of Goods Sold            PLUG
     Ending  Inventory figure given on last page of                         Purchases                        xx
        Transaction List
                                                                             Freight-In                       xx
     The    form of Inventory AJE is shown on p. 75
        of the Reference book. (next slide)




             AJE for Inventory                                       End of Period Procedures
                                  DR    CR                     Closing Entries
          Inventory               xx or xx                     1.    Close Revenues & Gains to Income Summary
          Purchase Discounts      xx                           2.    Close Expenses & Losses to Income Summary
          Purchase Returns        xx                           3.    Close Income Summary to Retained Earnings
          Cost of Goods Sold     PLUG
                                                               4.    Close Dividends to Retained Earnings
              Purchases                 xx
              Freight-In                xx
                                                                             (Sample Entries: p. 23 Reference Book)
                                                           End of Period Procedures
     End of Period Procedures                                     Finding Errors in the worksheet

 Notes:                                              Calculate the difference between DR and CR
   Use worksheet spreadsheet on web site              and:
   AJE for income tax accrual: Use worksheet         1.   Look for that number in your entries and posting
   Financial Statements automatically prepared            (you may have forgotten to post the number)
    on spreadsheet                                    2.   Look for an amount that is exactly half of that
   The only columns you need to fill in on the            difference (you may have posted a debit as a credit or
    worksheet are the Unadjusted Trial Balance             vice versa)
    and the Adjustments (green columns)               3.   Divide the difference by 9 and see if it divides with
                                                           no remainder (indicates a transposition error)




Postings include:          Cash  25               If you mispost:                  As:
                             A/R    25                 Cash 25                            Cash 25
                                                         A/R      25                        A/P   25

   Resulting Post-Closing Trial Balance:               Resulting Post-Closing Trial Balance:
                           DR       CR                                         DR       CR
       Cash                50                              Cash                50
       A/R                100                              A/R                125
       A/P                           75                    A/P                          100
       Common Stock                  75                    Common Stock                  75
                          150       150                                       150       150

                                                                         No Difference!!!




If you mispost:           As:                     If you mispost:                  As:
     Cash 25                    Cash       25          Cash 25                            Cash 25
       A/R      25              A/R        25            A/R      25                        A/R   52

   Resulting Post-Closing Trial Balance:               Resulting Post-Closing Trial Balance:
                           DR       CR                                         DR       CR
       Cash                50                              Cash                50
       A/R                150                              A/R                 73
       A/P                          75                     A/P                           75
       Common Stock                  75                    Common Stock                  75
                          200       150                                       123       150
                                                       150 – 123 = 27 / 9 = 3.000 – no remainder
          200 – 150 = 50 / 2 = $25 error
                                                       must be a TRANSPOSITION ERROR
   Fixed Asset Trial Balance                                   SUA 3 - Completing the project
(Do after depreciation AJE is made and posted)

                      Waren Sports Supply
                                                              Notes:
                    Fixed Asset Trial Balance                  All AJE’s and Closing entries are made in
                       December 31, 2005
                                                                General Journal and are posted to G/L
  ASSET               Cost       Accumulated        Net        Foot all journals and ledgers
                                 Depreciation    Book Value
                                                               From the spreadsheet, print off the
  Computers        $ 15,000.00    $12,000.00    $ 3,000.00
  Furniture          10,000.00     10,000.00          0.00      Worksheet, the Income Statement, and the
  Equipment          25,000.00      8,000.00     17,000.00      Balance Sheet and put in SUA envelope
   TOTALS          $ 50,000.00    $ 30,000.00   $ 20,000.00    Total manual G/L balances and make sure they
                                                                agree with the worksheet balances
                                                                 DO NOT DO A STATEMENT OF CASH FLOWS




SUA 3 - Completing the project                                         Creating the schedules
                  A/R Trial Balance                            Type up in Excel
                  A/P Trial Balance                            Line up numbers in columns
              Fixed Asset Trial Balance
                                                               Show pennies, even when .00
                 Bank Reconciliation
                                                               Use “Accounting” number format
                                                               Underline columns, double underline totals
  MUST BE TYPED AND IN GOOD FORM
HANDWRITTEN WORK WILL NOT RECEIVE
               CREDIT




                                                                  Prepare project for submission
                                                                 Tape grading sheet to front of envelope and
                                                                  fill in name and section time
Use this semester’s transaction                                  Make sure that your documents are all filed
                                                                  according to the File System page
  set and your own numbers                                       Remove the Reference Manual
                                                                 Tape envelope shut
                                                                 Hand in
                                                                 Have a beer
                                                                 Internal Controls & Auditing




        What is Internal Control?                                    What is Internal Control?
   Internal Control is a process implemented by the           Internal Control is a process implemented by the
    BOD and management that gives reasonable                    BOD and management that gives reasonable
    assurance that these objectives are achieved:               assurance that these objectives are achieved:
     Safeguard assets                                             Safeguard assets
     Ensure accurate and reliable accounting (and other)        Ensure    accurate and reliable accounting (and
      information                                                   other) information
     Improve operational efficiency
                                                                   Improve operational efficiency
     Promote adherence to managerial policies
                                                                   Promote adherence to managerial policies
     Financial statements are prepared using GAAP
                                                                 Financial    statements are prepared using GAAP
     Comply with laws and regulations
                                                                   Comply with laws and regulations




Financial Statement Assertions                              Financial Statement Assertions
    Who owns the financial                                      Who owns the financial
    statements of a company?                                    statements of a company?           Management
                                                                Every number on the financial
                                                                statements represents a set of
                                                                claims being made by
                                                                management known as:           Assertions
Financial Statement Assertions                          Financial Statement Assertions
            “Sales         $ 1,500,000.00”                          “Sales         $ 1,500,000.00”
This statement asserts:                                 This statement asserts:

1. All sales are properly included,                     1. All sales are properly included,
     you have left none out                                 you have left none out               COMPLETENESS
2. All sales actually occurred, number                  2. All sales actually occurred, number
     represents actual sales made                            represents actual sales made
3. Sales are properly valued, net of                    3. Sales are properly valued, net of
     returns, allowances, discounts                          returns, allowances, discounts
4. You have proper claim to revenue                     4. You have proper claim to revenue
     generated, sales belong to you                          generated, sales belong to you




Financial Statement Assertions                          Financial Statement Assertions
            “Sales         $ 1,500,000.00”                          “Sales         $ 1,500,000.00”
This statement asserts:                                 This statement asserts:

1. All sales are properly included,                     1. All sales are properly included,
    you have left none out               COMPLETENESS       you have left none out               COMPLETENESS
2. All sales actually occurred, number                  2. All sales actually occurred, number
     represents actual sales made         EXISTENCE          represents actual sales made         EXISTENCE
3. Sales are properly valued, net of                    3. Sales are properly valued, net of
     returns, allowances, discounts                          returns, allowances, discounts       VALUATION
4. You have proper claim to revenue                     4. You have proper claim to revenue
     generated, sales belong to you                          generated, sales belong to you




Financial Statement Assertions                                         Computerization does
            “Sales         $ 1,500,000.00”                                not change the
                                                                           assertions!!!
This statement asserts:

1. All sales are properly included,
    you have left none out               COMPLETENESS                  What changes in a
2. All sales actually occurred, number
     represents actual sales made         EXISTENCE                  computerized system is
3. Sales are properly valued, net of
     returns, allowances, discounts       VALUATION                     the design of the
4. You have proper claim to revenue
     generated, sales belong to you
                                         RIGHTS/                        internal controls.
                                          OBLIGATIONS
              Auditing and Risk                                       Auditing and Risk
   Auditors must give an opinion about the fairness                  “Sales         $ 1,500,000.00”
    of the financial statements.
                                                              To audit Sales:
   To do this they use their knowledge of
                                                              1. What is the risk of misstatement with Sales?
    transaction cycles, assertions and the audit trail
                                                              2. Which assertions may be false?
   Auditors cannot review every transaction, they
    must focus their audit work with an assessment            3. Use the Sales audit trail to design audit
                                                                   testing of Sales
    of risk




                Auditing Sales                                           Auditing Sales
                     Credit Sale                                               Credit Sale
1.    Customer Purchase Order                             Overstatement        risk
2.   Approve Credit                                         Existence   assertion must be investigated
3.   Bill of Lading                                         Test   by vouching back up the audit trail
4.   Sales Invoice                  G/L         F/S           ie. Sample entries in sales journal and look for
                                                                    matching customer p.o.s and sales invoices
5.   Pack and ship order
                                                          Understatement       risk
6.   Review and approve documents
                                                            Completeness  assertion must be investigated
7.   Mail invoice to customer
                                                            Test by tracing forward down the audit trail
8.   Make the entry in the Sales Journal and then             ie. Sample customer p.o.s and sales invoices and
     update the A/R Subsidiary Ledger                               look for matching entries in sales journal




       Very Common Audit Tests
 Sales - Existence
 A/P - Completeness
 Fixed assets - Valuation
 A/R - Existence and Valuation

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:365
posted:8/17/2011
language:English
pages:14
Description: Sua Excel Spreadsheet document sample