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

///
Comment0
/
Categories

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.

if

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.

if

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?).

countif

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ā.

countif

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!.

iferror

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.

iferror

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.

nested-if

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ā.

nested if

 

 
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

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.

sum

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.

sumproduct

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“)

 sumif
Mūsu gadījumā SUMIF funkcija tiek pielietota, piemēram, lai no grāmatvedības datiem budžeta failā uzreiz saskaitītu noteiktu struktūrvienību apgrozījumu/izmaksas, u.c. (saskaitīt apgrozījumu visiem gada mēnešiem tikai tam veikalam, kurš atrodas tirdzniecības centrā “Alfa”.
7. VLOOKUP funkcija

VLOOKUP (/HLOOKUP) izmanto, lai ātri un vienkārši atrastu datus tabulā, piemēram, lai ātri atrastu kāda darbinieka pārdoto apjomu vai ID numuru. Funkcijas sintakse ir sekojoša: =VLOOKUP(ko uzmeklēt, kur meklēt, atbildes kolonnas numurs no izvēlētā diapazona).

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

vlookup

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:

xlookup

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:

index-match

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.

vlookup

 

9. MIN, MAX un AVERAGE funkcijas

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.

min-max-average

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ā.

10. ADDRESS & INDIRECT funkcijas
ADDRESS funkcija tiek pielietota, lai iegūtu kādas šūnas adresi Excel, ja zināmi tās rindas un kolonnas numuri. Tā var atrast adresi arī šūnām no citām lapām. Savukārt, ar INDIRECT, šo adresi var pārvērst par saturu, parādot skaitli vai tekstu vai jebko citu, kas atrodas adresētajā šūnā. INDIRECT formula kalpo par atsauci uz citām šūnām vai darblapām. Tā lieti noder gadījumā, ja izmainīt formulu nav iespējams vai nav vēlams, taču jebkādu iemeslu dēļ ir jāmaina atsauce uz šūnu.
Mūsu budžeta modeļos šīs funkcijas tiek bieži vien apvienotas ar vēl citām formulām, un tās atvieglo budžeta faila lietošanu gadījumos, kad paredzēti dažādi scenāriji un dati atrodas dažādās darblapās.
address-and-indirect-gif
Tāpat budžeta modeļu veidošanā tiek izmantotas daudzas citas Excel palīglietas, piemēram, atsauces no vienas lapas uz citiem dokumentiem. Formulas bieži vien tiek kombinētas un vienā šūnā mēdz tikt izmantotas pat 3-5 formulas. Galu galā budžeta modeļi sastāv no aptuveni 16 līdz pat 24 lapām, kas piepildītas gan ar vienkāršām, gan sarežģītām formulām.

 

Ja vēlies uzzināt vairāk par katru no aprakstītājām formulām, spied šeit:
SUM https://support.microsoft.com/lv-lv/office/funkcija-sum-043e1c7d-7726-4e80-8f32-07b23e057f89
SUMPRODUCT https://support.microsoft.com/lv-lv/office/sumproduct-funkcija-sumproduct-16753e75-9f68-4874-94ac-4d2145a2fd2e
SUMIF https://support.microsoft.com/lv-lv/office/funkcija-sumif-169b8c99-c05c-4483-a712-1697a653039b
IF https://support.microsoft.com/lv-lv/office/funkcija-if-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
COUNTIF https://support.microsoft.com/lv-lv/office/countif-funkcija-countif-e0de10c6-f885-4e71-abb4-1f464816df34
IFERROR https://support.microsoft.com/lv-lv/office/iferror-funkcija-c526fd07-caeb-47b8-8bb6-63f3e417f611
VLOOKUP https://support.microsoft.com/lv-lv/office/vlookup-funkcija-vlookup-0bbc8083-26fe-4963-8ab8-93a18ad188a1
XLOOKUP https://support.microsoft.com/lv-lv/office/funkcija-xlookup-b7fd680e-6d10-43e6-84f9-88eae8bf5929
INDEX/MATCH https://support.microsoft.com/lv-lv/office/v%C4%93rt%C4%ABbu-uzmekl%C4%93%C5%A1ana-izmantojot-funkciju-vlookup-index-vai-match-68297403-7c3c-4150-9e3c-4d348188976b
MIN https://support.microsoft.com/lv-lv/office/funkcija-min-61635d12-920f-4ce2-a70f-96f202dcc152
MAX https://support.microsoft.com/lv-lv/office/funkcija-max-e0012414-9ac8-4b34-9a47-73e662c08098
AVERAGE https://support.microsoft.com/lv-lv/office/average-funkcija-average-047bac88-d466-426c-a32b-8f33eb960cf6
INDIRECT https://support.microsoft.com/lv-lv/office/indirect-funkcija-indirect-474b3a3a-8a26-4f44-b491-92b6306fa261
ADDRESS https://support.microsoft.com/lv-lv/office/funkcija-address-d0c26c0d-3991-446b-8de4-ab46431d4f89

Leave a Reply