BACKGROUND

You are an information analyst working for NEE. The company president has asked you to prepare a Quantitative analysis of financial, sales, and operations data to help determine which power plant investment offers the better strategic opportunity for the company. The department managers have been asked to provide the following data from their functional areas:

• Financial and Accounting—The current cash position of the company, the cash outlay for the two investment choices, data for operating costs and period costs for each alternative, and the corporate income tax rates

• Sales—Forecasts for regional electricity demand and sales price, a formula for calculating sales demand based on weather, and forecasts of carbon credit sales for the hydroelectric alternative

• Engineering—Plant power capacities in megawatts, projected operating hours per year, and timelines for construction of either alternative

The departments have given you the following data:

• Carbon credits available for sale from the hydroelectric plant, 2016 through 2019

• Operating costs per megawatt-hour (MWh), 2015 through 2019

• Estimated market price per megawatt-hour, 2015 through 2019

• Hours of scheduled operation per year for both alternatives, 2015 through 2019

• Capital investment for hydroelectric and fuel cell plants, 2014

• Design generation capacity in megawatts for either alternative

• Projected corporate income tax rates, 2015 through 2019

• Forecasted additional market demand base for energy, 2014

• Period cost allocation base, 2014

• End-of-year cash on hand for 2014; this money is available for investment

Assignment 1 contains information you need to write formulas for the Calculations section, Income and Cash Flow Statements section, IRR Calculation section, and Canadian Bond Payment Calculation section. Because construction of the hydroelectric plant costs more than twice as much as the fuel cell plant, and

because NEE has enough cash to build only the fuel cell plant, the Canadian government has agreed to issue a tax-exempt bond for $1 billion that NEE can repay over 10 years at 4 percent interest. This financial assistance and the higher generating capacity of the plant will be factored into the analysis. You will use Excel to see how much profit and positive cash flow each alternative will generate for NEE from 2015 through 2019, and you will use Excel to calculate an internal rate of return for each alternative.You will also examine the effects of the weather (mild or severe) and the market price of methane (low or high) on projected electricity sales and profits for each alternative. In summary, your DSS will include the following inputs:

• Your decision to invest in the hydroelectric plant or methane fuel cell plant

• Whether the long-term weather outlook is mild or severe

• Whether the price of methane is low or high

Your DSS model must account for the effects of the preceding three inputs on costs, selling prices, sales demand, and other variables. If you design the model well, it will let you develop “what-if” scenarios with all the inputs, see the results, and show a preferred alternative for NEE to adopt.

ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT

In this assignment, you create a spreadsheet that models the business decision NEE is seeking. In Assignment 2A, you use Scenario Manager to summarize the financial outcomes for different combinations of inputs. To begin, you create the spreadsheet model of the company’s financial and marketing data. The model will cover sales from 2015 through 2019 for the type of generating plant selected. Assume that theme that thepreliminary research and development has been completed for the fuel cell alternative, and that the

Canadian government has completed the hydroelectric dam and requires NEE only to install the turbines and generators to put the plant into operation. Under these assumptions, the fuel cell plant can start operating in 2015 and the hydroelectric plant can begin operations in 2016.

This section helps you set up each of the following spreadsheet components before entering the cell

formulas:

• Constants

• Inputs

• Summary of Key Results

• Calculations

• Income and Cash Flow Statements

• Internal Rate of Return Calculation

• Bond Payment Calculation

The Internal Rate of Return Calculation section is needed because Excel financial formulas such as IRR work better if the cash outflow and inflow data are arranged in a vertical column with the years in ascending order, as opposed to taking the cash flows from across the page or from nonadjacent cells.

Constants Section

First, build the skeleton of your spreadsheet. You can also download the spreadsheet skeleton if you prefer.

• Carbon Credits available for sale, Hydroelectric Plant—This value is the number of metric tons of carbon saved each year by operating the hydroelectric plant. Each metric ton of carbon equals one carbon credit. Carbon credits can be sold on the international market.

