Hlukanisa iphuzu ku-excel. Imiyalo yokuthola iphuzu lekhefu ku-Excel

Ukuphumelela okuqhubekayo kwenkampani kufanele kugcinwe, futhi ngenxa yalokhu kuyadingeka ukunquma imingcele ephephile yevolumu yokuthengisa. Lolu lwazi lungatholakala ngokusebenzisa iphuzu le-break-ven point. Ake sithole ukuthi iyini, iyini ukusetshenziswa kwayo, nokuthi ungenza kanjani izibalo usebenzisa amathuluzi e-Microsoft Excel.

Ukunikeza iphoyinti lekhefu

Umphumela womsebenzi webhizinisi isikhathi esithile imali engenayo nezindleko. Ukuze uthole izinga lenzuzo, izindleko zisuswa emalini engenayo, kodwa umphumela awuhlali ulungile, ikakhulukazi uma inhlangano isanda kungena emakethe. I-break-ven point yisimo somnotho lapho imali engenayo ihlanganisa izindleko, kodwa inkampani ayikayenzi inzuzo.. Amanani wokuxhumanisa anguziro.

Ukuthola iphuzu lokulinganisa kuletha ukuqonda ukuthi kungakanani okudingeka kukhiqizwe futhi kudayiswe ukuze kuqinisekiswe intuthuko ezinzile. Le nkomba ibalwa ukuze kunqunywe isimo sebhizinisi. Uma kunezinkomba eziphezulu zokukhiqiza nezokuthengisa ngaphezu kwephuzu lokuphumula, inkampani isebenza ngokuzinzile, izingozi zincane. Futhi, ukuhlola isimo kusuka ku-zero kusiza abaphathi ukuthi benze izinqumo ezinkulu - isibonelo, ukwandisa ukukhiqiza kanye nokwethula izindlela ezintsha. Idatha ewumphumela inikezwa abatshalizimali nababolekisi ukuze kuqinisekiswe ukusimama kwenhlangano.

I-Break Even Point Formula ku-Excel

Ungakwazi ukubala amanani endaweni eyiziro usebenzisa le fomula: P*X - FC - VC*X = 0. Amanani aguquguqukayo:

• P - izindleko zomkhiqizo zomthengi;
• U-X umthamo wokukhiqiza;
• FC - izindleko ezingashintshi;
• I-VC izindleko eziguquguqukayo ezitholwa yinkampani ekukhiqizeni iyunithi yomkhiqizo.

Okubili okuguquguqukayo kufomula kuthinta ikakhulukazi inzuzo - inani lomkhiqizo okhiqizwayo kanye nezindleko ezingaguquki. Lezi zinkomba zihlobene, ushintsho lwazo luholela ekwandeni noma ekwehleni kwemali engenayo. Ngaphezu kokulingana kwezimali, kukhona amayunithi emvelo - ukubalwa kwenani lezimpahla kwenziwa ngokuya ngale fomula elandelayo: X = FC/(P - VC). Izindleko ezigxilile (FC) zihlukaniswa ngomehluko phakathi kwentengo (P) nezindleko ezingaguquki (VC) ukuze uthole inani lomkhiqizo odayisiwe odingekayo ukuze uzinze.

Inani lezindleko zokumboza imali engenayo licatshangelwa ngevolumu eyaziwayo yokukhiqiza. Inkomba iphindaphindwa ngezindleko zeyunithi ngayinye yokuhle okukhiqiziwe: P*X. Lapho amafomula adingekayo aziwa, sekuyisikhathi sokuthola ukuthi yiziphi izinkomba ibhizinisi elizoba sesimweni sokungathathi hlangothi.

Hlukanisa isibalo sephoyinti

Izazi zezomnotho zazi izindlela ezimbalwa zokuthola izinkomba ezidingekayo ukuze ufinyelele iphuzu lokuphumula. Ngamunye wabo usebenzisa amaspredishithi e-Microsoft Excel futhi asebenze namafomula.

Imodeli yokubala iphoyinti lebhizinisi

Khumbula! Lapho kunqunywa umzuzu wezomnotho onguziro, kuthathwa izinombolo ezifanele nezibalo.

Ukuthola iphuzu lokuphumula kuyisibonelo esihle sokuthuthukiswa kwenhlangano; empeleni, imiphumela ingase ishintshe ngenxa yokwenyuka okungalindelekile kwezindleko noma ukwehla kwesidingo. Cabangela ukucatshangelwa okusebenzayo ngesikhathi sokubala:

