SHOW on EXCEL SPREADSHEET

A carmaker company is thinking about introducing a new sport car. The firm already produces different types of cars including luxury cars. It is a 10-year project. It would require a $200 million investment.

a/ The firm estimates that they will be able to sell each year 20,000 cars at $40,000 each.

However, 25% of the customers will come from existing customers of the firm and who buy luxury cars.

We assume that the price of the luxury car is $35,000.

What will be the incremental revenues of the project?

b/ Lets suppose that the project will bring additional sales of $90 million each year from posters, gadgets, and other secondary items. What is the new incremental revenue?

c/ The cost of goods sold will be $600 million the first year and increase by 1 % every year.

In addition, the firm considers to launching an advertising campaign. It will cost $10 million the first year, $6 million the second year, and $3 million the following two years, then no thing after.

What are the incremental EBITDAs?

Show on a spreadsheet all the relevant items like in an income statement until EBITDA for year 0 and each of the following 10 years.

d/ Lets suppose that you depreciate linearly the $200 million in plant and equipment linearly over the 10 years.

Also, there is no debt, and the tax is 35%.

What are the incremental net incomes?

Again, show on a spreadsheet all the relevant items like in an income statement until net income for year 0 and each of the following 10 years. (Of course, take the spreadsheet from c/ and continue until net income)

e/ Lets suppose that the firm will have the following results the next 10 years for the project itself:

Years

We have: 0 1 2 3 4 5 6 7 8 9 10

Account Receivable 20 22 23 26 24 23 23 23 23 23

Inventory 4 3 3 3 3 2 2 2 2 2

Account Payable 15 20 21 22 23 24 25 22 22 22

What will be the incremental FCF?

Again, show on a spreadsheet all the relevant items like in a statement until FCFF for year 0 and each of the following 10 years. (Of course, take part of the spreadsheet from d/ and continue until FCFF)

f/ Calculate the NPV of the project using the cost of capital of Ford tell me how you found the cost of capital of Ford).

Show on the spreadsheet.

g/ Calculate the IRR of the project.

Show on the spreadsheet.