Bvisa kunyange point in excel. Mirayiridzo yekuwana iyo yekutyora-kunyange poindi muExcel

Kubudirira kunoenderera mberi kwekambani kunofanira kuchengetedzwa, uye nokuda kweizvi zvakakosha kugadzirisa miganhu yakachengeteka yehuwandu hwekutengesa. Ruzivo urwu runogona kuwanikwa uchishandisa break-even point. Ngationei kuti chii, chii chiri kushandiswa kwayo, uye maitiro ekuita masvomhu uchishandisa maturusi eMicrosoft Excel.

Kugovera nzvimbo yekuzorora

Mhedzisiro yebasa rebhizinesi kweimwe nguva yenguva mari uye mutengo. Kuti uwane huwandu hwemhindu, mari inobviswa kubva pamari, asi mhedzisiro haisi yakanaka nguva dzose, kunyanya kana sangano richangobva kupinda mumusika. Iyo break-even point imamiriro ehupfumi apo mari inovhara mari, asi kambani haisati yaita purofiti.. Iyo coordinate values ​​ndeye zero.

Kutora nzvimbo yekuzorora kunounza kunzwisisa kwehuwandu hunoda kugadzirwa uye kutengeswa kuitira kuti uve nechokwadi chebudiriro yakagadzikana. Ichi chiratidzo chinoverengerwa kuona mamiriro ebhizinesi. Kana paine yakakwira kugadzirwa uye zviratidzo zvekutengesa pamusoro pekuputsika-kunyange poindi, kambani inoshanda zvakadzikama, njodzi ishoma. Zvakare, kuongorora mamiriro kubva pa zero kunobatsira mamaneja kuita sarudzo huru - semuenzaniso, kuwedzera kugadzirwa uye kuunza nzira nyowani. Iyo data inoguma inopihwa kune vanoisa mari uye vanokweretesa kuti vasimbise kusimba kwesangano.

Bvisa Even Point Formula muExcel

Unogona kuverenga kukosha pa zero point uchishandisa iyi formula: P*X - FC – VC*X = 0. Variable values:

• P - mutengo wechigadzirwa kune mutengi;
• X ihuwandu hwekugadzirwa;
• FC - mari yakatarwa;
• VC ndiyo mutengo unochinja unowanikwa nekambani mukugadzira chikamu chechigadzirwa.

Zvikamu zviviri mufomula zvinonyanya kukanganisa purofiti - huwandu hwechigadzirwa chakagadzirwa uye mari isiri-yakagadziriswa. Izvi zviratidzo zvakabatana, shanduko yavo inotungamira mukuwedzera kana kuderera kwemari. Pamusoro pekuenzana kwemari, kune masikirwo emayuniti - kuverenga kwehuwandu hwezvinhu kunoitwa maererano neinotevera formula: X = FC/(P - VC). Fixed cost (FC) inokamurwa nemusiyano uripakati pemutengo (P) nemitengo isingachinji (VC) kuti uwane huwandu hwechigadzirwa chinotengeswa chinodiwa kuti chigadzikane.

Huwandu hwemari inovhara mari inotariswa pahuwandu hunozivikanwa hwekugadzirwa. Chiratidzo chinowedzerwa nemutengo pachikamu chezvakanaka zvinogadzirwa: P*X. Kana mafomula anodiwa achizivikanwa, yave nguva yekuziva kuti ndezvipi zviratidzo bhizinesi richange riri mune kwarakarerekera.

Break even point calculation

Nyanzvi dzezvehupfumi dzinoziva nzira dzakati wandei dzekutsvaga zviratidzo zvinodikanwa kuti urove padanho. Mumwe nemumwe wavo anoshandisa Microsoft Excel maspredishiti uye anoshanda nemafomula.

Iyo modhi yekuverenga iyo break-even point yebhizinesi

Rangarira! Pakusarudza iyo zero nguva yehupfumi, nhamba dzakakodzera uye masamu anotorwa.

Kutora nzvimbo yekuputsika ndiyo muenzaniso wakanaka wekusimudzira sangano; muchokwadi, zvigumisiro zvinogona kuchinja nekuda kwekuwedzera kusingatarisirwi mumitengo kana kuderera kwekuda. Funga nezve fungidziro dzinoshanda panguva yekuverenga:

