Employee Expense Report Template Excel by wck25557

VIEWS: 260 PAGES: 51

More Info
									                                                                                             ]
                            Raechal Martin, FI Data Analyst – Powell Electronics
                                                   Jim Halepaska, VP of Sales – Z Option

                               Automate What You Hate

[ CHRISTINA CRONE
 ASUG INSTALLATION MEMBER
 MEMBER SINCE: 1999




                                                                 [ MIKE STOKO
                                                                  ASUG INSTALLATION MEMBER
                                                                  MEMBER SINCE: 2003




                                  [ BOB GAUTHIER
                                   ASUG ASSOCIATE MEMBER
                                   MEMBER SINCE:1998
[    What We’ll Cover …

     Powell Electronics and Z Option Information

     Streamline Employee Expense Reimbursement
      Process

     Tips and Tricks to Make Your Spreadsheet Template
      Work for You

     Demonstration of GLSU

     Ideas for Automating Other Accounting Processes

     Wrap-Up



2
[   Powell Electronics At a Glance

       Privately owned (celebrating our 61st year)
       Interconnect and Electromechanical Value-Added Distributor
       One of the top 25 of all U.S. distribution
          Financially sound
       Ten sales offices throughout U.S.
          Nine sales locations outside USA, 8 of which are
           operated by agents on commission
       ISO-9001:2000 Registered as AS 9100 Certified




3
[   Powell Electronics At a Glance




4
[   Powell Electronics – SAP Environment

                R/3 Enterprise (4.7)
            Go-Live year 2000 on 4.0B
      Upgrade to Enterprise in November 2003
      MySAP Business Suites Licensed in 2005

            SD, MM, PP, FI, CO, PA, QM
               Solution Manager 4.0
              Direct Customer of SAP
                  172 SAP Users

              SME – just under $100
                225+ employees

5
[   Z Option At a Glance


    • Located in Lewisville, Texas (Near
      Dallas)

    • Formed in 1998 by accountants and
      programmers

    • SAP Software Partner

    • World-class products and support
      for customers of all sizes and
      industries

6
[   Z Option Customers




7
[   General Product Overview




               Spreadsheet Server
                  “Real Time Interactive Reporting”
8
[   What We’ll Cover …

     Powell Electronics and Z Option Information

     Streamline Employee Expense Reimbursement
      Process

     Tips and Tricks to Make Your Spreadsheet Template
      Work for You

     Demonstration of GLSU

     Ideas for Automating Other Accounting Processes

     Wrap-Up



9
[    Streamline Employee Expense
     Reimbursement Process


             What was the Problem?

              What were the Goals?

             What was our Solution?

                 Who Benefited?



10
[       The Problem: Why did we do this?


                                               Send Expense report &
  Fill out paper          Attach receipts        receipts to Bob for
form in triplicate         using stapler       approval (via snail mail
      1 Hour
                                                     2- 3 days)


 Bob misplaces
                      Raechal nags Bob
 expense report                             Bob approves and sends to
                     (Did you get it? Did
for several days                              Accounts Payable (via
                     you approve it? Did
   (2-3 days)                                  snail mail 2-3 days)
                     you send it to Corp)




 11
[      The Problem: Why did we do this?

                                                  Bob approves and sends to
                                                    Accounts Payable (via
                                                     snail mail 2-3 days)




                                                                 Check is snail
 Accounts                 Accounts                                mailed (2-3
 Payable                 Payable files        Checks are           days snail
  makes                   paper and           processed              mail)
manual entry               receipts


     Estimated time of processing was 2 + weeks plus costs of snail mail


12
[    Streamline Employee Expense
     Reimbursement Process


             What was the Problem?

              What were the Goals?

             What was our Solution?

                 Who Benefited?



13
[    The Goals


        No Manual Calculations
          Less errors

        Reimbursed Faster
          Keep sales people happy – sell more

        No Hardcopies
          How many people are filing copies of
           this?

14
[    Streamline Employee Expense
     Reimbursement Process


             What was the Problem?

              What were the Goals?

             What was our Solution?

                 Who Benefited?



15
[    THE SOLUTION
                    Solution




16
[    THE SOLUTION


             Contains 4 Worksheets


                    Expenses

                    Receipts

                    Audit Trail

                      GLSU



17
[    Expenses Worksheet




18
[    Receipts Worksheet




19
[    Audit Trail Worksheet




20
[    GLSU Worksheet




21
 [      Worksheet Automation
                                 Drop-Down list is created to
                                 choose names.

                                 Amounts from Expense sheet are
                                 brought into the amounts for the
                                 GLSU entry.




Lookup table inserts the text,
Vendor number, Profit and
Cost centers.

IF Statement comments out
unnecessary lines.
   22
[    Attach Document In SAP




23
[    View Document In SAP




24
 [      The Solution: What did we do?



Fill out Expense        Scan Receipts        Attach scanned        Email Expense
Worksheet (1 Hr)         (10 Minutes)       receipts (minute)     report to Bob for
                                                                  approval (minute)



 Bob approves
                          Accounts Payable posts using
 and emails to
                         GLSU and attaches document in             Process Direct
   Accounts
                               SAP (10 Minutes)                       Deposit
Payable (minute)


       Estimated time of processing is cut from 2+ weeks to less than 2 Hours


  25
[    Streamline Employee Expense
     Reimbursement Process


             What was the Problem?

              What were the Goals?

             What was our Solution?

                 Who Benefited?



26
[    WHO BENEFITED

        IT
          Little IT support
          No consulting

        Auditors
          Ability to view Expense Report File in
           SAP
          Comprehensive audit trail

        Managers
          Less nagging
          No paper backup
27
[    WHO BENEFITED

        Accounts Payable
          Automatic Posting
          No filing
          No printing checks
        Field Sales
          Faster processing
          Direct Deposit
        Trees
          No paper filing
          No paper cuts

28
[    What We’ll Cover …

      Powell Electronics and Z Option Information

      Streamline Employee Expense Reimbursement
       Process

      Tips and Tricks to Make Your Spreadsheet Template
       Work for You

      Demonstration of GLSU

      Ideas for Automating Other Accounting Processes

      Wrap-Up



29
[    TIPS AND TRICKS

                   Lookup Tables

                       IF Statements

                   Dropdown Lists

                 Password Protection

                         Macros



30
[       TIPS AND TRICKS
 LOOKUP TABLES (Not as hard as it sounds)
    Vertical lookup =vlookup(what,from:to,where,false)
          =VLOOKUP(B69,GLSU!B31:G114,6,FALSE)
       Horizontal lookup =hlookup(what,from:to,where,false)
          =HLOOKUP(B69,GLSU!B31:G114,6,FALSE)




 31
[    TIPS AND TRICKS

 IF STATEMENTS
    =IF(a=b,true,false)
        =IF($D16 = 0,”COMMENT”,” ”)




32
[       TIPS AND TRICKS
                             DROP-DOWN LISTS
                                Click on: Data, Data Validation




     On the settings tab make sure
     it states list and designate
     where your list is located. In
     this example the list is located
     on the GLSU worksheet cells
     B29:B30



33
[
•
          TIPS AND TRICKS

         DROP-DOWN LISTS
                                     You may choose an error
          You may choose to have a
                                     message when erroneous
          message appear when the
                                     information is typed in.
          cell is chosen.




    34
 [     TIPS AND TRICKS


 PASSWORD PROTECTION
    Unlock cells
        Choose cells to unlock
        Excel 2007 = Home, Format, Format
         Cells
        Excel 2003 = Format, Cells




  35
[    TIPS AND TRICKS




        PASSWORD PROTECTION
           Unlock cells
              On the protection tab remove
               the checkmark from “Locked”




36
[     TIPS AND TRICKS

 PASSWORD PROTECTION
      Password protect worksheet
         2007 Review, Protect Sheet, enter and confirm a password you will
          remember
         2003 Tools, Protection, Protect Sheet




37
[    TIPS AND TRICKS

 MACROS
     Macros are great tools for those actions you repeat
       redundantly:
         Do you always format headers the same?
         Do you always format total lines the same?
         Do you have a file that comes from a vendor that you always
          need to input or remove columns and input formulas into
          columns?
     A Macro can be set up to do all of these with a single button.
       The steps are:
         Record Macro
         Perform Task
         Stop Recording
38       Run Macro
[    TIPS AND TRICKS
 MACROS - RECORD
    Excel 2007 click on: Developer, Record Macro, Name Macro
    Earlier versions of Excel click on: Tools, Macro, Record new
     Macro, Name Macro




39
[       TIPS AND TRICKS
                                                                  Tip

 MACROS - PERFORM TASK

       Complete the task you want the button to perform
          Start with something simple like choosing a set of cells
          Change the Fill Color to yellow
          Change the font to Bold
          Put a box around the cells
          Change the font size in the cells to 16




 40
[      TIPS AND TRICKS

 MACROS – STOP RECORDING
    Excel 2007 click on: Developer, Stop Recording
    Earlier versions of Excel click on: Tools, Macro, Stop Recording




  41
[    TIPS AND TRICKS
 MACROS – RUN
    Excel 2007 click on: Developer, Macros
    Earlier versions of Excel click on: Tools, Macro, Macros




42
[    What We’ll Cover …

      Powell Electronics and Z Option Information

      Streamline Employee Expense Reimbursement
       Process

      Tips and Tricks to Make Your Spreadsheet Template
       Work for You

      Demonstration of GLSU

      Ideas for Automating Other Accounting Processes

      Wrap-Up



43
[    Problem Resolution: Z Option Response Time




44
[    What We’ll Cover …

      Powell Electronics and Z Option Information

      Streamline Employee Expense Reimbursement
       Process

      Tips and Tricks to Make Your Spreadsheet Template
       Work for You

      Demonstration of GLSU

      Ideas for Automating Other Accounting Processes

      Wrap-Up



45
[    IDEAS FOR
     AUTOMATING




                  Standard Journal Entries
                       Inter/Intra
                       Freight Re-classes
                       Variance Re-classes

46
[    IDEAS FOR
     AUTOMATING
                  Monthly Allocations
                       Depreciation Schedules
                       Income / Salary
                       Insurances
                       Rent




47
[    What We’ll Cover …

      Powell Electronics and Z Option Information

      Streamline Employee Expense Reimbursement
       Process

      Tips and Tricks to Make Your Spreadsheet Template
       Work for You

      Demonstration of GLSU

      Ideas for Automating Other Accounting Processes

      Wrap-Up



48
[    7 Key Points to Take Home

1. Use Excel and Z Option’s GLSU to automate manual accounting processes.

2. Streamline Employee Expense Reimbursement process. Does your process
    take weeks or hours?

3. Not all high ROI projects require a huge budget.

4. Improve your audit trail by automatically attaching source documents.

5. Make Excel work for you by using standard functionality.

6. Increase Accuracy, reduce time and effort, shorten month-end close cycle.

7. Save trees and aggravation with paperless processes.

49
[    Your Turn – Questions!



                         How to Contact: Raechal Martin
                              rmartin@powell.com
                                www.powell.com
                                 1-856-241-8043


                         How to Contact: Jim Halepaska
                               jim@zoption.com
                               www.zoption.com
                                 1-972-315-8800
                                  Booth #1572

50
[     Thank you for participating.
              Please remember to complete and return your
                      evaluation form following this session.
        For ongoing education on this area of focus, visit the
         Year-Round Community page at www.asug.com/yrc
                                                                 ]
                             [SESSION CODE:
                              4601




51

								
To top