A B Cc D E Cott Corporation Fruit Drink Project Information Provided Land Purchase Price 50000 Land Book Value 50000 Land's Price Today 100000 Engineer Fee 15000 Factory Cost 2100000 Project Market Value in Yr. 4 500000 Year 1 2 3 4 MACRS Coefficient 33.3396 44,4596 14.81% 7.AL% Selling Price 1.64 Unit Sales 1st Year 4000000 Sales Growth 9% Apple Juice Sales Decrease 500000 Variable Costs 60% of Sales Fixed Costs 1250000 Bond Interest Payment 60000 Inventory Increase Year 0 275000 Receivables Increase Year 0 135000 Payables Increase Year 0 110000 Investment in NWC (years 1 - end) 10% of the Forecasted Sales Increase Tax Rate 35% Intermediary Calculations Pro-Forma Statement Year 1 2 3 4 Sales Variable Cost Fixed Costs Depreciation EBIT Interest Taxable Income Taxes NetIlncome Suppose that Cott Corporation (COT) is considering adding a new product. Cott currently sells apple juice, and they are considering adding a new fruit drink. The new fruit drink will be discontinued in 4 years. They own a piece of land that was bought 10 years ago for $50,000, it is in the company's books for $50,000 and could be sold today for $100,000. They hired an engineer to evaluate it, and she has approved it for building a new factory. The engineer billed the company for $15,000. Building a new factory will cost $2,100,000. At the end of the project, the factory (including the land) can be sold for $500,000. The factory will be depreciated via 3-year MACRS depreciation schedule; however, the land is not depreciable. The fruit drink will have a selling price of $1.64 per container. Projected sales are expected to be 4,000,000 containers in the first year and they will increase at 9% for each subsequent year. As existing customers switch to the new fruit drink, sales of the existing apple juice will decline by $500,000 per year. Variable costs are 60% of the sales and fixed costs are $1,250,000 per year. To finance this project the company will issue a bond and it will cost the company $60,000/year in interest. The new fruit drink requires an increase in inventory of $275,000, an increase in accounts recervable of $135,000, and an increase in accounts payable of $110,000 im year 0. In the following years (that is, years 1, 2, 3) the investment in net working capital will be 10% of the forecasted sales increase. This means that the investment in NWC in year 0 will be enough to support the sales of year 1. The investment in NWC in year 1 will be 10% of the forecasted sales increase (the sales increase is calculated as forecasted Sales, - Sales). For the forecasted sales numbers, use whatever sales numbers you have in Row 31. The company's tax rate is 0.35. Hints: Keep in mind that sales are increasing every year by 9%, but the $500,000 decrease in the sales of the existing apple juice remains constant every year. When calculating sales numbers in subsequent years DO NOT do PreviousSales*1.09 as this calculation would assume that the $500,000 also increases by 9% per year. Use a formula that allows the sales of the new product to grow by 9% per year, but the 500000 to remain constant. When calculating the variable cost use the same sales number that you are using in Row 31, which should account for the sales erosion (DO NOT calculate the variable cost solely on the sales of the new product). The rationale for this is that if the sales of the apple juice are declining, so are the variable costs associated with those sales; hence, this product, while decreasing sales of other products, is also saving on the variable costs associated with those products. The initial investment should be Factory Cost + MV of the Land Today = 2100000+100000 = 2200000. So in Cell B49 you should have -2,200,000. The point I was trying to make here was that when calculating initial investment we need to account for the opportunity cost (the Land) and that we only care for the Market Value of the Land (the 100,000) and that we don't care for its historical cost or its book value. However, keep in mind that when calculating depreciation, you are only depreciating the factory (the 2100000)