• Operating Cost per Megawatt-Hour, Hydroelectric Plant—This value is the direct cost of generating one megawatt-hour of power needed to operate the hydroelectric plant. Most of this cost would be spent on station operators’ salaries, monitoring technology, parts, and maintenance of the water turbines and generators. This cost increases by 3 percent per year.

• Operating Cost per Megawatt-Hour, Methane Fuel Cell Plant (base)—This value is the direct cost of generating one megawatt-hour of power needed to operate the fuel cell plant. Unlike the hydroelectric plant, the fuel cell stacks have no moving parts, so the major component of the direct cost is the methane purchased to make the conversion to electricity through the fuel cells. The base cost increases by 3 percent per year, but it could vary considerably due to fluctuations in the market price of methane.

• Estimated Market Price per Megawatt-Hour—This value is the projected market price for one megawatt-hour of electricity for the next five years. In practice, the market price can vary considerably over the seasons according to supply and demand, but these seasonal prices havebeen averaged to keep the value simple. In the spreadsheet, the average market price increases by 5 percent per year.

• Estimated Sales Price for Carbon Credit (base)—This value is the projected sales price for a carbon credit over the next five years. The value increases by two dollars each year. The actual sales price for carbon credits will depend on whether enough nations require industrial polluters to purchase carbon credits.

• Hours of Scheduled Operation in a Year, Hydroelectric Plant—This value is the number of hours in 50 weeks of continuous plant operation. Hydroelectric plants typically require two weeks offline each year for preventive maintenance.

• Hours of Scheduled Operation in a Year, Fuel Cell Power Plant—This value is the number of hours in 51 weeks of continuous plant operation. Because the fuel cell stacks contain no moving parts or rotating machinery, the Engineering department estimates that the new technology would require only one week offline per year for preventive maintenance, mostly for the cooling and auxiliary equipment.

• Capital Investment for Hydroelectric Plant—This value is the total amount of investment needed in equipment, materials, and construction costs to build the hydroelectric plant.

• Capital Investment for Fuel Cell Plant—This value is the total amount of investment needed in equipment, materials, and construction costs to build the methane fuel cell plant.

• Generation Capacity of Hydroelectric Plant, Megawatts—This value is the designed output capacity for the hydroelectric plant, measured in megawatts.

• Generation Capacity of Fuel Cell Plant, Megawatts—This value is the designed output capacityfor the methane fuel cell plant, measured in megawatts.

• Corporate Income Tax Rate—These values are the projected corporate income tax rates for

NEE. These rates are lower than usual corporate income tax rates because NEE would receive research and development tax credits for any new technologies it can incorporate into either plant.

Inputs Section

• Weather Outlook—This value is either Mild (M) or Severe (S). Weather is a major determinant of electricity demand. Higher demand allows generating plants to operate closer to design capacity, which increases sales revenues.

• Market Price of Methane—This value is either Low (L) or High (H). The market price of natural gas would have a significant effect on the operating cost of the methane fuel cell plant. In

Assignment 2C later in this case, you explore how NEE can minimize the impact of fluctuations in methane’s market price by executing futures contracts for purchases.

•Investment Selection—This value is the basic input for the strategic decision to build either the proven hydroelectric plant (W) or the innovative methane fuel cell plant (F).

Summary of Key Results Section

This section (see Figure 6-3) contains the results data, which is of primary interest to the management team at NEE. This data includes income and end-of-year cash on hand information, as well as the annualized internal rate of return for a particular set of business inputs. This section summarizes the values from the Calculations, Income and Cash Flow Statements, and Internal Rate of Return Calculation sections.

For each year from 2015 to 2019, your spreadsheet should show net income after taxes and end-of-year

cash on hand. The net income after taxes is also the cash inflow for the IRR calculation.

N O T E

