Rettsmedisinske revisorer kan bruke Excel til raskt å vasse gjennom hundretusener av poster for å finne mistenkelige transaksjoner. I dette segmentet vil vi se på noen av disse metodene.
Sak 1:
Leverandøradresser mot ansattadresser
Bruk en MATCH-funksjon for å sammenligne nummerdelen av gateadressen til dine ansattes poster med nummerdelen av gateadressen til leverandørene dine. Er det noen sjanse for at noen ansatte også selger tjenester til selskapet?
- Start med en liste over leverandører og en liste over ansatte.
- En formel som for eksempel
=LEFT(B2,7)vil isolere den numeriske delen av gateadressen og de første bokstavene i gatenavnet.
- Lag en lignende formel for å isolere den samme delen av leverandøradressene.
- MATCH-funksjonen vil se etter adressedelen i C2 og prøve å finne en match i leverandørdelene i H2: H78. Hvis det blir funnet en kamp, vil resultatet fortelle deg det relative radnummeret der samsvaret er funnet. Når ingen samsvar blir funnet, returneres # N / A.
- Eventuelle resultater i MATCH-kolonnen som ikke er # N / A, er potensielle situasjoner der en ansatt også fakturerer selskapet som leverandør. Sorter stigende etter MATCH-kolonnen, og eventuelle problemer registreres øverst.
Sak 2:
Uvanlige svinger i leverandørdatabasen
Et selskap har 5000 leverandører. Vi bruker et spredningsdiagram for å visuelt finne de 20 leverandørene som skal revideres.
- Få en liste over leverandør-ID, fakturatall, totalt fakturabeløp for dette året.
- Få en liste over leverandør-ID, fakturatall, totalt fakturabeløp for året før.
- Bruk VLOOKUP for å matche disse listene med fem kolonner med data:
- Legg til nye kolonner for Count Delta og Amount Delta:
- Velg dataene i H5: G5000. Sett inn et spredningsdiagram (XY). De fleste resultatene vil være klumpete i midten. Du er interessert i avvikerne. Start med leverandørene i boksområdet; de sendte færre fakturaer for langt flere totale dollar:
Merk
Hold markøren over punktet for å finne leverandøren som er tilknyttet et punkt. Excel vil fortelle deg antall delta og mengde delta som du skal finne i det originale datasettet.
Sak 3:
Bruke et pivottabell til å bore ned
I dette tilfellet ser vi på fakturaer og fordringer. Gjennom ulike nedtrappinger av dataene, oppdag hvilke to kundefordeltesanalytikere som bruker fredag ettermiddager i baren i stedet for å jobbe.
- Jeg startet med to datasett. Den første er fakturdata, faktura, dato, kunde, beløp.
- De neste dataene er faktura, mottaksdato, mottatt beløp, utvalgsnavn
- Beregn kolonnen Days to Pay. Dette er mottaksdato - fakturadato. Formater resultatet som et tall i stedet for en dato.
- Beregn ukedagen. Dette er
=TEXT(ReceiptDate,"dddd") - Velg en celle i datasettet. Bruk data - pivottabell (Excel 97-2003) eller Sett inn - pivottabell (Excel 2007)
- Det første pivottabellen hadde Days To Pay ned størrelsen. Høyreklikk en verdi og velg Gruppe og Vis detalj - Gruppe. Grupper etter 30 dagers skuffer.
- Flytt dager til betaling til kolonneområdet. Sett kunder i radområdet. Sett inntektene i dataområdet. Du kan nå se hvilke kunder som betaler sakte.
- Fjern Days to Pay og sett Weekday i kolonneområdet. Fjern kunde og legg rep i radområdet. Du kan nå se beløpene mottatt etter ukedag.
- Velg en celle i dataområdet. Klikk på feltinnstillinger-knappen (i pivottabellverktøylinjen i Excel 97-2003 eller i Alternativer-fanen i Excel 2007).
- Klikk Mer i Excel 97-2003. I Excel 2007 klikker du på fanen Vis verdier som. Velg% av rad.
- Resultatet: Bob og Sonia ser ut til å behandle langt færre fakturaer på fredag enn de andre. Kom innom kontoret sitt fredag ettermiddag for å se om (a) de faktisk jobber, og (b) om det er en haug med ubehandlede sjekker som henger ut i pulteskuffen til fredag.
