Analiza e ndjeshmërisë në Excel (Shembull i fletës së të dhënave)

Proceset në fushën e financave janë gjithmonë të ndërlidhura - një faktor varet nga një tjetër dhe ndryshon me të. Ndiqni këto ndryshime dhe kuptoni se çfarë të prisni në të ardhmen, ndoshta duke përdorur funksionet e Excel dhe metodat e tabelave.

Marrja e rezultateve të shumta me një tabelë të dhënash

Aftësitë e fletës së të dhënave janë elementë të analizës "çka-nëse"—shpesh bëhen përmes Microsoft Excel. Ky është emri i dytë për analizën e ndjeshmërisë.

Përmbledhje

Një tabelë e të dhënave është një lloj gamë qelizash që mund të përdoren për të zgjidhur problemet duke ndryshuar vlerat në disa qeliza. Krijohet kur është e nevojshme të mbahen gjurmët e ndryshimeve në përbërësit e formulës dhe të marrin përditësime për rezultatet, sipas këtyre ndryshimeve. Le të zbulojmë se si të përdorim tabelat e të dhënave në kërkime dhe cilat lloje janë ato.

Bazat rreth tabelave të të dhënave

Ekzistojnë dy lloje të tabelave të të dhënave, ato ndryshojnë në numrin e komponentëve. Ju duhet të përpiloni një tabelë me fokus në numrin e vlerave uXNUMXbuXNUMXbqë duhet të kontrolloni me të.

Statisticienët përdorin një tabelë të vetme variabël kur ka vetëm një ndryshore në një ose më shumë shprehje që mund të ndryshojë rezultatin e tyre. Për shembull, shpesh përdoret në lidhje me funksionin PMT. Formula është krijuar për të llogaritur shumën e pagesës së rregullt dhe merr parasysh normën e interesit të specifikuar në marrëveshje. Në llogaritjet e tilla, variablat shkruhen në një kolonë, dhe rezultatet e llogaritjeve në një tjetër. Një shembull i një pllake të dhënash me 1 ndryshore:

Më pas, merrni parasysh pllakat me 2 ndryshore. Ato përdoren në rastet kur dy faktorë ndikojnë në ndryshimin e çdo treguesi. Dy variablat mund të përfundojnë në një tabelë tjetër të lidhur me kredinë, e cila mund të përdoret për të përcaktuar periudhën optimale të shlyerjes dhe shumën e pagesës mujore. Në këtë llogaritje, duhet të përdorni edhe funksionin PMT. Një shembull i një tabele me 2 ndryshore:

Krijimi i një tabele të dhënash me një ndryshore

Konsideroni metodën e analizës duke përdorur shembullin e një librari të vogël me vetëm 100 libra në magazinë. Disa prej tyre mund të shiten më shtrenjtë (50 dollarë), pjesa tjetër do t'i kushtojë blerësit më pak (20 dollarë). Të ardhurat totale nga shitja e të gjitha mallrave janë llogaritur - pronari vendosi që ai të shiste 60% të librave me një çmim të lartë. Ju duhet të zbuloni se si do të rriten të ardhurat nëse rritni çmimin e një vëllimi më të madh të mallrave - 70%, e kështu me radhë.

Kushtojini vëmendje! Të ardhurat totale duhet të llogariten duke përdorur një formulë, përndryshe nuk do të jetë e mundur të përpilohet një tabelë e të dhënave.

1. Zgjidhni një qelizë të lirë larg skajit të fletës dhe shkruani formulën në të: =Qelia e të ardhurave totale. Për shembull, nëse të ardhurat janë shkruar në qelizën C14 (tregohet përcaktimi i rastësishëm), duhet të shkruani këtë: =S14.
2. Ne shkruajmë përqindjen e vëllimit të mallrave në kolonën në të majtë të kësaj qelize - jo poshtë saj, kjo është shumë e rëndësishme.
3. Ne zgjedhim gamën e qelizave ku ndodhet kolona e përqindjes dhe lidhja me të ardhurat totale.

4. Ne gjejmë në skedën "Të dhënat" artikullin "Po nëse analiza" dhe klikojmë mbi të - në menunë që hapet, zgjidhni opsionin "Tabela e të dhënave".

