Kev sib tsoo txawm tias yog ib qho cuab yeej rau kev tshuaj xyuas cov peev txheej peev txheej ntawm tus qauv kev lag luam thiab rau kev tshuaj xyuas ntau yam phiaj xwm kev nqis peev. Koj tuaj yeem yooj yim suav cov nqi tsub, tus nqi sib txawv, thiab kev xaiv tus nqi hauv Excel los txiav txim siab qhov tawg txawm tias taw tes rau koj cov khoom. Nov yog tus naj npawb ntawm chav nyob uas koj xav tau muag ntawm tus nqi koj teeb tsa txhawm rau txhawm rau txhawm rau.
Cov kauj ruam
Ntu 1 ntawm 5: Tsim Koj Cov Nqi Tus Nqi Tsis Txaus
Kauj Ruam 1. Qhib Excel thiab tsim phau ntawv ua haujlwm tshiab
Koj yuav tsim ntau nplooj ntawv hauv phau ntawv no txhawm rau txhawm rau taug qab txhua yam ntawm koj cov nqi.
Kauj Ruam 2. Nyem rau "+" khawm tom ntej ntawm "Sheet1" hauv qab ntawm qhov screen
Qhov no yuav tsim daim ntawv ua haujlwm dawb paug tshiab.
Kauj Ruam 3. Hloov daim ntawv tshiab rau "VariableCosts
" Daim ntawv no yuav nyob ntawm lub rooj uas taug qab txhua yam ntawm koj cov khoom lag luam tus nqi sib txawv, xws li kev xa khoom, kev cog lus, thiab lwm yam nqi.
Kauj Ruam 4. Tsim cov ntawv cim npe rau daim ntawv tshiab
Txhawm rau tsim cov nqi sib txawv yooj yim, sau "Nqe lus piav qhia" rau hauv A1 thiab "Tus lej" rau hauv B1.
Kauj Ruam 5. Sau cov npe ntawm koj tus lag luam tus nqi sib txawv hauv kab A
Hauv qab kab lus "Nqe lus piav qhia", nkag mus rau hom kev hloov pauv tus nqi uas koj yuav cuam tshuam nrog rau koj cov khoom.
Kauj Ruam 6. Tawm sab B ("Tus Nqi") tsis muaj dab tsi tam sim no
Koj yuav tau sau cov nqi tiag tom qab hauv cov txheej txheem.
Kauj Ruam 7. Tsim lub rooj los ntawm cov ntaub ntawv koj tau nkag mus
Tig cov ntaub ntawv rau hauv lub rooj yuav ua rau nws yooj yim rau ntsaws rau hauv cov qauv tom qab:
- Xaiv tag nrho cov ntaub ntawv, suav nrog kab lus hauv kab ntawv thiab cov nyiaj seem, los ntawm kev nyem thiab rub koj tus nas hla txhua lub cell.
- Nyem rau "Format as Table" khawm. Koj yuav pom qhov no hauv Tsev Tsev tab. Yog tias koj siv Excel rau Mac, nyem rau Tab Tables, nyem lub pob "Tshiab", thiab tom qab ntawd xaiv "Ntxig Cov Lus nrog Headers."
- Khij lub npov "Kuv lub rooj muaj headers". Qhov no yuav khaws cov ntawv lo rau thawj kab ua cov ntawv cim npe.
- Nyem rau "Table Table" teb nyob rau ntawm kaum sab xis thiab npe nws "VariableCosts."
Ntu 2 ntawm 5: Tsim Koj Cov Nqi Them Nqi
Kauj Ruam 1. Nyem rau "+" khawm tom ntej ntawm "VariableCosts" nyob hauv qab ntawm qhov screen
Qhov no yuav tsim lwm daim ntawv ua haujlwm dawb paug.
Kauj Ruam 2. Hloov daim ntawv tshiab rau "FixedCosts
" Daim ntawv no yuav tsev tag nrho cov nqi tsub rau koj cov khoom, xws li nqi xauj tsev, ntawv pov hwm kev noj qab haus huv, thiab lwm yam nqi uas tsis hloov pauv.
Kauj Ruam 3. Tsim cov ntawv cim npe
Zoo li nrog Daim Ntawv Nqi Tus Nqi, tsim daim ntawv "Nqe lus piav qhia" hauv cell A1 thiab daim ntawv "Tus lej" hauv cell B1.
Kauj Ruam 4. Sau cov npe ntawm koj tus lag luam tus nqi tsub hauv kab A
Sau thawj kab ntawv nrog cov lus piav qhia txog koj tus nqi tsub, xws li "Xauj."
Kauj Ruam 5. Tawm sab B ("Tus Nqi") tsis muaj dab tsi tam sim no
Koj yuav tau ua tiav cov nqi no tom qab tsim cov ntawv nthuav tawm tas li.
Kauj Ruam 6. Tsim lub rooj los ntawm cov ntaub ntawv koj tau nkag mus
Xaiv txhua yam koj tau tsim ntawm daim ntawv no, suav nrog lub taub hau:
- Nyem rau "Format as Table" khawm hauv Tsev Tsev.
- Txheeb xyuas "Kuv lub rooj muaj lub taub hau" kom tig kab 1 rau hauv kab taub hau rau lub rooj.
- Nyem rau "Table Table" teb thiab npe lub rooj "FixedCosts."
Ntu 3 ntawm 5: Tsim Daim Ntawv Sib Tsoo
Kauj Ruam 1. Hloov Npe Sheet1 rau "BEP" thiab xaiv nws
Daim ntawv no yuav nyob nrog koj lub ntsiab BEP (Break Even Point) daim duab. Koj tsis tas yuav hloov nws mus rau "BEP," tab sis nws yuav yooj yim dua rau koj phau ntawv ua haujlwm yog tias koj ua.
Kauj Ruam 2. Tsim qhov txheej txheem rau koj daim ntawv tawg
Rau lub hom phiaj ntawm qhov piv txwv no, tsim koj daim ntawv siv cov txheej txheem hauv qab no:
- A1: Kev Muag Khoom - Nov yog daim ntawv lo rau Kev Muag Khoom ntawm daim ntawv nthuav dav.
- B2: Nqe Ib Chav - Qhov no yuav yog tus nqi koj them rau txhua yam khoom koj muag.
- B3: Chav Nyob Muag - Qhov no yuav yog tus lej ntawm cov khoom koj tau muag ntawm tus nqi tshwj xeeb hauv lub sijhawm teev tseg.
- A4: Cov Nqi - Qhov no yog daim ntawv lo rau Nqi seem ntawm daim ntawv nthuav tawm.
- B5: Cov Nqi Hloov pauv - Cov no yog cov nqi ntawm koj cov khoom uas koj muaj kev tswj hwm (kev xa khoom, nqi tus nqi, thiab lwm yam)
- B6: Cov Nqi Txhim Kho - Cov no yog cov nqi ntawm koj cov khoom uas koj tsis muaj kev tswj hwm (chaw xauj tsev, pov hwm, thiab lwm yam)
- A7: Cov Nyiaj Tau Los - Qhov no yog qhov nyiaj tau los muag koj cov khoom tsim ua ntej tus nqi raug txiav txim.
- B8: Cov Nyiaj Tau Los - Qhov no yog cov nyiaj uas koj ua rau ib chav tom qab txiav txim siab tus nqi.
- B9: Gross Margin - Qhov no yog tag nrho cov nyiaj koj tau ua rau txhua chav nyob muag tom qab tus nqi.
- A10: BEP - Nov yog daim ntawv lo rau ntu Break Even Point ntawm daim ntawv nthuav tawm.
- B11: Chav Nyob - Qhov no yog tus lej ntawm cov chav koj yuav tsum tau muag kom phim koj cov nuj nqis.
Kauj Ruam 3. Hloov cov lej zauv rau cov zis thiab cov xov tooj ntawm tes
Koj yuav tsum tau hloov pauv cov lej rau qee lub xov tooj kom koj cov ntaub ntawv tshwm sim kom raug:
- Highlight C2, C5, C6, C8, thiab C9. Nyem rau ntawm cov ntawv qhia zaub mov nco hauv ntu "Tus lej" ntawm Lub Tsev tab thiab xaiv "Txiaj Ntsig."
- Highlight C3 thiab C11. Nyem rau cov ntawv qhia zaub mov nco-down thiab xaiv "Ntau tus lej ntau dua." Xaiv "Tus lej" thiab tom qab ntawd teeb tsa "Cov lej zauv" rau "0."
Kauj Ruam 4. Tsim cov kab sib txawv los siv hauv cov qauv
Xaiv thiab tsim cov kab hauv qab no los ua koj cov qauv ua haujlwm. Qhov no yuav tsim qhov sib txawv uas tuaj yeem txuas rau hauv koj cov qauv, tso cai rau koj yooj yim siv thiab hloov kho cov txiaj ntsig no.
- Xaiv B2: C3 thiab tom qab ntawv nyem rau "Formulas" tab. Nyem "Tsim los ntawm kev xaiv" thiab tom qab ntawv nyem "OK".
- Xaiv B5: C6 thiab tom qab ntawv nyem rau "Cov Qauv" tab. Nyem "Tsim los ntawm kev xaiv" thiab tom qab ntawv nyem "OK".
- Xaiv B8: C9 thiab tom qab ntawv nyem rau "Cov Qauv" tab. Nyem "Tsim los ntawm kev xaiv" thiab tom qab ntawv nyem "OK".
- Xaiv B11: C11 thiab tom qab ntawv nyem rau "Formulas" tab. Nyem "Tsim los ntawm kev xaiv" thiab tom qab ntawv nyem "OK".
Ntu 4 ntawm 5: Nkag Mus Rau Koj Cov Qauv
Kauj Ruam 1. Nkag mus rau tus lej sib txawv tus nqi
Qhov no yuav suav tag nrho cov nqi sib txawv rau tus naj npawb ntawm cov khoom koj muag. Nyem C5 thiab sau cov qauv hauv qab no:
= SUM (VariableCosts)*Units_Sold
Kauj Ruam 2. Nkag mus rau tus lej them tus nqi
Qhov no yuav suav tag nrho cov nqi tsub rau koj cov khoom. Nyem C6 thiab sau cov qauv hauv qab no:
= SUM (FixedCosts)
Kauj Ruam 3. Nkag mus rau tus lej ntawm tus lej
Qhov no yuav suav cov paj tau paj uas koj tau ua tom qab tau txiav txim siab tus nqi. Nyem C8 thiab sau cov qauv hauv qab no:
= Price_Per_Unit-SUM (VariableCosts)
Kauj Ruam 4. Nkag mus rau cov lej tag nrho cov txiaj ntsig
Qhov no txiav txim siab tag nrho cov nyiaj koj tau ua rau txhua chav nyob uas koj muag tom qab tus nqi sib txawv. Nyem C9 thiab sau cov qauv hauv qab no:
= Unit_Margin*Units_Sold
Kauj Ruam 5. Nkag mus rau BEP tus qauv
Qhov no yuav siv koj cov nqi tsub thiab muab piv rau koj cov npoo, qhia rau koj paub pes tsawg chav uas koj xav tau kom muag kom tawg txawm tias. Nyem C11 thiab sau cov qauv hauv qab no:
= IFERROR (Fix_Costs/Unit_Margin, 0)
Ntu 5 ntawm 5: Txheeb Xyuas Qhov Ua Ntej Ua Ntej
Kauj Ruam 1. Nkag mus rau koj tus lag luam tus nqi sib txawv
Rov qab mus rau VariableCosts cov lus thiab sau tag nrho cov nqi ntsig txog koj cov khoom. Qhov tseeb dua koj nyob ntawm no, qhov tseeb koj qhov kev suav BEP yuav yog.
Txhua tus nqi hauv VariableCosts cov lus yuav tsum yog ib chav muag
Kauj Ruam 2. Nkag mus rau koj tus lag luam tus nqi tsub
Nkag mus rau cov nuj nqis no rau hauv koj Cov Nqi Them Nqi. Cov no yog cov nqi ntawm kev khiav koj txoj kev lag luam, thiab yuav tsum tau teeb tsa tib lub sijhawm (piv txwv li, tus nqi txhua hli).
Kauj Ruam 3. Ntaus tus nqi ib chav tsev
Hauv daim ntawv BEP, sau tus nqi kwv yees pib ib chav. Koj yuav tuaj yeem kho qhov no thaum koj ua qhov kev suav
Kauj Ruam 4. Sau tus naj npawb ntawm cov chav koj xav muag
Nov yog tus naj npawb ntawm chav nyob koj npaj siab yuav muag nyob rau tib lub sijhawm raws li koj Cov Nqi Kho. Piv txwv li, yog tias koj tus nqi tsub nqi suav nrog nqi xauj tsev thiab pov hwm txhua hli, Chav Muag Khoom yuav yog tus lej ntawm cov chav muag nyob rau tib lub sijhawm ntawd.
Kauj Ruam 5. Nyeem qhov "Chav" tso tawm
Cov Chav nyob tso tawm ntawm tes (C11) yuav tso tawm tus lej ntawm chav nyob uas koj yuav xav tau muag hauv koj lub sijhawm kom tawg txawm tias. Tus lej no yuav hloov pauv nyob ntawm Tus Nqi Ib Chav nrog rau koj li Tus Nqi Tus Kheej thiab Cov Nqi Tsub Tus Nqi.
Kauj Ruam 6. Hloov kho tus nqi thiab tus nqi
Hloov Tus Nqi Ib Chav yuav hloov tus lej ntawm cov chav koj xav tau txhawm rau txhawm rau. Sim hloov tus nqi thiab saib dab tsi tshwm sim nrog koj li BEP tus nqi.