When writing formulas in the Calculations and Income and Cash Flow Statements sections, be careful if you copy a formula from one cell to another and the formula references a value from a particular cell in the Constants or Inputs section. For instance, if you copy the formula for Forecasted Additional Market Demand for Energy from cell C28 to cells D28 through G28, all destination cells must reference the value in cell B18 in order to complete the IF statement successfully. Therefore, you must use an absolute cell reference for cell B18 ($B$18) in the formula for cell C28 so the destination cells will retain cell B18 in the copied formulas. If you do not use an absolute cell reference, the Copy command will incorrectly use cells C18 through F18 in the copied formula values. To use absolute cell referencing, add $ signs before the column and row designations; these signs“anchor” the cell and ensure that destination cells refer to the correct source cell when information is copied. If necessary,consult the Excel online help for an explanation of relative and absolute cell references.

Calculations Section

The Calculations section includes the calculations you must perform to determine the market demand for energy, the projected annual electricity sales, the projected annual carbon credit sales for the hydroelectric alternative, the annual operating costs for both alternatives, and the period costs allocated to both alternatives.

• Forecasted Additional Market Demand for Energy--MW—This value is the amount of additional market demand for energy, measured in megawatts. Cell B28 contains the demand in 2014 (2,000 megawatts). The forecasted demand for 2015 through 2019 depends on the weather outlook selected in the Inputs section. If the weather outlook is Mild (M), the demand for each succeeding year will be 3 percent higher than the previous year’s (in other words, the previous year’s demand multiplied by 1.03). For example, the value in cell C28 is the value in cell B28 multiplied by 1.03. If the weather outlook is Severe (S), the demand for each succeeding year will be 10 percent higher than the previous year’s. Note that when you see the word if in the text, you need to write a formula that uses the IF function for the target cell.

• Available Additional Generation Capacity for Sale—This value is the amount of additional megawatts that the selected power station can provide for sale. The additional capacity depends on the investment selection from the Inputs section. In 2015 (cell C29), only the methane fuel cell alternative can provide additional capacity—the hydroelectric plant would be under construction and unavailable until 2016. For this cell, you must construct an IF formula that provides a zero value for the Hydroelectric option (W); for the methane fuel cell (F) selection, the formula must provide the fuel cell plant capacity from cell B14 of the Constants section. For the years 2016 through 2019 (cells D29 through G29), both plants would be producing electricity, so the appropriate generation capacity would be selected from cells B13 and B14, depending on the investment selection.

Annual Electricity Sales—The formula for these cells depends on the investment selection from the Inputs section and whether the demand value in Row 28 is smaller or greater than the supply value in Row 29. You cannot sell more electricity than you have the capacity to produce or than the market demands. In 2015 (cell C30), only the methane fuel cell plant can provide additional capacity, so the formula for that cell must take the lesser of the two values in cells C28 and C29. You can use the MIN function to determine this value. Next, multiply that value by the Estimated Market Price per Megawatt-Hour (cell C7) and by the hours of scheduled operation per year for the fuel cell power plant (cell B10). The formula for cell C30 does not require an IF formula because cell C29 will return a zero for the 2015 available capacity of the hydroelectric plant. However, years 2016 through 2019 (cells D30 through G30) require an IF formula to select the correct hours of scheduled operation in a year for inclusion in the electricity sales calculation. Multiply the Estimated Market Price per Megawatt-Hour in cells D7 through G7 by either the hydroelectric plant or fuel cell plant’s scheduled hours of operation in a year (cells B9 or B10).

• Annual Carbon Credit Sales (Hydroelectric Only)—Only the hydroelectric investment selection will allow NEE to sell carbon credits. Build an IF formula that returns a zero in cells D31 through G31 for the fuel cell alternative. For the hydroelectric alternative, the IF formula multiplies the carbon credits available for sale (cells D4 through G4) by the Estimated Sales Price for Carbon Credit (cells D8 through G8), and then enters the answer in cells D31 through G31.

