10 formulas, ko izmantojam budžeta modelēšanā

Praktiski visus finanšu modeļus mēs veidojam Microsoft Excel programmā, jo tas sniedz lielu elastību darbā un ir saprotams ikvienam klientam. MS Excel ir populārākā izklājlapu programma pasaulē; to izmanto gan iesācēji, lai veiktu vienkāršas darbības vai uzskaitītu datus savām vajadzībām, gan advancēti lietotāji ikdienas darbam ar lielu datu plūsmu. Šajā rakstā esam apkopojuši svarīgākās un visbiežāk izmantotās formulas, veidojot budžeta modeļus klientiem.

1. IF funkcija.

Tu visticamāk jau esi dzirdējis par IF funkciju – ar to ir iespējams veidot dažādus loģiskus nosacījumus (kas notiks, ja izdarīsim darbību X?). Piemēram, =IF(A2=”AG Capital”;1;2) nozīmē: ja A2 šūnā ierakstīts “AG Capital”, tad Excel atgriezīs 1; pretējā gadījumā 2.

Mūsu budžeta modeļos izmantojam IF funkciju, piemēram, skaitot debitoru un kreditoru bilances atlikumus katrā mēnesī, jo tie ir atkarīgi no pirms un pēcapmaksas dienām. Papildus, IF funkcija noder formatējot modeli, ar IF palīdzību izņemot liekus datus.

2. COUNTIF funkcija

COUNTIF funkcija noteiktā diapazonā saskaita šūnas, kas atbilst kādam lietotāja izvēlētam kritērijam. Ar COUNTIF palīdzību var noskaidrot, piemēram, cik reizes darbinieku sarakstā parādās cilvēki, kas pārdevuši vairāk par 30 produktiem mēnesī. Microsoft Excel piedāvā vienkāršu piemēru formulas izprašanai: COUNTIF(Kur vēlies meklēt?, Ko vēlies meklēt?).

Budžeta modelēšanā COUNTIF funkciju izmantojam, piemēram, lai saskaitītu, cik veikali atvērušies vai cik produkti pārdoti tieši tekošajā mēnesī nevis kopā.

3. IFERROR funkcija

IFERROR funkciju pārsvarā izmanto, lai formatētu modeļus. Funkcija meklē kļūdas, un ja tās atrod, atgriež lietotāja norādīto vērtību. IFERROR sintakse izskatās šādi: IFERROR(vērtība, vērtība_ja_kļūda), un funkcija pārbauda šādas kļūdas: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? vai #NULL!.

Visbiežākā IFERROR funkcijas pielietošana budžeta modeļos ir vēl neaizpildītu lauku kontrolēšana, kur datu vietā ir 0 un Excel izdod kļūdu #DIV/0! (dalīts ar 0). IFERROR formula novērš šo kļūdu un modelis ir vieglāk lasāms.

Nested IF funkcijas

Nested funkcijas visvienkāršāk izprast, domājot par tām kā “funkciju funkcijā”. Tās ļauj pārbaudīt vairākus kritērijus un iespējamo iznākumu skaits pieaug. Piemēram, lai piešķirtu burta atzīmi noteiktiem skaitļiem, var izmantot funkciju =IF(A3>89,”A”,IF(A3>79,”B”, IF(A3>69,”C”,IF(A3>59,”D”,”F”)))) – ja skaitlis, kas ir šūnā A3 būs lielāks par 89, tam tiks piešķirts A burts, ja lielāks par 69 – C burts, ja lielāks par 59 – D burts, un visos citos gadījumos tas tiks novērtēts ar F.

Mūsu budžeta modeļos Nested IF funkcijas sastopamas, piemēram, prognozējot ienākumus, kas ir atkarīgi no apjoma. Ja apjoms ir X, tad cena ir A, ja apjoms pieaug un ir Y, tad cena ir B – to visu ierakstām Nested IF funkcijā.

4. SUM funkcija

