Mēs visi esam tur bijuši. Ceturkšņa beigas, un Tu skaties uz izklājlapu ar tūkstošiem darījumu datu rindu. Ir jāsagatavo atskaite, noviržu analīze, jāprognozē nākamais periods un jāatbild uz desmitiem vadības uzdotu papildu jautājumu – un viss jāizdara jau vakar. Spiediens pieaug, un Tavs uzticamākais rīks ir tas pats, kas gadiem ilgi bijis uz darbvirsmas: Microsoft Excel.
Daudziem finanšu plānošanas un analīzes speciālistiem Excel ir kā otrā valoda. Tas ir pazīstams, uzticams un pieejams gandrīz katrā uzņēmuma datorā. Bet padomā: kā būtu, ja veid, ka līdz šim lieto Excel, atklāj tikai daļu no tā iespējām? Varbūt pašā programmā jau ir rīki, kas var automatizēt garlaicīgākos uzdevumus, mazināt manuālas kļūdas un sniegt daudz skaidrāku priekšstatu par uzņēmuma finanšu veselību?
Šeit nav runa par dārgu, sarežģītu programmatūru vai programmēšanas prasmēm. Runa ir par efektīvāku izmantošanu tam rīkam, kas Tev jau ir. Mēs apskatīsim dažas no efektīvākajām Excel datu analīzes iespējām, sākot no pamata labākās prakses līdz uzlabotām analītiskām tehnikām. Mērķis ir sniegt praktiskus soļus, kurus vari izmantot jau šodien, lai strādātu ātrāk, veiktu dziļāku analīzi un padarītu savu darbu nedaudz vieglāku.
Pirms ķerties pie sarežģītas analīzes, ir jārisina viens būtisks ieradums. Daudzi ar datiem strādā vienkāršā šūnu diapazonā. Tas darbojas, bet ir neefektīvi un rada lielu kļūdu risku. Labākais, ko vari darīt, lai uzlabotu datu apstrādes plūsmu, ir pārvērst savus datus Excel tabulā. Tā ir vienkārša darbība ar būtiskiem ieguvumiem.
Lai pārvērstu datu diapazonu par tabulu, vienkārši noklikšķini jebkurā datu kopas vietā un nospied Ctrl+T. Parādīsies neliels dialoga logs, kurā varēsi apstiprināt datu diapazonu un norādīt, vai Tavā tabulā ir galvene. Atzīmējiet izvēles rūtiņu, spied OK, un darbs padarīts.
Tātad, ko šis vienkāršais taustiņu īsceļš Tev tikko deva?
- Strukturētās atsauces: Aizmirsti manuālu rakstīšanu un atlasīšanu, piemēram, =SUM(C2:C5000). Izmantojot tabulu, Tavas formulas kļūst daudz saprotamākas, piemēram, =SUM(Ienākumi[Summa]). Ja pievienosi jaunas rindas pārdošanas datiem, formula tās automātiski iekļaus. Vairs nekādas manuālas diapazonu labošanas katru mēnesi un uztraukuma, ka kāda rinda varētu būt izlaista.
- Automātiskā formatēšana: Jaunas rindas un kolonnas automātiski pārņem tabulas formatējumu. Tas ļauj atskaitēm izskatīties kārtīgām un profesionālām bez papildu darba.
- Iebūvētā filtrēšana un kārtošana: Tabulas galvenes automātiski iegūst filtru nolaižamos sarakstus, kas ļauj vienkārši kārtot datus vai skatīt konkrētus segmentus, nepiemērojot filtra funkciju manuāli.
- Vienkārši kopsavilkumi: Dodies uz cilni “Tabulas dizains”, kas parādās, kad noklikšķini uz savas tabulas, un atzīmē rūtiņu “Kopsummas rinda”. Tabulas apakšā parādīsies jauna rinda, kurā no nolaižamās izvēlnes varēsi izvēlēties SUM, AVERAGE, COUNT vai citu aprēķinu katrai kolonnai.
Sākt ar pareizi izveidotu Tabulu ir stabils pamats labai datu analīzei programmā Excel. Tā sakārto datus, padara formulas izturīgākas un sagatavo ceļu uz daudz progresīvākiem rīkiem, kurus tūlīt izpētīsim.
Power Query (Datu iegūšana un transformēšana)
Šeit mēs speram lielu soli uz priekšu. Viena no laikietilpīgākajām finanšu analīzes daļām ir datu sagatavošana. Tu saņem datus no ERP, CRM, grāmatvedības sistēmas vai pat no dažiem CSV failiem. Formāti ir neviendabīgi, ir liekas kolonnas, tukšas rindas, un datumi saglabāti kā teksts. Nereti paiet vairākas stundas, kamēr datus iztīra un apvieno, lai vispār varētu sākt analīzi.
Power Query ir radīts, lai novērstu šo manuālo darbu. Tas ir ETL (Extract, Transform, Load – iegūšana, transformēšana, ielāde) dzinējs, kas iebūvēts tieši Excel vidē. Ko tas nozīmē vienkāršā valodā:
- Extract (Iegūšana): pieslēdzies un importē datus no dažādiem avotiem – faila datorā, mapes ar vairākiem failiem, SQL datubāzes, mājaslapas un citiem avotiem.
- Transform (Transformēšana): šeit ir īstā burvība. Tu vari iztīrīt un pārveidot savus datus ar vienkāršu un ērtu saskarni. Var izņemt kolonnas, filtrēt rindas, sadalīt kolonnas, mainīt datu tipus un pat veikt datu “unpivotošanu” – uzdevums, ar ko bieži saskaras FP&A speciālisti, kad mēneši atrodas kolonnu virsrakstos.
- Load (Ielāde): kad dati ir iztīrīti, tos var ielādēt Excel tabulā vai tieši Datu modelī (par to vēlāk).
Svarīgākā Power Query priekšrocība ir tā, ka tas ieraksta katru veikto transformācijas soli. Kad nākamajā mēnesī saņemsi jaunu datu failu, vairs nebūs jāatkārto tīrīšanas process. Pietiek norādīt Power Query uz jauno failu un nospiest “Refresh” – visi tīrīšanas un transformācijas soļi tiks automātiski atkārtoti sekundēs.
Praktisks piemērs: Mēneša pārdošanas failu konsolidācija
Iedomājies, ka katram mēnesim saņem atsevišķu Excel pārdošanas datu failu. Ceturkšņa beigās tos vajag apvienot vienā kopīgā failā analīzei. Lūk, kā to paveikt ar Power Query, nevis ar garlaicīgu “copy-paste”:
- Ievieto visus mēneša pārdošanas failus vienā mapē.
- Excel atver cilni Data.
- Klikšķi Get Data > From File > From Folder.
- Atrodi un atlasi mapi, kurā atrodas faili, un noklikšķi OK.
- Parādīsies priekšskatījuma logs ar visiem šīs mapes failiem. Loga apakšā noklikšķini uz pogas “Combine & Transform Data” (Apvienot un transformēt datus).
- Excel lūgs izvēlēties parauga failu, lai noteiktu datu struktūru. Izvēlies vienu no saviem pārdošanas failiem un noklikšķini OK.
- Tiks atvērts Power Query Editor logs. Šeit Tu redzēsi visus savus datus, kas apvienoti vienā tabulā. Tagad vari veikt nepieciešamo datu attīrīšanu. Piemēram:
○ Ar peles labo pogu noklikšķini uz kolonnas virsraksta un izvēlies “Remove”, lai noņemtu liekās kolonnas.
○ Noklikšķini uz ikonas kolonnas virsrakstā (piem., “ABC” vai “123”), lai mainītu datu tipu uz pareizu Datumu, Valūtu vai Veselu skaitli.
- Kad dati ir sagatavoti un esi apmierināts ar rezultātu, augšējā kreisajā stūrī noklikšķini uz “Close & Load”.
Tavi apvienotie un attīrītie dati tagad ir ielādēti jaunā darblapā kā Excel tabula, gatava analīzei. Nākamajā mēnesī, kad pievienosi jauno pārdošanas failu mapē, vienkārši ar peles labo pogu noklikšķini uz tabulas un izvēlies “Refresh”. Jaunie dati parādīsies uzreiz. Šī vienkāršā darbplūsma katru mēnesi var ietaupīt stundām ilgu garlaicīgu un kļūdām pakļautu darbu.
Pivot Table pārvaldīšana
Kad dati ir sakārtoti un strukturēti tabulā, PivotTable ir ātrākais veids, kā tos apkopot un atrast nozīmīgas tendences. Lielākā daļa finanšu profesionāļu to ir izmantojuši, bet daudzi neiet tālāk par lauku ievilkšanu uz Rindu, Kolonnu un Vērtību kastītēm. Apskatīsim dažas funkcijas, kas sniedz dziļāku finanšu ieskatu.
Aprēķinātie lauki
Tā vietā, lai pievienotu jaunu kolonnu avota datiem, Tu vari veidot aprēķinus tieši PivotTable. Pieņemsim, ka datos ir Pārdošanas ienākumi un Pārdoto preču izmaksas, bet nav Bruto marža.
- Noklikšķini jebkur PivotTable iekšpusē.
● Dodies uz cilni PivotTable Analyze.
● Noklikšķini Fields, Items, & Sets > Calculated Field.
- Piešķir tai nosaukumu, piemēram, Bruto marža.
- Formulu lodziņā ieraksti: =Pārdošanas ienākumi – Pārdoto preču izmaksas.
- Noklikšķini OK.
auns lauks “Bruto marža” tagad parādās Tavā PivotTable, un Tu to vari analizēt tāpat kā jebkuru citu datu punktu. Līdzīgi Tu vari aprēķināt Bruto peļņas procentu ar formulu = (Pārdošanas ienākumi – Pārdoto preču izmaksas) / Pārdošanas ienākumi
Neesi pazīstams ar šiem terminiem? Nekādu problēmu! Apskati mūsu rakstu par finanšu žargona un terminu atšifrēšanu.
Rādīt vērtības kā (Show Values As)
Šī ir ārkārtīgi noderīga funkcija noviržu un veiktspējas analīzei. Pēc noklusējuma Vērtību laukā tiek rādīta Summa vai Skaits. Bet Tu vari mainīt šo iestatījumu, lai rādītu kontekstuālus aprēķinus.
- Noklikšķini ar peles labo pogu uz vērtības savā PivotTable.
● Dodies uz Rādīt vērtības kā (Show Values As).
● Tu redzēsi iespēju sarakstu. Šeit ir dažas vērtīgas FP&A iespējas:
○ % no kopējā kopsummas: Parāda, kāds procents no kopējā rezultāta pienākas katram rindas vienumam. Ideāli, lai redzētu, kura produktu līnija veido lielāko daļu ieņēmumu.
○ % no vecākās rindas kopsummas: Lieliski piemērots detalizētai analīzei. Var redzēt, kādu procentu katrs produkts veido attiecīgajā produktu kategorijā, nevis no kopējā rezultāta.
○ Atšķirība no: Tavs noviržu analīzes rīks. Ja kolonnās ir mēneši, vari parādīt eiro atšķirību no iepriekšējā mēneša. Vienkārši iestati Bāzes lauku uz datuma lauku un Bāzes vienumu uz “iepriekšējo”.
○ Kumulatīvā summa: Ātri izveido kumulatīvu summu laukam, kas lieliski noder, lai sekotu progresam pret ceturkšņa vai gada mērķi.
Slicer un Laika joslas
Statiskie pārskati ir labi, bet interaktīvi paneļi ir vēl labāki, īpaši, ja tos prezentē vadībai. Slicer un Laika joslas ir vizuālie filtri, kas padara Tavu PivotTable interaktīvu.
- Noklikšķini uz sava PivotTable.
- Cilnē PivotTable Analyze noklikšķini uz Insert Slicer.
- Atzīmē laukus, pēc kuriem vēlies filtrēt, piemēram, Reģions, Produktu kategorija vai Pārdošanas pārstāvis.
- Ja ir datuma lauks, noklikšķini uz Insert Timeline. Tas izveido īpašu laika bāzētu filtru, kas ļauj ērti izvēlēties gadus, ceturkšņus, mēnešus vai pat konkrētas dienas.
Tagad Tu vari noklikšķināt uz šiem Slicer un Laika joslas elementiem, lai uzreiz filtrētu savu PivotTable. Tas ļauj Tev un Tavai komandai dinamiskā veidā izpētīt datus sapulces laikā, atbildot uz jautājumiem uz vietas, neveidojot jaunu pārskatu katram scenārijam.
Nākotnes plānošana: “What-If” analīze
Līdz šim mēs koncentrējāmies uz vēsturisko datu analīzi. Taču liela FP&A daļa ir skatīties uz priekšu – budžetēšana, prognozēšana un dažādu biznesa rezultātu modelēšana. Excel What-If Analysis rīki ir veidoti tieši šim mērķim. Tos var atrast cilnē Dati zem nolaižamās izvēlnes What-If Analysis.
Mērķa meklēšana (Goal Seek)
Goal Seek ir vienkāršs, bet efektīvs rīks, kas strādā atpakaļgaitā. Tu zini rezultātu, ko vēlies, un Tev jānoskaidro vienīgais ievades parametrs, kas nepieciešams, lai to sasniegtu.
- Scenārijs: Tu strādā ar vienkāršu peļņas modeli. Sākotnēji pārdod 10 vienības par fiksētu cenu – 50 EUR katra, un pārdoto preču izmaksas veido 30% no pārdošanas cenas. Vienību pārdošana katru mēnesi pieaug par 5%. Pašlaik Tava neto peļņa ir 3 342 EUR. Tu vēlies uzzināt, cik vienību jāpardod, lai sasniegtu neto peļņu 5 000 EUR.
Tagad Tu vari noklikšķināt uz šiem Slicer un Laika joslas elementiem, lai uzreiz filtrētu savu PivotTable. Tas ļauj Tev un Tavai komandai dinamiskā veidā izpētīt datus sapulces laikā, atbildot uz jautājumiem uz vietas, neveidojot jaunu pārskatu katram scenārijam.
Ņem vērā, ka šajā modelī ievades lauks Sākotnējais pārdoto vienību skaits (Initial Quantity Sold) šūnā D4 tiek atsaukts šūnā D13, un pašreizējā neto peļņa šūnā D7 ir vienkārši Neto peļņas (Net Profit) summas rinda 16. Šīs atsauces ir ļoti svarīgas, lai nākamās darbības strādātu pareizi.
- Kā to izmantot:
- Noklikšķini uz What-If Analysis > Goal Seek.
- Set cell (Iestatīt šūnu): izvēlies šūnu, kurā atrodas Pašreizējā neto peļņa (Current Net Profit).
- To value (Vērtība): ieraksti 5000.
- By changing cell (Maināmajā šūnā): izvēlies šūnu, kurā atrodas ievades lauks Sākotnējais pārdoto vienību skaits (Initial Quantity Sold).
- Spied OK.
Excel izies cauri dažādām iespējām un atradīs precīzu vienību skaitu, kas jāpārdod, lai sasniegtu Tavu peļņas mērķi. Tas ir ideāli piemērots ātriem jautājumiem ar vienu mainīgo.
Ja vēlies veidot sarežģītākus finanšu modeļus, sazinies ar mums, un mūsu pasaules līmeņa eksperti jūs apmācīs.
Datu tabulas
Datu tabulas ir ideālas, ja vēlies redzēt, kā izmaiņas vienā vai divos mainīgajos ietekmē galveno rezultātu visā iespēju diapazonā.
- Viena mainīgā datu tabula: Tu vēlies redzēt, kā dažādas procentu likmes ietekmē Tava uzņēmuma Neto ienākumus.
- Izveido nelielu tabulu. Vienā kolonnā uzskaiti iespējamās procentu likmes (piem., 3,0%, 3,5%, 4,0%).
- Šūnā vienu rindu virs un vienu kolonnu pa labi no saraksta, ievieto saiti uz gala Neto ienākumu formulu (piem., =B20).
- Izvēlies visu tabulu (procentu likmju sarakstu un saistīto formulas šūnu).
- Dodies uz What-If Analysis > Data Table.
- Lodziņā Column input cell izvēlies sākotnējo procentu likmes šūnu finanšu modelī.
- Noklikšķini OK. Excel aizpildīs tabulu, rādīdams Neto ienākumus katrai procentu likmei sarakstā.
- Divu mainīgo datu tabula: Tu vēlies redzēt, kā Neto ienākumi mainās atkarībā gan no Vienības cenas, gan Pārdoto vienību skaita.
- Izveido tabulu līdzīgi, bet sarakstu Pārdoto vienību skaits ievieto kolonnā un Vienības cena sarakstu ievieto rindā.
- Stūra šūnā, kur krustojas rinda un kolonna, ievieto saiti uz Neto ienākumu formulu.
- Izvēlies visu tabulu.
- Dodies uz What-If Analysis > Data Table.
- Lodziņā Row input cell izvēlies ievades šūnu Vienības cena jūsu modelī.
- Lodziņā Column input cell izvēlies sākotnējo Pārdoto vienību skaits šūnu savā modelī.
- Noklikšķiniet OK. Excel izveidos režģi, parādot katru iespējamo cenu un apjoma kombināciju, sniedzot spēcīgu jutīguma matricu.
Nākamais līmenis: Datu modelis un Power Pivot
Gadu garumā, ja vajadzēja apvienot datus no dažādām tabulām (piem., pārdošanas dati un klientu dati), bieži izmantotais risinājums bija VLOOKUP vai INDEX/MATCH formula. Šie risinājumi strādā, bet tie var padarīt jūsu failus lēnus, lielus un trauslus. Ja nepieciešams analizēt vairākas lielas datu kopas vienlaicīgi, ir daudz labāks veids: Excel Datu modelis.
Domā par Datu modeli kā par nelielu datu bāzi, kas dzīvo Tavā Excel failā. Tā vietā, lai visus datus sapludinātu vienā milzīgā tabulā ar VLOOKUP formulām, Tu vari ielādēt atsevišķas tabulas modelī un pēc tam definēt attiecības starp tām.
Power Pivot ir interfeiss, ko izmanto, lai pārvaldītu Datu modeli un veidotu uzlabotus aprēķinus. Tas ievieš formulu valodu DAX (Data Analysis Expressions). DAX izskatās līdzīgi parastajām Excel formulām, bet ir daudz spēcīgāka, it īpaši, lai veidotu aprēķinus, kas reaģē uz PivotTable kontekstu.
Kāpēc šis ir labāks piegājiens?
● Veiktspēja: faili ir mazāki un ātrāki. Datu modelis izmanto modernu dzinēju, kas daudz efektīvāk apstrādā miljoniem datu rindu nekā standarta darblapa.
● Vienkāršība: vairs nav nepieciešamas sarežģītas lookup formulas. Vienu reizi izveido attiecību starp divām tabulām (piem., savieno ProductID pārdošanas tabulā ar ProductID produktu tabulā), un tas vienkārši strādā.
● Dziļāka analīze: DAX ļauj veikt aprēķinus, kas parastajās formulās ir grūti vai neiespējami. Piemēram, laika inteliģences funkcijas ļauj viegli aprēķināt gada pieaugumu, konkrētā brīža summas, vai slīdošos vidējos rādītājus.
Vienkāršs piemērs: Pārdošanas analīze ar kalendāra tabulu
- Izmanto Power Query, lai ielādētu savus Pārdošanas datus un atsevišķu Kalendāra tabulu (vienkārša tabula ar katru datumu un kolonnām Gads, Ceturksnis, Mēnesis utt.).
- Ielādējot, izvēlies “Pievienot šos datus Datu modelim”.
- Dodies uz cilni Power Pivot un noklikšķini Pārvaldīt. Tiks atvērts Power Pivot logs.
- Dodies uz Diagrammas skats. Tu redzēsi abas tabulas. Velc datuma lauku no pārdošanas tabulas uz datuma lauku kalendāra tabulā, lai izveidotu attiecību.
- Aizver Power Pivot logu.
- Ievieto jaunu PivotTable, bet šoreiz dialoglodziņā izvēlies “Izmantot šīs darblapas datu modeli”.
- Tagad PivotTable lauku sarakstā redzēsi abas tabulas. Vari vilkt Gadu no kalendāra tabulas uz rindām un Pārdošanas summu no pārdošanas tabulas uz vērtībām. PivotTable pareizi apkopo pārdošanu pa gadiem, pat ja dati nāk no divām atsevišķām tabulām.
Tas ir tikai sākums. Ar DAX Tu varētu izveidot mērījumu, piemēram:
Sales YTD := TOTALYTD(SUM(Sales[Sales Amount]), ‘Calendar'[Date])
lai iegūtu konkrētā brīža aprēķinu, kas automātiski atjaunojas katru gadu.
No datu ievades līdz uz datiem balstītiem stāstiem
Excel ir spēcīga vide, lai pārveidotu neapstrādātus datus par skaidru finanšu stāstu. Pārejot no pamata formulām un izmantojot programmā iebūvētos rīkus, Tu vari fundamentāli mainīt savu darba plūsmu.
Sāc ar maziem soļiem. Šonedēļ mēģini pārvērst vienu no saviem ierastajiem datu diapazoniem par Excel tabulu. Pēc tam ņem kādu atkārtotu datu tīrīšanas uzdevumu un paskaties, vai vari to automatizēt ar Power Query. Eksperimentē ar funkciju “Show Values As” Pivot Table, lai veiktu ātru noviržu analīzi.
Katrs no šiem soļiem ietaupīs Tavu laiku, samazinās manuālo kļūdu risku un ļaus koncentrēties uz patiešām svarīgo: skaitļu interpretāciju, tendences izpratni un ieskatiem, kas palīdz pieņemt labākus biznesa lēmumus. Rīki jau ir Tavā datorā un gaida, kad tos izmantosi.
Uzņēmums AG Capital CFO Services specializējas pielāgotu finanšu risinājumu nodrošināšanā, kas atbilst mūsu klientu unikālajām vajadzībām. Neatkarīgi no tā, vai tie ir neregulāri finanšu direktora pakalpojumi, budžeta veidošana, rentabilitātes analīze vai biznesa vērtēšana, mūsu komanda var palīdzēt uzņēmumiem sasniegt savus finanšu mērķus. Sadarbojoties ar mums, jūs iegūsit piekļuvi plašām finanšu zināšanām, kas ļaus jums ar pārliecību koncentrēties uz sava biznesa attīstību. Neatkarīgi no tā, vai esat iesācējs, MVU jeb liels uzņēmums ar desmitiem miljonu apgrozījumu, mūsu finanšu risinājumi ir izstrādāti, lai palīdzētu jums sasniegt savus mērķus katrā jūsu biznesa attīstības posmā.