• umthamo wezimpahla ezikhiqizwayo kanye nezindleko zihlobene ngokuqondile;
• umthamo wokukhiqiza kanye nohlobo lomkhiqizo kuhlala kufana;
• intengo nezindleko ezingaguquki zihlala zingashintshile esikhathini esicatshangelwayo;
• inani elikhiqizwayo lilingana nokuthengisa, asikho isitokwe somkhiqizo;
• izindleko eziguquguqukayo zingabikezelwa ngokunemba okuphelele.

Izigaba zokubala iphuzu lekhefu ngokusho kwe-AD Sheremet

Ngokombono wesazi sezomnotho u-AD Sheremet, iphoyinti elinguziro kufanele linqunywe ngezigaba ezintathu. Usosayensi ukholelwa ukuthi izinhlangano zidinga ulwazi mayelana nale nkomba ukuze zihlale endaweni ephephile futhi zinwebe ngangokunokwenzeka. Ake sibheke izinyathelo ezathathwa nguSheremet:

1. Ukuthola ulwazi mayelana nenani lemikhiqizo ekhiqizwayo, imali engenayo kanye nezindleko, izinga lokuthengisa.
2. Ukunqunywa kwezindleko ezingaguquki nezingaphindeleli, nangemuva - iphuzu elinguziro nobubanzi lapho umsebenzi wenhlangano uphephile khona.
3. Ukuhlonzwa kwenani elifanele lezimpahla ezikhiqizwe futhi zidayiselwe inkampani ethile.

Inketho yokuqala yokubala: siyazazi izindleko nevolumu yokuthengisa

