Kev sib tsoo txawm tias yog ib qho cuab yeej rau kev tshuaj xyuas cov txiaj ntsig txiaj ntsig ntawm tus qauv kev lag luam thiab ntau yam tswv yim kev nqis peev. Koj tuaj yeem nrhiav tus nqi tsub, tus nqi sib txawv, thiab kev xaiv tus nqi hauv Excel los txiav txim siab qhov sib tsoo-txawm tias cov khoom. Qhov tawg-txawm tias tus lej yog tus lej ntawm chav nyob uas yuav tsum tau muag ntawm tus nqi teeb tsa kom tawg txawm tias (npog cov nqi tsim khoom).
Kauj ruam
Ntu 1 ntawm 5: Tsim Tus Nqi Hloov Tus Nqi
Kauj Ruam 1. Qhib Excel thiab tsim phau ntawv ua haujlwm
Koj yuav xav tau qee daim ntawv ua haujlwm hauv phau ntawv ua haujlwm kom 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 qhib daim ntawv ua haujlwm tshiab.
Kauj Ruam 3. Npe cov ntawv tshiab "Tus nqi hloov pauv"
Daim ntawv ua haujlwm no yuav suav tag nrho cov ntxhuav uas saib xyuas txhua tus nqi sib txawv, xws li nqi thauj mus los, nqi ua haujlwm, thiab lwm yam.
Kauj Ruam 4. Tsim daim ntawv lo lub npe rau daim ntawv tshiab
Txhawm rau tsim cov txheej txheem tus nqi sib txawv, sau "Nqe lus piav qhia" rau hauv cell A1 thiab "Amount" rau hauv cell B1.
Kauj Ruam 5. Nkag mus rau koj lub npe lag luam tus nqi sib txawv hauv kab A
Hauv qab "Nqe lus piav qhia" nqe lus, nkag mus rau hom khoom lag luam tus nqi sib txawv los daws.
Kauj Ruam 6. Tawm sab B ("Tus Nqi") tsis muaj dab tsi tam sim no
Tom qab ntawv kab ntawv no yuav suav nrog cov nqi uas tau tshwm sim tiag tiag thaum txheej txheem.
Kauj Ruam 7. Tsim lub rooj los ntawm cov ntaub ntawv nkag
Hloov cov ntaub ntawv rau hauv cov rooj kom koj nkag tau yooj yim rau hauv cov qauv:
- Xaiv tag nrho cov ntaub ntawv, suav nrog kab lus npe thiab cov cell tsis muaj dab tsi, los ntawm kev nyem thiab rub tus nas cursor hla txhua lub cell.
- Nyem rau "Format as Table" khawm. Koj yuav pom nws nyob hauv qab daim ntawv lo Tsev. Yog tias koj siv Excel rau Mac, nyem rau Tables daim ntawv lo, nyem rau "New" khawm, thiab xaiv "Insert Table with Headers".
- Khij lub npov "Kuv lub rooj muaj lub taub hau". Yog li, thawj kab ntawv lo yuav raug teeb tsa ua cov ntawv cim npe.
- Nyem rau "Lub Npe Lub Npe" lub npov nyob rau sab xis saum toj thiab hloov lub npe mus rau "Tus Nqi Hloov pauv"
Ntu 2 ntawm 5: Tsim Tus Nqi Them Nqi
Kauj Ruam 1. Nyem rau lub pob "+" nyob ib sab ntawm "Tus nqi hloov pauv" nyob hauv qab ntawm qhov screen
Qhov no yuav qhib daim ntawv ua haujlwm tshiab.
Kauj Ruam 2. Hloov daim ntawv ua haujlwm tshiab mus rau "Cov Nqi Kho"
Daim ntawv no yuav suav tag nrho cov khoom lag luam tus nqi, 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 daim ntawv cim npe
Ib yam li cov ntawv hloov pauv Tus Nqi, tsim daim ntawv lo "Nqe lus piav qhia" hauv cell A1 thiab daim ntawv lo "Tus lej" hauv cell B1.
Kauj Ruam 4. Sau cov npe ntawm cov nqi tsub koj li lag luam hauv kab A
Sau rau hauv kab ntawv thawj nrog cov lus piav qhia ntawm tus nqi tsub, piv txwv li "Xauj".
Kauj Ruam 5. Tawm sab B (“Tus Nqi”) tsis muaj dab tsi tam sim no
Koj yuav sau cov nqi no tom qab ua tiav txhua daim ntawv.
Kauj Ruam 6. Tsim lub rooj los ntawm cov ntaub ntawv uas tau nkag mus
Xaiv txhua yam koj tau tsim ntawm daim ntawv no, suav nrog lub npe:
- Nyem rau "Format as Table" khawm hauv daim ntawv lo Tsev.
- Txheeb xyuas "Kuv lub rooj muaj lub taub hau" txhawm rau hloov kab 1 rau cov npe lus.
- Nyem rau ntawm "Lub Npe Npe" lub npov thiab sau lub npe lub npe "Cov Nqi Nqes"
Ntu 3 ntawm 5: Tsim Daim Ntawv So-Even Worksheet
Kauj Ruam 1. Hloov Npe Sheet1 rau "BEP" thiab xaiv nws
Daim ntawv no yuav tuav koj lub ntsiab BEP (Break Even Point) daim ntawv. Koj tsis tas yuav hloov lub npe mus rau "BEP", tab sis nws yuav ua rau koj txoj haujlwm yooj yim dua yog tias koj ua.
Kauj Ruam 2. Teev qhov txheej txheem rau daim ntawv so-txawm tias ua haujlwm
Txog rau qhov piv txwv no, tsim ib daim ntawv ua haujlwm siv cov qauv hauv qab no:
- A1: Kev Muag Khoom - Nov yog daim ntawv lo rau Kev Muag Khoom ntawm daim ntawv ua haujlwm.
- B2: Nqe Ib Chav - Qhov no yog tus nqi them rau txhua yam khoom muag.
- B3: Cov Chav Muag Khoom - Qhov no yog tus lej ntawm chav nyob muag ntawm tus nqi teev hauv lub sijhawm muab sijhawm.
- A4: Cov Nqi - Qhov no yog daim ntawv lo rau Nqi seem ntawm daim ntawv ua haujlwm.
- B5: Tus nqi sib txawv - Cov no yog cov khoom lag luam tus nqi koj tuaj yeem tswj tau (xa khoom, nqi tus nqi, thiab lwm yam)
- B6: Cov Nqi Txhim Kho - Cov no yog cov khoom lag luam tus nqi uas koj tsis muaj kev tswj hwm (chaw xauj tsev, pov hwm, thiab lwm yam)
- A7: Cov Nyiaj Tau Los - Qhov no yog cov nyiaj tau los ntawm kev muag cov khoom ua ntej txiav tawm cov nqi.
- B8: Cov Nyiaj Tau Los - Qhov no yog cov nyiaj tau los ntawm ib chav tom qab txiav tawm cov nqi.
- B9: Gross Profit Margin - Qhov no yog tag nrho cov nyiaj tau los los ntawm kev muag cov khoom ntawm cov khoom tom qab txiav cov nqi.
- A10: BEP-Qhov no yog daim ntawv lo rau ntu tawg-ntu ntu ntawm daim ntawv ua haujlwm.
- B11: Chav Nyob - Qhov no yog tus naj npawb ntawm cov chav uas yuav tsum tau muag kom npog cov nqi tsim khoom.
Kauj Ruam 3. Hloov cov lej zauv rau cov zis thiab cov cell input
Koj yuav tsum tau hloov cov lej zauv rau cov cell tshwj xeeb kom cov ntaub ntawv tshwm sim kom raug:
- Highlight C2, C5, C6, C8, thiab C9. Nyem rau cov ntawv qhia zaub mov nco hauv ntu "Tus lej" ntawm Daim Ntawv Qhia Tsev thiab xaiv "Txiaj Ntsig".
- Highlight C3 thiab C11. Nyem rau cov ntawv qhia zaub mov nco thiab xaiv "Ntau tus lej". Xaiv "Tus lej" thiab teeb tsa "Cov lej zauv" rau "0".
Kauj Ruam 4. Tsim kom muaj ntau yam los siv hauv cov qauv
Xaiv thiab tsim cov kab hauv qab no rau cov qauv ua haujlwm. Txoj hauv kev no, hloov pauv tuaj yeem tsim los nkag rau hauv cov qauv kom koj tuaj yeem yooj yim taug qab thiab hloov kho lawv qhov txiaj ntsig.
- Xaiv B2: C3 ces nyem rau daim ntawv "Cov Qauv". Nyem "Tsim los ntawm kev xaiv" thiab txuas ntxiv nrog "OK".
- Xaiv B5: C6 ces nyem rau daim ntawv "Cov Qauv". Nyem "Tsim los ntawm kev xaiv" thiab txuas ntxiv nrog "OK".
- Xaiv B8: C9 ces nyem rau ntawm "Cov Qauv" daim ntawv lo. Nyem "Tsim los ntawm kev xaiv" thiab txuas ntxiv nrog "OK".
- Xaiv B11: C11 ces nyem rau ntawm "Cov Qauv" daim ntawv lo. Nyem "Tsim los ntawm kev xaiv" thiab txuas ntxiv nrog "OK".
Ntu 4 ntawm 5: Nkag Mus Rau Cov Qauv
Kauj Ruam 1. Nkag mus rau tus lej sib txawv tus nqi
Cov mis no yuav suav tag nrho cov nqi sib txawv rau cov khoom muag. Nyem C5 thiab sau cov qauv hauv qab no:
= SUM (Tus nqi sib txawv)*Units_Sold
Kauj Ruam 2. Plug tus nqi tsub rau hauv tus qauv
Excel yuav suav tag nrho tus nqi tsub nqi ntawm koj cov khoom. Nyem C6 thiab sau cov qauv hauv qab no:
= SUM (Tus nqi tsub)
Kauj Ruam 3. Nkag mus rau tus lej ntawm tus lej
Cov mis no yuav suav cov txiaj ntsig tom qab suav nrog cov nqi sib txawv. Nyem C8 thiab sau cov qauv hauv qab no:
= Price_Per_Unit-SUM (Tus nqi sib txawv)
Kauj Ruam 4. Nkag mus rau cov lej tag nrho cov paj tau paj
Cov mis no txiav txim siab tag nrho cov nyiaj tau los rau txhua chav nyob tom qab txiav tawm cov nqi sib txawv. Nyem C9 thiab sau cov qauv hauv qab no:
= Margin_Unit*Unit_Sold
Kauj Ruam 5. Nkag mus rau BEP tus qauv
Cov mis no siv tus nqi tsub thiab sib piv lawv rau cov paj tau paj kom koj thiaj paub pes tsawg chav uas koj xav tau kom muag txhawm rau txhawm rau txhawm rau. Nyem C11 thiab sau cov qauv hauv qab no:
= IFERROR (Fix_Cost/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 Tus Nqi Tus Nqi Hloov pauv thiab sau txhua tus nqi cuam tshuam nrog cov khoom. Qhov tseeb ntau dua tus nqi sau npe, qhov tseeb dua qhov kev suav yuav raug.
Txhua tus nqi hauv Cov Nqi Tus Nqi Hloov pauv yuav tsum yog ib chav muag
Kauj Ruam 2. Nkag mus rau koj tus lag luam tus nqi tsub
Nkag mus rau cov nqi no rau hauv cov lus tsub nqi. Cov no yog cov nqi ntawm kev ua lag luam, txhua qhov teeb tsa sib npaug (piv txwv li cov nqi txhua hli).
Kauj Ruam 3. Ntaus tus nqi ib chav
Hauv daim ntawv BEP, sau tus nqi kwv yees ib chav. Koj yuav tuaj yeem kho nws raws li kev suav sau zuj zus.
Kauj Ruam 4. Sau tus naj npawb ntawm cov chav koj xav muag
Qhov no yog tus naj npawb xav tau ntawm kev muag khoom nyob rau tib lub sijhawm raws li Tus Nqi Kho. Piv txwv, yog tias koj tus nqi tsub nqi suav nrog nqi xauj tsev thiab kev pov hwm txhua hli, Chav Muag Khoom yog tus lej ntawm cov chav muag nyob rau tib lub sijhawm.
Kauj Ruam 5. Nyeem qhov "Unit" output
Cov Chav nyob tso tawm ntawm tes (C11) yuav tso saib tus naj npawb ntawm chav nyob uas yuav xav tau muag thiaj li yuav tawg. Tus lej no yuav hloov pauv nyob ntawm Tus Nqi Ib Chav ntxiv rau koj li Tus Nqi Tus Nqi thiab Tus Nqi Tsub Tus Nqi.
Kauj Ruam 6. Hloov kho tus nqi thiab tus nqi
Hloov Tus Nqi Ib Chav Tsev tseem yuav hloov tus lej ntawm chav nyob uas xav tau txhawm rau txhawm rau. Sim hloov tus nqi muag ntawm chav tsev thiab pom qhov cuam tshuam ntawm BEP tus nqi.
