Reference no: EM133007837

Question 1

A new machine will cost $250,000. The expected market value of the machine at the end of each year, and the annual maintenance costs are given in the table below. Assuming 11% cost of capital, what is the economic life of the machine? Clearly answer the question. (Hint: construct a table similar to Example 9-4 in the textbook.)

Question 2 The city of Angola, Indiana is considering building a snowmobile trail on the land owned by the city. The life of the project is assumed to be 20 years. Building the trail would require an investment of $140,000 from the public funds. The annual maintenance would require an additional $25,000 per year. The annual benefits to the public of using the trail are estimated to be equivalent to $40,000 per year. After 20 years, the city will sell the right to operate the trail to the Angola Snowmobile Club, Inc. for $10,000.

Compute a MODIFIED B-C ratio, using the present worth method, for the project, determine whether it should be accepted, and explain why. Make sure you use the formula appropriate for the modified B-C ratio. Assume 4% cost of capital.

Question 3
Angola United Theaters, Inc. is considering opening a new movie theater in Angola, Indiana. The relevant cost of capital is 8%. The purchase, renovation, and modification of a downtown building would require an initial investment of $1.5 million. The theater is expected to be operational for 25 years. After 25 years, the property would be sold for $1 million. The annual operating cost is equal to $100,000 plus $2 per customer. The annual revenues are estimated at $15 per customer. Determine the minimum number of customers per year that would result in accepting the project. (Hint: use annualized worth and solve for the number of customers. Do not use Goal Seek or Solver; perform all of the necessary calculations directly in the Excel cells.)

Question 4
Angola Superheros, Inc. is considering launching a production of a new superhero toy. The production and sales are expected to last 4 years. The project would require a new machine, with a cost of $1,000,000. The machine would be depreciated using the straight-line method over 8 years to zero value after year 8. After 4 years, at the end of the project, the machine is expected to be sold for $30,000. The company estimates that 40,000 toys would be sold annually, at a selling price of $20 per unit. The variable cost of producing each unit is estimated to be $5. In addition, the company will have to pay fixed costs equal to $35,000 each year. The relevant cost of capital is 14%, and the company faces a 25% marginal tax rate.

a. Compute the project's cash flows in years 0-4, calculate the net present worth of the project and its internal rate of return, and determine whether the project should be accepted. Explain your answer.

b. Run a Monte Carlo simulation, varying the variable cost per unit, the annual fixed cost, and the number of units sold. Assume that the variable cost per unit has a mean of $5 and standard deviation of $0.20, that the annual fixed cost has a mean of $35,000 and standard deviation of $2,000, and that the number of units sold annually has a mean of 40,000 and standard deviation of 1,500, and that all three variables follow normal distribution. Compute the present worth of the project for 100 simulation runs. Based on the simulation results, compute the average net present worth and the threshold for the 5% of worst possible outcomes. Comment on the results, the uncertainty of the project's net present worth, and whether the project should be accepted.

c. Use the data table function to construct a table showing the net present worth of the project, while varying the selling price per unit and the cost of capital. Use the following selling prices in your table: 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, and 25. Use the following cost of capital in your table: 9%, 10%, 11%, 12%, 13%, 14%, 15%, 16%, 17%, 18%, and 19%. Comment on the sensitivity of the project's present worth to the cost of capital and the selling price per unit.

Question 5

Estimate the weighted average cost of capital for Caterpillar, Inc. (ticker CAT), using the income statement and balance sheet data for CAT from morningstar website, and using the historical stock price data and the market capitalization data for CAT and VFINX from finance.yahoo. (Do not use balance sheet and income statement from finance.yahoo, it has errors in it. Make sure you use year 2019 data from the balance sheet and income statement on Morningstar - do not use the TTM column.) Note: From Morningstar, go to financials, access the balance sheet and income statement for year 2019, use "interest expense net of capitalized interest" for interest expense (it will be showing as a negative number - it simply means the company paid this amount in interest expense), and use "long- term debt" for debt (do not include capital lease, ignore short-term debt - this is to make it simpler for you). You will also need pre-tax income and provision for income tax to calculate the tax rate. To get the market value of equity use market cap from the summary page on Yahoo Finance.

1. Create a sheet in your Excel file (Q5a) that estimates the weighted average cost of capital, listing the necessary inputs at the top of your sheet. Obtain any necessary data from the sources listed above. Link to other sheets in your file that will calculate CAT's cost of equity and beta.

2. Create a second sheet (Q5b), where you use the capital asset pricing model to estimate CAT's cost of equity, assuming 2.5% risk-free rate and 5.5% market risk premium. Link to a separate sheet with beta estimation.

3. Create a third sheet (Q5c), where you estimate CAT's beta, using historical prices with WEEKLY frequency for the following dates: starting date 12/01/2017, and ending date 12/01/2020 (note: input the dates 12/1 using the calendar icon, when you click "done" the dates showing may be 11/30, that's okay). Use finance.yahoo.com to download the prices for CAT and VFINX (make sure you use the ADJUSTED CLOSE price). Use the SLOPE function to estimate beta. Insert a scatter chart that shows the trendline from regressing returns of CAT on the VFINX returns, and displays the estimated equation.

Attachment:- Excel Problem Set.rar