• huwandu hwezvinhu zvinogadzirwa uye mutengo unowirirana;
• kugadzirwa kwesimba uye rudzi rwechigadzirwa zvinoramba zvakafanana;
• mutengo uye mari dzisiri dzakagadziriswa dzinoramba dziripo panguva yakatarwa;
• huwandu hunogadzirwa hwakaenzana nekutengesa, hapana chigadziro chechigadzirwa;
• mitengo yakasiyana-siyana inogona kufanotaurwa nekururama kwakakwana.

Matanho ekuverenga iyo break-even point zvinoenderana neAD Sheremet

Zvinoenderana nedzidziso yemuongorori wezvehupfumi AD Sheremet, zero point inofanirwa kutariswa mumatanho matatu. Musayendisiti anotenda kuti masangano anoda ruzivo nezve chiratidzo ichi kuitira kuti arambe ari munzvimbo yakachengeteka uye nekuiwedzera zvakanyanya sezvinobvira. Ngatitarisei matanho akatorwa naSheremet:

1. Kuwana ruzivo pamusoro pehuwandu hwezvigadzirwa zvinogadzirwa, mari uye mari, chiyero chekutengesa.
2. Kutemerwa kweyakagadziriswa uye isingadzokororwe mari, uye mushure - zero point uye huwandu umo basa resangano rakachengeteka.
3. Kuzivikanwa kwehuwandu hwakakodzera hwezvinhu zvinogadzirwa uye zvinotengeswa kune imwe kambani.

Yekutanga yekuverenga sarudzo: isu tinoziva mutengo uye huwandu hwekutengesa

Nekugadzirisa zero point formula, tinoverenga mutengo wechigadzirwa, nekugadzirisa izvo zvichave zvichiita kuti uwane kukosha kwakashata. Kutanga kuverenga, iwe unofanirwa kuwana data pamusoro pekurasikirwa kwesangano zvachose, mutengo wezvinhu uye zvakarongwa zvekutengesa. Iyo formula yakanyorwa seizvi: P = (FC + VC(X))/H. VC(X) zvinoreva kuti iwe unofanirwa kuwedzera mutengo wemutengo nehuwandu hwezvinhu zvinotengeswa. Mhedzisiro muchimiro chetafura inotaridzika seizvi:

Iyo inozivikanwa data inoratidzirwa mutsvuku. Nekuvaisa muhuwandu, tinowana huwandu hwezvinhu zvinotengeswa muRubles kana imwe mari.

Yechipiri yekuverenga sarudzo: isu tinoziva mutengo uye mutengo

Iyo inonyanya kufarirwa nzira yekuziva kuverenga kweiyo break-even point, inoshandiswa mumasangano ane kugadzirwa kukuru. Izvo zvinodikanwa kuti uzive kuti zvingani zvinhu zvinotengeswa zvinotungamira sangano kune zero kurasikirwa uye purofiti. Kuti uone nhamba iyi, iyo formula yeyakasikwa yakaenzana neye break-even point inoshandiswa: X = FC/(P - VC).

Inozivikanwa data inogadziriswa uye inochinja mari, pamwe chete nemutengo wakatarwa wezvinhu. Kuti uone kuenzana kwemari, mutengo wechigadzirwa unowedzerwa nehuwandu hwekutengesa kunoguma mumayuniti echigadzirwa. Tafura mune iyi kesi inoita seizvi:

Yechitatu yekuverenga sarudzo: yebasa rebasa uye kutengeserana

Zvakaoma kumutengesi kana sangano rebasa kuti riverenge nzvimbo yekupwanya nekuti zvinhu zvese nemasevhisi zvine mutengo wakasiyana. Kukosha kwepakati hakuzoshandi - chigumisiro chichave chisina kururama. Iyo inoshanduka mu zero point calculation ichave purofiti, chiratidzo ichi chinoita basa mukutengesa.

Kubudirira kwechinangwa ndiyo mwero wemark-up inogamuchirwa paunenge uchitengesa chigadzirwa. Kuti uverenge huwandu hunodiwa hwemari (S), iwe unofanirwa kuziva kukosha kwayo (R) uye ruzivo nezve yakatemwa mitengo (FC). Revenue ndiyo inotarirwa kutengesa huwandu mumaruble. Formula ndeiyi: S = FC/R.

