Assignment Task 

Logistics

Provide your responses/answers in the written report.

CFTP is a large class. To increase marking efficiency (and reduce turnaround time), the written report serves as the primary interface between the markers and your work. That is, you do the computations in Excel and present your responses in the written report. Thus to help the marker help you those responses should be as unambiguous as possible.

Responses in the written report must be backed by computations in the Excel spreadsheet. Otherwise, zero credit is awarded. No strict specifications on the cover page of the written report.

CAPM ẞ Estimation

To complete this segment, download data from the Yahoo Finance website. Data should have the following specifications:

1. Perform these tasks in Microsoft Excel.

(a) Download data on the following tickers: GM (firm), SPY (mar- ket portfolio proxy), TNX (risk-free rate). Merge the data on date (hint: Use VLOOKUP function in Excel).

(b) What is the estimated CAPM 3. Report the regression output in the Excel spreadsheet. Provide a screenshot of the regression output in the report.

(c) Interpret the CAPM 8.

Capital structure

To complete this segment, refer to the following financial information of GM, as of 31th December 2022.

• There are 1.454 billion shares outstanding.
• Short-term and long-term debt amounts are $38,778 millions and $75,921 mil- lions, respectively.
• Last closing share price is $33.64.
• Depreciation & amortization expenses are $11,276 millions.
• Earnings before interest and taxes are $10,314 millions.
• Marginal tax rate is 21% and the effective tax rate is 14.75%.
• Government bond yield is 5%.

2. Using the CAPITAL STRUCTURE Excel spreadsheet template, answer the following questions. Include all necessary workings in the spreadsheet otherwise, zero credit will be awarded.

(a) What is the WACC under the optimal capital structure sug- gested by the Excel spreadsheet analysis?

(b) In your baseline scenario, the expected market risk premium (MRP) is 7% and the government bond yield is 5%. However, you believe the MRP can be as 12% or as low as 5%. You also believe that the gov- ernment bond yield can range from 2% to 7%. Analyze how sensitive the WACC is to your assumptions of the MRP (in increments of 0.5%) and the government bond yield (in increments of 1%). Provide a screenshot of your sensitivity analysis in the report.

(c) Find the highest and lowest WACCs contained in your sensitivity analysis. To derive a WACC that better accounts for extreme scenarios, you decide to assign the following probability weights: High- est (20%); Baseline (70%); Lowest (10%). Find the probability-weighted average WACC. Clearly show your workings.

Capital budgeting

GM is considering a project to introduce a high-end sports car to its product line. As a business analyst, you are tasked to perform a capital budgeting analysis. Below are the information associated with this project.

• GM has completed a $1 million marketing survey to assess the attractiveness of the sports car.
• The project has an estimated life of 4 years.
• Expected selling price is $26,000/car in Year 1.
• Upfront R&D costs are $1.5 million Upfront new equipment costs $7.5 million; 5-year straight-line depreciation.
• Annual overhead expenses are $2.8 million.
• Expected manufacturing cost is $11,000/car in Year 1.
• The equipment is to be housed in an existing empty factory. The factory could have been rented out for $200,000/year.
• GM expects to sell 100, 125, 50 and 50 units of the sports car in Years 1 through 4, respectively. 20% of the sports car units sold come from customers who would otherwise have bought an existing old car model made by GM.
• The existing old car model sells for $10,000 in Year 1 and costs $6,000 to make.
• However, if GM does not introduce the sports car, those customers would have bought sports cars from an automaker competitor.
• Prices and production costs of all cars (new and old) will fall by 10% per year.
• Overhead expenses and factory rentals will rise by 4% per year.
• No working capital is needed in this project.
• This project is as risky as the average project of GM.

3. Find the NPV of this project using Excel. Correctly account for

(a) Incremental sales

(b) COGS

(c) Depreciation expenses

(d) Overhead expenses, and

(e) Cash flow adjustments.

(f) Finally, compute the NPV of this project.