Hæ! Bruker du Excel til månedsrapportering? Til å konsolidere tall fra datterselskaper i konsernet? Til å utarbeide cash-flow forecast? Til å lage en femårsprognose til strategien? Til ad-hoc analyser av kostnadsutvikling? Til å holde følge med de galopperende kostnadene på skytjenesten din?
Du skammer deg over at hele virksomheten din styres av en programvare som kom i første versjon for 36 år siden. Selskapet ditt har brukt en liten formue på en Business Intelligence Platform, og så sitter finansavdelingen der og skriver SUMIFS i Excel. ERP systemet ditt har en budsjettmodul, men avdelingslederne lager prognoser i en delt Excel-fil. Du går på konferanser og hører om det siste nye innenfor AI-drevet forretningsanalyse, mens du googler «VLOOKUP» en gang i måneden.
Du er heldigvis ikke alene. Og kanskje er det ikke så mye å skamme seg over hvilke verktøy du benytter, det er hva du gjør med det som betyr noe.
Og du gjør det feil! Mer om det senere.
Hvorfor blir Excel verktøyet der "alt ender opp"?
Igjennom å ha samhandlet med andre ledere, kontrollere, Investment Bankers og slipskledde PE trainees primært igjennom regneark i et tiår eller to, så begynner det å bli klart for meg hvorfor Excel fremdeles er "go-to" verktøyet for de som skal få jobben gjort:
1. “Management Adjustments”. Ja, bade CRM-et ditt og ERP systemet ditt har en rapporteringsfunksjon. Du kan jo bare ta en rapport rett fra kilden? Men det er alltid noe der som må justeres. Kanskje en kontrakt som var registret feil? En valutakurs som ikke stemmer overens med virkeligheten? Eller en postering som påvirker sammenlignbarheten over flere år? Eller en avtale som var bokført på feil konto? Vi fikser det i Excel.
2. Uforståelige feltnavn. Skal vi se på “Amount.loc.curr.1” eller “Amount.loc.curr.2”? Datamodellen til et ERP-system er sjeldent designet med lesbarhet i tanke, og noe enhver Controller vil skjule for ledelsen. Vi fikser det i Excel.
3. Kontoplaner fra helvete. Hva er forskjellen på inntekter som føres som «konsulentvirksomhet», «rådgivning», eller kostnader som ligger på «IT innkjøp» og «IKT innkjøp»? Vi fikser det i Excel.
4. Sammenstilling av data. Kanskje du har et datterselskap som kjører et eget regnskapssystem du sliter med å konsolidere inn i ditt ERP? Kanskje de fører i NGAAP og konsernet i IFRS? Vi fikser det i Excel.
5. «Styret ønsket en ny rapport». Et nytt styremedlem som ønsker rapport i et vannfallsdiagram? Vi fikser det i Excel.
6. «Hva er CAGR over 5 år?» Det er alltid en besserwisser som ber om nok en analysene av tallene. Kan de ikke bare analyser selv? Hen kan fikse det i Excel.
Så løsningen blir fremdeles ofte å bruke Excel til å justere, konsolidere, sammenstille, presentere og an analysere viktige tall fra det er for komplekst å fikse det i kilden. Men hvordan gjøre det på en måte som er effektivt og robust?
Software Design Patterns
Så hva har økonomer å lære av en utrangert utvikler når de skal sette opp Excel-arkene sine? Hvordan man håndterer økende kompleksitet, avhengigheter og fremtidige endringer.
La oss gå tilbake til 70-tallet da utvikling av moderne programvarespråk for alvor tok fart. Den fremdeles ganske så akademiske disiplinen fant opp stadig nye utviklingsspråk for å gjøre det enklere og mer effektivt å utvikle programvare, og for å kunne løse stadig mer komplekse problemer.
Men å løse mer komplekse problemer skapte mer komplisert kode, og veldig mye repetisjon. Utviklere skyr repetitive oppgaver og elsker gjenbruk, så mye av koden som kunne gjenbrukes blir lagt i såkalte biblioteker (mange av dem fremdeles i bruk).
Samtidig utviklet det seg, igjennom prøving og feiling, god praksis for hvordan lignende problemer burde løses, og dermed var Software Design Patterns født. Design Patterns brukes blant annet til å abstrahere kompleksitet og til å dele opp store oppgaver i mindre biter.
Så hva har dette med Excel å gjøre? Vi skal ta to veldig kjente (for en utvikler) Design Patterns for å rydde opp i både Excel og din tilnærming til Excel.
ETL – Extract, Transform, Load
ETL er et enkelt pattern i tre (hoved)steg:
1. Extract – Hente ut data fra en eller flere kilder
2. Transform – Tilpasse, massere, fikse og modifisere dataene.
3. Load – Laste data inn i mottakende system (i vårt eksempel – Excel!)
Når vi benytter dette mønsteret så får vi:
Delt opp en litt kompleks oppgave inn i tre deler
Utviklet noe som er repeterbart og som enkelt kan endres på
Lagt grunnmuren for en robust måte å hente og oppdatere data fra kildesystemer på.
La oss kikke litt nærmere på de ulike stegene, og hvordan du får dette til i Excel.
Extract
Sjansen er stor for at når du sitter og svetter over et Excel-ark så kommer dataene fra ett eller annet sted utenfor regnearket ditt. Ofte fra en annet Excel-fil, men er du heldig så har du kanskje en kobling direkte inn i datakilden. Og i Excel er det mange muligheter til å hente inn data!
Problemet er at mange av måtene Excel kan hente inn data fra andre kilder er lite robust. Har du noen gang åpnet et svært Excel ark og funnet ut at alle verdiene du hentet fra en annen kilde har blitt erstattet med #REF?
Konserncontrollerens skrekk! Hvordan var nå den SUMIF formelen igjen?
Når vi skal hente inn data fra Excel så vil vi gjøre det på en måte som helst ikke krasjer hele regnearket ditt fordi Sven på innkjøp har omorganisert mappestrukturen sin. Den mest robuste måten å gjøre dette på er i PowerQuery (som også er bygget rundt ETL patternet).
I eksempelet under (veldig forenklet) så skal jeg bruke to filer. En Excel-fil med budsjett for 2024 og en CSV-fil med et fiktivt uttrekk fra regnskapssystemet.
Budsjett 2024 (Excel):
Regnskap ser sånn ut når man later som det ikke finnes en balansekonto.
Faktiske tall fra regnskapssystemet (semikolon-separert tekstfil):
Her er det flere veier til Rom, men siden vi skal laste inn data fra to forskjellige filer så velger jeg å åpne PowerQuery editoren direkte fra Data -> Get Data -> Launch Powerquery editor. Dette åpner et nytt vindu.
Eh, er dette Excel?
Så må vi begynne med å laste inn data. Velg «New Source» og finn frem Excel filen med budsjettet. Her kan man velge hvilket ark eller tabell i Excel dataene skal hentes fra. I mitt eksempel har ikke budsjettet blitt definert som en tabell, så vi henter data fra arbeidsarket.
Dette ser ikke veldig pent ut! Men dataene er der, og vi skal få vasket dette neste steg. La oss bare også laste inn eksporten fra regnskapssystemet først – fremgangsmåten er mye det samme som over, bare husk å velge Text/CSV.
Da har vi nesten fullført «Extract» fasen ETL. Bare en liten detalj gjenstår. Hvordan skal disse to datakildene kobles sammen? Her finnes det flere fremgangsmåter, men vi skal foreløpig ta den enkleste veien til mål. Vi henger den ene datakilden på den andre (Append). Men først må vi massere dataene litt!
Transform
Da er det på tide å rydde opp i de importerte dataene. I vårt tilfelle så har de som laget budsjettet ikke vært så opptatt av å separere data og formatering, men har "optimalisert for lesbarhet". Vi må klare å få dette til å bli et datasett:
Dette steget ville vært lettere hvis data lå i en definert tabell..
Hvilke problemer ser vi er?
Tomme linjer
Summeringslinjer
Ingen header i tabellen
Ikke noe problem, med å benytte noen av funksjonene i PowerQuery, så får vi det til å se slik ut:
Men er dette et godt datasett? Ikke for en utvikler. Jeg vil ha dato som et attributt, ikke som en kolonne. Det gjør det enklere å slå sammen dataene med faktiske tall fra regnskapssystemet, og det blir også mye enklere å lage gode rapporter og analyser senere via f.eks. pivottabeller (Eller SUMIFS). Powerquery funksjonen «unpivot» fikser jobben:
Så gir vi attributt- og verdiparet et godt navn:
Nå skal vi straks kombinere dette med faktiske regnskapstall. Men hvordan skal vi da kunne skille mellom budsjett og regnskap? Vi legger til en ny kolonne og fyller den med verdien «Budsjett».
Siden vi skal slå sammen disse to datasettene må vi gjøre noen mindre endringer i eksporten fra regnskapssystemet. Kolonnene må hete det samme i begge filene, ellers får vi nye kolonner og mer rot å rydde opp i.
(Uttrekket fra ERP systemet mangler en «beskrivelse» kolonne, men det kan vi rydde opp i senere).
Da går vi tilbake til spørringen mot budsjettfilen og henger ved spørringen til regnskapssystemet.
Da er vi nesten i mål! Det kan også være greit å sjekke at kolonnene har den datatypen du ønsker (dato, valuta, tekst etc). Men så har vi et lite problem med at beskrivelsen er tom for regnskapstallene. Det vil bli et problem senere når vi skal lage rapporter.
Dette kan løses på flere måter. Og her kommer robustheten i PowerQuery frem.
Ta et nytt uttrekk fra regnskapssystemet og få med beskrivelsen denne gang. Trykk refresh.
Manuelt skriv inn verdier (Ikke anbefalt)
Ha en egen oppslagstabell for kontonummer og kontonavn (anbefalt)
Workaround: Lag en ny beskrivelseskolonne med følgende logikk:
Fyll inn med nok "Else If".
Når dette er rettet opp i, så er dataene klar til å lastes inn i Excel.
Load
Resultatet fra alt arbeidet over blir til en tabell i Excel.
Men blir ikke dette bare nok en kopi av dataene? Hvorfor kan jeg ikke lenke rett inn i kildedataene?
Dette er ikke en kopi, men et uttrekk. Hvis verdier i kildedatasettet ditt endres så kan du, enten automatisk eller manuelt, oppdatere denne tabellen uten å måtte endre noe av logikken. For hver gang du oppdaterer datasettet, så kjøres logikken vi møysommelig laget i steget over.
Og bonusen er at vi får et datasett som er robust mot feil med kildedata. Skulle kildefilene forsvinn, så vil dette datasettet være tilgjengelig i siste brukbare versjon. (Graceful Degradation of Service, som vi sier på norsk)
Men dette er jo ikke slik jeg vil at dataene skal fremstå i rapporten! Nei, og da kan vi gå videre til det neste software patterns som bør benyttes i Excel 😊
Model-View-Controller (MVC)
MVC er et mye brukt Software Pattern fra slutten av 70-tallet. Det ble først benyttet i tradisjonell programvare basert på objektorienterte språk, men i dag er det også svært vanlig i web-basert programvare. Det ble opprinnelig utviklet av nordmannen Trygve Reenskaug, en pioneer innenfor forskning og utvikling innenfor objektorienterte språk og programmering.
Ideen med MVC er å separere tre hoveddeler av en programvare for å minske kompleksitet, og for å lett kunne oppdatere en komponent uten at resten av programvaren blir berørt. MVC består av tre deler.
Model – Dvs datasettet, logikken og reglene som styrer hvordan dataene henger sammen og kan manipuleres. I vårt enkle tilfelle er tabellen over modellen. Denne komponenten av programvaren «skjules for sluttbrukerne som kun forholder seg til de neste to delene.
View – Representasjonen av dataene fra modellen. Det kan for eksempel være en tabell i seg selv eller en graf.
Controller. Den delen av programvaren som lar brukeren manipulere visningen av dataene (og dataene selv). For eksempel en knapp eller et filter.
Her er en enkel illustrasjon av MVC.
Når vi benytter dette mønsteret så får vi:
Separert data fra formatering og visning.
Separert oppdatering av data fra oppdatering av visningen
Separert manipulering av visninger fra manipulering av data.
La oss kikke litt nærmere på de ulike stegene, og hvordan du får dette til i Excel.
Vi har modellen (forrige prosess), hva med View og Controller? La oss si at vi nå lager en pivottabell av modell-dataene våre.
Tallene er hentet fra tabellen (Model), men kan vises (View) på ulike måter ved å manipulere (Controller) filtre eller trekke felter inn eller ut av feltvelgeren.
Man kan også, helt uten å endre modellen, lage en ny visning av dataene. Denne får ingen konsekvens for mine andre visningeer - men jeg kan (hvis jeg ønsker) la de samme kontrollene styre ulike visninger. Under vises dataene som løpende total av året, en fin måte å visualisere om selskapet ligger foran eller etter budsjettet sitt:
Man kan selvfølgelig også lage sitt eget oppsett, hvis man ikke ønsker å bruke pivotfunksjonene. Her er et eksempel på en egenutviklet rapport:
Det kan se litt komplisert ut å lage formlene her? Her kommer nok et utvikler-triks. Som sagt er utviklere allergisk mot repetisjon. I dette tilfelle er formelen for alle verdiene som hentes fra Modellen lik, og bare kopieres til alle kolonner:
For å gjenta meg selv til det kjedsommelige. Separer data fra visning og manipulering. Ikke ødelegg datamodellen din fordi du vil trenger litt «luft» i regnearket.
«Ja, men nå skal vi i gang med PowerBI»
Vent til du har mestret Excel og prinsippene over. Først når du forstår ETL og MVC patterns, og får til å bruke PowerQuery, så har du kanskje en sjans til å komme i gang med PowerBI. Læringskurven fra Excel til PowerBI er bratt nok som den er (PowerBI benytter også PowerQuery til å laste inn data i modellen, og koblingene fra Excel kan benyttes direkte i PowerBI).
Her er et enkelt eksempel der vi har gjenbrukt koblingene fra Excel – men nå i PowerBI.
La det være sagt – PowerBI kan være å skyte spurv med kanon for mye av den Adhoc rapporteringen som foregår rundt omkring i finansavdelinger. Dette er først og fremst et verktøy for dataanalytikere. Det lager gode, langt-levende og avanserte rapportdashboard, men krever også mer kunnskap av de som skal bruke dem. Hvis ledergruppen din ikke klarer å endre et filter i en pivottabell så er de neppe klar for å få servert flotte analyser i PowerBI.
Ekspert-triks – Lage en avansert modell i Excel
I eksempelet over brukte vi PowerQuery til å slå sammen data fra regnskap og budsjett, og laste de rett inn i et regneark. Hvis man jobber med mer avanserte datamodeller, eller modeller med mye data som tar lang tid å laste, så kan det være en fordel å laste de ulike datakildene rett til Excel Modellen og bygge en datamodell der.
Så setter du opp relasjoner i datamodellen mellom de ulike datasettene (obs- Støtter ikke mange-til-mange koblinger).
Deretter dukker datamodellen opp i pivot-verktøyet, og det er bare å sette i gang å lage gode visualiseringer av datamodellen din:
Oppsummering - God Excel praksis
Excel er bedre enn sitt rykte, og for mange oppgaver så er det enklere å bruke enn Tableau, PowerBI, Qlik, og andre plattformer for dataanalyse .
Men hvis du ikke vil sitte å editere formler i det uendelige, rive deg i håret over at balansen ikke går opp eller vente tre minutter på at alle dine VLOOKUPS skal kalkulere ferdig, så må det en god praksis til. Start med å sikre at data fra eksterne kilder lastes inn på en god måte, og at du skiller data fra visning.
Lykke til!