# Write a spreadsheet program to

Write a spreadsheet program to calculate the NPV of a project with an irregular pattern of cash flows for up to 10 periods without using the spreadsheet software’s NPV function. Essentially, the task is to program equation 10.1 with n = 10. First input the interest rate (k) in a single cell. Next set up three horizontal rows of 11 cells (including C0). The top row will receive the cash flows as inputs. Program the present value factor for each period into the second row of cells using the interest rate you input earlier as follows.

Note that we’re calling the interest rate k, but it will appear as a cell name in your program. Next form the third row by multiplying the top two cells in each column together.

This makes the third row the present value of each cash flow. Finally, sum the values along the third row in another cell to form the project’s NPV. Notice that your program will handle a project of less than 10 periods if you simply input zero (or leave blank) the cash flow cells from n+1 to 10. Also notice that you can easily extend your program to any reasonable number of periods by extending the horizontal rows and the programming logic. Test your program on the data in Example 10.3 on pages 428–430 to make sure it works correctly.

Example 10.3

The Xavier Motor Company makes outdoor power equipment including lawn mowers and garden tractors and is considering two diversification ventures. The first involves manufacturing a larger, more powerful tractor than the firm has made up until now. Market research indicates a substantial demand for more powerful equipment, and some competitors are already moving in that direction. The second opportunity involves building snowblowers.

The manufacturing and engineering technology required for making snowblowers is essentially the same as that for building garden equipment, but Xavier has never made snowblowers before. Management wants to make a decision based on only five years of projected cash flows, because it feels the future beyond that time is too vague to form a basis for current decisions. In other words, if a project isn’t expected to earn enough to justify itself in five years, management considers it too risky.

Working with representatives from the marketing, engineering, and manufacturing departments, a financial analyst has put together a set of projected incremental cash flows for each project. Xavier’s cost of capital is 9%.

A financial analysis of the project situation should provide answers to the following questions.

a. If these projects were being considered on a stand-alone basis, would either or both be acceptable?

b. If Xavier can raise no more than \$5 million for new projects, which of these projects should be chosen?

c. If Xavier’s management is willing to consider two more years of projected cash flow, and the contributions continued at the level of the last two years, which project would be chosen?

d. Are any risk considerations relevant beyond the numbers in this situation?

### Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount

Posted in Uncategorized

# Write a spreadsheet program to

Write a spreadsheet program to calculate the expected return and beta for a portfolio of 10 stocks given the expected returns and betas of the stocks in the portfolio and their dollar values. The calculation involves taking a weighted average of the individual stocks’ expected returns and betas where the weights are based on the dollar values invested in each stock. Set up your spreadsheet like this:

The computational procedure is as follows.

1. Input the names of the stocks, their dollar values, their betas, and their ke’s.

2. Sum the value column.

3. Calculate the weight column by dividing each row’s value cell by the cell carrying the sum of the values.

4. Calculate the beta and ke factors by multiplying the individual beta and ke cells by the cells in the weight column on the same row.

5. Sum the two factor columns for the results indicated.

Is your program general in that it will handle a portfolio of up to 10 stocks, or will it only work for exactly 10? If it is general, what do you have to be careful about with respect to inputs?

Extra: Assume you have \$1 million to invest in stocks. Look up several stocks’ betas in Value Line and estimate ke for each. Look up the current price of each stock in The Wall Street Journal, and form a hypothetical portfolio by allocating your money among the stocks. Find your portfolio’s expected return and beta using your program.

### Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount

Posted in Uncategorized