Problem : You have been assigned to determine whether more people prefer Coke or Pepsi. Assume that roughly half the population prefers Coke and half prefers Pepsi.
How large a sample do you need to take to ensure that you can estimate, with 95% confidence, the proportion of people preferring Coke within 2% of the actual value?Show work.
Capital Budgeting Assignment
This assignment must be done in MS-Excel using the formulas and functions in Excel. This means you will NOT receive full credit if you work the problem on paper and then type the numbers into the spreadsheet cells or work portions of the assignment on your calculator.
Make sure you have a separate worksheet within your ONE Excel file for each problem and that you label the worksheet tabs. I will NOT accept
work done in two Excel files. Your recommendations need to be included in a text box, not typed into a cell.
1. Your company is considering a new product. The marketing department has estimated that the new line will sell four million units in year one at a price of $15 per unit. Unit sales are expected to increase by 3% per year until the product is retired in 8 years. The variable cost per unit is $10 in year one. The price is expected to increase by 2% per year. If you introduce the new product you will sell 1.2 million fewer units per year of an existing product for the first 2 years.
The price of the existing product is $8 per unit and the variable cost per unit of the existing product is $4. The price and cost are not expected to change over the next 2 years. The existing line will be discontinued in2 years regardless of whether we introduce the new product. Fixed costs are expected to be $10 million in year one and increase by 1% per year due to inflation.
You can manufacture the product in a factory that is currently sitting idle. The factory originally cost $10 million and is fully depreciated. You could sell the factory for $15 million today, before taxes. If you use the factory now, you estimate that you could sell it in 8 years for $17 million before taxes.
The required equipment will cost $15 million and will be depreciated using 5-year MACRS. The expected salvage at the end of year 8 is $1.5 million. The new product will require net working capital equal to 10% of the next year's sales.
This is similar to the NWC requirements for the existing products. The company's cost of debt is 5%, the required return on projects of this risk is 10% and the marginal tax rate is 35%. Use Excel to create pro forma income statements, compute the relevant cash flows and find the payback period, NPV, and IRR. Based on this analysis should the company produce the new product? Why or why not?
2. Compute the cash flows, NPV, and the IRR for the following replacement project. Should you replace the current equipment?
Tax rate = 35%; Required Return 4%
|
Current Machine
|
Proposed Machine
|
|
Original Cost
|
16 million
|
Cost
|
15 million
|
|
Depreciation
|
7-year MACRS
|
Depreciation
|
7-year MACRS
|
|
Purchased
|
5 years ago
|
Expected salvage in 6 years
|
5 million
|
|
Salvage today
|
6 million
|
Increase in revenue
|
500,000 per year
|
|
Expected salvage in 6 years
|
2 million
|
Increase in operating cost
|
400,000 per year
|
|
|
|
Initial increase in NWC
|
200,000
|