5. Do të hapet një dritare e vogël ku duhet të specifikoni një qelizë me përqindjen e librave të shitur fillimisht me një çmim të lartë në kolonën "Zëvendësoni vlerat sipas rreshtave në ...". Ky hap bëhet për të rillogaritur të ardhurat totale duke marrë parasysh përqindjen në rritje.

Pas klikimit të butonit “OK” në dritaren ku janë futur të dhënat për përpilimin e tabelës, rezultatet e llogaritjeve do të shfaqen në rreshta.

Shtimi i një formule në një tabelë të dhënash të ndryshueshme të vetme

Nga një tabelë që ndihmoi në llogaritjen e një veprimi me vetëm një ndryshore, mund të bëni një mjet analize të sofistikuar duke shtuar një formulë shtesë. Duhet të futet pranë një formule tashmë ekzistuese - për shembull, nëse tabela është e orientuar nga rreshti, ne e fusim shprehjen në qelizën në të djathtë të asaj ekzistuese. Kur vendoset orientimi i kolonës, ne shkruajmë formulën e re nën atë të vjetër. Më pas, ndiqni algoritmin:

1. Zgjidhni përsëri gamën e qelizave, por tani duhet të përfshijë formulën e re.
2. Hapni menunë e analizës "çka nëse" dhe zgjidhni "Datasheet".
3. Shtojmë një formulë të re në fushën përkatëse në rreshta ose kolona, ​​në varësi të orientimit të pllakës.

Krijoni një tabelë të dhënash me dy variabla

Fillimi i një tabele të tillë është paksa i ndryshëm - duhet të vendosni një lidhje me të ardhurat totale mbi vlerat e përqindjes. Më pas, ne kryejmë këto hapa:

1. Shkruani opsionet e çmimeve në një rresht me një lidhje me të ardhurat - një qelizë për çdo çmim.
2. Zgjidhni një gamë qelizash.

3. Hapni dritaren e tabelës së të dhënave, si kur përpiloni një tabelë me një variabël – përmes skedës “Data” në shiritin e veglave.
4. Zëvendësoni në kolonën "Zëvendësoni vlerat sipas kolonave në ..." një qelizë me një çmim fillestar të lartë.
5. Shtoni një qelizë me përqindjen fillestare të shitjeve të librave të shtrenjtë në kolonën "Zëvendësoni vlerat sipas rreshtave në ..." dhe klikoni "OK".

Si rezultat, e gjithë tabela është e mbushur me shuma të të ardhurave të mundshme me kushte të ndryshme për shitjen e mallrave.

Përshpejtoni llogaritjet për fletët e punës që përmbajnë tabela të të dhënave

Nëse keni nevojë për llogaritje të shpejta në një tabelë të dhënash që nuk shkaktojnë një rillogaritje të të gjithë librit të punës, ka disa gjëra që mund të bëni për të shpejtuar procesin.

1. Hapni dritaren e opsioneve, zgjidhni artikullin "Formulat" në menynë në të djathtë.
2. Zgjidhni artikullin "Automatik, përveç tabelave të të dhënave" në seksionin "Llogaritjet në librin e punës".

3. Le të rillogaritim rezultatet në tabelë me dorë. Për ta bërë këtë, zgjidhni formulat dhe shtypni tastin F.

Mjete të tjera për kryerjen e analizës së ndjeshmërisë

Ka mjete të tjera në program për t'ju ndihmuar të kryeni analizën e ndjeshmërisë. Ata automatizojnë disa veprime që përndryshe do të duhej të bëheshin manualisht.

1. Funksioni "Zgjedhja e parametrave" është i përshtatshëm nëse dihet rezultati i dëshiruar dhe ju duhet të dini vlerën hyrëse të ndryshores për të marrë një rezultat të tillë.
2. "Kërkimi për një zgjidhje" është një shtesë për zgjidhjen e problemeve. Është e nevojshme të vendosni kufij dhe t'i drejtoni ato, pas së cilës sistemi do të gjejë përgjigjen. Zgjidhja përcaktohet duke ndryshuar vlerat.
3. Analiza e ndjeshmërisë mund të bëhet duke përdorur Scenario Manager. Ky mjet gjendet në menunë e analizës what-if nën skedën e të dhënave. Ai zëvendëson vlerat në disa qeliza - numri mund të arrijë në 32. Dispeçeri i krahason këto vlera në mënyrë që përdoruesi të mos ketë nevojë t'i ndryshojë ato me dorë. Një shembull i përdorimit të menaxherit të skriptit:

