Pistachio inc. is thinking of building a bakery to itnroduce French cookies, so-
ID: 2748609 • Letter: P
Question
Pistachio inc. is thinking of building a bakery to itnroduce French cookies, so-called macarons, to the Newark market. A preliminary marketing study, which cost $75,000 and which was completed last year, showed significant demand for macarons in the Newark area. The bakery is expeced to last for 25 years. Its initial cost is $220,000 This cost can be depreciated over 15 years using straight line depreciation. The salvage value of the equipment in the bakery after 15 years is $20,000. After 15 years the bakery can be renovated. The cost of the renovation will be $80,000 and can be depreciated (again using straight line depreciation) over the remaining 10 years of the bakery's life. The salvage value of the equipment after the remaining 10 years will be $10,000. The land the bakery is built on could be rented out for $12,500 a year for 25 years.
The bakery will be able to produce 75,000 macarons a year. The price of a macaron is currently $1.55. It is expected to grow at a rate of 5% per year for the first 2 years, then at 2% per year for 6 years and finally stays the same thereafter for the remainer of the bakery's life. Pistachio, INc expects to be able to sell all the macarons that it can produce. The basic ingredients for a macaron currently (t=0) cost $0.25 These costs are expected to grow by 1% through the lifetime of the project. The labor required to operate the bakery is expected to cost a total of $45,000 dollars in t=1 and this is expected to increase 4% thereafter. There is no working capital requirement.
The firm's total tax rate including local taxes is 36%. Pistachio Inc expects to make substantial profits on its other products so that it can offst any losses on the bakery for tax purposes.
1. Construct a spreadsheet in EXCEL to find annual cash flows of the macarons project. All cash flows occur at year's end.
2. Currently the firm's market cap is $800 million. Its equity beta is 1.25 It also has 200 million debt outstanding. The total amount of interest paid on its debt each year is 5 million. The risk free rate is 5% and market risk premium is 8. What's the weighted average cost of capital for the firm?
3. What's the NPV of the project?
Explanation / Answer
Premilinary expenses 75000 Initial cost 220000 Depreciaton 15 year Salvage 20000 Depreciation yearly to be charged 13333.3333 Cost of renovaction 80000 Salvage 10000 period 10 year Depreciation for next 10 year 7000 Rental income for rent 12500 p.a Yearly production 75000 Current price 1.55 Growth rate 5% for first 2 year and tehreafter Current cost 0.25 grwoth 1% Cost of labor 45000 Growth 4% Tax rate 36% Year 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Inflow yearly production 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 75000 Selling price 1.55 1.6275 1.708875 1.743053 1.777914 1.813472 1.813471821 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 1.813472 Total production revenue 116250 122062.5 128165.6 130728.9 133343.5 136010.4 136010.3866 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 136010.4 Rental income 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 12500 Total income 128750 134562.5 140665.6 143228.9 145843.5 148510.4 148510.3866 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 148510.4 Outlow Cost 0.25 0.2525 0.255025 0.257575 0.260151 0.262753 0.26538 0.268033838 0.270714 0.273421 0.276156 0.278917 0.281706 0.284523 0.287369 0.290242 0.293145 0.296076 0.299037 0.302027 0.305048 0.308098 0.311179 0.314291 0.317434 0.320608 Production cost total 18937.5 19126.88 19318.14 19511.33 19706.44 19903.5 20102.53785 20303.56 20506.6 20711.66 20918.78 21127.97 21339.25 21552.64 21768.17 21985.85 22205.71 22427.77 22652.04 22878.56 23107.35 23338.42 23571.81 23807.52 24045.6 Operating labor cost 45000 46800 48672 50618.88 52643.64 54749.38 56939.35583 59216.93 61585.61 64049.03 66610.99 69275.43 72046.45 74928.31 77925.44 81042.46 84284.16 87655.52 91161.74 94808.21 98600.54 102544.6 106646.3 110912.2 115348.7 Total Cost 63937.5 65926.88 67990.14 70130.21 72350.07 74652.88 77041.89368 79520.49 82092.21 84760.7 87529.77 90403.4 93385.7 96480.95 99693.61 103028.3 106489.9 110083.3 113813.8 117686.8 121707.9 125883 130218.2 134719.7 139394.3 Earning before depreciatin and tax 64812.5 68635.63 72675.48 73098.73 73493.44 73857.5 71468.49289 68989.89 66418.18 63749.69 60980.61 58106.98 55124.69 52029.44 48816.78 45482.08 42020.52 38427.1 34696.6 30823.61 26802.5 22627.4 18292.23 13790.66 9116.099 Depreciation 13333.33 13333.33 13333.33 13333.33 13333.33 13333.33 13333.33333 13333.33 13333.33 13333.33 13333.33 13333.33 13333.33 13333.33 13333.33 7000 7000 7000 7000 7000 7000 7000 7000 7000 7000 Profit before tax 51479.17 55302.29 59342.15 59765.4 60160.11 60524.17 58135.15956 55656.56 53084.85 50416.36 47647.28 44773.65 41791.35 38696.1 35483.45 38482.08 35020.52 31427.1 27696.6 23823.61 19802.5 15627.4 11292.23 6790.662 2116.099 Tax rate 18532.5 19908.83 21363.17 21515.54 21657.64 21788.7 20928.65744 20036.36 19110.54 18149.89 17153.02 16118.51 15044.89 13930.6 12774.04 13853.55 12607.39 11313.76 9970.776 8576.5 7128.899 5625.864 4065.204 2444.638 761.7957 PAT 32946.67 35393.47 37978.97 38249.86 38502.47 38735.47 37206.50212 35620.2 33974.3 32266.47 30494.26 28655.14 26746.47 24765.51 22709.4 24628.53 22413.13 20113.34 17725.82 15247.11 12673.6 10001.54 7227.03 4346.024 1354.304 2 WACC Market Cap 800 beta 1.25 Debt 200 risk free rate 5% risk premium 8 CAPM (cost of equity) rf+ beta*risk premium 5%+(1.25*.08) 15% Cost of debt Debt 200 Interest paid 5 Interest rate 2.50% Capital Structure weight weight Equity 800 0.8 Debt 200 0.2 Total 1000 1 WACC Weight of equity * cost of equity + Weight of debt* (1-taxrate) 12.32% 3 NPV ($42,201.08) Period Cash inflow Cash outflow Net 0 0 -295000 -295000 1 32946.66667 32946.67 2 35393.46667 35393.47 3 37978.97467 37978.97 4 38249.85535 38249.86 5 38502.46994 38502.47 6 38735.46868 38735.47 7 37206.50212 37206.5 8 35620.19837 35620.2 9 33974.30215 33974.3 10 32266.46837 32266.47 11 30494.25851 30494.26 12 28655.13689 28655.14 13 26746.46681 26746.47 14 24765.50651 24765.51 15 22709.40492 22709.4 16 24628.53071 24628.53 17 22413.13435 22413.13 18 20113.34343 20113.34 19 17725.82436 17725.82 20 15247.11066 15247.11 21 12673.5976 12673.6 22 10001.53671 10001.54 23 7227.029992 7227.03 24 4346.023982 4346.024 25 1354.303519 1354.304