Question

In: Finance

1) Use an Excel spreadsheet to evaluate the ABCD Company proposal. 2) Conduct a sensitivity analysis...

1) Use an Excel spreadsheet to evaluate the ABCD Company proposal.

2) Conduct a sensitivity analysis that focuses on the cost of capital. For a best case scenario, decrease the cost of capital by three percentage points. For a worst case scenario, increase the cost of capital by three percentage points.

3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case, and the worst case.

4) What do you recommend? Explain. You may type your recommendation and explanation on the Excel sheet.

Please provide ALL formula so that I can gain a better understanding.

The ABCD Co. wants to add a production line. To do this, the company must spend $200,000 to expand its current building and purchase $1 million in new equipment. The new production line is expected to produce 100,000 units per year of a new product, which has a projected sales price of $7.75 per unit and a variable cost of $3.90 per unit. Introducing the new product is expected to cause sales of existing products to decrease by $89,000 per year and existing costs to decline by $49,000 per year. Fixed costs of the new line will be $142,000 annually. The company expects net working capital to increase by $1,800,000 when the new line is added, and then decrease by that amount when the project ends in five years. ABCD also expects to sell the equipment and building space at the end 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 of capital 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

Solutions

Expert Solution

Part (1)

Please see the table below.

The project has positive NPV in the base case,

Part (2)

Base case NPV = 808,202.70

Best Case NPV =  599,913.77

Worst case NPV =  1,050,582.39

Part (3)

Base case excel screenshot is there in part (1)

Best Case screenshot is below:

Worst case screenshot is shown below:

Part (4)

Recommendation: NPV is positive in all the three cases. Since NPV is positive in all the three cases, this project should be undertaken by the company.


Related Solutions

1) Use an Excel spreadsheet to evaluate the Pear Computer Company proposal. 2) Conduct a sensitivity...
1) Use an Excel spreadsheet to evaluate the Pear Computer Company proposal. 2) Conduct a sensitivity analysis that focuses on the cost of capital. For a best case scenario, decrease the cost of capital by three percentage points. For a worst case scenario, increase the cost of capital by three percentage points. 3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case, and the worst case. 4) What do you recommend? Explain....
1) Use an Excel spreadsheet to evaluate the Pear Computer Company proposal (info below). 2) Conduct...
1) Use an Excel spreadsheet to evaluate the Pear Computer Company proposal (info below). 2) Conduct a sensitivity analysis that focuses on the cost of capital. For a best case scenario, decrease the cost of capital by three percentage points. For a worst case scenario, increase the cost of capital by three percentage points. 3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case, and the worst case. 4) What do you...
Evaluate the Pear Computer Company proposal. 2) Conduct a sensitivity analysis that focuses on the cost...
Evaluate the Pear Computer Company proposal. 2) Conduct a sensitivity analysis that focuses on the cost of capital. For a best case scenario, decrease the cost of capital by three percentage points. For a worst case scenario, increase the cost of capital by three percentage points. 3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case, and the worst case. 4) What do you recommend? Explain. You may type your recommendation and...
,10. ***( I NEED TO SOLVE THIS WITH EXCEL SPREADSHEET AND SENSITIVITY REPORT) *** Grave City...
,10. ***( I NEED TO SOLVE THIS WITH EXCEL SPREADSHEET AND SENSITIVITY REPORT) *** Grave City is considering the relocation of several police substations to obtain better enforcement in high-crime areas. The locations under consideration together Potential Locations for Substations: Areas Covered: A 1,5,7 B 1,2,5,7 C 1,3,5 D 2,4,5 E 3,4,6 F 4,5,6 G 1,5,6,7 a. Formulate an integer programming model that could be used to find the minimum number of locations necessary to provide coverage to all areas....
I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to...
I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to solve problem #31 (the PUTZ, Inc. project) for Chapter 10 in the textbook. 2) Conduct a sensitivity analysis that focuses on the sales price by increasing the price by 10% above the best estimate, and then by decreasing the price by 10% below the best estimate. 3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case,...
Please use Excel to solve the assignment and submit as an excel spreadsheet. Bethesda Mining Company...
Please use Excel to solve the assignment and submit as an excel spreadsheet. Bethesda Mining Company Based on a Mini Case presented in the textbook Ross, S.A., R.W. Westerfield and J. Jaffe, Corporate Finance, McGraw Hill/Irwin. Bethesda Mining is a midsized coal mining company with 20 mines located in Ohio, Pennsylvania, West Virginia and Kentucky. The company operates deep mines as well as strip mines. Most of the coal mined is sold under contract, with excess production sold on the...
Analysis 1: Renown Health Server Virtualization For this analysis you are to evaluate a proposal for...
Analysis 1: Renown Health Server Virtualization For this analysis you are to evaluate a proposal for Renown Medical Center to optimize their data center by virtualizing (see chapter 5) their existing servers. Use the following facts to conduct a break-even analysis of the virtualization technology. Costs It is estimated that virtualization will reduce the number of servers required in Renown’s data center by a ratio of 4 to 1. That means for every 4 physical servers running applications, the virtualization...
Question 3.4 Conduct a sensitivity analysis and use margin of safety to give Crooked Creek wine...
Question 3.4 Conduct a sensitivity analysis and use margin of safety to give Crooked Creek wine advice about two different proposed labour costing methods. Crooked Creek Wines operates a cellar door venue in which customers can sample and purchase wines. The average revenue per day is $750, variable cost per day is $250 and annual fixed costs for the cellar door operations is $95,000. Assume 290 operating days. Calculate the margin of safety in units and dollars and the margin...
1. Suppose you are hired as a consultant to conduct a cost/benefit analysis of a proposal...
1. Suppose you are hired as a consultant to conduct a cost/benefit analysis of a proposal to power a school with 100% solar power. a) identify all of the social benefits that you would need to estimate and classify the values (existence, extractive use, etc). Are the private benefits for the school the same as the social benefits? b) identify all of the social costs that you would need to estimate. Are the private costs for the school the same...
1. Understand how to use EXCEL Spreadsheet (a)  Develop proforma Income Statement Using Excel Spreadsheet (b)  Compute  Net Project...
1. Understand how to use EXCEL Spreadsheet (a)  Develop proforma Income Statement Using Excel Spreadsheet (b)  Compute  Net Project Cashflows, NPV,  and IRR (c) Develop problem-solving and  critical thinking skills and make long-term investment decisions 1) Life Period of the Equipment = 4 years 8) Sales for first year (1) $200,000 2) New equipment cost $(200,000) 9) Sales increase per year 5% 3) Equipment ship & install cost $(35,000) 10) Operating cost (60% of Sales) $(120,000) 4) Related start up cost $(5,000)     (as a percent...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT