Gwirani mfundo mu Excel. Malangizo opezera malo opumira mu Excel

Kupambana kopitilira kwa kampani kuyenera kusungidwa, ndipo chifukwa cha izi ndikofunikira kudziwa malire otetezeka a kuchuluka kwa malonda. Chidziwitso ichi chingapezeke pogwiritsa ntchito mfundo yopuma. Tiyeni tiwone chomwe chiri, ntchito yake, komanso momwe tingawerengere pogwiritsa ntchito zida za Microsoft Excel.

Kupereka malo opumira

Zotsatira za ntchito yabizinesi kwa nthawi inayake ndi ndalama ndi ndalama. Kuti mudziwe kuchuluka kwa phindu, ndalama zomwe zimagwiritsidwa ntchito zimachotsedwa ku ndalama, koma zotsatira zake sizikhala zabwino nthawi zonse, makamaka ngati bungwe lalowa msika posachedwa. Nthawi yopumula ndivuto lachuma pomwe ndalama zimalipira ndalama, koma kampaniyo sinapangebe phindu.. Ma coordinates ndi zero.

Kupeza nthawi yopuma kumabweretsa kumvetsetsa kuchuluka kwa zomwe ziyenera kupangidwa ndikugulitsidwa kuti zitsimikizire chitukuko chokhazikika. Chizindikiro ichi chimawerengedwa kuti chidziwe momwe bizinesi ilili. Ngati pali zisonyezo zazikulu zopanga ndi zogulitsa pamwamba pa nthawi yopuma, kampaniyo imagwira ntchito mokhazikika, zowopsa ndizochepa. Komanso, kuwunika momwe zinthu zilili paziro zimathandizira oyang'anira kupanga zisankho zazikulu - mwachitsanzo, kukulitsa kupanga ndikuyambitsa njira zatsopano. Zotsatira zake zimaperekedwa kwa osunga ndalama ndi obwereketsa kuti atsimikizire kukhazikika kwa bungwe.

Break Even Point Formula mu Excel

Mutha kuwerengera zikhalidwe pa zero pogwiritsa ntchito njira iyi: P*X - FC -Vc*X = 0. Makhalidwe osiyanasiyana:

• P - mtengo wa mankhwala kwa wogula;
• X ndi kuchuluka kwa kupanga;
• FC - ndalama zokhazikika;
• VC ndi mtengo wosinthika womwe kampani imapeza popanga gawo la chinthu.

Zosintha ziwiri mu fomula zimakhudza makamaka phindu - kuchuluka kwa zinthu zopangidwa ndi ndalama zosakhazikika. Zizindikirozi zimagwirizana, kusintha kwawo kumabweretsa kuwonjezeka kapena kuchepa kwa ndalama. Kuphatikiza pazofanana ndi ndalama, pali mayunitsi achilengedwe - kuwerengera kuchuluka kwa katundu kumachitika motsatira njira iyi: X = FC/(P - VC). Ndalama zokhazikika (FC) zimagawidwa ndi kusiyana pakati pa mtengo (P) ndi ndalama zosakhazikika (VC) kuti mupeze kuchuluka kwa zomwe zimagulitsidwa kuti zikhazikike.

Kuchuluka kwa ndalama zogulira ndalama kumaganiziridwa pagulu lodziwika la kupanga. Chizindikirocho chimachulukitsidwa ndi mtengo pagawo lililonse la zabwino zomwe zimapangidwa: P*X. Pamene mafomu ofunikira amadziwika, ndi nthawi yoti mudziwe zomwe bizinesiyo idzakhala yosalowerera ndale.

Dulani mawerengedwe a mfundo

Akatswiri azachuma amadziwa njira zingapo zodziwira zizindikiro zomwe zimafunikira kuti zitheke. Aliyense wa iwo amagwiritsa ntchito Microsoft Excel spreadsheets ndi ntchito ndi mafomula.

Chitsanzo chowerengera malo opuma abizinesi

Kumbukirani! Pozindikira zero nthawi yazachuma, manambala abwino ndi ndalama zimatengedwa.

Kupeza nthawi yopuma ndi chitsanzo chabwino cha chitukuko cha bungwe; kwenikweni, zotsatira zikhoza kusintha chifukwa cha kukwera kosayembekezereka kwa ndalama kapena kuchepa kwa kufunikira. Ganizirani malingaliro omwe amagwira ntchito powerengera:

• kuchuluka kwa zinthu zopangidwa ndi mtengo wake zimagwirizana;
• mphamvu yopanga ndi mtundu wa mankhwala amakhalabe chimodzimodzi;
• mtengo ndi ndalama zosakhazikika zimakhalabe nthawi zonse;
• kuchuluka komwe kumapangidwa kumafanana ndi malonda, palibe katundu wazinthu;
• ndalama zosinthika zitha kuneneratu molondola.

Magawo owerengera nthawi yopuma molingana ndi AD Sheremet

Malinga ndi chiphunzitso cha katswiri wazachuma AD Sheremet, zero point iyenera kutsimikiziridwa mu magawo atatu. Wasayansiyo amakhulupirira kuti mabungwe amafunikira chidziwitso chokhudza chizindikirochi kuti akhalebe pamalo otetezeka ndikukulitsa momwe angathere. Tiyeni tiwone njira zomwe Sheremet adapeza:

1. Kupeza zambiri za kuchuluka kwa zinthu zomwe zimapangidwa, ndalama ndi ndalama zomwe zimagulitsidwa, kuchuluka kwa malonda.
2. Kutsimikiza kwa ndalama zokhazikika komanso zosabwerezeka, ndipo pambuyo - zero point ndi mtundu womwe ntchito ya bungwe ili yotetezeka.
3. Kuzindikiritsa kuchuluka koyenera kwa katundu wopangidwa ndikugulitsidwa kukampani inayake.

Njira yoyamba yowerengera: timadziwa mtengo ndi kuchuluka kwa malonda