Analiza e ndjeshmërisë së një projekti investimi në Excel

Analiza "Çfarë nëse" është veçanërisht e dobishme në situatat kur kërkohet parashikimi, siç është investimi. Analistët përdorin këtë metodë për të zbuluar se si do të ndryshojë vlera e aksioneve të një kompanie si rezultat i ndryshimeve në disa faktorë.

Metoda e analizës së ndjeshmërisë së investimeve

Kur analizoni "çka nëse" përdorni numërimin - manual ose automatik. Gama e vlerave është e njohur dhe ato zëvendësohen në formulë një nga një. Rezultati është një grup vlerash. Zgjidhni numrin e duhur prej tyre. Le të shqyrtojmë katër tregues për të cilët kryhet analiza e ndjeshmërisë në fushën e financave:

1. Vlera aktuale neto – llogaritet duke zbritur shumën e investimit nga shuma e të ardhurave.
2. Norma e brendshme e kthimit/fitimit – tregon se sa fitim kërkohet të merret nga një investim në një vit.
3. Raporti i kthimit është raporti i të gjitha fitimeve ndaj investimit fillestar.
4. Indeksi i zbritur i fitimit - tregon efektivitetin e investimit.

Formulë

Ndjeshmëria e futjes mund të llogaritet duke përdorur këtë formulë: Ndryshimi në parametrin e daljes në % / Ndryshimi në parametrin e hyrjes në %.

Parametrat e daljes dhe të hyrjes mund të jenë vlerat e përshkruara më herët.

1. Ju duhet të dini rezultatin në kushte standarde.
2. Ne zëvendësojmë një nga variablat dhe monitorojmë ndryshimet në rezultat.
3. Ne llogarisim ndryshimin në përqindje të të dy parametrave në lidhje me kushtet e vendosura.
4. Përqindjet e fituara i fusim në formulë dhe përcaktojmë ndjeshmërinë.

Një shembull i analizës së ndjeshmërisë së një projekti investimi në Excel

Për një kuptim më të mirë të metodologjisë së analizës, nevojitet një shembull. Le të analizojmë projektin me të dhënat e mëposhtme të njohura:

1. Plotësoni tabelën për të analizuar projektin mbi të.

2. Ne llogarisim fluksin e parasë duke përdorur funksionin OFFSET. Në fazën fillestare, fluksi është i barabartë me investimet. Më pas, ne aplikojmë formulën: =IF(OFFSET(Numri,1;)=2;SUM(Prurje 1:Dalje 1); SUM(Prurje 1:Dalje 1)+$B$ 5)
   Emërtimet e qelizave në formulë mund të jenë të ndryshme, në varësi të paraqitjes së tabelës. Në fund, shtohet vlera nga të dhënat fillestare - vlera e shpëtimit.

3. Ne përcaktojmë periudhën për të cilën projekti do të paguajë. Për periudhën fillestare, ne përdorim këtë formulë: = SHUMË (G7: G17;»<0″). Gama e qelizave është kolona e fluksit të parasë. Për periudha të mëtejshme, ne zbatojmë këtë formulë: =Periudha fillestare+IF(First e.stream>0; First e.stream;0). Projekti është në pikën më të ulët në 4 vjet.

4. Ne krijojmë një kolonë për numrat e atyre periudhave kur projekti shpërblehet.

5. Ne llogarisim kthimin e investimit. Është e nevojshme të bëhet një shprehje ku fitimi në një periudhë të caktuar kohore ndahet me investimin fillestar.

6. Ne përcaktojmë faktorin e zbritjes duke përdorur këtë formulë: =1/(1+disk.%) ^ Numri.

7. Ne llogarisim vlerën aktuale duke përdorur shumëzimin - fluksi i parasë shumëzohet me faktorin e zbritjes.

8. Le të llogarisim PI (indeksi i përfitimit). Vlera aktuale me kalimin e kohës ndahet me investimin në fillim të projektit.

9. Le të përcaktojmë normën e brendshme të kthimit duke përdorur funksionin IRR: =IRR(Rapësi i fluksit të parasë).

Analiza e ndjeshmërisë së investimeve duke përdorur fletën e të dhënave

