Can you show me step by step instructions on how to solve these four problems and put them in an excel spreadsheet so that I can click on the cells and see the calculations:
1. It is January 1, 2005. Bethesda Manufacturing Company (BMC) is considering the purchase of a new fabrication machine for $2,500,000 with a 5 year MACRS useful life. Freight for the new machine will be 2.5% of the purchase price and set up for the new machine will cost $25,000. The project will end in 4 years and BMC will sell the machine for $190,000. Also, the NOWC will be restored at the end of year 4. This new project has a 65% chance of increasing first year revenue by $400,000 and a 35% chance of increasing first year revenue by $200,000. The new project will decrease first year annual expenses before taxes and depreciation by $350,000. Annual revenue is expected to increase 6% per year over the project's life and annual expenses are projected to increase 7% per year over the project's life. The project requires NOWC of $60,000. The firms marginal tax rate is 40% and its WACC is 16%. What is the projects NPV and should BMC invest in this machine?
5 year MACRS percentages are: 20% 32% 19% 12% 11% 6%
2. It is 2004 (T=0) and SJB Investment's is considering buying vacant lots in Bethesda that sell for $17.9 million. If the property is purchased, the company plans to spend another $25 million today to build a hotel on the property. The after-tax cash flows from the hotel depends critically on whether the metro rail system is built with a stop in Bethesda. There is a 70% chance that the metro rail will stop in Bethesda. If the metro rail system stops in Bethesda, the hotel is expected to produce after-tax cash inflows of $12.7 million at the end of each year for the next 10 years. If the metro rail does not stop in Bethesda, the hotel is expected to produce after-tax cash inflows of $2,200,000 per year at the end of each of the next 10 years. The project has a 23% WACC.
(a) If SJB Investments has an option to abandon this project at the end of year 1 and sell the property for $15,000,000, what is the projects expected NPV and what is the dollar value of this option?
(b) Presume that the company has an option to wait 1 year from now at which time SJB Investment's will know if the metro rail will stop in Bethesda. If SJB Investment's waits 1 year, what is the NPV of this project in 2004?
3. The following data applies to SJB Corporation:
Total assets: $10,000,000.00
Common equity: 75.00%
Before-tax cost of debt: 12.00%
Risk-free rate: 5.00%
Market return: 16.00%
Beta at current capital structure: 1.20
Tax rate: 40%
Shares outstanding: 200,000
Dividend payout rate: 100%
a) What is SJB's current Weighted Average Cost of Capital?
b) What is SJB's unlevered beta?
c) SJB is considering changing its capital structure to 35% debt and 65% common equity. To make this change, SJB will issue additional debt and use the proceeds to repurchase common stock. If SJB does this, the total annual interest expense will rise by $130,000. SJB would repurchase its common stock for $12 per share. What would be the cost of common equity if SJB adopts this new capital structure?
4. You have just been retained to help the Montgomery Hamburger Shop improve its current assets management. Sales in 2004 were $2,500,000 (all credit) and it earned a net profit of 12% of sales. It turned over its inventory 8 times during the year, and its DSO was 45 days. The company has fixed assets of $800,000. Montgomery Hamburger Shop's payables deferral period is 40 days. Presume a 360 day year.
(a) Calculate the Montgomery Hamburger Shop's cash conversion cycle.
(b) Presuming that Montgomery Hamburger Shop has negligible amounts of cash and securities, calculate its total assets turnover and ROA.
(c) What would the cash conversion cycle, total assets turnover, and ROA be if the Montgomery Hamburger Shop could turn over its inventory 12 times per year?
This solution is comprised of a detailed, step by step response which is enclosed within an attached Excel document. By clicking directly on to the cells of the spreadsheet, a student can see what functions were used to derive the computed values.