Posintha ndondomeko ya zero, timawerengera mtengo wa chinthucho, pokhazikitsa zomwe zingatheke kuti tipeze mtengo wosalowerera. Kuti muyambe kuwerengera, muyenera kupeza deta pazochitika zotayika za bungwe, mtengo wa katundu ndi malonda okonzekera. Njirayi yalembedwa motere: P = (FC + VC(X)/H. VC(X) zikutanthauza kuti muyenera kuchulukitsa mtengo wamtengo wapatali ndi kuchuluka kwa katundu wogulitsidwa. Zotsatira mu mawonekedwe a tebulo zidzawoneka motere:

Deta yodziwika imawonetsedwa mofiira. Powayika mu ndondomekoyi, timapeza kuchuluka kwa katundu wogulitsidwa mu ruble kapena ndalama zina.

Njira yachiwiri yowerengera: timadziwa mtengo ndi mtengo wake

Njira yotchuka kwambiri yodziwira kuwerengera kwa nthawi yopuma, imagwiritsidwa ntchito m'mabungwe omwe ali ndi kupanga kwakukulu. Ndikofunikira kudziwa kuti ndi zinthu zingati zomwe zimagulitsidwa zomwe zingatsogolere bungwe kuti liwononge ziro ndi phindu. Kuti mudziwe nambala iyi, njira yofananira yachilengedwe ya break-ven point imagwiritsidwa ntchito: X = FC/(P - VC).

Deta yodziwika ndi ndalama zokhazikika komanso zosinthika, komanso mtengo wokhazikika wa katundu. Kuti mudziwe kuchuluka kwa ndalama, mtengo wa chinthucho umachulukitsidwa ndi kuchuluka kwa malonda omwe amagulitsidwa m'mayunitsi a chinthucho. Table mu nkhani iyi ikuwoneka motere:

Njira yachitatu yowerengera: gawo lautumiki ndi malonda

Ndizovuta kwa wamalonda kapena bungwe lautumiki kuwerengera nthawi yopuma chifukwa katundu ndi ntchito zonse zimakhala ndi mtengo wosiyana. Mtengo wapakati sudzagwira ntchito - zotsatira zake zidzakhala zolakwika kwambiri. Kusintha kwa chiwerengero cha zero point kudzakhala phindu, chizindikiro ichi chimagwira ntchito pa malonda.

Kupindula komwe mumafuna ndi kuchuluka kwa zomwe mumapeza mukagulitsa. Kuti muwerenge kuchuluka kwa ndalama zomwe zimafunikira (S), muyenera kudziwa mtengo wake (R) komanso zambiri zamitengo yokhazikika (FC). Mtengo ndi kuchuluka kwa ma ruble omwe amagulitsidwa. Fomula ndi: S = FC/R.

Tiyeni tipange tebulo ndi zodziwika bwino ndikuyesera kudziwa ndalama zomwe zimayenera kukhazikika. Kuti tidziwe kuchuluka kwa malonda m'tsogolomu, tidzawonjezera mtengo wamtengo wapatali. Kwa izi, njira yotsatirayi imagwiritsidwa ntchito: Sn=S/P. Pogawa mtengo umodzi ndi wina, timapeza zotsatira zomwe tikufuna:

Chitsanzo chowerengera malo opumira mu Excel

Kuwerengera kudzachitidwa ndi njira yachiwiri, chifukwa imagwiritsidwa ntchito nthawi zambiri. Ndikofunikira kupanga tebulo lokhala ndi chidziwitso chodziwika bwino chokhudza ntchito ya kampani - ndalama zokhazikika, mtengo wosinthika komanso mtengo wagawo. Kuwonetsa zambiri papepala kumatithandiza kuwerengera mosavuta pogwiritsa ntchito fomula. Chitsanzo cha tebulo lotsatira:

Malingana ndi deta yolembedwa, tebulo lachiwiri limamangidwa. Mzere woyamba uli ndi deta pa voliyumu yopanga - muyenera kupanga mizere ingapo kwa nthawi zosiyanasiyana. Chachiwiri chimakhala ndi maselo obwerezabwereza ndi kuchuluka kwa ndalama zokhazikika, ndalama zosinthika zili mu gawo lachitatu. Chotsatira, mtengo wonse umawerengedwa, ndime 4 ikuphatikizidwa ndi deta iyi. Chigawo chachisanu chili ndi chiwerengero cha ndalama zonse pambuyo pogulitsa zinthu zosiyanasiyana, ndipo chachisanu ndi chimodzi - kuchuluka kwa phindu. Izi ndi zomwe zikuwoneka:

Kuwerengera kwa mizati kumachitika pogwiritsa ntchito mafomu. Mayina amafoni amatha kulembedwa pamanja. Palinso njira ina: lowetsani chizindikiro "=" mu mzere wa ntchito ndikusankha selo lomwe mukufuna, ikani chizindikiro cha masamu chomwe mukufuna ndikusankha selo lachiwiri. Kuwerengera kudzachitika zokha molingana ndi formula yomwe idapangidwa. Ganizirani mawu owerengera deta mumzere uliwonse:

• mtengo wosinthika = kuchuluka kwa kupanga * ndalama zokhazikika;
• ndalama zonse = zokhazikika + zosinthika;
• ndalama zopangira uXNUMXd * ndalama zonse;
• ndalama zocheperako uXNUMXd ndalama - ndalama zosinthika;
• phindu / kutayika = ndalama zonse - ndalama zonse.

Tebulo lotsatira likuwoneka motere:

Ngati palibe zingwe zomwe zimathera ndi ziro pazotsatira, muyenera kuwerengeranso zina - kuti mudziwe mtengo wa malire a chitetezo / malire mu kuchuluka ndi ndalama. Mtengo uwu ukuwonetsa momwe kampaniyo ilili kutali ndi malo opumira. Pangani mizati iwiri yowonjezera patebulo.

Malinga ndi njira yachitetezo pazandalama, muyenera kuchotsa pamtengo uliwonse wamtengo wapatali womwe uli pafupi ndi ziro. M'njira yosavuta, zalembedwa motere: KBden uXNUMXd Vfact (ndalama zenizeni) - Wtb (ndalama pamalo otetezedwa).

Kuti mudziwe kuchuluka kwa chitetezo, muyenera kugawa mtengo wachitetezo chandalama ndi kuchuluka kwa ndalama zenizeni ndikuchulukitsa chiwerengerocho ndi 100: KB% u100d (KBden / Vactual) * XNUMX%. Malo opumira amatha kutsimikiziridwa molondola kuchokera pamphepete mwachitetezo, monga momwe tawonetsera pachithunzichi:

Momwe Mungasankhire Tchati cha Break Even Point mu Excel

Grafuyo ikuwonetsa nthawi yomwe phindu limakhala lalikulu kuposa kutayika. Kuti tichite zimenezi, tidzagwiritsa ntchito zida za Excel. Choyamba muyenera kusankha "Ikani" tabu ndi kupeza "Machati" katundu pa izo. Mukadina batani lolemba izi, mndandanda wa ma templates udzawonekera. Timasankha chiwembu chobalalitsa - palinso angapo a iwo, timafunikira chithunzi chokhala ndi ma curve opanda mapindika akuthwa.

Kenaka, timadziwa zomwe zidzawonekere pa tchati. Pambuyo podina kumanja pamalo oyera, pomwe chithunzicho chidzawonekera pambuyo pake, menyu idzawonekera - muyenera chinthu cha "Sankhani Data".

Pazenera la kusankha kwa data, pezani batani la "Add" ndikudina pamenepo. Ili kumanzere.

Zenera latsopano lidzawonekera pazenera. Kumeneko muyenera kulowa m'magulu a maselo omwe deta ya nthambi imodzi ya tchati ili. Tiyeni titchule graph yoyamba "Zowonongeka zonse" - mawuwa ayenera kulembedwa pamzere "Dzina la mndandanda".

Mukhoza kutembenuza deta kukhala graph motere: muyenera kudina pamzere "X Values", gwirani pansi pa selo pamwamba pa ndime ndikukokera cholozera mpaka kumapeto. Timachita chimodzimodzi ndi mzere "Values ​​Y". Choyamba, muyenera kusankha ndime "Nambala ya katundu", yachiwiri - "Total cost". Magawo onse akadzazidwa, mukhoza dinani "Chabwino".

Dinani "Add" kachiwiri pawindo losankhira deta - zenera lomwelo monga lapitalo lidzawonekera. Dzina la mndandanda tsopano ndi "Total Income". Makhalidwe a X amatanthawuza zomwe zili m'maselo a "Number of Items". Gawo la "Y Values" liyenera kudzazidwa, ndikuwunikira gawo la "Total Revenue".

Tsopano mutha dinani batani la "Chabwino" pazenera la "Select Data Source", potero kutseka. Chithunzi chokhala ndi mizere yodutsana chikuwonekera m'dera la tchati. Njira yodutsamo ndiyo breakeven point.

Ngati pakufunika kuwerengera mwatsatanetsatane, yesani kugwiritsa ntchito

Kupeza nthawi yopuma kumathandiza m'madera osiyanasiyana omwe mbali yachuma imagwira ntchito yofunika kwambiri. Mkati mwa kampani, kuwerengera kumatha kuchitidwa ndi katswiri wazachuma, wotsogolera chitukuko kapena eni ake. Kudziwa zikhulupiriro za zero kudzakuthandizani kumvetsetsa pamene bizinesiyo ipindula, momwe ilili panthawi inayake. Dongosolo la malonda likhoza kulembedwa molondola, podziwa nthawi yopuma.

Ngati wobwereketsa kapena wobwereketsa ali ndi deta yokwanira ya kampaniyo, amatha kudziwanso kudalirika kwa bungwe ndi gawo lopuma ndikusankha ngati kuli koyenera kuyikapo.

Ubwino ndi kuipa kwa mtundu wa break-ven point

Ubwino waukulu wa chitsanzo ichi ndi kuphweka kwake. Njira zitatu zodziwira nthawi yopuma zili m'manja mwa aliyense yemwe ali ndi Microsoft Excel pazida zawo. Vuto ndiloti chitsanzocho ndi chokhazikika komanso chochepa. Mwachizoloŵezi, kusintha kosayembekezereka mu chimodzi mwa zizindikiro zikhoza kuchitika, chifukwa chomwe zotsatira za mawerengedwewo zikhoza kuonedwa ngati zopanda ntchito. Ngati kufunikira kwa zinthu sikukhazikika, ndizosatheka kudziwa kuchuluka kwa malonda pasadakhale. Zimakhudzidwanso ndi zinthu zina - mwachitsanzo, ubwino wa ntchito yogulitsa malonda.

Kutsiliza

Kuwerengera nthawi yopuma ndi njira yothandiza kwa mabizinesi anthawi yayitali omwe amafunikira zinthu zokhazikika. Poyang'ana chizindikiro ichi, mukhoza kukonzekera ndondomeko ya ntchito kwa nthawi ndithu. The yopuma-ngakhale mfundo limasonyeza pa zimene buku la kupanga ndi malonda phindu chimakwirira zomvetsa, chimatsimikizira kampani chitetezo zone.