Për analizën e projekteve në fushën e investimeve, metodat e tjera janë më të përshtatshme se tabela e të dhënave. Shumë përdorues përjetojnë konfuzion kur përpilojnë një formulë. Për të zbuluar varësinë e një faktori nga ndryshimet në të tjerët, duhet të zgjidhni qelizat e sakta për futjen e llogaritjeve dhe për leximin e të dhënave.

Analiza e faktorëve dhe dispersionit në Excel me automatizimin e llogaritjes

Një tjetër tipologji e analizës së ndjeshmërisë është analiza e faktorëve dhe analiza e variancës. Lloji i parë përcakton marrëdhënien midis numrave, i dyti zbulon varësinë e një ndryshoreje nga të tjerët.

ANOVA në Excel

Qëllimi i një analize të tillë është të ndajë ndryshueshmërinë e vlerës në tre komponentë:

1. Ndryshueshmëria si rezultat i ndikimit të vlerave të tjera.
2. Ndryshimet për shkak të marrëdhënies së vlerave që ndikojnë në të.
3. Ndryshime të rastësishme.

Le të bëjmë analizën e variancës përmes shtesës Excel "Analiza e të dhënave". Nëse nuk është i aktivizuar, mund të aktivizohet te cilësimet.

Tabela fillestare duhet të ndjekë dy rregulla: ka një kolonë për secilën vlerë, dhe të dhënat në të janë të renditura në rend rritës ose zbritës. Është e nevojshme të kontrollohet ndikimi i nivelit të arsimimit në sjelljen në konflikt.

1. Gjeni mjetin Analiza e të dhënave në skedën Të dhëna dhe hapni dritaren e tij. Në listë, ju duhet të zgjidhni analizën e njëanshme të variancës.

2. Plotësoni rreshtat e kutisë së dialogut. Intervali i hyrjes është të gjitha qelizat, duke përjashtuar titujt dhe numrat. Grupi sipas kolonave. Ne i shfaqim rezultatet në një fletë të re.

Meqenëse vlera në qelizën e verdhë është më e madhe se një, supozimi mund të konsiderohet i pasaktë - nuk ka asnjë lidhje midis edukimit dhe sjelljes në konflikt.

Analiza e faktorëve në Excel: një shembull

Le të analizojmë marrëdhënien e të dhënave në fushën e shitjeve - është e nevojshme të identifikohen produktet e njohura dhe jo të njohura. Informacioni fillestar:

1. Duhet të zbulojmë se për cilat mallra u rrit më shumë kërkesa gjatë muajit të dytë. Ne po përpilojmë një tabelë të re për të përcaktuar rritjen dhe rënien e kërkesës. Rritja llogaritet duke përdorur këtë formulë: =IF((Kërkesa 2-Kërkesa 1)>0; Kërkesa 2- Kërkesa 1;0). Formula e zvogëlimit: =IF(Rritja=0; Kërkesa 1- Kërkesa 2;0).

2. Llogaritni rritjen e kërkesës për mallra si përqindje: =IF(Rritja/Rezultati 2 =0; Zvogëlimi/Rezultati 2; Rritja/Rezultati 2).

3. Le të bëjmë një tabelë për qartësi - zgjidhni një varg qelizash dhe krijoni një histogram përmes skedës "Fut". Në cilësimet, duhet të hiqni mbushjen, kjo mund të bëhet përmes mjetit Format Data Series.

Analiza e dyanshme e variancës në Excel

Analiza e variancës kryhet me disa variabla. Konsideroni këtë me një shembull: ju duhet të zbuloni se sa shpejt shfaqet reagimi ndaj një tingulli me volum të ndryshëm tek burrat dhe gratë.

1. Ne hapim "Analizën e të dhënave", në listë ju duhet të gjeni një analizë të dyanshme të variancës pa përsëritje.
2. Intervali i hyrjes - qelizat që përmbajnë të dhëna (pa titull). Ne i shfaqim rezultatet në një fletë të re dhe klikojmë "OK".

Vlera F është më e madhe se F-kritike, që do të thotë se dyshemeja ndikon në shpejtësinë e reagimit ndaj zërit.

Përfundim

Në këtë artikull, analiza e ndjeshmërisë në një spreadsheet Excel u diskutua në detaje, në mënyrë që çdo përdorues të jetë në gjendje të kuptojë metodat e aplikimit të saj.