All work must be done on excel no reportCourse is 'Case studies in Finance'
this is worth 15%
CASE STUDIES IN FINANCE -USE EXCEL Ferrous Supplies, Inc., a manufacturer of finished steel products from recycled metals and recycled ferrous and non-ferrous metal and auto parts, is evaluating two mutually exclusive investment projects. In the first project, the firm will supply 9,000 wheels annually to Ford Motor Company at an average price of $700 per wheel over a period of 7 years. The second project involves the supply of 12,500 auto exhaust systems annually to General Motors Company at an average price of $400 per unit over a period of seven years. The equipment required to produce the wheels will cost $1,400,000 plus $100,000 in shipping and installation costs. This equipment has an expected life of 10 years and will be depreciated using the MACRS 7-year class life. After the 7-year contract with Ford Motor Company, this equipment can be sold for 800,000, but the firm will need to pay $30,000 in removal expenses. The production of the wheels will require an additional investment of $500,000 in raw materials and the variable costs per wheel are estimated to be 70% of the selling price. The forecasted fixed costs associated with the wheels include 10 workers to operate the equipment earning an average of $45,000 each per year in salaries and benefits and $70,000 annually in maintenance costs and miscellaneous fixed expenses. The equipment required to produce auto exhaust systems will cost $1,200,000 plus $120,000 of shipping and installation expenses. The expected life of this equipment is 10 years and will be depreciated using the MACRS 7-year class life. The equipment has an estimated salvage value of $155,000 after the 7-year contract with General Motors Company, but the firm will need to pay $50,000 in removal expenses. The production of the auto exhaust systems will require an additional investment of $200,000 in raw materials and the variable costs per unit are estimated to be 60% of the selling price. The forecasted fixed costs associated with the systems include 12 workers to operate the equipment earning an average of $38,000 each per year in salaries and benefits and $40,000 annually in maintenance expenses and miscellaneous fixed expenses. The firm plans to finance the selected investment project with a bank loan at a fixed rate of 5% for 7 years. The loan will be repaid in periodic installments (annually). The workers’ salaries and benefits, and maintenance expenses are expected to grow at an average rate of inflation of 3%. The firm’s WACC (Cost of Capital) is 12% and its marginal tax rate is 30%. a) Calculate the initial investment, annual after-tax cash flows, and the terminal cash flow of each investment project. b) Calculate the payback period, NPV and IRR of each investment project. Should the firm accept or reject one or both projects? c) The firm’s management has decided to reconsider some estimated variables that equipment costs, salvage value, and variable cost are accurate within ∓25%. Create scenario analysis showing the riskiness of this project to the possible changes in equipment cost, salvage value and variable cost. Include a graph in your analysis. Under which scenario the project should be selected? d) Perform the same sensitivity analysis (use increments of 2% from -10% to 10%) for the following uncertain variables: number of units, variable cost as a percentage of sales, investment in net working capital, salvage value, and inflation rate. Create a sensitivity diagram that includes all of these variables. e) Perform the Break-Even Analysis (accounting and financial).