Can you please help me with the answer to following 2 ASSIGNMENTS questions attached in excel format.

Thanks

EXERCISE: Valuing a gas pipeline You own a gas pipeline that requires no maintenance and will produce $2 million of revenue next year. Unfortunately, after the first year the volume of gas (and thus the revenue) is expected to decline by 4.0% per year. a. If the discount rate is 11.0% and the pipeline lasts forever, what is it worth today? b. If the pipeline is to be abandoned at the end of 20 years, what is it worth today? c. Assume now that the potential buyer of the pipeline has other investment opportunities that could give him a 16% return per year. Would he consider 10 million$ to be a fair price to buy the pipeline and use it for 20 years? EXERCISE: Expanding capacity in molybdenum mines AMAX Corporation is a mining company that focuses on extraction of molybdenum - a crucial additive in the production of steel. AMAX is considering expanding its molybdenum capacity and is deciding whether to pursue one of the following investment alternatives. A) An investment to expand capacity at its Climax mine would cost $100M today (year 0), $50M next year (year 1). It would increase capacity in years 3 to 9 by 15M pounds (note there is no cash flow in year 2). The variable cost of extracting molybdenum at this location would be $4/pound. Hint: nominal annual profits from the increased capacity would therefore be given by: 15M * (P - $4), where P is the price of molybdenum. Year Investment (in $M) 0 - 100 1 - 2 3 4 5 6 7 8 9 50 B) An investment to expand capacity at its Henderson mine would cost $75M today (year 0), $30M next year (year 1). It would increase capacity by 13M pounds per year from years 3 to 9. The variable cost of extracting molybdenum at this location would be $4.5/pound. 1/2 Year Investment (in $M) 0 - 75 1 - 2 3 4 5 6 7 8 9 7 8 9 30 C) Reopening of its Kitsault mine would require an investment of $25M today (year 0), $10M in year 1 and $10M in year 2. It would increase capacity in years 3 to 9 by 10M pounds. The variable cost of extraction would be $6/pound. Year Investment (in $M) 0 - 25 1 - 10 2 - 3 4 5 6 10 If the discount rate is 16%, find the price of molybdenum above which it makes sense to do each of the investments a), b) and c). Be careful: if you use Excel, it often creates problems with there is an empty cell in a cash flow stream; therefore, you are invited to leave no empty cell but plug in zeros. 2/2 Chocolat Cordon Rouge Corporate Finance You are part of Marcel Arnaud's team to analyze the 7 different projects under consideration. The table in the case describes the expected cash flows of the different projects. All the costs and benefits of the projects have been taken into consideration except for two potential costs: 1. John Hsu, the manager sponsoring the project to build a factory in the U.S., did not include the loss in output of the French plant in Years 4-10. He claims that those cash flows are not part of his proposed project and as such should not be included. 2. Bertrand Godard, who is proposing to expand capacity in Brittany, has not included the cost of the land because he claims that the firm already owns it. However, you should note that the cash flows of the project include the sale of the land in year 10. Before conducting your analysis, think about whether these decisions are correct and, if not, adjust the cash flows accordingly. Clarifications: The case gives you the WACC of each project. This is just the discount rate. As the case indicates, CCR has only 75M in its bank account to pay for the projects investment costs. Assignment Questions 1. Compute the NPV and IRR of each project. If there were no budget constraint, which projects would you recommend? 2. Which projects would you recommend with the 75M budget? Assume first that if CCR sells the land, it will NOT use the proceeds to increase its capital budget. ID#140301 PUBLISHED ON MARCH 18, 2014 Chocolat Cordon Rouge: A Capital Budgeting Review BY PAUL INGRAM * AND DANIEL WOLFENZON ABSTRACT CONTENTS The top management team at Paris-based Chocolat Cordon Rouge (CCR) is considering how to allocate a predetermined capital expenditure (capex) budget of Background .........................................1 75 million. This case outlines the seven projects under consideration and the cash flow estimates Project Evaluation ...............................1 Members of the Top Management Team .....................................................2 The Prospective Projects ...................3 Cash Flow Estimates ..........................7 associated with each. * Kravis Professor of Business, Columbia Business School Stefan H. Robock Professor of Finance and Economics, Columbia Business School Acknowledgements Columbia CaseWorks Fellow David Donner Chait, MBA'13, provided research and writing support for this case. Copyright information 2013-2014 by The Trustees of Columbia University in the City of New York. This version of the case replaces an earlier version that was published on July 12, 2013. All companies, persons, and events are composites based on common business scenarios. This case is for teaching purposes only and does not represent an endorsement or judgment of the material included. This case cannot be used or reproduced without explicit permission from Columbia CaseWorks. To obtain permission, please visit www.gsb.columbia.edu/caseworks, or e-mail ColumbiaCaseWorks@gsb.columbia.edu Background Chocolat Cordon Rouge (CCR) is a Paris-based, family-owned confectioner celebrating its 100th year of selling fine chocolate to customers as near as its Champs-Elyses flagship store and as far away as the United States, the Middle East, and China. CCR management will soon meet to decide on its annual capital expenditure budget. Though the company considers capex projects every year, this year in particular the projects have strategic implications for the future of the company. Toward that end, proposals for various prospective projects have been prepared. CCR is sure of only two things: all of the projects cannot be funded and a myriad of opinions will be voiced during the upcoming Project Review Meeting. The Benoit family, which owns the majority of CCR, is nearly allergic to red ink: some decades ago, relatively liberal management took on enough debt at just the wrong time that the company nearly went under. What's more, the family is equally strict about the prospect of diluting its ownership: it will not issue new shares in order to raise capital. Therefore, CCR will limit itself to using cash it has on hand. Right now, the family is experiencing both the good and the bad sides of a wild increase in consumer interest in fine chocolate. On the one hand, sales worldwide are up, and CCR has a special place in the market as a grande dame of French chocolate. On the other hand, luxury and boutique chocolatiers are popping up at a staggering rate: there has never been such fierce competition, and the trend is not encouraging. CCR believes that at this critical moment of opportunity and risk, it needs to make investments that will ensure the long-term health of the company. Although the Benoit family is prudent, it is willing to be bold as long as the likelihood of reward outweighs the prospect of risk. It believes that all of the projects outlined below are worthy of discussionbut that the task now is to make hard choices. As such, the Board has allocated 75 million for projects approved this year. Project Evaluation Each year CCR Chief Financial Officer (CFO) Marcel Arnaud and his team review all potential capital expenditures. For this review, each project is assigned its own unique estimated weighted average cost of capital (WACC) according to the risk associated with the project and estimated cash flows. These projects are then evaluated based on four different criteria: Net Present Value (NPV) Internal Rate of Return (IRR) Payback Period Profitability Index (PI) Page 1 | A Capital Budgeting Review BY PAUL INGRAM* AND DANIEL WOLFENZON Members of the Top Management Team Each year the capital budgeting decisions are made by six senior managers from CCR. To be considered, a project must be sponsored by one of the senior managers present at an annual meeting. The deciding senior managers include: Sylvie Benoit, Chief Executive Officer (CEO). Great-granddaughter of CCR Founder Henri Benoit, Benoit is both CEO of CCR and spokesperson for the Benoit family. She feels a strong sense of responsibility to protect her family's interests and reputation and is subsequently often in favor of lower-risk and non-controversial projects. In this same vein, Benoit believes in a clear process and supports the hard 75 million capital budget cap. Marcel Arnaud, Chief Financial Officer (CFO). A Columbia Business School graduate, Arnaud and his team annually prepare all cash flows and analysis for the capital budgeting meeting. Arnaud has not sponsored a project this year. Charles LeFevre, Chief Operating Officer (COO). As COO of CCR, LeFevre has been known to champion projects that augment CCR's overall company image without necessarily impacting the bottom line. In this vein, LeFevre has recently become an ardent supporter of \"green\" initiatives across the company, believing this will create value in the long-run. A 40-year veteran of CCR, at 63, LeFevre is only a few years from retirement. John Hsu, Chief Marketing Officer (CMO). Hsu, relatively new to CCR, joined during the previous year as a part of Benoit's push to hire more international executives. An American, Hsu is the first non-French senior manager. Prior to his position at CCR, Hsu was the North American head of marketing and sales at the Hershey Company; he left this position with the expectation that CCR was committed to the American market and was willing to expand this commitment. Bertrand Godard, Vice President of Production. As Vice President of Production, Godard oversees all European production for CCR including the main plant in Brittany. He is extremely proud of CCR's French history and is confident in the ability of French workers to deliver world-class chocolate. As such, he is a champion of European expansion. Jacqueline Durand, Vice President of Strategic Planning. As Vice President of Strategic Planning, Durand firmly believes that the future of CCR rests in global expansion. Hired from Unilever four years earlier, Durand previously oversaw the expansion of several Unilever brands across markets. She has an MBA from INSEAD and finished her coursework for a PhD while there, though never completed her dissertation. Since joining CCR, she has continuously pushed for increased international expansion. A Capital Budgeting Review | Page 2 BY PAUL INGRAM* AND DANIEL WOLFENZON The Prospective Projects At the meeting, CCR's senior managers will evaluate the below proposals: Project 1. 2. 3. 4. 5. 6. 7. Build a factory in the United States Enter the Latin American market by building a factory in Uruguay Expand capacity at current manufacturing sites Expand capacity for \"Chocaf\" beverage line \"Green\" CCR by reducing the company's carbon footprint \"Green\" Chinese production plant Purchase new machinery from a bankrupt competitor Cost (millions) 55.0 Sponsoring Manager John Hsu 37.5 Jacqueline Durand 14.5 6.5 10.0 8.2 29.5 Bertrand Godard Sylvie Benoit Charles LeFevre Charles LeFevre Charles LeFevre 1. BUILD A FACTORY IN THE UNITED STATES: COST, 55M For years, CCR has wrestled with the question of whether it makes sense not to make chocolate in the US. After all, this is the company's second-largest market outside of France, and has been for decades. If only it were that simple. The Benoit family has resisted the urge to make this move for good reasons. First, if CCR brings capacity to the US, the only way that will not affect the manufacturing plant in Brittany is if demand in the US rises to meet the new capacity Would CCR be willing to reduce capacity back home? Would CCR be able to find or develop a workforce with the necessary skills? Would cultural barriers between management and American workers prove difficult? And would US consumers be as enthusiastic about CCR's products if they were made in Wisconsin instead of imported from France? Those are the risks. John Hsu believes that the opportunities are also compelling. A new plant would almost certainly offer efficiencies, including green architecture and production methods, that would make production more efficient than what CCR has in France, with the added bonus of favorable press coverage on what CCR imagines as a \"green chocolate\" ad campaign upon the opening of the US plant. And a new plant in the US might offer an opportunity to create new lines of chocolate tailored to the American market. Hsu has proposed a 55 million factory to be built in the US. Arnaud considers this project to be a high-risk proposition. As a result, this project has a relatively high estimated WACC of 10%. Furthermore, according to a report by the French Ministry for the Economy, Industry, and Employment, the US plant would decrease output and subsequent cash flows of the French plant by 11.5% each year, once the plant is fully operational in Year 4. (There would be no cannibalization in Years 0-3.) For example, if they build in the US, the expected Year 4 cash flow for the French plant would be 77.99 instead of 88.12 (see Table 1), the expected Year 5 cash flow would be 81.89, not 92.53, and so on. Note that this reduction only affects the existing cash flows and not those from the Brittany capacity expansion (Project 3 below). Page 3 | A Capital Budgeting Review BY PAUL INGRAM* AND DANIEL WOLFENZON 2. ENTER THE LATIN AMERICAN MARKET BY BUILDING A FACTORY IN URUGUAY: COST, 37.5M One year ago, Jacqueline Durand issued an internal report so tantalizing it became known within management simply as the \"golden report.\" It described in detail market research the company had conducted regarding the prospect of selling CCR chocolate in Latin America. It found that two thingsan age-old, region-wide appreciation for chocolate and a taste among the wealthy for French luxuryamounted to a superb opportunity for CCR to expand into a new and growing market. There are many factors that support an expansion of operations into Latin America. . CCR would not have to worry about cannibalizing its French production, since the plant in Uruguay would only be meant to satisfy demand that is not presently being met: CCR currently sells only a tiny amount of chocolate to Latin Americans, through mail order. Also, CCR would have easy access to high-quality, low-cost cocoa. Finally, labor costs would be much lower than in France, and labor regulations would be less stringent than at home. However others in top management at CCR are well aware of three broad classes of risk. First, this is an opportunity on paper alone: the only way to find out whether this new market is as promising as CCR suspects will be to make a costly entry into the market. Second, Latin America presents what amounts to a symphony of risk and doubt with regard to governmental relations: as a purveyor of a foreign luxury good, CCR would be doubly exposed to populist political leaders and the financial consequences that such risk would entail. Third, even if CCR found a way to operate with stability and profitability, it would need to ensure that local labor practices were consistent with company values. Arnaud and his team calculate that this project would have a high NPVbut also that cash flows would arrive relatively late, due to the need to build up a new market. Given the high risk associated with entry into a new market, CCR has assigned this project a high WACC of 12.5%. Durand remains convinced that a Latin American expansion is worth the associated risks and has proposed entering the Latin American market with the 37.5 million construction of a plant in Uruguay. 3. EXPAND CAPACITY AT CURRENT MANUFACTURING SITES: COST, 14.5M For its manufacturing hub, CCR owns prime real-estate in Brittany, which is an important agricultural center for France and very convenient for businesses with heavy shipping needs, given the region's proximity to the sea. Right now, in light of great worldwide demand for organic cheese, meat, and produce, prices for that real estate are at an all-time high. CCR has an unused parcel of land abutting its plant that it could either sell or use for a plant expansion. The company estimates that if it put that land on the market today, it could expect the property to command a price of 22.5 million. The cash flows assume that the land used for the project is sold in year 10therefore the cash flows include those from production as well as from the asset sale. A Capital Budgeting Review | Page 4 BY PAUL INGRAM* AND DANIEL WOLFENZON CCR senior management is agreed that the current 5% annual increase in demand for CCR chocolate in Europe and the United States necessitates moderately increased capacity. It also believes that in China, it will have at least a 10% growth in demand for at least the next 10 years. It also sees the possibility of an undefined amount of growth should it begin selling chocolate in Latin America (see #2, above). Whatever the amount CCR decides it must increase capacity, the question is whether that capacity should be created in Brittany or elsewhereor both in Brittany and elsewhere. CCR is very happy with the efficiency of the Brittany operation and has determined that any on-site expansion of capacity would only serve to increase that efficiency. However, CCR also sees that shipping costs per unit of weight are increasing at rate of 4% annually, well above inflation, which is currently at 1%. Bertrand Godard proposes a 14.5 million expansion of capacity on the unused land at the Brittany plant. The CFO and his team consider this to be a low-risk project. As such, it has been assigned a WACC of 7%. If CCR doesn't expand in Brittany, they will sell the land. 4. EXPAND CAPACITY FOR \"CHOCAF\" BEVERAGE LINE: COST, 6.5M Three years ago, Pierre Benoit, great-great grandson of CCR Founder Henri Benoit, had an idea for a cold, bottled beverage that combined milk, coffee, and chocolate. As he had just turned 18 at the time, his idea was given cautious attention by management. With his Aunt Sylvie's encouragement, he was allowed to lead a small, pilot effort to see whether the idea had merit. To the Benoit family's delight, Chocaf is doing surprisingly well. What began as an operation run out of one of the Brittany plant's barns and selling to cafes in Brittany is now sending 3,000 singleserving bottles to Paris each day. The current facility devoted to Chocaf is running at capacity, which amounts to 5,000 bottles per day. Each bottle sold earns CCR a profit of 25 cents. Market research signals that the European market would today buy 20,000 bottles per day. To meet that capacity, Sylvie Benoit has proposed that a new plant would need to be built at a cost of 6.5 million. Arnaud and his team consider the risk around this project to be moderate with an estimated WACC of 10%. Given the weight and perishability of Chocaf, the product's profitability would be more tightly tied to shipping costs than CCR's chocolate business. Also, CCR management has a slight hesitation about the beverage business: CCR feels that it is not an inside player, and therefore is at greater risk of being outmaneuvered by the competition than it is in its chocolate business. 5. \"GREEN\" CCR BY REDUCING THE COMPANY'S CARBON FOOTPRINT: COST, 10M Charles LeFevre is in favor of reducing the company's carbon footprint and \"going green.\" CCR's marketing department believes that should the company invest in significant improvementssay, about 10 million worththe company would be in a position to claim that it is the greenest chocolatier in Europe. It is believed that this goodwill would contribute to the growth in the company's annual sales. Also, greening the company would significantly reduce the risk of being sued for environmental damage. Twenty years ago, CCR quietly settled a suit with residents of a Page 5 | A Capital Budgeting Review BY PAUL INGRAM* AND DANIEL WOLFENZON village in Brittany who suffered from exposure to CCR industrial chemicals that seeped into the water supply. While it made the necessary fixes to prevent future seepage, going green would completely remove toxic chemicals from the equation. In addition, CCR believes that an effort toward greening must include some benefit from the government. LeFevre, a champion of the \"green\" movement, estimates that if CCR were to invest 10 million in greening right now, and assuming no growth in output, the company would stand to gain significantly in tax reductions from the French government. The costs would come mainly from purchasing new, more fuel-efficient production equipment, as well as a fleet of more fuel-efficient trucks. Further costs would come from better purification of liquids and gas released as a by-product of production. However, no one at CCR can agree to a set of metrics that would be use d to measure the company's progress in going green. While there are national standards bodies in France that can measure a company's carbon footprint, the goalposts seem to change annually. Also, the legislative landscape in France and the European Union is unsettled enough to make any forecasting about possible regulations pure guesswork. CCR considers the greening project to be of moderate to high risk, mainly because of an unpredictable regulatory environment. Consequently, Arnaud and his team have assigned this project an estimated WACC of 12.5%. 6. \"GREEN\" CHINESE PRODUCTION PLANT: COST, 8.2M CCR owns a growing production facility outside of Beijing, and for good reason: Chinese demand for CCR chocolate is projected to increase at least 10% annually for the next 10 years. While CCR has been recognized for its unusually aggressive efforts to secure good or excellent working conditions for its Chinese employees, it is merely average in its approach to Chinese environmental concerns. The equipment it uses amounts to one-generation-old technology: it is 25% less productive than the newer equipment being used in Brittany and twice as environmentally damaging. LeFevre proposes a 8.2 million project for the greening of these production plants. Nonetheless, there is no pressure from the Chinese government for CCR to change: as long as its environmental impact remains what it is, the company has virtually zero chance of being sued by the Chinese government. However, with relatively modest resources, CCR could simultaneously improve efficiency and gain positive headlines. Arnaud and his team view this project as entailing relatively high risk as well, with an estimated WACC of 12.5%. 7. PURCHASE NEW MACHINERY FROM A BANKRUPT COMPETITOR: COST, 22.25M Five years ago, a leading French fashion conglomerate thought it saw a big opportunity in luxury foods, and in particular, chocolate. With heavy marketing spending, the company did get attention A Capital Budgeting Review | Page 6 BY PAUL INGRAM* AND DANIEL WOLFENZON but discerning French customers quickly concluded that fancy packaging and ubiquitous advertising did not make for great chocolate: in the end, the new offering was an average product at a premium price. That company's equipment is now up for sale, and at a good price. There are, however, some drawbacks. First, it will take CCR two years to start production using this equipment. Second, the equipment is so new and efficient that its implementation in CCR's production facility would require layoffs. Any layoffs at CCR would come at considerable cost: six months' salary, on average, for each worker. The French Ministry for the Economy, Industry, and Employment estimates that the cost of such layoffs would reach 7.25 million. CCR would need to pay this cost in the same year as the capital investment. Note that the total expenditure on this project is therefore 29.5 M. CCR is aware that its competitors are also interested in purchasing the equipment. If it wants to act, it will need to do so swiftly. LeFevre has proposed quick action in acquiring this equipment. Arnaud and his team consider this project to be of medium risk. Though cost savings are sure to accrue from implementing the machinery, difficulties with labor will incur costs. Given the labor cost uncertainties, CCR evaluates this project to have modest risk with an estimated WACC of 10%. Cash Flow Estimates Following extensive review and budgeting, CCR Chief Financial Officer Marcel Arnaud provided the Benoit family and Board with the below 10-year projected project cash flows. As a point of comparison, estimated cash flows for the main French plant are provided as well. The projections do not consider any interdependencies between the projects, or current operations. Page 7 | A Capital Budgeting Review BY PAUL INGRAM* AND DANIEL WOLFENZON TABLE 1 FREE CASH FLOWS BY PROJECT CHOCOLAT CORDON-ROUGE (CCR) Free Cash Flows by Project Euros, Millions Project Total Investment 1 2 3 Build U.S. Enter LATAM Factory (Uruguay) Increase Capacity at current site (Brittany) 4 5 6 7 "Green" / Capacity Carbon Expansion for Footprint "Chocafe" Reduction Greening Chinese Production Plant Purchase New Machinery from Bankrupt Competitor (1) French Plant Regular CF 55.00 37.50 14.50 6.50 10.00 8.20 29.50 EXPECTED FREE CASH FLOWS Year 0 1 2 3 4 5 6 7 8 9 10 -55.00 3.00 5.80 12.00 14.50 17.50 21.30 26.30 29.10 29.60 29.60 -37.50 0.40 0.90 1.75 3.80 7.40 9.20 16.10 20.40 22.60 22.60 -14.50 1.74 4.94 4.94 4.94 4.94 4.94 4.94 4.94 4.94 17.14 -6.50 1.05 1.33 1.33 1.33 1.33 1.33 1.33 1.33 1.33 1.33 -10.00 2.05 2.05 2.05 2.05 2.05 2.05 2.05 2.05 2.05 2.05 -8.20 0.75 0.75 1.35 1.35 1.35 1.55 2.25 2.25 2.25 0.08 -29.50 0.00 5.85 5.85 5.85 5.85 5.85 5.85 5.85 5.85 5.85 72.50 76.13 79.93 83.93 88.12 92.53 97.16 102.01 107.12 112.47 118.09 Undiscounted Sum (years 1-10) 133.70 67.65 43.90 6.48 10.50 5.73 23.15 1,029.99 Project WACC 10.0% 12.5% 7.0% 10.0% 12.5% 12.5% 10.0% A Capital Budgeting Review | Page 8 BY PAUL INGRAM* AND DANIEL WOLFENZON CHOCOLAT CORDON-ROUGE (CCR) Free Cash Flows by Project Euros, Millions Project Total Investment 1 2 3 Build U.S. Enter LATAM Factory (Uruguay) 55.00 37.50 Increase Capacity at current site (Brittany) 14.50 4 5 "Green" / Capacity Carbon Expansion for Footprint "Chocafe" Reduction 6.50 10.00 EXPECTED FREE CASH FLOWS Year 0 1 2 3 4 5 6 7 8 9 10 -55.00 3.00 5.80 12.00 14.50 17.50 21.30 26.30 29.10 29.60 29.60 -37.50 0.40 0.90 1.75 3.80 7.40 9.20 16.10 20.40 22.60 22.60 -14.50 1.74 4.94 4.94 4.94 4.94 4.94 4.94 4.94 4.94 17.14 -6.50 1.05 1.33 1.33 1.33 1.33 1.33 1.33 1.33 1.33 1.33 -10.00 2.05 2.05 2.05 2.05 2.05 2.05 2.05 2.05 2.05 2.05 Undiscounted Sum (years 1-10) 133.70 67.65 43.90 6.48 10.50 Project WACC 10.0% 12.5% 7.0% 10.0% 12.5% 6 7 Greening Chinese Production Plant 8.20 Purchase New Machinery from Bankrupt Competitor 29.50 (1) French Plant Regular CF -8.20 0.75 0.75 1.35 1.35 1.35 1.55 2.25 2.25 2.25 0.08 -29.50 0.00 5.85 5.85 5.85 5.85 5.85 5.85 5.85 5.85 5.85 72.50 76.13 79.93 83.93 88.12 92.53 97.16 102.01 107.12 112.47 118.09 5.73 23.15 1,029.99 12.5% 10.0%