Finance 4010 Chapter 12 Homework 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 straight- line 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. Here is how I set up my spreadsheet (but you do you): B D E Year 0 1 2 N 1 2 Selling Price per unit 3 Annual Quantity Sold 4 Variable Cost per unit 5 WACC 6 Tax Rate 7 Software Lease 8 9 10 Market Value Building 11 Book Value Building 12 WW Equip MV 13 A NOWC 14 15 Sales Revnue 16 Variable Cost 17 Fixed cost 18 Software Lease 19 Building Depreciation (incremental) 20 Equipment Depreciation 21 EBIT 22 Taxes 23 NI 24 OCF 25 NPV 26 IRR 27 PBE 28 QBE 29 Not everything is necessarily germane in mine; your spreadsheet might be better. I just used Goal Seek for the break-even price and quantity that yields a zero NPV; if you don't like using =GoalSeek (or if you just like to check your work) you could: 1. Find the NPV of the costs of the project 2. Turn that into an Equivalent Annual Cost 3. Work your way back through the proforma income statement using your EAC as an OCF as shown in the lecture That's a lot of work, but a refreshing exercise in algebra. Finance 4010 Chapter 12 Homework 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 straight- line 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. Here is how I set up my spreadsheet (but you do you): B D E Year 0 1 2 N 1 2 Selling Price per unit 3 Annual Quantity Sold 4 Variable Cost per unit 5 WACC 6 Tax Rate 7 Software Lease 8 9 10 Market Value Building 11 Book Value Building 12 WW Equip MV 13 A NOWC 14 15 Sales Revnue 16 Variable Cost 17 Fixed cost 18 Software Lease 19 Building Depreciation (incremental) 20 Equipment Depreciation 21 EBIT 22 Taxes 23 NI 24 OCF 25 NPV 26 IRR 27 PBE 28 QBE 29 Not everything is necessarily germane in mine; your spreadsheet might be better. I just used Goal Seek for the break-even price and quantity that yields a zero NPV; if you don't like using =GoalSeek (or if you just like to check your work) you could: 1. Find the NPV of the costs of the project 2. Turn that into an Equivalent Annual Cost 3. Work your way back through the proforma income statement using your EAC as an OCF as shown in the lecture That's a lot of work, but a refreshing exercise in algebra