In: Finance
Budweiser is thinking about making wine…
• Expected sales per unit o 260,000 bottles sold in the first year o Sales revenue declines 10% per year (ie, sales growth = -10%)
• Proposed selling price per bottle = $5.20
• Variable cost per bottle = $2
• Fixed costs = $250K/year in maintenance and labor
• Budweiser also expects to lose $300K pretax per year (revenue net of expenses) from beer sales as people switch from beer to wine
• Working capital necessary = $50K
• Wine-making equipment o would cost $750K, plus $50K in modifications required on the assembly line (included in depreciable basis)
o depreciated 5-year MACRS
• After five years, project ends as everyone realizes Budweiser wine is disgusting o all remaining working capital recouped o wine-making equipment sold for $400,000
• Tax rate = 30%
• Assume any negative taxes can be treated as an immediate tax credit. (=treat negative taxes as a positive CF.)
• Required return = 10%.
On Excel, calculate the NPV and the IRR of the project. Should the company pursue this project?
Year | 0 | 1 | 2 | 3 | 4 | 5 | |||||
initial Investment in machine =750000+50000 | -800000 | year | cost of equipment | MACRS rate | Annual depreciation | ||||||
Investment in working capital | -50000 | 1 | 800000 | 20% | 160000 | ||||||
sales in units =sales in Year 1*(1-r)^n r =-10% n =1,2,3,4 | 260000 | 234000 | 210600 | 189540 | 170586 | 2 | 800000 | 32% | 256000 | ||
sales revenue =units sold*sales in units | 1352000 | 1216800 | 1095120 | 985608 | 887047.2 | 3 | 800000 | 19.20% | 153600 | ||
variable cost | 520000 | 468000 | 421200 | 379080 | 341172 | 4 | 800000 | 11.52% | 92160 | ||
fixed cost | 250000 | 250000 | 250000 | 250000 | 250000 | 5 | 800000 | 11.52% | 92160 | ||
Annual depreciation | 160000 | 256000 | 153600 | 92160 | 92160 | Accumulated depreciation over a period of 5 years | 753920 | ||||
operating profit | 422000 | 242800 | 270320 | 264368 | 203715.2 | ||||||
pre tax loss of net revenue | 300000 | 300000 | 300000 | 300000 | 300000 | Book value of equipment | 800000-753920 | 46080 | |||
before tax net revenue | 122000 | -57200 | -29680 | -35632 | -96284.8 | selling price | 400000 | ||||
tax-30% | 36600 | -17160 | -8904 | -10689.6 | -28885.4 | Gain on sale of equipment | 353920 | ||||
after tax profit | 85400 | -40040 | -20776 | -24942.4 | -67399.4 | Tax on gain on sale of equipment | 353920*30% | 106176 | |||
add depreciation | 160000 | 256000 | 153600 | 92160 | 92160 | after tax sale proceeds | 400000-106176 | 293824 | |||
after tax scrap value of machine | 293824 | ||||||||||
recovery of working capital | 50000 | ||||||||||
net operating cash flow | -850000 | 245400 | 215960 | 132824 | 67217.6 | 368584.6 | |||||
present value factor at 10% =1/(1+r)^n r=10% n=1,2,3,4,5 | 1 | 0.909091 | 0.82644628 | 0.751315 | 0.683013 | 0.620921 | |||||
present value of cash flow | -850000 | 223090.9 | 178479.339 | 99792.64 | 45910.53 | 228862.1 | |||||
Net present value = sum of present value of cash flow | -73864.53 | ||||||||||
IRR =Using IRR function in MS excel | IRR(R1195:W1195) | 6.59% | |||||||||
No project should not be pursued as NPV is negative and IRR is less than required rate of return of 10% |