Final Excel project
Scenario:
Tom Jones, the CFO for the firm PSUWC Energy, LLC, woke up with a start at 4:00
am on 4/21/20, 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 4/20/20 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 their annual 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. 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 34000 kW [1] per hour
power. Typically, PSUWC ran its plants 24 hours a day, 7 days a week at an
average of 65 % Capacity factor [2] , 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 18 % a year
till they reached a 100% capacity factor. (This meant that the capacity factor
for year 2, CF2, would be = CF1*(1+growth_rate), till 100% was reached and then
would stay at 100%). A total investment of $32,000,000.00 USD for new equipment
was required. The equipment had fixed maintenance contracts of $3,800,000.00
per year with a salvage value of $9,000,000.00 and variable costs were 53% 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.00 and $1,000,000.00 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.138 per kilo-watt hour
in the market they served.
The corporate tax rate
was 39% and PSUWC currently has 1,000,000 shares of stock outstanding at a
current price of $16.00. The company also has 30,000 bonds outstanding, with a
current price of $943.00. The bonds pay interest semi-annually at a coupon rate
of 4.60%. The bonds have a par value of
$1,000 and will mature in 21 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 return [4].
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 30% /70% split between issuing bonds/equity or a 70 % /30 % split between
issuing bonds/equity . The bonds would have to be retired at the end of the
project's life [6]. 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 Rd with DtoE, realizing that the cost of debt
increased with an increasing D/E ratio [7] . Additionally, the state government
had promised to raise the debt for PSUWC via the issuance of bonds, with the
caveat that upon termination of the project PSUWC would have to pay a Nuclear
Waste Disposal Fee, equivalent to the amount of money raised via the issuance
of debt.
Tom needed to calculate
the rate at which he would have to discount the project to calculate the Net
present Value of the proposed project based on his decision of raising capital
and the current capital market environment. This discount rate, the WACC, would
obviously influence the NPV and could affect the decision of whether or not to
accept the project. Thankfully, he had all the information needed to calculate
this and hence the NPV. Tom needed to clearly show all his calculations and
sources for all parameter estimates used in the calculation of the WACC.
Gathering all the available information, Tom got a large cup of extra strong
coffee and sat down to work on the development of his Capital Budgeting project
model. His correct recommendation to the board was critical to the future
growth of the firm!