1) Use an Excel spreadsheet to evaluate the ABCD Companyproposal.
2) Conduct a sensitivity analysis that focuses on the cost ofcapital. For a best case scenario, decrease the cost of capital bythree percentage points. For a worst case scenario, increase thecost of capital by three percentage points.
3) You must provide one spreadsheet for each of the threesituations—the base case estimate, the best case, and the worstcase.
4) What do you recommend? Explain. You may type yourrecommendation and explanation on the Excel sheet.
Please provide ALL formula so that I can gain a betterunderstanding.
The ABCD Co. wants to add a production line. To do this, thecompany must spend $200,000 to expand its current building andpurchase $1 million in new equipment. The new production line isexpected to produce 100,000 units per year of a new product, whichhas a projected sales price of $7.75 per unit and a variable costof $3.90 per unit. Introducing the new product is expected to causesales of existing products to decrease by $89,000 per year andexisting costs to decline by $49,000 per year. Fixed costs of thenew line will be $142,000 annually. The company expects net workingcapital to increase by $1,800,000 when the new line is added, andthen decrease by that amount when the project ends in five years.ABCD also expects to sell the equipment and building space at theend of the project in five years to net $320,990.36 after taxes.The company has a 34 percent marginal tax rate. ABCD’s cost ofcapital is 11%. Depreciation would be as follows, Year 1:$206,349.20 Year 2: $326,349.20 Year 3: $198,349.20 Year 4:$121,549.20 Year 5: $121,549.20