• Annual Operating Cost for Hydroelectric Plant—This value is the operating cost per megawatt- hour for the hydroelectric plant (cells D5 through G5) multiplied by the hours of scheduled operation per year for the hydroelectric plant (cell B9), multiplied by the generation capacity of the hydroelectric plant in megawatts (cell B13). All three values are from the Constants section. Note that the hydroelectric plant only incurs operating costs in 2016 through 2019 (cells D32 through G32).

• Annual Operating Cost for Methane Fuel Cell Plant—The base calculation for this value is the operating cost per megawatt-hour for the methane fuel cell plant (cells C6 through G6) multiplied by the hours of scheduled operation per year for the methane fuel cell plant (cell B10), multiplied by the generation capacity of the fuel cell plant in megawatts (cell B14). All three values are from the Constants section. This formula must also include a factor for the market price of methane from the Inputs section. If the market price is Low (L), the base calculation is reduced by 10 percent; in other words, multiply the base calculation by .9. If the market price of methane is High (H), the base calculation is increased by 10 percent; in other words, multiply the base calculation by 1.1. The formulas for 2015 through 2019 are written for cells C33 through G33.

• Period Cost Allocation (Sales, General, & Administrative)—The value for 2014 (cell B34) is a projected cost allocation base to be assigned to the selected generating plant. Period costs are corporate costs that are not directly traceable to an operating plant, but that must be absorbed by the company’s operations. The formula for the 2015 period cost (cell C34) depends on the investment selection. If the hydroelectric plant is selected, no period cost can be allocated because the plant will not be in operation yet. Therefore, the value is zero. If the fuel cell plant is selected, the period cost allocation for 2015 is the 2014 period cost increased by 3 percent. In other words, multiply the 2014 base value by 1.03. The 2016 period cost (cell D34) needs a specific IF formula as well. If the hydroelectric plant is selected, the period cost allocation for 2016 is the 2014 period cost increased by 6 percent. In other words, multiply the 2014 base value by 1.06. If the fuel cell plant is selected, the period cost allocation for 2016 is the 2015 period cost increased by 3 percent. For each of the years 2017 through 2019 (cells E34, F34, and G34), the period cost calculation is the same for either investment alternative—increase the preceding year’s period cost by 3 percent.

Income and Cash Flow Statements Section

The statements for income and cash flow start with the cash on hand at the beginning of the year. Because NEE is funding the capital investments internally—that is, with its own cash on hand—you must deduct the invested funds from the cash on hand at the end of 2014. Figure 6-5 and the following list show how you should structure the Income and Cash Flow Statements section.

Beginning-of-year Cash on Hand—For 2015, this value is the end-of-year cash on hand from 2014 minus the capital investment, depending on the investment selection. If you choose the hydroelectric plant, the capital investment will be $1 billion—in other words, cell B11 in theConstants section minus the amount of the Canadian bond, which is cell F51 in the Bond Payment Calculation section. If you choose the methane fuel cell plant, the capital investment will be $900 million (cell B12 in the Constants section). For the years 2016 through 2019, the beginning-of-year cash on hand is the end-of-year cash on hand from the previous year.

• Electricity Sales—This value is the Annual Electricity Sales from the Calculations section (cells C30 through G30). Note that the hydroelectric plant investment will have no electricity sales in 2015 because the plant will not be completed yet.

• Carbon Credit Sales—This value is the Annual Carbon Credit Sales from the Calculations section (cells D31 through G31). Note that the investment in the methane fuel cell plant will be zero for carbon credit sales.

• Total Revenues—This value is the sum of the Electricity Sales and the Carbon Credit Sales.

• Less: Operating Costs—If the hydroelectric plant is selected, the operating costs are zero in 2015 and are copied from cells D32 through G32 for the years 2016 through 2019. If the methane fuel cell plant is selected, the operating costs for 2015 are copied from cell C33 in the Calculations section. For the years 2016 through 2019, the operating costs are copied from cells D33 through G33.