Ngatigadzire tafura ine hunhu hunozivikanwa uye tiedze kuona mari inodiwa mukugadzikana. Kuti uwane huwandu hwekutengesa mune zvenyama mune ramangwana, isu tichawedzera inofungidzirwa mutengo wezvinhu. Nekuda kweizvi, iyo inotevera formula inoshandiswa: Sn=S/P. Nekukamura kukosha kumwe neimwe, tinowana mhedzisiro inodiwa:

Muenzaniso wekuverenga iyo break-even point muExcel

Iko kuverenga kuchaitwa neyechipiri nzira, sezvo inowanzoshandiswa mukuita. Izvo zvinodikanwa kugadzira tafura ine data inozivikanwa nezvebasa rekambani - yakagadziriswa mari, mutengo unosiyana uye mutengo weyuniti. Kuratidza ruzivo papepa kuchatibatsira kurerutsa kuverenga tichishandisa fomula. Muenzaniso wetafura inobuda:

Kubva pane data rakanyorwa, tafura yechipiri inovakwa. Koramu yekutanga ine data pahuwandu hwekugadzira - iwe unofanirwa kugadzira akati wandei mitsara yenguva dzakasiyana. Yechipiri inosanganisira kudzokorora masero nehuwandu hwemari yakagadziriswa, mari yakasiyana-siyana iri mumutsara wechitatu. Tevere, mutengo wakazara unoverengerwa, koramu 4 inounganidzwa neaya data. Chikamu chechishanu chine kuverenga kwemari yose mushure mekutengeswa kwenhamba yakasiyana-siyana yezvigadzirwa, uye yechitanhatu - chiyero chemari yakawanda. Hezvino zvazvinoita:

Kuverengera kwemakoramu kunoitwa uchishandisa mafomula. Mazita eSero anogona kuiswa nemaoko. Pane imwe nzira: pinda iyo "=" saini mumutsara webasa uye sarudza sero raunoda, isa inodiwa yemasvomhu chiratidzo uye sarudza yechipiri sero. Iko kuverenga kuchaitika otomatiki zvinoenderana neyakagadzirwa formula. Funga nezvemashoko ekuverengera data mumutsara wega wega:

• mari inochinja = vhoriyamu yekugadzira * mari yakatarwa;
• mari yose = yakatarwa + yakasiyana;
• mari uXNUMXd yekugadzira vhoriyamu * yakazara mutengo;
• mari yepasi uXNUMXd mari - inochinja mari;
• mambure purofiti / kurasikirwa = mari - yakazara mutengo.

Tafura inobuda inoita seizvi:

Kana pasina imwe yetambo inopera ne zero mumhedzisiro, iwe uchafanirwa kuita mamwe maverengero - kuti uwane kukosha kweiyo margin yechengetedzo / margin muchikamu uye mumari. Kukosha uku kunoratidza kuti kambani iri kure sei kubva pabreakeven point. Gadzira mamwe makoramu maviri mutafura.

Zvinoenderana neakachengeteka margin formula mumari, iwe unofanirwa kubvisa kubva kune yega yega kukosha kwemari iyo yakanaka kukosha kwayo, iri padyo ne zero. Mune chimiro chakareruka, zvakanyorwa seizvi: KBden uXNUMXd Vfact (chaiyo mari) - Wtb (mari inowanikwa panzvimbo yekuchengetedza).

Kuti uwane huwandu hwekuchengetedza, iwe unofanirwa kugovera kukosha kwemari yechengetedzo nehuwandu hwemari chaiyo uye wowedzera iyo inobuda nhamba ne100: KB% u100d (KBden / Vactual) * XNUMX%. Iyo yekutyora-chero poindi inogona kunyatso kutsanangurwa kubva kumucheto yekuchengetedza, sezvakaratidzwa mumufananidzo uri pazasi:

Maitiro eKuronga Kuputsa Kunyange Point Chati muExcel

Iyo girafu inotaridzika inoratidza panguva ipi iyo purofiti inova yakakura kupfuura kurasikirwa. Kuiunganidza, isu tichashandisa Excel zvishandiso. Kutanga iwe unofanirwa kusarudza iyo "Insert" tab uye uwane iyo "Chati" chinhu pairi. Paunodzvanya bhatani rine chinyorwa ichi, rondedzero yematemplate inobuda. Isu tinosarudza chirongwa chekuparadzira - kunewo akati wandei, tinoda dhayagiramu ine curves isina mabhendi akapinza.