Ngokushintsha ifomula yephoyinti elinguziro, sibala intengo yomkhiqizo, ngokumisa okuzokwazi ukuzuza inani eliphakathi nendawo. Ukuze uqale ukubala, udinga ukuthola idatha yokulahlekelwa unomphela kwenhlangano, izindleko zezimpahla nokuthengisa okuhleliwe. Ifomula ibhalwe kanje: P = ((FC + VC(X)/H. I-VC(X) isho ukuthi udinga ukuphindaphinda intengo yezindleko ngenani lezimpahla ezithengisiwe. Imiphumela ngendlela yethebula izobukeka kanje:

Idatha eyaziwayo igqanyiswe ngokubomvu. Ngokuwafaka kufomula, sithola inani lezimpahla ezithengiswa ngama-ruble noma enye imali.

Inketho yesibili yokubala: siyayazi intengo nezindleko

Indlela ethandwa kakhulu yokuthola ukubalwa kwephuzu lekhefu, isetshenziswa ezinhlanganweni ezinokukhiqiza okukhulu. Kudingekile ukuthola ukuthi zingaki izimpahla ezithengisiwe ezizoholela inhlangano ekulahlekelweni kweqanda kanye nenzuzo. Ukunquma le nombolo, kusetshenziswe ifomula yokulingana kwemvelo kwephoyinti le-break-even isetshenziswa: X = FC/(P - VC).

Idatha eyaziwayo yizindleko ezimisiwe neziguquguqukayo, kanye nenani elimisiwe lezimpahla. Ukunquma okulingana nemali, intengo yomkhiqizo iphindaphindwa ngomphumela wevolumu yokuthengisa ngamayunithi omkhiqizo. Ithebula kuleli cala libukeka kanje:

Inketho yesithathu yokubala: yomkhakha wezinsizakalo nezohwebo

Kunzima kumthengisi noma inhlangano yesevisi ukubala iphoyinti lokulinganisa ngoba zonke izimpahla namasevisi anenani elihlukile. Inani eliphakathi ngeke lisebenze - umphumela uzobe unganembile kakhulu. Okuguquguqukayo ekubalweni kwamaphuzu angu-zero kuzoba yinzuzo, le nkomba idlala indima ekuthengisweni.

Inzuzo eqondisiwe yizinga lokumakishwa elitholwa lapho uthengisa umkhiqizo. Ukuze ubale inani elidingekayo lemali engenayo (S), udinga ukwazi inani layo (R) kanye nolwazi olumayelana nezindleko ezigxilile (FC). Imali engenayo iyivolumu yokuthengisa okuhlosiwe ngama-ruble. Ifomula ithi: S = FC/R.

Masenze ithebula elinamanani aziwayo futhi sizame ukunquma imali engenayo edingekayo ukuze kube nokuzinza. Ukuze uthole ivolumu yokuthengisa ngokwemibandela ephathekayo ngokuzayo, sizongeza inani elilinganisiwe lezimpahla. Kulokhu, kusetshenziswa ifomula elandelayo: Sn=S/P. Ngokuhlukanisa inani elilodwa kwelinye, sithola umphumela oyifunayo:

Isibonelo sokubala iphuzu le-break-even ku-Excel

Ukubala kuzokwenziwa ngendlela yesibili, ngoba isetshenziswa kaningi ekusebenzeni. Kuyadingeka ukwenza itafula elinedatha eyaziwayo ngomsebenzi wenkampani - izindleko ezihleliwe, izindleko eziguquguqukayo kanye nenani leyunithi. Ukubonisa ulwazi eshidini kuzosisiza ukuthi senze isibalo sibe lula sisebenzisa ifomula. Isibonelo sethebula eliwumphumela:

Ngokusekelwe kudatha erekhodiwe, kwakhiwa ithebula lesibili. Ikholomu yokuqala iqukethe idatha yevolumu yokukhiqiza - udinga ukudala imigqa eminingana yezikhathi ezahlukene. Okwesibili kuhlanganisa amaseli aphindayo anesamba sezindleko ezingaguquki, izindleko eziguquguqukayo zikukholomu yesithathu. Okulandelayo, inani lezindleko liyabalwa, ikholomu 4 ihlanganiswa nale datha. Ikholomu yesihlanu iqukethe ukubalwa kwemali engenayo ngemuva kokudayiswa kwenani elihlukile lemikhiqizo, kanti eyesithupha - inani lenzuzo yenetha. Nansi indlela ebukeka ngayo:

Izibalo zamakholomu zenziwa kusetshenziswa amafomula. Amagama amaseli angafakwa mathupha. Kukhona enye indlela: faka uphawu "=" emugqeni wokusebenza bese ukhetha iseli oyifunayo, faka uphawu lwezibalo olufunayo bese ukhetha iseli yesibili. Isibalo sizokwenzeka ngokuzenzakalelayo ngokuya ngefomula edaliwe. Cabangela izinkulumo zokubala idatha kumugqa ngamunye:

• izindleko eziguquguqukayo = umthamo wokukhiqiza * izindleko ezingashintshi;
• izindleko eziphelele = fixed + variable;
• imali engenayo uXNUMXd umthamo wokukhiqiza * izindleko eziphelele;
• imali engenayo encane uXNUMXd - izindleko eziguquguqukayo;
• inzuzo yonke / ukulahlekelwa = imali engenayo - izindleko eziphelele.

Ithebula eliwumphumela libukeka kanje:

Uma ingekho iyunithi yezinhlamvu egcina ngoziro kumphumela, kuzodingeka wenze ezinye izibalo ezengeziwe - ukuthola inani lemajini yokuphepha / imajini ngamaphesenti nasemalini. Leli nani libonisa ukuthi inkampani ikude kangakanani nephuzu le-breakeven. Dala amakholomu amabili engeziwe kuthebula.

Ngokwefomula yemajini yokuphepha ngokwemibandela yemali, udinga ukususa kunani ngalinye lemali engenayo lelo nani layo elihle, eliseduze kakhulu noziro. Ngendlela eyenziwe lula, ibhalwe kanje: I-KBden uXNUMXd Vfact (imali engenayo yangempela) - Wtb (imali engenayo endaweni yezokuphepha).

Ukuze uthole iphesenti lokuphepha, kufanele uhlukanise inani lomkhawulo wemali wokuphepha ngenani lemali engenayo yangempela bese uphindaphinda inombolo engumphumela ngo-100: KB% u100d (KBden / Vactual) * XNUMX%. Iphoyinti lokuphumula linganqunywa ngokunembe kakhudlwana onqenqemeni lokuphepha, njengoba kuboniswe esithombeni esingezansi:

Indlela Yokuhlela Ishadi Lephuzu Lekhefu ku-Excel

Igrafu ikhombisa ngokusobala ukuthi inzuzo iba nkulu nini kunokulahlekelwa. Ukuyihlanganisa, sizosebenzisa amathuluzi e-Excel. Okokuqala udinga ukukhetha ithebhu ethi "Faka" bese uthola into ethi "Amashadi" kuyo. Uma uchofoza inkinobho enalokhu mbhalo, uhlu lwezifanekiso luzovela. Sikhetha isakhiwo sokuhlakazeka - kukhona futhi eziningana zazo, sidinga umdwebo onama-curve ngaphandle kokugoba okubukhali.

Okulandelayo, sinquma ukuthi iyiphi idatha ezovela eshadini. Ngemuva kokuchofoza kwesokudla endaweni emhlophe, lapho umdwebo uzovela khona kamuva, imenyu izovela - udinga into ethi "Khetha Idatha".

Ewindini lokukhetha idatha, thola inkinobho ethi "Engeza" bese uchofoza kuyo. Itholakala ohlangothini lwesobunxele.

Iwindi elisha lizovela esikrinini. Lapho udinga ukufaka ububanzi bamaseli lapho idatha yelinye lamagatsha eshadi itholakala khona. Masiqambe igrafu yokuqala “Isamba sezindleko” – lo musho kufanele ufakwe emgqeni “Igama lochungechunge”.

Ungakwazi ukuphendula idatha iyenze igrafu ngendlela elandelayo: udinga ukuchofoza umugqa othi “X Values”, ubambe iseli eliphezulu lekholomu bese uhudulela ikhesa phansi ekupheleni. Senza okufanayo ngomugqa "Values ​​Y". Esimweni sokuqala, udinga ukukhetha ikholomu "Inani lezimpahla", okwesibili - "Izindleko eziphelele". Lapho zonke izinkambu zigcwalisiwe, ungachofoza "OK".

Chofoza okuthi "Engeza" futhi efasiteleni lokukhetha idatha - iwindi elifanayo nelangaphambilini lizovela. Igama lochungechunge manje selithi "Imali Ephelele". Amanani angu-X abhekisela kudatha ekumaseli wekholomu “Inani Lezinto”. Inkambu ethi “Y Values” kufanele igcwaliswe, kugqanyiswe ikholomu ethi “Isamba Semali Engenayo”.

Manje ungachofoza inkinobho ethi "KULUNGILE" efasiteleni elithi "Khetha Umthombo Wedatha", ngaleyo ndlela uyivale. Igrafu enemigqa ephambanayo ivela endaweni yeshadi. Iphoyinti le-intersection iphuzu lokuhlukana.

Lapho kudingeka izibalo ezinemininingwane, zijwayeze ukusebenzisa

Ukuthola iphuzu lokuphumula kusiza ezindaweni ezihlukahlukene lapho uhlangothi lwezezimali ludlala indima ebalulekile. Ngaphakathi kwenkampani, izibalo zingenziwa umhlaziyi wezezimali, umqondisi wokuthuthukiswa noma umnikazi. Ukwazi amanani wephuzu elingu-zero kuzosiza ukuqonda ukuthi ibhizinisi linenzuzo nini, likusiphi isimo endaweni ethile ngesikhathi. Uhlelo lokuthengisa lungadwetshwa ngokunembe kakhudlwana, ngokwazi iphuzu lekhefu.

Uma umbolekisi noma umtshali-zimali enedatha eyanele mayelana nenkampani, angakwazi futhi ukunquma ukwethembeka kwenhlangano ngephuzu lekhefu futhi anqume ukuthi kufanelekile yini ukutshala imali kuyo.

Izinzuzo kanye nokubi kwemodeli yephuzu lokuhlukana

Inzuzo eyinhloko yalo modeli ukulula kwayo. Izindlela ezintathu zokunquma indawo yokuhlukana zingaphakathi kwamandla awo wonke umuntu one-Microsoft Excel kudivayisi yakhe. Inkinga ukuthi imodeli inemibandela futhi inomkhawulo. Ngokwenza, izinguquko ezingalindelekile kwesinye sezinkomba zingase zenzeke, ngenxa yokuthi imiphumela yezibalo ingabhekwa njengento engenamsebenzi. Uma isidingo semikhiqizo singazinzile, akunakwenzeka ukucacisa inani eliqondile lokuthengisa kusengaphambili. Kuphinde kuthonywe ezinye izici - isibonelo, ikhwalithi yomsebenzi womnyango wokumaketha.

Isiphetho

Ukubala iphoyinti lokulinganisa kuwumkhuba owusizo wamabhizinisi asebenza isikhathi eside anesidingo esizinzile semikhiqizo. Ngokugxila kule nkomba, ungahlela uhlelo lokusebenza isikhathi esithile kusengaphambili. Iphuzu lekhefu libonisa ukuthi ingakanani ivolumu yokukhiqiza nokuthengisa inzuzo ihlanganisa ngokuphelele ukulahlekelwa, inquma indawo yokuphepha yenkampani.