Šī funkcija ir, vienkāršākā, noderīgākā un arī visplašāk izmantotā. Tā ļauj aizvietot kalkulatoru un momentāli saskaitīt vērtības.

SUM funkcija mūsu budžeta modeļos tiek diezgan bieži izmantota, lai saskaitītu kopā atsevišķu struktūrvienību datus un klients redzētu galveno – kopskatu. Tā tiek pielietota gan lai saskaitītu ienākumus, izmaksas, krājumus, iepirkumus un citas pozīcijas.

5. SUMPRODUCT funkcija.

SUMPRODUCT izmantojams, lai sareizinātu divas kolonnas vai rindas un pēc tam saskaitītu šo rezultātu kopā. Piemēram, lai aprēķinātu kopējo pārdošanas apjomu, ir vispirms jāsareizina cena ar pārdoto apjomu un tikai tad jāsaskaita viss kopā. SUMPRODUCT funkcija ļauj to izdarīt vienā solī ar sekojošu formulu =SUMPRODUCT(masīvs1, [masīvs2], [masīvs3], …). Savā visvienkāršākajā formā masīvs nozīmē šūnu diapazonu, piemēram C6:C14.

Mēs pielietojam SUMPRODUCT funkciju, lai aprēķinātu kopējo apgrozījumu atkarībā no vairākiem produktiem vai pakalpojumiem.

6. SUMIF funkcija.

SUMIF funkciju izmanto, lai ātri un vienkārši saskaitītu tikai tās vērtības, kas atbilst norādītam kritērijam. Piemēram, ja vēlies kādā kolonnā saskaitīt tikai tās vērtības, kas lielākas par 100, izmanto šādu formulu: SUMIF(D3:D30,”>100“)

 

7. VLOOKUP funkcija

Taču vieglāk šī funkcija izprotama ar uzskatāmu piemēru:

Microsoft iesaka izmantošanai XLOOKUP funkciju, kas ir jaunāka un uzlabota VLOOKUP versija, jo tā darbojas jebkurā virzienā kā arī atvieglo lietošanu, jo nav jānorāda kolonnas numurs:

8. INDEX/MATCH funkcijas

Izmantojot VLOOKUP funkciju, iespējami saskarties ar dažiem ierobežojumiem, piemēram, vērtību var uzmeklēt tikai no kreisās puses uz labo vai arī pirmajai references kolonnai jābūt specifiskai, lai funkcija strādātu. Tāpēc izstrādāta arī INDEX/MATCH funkcija, kura strādā visnotaļ līdzīgi:

VLOOKUP (HLOOKUP), XLOOKUP, kā arī INDEX/MATCH funkcijas budžeta modeļos mēs izmantojam, lai precīzi atlasītu datus, kad nepieciešams uzrādīt kādu specifisku skaitli/aprēķinu, vai arī kā papilddarbību pirms citu darbību veikšanas.

MIN funkcijā programmā Excel atgriezīs pašu mazāko skaitli no lietotāja noteikta diapazon, savukārt, MAX funkcija atgriezīs lielāko skaitli no lietotāja noteikta diapazona. Funkcija AVERAGE atgriež vidējo aritmētisko vērtību no noteikta diapazona. Piemēram, ja Excel lapas diapazons B43:B60 satur skaitļus, formula =AVERAGE (B43:B60) saskaitīs visus skaitļus šajā diapazonā un dalīs to summu uz vērtību skaitu.

Budžeta modeļos MIN, MAX un AVERAGE funkcijas izmanto, lai parādītu vidējās, minimālās un maksimālās izmaksas, ieņēmumus, veikalu skaitu, darbinieku skaitu, utt. Šis skaitlis dod ātru skatu uz svarīgākajām pozīcijām pirms iedziļināšanās detaļās. AVERAGE funkciju izmanto lai aprēķinātu vidējo bruto darba algu uzņēmumā, savukārt, MIN un MAX funkcijas arī plaši pielietojamas motivācijas sistēmā, kad darbiniekam tiek aprēķināta prēmija, ja izdosies sasniegt noteiktu apjomu pārdošanā.