(Phần Excel) ng d n chi tiết cách giải (giải đầy...

(Phn Excel)

- Hng dn chi tit cch gii

(gii y )

MC LC

PHN 1: EXCEL .......................................................................................................... 1

Bi 1 ............................................................................................................................... 1

Bi 2 ............................................................................................................................... 6

Bi 3 ............................................................................................................................. 12

Bi 4 ............................................................................................................................. 16

Bi 5 ............................................................................................................................. 21

Bi 6 ............................................................................................................................. 25

Bi 7 ............................................................................................................................. 26

Bi 8 ............................................................................................................................. 29

Bi 9 ............................................................................................................................. 33

Bi 10 ........................................................................................................................... 36

Bi 11 ........................................................................................................................... 37

Bi 12 ........................................................................................................................... 38

Bi 13 ........................................................................................................................... 40

Bi 14 ........................................................................................................................... 42

Bi 15 ........................................................................................................................... 44

Bi 16 ........................................................................................................................... 49

Bi 17 ........................................................................................................................... 51

Bi 18 ........................................................................................................................... 57

Bi 19 ........................................................................................................................... 64

Bi 20 ........................................................................................................................... 68

Bi 21 ........................................................................................................................... 73

Bi 22 ........................................................................................................................... 79

Bi 23 ........................................................................................................................... 87

Bi 24 ........................................................................................................................... 92

Bi 25 ........................................................................................................................... 95

Bi 26 ........................................................................................................................... 96

PHN 2: ACCESS ...................................................................................................... 99

1

PHN 1: EXCEL

Bi 1

Bng nhp liu

ngha s chng t (S CT): k t u cho bit m hang, k t th 2 cho bit sn

phm thuc loi no, cc k t tip theo l m s chng t.

Cu 1: Da vo s chng t v bng 1 in gi tr ct sn phm v n gi. Bit

rng gi bn theo loi sn phm v nhng chng t trong thng 1 v 2 gi tng

10% so vi gi nim yt.

*in gi tr ct sn phm

- Nhp vo C3 hm sau: =VLOOKUP(LEFT(B3;1);$A$17:$B$21;2;0)

- Tip theo gi nt in v ko t C3 cho n C12.

*in gi tr ct Loi SP

- Nhp vo E3 hm sau: =VLOOKUP(LEFT(B3;1);$A$17:$B$21;2;0)

- Tip theo gi nt in v ko t E3 cho n E12.

*in gi tr ct n gi

- Tm n gi t bng 1 ta dng hm index:

=INDEX($C$17:$E$21;MATCH(C3;$B$17:$B$21;0);MATCH(E3;$C$16:$E$16;0))

- Nhp vo G3 hm sau:

2

=IF(OR(MONTH(D3)=1;MONTH(D3)=2);INDEX($C$17:$E$21;MATCH(C3;$B$

17:$B$21;0);MATCH(E3;$C$16:$E$16;0))*1.1;INDEX($C$17:$E$21;MATCH(C3;

$B$17:$B$21;0);MATCH(E3;$C$16:$E$16;0)))

- Tip theo gi nt in v ko t G3 cho n G12.

Cu 2: in gi tr ct loi SP (lm cu 1)

Cu 3: in gi tr ct gim gi, bit rng chng t mua sn phm loi 3 trong

thng 2 c gim 5% tr gi.

- Nhp vo H3 hm sau: =IF(AND(E3=3;MONTH(D3)=2);G3*0.95;G3)

- Tip theo gi nt in v ko t H3 cho n H12.

Cu 4: in gi tr ct doanh thu. Doanh thu = s lng * n gi gim gi

- Nhp vo I3 hm sau: =F3*G3-H3

- Tip theo gi nt in v ko t I3 cho n I12.

Cu 5: in gi tr cho bng thng k 2: thng k doanh thu theo sn phm v

theo thng.

Bc 1: Nhp vo B26 hm sau:

=SUM(IF($C$3:$C$12=A26;IF(MONTH($D$3:$D$12)=$B$25;$I$3:$I$12)))

Sau nhn Ctrl + Shift + Enter.

- Tip theo gi nt in v ko theo hng dc t B26 cho n B30.

Bc 2: Sa hm B26 thnh:

=SUM(IF($C$3:$C$12=$A$26;IF(MONTH($D$3:$D$12)=B25;$I$3:$I$12)))


- Tip theo gi nt in v ko theo hng ngang t B26 n B28.

Bc 3: Sa hm C26 thnh:

=SUM(IF($C$3:$C$12=A26;IF(MONTH($D$3:$D$12)=$C$25;$I$3:$I$12)))


- Tip theo gi nt in v ko theo hng dc t C26 cho n C30.

Bc 4: Sa hm D26 thnh:

=SUM(IF($C$3:$C$12=A26;IF(MONTH($D$3:$D$12)=$D$25;$I$3:$I$12)))


- Tip theo gi nt in v ko theo hng dc t D26 cho n D30.

Cu 6: in gi tr cho bng thng k 3: thng k doanh thu theo sn phm v

theo loi trong thng 3.

Bc 1: Nhp vo G26 hm sau:

3

=SUM(IF($C$3:$C$12=F26;IF($E$3:$E$12=$G$25;IF(MONTH($D$3:$D$12)=

3;$I$3:$I$12))))


- Tip theo gi nt in v ko theo hng dc t G26 cho n G30.

Bc 2: Sa hm G26 thnh:

=SUM(IF($C$3:$C$12=$F$26;IF($E$3:$E$12=G25;IF(MONTH($D$3:$D$12)=

3;$I$3:$I$12))))


- Tip theo gi nt in v ko theo hng ngang t G26 n I28.

Bc 3: Sa hm H26 thnh:

=SUM(IF($C$3:$C$12=F26;IF($E$3:$E$12=$H$25;IF(MONTH($D$3:$D$12)=

3;$I$3:$I$12))))


- Tip theo gi nt in v ko theo hng dc t H26 cho n H30.

Bc 4: Sa hm I26 thnh:

=SUM(IF($C$3:$C$12=F26;IF($E$3:$E$12=$I$25;IF(MONTH($D$3:$D$12)=3

;$I$3:$I$12))))


- Tip theo gi nt in v ko theo hng dc t I26 cho n I30.

Cu 7: nh dng du phn cch hng ngn cho cc ct c gi tr kiu s.

4

- Chn vng d liu c gi tr kiu s mun nh dng (n gi, gim gi, doanh

thu, doanh thu theo thng v doanh thu thng 3 theo loi sn phm)

- Trong Home chn th Number chn biu tng Comma Style

- Mun gim s thp phn chn biu tng Decrease Decimal .

Cu 8: Da vo bng thng k 2, v th so snh doanh thu trong tng thng

ca my ca, my mi v my phay.

- Chn d liu doanh thu ca my ca, my mi v my phay t bng thng k

2, nhn phm Ctrl ty chn ng thi nhiu dng.

- Insert trong th Charts chn biu tng ng.

Vd: chn biu 3-D Column ta c hnh sau

Cu 9: Trch cc chng t c tr gi t 30 triu 50 triu ra mt vng ring trn

bng tnh.

- Copy ct doanh thu ra mt vng ring v g iu kin nh sau:

- Data trong th Sort & Filter chn Advanced hp thoi Advanced Filter

xut hin.

5

+ Trong phn Action chn Copy to another location trch d liu lc c

ra mt vng ring ca bng tnh.

+ List range: chn vng mun trch d liu (bao gm c phn tiu v phn

d liu).

+ Criteria range: chn vng iu kin.

+ Copy to: Chn vng mun hin d liu trch ra (chn mt vng trng tng

ng vi tiu bng tnh).

- Nhp chut vo nt OK ta c bng lc mi nh sau:

*Bi tp hon thnh nh sau:

6

Bi 2

Hon tt bng bo co sau:

Cu 1: Lp cng thc in d liu cho cc ct cn li, bit rng:

*Ct VT v n gi ly ra t bng tham chiu

- Nhp vo E4 hm sau: =VLOOKUP(D4;$A$17:$B$20;2;0)

- Tip theo gi nt in v ko t E4 cho n E13.

- Nhp vo G4 hm sau: =VLOOKUP(D4;$A$17:$B$20;3;0)


*T-Tin = S lng*n gi

- Nhp vo H4 hm sau: =F4*G4

- Tip theo gi nt in v ko t H4 cho n H13.

*Ph ph = T-Tin*ph ph cc mt hng tng ng trong bng tham

chiu

- Nhp vo I4 hm sau: =H4*VLOOKUP(D4;$A$17:$D$20;4;0)


* Tnh Thu TT: Nu 2 k t cui ca SH l NB th Thu TT bng 0,

ngc li Thu TT = (T-Tin +ph ph) * t l thu (ly t bng tham chiu)

- Nhp vo J4 hm sau: =IF(RIGHT(B4;2)="NB";0;(H4+I4)*$B$21)

- Tip theo gi nt in v ko t J4 cho n J13.

* Tng cng = T-Tin + Ph ph + Thu TT

- Nhp vo K4 hm sau: =H4+I4+J4

- Tip theo gi nt in v ko t K4 cho n K13.

Cu 2: in gi tr cho bng tng hp doanh s bn hng

- Nhp vo G18 hm sau: =SUMIF($C$4:$C$13;F18;$K$4:$K$13)


7

- Nhp vo I18 hm sau: =SUMIF($D$4:$D$13;H18;$K$4:$K$13)


Cu 3: Trch ra nhng hp ng khng tnh thu tiu th ra mt vng ring

trn bng tnh

- Copy ct doanh thu ra mt vng ring v g iu kin (=0) nh sau:

- Data trong th Sort & Filter chn Advanced hp thoi Advanced Filter

xut hin.

+ Trong phn Action chn Copy to another location trch d liu lc c

ra mt vng ring ca bng tnh.

+ List range: chn vng mun trch d liu (bao gm c phn tiu v phn

d liu).

+ Criteria range: chn vng iu kin.

+ Copy to: Chn vng mun hin d liu trch ra (chn mt vng trng tng

ng vi tiu bng tnh).

- Nhp chut vo nt OK ta c bng lc mi nh sau:

*Bi hon thnh:

8

Cu 4: Dng chc nng PivotTable thng k tng s lng, tng thnh tin, tng

thu TT theo khch hng theo ngy

- Insert trong th Tables chn PivotTable PivotTable hp thoi Create

PivotTable xut hin.

+ Table/Range: chn ngun d liu (chn bng bao gm c tiu v d liu)

+ Choose where you want the PivotTable report to be placed: Chn vng hin

th (kch vo New Worksheet hin th kt qu ra mt sheet mi)

+ Kt thc nhp vo OK, ta c hnh sau:

9

+ Trong phn Choose fields to add to report, kch chut vo chn Ngy,

Khch, S lng, T-Tin, Thu TT, Tng.

+ Ko Ngy t th Row Labels ln th Report Filter.

+ Trong th Values chn hm mun tnh ton. Kch chut vo du mi tn

hng xung Chn Value Field Setting Chn Sum OK.

+ Kch chut vo tiu v sa tn theo ng yu cu ca bi.

*Bng kt qu:

Cu 5: Dng chc nng SubTotal thng k theo tng khch hng

Bc 1: Sp xp d liu theo khch hng

- Chn bng d liu (t A3 n K13, chn lun phn tiu ).

- Home trong th Editing chn Sort & Filter Custom Sort hp thoi

Sort xut hin

10

+ Trong Column (Sort by) chn Khch hng.

+ Trong Sort On chn Values.

+ Trong Order chn A to Z.

+ Nhp chut vo OK, ta c bng d liu c sp xp theo Khch hng.

Bc 2: Thc hin thng k bng chc nng Subtotal

- Chn bng d liu (t A3 n K13, chn lun phn tiu ).

- Data Subtotal hp thoi Subtotal xut hin

+ Trong At each change in chn Khch hng (bin sp xp)

+ Trong Use function chn Sum (hm mun thc hin tnh ton)

+ Trong Add subtotal to chn cc bin mun tnh tng: S lng, T-Tin,

Ph ph, Thu TT, Tng.

+ Nhp chut vo OK, ta c bng kt qu nh sau:

12

Bi 3

Cng ty thc phm c doanh thu 6 thng u nm 2014 nh sau:

Cu 1: Dng chc nng PivotTable tng hp doanh thu ca cc ca hng theo

thng.

- Nhn ln lt phm Alt + D + P hp thoi PivotTable and PivotChart

Wizard xut hin Chn Multiple consolidation ranges Next.

- Tip theo chn I will create the page fields trong phn How many page fields

do you want? Next

13

- Hp thoi mi xut hin

+ Trong Range: chn cc vng d liu mun thng k Add.

+ Trong phn How many page fields do you want chn 1 v in vo Field

one ch Ca hng.

Next

- Hp thoi mi xut hin Chn New worksheet th hin bng kt qu

sang mt sheet mi Finish.

14

- Sa li tiu theo yu cu ca bi.

*Bng kt qu

Cu 2: Dng chc nng Consolidate tng hp bo co

- Copy cu trc d liu ra mt vng khc (hoc mt sheet khc), sau chn

vng mun hin th kt qu bo co (khng chn tiu , xem hnh di).

- Data trong th Data Tools chn Consolidate hp thoi Consolidate xut

hin.

15

+ Trong Function, chn hm mun tnh ton kt qu bo co Sum.

+ Trong Reference chn a ch cc vng d liu chi tit (lu : khng chn

tiu ) Add.

+ Trong phn Use lables in chn Create links to source data lin kt t bng

tng hp n bng d liu chi tit OK.

*Bng kt qu:

16

Bi 4

Mt cng ty c 3 ca hng bn l dng c th thao ti tnh A. Cng ty tng hp

doanh thu hng tun vi cc thng tin nh bng sau (file Bai 4.xlsx)

Cu 1: Dng chc nng PivotTable thng k tng s lng khch, tng doanh

thu ca tng loi Cu Lng, Tennis, Bng bn theo ca hng theo ngy.

- Chn bng d liu (bao gm c phn tiu )


PivotTable xut hin.


17




+ Trong phn Choose fields to add to report, kch chut vo chn Ca hng,

Ngy, Tng lng khch, Cu lng, Bng bn, Tennis.

+ Ko Ngy t th Row Labels th ln th Report Filter.

+ Ko Ca hng t th Values th sang th Row Lables.

+ Ko Tennis ln trn Bng bn (trong th Values)


hng xung Chn Value Field Setting Chn Sum OK.


*Bng kt qu:

Cu 2: Dng chc nng PivotTable thng k ca hng c lng khch ng

nht theo ngy

- Chn bng d liu (bao gm c phn tiu ).


PivotTable xut hin.

18





+ Trong phn Choose fields to add to report, kch chut vo chn Ca hng,

Ngy, Tng lng khch.

+ Ko Ngy vo th Row Labels.

+ Ko Ca hng t th Values th sang th Row Lables (Ca hng phi nm

di Ngy).


hng xung ca Tng lng khch Chn Value Field Setting Chn

Max OK.


19

*Bng kt qu:

Cu 3: Dng chc nng SubTotal thng k doanh thu tng ca hng

- Trc khi thc hin Subtotal phi sp xp li bin cn thng k (Sort by A to

Z).

- Chn bng d liu (t A4 n J25, chn lun phn tiu ).

- Data Subtotal hp thoi Subtotal xut hin

20

+ Trong At each change in chn Ca hng (bin sp xp)

+ Trong Use function chn Sum (hm mun thc hin tnh ton)

+ Trong Add subtotal to chn cc bin mun tnh tng: Tng doanh thu,

Bng r, Cu long, Bng , Bng chuyn, Bng bn, Tennis.

+ Nhp chut vo OK, ta c bng kt qu nh sau:

21

Bi 5

S liu tng kt s lng hc sinh t yu cu trong 2 nm hc 2009 2010 nh

sau

Cu 1: Dng chc nng PivotTable thng k tng s hc sinh t yu cu ca

tng mn theo nm hc v theo hc k.

- Nhn ln lt phm Alt + D + P hp thoi PivotTable and PivotChart

Wizard xut hin Chn Multiple consolidation ranges Next.

- Tip theo chn I will create the page fields trong phn How many page fields

do you want? Next.

22

- Hp thoi mi xut hin

+ Trong Range: chn cc vng d liu mun thng k (chn c phn tiu )

Add.

+ Trong phn How many page fields do you want chn 2 v in vo Field

one ch Nm hc v Hc k.

Next

- Hp thoi mi xut hin Chn New worksheet th hin bng kt qu

sang mt sheet mi Finish.

23

- Sa li tiu theo yu cu ca bi.

- Nu c dng (blank) th kch chut phi Hide.

*Bng kt qu

Cu 2: Dng chc nng Consolidate tng hp bo co

- Copy cu trc d liu ra mt vng khc (hoc mt sheet khc), sau chn

vng mun hin th kt qu bo co (khng chn tiu , xem hnh di).

- Data trong th Data Tools chn Consolidate hp thoi Consolidate xut

hin.

24

+ Trong Function, chn hm mun tnh ton kt qu bo co Sum.

+ Trong Reference chn a ch cc vng d liu chi tit (lu : khng chn

tiu ) Add.

+ Trong phn Use lables in chn Create links to source data lin kt t bng

tng hp n bng d liu chi tit OK.

*Bng kt qu:

25

Bi 6

Da vo s liu thng k s lng bt git bn ra ca cng ty ABC nm 2010, hy

tnh cc gi tr sau:

*Trung bnh

- Nhp vo E3 hm sau: =AVERAGE(B3:B14)

*Gi tr ln nht

- Nhp vo E4 hm sau: =MAX(B3:B14)

*Gi tr nh nht

- Nhp vo E5 hm sau: =MIN(B3:B14)

*Phng sai

- Nhp vo E6 hm sau: =VAR(B3:B14)

* lch chun

- Nhp vo E7 hm sau: =STDEV(B3:B14) hoc =SQRT(E6)

* nhn

- Nhp vo E8 hm sau: =KURT(B3:B14)

*S trung v

- Nhp vo E9 hm sau: =MEDIAN(B3:B14)

*S yu v

- Nhp vo E10 hm sau: =MODE(B3:B14)

* bt i xng

- Nhp vo E11 hm sau: =SKEW(B3:B14)

26

Bi 7

Cho s liu v doanh thu, chi ph qung co v tin lng ca 12 cng ty nh sau:

Yu cu: dng chc nng Regression d bo doanh thu ca cng ty nu chi ph

qung co l 25 v tin lng l 18.

*Thm chc nng Regression vo Excel nh sau (nu my bn cha c):

- File Option Add-Ins Analysis ToolPak Go Analysis ToolPak

OK.

*M chc nng Regression ln v thc hin cc thao tc:

- Data trong th Analysis chn Data Analysis Regression OK hp thoi

Regression xut hin:

+ Trong Input Y Range, chn vng d liu ca Doanh thu (Y).

27

+ Trong Input X Range, chn vng d liu ca Chi ph qung co v Tin lng

(X1 v X2).

Lu : khi chn vng phi chn lun tiu Y v X1, X2 (v d: chn vng ca Y l

$A$3:$A$15).

+ Trong Output Range chn vng hin th ca bng kt qu Regression (chn mt

trng bt k).

+ Kt thc nhp chut vo OK, ta s c kt qu sau:

+ Da vo kt qu trn ta c c h s hi quy tuyn tnh mu ti Coefficients (

B32):

{

0 = 29,66190108

1 = 0,002318393

2 = 8,484157651

Hm hi quy tuyn tnh th hin s ph thuc ca doanh thu vo chi ph qung

co v tin lng l:

= 0 + 1. 1 + 2. 2

= 29,66190108 + 0,002318393. 1 + 8,484157651. 2

- Tnh doanh thu d bo ti A16 khi chi ph qung co l 25 triu ng/thng

v tin lng l 18 triu ng/thng:

+ Nhp vo A16 hm sau : =B33+B34*B16+B35*C16

+ Nhp vo Decrease Decimal gim bt phn thp phn (Home trong th

Number chn Decrease Decimal)

*Bng kt qu:

29

Bi 8

Cho s liu v nng sut lao ng, gi thnh sn phm, chi ph nguyn vt liu

chnh trong gi thnh sn phm ca 5 x nghip cng sn xut mt loi sn phm nh

sau:

Gi s c quan h tuyn tnh gia Y v X1, X2.

Cu 1: Dng hm LINEST thnh lp hm hi quy tuyn tnh.

- Kch chut ti F5 v chn mt vng trng bao gm 5 dng v 3 ct (ging

vi cu trc d liu), sau g hm =LINEST(B5:B9;C5:D9;1;1) ti F5

Kt thc nhn Ctrl + Shift + Enter.

Lu : chn vng xong th g hm lun.

- Ta c bng kt qu nh sau :

- Hm hi quy tuyn tnh cn tm l:

= 4,25925926 0,37037037. 1 + 1,074074074. 2

30

Cu 2: Dng chc nng Regression d bo gi thnh n v sn phm trong

trng hp nng sut lao ng l 27 triu ng, nguyn vt liu chnh chim

52% gi thnh n v sn phm.

*Thm chc nng Regression vo Excel nh sau (nu my bn cha c):

- File Option Add-Ins Analysis ToolPak Go Analysis ToolPak

OK.

*M chc nng Regression ln v thc hin cc thao tc:

- Data trong th Analysis chn Data Analysis Regression OK hp thoi

Regression xut hin:

+ Trong Input Y Range, chn vng d liu ca gi thnh n v (Y).

+ Trong Input X Range, chn vng d liu ca nng sut lao ng v nguyn vt

liu chnh (X1 v X2).

Lu : khi chn vng phi chn lun tiu Y v X1, X2 (v d: chn vng ca Y l

$B$4:$B$9).

+ Trong Output Range chn vng hin th ca bng kt qu Regression (chn mt

trng bt k).

+ Kt thc nhp chut vo OK, ta s c kt qu sau:

31

+ Da vo kt qu trn ta c c h s hi quy tuyn tnh mu ti Coefficients (

B27):

{

0 = 4,259259259

1 = 0,37037037

2 = 1,074074074

Hm hi quy tuyn tnh th hin s ph thuc ca gi thnh n v vo nng sut

lao ng v nguyn vt liu chnh l:

= 0 + 1. 1 + 2. 2

= 4,259259259 0,37037037. 1 + 1,074074074. 2

- Tnh gi thnh d bo ti B10 khi chi ph qung co l 27 triu ng/thng v

Tin lng l 52 triu ng/thng:

+ Nhp vo B10 hm sau : =B27+B28*C10+B29*D10

+ Nhp vo Decrease Decimal gim bt phn thp phn (Home trong th

Number chn Decrease Decimal)

*Bng kt qu:

33

Bi 9

Cng ty C phn X c ti sn l xe ch hng c nguyn gi l 500 triu ng, thi

gian s dng l 8 nm. n nm th 5 xe b h phi sa cha vi chi ph l 75 triu

ng. Lp bng khu hao ti sn c nh vi phng php ng thng, phng php

khu hao nhanh vi t l ty chn.

*Phng php khu hao ng thng:

Cng thc tng qut: =SLN(cost; salvage; life)

=SLN(nguyn gi; GTCL; thi gian SD)

- Da vo yu cu bi ton, ta lp bng sau:

- Nhp vo B6 hm sau: =SLN($B$1;$B$2;$B$3). Sau gi nt in v ko

theo hng ngang t B6 n F6.

- Nhp vo B7 hm sau: =SUM($B$6:B6). Sau gi nt in v ko theo

hng ngang t B7 n F7.

- Nm th 5 c chi ph sa cha l 75000000 ng.

- Nhp vo B9 hm sau: =$B$1-B7. Sau gi nt in v ko theo hng

ngang t B9 n E9.

- Do nm th 5 c chi ph sa cha nn GTCL ca nm th 5 l (nguyn gi

khu hao tch ly + chi ph sa cha).

+ Nhp vo F9 hm sau: =$B$1-F7+F8

- GTCL ca nm th 5 chnh l nguyn gi ca nm th 6. Tnh khu hao ca

nm th 6 nh sau:

+ Nhp vo G6 hm sau: =SLN($F$9;$B$2;3). Sau gi nt in v ko

theo hng ngang t G6 n I6.

- Tnh khu hao tch ly ca nm 6, 7, 8.

+ Nhp vo G6 hm sau: =SUM($B$6:G6). Sau gi nt in v ko theo

hng ngang t G7 n I7 (hoc gi nt in B6 ko theo hng ngang

cho n I6).

- Tnh GTCL nm 6, 7, 8. (ly nguyn gi ban u - khu hao tch ly + chi ph

sa cha).

+ Nhp vo G9 hm sau: =$B$1-G7+$F$8. Sau gi nt in v ko theo

hng ngang t G9 n I9.

*Bng kt qu

34

*Phng php khu hao nhanh vi t l ty chn:

Cng thc tng qut: =DDB(cost; salvage; life; per; [factor])

=DDB(nguyn gi; GTCL; thi gian SD; th t nm KH; [t l KH ty chn])

Trong , t l khu hao ty chn

r = 1,5 i vi TSC c TGSD 3 4 nm.

r = 2 i vi TSC c TGSD 5 6 nm.

r = 2,5 i vi TSC c TGSD trn 6 nm.

*Da vo yu cu bi ton, ta lp bng sau:

Nhp vo B12 hm sau: =DDB($B$1;;$B$3;B11;2.5). Sau gi nt in v

ko theo hng ngang t B12 n F12.

Nhp vo B13 hm sau: =SUM($B$12:B12). Sau gi nt in v ko theo

hng ngang t B13 n F13.

Nm th 5 c chi ph sa cha l 75000000 ng.

Nhp vo B15 hm sau: =$B$1-B13. Sau gi nt in v ko theo hng

ngang t B15 n E15.

Do nm th 5 c chi ph sa cha nn GTCL ca nm th 5 l (nguyn gi

khu hao tch ly + chi ph sa cha).

+ Nhp vo F15 hm sau: =$B$1-F15+F14

GTCL ca nm th 5 chnh l nguyn gi ca nm th 6. Tnh khu hao ca

nm th 6 nh sau:

+ Nhp vo G12 hm sau: =DDB($F$15;;3;B11;1.5). Sau gi nt in v

ko theo hng ngang t G12 n I12.

Tnh khu hao tch ly ca nm 6, 7, 8.

35

+ Nhp vo G13 hm sau: =SUM($B$12:G12). Sau gi nt in v ko

theo hng ngang t G13 n I13 (hoc gi nt in B13 ko theo hng

ngang cho n I13).

Tnh GTCL nm 6, 7, 8. (ly nguyn gi ban u - khu hao tch ly + chi ph

sa cha).

+ Nhp vo G15 hm sau: =$B$1-G15+$F$14. Sau gi nt in v ko

theo hng ngang t G15 n I15.

*Bng kt qu

36

Bi 10

Mt cng ty xy dng vay tin ngn hng ABC 8 t ng vi li sut 9%/ nm, tr

n 4 k, nh k 6 thng/ln. Tin thc hin d n nh sau:

Ngy 01/01/2014 ngn hng gii ngn 3 t



Ngy 15/07/2014 cng trnh hon thnh v nghim thu.

Yu cu: Tnh li thi cng ca d n m cng ty phi tr.

37

Bi 11

Doanh nghip A c cc phng n sau:

- Phng n 1: u t my t ng ch bin bn kh vi gi 16550 triu ng. Vi

d tnh sn xut ko di trong 5 nm c cc khon thu trong tng nm nh sau:

- Phng n 2: mua tri phiu chnh ph vi li sut 12%. Hy t vn cho doanh

nghip A nn chn phng n no (b qua yu t ri ro)?

*Tnh gi tr hin ti thun ca d n 1

- Nhp vo B6 hm sau: =NPV(B3;C4:G4)-B2

Vi NPV ca d n = GTHT ca dng tin thu vo GTHT ca dng tin chi ra

- Ta c bng kt qu

D n 1 khng kh thi v NPV ca d n m, vy nn u t d n 2.

38

Bi 12

Cng ty c d n vi vn u t ban u ca d n l 20 t ng, cui nm 1 l 16

t ng, sau thu hi cho n ht nm th 13 nh sau: nm th 3: 2 t, nm th 4: 4

t, nm th 5: 6 t, nm th 6: 8 t, nm th 7: 10 t, nm th 8: 12 t, nm th 9: 10

t, nm th 10: 8 t, nm th 11: 6 t, nm th 12: 4 t v nm th 13: 2 t ng.

Hin ti, cng ty ang cn nhc d n ny vi mt d n khc vi t sut li nhun

8%/nm. Vy Cng ty nn u t vo d n no?

*Da vo d liu bi ton ta thit lp bng sau:

- tnh ngn lu rng ta ly doanh thu tr chi ph:

+ Nhp vo C4 hm sau: =C3-C2 . Sau gi nt in v ko theo hng

ngang t C4 n O4.

- Tnh NPV ca d n

- Tnh ch s IRR (khi NPV > 0, d n kh thi)

*Bng kt qu

39

Ta thy NPV > 0 nn d n c kh thi v IRR > 8% nn quyt nh u t vo d

n l hp l.

40

Bi 13

Mt cng ty du lch t chc chuyn du lch Mi N Phan Thit c s liu nh

sau: Chi ph c nh l 45.000.000 ng; gi cho mt ngi (1 sut) l 1.750.000

ng; chi ph bin i ca mt chuyn i l 95% cho mi ngi. Vy cng ty cn bn

bao nhiu sut ha vn?

*Da vo d liu bi ton ta thit lp bi ton dng nh sau:

- B5 l sn lng cn tm (ta in vo ny mt gi tr bt k).

- Khi c sn lng th ta hon thnh cc B7, B8 v B10 da vo cc cng

thc ti chnh hc (cc ny c gi tr ph thuc vo sn lng).

+ Nhp vo B7 hm sau: =B2+B4*B3*B5

+ Nhp vo B8 hm sau: =B3*B5

+ Nhp vo B10 hm sau: =B8-B7

ha vn th li nhun phi bng 0. S dng cng c Goal Seek tm sn

lng ha vn:

Data trong th Data Tools chn What - If Analysis Goal Seek hp thoi

Goal Seek xut hin

Trong Set cell chn c gi tr mc tiu (li nhun bng 0, B10).

Trong To value g gi tr mc tiu cn t c (0).

Trong By changing cell chn c gi tr thay i c ( B5, chnh l sn

lng ha vn).

Sau nhp vo OK ta thy xut hin hp thoi Goal Seek Status nhp

vo OK ta s thy kt qu

41

Vy cng ty cn bn 515 sut th ha vn.

42

Bi 14

Mt doanh nghip c s liu sn xut trong k l 15.000 sn phm v tiu th

12.000 sn phm. Gi bn n v sn phm l 500.000 ng, bin ph n v sn

phm l 250.000 ng. Tng chi ph c nh chi ra trong k l 2.000.000.000 ng.

Cu a: Xc nh im ha vn?

*Da vo d liu bi ton ta thit lp bi ton dng nh sau:

- B6 l sn lng ha vn cn tm (ta in vo ny mt gi tr bt k).



+ Nhp vo B7 hm sau: =B2+B4*B6




lng ha vn:


Goal Seek xut hin



43


lng ha vn).

Sau nhp vo OK ta thy xut hin hp thoi Goal Seek Status nhp vo

OK ta s thy kt qu

Vy cng ty cn sn xut 8000 sn phm th ho vn.

Cu b: Xc nh li thc ca doanh nghip?

Nhp vo B15 hm sau: =B7*B3-B2-B4*B5

Cu c: Cho thu sut thu nhp doanh nghip l 22%. Xc nh thu thu nhp

DN phi np?

Nhp vo B14 hm sau: =B15*B13

*Bng kt qu:

44

Bi 15

Cho cc s liu sau y

STT Thit b S lng n gi

1 My ch 01 25.000.000

2 My trm 40 17.000.000

3 Bn gh 41 400.000

4 Thit b mng, in 10.000.000

5 Chi ph khc 5.000.000

Chi ph mi lp 40 hc vin, 45 tit

STT Thit b n gi

1 Th lao ging vin/tit 120.000

2 Chi ph in, /lp 7.000.000

3 Chi ph qun l lp 1.200.000

4 Bin ph khc cho mi lp 3.000.000

Gi s hc ph cho 1 lp ca mi hc vin 900.000 ng. Yu cu:

Nu mi nm m c 20 lp th sau bao nhiu nm mi thu hi vn?

Gi s m c 200 lp, c li nhun 100.000.000 ng th hc ph l bao

nhiu?

* Nu mi nm m c 20 lp th sau bao nhiu nm mi thu hi vn?

+Bng nhp liu bi ton nh sau:

45

Trong ct Thnh tin, ta tnh ton da vo n gi v s lng hoc s lp, s tit

hc tng ng.

Trong B18 chnh l nm thu hi vn cn tm (ta nhp mt gi tr bt k vo

ny).

Bng tnh ton ct Thnh tin nh sau:

Trong D20 (Li nhun), nhp hm nh sau:

=D17*B18-SUM(D3:D7)-SUM(D9:D12)*B18

thu hi vn th li nhun phi bng 0. S dng cng c Goal Seek tm nm

thu hi vn:


Goal Seek xut hin

Trong Set cell chn c gi tr mc tiu (li nhun bng 0, D20).


Trong By changing cell chn c gi tr thay i c ( B18, chnh l nm

thu hi vn).


OK ta s thy kt qu

46

Vy nu mi nm m c 20 lp th sau 1,9 nm (khong 23 thng) th s thu hi

c vn.

*Gi s m c 200 lp, c li nhun 100.000.000 ng th hc ph l bao

nhiu?

+Bng nhp liu bi ton nh sau:

47

Trong B16 ta g 200 (s lp), B18 ta g 1 (s nm u t).

Trong B17 chnh l hc ph mi hc vin (ta nhp mt gi tr bt k vo ny).

Trong D20 (Li nhun), nhp hm nh sau:

=D17*B18-SUM(D3:D7)-SUM(D9:D12)*B18

t li nhun l 100.000.000 ng, s dng cng c Goal Seek tm hc ph

ca mi hc vin phi np:


Goal Seek xut hin

Trong Set cell chn c gi tr mc tiu (li nhun bng 100 triu ng,

D20).


Trong By changing cell chn c gi tr thay i c ( C17, chnh hc

ph/hc vin).


OK ta s thy kt qu

48

Vy nu m c 200 lp, c li nhun 100.000.000 ng th hc ph mi hc

vin l 519550 ng.

49

Bi 16

Cng ty C phn X sn xut dng sn phm mi l dy in t. Cng ty mun thu

20% li nhun trong nm u tin vi gi thuyt s liu c cho nh sau: Trong

nm u tin ny s bn c 100 tn dy in t (100.000 kg). Mc chit khu trung

bnh cho cc i l l 10%. Tng chi ph c nh l 4 t ng v chi ph cho mi sn

phm l 120.000ng. Cho bit gi bn cng ty c th t li nhun 20% trong

nm u tin.

*Da vo d liu bi ton ta lp c bng sau:

- B6 l gi bn cn tm (ta in vo ny mt gi tr bt k).

- Khi c gi bn th ta hon thnh cc B8, B9 v B11 da vo cc cng thc

ti chnh hc (cc ny c gi tr ph thuc vo gi bn).

+ Nhp vo B8 hm sau: =B2+B3*B4

+ Nhp vo B9 hm sau: =B4*B6-B4*B6*B5

+ Nhp vo B11 hm sau: =((B9-B8)/B9)*100

thu 20% li nhun trong nm u tin th t l (li nhun/doanh thu) phi bng

20%. S dng cng c Goal Seek tm gi bn tng ng:

50


Goal Seek xut hin

Trong Set cell chn c gi tr mc tiu (li nhun/doanh thu bng 20%,

B11).


Trong By changing cell chn c gi tr thay i c ( B6, chnh l gias

bn).


OK ta s thy kt qu

Vy gi bn 222.223 /kg th cng ty c th t li nhun 20% trong nm u tin.

51

Bi 17

Doanh nghip PC WORD ang d kin tung ra mt sn phm mi. S liu t b

phn ti chnh k ton cho bit sn phm mi c d kin bn vi gi $1100. sn

xut, cn u t mt thit b mi vi gi $300.000; tng nh ph khc l $100.000,

chi ph sn xut ra mt sn phm l $500. Ban gim c cn ang lng l v

quyt nh cui cng. B phn tip th v bn hng cho bit s lng bn d kin cho

nm ti c th t 500 ti 1100 sn phm. Hi: Li nhun trong trng xu nht v tt

nht l bao nhiu?

*Da vo d liu bi ton ta thnh lp c bng sau:

- Trong bin sn lng c gi tr thay i c nn ta in mt gi tr bt k

vo y ( B5).

- Khi c sn lng th ta hon thnh cc B7 v B8 da vo cc cng thc ti

chnh hc (cc ny c gi tr ph thuc vo sn lng).

+ Nhp vo B7 hm sau: ==B2+B3+B4*B5

Sau gi nt in v ko theo hng ngang t B9 n D9.


Sau gi nt in v ko theo hng ngang t B10 n D10.


bit li nhun trong hai trng hp xu nht v tt nht ta s dng cng c

Scenario:

52

Data trong th Data Tools chn What - If Analysis Scenario Manager

hp thoi Scenario Manager xut hin chn Add thm cc trng hp

+ Hp thoi Add Scenario xut hin

+ Trong Scenario name: g tn trng hp u tin.

+ Trong Changing cells: chn c gi tr thay i ( B5, chnh l sn lng).

+ Sau nhp vo OK hp thoi Scenario Value xut hin ta in vo gi tr

bt k

53

+ Nhp vo OK xut hin li hp thoi Add Scenario Chn Add thm

trng hp tip theo

+ Trong Scenario name: g tn trng hp xu nht.

+ Trong Changing cells: gi nguyn, khng thay i.


sn lng trong trng hp xu nht


trng hp tip theo

54

+ Trong Scenario name: g tn trng hp tt nht.



sn lng trong trng hp tt nht

+ Nhp vo OK xut hin li hp thoi Add Scenario Chn Summary

xem kt qu

55

+ Hp thoi Scenario Summary xut hin chn Scenario summary xem kt

qu

+ Trong Result cell chn mc tiu (B9 l li nhun), ta c kt qu nh sau:

- Hoc ta c th xem kt qu dng khc bng cch chn Scenario PivotTable report

trong hp thoi Scenario Summary

56

+ Nhp vo OK ta c bng kt qu nh sau:

Vy trong trng hp xu nht l bn c 500 sn phm th doanh nghip s l

100.000$, trng hp tt nht l bn c 1100 sn phm th doanh nghip s li

260.000$.

57

Bi 18

Cng ty XYZ sn xut bn go kh vi cc s liu sau: (vt: VN ng)

My ch bin 16.550.000

Thit b in 8.000.000

Chi ph thit b khc 2.500.000

Nguyn liu 1 tn sn phm 1.550.000

Bao b/1 tn sn phm 900.000

Gi bn 1 tn 2.650.000

Cu 1: Hy tnh sn lng bn go kh sn xut v tiu th cng ty XYZ ha

vn?

*Da vo d liu bi ton ta thnh lp c bng d liu nh sau:

- B8 l sn lng ha vn cn tm (ta in vo ny mt gi tr bt k).



+ Nhp vo B10 hm sau: =B2+B3+B4+B5*B8+B6*B8



58


lng ha vn:


Goal Seek xut hin




lng ha vn).


OK ta s thy kt qu

59

Vy cng ty ha vn th phi sn xut v tiu th 135 tn bn go kh.

Cu 2: Gi s cng ty sn xut v tiu th 100 tn/thng, hy tnh li nhun

trc thu nu gi bn dao ng t 2.800.000 n 3.500.000/tn.

Ta in vo sn lng B8 gi tr l 100 tn/thng, sau lp thm bng nh

hnh di y:

Trong , B17 (mu vng) ta nhp hm sau: =B13.

+ Tip theo chn vng t B16 n J17 Data trong th Data Tools chn

What-If Analysis Data Table

+ Hp thoi Data Table xut hin trong Row input cell chn c gi tr thay

i trn bng d liu ( gi bn, B7)

60

+ Sau nhp vo OK ta c bng kt qu

Cu 3: Hy tnh li nhun trc thu nu sn lng sn xut v tiu th dao

ng t 100 tn n 170 tn.

Ta lp thm bng nh hnh di y:

Trong , C21 (mu vng) ta nhp hm sau: =B13.

+ Tip theo chn vng t B21 n C29 Data trong th Data Tools chn


+ Hp thoi Data Table xut hin trong Column input cell chn c gi tr

thay i trn bng d liu ( sn lng, B8)

61


Cu 4: Hy tnh li nhun trc thu khi gi bn dao ng t 2.800.000 n

3.500.000, sn lng sn xut v tiu th dao ng t 100 tn n 170 tn.

Ta lp thm bng nh hnh di y:

62

Trong , B32 (mu vng) ta nhp hm sau: =B13.

+ Tip theo chn vng t B32 n J40 Data trong th Data Tools chn



i trn bng d liu ( gi bn, B7); trong Column input cell chn c gi tr thay

i trn bng d liu ( sn lng, B8)


64

Bi 19

Cng ty CP Thanh Nin bn cn h tr gp ti Chung c Hamona ng Trng

Cng nh, qun Tn Bnh vi cc loi cn h (vt: triu ng)

Din tch (m2) n gi

Cn h loi 1 3 PN 150 18

Cn h loi 2 3 PN 120 16

Cn h loi 3 3 PN 91 15

Cn h loi 1 2 PN 87 18

Cn h loi 2 2 PN 85 16

Cn h loi 3 2PN 80 15

Ngn hng Agribank h tr cho vay vi li sut tin vay c nh 11%/nm, thi

gian vay ti a 20 nm v s tin vay ti a l 80% tr gi cn h.

1. Hy tnh s tin gp u hng thng ca tng loi cn h vi thi gian tr gp l

5 nm, 10 nm, 15 nm v 20 nm.

2. Trong trng hp khch hng mua cn h loi 1 3PN tr gp trong 10 nm v

cho thu, mi thng tin thu thu c l 15 triu v n cui nm th 10 s sang

nhng cn h vi gi tng 20%. Nh vy khch hng c th u t c khng? thu

li mi nm l bao nhiu (NPV) v sut sinh li mi nm (IRR) l bao nhiu?

Cu 1: Hy tnh s tin gp u hng thng ca tng loi cn h vi thi gian

tr gp l 5 nm, 10 nm, 15 nm v 20 nm.

*T d liu bi ta tnh c tr gi cn h v s tin vay tng ng nh sau:

- Nhp vo D3 hm sau: =B3*C3

+ Gi nt in v ko theo hng dc t D3 n D8.

- Nhp vo E3 hm sau: =D3*80%

+ Gi nt in v ko theo hng dc t E3 n E8.

tnh c s tin gp u hng thng ta cn 3 i s cho hm PMT

+ Li sut (rate): 11%

65

+ Thi gian tr gp (nper): c gi tr thay i c (ti a l 20 nm) ta nhp

vo B11 gi tr bt k ( 20).

+ S tin vay cn h (pv): c gi tr thay i ty thuc vo loi cn h ta nhp

vo B12 s tin vay ca 1 cn h bt k.

Trong B13 ta dng hm PMT nh sau: =-PMT(B10/12;B11*12;B12)

Dng cng c Data Table tm nhy cho hai yu t thay i.

Trong , C17 (mu cam) ta nhp hm sau: =B13.

+ Tip theo chn vng t C17 n G23 Data trong th Data Tools chn



i trn bng d liu ( thi gian tr gp, B11); trong Column input cell chn c

gi tr thay i trn bng d liu ( s tin vay cn h, B12).

66

+ Nhp vo OK trong hp thoi Data Table ta s c c bng kt qu nh sau:

Cu 2: Trong trng hp khch hng mua cn h loi 1 3PN tr gp trong

10 nm v cho thu, mi thng tin thu thu c l 15 triu v n cui nm

th 10 s sang nhng cn h vi gi tng 20%. Nh vy khch hng c th u

t c khng? thu li mi nm l bao nhiu (NPV) v sut sinh li mi nm

(IRR) l bao nhiu?

*Ta thnh lp bng cho cu b nh sau:

67

Nhp s tin bn cn h cui nm 10 ( B27) hm nh sau: =D3*120% (v gi

tng 20%).

Trong Vn u t ban u (B30) ta nhp hm sau: =D3*20% (v ngn hng

ch cho vay ti a 80% tr gi cn h nn ta phi b ra s vn chim 20%).

Trong cc Vn u t cn li t nm 1 n nm 10 ta ch cn ly s tin tr

gp hng thng trong vng 10 nm ca cn h loi 1 3 PN nhn thm cho 12

thng, hm nh sau: =$E$18*12

Khon thu t vic cho thu cn h, ta nhp vo C31 hm sau: =15*12 (v tin

cho thu mi thng l 15 triu ng nn 1 nm s thu c l 15*12 triu ng).

Khon thu t vic bn cn h cui nm 10, ta nhp vo L32 hm sau: =B27

Tip theo ta tnh dng cn i trong B33 bng hm nh sau: =B31+B30-B29.

Gi nt in v ko theo hng ngang t B33 n L33.

Tnh ch s NPV ca d n, ta nhp vo B35 hm sau: =B33+NPV(11%;C33:L33)

Ta c bng kt qu

68

Bi 20

h tr cho nhn vin, cng ty t Lnh c chng trnh bn cn h tr gp cho

nhn vin c thm nin cng tc t 5 nm tr ln, cn h c din tch 100 m2, 3

phng ng ti tng 9, vi gi l 1,1 t VN, li sut c nh hng nm l 8,5%, tr

gp trong vng 20 nm.

a. Hy xc nh s tin gp u hng thng trong trng hp tr trc. Cho bit c

th s tin tr vn, tr li tng thng.

b. Trong trng hp Cng ty quy nh t l tr trc dao ng trong khong 20% -

30% tr gi cn h, thi gian gp t 10 nm 20 nm, hy xc nh s tin gp u

hng thng.

c. Nhn vin Kiu Trang ng k vi s tin tr trc l 20% tr gi cn h v hnh

thc gp u hng thng, thi gian gp 15 nm.

- Cho bit s tin b phi tr tng thng.

- B tr c 10 nm, n nm th 11 b mun tr dt im. Xc nh s tin

phi tr?

*Nhp d liu cho bi ton:

Cu a: S tin gp u hng thng trong trng hp tr trc. Cho bit c

th s tin tr vn, tr li tng thng.

Lp bng tnh s tin gp u hng thng trong vng 20 nm (240 thng).

69

.

.

.

Nhp vo B8 hm sau: =-IPMT($B$2/12;A8;$B$3*12;$B$4)

Sau gi nt in v ko theo hng dc t B8 n B261 (t thng u

tin n thng 240).

Nhp vo C8 hm sau: =-PPMT($B$2/12;A8;$B$3*12;$B$4)

Sau gi nt in v ko theo hng dc t C8 n C261.

Nhp vo D8 hm sau: =-PMT($B$2/12;$B$3*12;$B$4)

Sau gi nt in v ko theo hng dc t D8 n D261.

Kt qu

...

70

Cu b: Trong trng hp Cng ty quy nh t l tr trc dao ng trong

khong 20% - 30% tr gi cn h, thi gian gp t 10 nm 20 nm, hy xc

nh s tin gp u hng thng.

*Dng cng c Data Table xc nh s tin gp u hng thng c ph thuc

vo 2 i lng c gi tr thay i. Ta lp bng cho cu b nh sau:

- Trong T l tr trc (G7) nhp mt gi tr bt k nh yu cu ca bi dao

ng trong khong 20% n 30%.

- Trong G8 ta tnh s tin gp u hng thng c tr trc theo t l nhp

G7, ta g vo G8 hm sau: =-PMT(B2/12;B3*12;(B4-B4*G7))

- Trong G11 (mu cam) ta nhp hm sau: =G8

71

+ Tip theo chn vng t G11 n J14 Data trong th Data Tools chn



i trn bng d liu ( s nm tr gp, B3); trong Column input cell chn c gi

tr thay i trn bng d liu ( t l tr trc, G7)


72

Cu c: Nhn vin Kiu Trang ng k vi s tin tr trc l 20% tr gi cn

h v hnh thc gp u hng thng, thi gian gp 15 nm.

- Cho bit s tin b phi tr tng thng.

- B tr c 10 nm, n nm th 11 b mun tr dt im. Xc nh s

tin phi tr?

*Da vo cu b ta thy nu nhn vin Kiu Trang ng k vi s tin tr trc l

20% tr gi cn h v gp u hng thng vi thi gian 15 nm th b phi tr u

hng hng vi s tin l 8.665.708 ng (hn 8,66 triu ng).

*Nm th 11 b mun tr dt im th:

Nhp vo B264 hm sau:

=-CUMPRINC(B2/12;15*12;B4*80%;1;120;0)+1.1*20%

Nhp vo B265 hm sau: =B4-B264

Nhp vo B266 hm sau: =-IPMT(B2/12;121;15*12;B4*80%)

Nhp vo B267 hm sau: =B265+B266

*Bng kt qu nh sau:

Vy n nm th 11 b tr dt im vi s tin l 425.369.000 ng (hn 425 triu

ng).

73

Bi 21

Anh Ba d tnh mun mua tr gp mt cn nh tr gi 2 t ng. Ngi bn nh ni

rng, anh Ba ch cn tr trc cho h 20% tr gi nh, phn cn li tr gp trong vng

20 nm, vi li sut khng i 6% nm. Tuy nhin h cn ni rng, c th gim hoc

tng s tin tr trc, cng nh thi hn vay, v nu thch th mi thng bn c th tr

thm cho h bao nhiu (tr vo tin gc) gim bt s tin li. Anh Ba ang

lng l mua gp vi 3 phng n:

tr trc 20% v tr trong 20 nm.



Hy gip anh Ba xem nn chn phng n no?

*Da vo bi ta thit lp bi ton nh sau:

- Trong B4 v B5 ta nhp gi tr bt k tng ng vi 3 phng n bi cho.

- Nhp vo B6 hm sau: =B2*(1-B5) hoc =B2-B2*B5

- Nhp vo B8 hm sau: =-PMT(B3;B4;B6)

- Nhp vo B9 hm sau: =-PMT(B3/12;B4*12;B6)

tm phng n tt nht ta s dng cng c Scenario:

74





+ Trong Changing cells: chn c gi tr thay i ( B4:B5, chnh l thi gian

tr gp v t l tr trc).


ca trng hp 1.

75


trng hp tip theo

+ Trong Scenario name: g tn Trng hp 2.



ca trng hp 2


trng hp tip theo

76

+ Trong Scenario name: g tn Trng hp 3.



ca trng hp 3


xem kt qu

77


qu

+ Trong Result cell chn mc tiu (B8;B9 l s tin tr gp), ta c kt qu

nh sau:

78




Vy nu anh Ba mun tr gp u hng nm th chn phng n 2 l tt nht, cn

nu tr gp u hng thng th chn phng n 3 l tt nht.

79

Bi 22

Mt cng ty mun a ra mt dng sn phm mi, cng ty d kin bin ph cho

mi sn phm 12.630 ng, mc chit khu trung bnh cho cc i l l 30%. Hy

tnh li nhun trc thu ca cng ty trong cc trng hp sau:

a. Sn lng tiu th hng thng l 30.000 sn phm, gi bn mi sn phm l

35.000 ng, chi ph c nh hng thng l 170.000.000 ng.

b. Sn lng tiu th hng thng l 32.000 sn phm, gi bn mi sn phm l


c. Sn lng tiu th hng thng l 20.000 sn phm, gi bn mi sn phm l


d. Sn lng tiu th hng thng l 35.000 sn phm, gi bn mi sn phm l


*Da vo bi ta thit lp bi ton nh sau:

- Trong B4, B5 v B6 ta nhp gi tr bt k tng ng vi 4 phng n bi

cho.

- Nhp vo B8 hm sau: =B2*B4+B6

- Nhp vo B9 hm sau: =B5*B4*(1-B3)

- Nhp vo B11 hm sau: =B9-B8

80

tm phng n tt nht ta s dng cng c Scenario:




81


+ Trong Changing cells: chn c gi tr thay i ( B4:B6, chnh l sn lng,

gi bn v nh ph).

+ Sau nhp vo OK hp thoi Scenario Value xut hin ta in vo ny

gi tr ca trng hp a


trng hp tip theo

82

+ Trong Scenario name: g tn Trng hp b.



ca trng hp b


trng hp tip theo

83

+ Trong Scenario name: g tn Trng hp c.



ca trng hp c


trng hp tip theo

84

+ Trong Scenario name: g tn Trng hp d.



ca trng hp d


xem kt qu

85


qu

+ Trong Result cell chn mc tiu (B11 l li nhun), ta c kt qu nh sau:



86


Vy trng hp a li nhun trc thu cao nht.

87

Bi 23

Khch sn Hong Yn lp phng n thit k cc phng ca khch sn mi xy

dng theo cc loi A, B, C v Delux. Tng s phng ca khch sn l 55 phng. S

phng ti thiu cho mi loi l 4, S phng ti a cho loi C l 10 v loi Delux l 6.

Tng s vn u t ti a trang tr phng l 500.000 USD.

Cc thng s khc lin quan n loi phng nh sau:

Delux A B C

Chi ph trang tr 1 phng (s dng 1 nm) 20.500 12.400 7.200 5.200

Chi ph phc v phng (ngy) 25 12 7 4

Gi thu 1 ngy 160 100 70 60

Thu sut theo loi phng 20% 20% 20% 20%

Cng sut thu phng 1 nm 280 ngy 300 ngy 300 ngy 325 ngy

Hy tnh s phng b tr cho mi loi l bao nhiu li gp trong 1 nm ca

khch sn t gi tr ti a. Cho bit:

Li gp = tng doanh thu (tng chi ph trang tr + chi ph phc v) thu

Tng doanh thu = tng doanh thu ca 4 phng

Doanh thu phng = gi thu phng * s phng * cng sut thu 1 nm

Chi ph phc v 1 nm = ph phc v phng (ngy) * cng sut thu phng *

s phng

Thu = thu sut * doanh thu

*Thit lp m hnh bi ton

Gi 1, 2, 3, 4 ln lt l s phng cho mi loi Delux, A, B v C.

Hm mc tiu:

Li gp = [tng doanh thu (tng chi ph trang tr + chi ph phc v) thu] max

Rng buc:

{

1 + 2 + 3 + 4 = 55205001 + 124002 + 72003 + 52004 50000

4 1 64 24 3

4 4 10

*Gii bi ton bng phn mm Excel

88

Trong F15 l gi tr hm mc tiu cn tm v t F17 n F22 l gi tr

tng ca cc rng buc.

Theo bi ta c:

Li gp hay li nhun mc tiu = tng doanh thu (tng chi ph trang tr + chi ph

phc v) thu

Ta cn to cc dng tnh ton bao gm: Tng doanh thu, Tng chi ph trang tr, Chi

ph phc v v Thu tnh c Li nhun mc tiu cn tm.

Vi dng s 10 l s phng li nhun cc i th ta khng cn nhp gi tr (hoc

nhp gi tr 0 ht cho tt c cng c, khi dng hm solver cng c ny s tm n

chnh xc gi tr ca n).

Nhp vo B11 (Doanh thu phng = gi thu phng * s phng * cng sut

thu 1 nm) hm nh sau: =B5*B7*B10

+ Sau giu nt in v ko theo hng ngang t B11 n E11.

Nhp vo B12 (Tng chi ph trang tr = chi ph trang tr 1 phng * s phng)

hm nh sau: =B3*B10


Nhp vo B13 (Chi ph phc v 1 nm = ph phc v phng (ngy) * cng

sut thu phng * s phng) hm nh sau: =B4*B7*B10


Nhp vo B14 (Thu = thu sut * doanh thu) hm nh sau: =B11*B6


Nhp vo B15 (Li nhun mc tiu = tng doanh thu (tng chi ph trang tr +

chi ph phc v) thu) hm nh sau: =B11-(B12+B13)-B14


Nhp vo F15 (mu ) hm tng ca li nhun mc tiu: =sum(B15:E15)

*Tip theo l nhp cc rng buc v gi tr tng cc rng buc

Nhp vo F17 hm sau: =B17*$B$10+C17*$C$10+D17*$D$10+E17*$E$10

+ Sau gi nt in v ko theo hng dc t F17 n F22.

89

Trong vng ti thiu, ti a v bng nhp cc gi tr m gi tr tng rng buc c

th t c.

*Thm chc nng Solver vo Excel nh sau (nu my bn cha c):

- File Option Add-Ins Solver Add-in Go Solver Add-in OK.

*M chc nng Solver ln v thc hin cc thao tc:

- Data trong th Analysis chn Solver hp thoi Solver Parameters xut hin:

+ Trong Set Objective chn a ch hm mc tiu ( F15)

+ Trong phn To: chn gi tr k vng hm mc tiu (Max)

+ Trong By Changing Variable Cells chn a ch cc bin thay i (B10:E10)

+ Trong Subject to the Constraints: hin th cc iu kin rng buc ca bi ton:

Rng buc 1: $B$10:$E$10 = integer (s phng phi l s nguyn)

Rng buc 2: $F$17 = $I$11

Rng buc 3: $F$18 $H$18

Rng buc 4: $F$19 $H$19

90

Rng buc 5: $F$19 $G$19

Rng buc 6: $F$20 $G$19

Rng buc 7: $F$21 $G$21

Rng buc 8: $F$22 $H$22

Rng buc 9: $F$22 $G$22

- hin th cc rng buc ta nhp chut vo Add hp thoi Add Constraint

xut hin ln lt add 9 rng buc trn Cancel.

- Quay v hp thoi Solver Parameters, nhp vo Solver hp thoi Solver

Result xut hin.

Nhp chut vo Answer OK ta s c bng kt qu.

91

Vy s phng cn b tr cho tng loi t li nhun cc i l loi Delux 4 phng,

loi A 13 phng, loi B 28 phng, loi C 10 phng.

92

Bi 24

Mt ngi c s tin 70 triu ng d nh u t vo cc khon sau:

- Gi tit kim khng k hn vi li sut 6,5%/nm.

- Gi tit kim c k hn vi li sut 8,5%/nm;

- Mua tn phiu vi li sut 10%/nm;

- Cho doanh nghip t nhn vay vi li sut 13%/nm.

Mi khon u t u c ri ro ca n nn ngi quyt nh u t theo cc ch

dn sau y ca nh t vn:

- Khng cho doanh nghip t nhn vay qu 20% s tin;

- S tin mua tn phiu khng c vt qu tng s tin u t vo 3 khon

cn li;

- u t t nht 30% tng s tin vo gi tit kim c k hn v mua tn phiu;

- T l tin gi tit kim khng k hn trn tin gi tit kim c k hn khng

c qu 1/3.

Hy xc nh s tin u t vo mi khon tng s tin li thu c cao nht v

tun theo cc ch dn ca nh t vn, bit ngi u t ht s tin hin c.


Gi 1, 2, 3, 4 (triu ng) ln lt l s tin u t vo tng phng n 1, 2, 3, 4

Hm mc tiu: f(x) = 0,065x1 + 0,085x2 + 0,1x3 + 0,13x4 max

Rng buc:

{

1 + 2 + 3 + 4 = 70 4 141 + 2 3 + 4 0

2 + 3 21 31 2 0

xj 0; j = 1,4


Trong F5 l gi tr hm mc tiu cn tm v F7 n F11 l gi tr tng ca

cc rng buc.

- Nhp vo F5 hm sau: =B5*$B$4+C5*$C$4+D5*$D$4+E5*$E$4

93

- Nhp vo F7 hm sau: =B7*$B$4+C7*$C$4+D7*$D$4+E7*$E$4

- Tip theo gi nt in v ko t F7 cho n F11.





+ Trong Set Objective chn a ch hm mc tiu ( F5)


+ Trong By Changing Variable Cells chn a ch cc bin thay i (B4:E4)


Rng buc 1: $F$7 = $I$7

Rng buc 2: $F$8 $H$8

Rng buc 3: $F$9 $G$9

Rng buc 4: $F$10 $G$10

Rng buc 5: $F$11 $H$11

94

Rng buc cc bin khng m: $B$4: $E$4 0



- Quay v hp thoi Solver Parameters, nhp vo Solver hp thoi Solver

Result xut hin.

Vy khng tm c phng n tha mn yu cu bi ton trn.

+ Nhp vo Cancel kt thc.

95

Bi 25

Mt cng ty a c nh xy 50 cn nh gm 4 loi: nh trt, nh 2 tng, nh 3 tng

v bit th. Thi gian (ngy) xy nh mi loi nh sau:

Hng mc Loi nh

Nh trt 2 tng 3 tng Bit th

Xy th 20 60 90 120

Trang tr 5 30 50 100

Li 50 triu 100 triu 200 triu 300 triu

Theo kinh nghim kinh doanh th mi loi nh phi xy t nht 5 cn th mi c li

cho qui trnh thi cng v trang b k thut. Ngoi ra thi gian phi hon thnh cng

trnh ti a l 4 nm trnh bin ng th trng v li sut ngn hng.

Hy xc nh k hoch xy dng nh ti u?

96

Bi 26

Cng ty ha cht s dng 3 loi nguyn liu A, B, C sn xut 2 sn phm I v

II. nh mc chi ph nguyn liu cho vic sn xut sn phm nh sau:

Nguyn liu nh mc chi ph Kh nng

cung ng Sn phm I Sn phm II

A 0,4 0,5 20

B 0,2 5

C 0,6 0,3 21

Li nhun bin cho mi sn phm I, II tng ng l 40 v 30 ngn ng cho mi

kg.

Vy Cng ty cn sn xut mi loi bao nhiu kg cc i li nhun?


Gi x1, x2 ln lt l s kg cn sn xut ng vi tng sn phm I v II.

Hm mc tiu:

f(x) = 40x1 + 30x2 max

Rng buc:

{

0,41 + 0,52 20 0,22 50,61 + 0,32 21

xj 0; j = 1,2


Trong D5 l gi tr hm mc tiu cn tm v D7 n D9 l gi tr tng ca

cc rang buc.

- Nhp vo D5 hm sau: =B5*$B$4+C5*$C$4

- Nhp vo D7 hm sau: =B7*$B$4+C7*$C$4

- Tip theo gi nt in v ko t D7 cho n D9.


97




+ Trong Set Objective chn a ch hm mc tiu ( D5)


+ Trong By Changing Variable Cells chn a ch cc bin thay i (B4:C4)


Rng buc 1: $D$7 $E$7



Rng buc cc bin khng m: $B$4: $C$4 0



98

- Nhp Cancel quay v hp thoi Solver Parameters, kch chut vo Solver

hp thoi Solver Result xut hin.

+ Trong phn Reports nhp Answer Nhp vo OK, xem kt qu.

Vy cn sn xut 25 kg sn phm loi I v 20 kg sn phm loi II th cc i li

nhun.

99

PHN 2: ACCESS

PHN 1: EXCELBi 1Bi 2Bi 3Bi 4Bi 5Bi 6Bi 7Bi 8Bi 9Bi 10Bi 11Bi 12Bi 13Bi 14Bi 15Bi 16Bi 17Bi 18Bi 19Bi 20Bi 21Bi 22Bi 23Bi 24Bi 25Bi 26PHN 2: ACCESS

(Phần Excel) ng d n chi tiết cách giải (giải đầy...

Documents

Transcript of (Phần Excel) ng d n chi tiết cách giải (giải đầy...