Question

Hillsdale Media is a specialty kitchen cabinet maker that produces cabinets to order. It is a mature business that earned EBITDA of $900,000 on revenues of $5 million in the most recent year and is expected to continue to generate these figures in perpetuity. The company is considering carrying some of its most popular models in inventory, with an eye on increasing sales and operating profits. It has collected the following information To carry inventory, the company will have to invest $2.25 million in a storage facility, which will be depreciated straight line over ten years down to a salvage value of $250,000 With the inventory, the company expects its annual revenues to increase to $7.5 million and its overall EBITDA margin (EBITDA as % of sales) to increase to 20% For the next decade, the inventory will be maintained at 10% of total revenues, with the investment made at the start of each year. The inventory will be sold for book value at the end of ten years. The cost of capital for the company is 10% and it faces a 40% tax rate a. Estimate the NPV of the project (carrying inventory) assuming at ten-year life for the investment (4 points) B. Estimate the breakeven EBITDA margin for the company, for the investment to have a zero NPV, if you now assumetha that the project lasts forever. Part A has been answered in Chegg but need to know how to do part B in excel.

Answer 1

Goal Seek Setting

Formula sheet

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

2

3

Initial investment

2250000

4

Estimated life

10

5

Salvage Value

250000

6

7

Depreciation per year

=(Cost - Salvage Value)/Estimated Life

8

=(D3-D5)/D4

=(D3-D5)/D4

9

For financial breakeven, EBITDA margin should be such that the NPV of incremental Cash flow becomes zero.

10

EBITDA Margin

0.196775731058894

11

Incremental cash flow of the new project can be calculated as follows:

12

Year

0

1

2

3

4

5

6

7

8

9

10

13

Total Sales

7500000

7500000

7500000

7500000

7500000

7500000

7500000

7500000

7500000

7500000

14

Total EBITDA

=E13*$D$10

=F13*$D$10

=G13*$D$10

=H13*$D$10

=I13*$D$10

=J13*$D$10

=K13*$D$10

=L13*$D$10

=M13*$D$10

=N13*$D$10

15

Initial EBITDA

900000

900000

900000

900000

900000

900000

900000

900000

900000

900000

16

Incremental EBITDA

=E14-E15

=F14-F15

=G14-G15

=H14-H15

=I14-I15

=J14-J15

=K14-K15

=L14-L15

=M14-M15

=N14-N15

17

Depreciation

=-$D$8

=-$D$8

=-$D$8

=-$D$8

=-$D$8

=-$D$8

=-$D$8

=-$D$8

=-$D$8

=-$D$8

18

EBIT

=E16+E17

=F16+F17

=G16+G17

=H16+H17

=I16+I17

=J16+J17

=K16+K17

=L16+L17

=M16+M17

=N16+N17

19

Taxes

=-E18*40%

=-F18*40%

=-G18*40%

=-H18*40%

=-I18*40%

=-J18*40%

=-K18*40%

=-L18*40%

=-M18*40%

=-N18*40%

20

EBIT*(1-T)

=E18+E19

=F18+F19

=G18+G19

=H18+H19

=I18+I19

=J18+J19

=K18+K19

=L18+L19

=M18+M19

=N18+N19

21

Add Depreciation

=-E17

=-F17

=-G17

=-H17

=-I17

=-J17

=-K17

=-L17

=-M17

=-N17

22

Incremental Operating Cash Flow

=E20+E21

=F20+F21

=G20+G21

=H20+H21

=I20+I21

=J20+J21

=K20+K21

=L20+L21

=M20+M21

=N20+N21

23

Initial investment

=-D3

24

Investment in inventory

=-E13*10%

=-F13*10%

=-G13*10%

=-H13*10%

=-I13*10%

=-J13*10%

=-K13*10%

=-L13*10%

=-M13*10%

=-N13*10%

25

Increase in working capital

=D24

=E24-D24

=F24-E24

=G24-F24

=H24-G24

=I24-H24

=J24-I24

=K24-J24

=L24-K24

=M24-L24

26

Recovery of working capital

=-D25

27

Salvage Value

=D5

28

Incremental Cash Flow

=D23+D25

=E22+E25

=F22+F25

=G22+G25

=H22+H25

=I22+I25

=J22+J25

=K22+K25

=L22+L25

=M22+M25

=N22+N25+N26+N27

29

30

NPV Calculation:

31

32

NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.

33

Given the following cash flow and WACC, NPV for the project can be calculated as follows:

34

Year

0

1

2

3

4

5

6

7

8

9

10

35

Free Cash Flow (FCF)

=D28

=E28

=F28

=G28

=H28

=I28

=J28

=K28

=L28

=M28

=N28

36

MARR (i)

0.1

37

(P/F,i,n) for each year

=1/((1+$D36)^E34)

=1/((1+$D36)^F34)

=1/((1+$D36)^G34)

=1/((1+$D36)^H34)

=1/((1+$D36)^I34)

=1/((1+$D36)^J34)

=1/((1+$D36)^K34)

=1/((1+$D36)^L34)

=1/((1+$D36)^M34)

=1/((1+$D36)^N34)

38

Present Value of cash flows = FCF*(P/F,i,n)

=E35*E37

=F35*F37

=G35*G37

=H35*H37

=I35*I37

=J35*J37

=K35*K37

=L35*L37

=M35*M37

=N35*N37

39

Present value if future cash flows

=SUM(E38:N38)

=SUM(E38:N38)

40

41

NPV for Project

=Present value fo future cash flows - Initial investment

42

=D39+D35

=D39+D35

43

44

Goal seek function of excel can be used to find EBITDA margin at which NPV becomes zero.

45

46

EBITDA margin for NPV to be Zero is

=D10

47