[Solution Available] FIN 301 Penn State World Campus Final Excel project

FIN 301 Penn State World Campus Final Excel project Scenario: Tom Jones, the CFO for the firm PSUWC Energy, LLC, woke up with a start at 4:00 am on July 2, 2012, due to his phone ringing. It was his senior financial analyst, vacationing in Europe, calling with bad news. Tom was supposed to present his project evaluation, at the end of the week, for the Board's proposal that they invest in new equipment that generates electricity, using a new nuclear technology. His staff of financial analysts had been working hard over the last few weeks collecting data and had prepared a model creating a financial forecast about the proposed project's viability. Disaster had struck on the night of July 1'st wherein malware all but wiped out the work of the analysts. Tom needed to prepare a financial analysis of the project to present the Board with his recommendations. All the staff had already left for the July 4th vacation and Tom was on his own. Tom quickly reached his office and managed to salvage what was left of the excel spreadsheet prepared for the presentation. The excel file is attached to this document. What follows is some basic information that Tom knew and was able to retrieve about the project. PSUWC's existing plant has excess capacity, in a fully depreciated building, to install and run the new equipment. Due to relatively rapid advances in the technology, the project was expected to be discontinued in six years. The proposed project was capable of providing 35,000 kW1 per hour power. Typically, PSUWC ran its plants 24 hours a day, 7 days a week at an average of 58% Capacity factor2, which is what the project would start with. However, his engineers had assured him that the implementation of the new technology would enable them to increase their capacity factor by 15% a year till they reached a 100% capacity factor. A total investment of $34 Million USD for new equipment was required. The equipment had fixed maintenance contracts of $4,000,000 per year with a salvage value of $7,000,000 and variable costs were 50% of revenues. The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in working capital of $6,000,000 and $1,000,000 of this increase would be offset with accounts payable. PSUWC would be able to sell all the electricity it generated at the rate of $0.15 per kilo-watt hour in the market they served. 1 kW stands for kilo-watt or 1000 watts. A watt is a measure of power. Electricity is priced in kWh (kilo-watt hours). 1 kilo-watt hour represents 1000 Watts of power expended for one hour (1 h) of time. The average price people in the U.S. pay for electricity is about 12 cents per kilowatt-hour. (Context: A typical U.S. household uses about 908 kWh a month of electricity.) 2 The net capacity factor of a power plant is the ratio of its actual output over a period of time, to its potential output if it were possible for it to operate at full nameplate capacity continuously over the same period of time. To calculate the capacity factor, take the total amount of energy the plant produced during a period of time and divide by the amount of energy the plant would have produced at full capacity. Capacity factors vary greatly depending on the type of fuel that is used and the design of the plant. The corporate tax rate was 35 percent and PSUWC currently has 1,000,000 shares of stock outstanding at a current price of $16. The company also has 30,000 bonds outstanding, with a current price of $960. The bonds pay interest semi-annually at the coupon rate is 5.6%. The bonds have a par value of $1,000 and will mature in twenty years. Even though the company has stock outstanding it is not publicly traded. Therefore, there is no publicly available financial information. However, management believes that given the industry they are in the most reasonable comparable publicly traded company is Companhia Paranaense de Energia - COPEL (NYSE Ticker Symbol ELP)3. In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the beta from ELP and the market risk premium based on the S&P 500 annual expected rate of return4. Tom knew that because of the size of the proposed project, he had to take into account the change in capital structure the new project would cause his firm. To this end, he had a choice between raising the new capital needed either using 75/25% split between issuing bonds/equity or a 25/75% split between issuing bonds/equity5. The bonds would have to be retired at the end of the project's life6. Tom knew that the cost of debt would depend on the new D/E ratio that the firm would have based on his decision to raise capital. Tom looked at the worksheet titled