PLEASE EXPLAIN USING EXCEL FUNCTIONS - THANK YOU

ABC Corp. will soon be undertaking its capital budgeting review process, and the board must approve all projects calling for expenditures greater than $10 million. ABCs top managers have discussed the appropriateness of different criteria they might use to evaluate proposed capital expenditures, including the choice between NPV and IRR.

ABC has a capital budgeting model to analyze all of its proposed projects. The model first forecasts each projects cash flows, after which it calculates the payback, Net Present Value (NPV), Internal Rate of Return (IRR), and Modified Internal rate of Return (MIRR). The model is also set up so that the analyst can see the effect of alternative sets of assumptions, ranging from scenarios where everything goes well to those where things go badly. These scenarios are used to get an idea of the projects risk. The president asked you to invite the controller and the vice presidents for marketing, production, and human resources to the capital budgeting sessions. Those executives must provide critical inputs for capital budgeting decisions, and the president wants to make sure that all participants know how the data they provide will be used to analyze capital budgeting decisions. In addition, several executives have questioned the weights that have been given to the decision criteria in actual capital budgeting decisions. For example, both the controller and the VP for marketing think that the most weight should be given to the payback, and that any project with a payback of less than 3 years should be accepted. Similarly, the VP for production thinks that the Payback is the best method, but several directors seem more comfortable with the IRR. You personally would like to consider only the NPV had the President not interceded and asked you to calculate all 5 criteria.

Table 1 provides some simplified data on two projects, S and L. Both involve developing a new microprocessor, but with different ways of handling the operation. Under plan S, the project would be accelerated; hence it would have high sales in Year 1. However, under S the firms competitors would learn about the product relatively soon, and sales would decline as competitors began to produce similar microprocessors. Therefore, Ss cash flows decline over its 4-year life. Under Project L, things would be handled differently.

Here, ABC would operate slowly, carefully, and secretly, and customers would be locked in through long-term contracts. Thus under L, sales and cash flows would rise over time. Even so, new products would eventually replace this microprocessor, so L would also have a 4-year life. Moreover, sometimes the different projects are independent, meaning that both can be accepted because their cash flows are not related to one another, but at other times projects are mutually exclusive, meaning that only one of several alternative projects can be accepted. Another issue that arose recently concerns situations in which the company can structure a project so that it will have a relatively short life or, by spending more money to buy longer-lasting equipment, a longer life. Two other proposed projects, SS and LL, whose cash flows are shown in Table 2, illustrate this situation. SS calls for the purchase of relatively inexpensive equipment that can be used for 2 years to produce a recently developed chip for the telecommunications industry. LL calls for the purchase of more expensive equipment that can be operated for 4 years. Such projects are, by definition, mutually exclusive, and again, decisions about them have led to heated discussions.

1. Calculate the Payback, NPV, IRR, and modified IRR (MIRR) for Projects S and L. If these projects are independent, do the criteria indicate that either, or both, should be accepted? Postpone a consideration of the situation if the projects are mutually exclusive until later, after the pros and cons of the various methods have been analyzed.
2. Surveys by various academics indicate that most companies, large and small, calculate the payback and apparently give it some weight in their capital budgeting decisions. Small companies often rely exclusively on the payback. What are the pros and cons of this criterion, and do you think ABC should give it any weight in its capital budgeting decision process?
3. If some of ABCs directors feel strongly that the IRR should be given significant weight in the decision process, would you recommend that they focus on the regular IRR, the MIRR, or both? Why?
4. The expected cash flows for Projects SS and LL are shown in Table 2. Explain why a problem exists, and then recommend which of these projects, if either, ABC should choose. Calculation Required
5. If S and L are mutually exclusive, which project would you recommend? Explain your choice in a manner that should satisfy the President and the other directors. HINT: Calculate the crossover rate and create a NPV profile. Show the Graph!

Table 2 Mutually Exclusive Project with Unequal Lives WACC Table 2 Mutually Exclusive Project with Unequal Lives WACC