Question
Create an Excel spreadsheet exclusively of YOUR OWN CREATION, WITHOUT PLAGIARIZING FROM ANYONE ELSE that addresses this capital budgeting problem. Your firm is considering a
Create an Excel spreadsheet exclusively of YOUR OWN CREATION, WITHOUT PLAGIARIZING FROM ANYONE ELSE that addresses this capital budgeting problem. Your firm is considering a two-year project to build desks for Wal-Mart. You have just signed a three-year lease for proprietary software that Wal-Mart requires its vendors to have. The first of three annual payments of $25,000 is due tomorrow. This can be expensed on your taxes. Your firm has no other plans for other projects involving Wal-Mart. Your firm is in the 34 percent tax bracket and has a ten percent weighted average cost of capital. This project is of equivalent risk to the other projects in your firm. Your variable cost per desk is $150; the contractual selling price is $300 per desk; the contract calls for the delivery of 500 desks per year for two years. You will be paid at the end of year one and the end of year two. Fixed costs are $25,000 per year, also paid at year-end. You will have to buy woodworking equipment with a cost of $50,000. IRS rules place this equipment in a five-year property class, therefore you will depreciate this equipment straightline to zero over five years. At the end of year two this equipment is expected to have a (pre-tax) market value of $30,000. You are considering housing the factory in a building that your firm already owns. Today the building has a (pre-tax) market value of $100,000, a book value of $20,000, and going-forward depreciation charges of $10,000 per year. In two years time the building is expected to have a market value of $120,000. The project will require a $30,000 investment in net operating working capital today, which is recoverable at the end of the project.
1. Calculate the Operating Cash Flow at time zero.
2. Calculate the Operating Cash Flow at the end of the first year.
3. Calculate the Operating Cash Flow at the end of the second year.
4. Calculate the NPV of this project.
5. Calculate the IRR of this project.
6. Calculate the MIRR of this project assuming that your reinvestment rate is only 9%.
7. Calculate the break-even quantity of desks sold per year with a selling price of $300 each that yields a zero NPV.
8. Calculate the break-even price (i.e. the price that gives a zero NPV) when 500 are desks sold per year.
Please be sure that your Excel spreadsheet is properly formatted: any dollar amounts should be formatted as currency, any interest rates should be formatted as percentages. Be sure that your decision variables only appear in one place and are clearly labeled. Do not have any numbers inside of formulas. When you turn in your Excel spreadsheet, make sure that I can see your formulas when I open it up. I am giving zero points for Excel spreadsheets that have only numbers
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started