Hansson Private Label Excel Solution

HANSSON PRIVATE LABEL

Operating Results: Revenue

Less: Cost of Goods Sold #o%% Po&'t

Income Statement

2003

2004

2005

2006

2007

$5034

$5437

$5!72

$636"

$6!07

405.2

432.3

496.2

513.4

558.2

(!2

"" """4

("0

"227

"225

Less Less:: Sell Sellin ing, g, Gene Genera rall & Adi Adini nist stra rati ti!e !e

3".8 3".8

44.6 44.6

45.8 45.8

51.5 51.5

49.0 49.0

EBIT)A

604

66!

452

7"2

735

Less: #e$re%iation EBIT

Less: nterest '($ense EBT

6.8

6.2

6.0

5.9

6.1

536

606

3(2

653

674

5.5

5.8

5.9

5.3

3.3

4!"

54!

333

600

64"

Less: )a(es

19.2

22 22.0

13 13.3

24 24.0

25 25.6

Net Income

$2!(

$3 $32!

$2 $200

$3 $360

$3 $3!5

8.0/ 20.5/ 8.2/ 12.3/ 11.1/ 6.0/ 40.1/

8.0/ 15.5/ ".8/ "."/ 6."/ 3.4/ 39.9/

8.3/ 19.3/ 8.1/ 11.2/ 10.3/ 5."/ 40.0/

".0/ 18.0/ ".2/ 10.8/ 9.9/ 5."/ 39.9/

*a+'n%,

*e!en+e GrotGross argin Selling, General & Adinistrati!e*e!en+e ')#A argin ') argin et n%oe argin argin 'ffe%ti!e )a( *ate

A 19.5/ ".5/ 12.0/ 10.6/ 5."/ 39.9/

Harvard Harvar d Business Publishing Publishing


Assets:

Cas- & Cas- '+i!alents A%%o+nts *e%ei!ale n!entor Tota- /uent A%%et% 7ro$ert, 7lant & '+i$ent t-er onC+rrent Assets Tota- A%%et%

Ba-ance S.eet

2003

2004

2005

2006

2007

4.3

5.1

4.8

".8

5.0

62.1 5"." "24"

"0.1 58.0 "332

"8.8 61.2 "44!

8".1 61.9 "56!

93.3 6".3 "656

201.4 12.3 $337!

202.9 12.1 $3 $34!2

203.1 11.8 $3 $35(7

202.3 12.5 $3 $37"6

204.4 10.8 $3 $3!0!

42. 42.2 2

45. 45.0 0

51. 51.6 6

53. 53.4 4

58. 58.1 1

91.6

82.8

"3.8

65.8

54.8

204.0 $337 $337! !

22 2 20.4 $34! $34!2 2

23 2 34.3 $35( $35(7 7

2 52.4 $37" $37"6 6

26 2 6".9 $3!0 $3!0! !

Liabilities & Owners' Equity: Equity:

A%%o A%%o+n +nts ts 7aa 7aal lee & A%%r A%%r+e +ed d Lia Liail ilit itie iess Long)er #et ners '+it Totaota- L'a L'a''-'t 't'e 'e%% 1 One One% % Eu' Eu't t

Net !r"ing !r"ing #apital:

A%%o+nts *e%ei!ale

62.1

"0.1

"8.8

8".1

93.3

7l+s: n!entor Less Less:: A%%o+ A%%o+nts nts 7aal 7aalee & A%%r A%%r+ed +ed '($en '($ense se Net o'n+ /a8'ta-

5"." 42.2 42.2 776

58 5 8.0 45.0 45.0 !3"

61 6 1.2 51.6 51.6 !!4

61 6 1.9 53.4 53.4 (56

6" 6 ".3 58.1 58.1 "025

15.4/

15.3/

15.1/

15.0/

15.1/

et ;or
44.4(

46.4(

48.3(

49.3(

49.3(

#as Sales n!entor #as 7aale +tstanding =1>

41.3( 34.3(

38.4( 34.0(

3".5( 34.3(

35.0( 34.0(

35.6( 34.4(

Cas-toCas-

51.4(

50.8(

51.6(

50.3(

50.5(



/a%. 9-o

#ash $r!% Operati!ns: Net Income

2003 $2!(

2004 $3 $32!

2005 $2 $200

2006 $3 $360

2007 $3 $3!5

7l+s: #e$re%iation Less: n%rease in A%%o+nts *e%ei!ale Less: n%rease in n!entor 7l+s: n%rease in A%%o+nts 7aale Tota- /a%. 9om O8eat'on%

6.8 3.1 0.5 0.3 $324

6.2 8.0 0.3 2.8 $335

6.0 8." 3.2 6.6 $207

5.9 8.3 0." 1.8 $347

6.1 6.2 5.4 4." $377

".3 0.5 $7!

"." =0.2> $75

6.2 =0.3> $5(

5.1 0." $5!

8.2 =1."> $65

8.0 14.4 $224

8.8 16.4 $252

9.0 6.1 $"5"

8.0 1".9 $25(

11.0 23.0 $340

$22

$0!

<$03=

$30

<$2!=

#ash $r!% nvesting:

Ca$ital '($endit+res 7l+s: n%reases in t-er onC+rrent Assets Tota- /a%. :%e; 'n Inve%t'n+ #ash $r!% $inaning: *e$aent of #et 7l+s: #i!idend 7aents /a%. :%e; 'n 9'nanc'n+ Tota- /a%. #eneate;



*aet

/om8aa-e /om8an'e%

Net nte8'%e

#!%pany:

Va-ue

)et

Va-ue

CatCat-le leen en Sin% Sin%la lair ir Genera Generall ?ealt?ealt- & ea

298. 298.9 9 319.3 319.3

1,32 1,329. 9.6 6 63.1 63.1

Eu't

N et

Boo

Beta Revenue EBIT)A

EBIT

Income

Va-ue

1,62 1,628. 8.5 5 382.4 382.4

2.22 2.22 1.95 1.95

163. 163.4 4 51.9 51.9

21.5 21.5 23.8 23.8

=1,1 =1,112 12.8 .8>> 204.2 204.2

1,346 1,346.8 .8 446.1 446.1

255. 255.3 3 61.9 61.9

;oens oens Care Care Co$a Co$a

498.6 498.6

55.3 55.3

553.9

1.14 1.14

39".3

52." 52."

49.3 49.3

"".0 "".0

43." 43."

S
1,204.2 1,204.2

3"1.9

1,5"6.1 1,5"6.1

1.35

1,24".6

155.8

125.4

65.3

316.2

#!%pany:

10.0(

P>E 13.9(

P>B

19.0/

EBIT Ean'n+% Revenue EBIT)A 12.1/ 1.6/ 1.2( 6.4(

EBIT

Cat-leen Sin%lair

EBIT)A

General ?ealt- & ea+t ;oens Care Co$an

13.9/ 13.3/

11.6/ 12.4/

5.3/ 19.4/

0.9( 1.4(

6.2( 10.5(

".4( 11.2(

13.4( 6.5(

1.6( 11.4(

S
12.5/ "46?

10.1/ ""6?

5.2/ 7(?

1.3( "2@

10.1( !3@

12.6( "03@

18.4( "3"@

3.8( 56@





e'+.te; Avea+e /o%t o& /a8'ta-

)et>

)et>

Eu't

)et

A%%et

#!%pany:

Va-ue

Eu't

Beta

Beta

Cat-leen Sin%lair General ?ealt- & ea+t

81.6/ 16.5/

444.9/ 19.8/

2.22 1.95

0.25 0.00

Beta 0."9 1."4

;oens Care Co$an

10.0/

11.1/

1.14

0.00

1.0"

S
23.6/ 32(?

30.9/ 4("?

1.35 "67

0.00 006

""!

1.14

)et>

)et>

A%%et

Eu't

/o%t o&

/o%t o&

Va-ue

Eu't

Beta

Beta

Eu't

)et

A//

A%%um8t'on%, 10@ear )reas+r

0.0/ 5.0/

0.0/ 5.3/

1.18 1.18

1.18 1.22

9.6"/ 9.86/

"."5/ "."5/

3."5/

9.6"/ 9.60/

ar
5.00/

10.0/ 15.0/

11.1/ 1".6/

1.18 1.18

1.26 1.31

10.0"/ 10.30/

"."5/ "."5/

9.53/ 9.45/

)a( *ate

40.0/

20.0/ 25.0/

25.0/ 33.3/

1.18 1.18

1.36 1.42

10.56/ 10.86/

"."5/ "."5/

9.38/ 9.31/

'st. ?ansson ')#A +lti$le 'st. ?ansson 'nter$rise al+e

".0( 514.5

'(isting et #et 7l+s: e e '($ansion #et Tota- E%t'mate; )et

49.8 5".8 "076

'(isting # 'stiated e #

9."/ 20.9/

Ass+ed #et eta 'stiated Cost of #et

0.00 "."5/



Inve%tment

#!st #!%p!nents:

Ao+nt

'st. L if ife

Ba%ilit '($ansion an+fa%t+ring '+i$ent 7a% Tota- Inve%tment

10,000 20,000 15,000 12,81" 57!"7

20rs. 10 10rs. 10rs.

7er%ent Cost of #e$r aintenan%e aintenan%e 500 2,000 1,500 0 4000

=1> )-e in%rease in or
5.0/ 5.0/ 5.0/ 0.0/

500 1,000 "5 0 0 2250



Operating Assu%pti!ns

Revenue Pr!(eti!n: Pr!(eti!n: )otal Ca$a%it =000s> Ca$a%it DtiliEation :n't Vo-ume Selling 7ri%e 7er Dnit  Groing at Revenue Pr!duti!n #!sts: *a aterials 7er Dnit Groing at * Cost an+ an+fa fa%t %t+r +rin ing g !er !er-e -ead ad Gro Groin ing g at aintenan%e '($ense Groing at Tota- Po;uct'on /o%t )alaried Lab!r #!st: anagers A!erag eragee Ann+al B+ll ll Loade aded Cost Tota- Sa-a'e; Lao /o%t H!urly Lab!r #!st: A!erag eragee B+l B+ll Loa Loaded ?o+rl rl Cost ?o+rs 7er @ear /o%t Pe Hou- Em8-oee +er of ?o+rl ?o+rl ;or
2.0/

1.0/ 3.0/ 3.0/ 3.0/

3.5/ .5/

3.5/ .5/

200(

20"0

20""

20"2

20"3

20"4

20"5

20"6

20"7

20"!

80,000 60.0/

80 80,000 65.0/

80 8 0,000 "0.0/

80 80,000 "5.0/

80 8 0,000 80.0/

80 80,000 85.0/

80 8 0,000 85.0/

80 80,000 85.0/

80 8 0,000 85.0/

80 80,000 85.0/

4!000

52 52000

56 56000

60 60000

64 64000

6! 6!000

6! 6!000

6! 6!000

6! 6!000

6! 6!000

1.""

1.81

1.84

1.88

1.92

1.95

1.99

2.03

2.0"

!4(60

(3!!"

"03"24

""2700

"226"!

"32!!7

"35545

"3!256

"4"02"

"43!4"

0.94 45,120 3,60 3,600 0 2,250

0.95 49,369 3,"0 3,"08 8 2,318

0.96 53,698 3,81 3,819 9 2,38"

0.9" 58,109 3,93 3,934 4 2,459

0.98 62,603 4,05 4,052 2 2,532

0.99 6",181 4,1" 4,1"3 3 2,608

1.00 6",852 4,29 4,299 9 2,68"

1.01 68,531 4,42 4,428 8 2,"6"

1.02 69,216 4,56 4,560 0 2,850

1.03 69,908 4,69 4,69" " 2,936

50(70

553(4

5((04

6450"

6("!7

73(62

74!3!

75726

76627

7754"

4 160.0

4 165.6

6 1"1.4

6 1"".4

8 183.6

8 190.0

8 196."

8 203.6

8 210."

8 218.1

6400

6624

"02!4

"0644

"46!!

"5202

"5734

"62!5

"6!55

"7445

20.00 .00 2,000

20."0 ."0 2, 2 ,000

21.42 .42 2,000

22.1" .1" 2, 2 ,000

22.95 .95 2,000

23."5 ."5 2, 2 ,000

24.59 .59 2,000

25.45 .45 2, 2 ,000

26.34 .34 2,000

2".26 .26 2, 2 ,000

40000

4"400

42!4(

4434(

45(0"

47507

4("70

50!("

52672

545"6

450

4"3

509

545

582

618

618

618

618

2.12

618

"!0 "!000 000 0 "(5 "(570 70( ( 2"! 2"!"4 "40 0 24" 24"(0 (02 2 267 26706 060 0 2(3 2(36! 6!2 2 303 303(6 (6" " 3"4 3"460 600 0 325 3256" 6"" " 337 33700 007 7 "!6400 6(6"0 "5350

20 202333 75 7562! "! "!253

22 22!424 !2 !2747 20 2037!

25 252546 !( !(756 22 22(44

2! 2!"74! (7 (7362 25 25256

30 30!!!5 "04!5" 2! 2!036

3" 3"(6(6 "06!07 2! 2!73!

33 330!!5 "0!!"4 2( 2(442

34 342466 ""0!73 30 30"4!

35 354452 ""2(!7 30 30!55

Selling, General & Adinistrati!e*e!en+e Sell Sellin ing, g, Gene Genera rall & Adi Adini nist stra rati ti!e !e* *e! e!en en+e +e

".8/ 6,59 6,594 4

".8/ ",28 ",286 6

".8/ 8,00 8,004 4

".8/ 8,"4 8,"4" "

".8/ 9,51 9,51" "

".8/ 10,3 10,314 14

".8/ 10,5 10,520 20

".8/ 10,"3 10,"30 0

".8/ 10,9 10,945 45

".8/ 11,16 1,164 4

EBIT)A

!756

"0(67

"2374

"4"(7

"573(

"7723

"!2"!

"!7""

"(202

"(6("

6 6(67 4"!0 4 4000 <"""=

! !374 5024 4 4000 <"270=

"0"(7 6""! 4 4000 <"3"2=

""73( 7044 4 4000 <"362=

"3723 !234 4 4000 <"407=

"42"! !53" 4 4000 <364=

"47"" !!27 4 4000 <372=

"5202 ("2" 4 4000 <37(=

"56(" (4"5 4 4000 <3!7=

EBIT O8eat'n+ Po&'t A&te Ta@ A;; )e8ec'at'on Le%%, Incea%e 'n /

4756 2!54 4000 <"2!"7=

9utue /a%. 9-o% Tem'na- ca%.&-o IRR 't.out Tem'naA// NPV 't. tem'naIRR 't. tem'na-

!r"ing #apital Assu%pti!ns *+,: #as Sales +tstanding #as Sales n!entor #as 7aale +tstanding

<45000=

<5(63=

!070

7755

!!06

(6!2

"0 "0!27

"2 "2"66

"2 "2455

"2 "2742

2" 2""!"

8,153 "205!? (3!?

4".6( 3".6( 34.2(

4".6( 3".6( 34.2(

4".6( 3".6( 34.2(

4".6( 3".6( 34.2(

4".6( 3".6( 34.2(

4".6( 3".6( 34.2(

4".6( 3".6( 34.2(

4".6( 3".6( 34.2(

4".6( 3".6( 34.2(

4".6( 3".6( 34.2(

?o+rl Laor Cost 7er Dnit

0.38

0.38

0.39

0.40

0.42

0.43

0.45

0.46

0.48

0.50

Dnits 7er )otal Laor ?o+rs

0.05

0.06

0.06

0.06

0.06

0.06

0.06

0.06

0.06

0.06

13/ 11,223 ",263 6,613

13/ 12,231 ", ","83 ",08"

13/ 13,436 8, 8 ,516 ","54

13/ 14,683 9, 9,23" 8,411

13/ 15,9"5 10,020 9,123

13/ 1",313 10,"90 9,825

13/ 1",660 10,992 10,008

13/ 18,013 11,198 10,19"

13/ 18,3"3 11,410 10,389

13/ 18,"41 11,628 10,588

=1> ased on -istori%al a!erages.

A* 7er%entage A* n!entor A7 A%%+red '($enses

O8eat'n+ A%%um8t'on%

13/

Harvard Business Publishing

Hansson Private Label Excel Solution

Recommend Documents