• Gross Profit—This value is the Total Revenues minus the Operating Costs.

• Less: Bond Payment—If the hydroelectric plant is selected, the bond payment is copied from the Annual Payment calculated in the Bond Payment Calculation section (cell F54). This value must be converted to a positive number, so you need to insert a minus sign in front of the cell in the IF statement argument. If the fuel cell plant is selected, the bond payment is zero.

• Less: Period Costs Allocated—This value is copied directly from the Period Cost Allocation row in the Calculations section (cells C34 through G34).

• Net Profit before Income Tax—This value is the Gross Profit minus the Bond Payment and the Period Costs Allocated.

• Less: Income Tax Expense—If you make a profit (in other words, if the Net Profit before Income Tax is greater than zero), this value is the Net Profit before Income Tax multiplied by the Corporate Income Tax Rate from the Constants section. If you make nothing or have a net loss, the Income Tax Expense is zero.

• Net Income after Taxes (Cash Inflow)—This value is the Net Profit before Income Tax minus the Income Tax Expense. From a strict accounting standpoint, the net income after taxes is not the cash inflow; you would have to add back all noncash expenses such as depreciation or depletion to determine the true cash inflow. However, for the purposes of this case, assume that net income after taxes is equal to cash inflow.

• End-of-year Cash on Hand—This value is the Beginning-of-year Cash on Hand plus the Net Income after Taxes.

Internal Rate of Return Calculation Section

Investment (Cash Outflow)—This value depends on the investment selection. If the hydroelectric plant is selected, the total cost is $2 billion. However, because the company is getting a $1 billion loan from the Canadian government, the company’s investment is actually the capital investment for the hydroelectric plant from the Constants section (cell B11) minus the bond amount from the Bond Payment Calculation section (cell F51). You might think it would be easier to enter $1 billion into the IF formula, but this approach would not work if the bond amount changed in cell F51. The fuel cell plant would be financed entirely from internal funds, so the company’s investment would be the capital investment for the fuel cell plant from the Constants section (cell B12). The selected investment amount must be converted to a negative number to represent it as a cash outflow. (Think of it as money out of your pocket.)

• Cash Inflow 2015—This value is the net income after tax for 2015.

• Cash Inflow 2016—This value is the net income after tax for 2016.

• Cash Inflow 2017—This value is the net income after tax for 2017.

• Cash Inflow 2018—This value is the net income after tax for 2018.

• Cash Inflow 2019—This value is the net income after tax for 2019.

• Internal Rate of Return (IRR)—This value is the annual rate of return that the project generates for the company. Many companies set a minimum required IRR for a project or investment before it can be selected for implementation. To calculate the IRR, click cell B57, which is where you want to record the IRR result. Next, click the fx symbol next to the cell-editing window. The Insert Function window appears (see Figure 6-7). Type IRR in the “Search for a function” text box, and then click Go.

When you click OK, the Function Arguments window appears to help you build the formula (see Figure 6-8). In the Values text box, enter the cells that contain all your cash outflows and inflows (B51:B56), or click and drag your mouse to select cells B51 through B56. Notice that Excel enters the formula for you in cell B57: =IRR(B51:B56). You do not have to enter a value in the Guess text box. When you click OK, Excel calculates the IRR and places the result in cell B57.

Bond Payment Calculation Section for the Hydroelectric Plant

The investment required for the Canadian hydroelectric plant is more than twice that of the methane fuel cell plant: $2 billion versus $900 million. NEE has only $1.2 billion of cash to invest in a new plant, so the Canadian government has agreed to issue tax-exempt bonds to raise $1 billion of the capital investment required to build the hydroelectric plant. If NEE chooses to build the hydroelectric plant, it must repay the Canadian government by making annual payments for 10 years at 4 percent compounded interest.

• Amount of Bond (Principal)—Enter $1,000,000,000 in cell F51.

• Term of Bond (yrs)—Enter 10 in cell F52.