Tevere, isu tinosarudza kuti ndeipi data ichaonekwa pane chati. Mushure mekudzvanya-kurudyi panzvimbo chena, iyo iyo dhizaini ichaonekwa gare gare, menyu ichaonekwa - iwe unoda "Sarudza Data" chinhu.

Muhwindo rekusarudza data, tsvaga "Wedzera" bhatani uye tinya pairi. Inowanikwa kuruboshwe.

Hwindo idzva richaonekwa pachiratidziri. Ikoko iwe unofanirwa kuisa mitsara yemasero umo iyo data yeimwe yematavi echati iripo. Ngatipei zita rekutanga girafu "Zvose zvinodhura" - mutsara uyu unofanirwa kuiswa mumutsara we "Series name".

Iwe unogona kushandura iyo data kuita girafu sezvinotevera: unofanirwa kudzvanya pamutsara we "X Values", bata pasi sero yepamusoro yekoramu uye dhonza cursor kusvika kumagumo. Isu tinoita zvakafanana nemutsara we "Values ​​Y". Muchiitiko chekutanga, iwe unofanirwa kusarudza koramu "Nhamba yezvinhu", mune yechipiri - "Total cost". Kana minda yese yazadzwa, unogona kudzvanya "OK".

Dzvanya "Wedzera" zvakare muhwindo rekusarudza data - hwindo rakafanana nerekare richaonekwa. Zita rakatevedzana rava "Total Income". Iwo ma X values ​​anoreva iyo data mumasero e "Nhamba Yezvinhu" column. Munda we "Y Values" unofanirwa kuzadzwa mukati, uchisimbisa "Total Income" column.

Iye zvino unogona kudzvanya bhatani re "OK" mu "Sarudza Data Source" hwindo, nekudaro uchivhara. Girafu rine mitsetse inopindirana rinoonekwa munzvimbo yechati. Nzvimbo yemharadzano ndiyo nzvimbo yebreakeven.

Apo kuverengera kwakadzama kunodiwa, dzidzira kushandisa

Kutora nzvimbo yekuzorora kunobatsira munzvimbo dzakasiyana siyana uko chikamu chemari chinoita basa rakakosha. Mukati mekambani, kuverenga kunogona kuitwa nemuongorori wezvemari, director director kana muridzi. Kuziva hunhu hwe zero poindi kuchabatsira kunzwisisa kana bhizinesi riine pundutso, mune mamiriro api pane imwe nguva nenguva. Urongwa hwekutengesa hunogona kudhirowa zvakanyatsojeka, kuziva kuputsa-kunyange poindi.

Kana mukweretesi kana investor aine data rakakwana nezvekambani, anogona zvakare kuona kuvimbika kwesangano neiyo break-even point uye kusarudza kana zvakakodzera kuisa mari mairi.

Zvakanakira uye kuipa kweiyo break-even point model

Kubatsira kukuru kwemuenzaniso uyu ndeye nyore. Nzira nhatu dzekuona iyo yekutyora-nyangwe poindi dziri mukati mesimba rechero munhu ane Microsoft Excel pane yavo kifaa. Dambudziko nderekuti modhi ine zvirevo uye inogumira. Mukuita, kuchinja kusingatarisirwi mune chimwe chezviratidzo zvinogona kuitika, nekuda kwekuti migumisiro yekuverenga inogona kunzi isingabatsiri. Kana kudiwa kwezvigadzirwa kusingagadzike, hazvibviri kuona huwandu chaihwo hwekutengesa pachine nguva. Inokonzerwawo nezvimwe zvinhu - semuenzaniso, hutano hwebasa redhipatimendi rekutengesa.

mhedziso

Kuverenga iyo break-even point itsika inobatsira kune mabhizinesi akareba ane kugadzikana kudiwa kwezvigadzirwa. Kutarisa pane chiratidzo ichi, unogona kuronga chirongwa chebasa kwenguva yakati pamberi. Iyo yekutyora-chero poindi inoratidza pahuwandu hwekugadzira uye kutengesa iyo purofiti inovhara zvachose kurasikirwa, inosarudza nzvimbo yekuchengetedza yekambani.