Capital Budget
FIN 641- Fall 2012 – Excel Assignment
This assignment is due 10 am on Thursday, December 6. Your assignment will be graded on a scale of 0 to 16. Each question is equally worth. You need to use the posted Excel Template. You should work on these individually. The assignment should be submitted on Blackboard. No late assignment will be accepted. Enjoy the problems!
Capital Budgeting
The C & S Company manufactures ice-cream bars. They are considering the purchase of a new machine that will top the bar with high quality chocolate. The cost of the machine is $900,000; it has a life of 10 years and the company will have to increase its net working capital by $20,000 to use it. The machine can produce up to 1,000,000 ice-cream bars annually. The marketing director believes that if the company will spend $20,000 in advertising every year, the company will be able to sell 400,000 bars for $1.30 each. The cost of producing each bar is $0.40; and other costs related to the new product are $4,000 annually. The company’s cost of capital is 14% and the corporate tax rate is 30%. The company will depreciate 10% (or $90,000) of the machine’s value every year and the book value at the end of year 10 would be zero. The company expects the equipment to be actually sold for $200,000 after 10 years.
1. Calculate the initial outlay, annual after-tax cash flows and terminal cash flows.
2. Complete the table with the timeline and calculate the NPV and IRR of the project. Is the project acceptable?
3. Data Table that shows changes in NPV, when required rate changes (11%, 12%, 13%, 14%, 15%)
4. Create a scenario summary table (using the scenario manager tool) to show what happens to the NPV and IRR for the following three scenarios for sales and tax rate.
Three Scenarios for quantity sold and unit price : Quantity Sold Unit Price
Worst 200,000 1.4
Expected 400,000 1.3
Best 600,000 1.1
This assignment is due 10 am on Thursday, December 6. Your assignment will be graded on a scale of 0 to 16. Each question is equally worth. You need to use the posted Excel Template. You should work on these individually. The assignment should be submitted on Blackboard. No late assignment will be accepted. Enjoy the problems!
Capital Budgeting
The C & S Company manufactures ice-cream bars. They are considering the purchase of a new machine that will top the bar with high quality chocolate. The cost of the machine is $900,000; it has a life of 10 years and the company will have to increase its net working capital by $20,000 to use it. The machine can produce up to 1,000,000 ice-cream bars annually. The marketing director believes that if the company will spend $20,000 in advertising every year, the company will be able to sell 400,000 bars for $1.30 each. The cost of producing each bar is $0.40; and other costs related to the new product are $4,000 annually. The company’s cost of capital is 14% and the corporate tax rate is 30%. The company will depreciate 10% (or $90,000) of the machine’s value every year and the book value at the end of year 10 would be zero. The company expects the equipment to be actually sold for $200,000 after 10 years.
1. Calculate the initial outlay, annual after-tax cash flows and terminal cash flows.
2. Complete the table with the timeline and calculate the NPV and IRR of the project. Is the project acceptable?
3. Data Table that shows changes in NPV, when required rate changes (11%, 12%, 13%, 14%, 15%)
4. Create a scenario summary table (using the scenario manager tool) to show what happens to the NPV and IRR for the following three scenarios for sales and tax rate.
Three Scenarios for quantity sold and unit price : Quantity Sold Unit Price
Worst 200,000 1.4
Expected 400,000 1.3
Best 600,000 1.1