• Interest Rate—Enter 4% in cell F53.

• Annual Payment—This value is the annual payment required for the term of the bond. To calculate the annual payment, click cell F54, which is where you want to record the payment result. Next, click the fx symbol next to the cell-editing window. The Insert Function window appears (see Figure 6-10). Type Payment or PMT in the “Search for a function” text box, and then click Go.

When you click OK, the Function Arguments window appears to help you build the formula (see Figure 6-11). In the Rate text box, enter the cell that contains the interest rate (F53) or click and drag your mouse to select cell F53. Similarly, enter cell F52 in the Nper (number of payments) text box, and enter cell F51 in the Pv (present value) text box. Excel enters the formula for you in cell F54 and displays it in the cell-editing window at the top of the page: =PMT(F53,F52,F51). You do not have to enter a value in the Fv or Type text box. Before you click OK, note that the bottom of the Function Arguments window displays a preview of the results based on your input. When you click OK, Excel calculates the payment and places the result in cell F54.

After you complete all the formulas, try testing your spreadsheet with various combinations of the three values from the Inputs section. There are eight possible combinations, as listed in the next section. If you receive any error messages or see strange values in the cells, go back and check your formulas. The DSS spreadsheet contains some values that represent millions or billions of dollars. Accountants often simplify their spreadsheets by listing outputs in multiples of millions or billions. It is not hard to do— you simply divide the cell values by a million or billion, depending on the scale—but for the purposes of this case, you should keep the large numbers in the spreadsheets. If you see cell results listed as a group of “#” signs when working with large numbers (see Figure 6-12), the column is not wide enough to display the contents of that cell. Simply widen the column until the number is displayed.

Assignment 2A: Using Scenario Manager to Summarize Data

For each of the eight situations listed earlier, you want to know the net income after taxes and the end- of-year cash on hand for 2019, as well as the internal rate of return generated by the different inputs. You will run “what-if” scenarios with the eight sets of input values using Excel Scenario Manager. If necessary, review Tutorial C for tips on using Scenario Manager. In this case, the input values are stored together in one vertical group of cells (B18 through B20) in the Inputs section, as are the three output cells (G23 through G25) in the Summary of Key Results section, so selecting the cells is easy. Run ScenarioManager to gather your data in a report called the Scenario Summary. Format this summary to make it presentable, and then print it for your instructor.

New England Energy Strategic Investment Decision
Constants	2014	2015	2016	2017	2018	2019
Carbon Credits available for sale Hydroelectric Plant	NA	NA	200,000	200,000	200,000	200,000
Operating Cost per Megawatt-Hour Hydroelectric Plant	NA	NA	$20.00	$20.60	$21.22	$21.85
Operating Cost per Megawatt-Hour Methane Fuel Cell Plant (base)	NA	$45.00	$46.35	$47.74	$49.17	$50.65
Estimated Market Price per Megawatt-Hour	NA	$70.00	$73.50	$77.18	$81.03	$85.09
Estimated Sales Price for Carbon Credit	NA	$80.00	$82.00	$84.00	$86.00	$88.00
Hours of Scheduled Operation in a Year, Hydroelectric Plant	8424	NA	NA	NA	NA	NA
Hours of Scheduled Operation in a Year, Fuel Cell Power Plant	8592	NA	NA	NA	NA	NA
Capital Investment for Hydroelectric Plant	$2,000,000,000	NA	NA	NA	NA	NA
Capital Investment for Fuel Cell Plant	$900,000,000	NA	NA	NA	NA	NA
Generation Capacity of Hydroelectric Plant, Megawatts	3000	NA	NA	NA	NA	NA
Generation Capacity of Fuel Cell Plant, Megawatts	2000	NA	NA	NA	NA	NA
Corporate Income Tax Rate	25%	25%	25%	25%	25%	25%

Answer rating: 100% (QA)

Scenario Summary In this table S represents Severe View the full answer