Hello, I need help withthis homework problem. I feel like I'm missing a step that's preventing me from getting the answer. The assignment is to be completed in Excel. If using Microsoft Word, then provide detailed instructions of how to input answers in Excel. I have attached this week's reading for more details. Thank you.

Assignment 7: Evaluation Criteria using IRR

Automated Welding Services, Inc. (AWS) business has been growing and they need to raise their automation to the next level. They have collected data on five alternative machines/processes for which the data is shown below. Only a single alternative will be selected. AWS uses a 5-year time horizon for project justifications. Submit your solution in a spreadsheet.

1. Determine which will offer the largest internal rate of return for AWS.
2. Using an internal rate of return criterion with an incremental analysis, determine which alternative will offer the largest monetary benefit to AWS if their MARR is 12%.
3. Determine the present worth of each alternative.

Altern- ative	Investment in year 0	Annual Cash Flow	Salvage value in last year
A1	$50,000	$18,000	$0
A2	$250,000	$85,000	$75,000
A3	$350,000	$110,000	$175,000
A4	$600,000	$150,000	$400,000
A5	$800,000	$175,000	$600,000

CHAPTER 7: PROPOSAL EVALUATION This chapter changes the focus to the financial evaluation of proposals for change. Technology management is largely oriented to making changes, which require investments. These investments have to be approved by executives, and a major part of their criteria is financial and is based on the financial concepts of the prior chapters. That is they utilize the same five variables; present value, future value, annual value (payment), number of periods and interest rate. The data used with these is the proposal's cash flow that is determined using the accounting statement format of the previous chapter. Several criteria are alternatively used to evaluate proposals, and all should lead to the same conclusion. Organizations choose one or more of these criteria depending on the background of their executives, the industry in which they operate, and tradition (\"We have always done it this way). Because of this, it is critical that you discuss analyses with a financial manager before submitting a major proposal. Every organization has its accepted ways of doing things, and you want the focus to be on the proposed technology, not on an inadvertent deviation from the company's financial criteria or tradition. The content in this and the upcoming chapters should prepare you for any situation. Addressed in this chapter are the alternative financial criteria that are used to evaluate proposals. Covered in the next two chapters are the processes for determining the cash flow used with the financial criteria. First, some terminology needs to be introduced. Minimum Attractive Rate of Return (MARR) Proposals are evaluated to determine if the resulting cash flows cover the investments. The costs of a proposal include all the investments in machines, facilities, technology and people, implementation, and also the cost of capital. This cost of capital is stated as a percentage of the investment; that is, a rate. As was noted in a previous chapter, organizations raise capital from many different sources such as loans, bonds, stocks, etc. Each one has differing interest rates, dividends, risks, administrative costs and availability. Data can be gathered on each and a cost of capital (CofC) rate can be determined using a weighted average (based on the percentage of each that is used). Alternatively, the interest, dividends, and administrative costs incurred can be divided by the total funds raised (all found on the accounting statements). Either approach yields a rate called the cost of capital (CofC) rate. This CofC is normally adjusted for inflation and risk to get what is called the Minimally Attractive Rate of Return (MARR). The MARR is considered the annual financial cost of financing an investment stated as a percentage. Inflation Component of the MARR Arriving at a MARR first involves adjusting the cost of capital to reflect forecasted inflation. For proposals, each investment revenue and cost amount could be increased by an expected inflation rate. Rather it is common to increase the cost of capital by an amount that reflects inflation. Often the inflation rate is simply added to the CofC. If the annual cost of capital is 10% and inflation is expected to be 3% annually, the MARR prior to the risk adjustment is 10% + 3% = 13%. The mathematically correct calculation is: MARR = (1 + CofC rate)*(1+ inflation rate) -1 1 = CofC rate + inflation rate + CofC rate * inflation rate = 10% + 3% + 10% * 3% = 10% + 3% + .3% = 13.3% Considering that forecasts of inflation and future cash flows are inexact measures, the difference of .3% (.003) is of little consequence. Risk Adjustment of the MARR Proposals all involve the future and future benefits and costs are forecasts. The relevant clich concerning forecasts is that \"the only sure thing about forecasts is that they will be wrong\". Forecasts are averages and about half of the time they will be higher than estimated, and half of the time they will be lower than estimated. Moreover, that may be optimistic. Aiming for the MARR without a risk adjustment means that 50% of the time you would be above the estimate and 50% of the time you would below (lose money). This is generally not acceptable; Therefore MARRs are normally increased by a cushion set by management that includes risk. Organizations usually increase the MARR by at least 3%. Therefore, the setting of the MARR is a policy decision of senior management and at least in concept is: MARR = CofC rate + Inflation Rate + Risk rate This MARR and its use in discounting is the basis of the financial aspect of proposal evaluation. Note that if a MARR of 0% is used, then all criteria reduce to simply summing cash flows as no discounting results. Fifty years ago, this omission of the time value of money was standard procedure. Analysis of Change only The evaluation of proposals focuses only on the monetary values relevant to the proposal. You may say, \"Well, of course!\" but it is not as obvious or as easy as it first seems because it is sometimes difficult to separate out those aspects only relevant to a proposal. A new product innovation may increase quantity sold and therefore revenues (quantity time price). Only the increased revenue should be included in the analysis, not the total company revenue. However, sometimes this will cut into the quantity sold or price, or both, of existing products. Furthermore, it may affect the costs for existing products if they share production resources. Therefore, in some cases, the cash flow that would result if no change were made has to be subtracted from the cash flow if the change is made to determine the change in monetary values due to a proposal. If only costs will be changed by a proposal then the changes in costs should be considered. In this case, revenues can be ignored if they are not changed by the proposal. It cannot be emphasized too much that proposal approval decisions are based on the change enabled by the proposal. Sorting out the changes is critical aspect of a financial analysis. Planning Horizons (Time Spans) The time span chosen for proposals often affects the evaluation criteria. It typically is a number chosen by the organization's executives. It should be long enough to consider both low 2 and high return years. Some businesses use differing time spans depending on the size of the investment. An example would be: Under $1,000, not considered an investment but rather comes out of departmental budgets as an expense. $1,0001-$10,000, use two years, $1001-100,000, use three years $100,000-$1 million, use four years Over $1 million, use five years. Sometimes, not all alternatives may have the same time span. Perhaps one machine has a different forecasted life span than another. Perhaps one process is expected to be viable for a shorter time than another. Some organizations simply use the annual worth of each alternative that is computed for the relevant life span. This is justifiable when cash flows are relatively constant or change similarly for each alternative. If not, the better approach is to estimate salvage values, if any, when a machine is replaced and include this and the replacement cost in the cash flow. This is discussed in forthcoming chapters. Independent vs. Mutually Exclusive Alternatives Project approval decisions are one of two basic types: independent or mutually exclusive alternatives. Independent proposals are \"go\" or \"no go\" options for each individual proposal. Acceptance can be for all proposals, none of the proposals, or anywhere between. The independent proposals do not compete with each other. An example is a situation where an investment committee, without a budget limit, looks at all submitted proposals and either approves or rejects each of them independently of the others. Mutually exclusive alternatives means that only one of the presented alternatives will be chosen because they are alternative means for the same project. For instance, if two machines are proposed for a task and only one is needed and will be chosen, the two machines are mutually exclusive alternatives. Instead of independent and mutually exclusive categories, the two categories could be similarly described as competitive and non-competitive. If only one of the alternatives will be accepted, it is a competitive or mutually exclusive situation. If multiple projects can be evaluated as single projects (each is a \"go\" or \"no go\" decision), it is a non-competitive or independent evaluation. Proposal Evaluation Criteria There are two broad types of proposal criteria with one focusing on the monetary worth of a proposal, and the other focusing on the rate of return of the proposal. The first five, as shown in figure 7-1, focus on the monetary worth of proposals. Each is based on one of the time value of money concepts presented in earlier chapters of present value, future value, payment, and number of periods. The last one focuses on the rate of return of a proposal. All of these should yield the same choice as long as the same MARR and time horizon is used. The selection of which criteria is used to evaluate a proposal is dependent on an organization's policies and the particular proposal. 3 Figure 7-1: Criteria Used to Evaluate Proposals MONETARY WORTH PROPOSAL CRITERIA This section addresses the monetary worth criteria and contains several variations on this. The basics of present worth calculations are illustrated in figure 7-2. In figure 7-2, there are three components of the cash flow. An investment for $1,000 is made in year zero. Profits of $250 a year are forecasted for years 1-5. In addition, a salvage value from selling the equipment purchased by the investment generates $100 in year 5. These three are summed to get the cash flow in years 0-5. Figure 7-2: Data for Present Worth Example The present worth calculation for this example is shown in figure 7-3 in three ways and using a MARR of 8%. The major change with present worth calculations is that the PV function (and the FV and PMT) function is prefaced with a minus sign. The NPV function is not. This is the opposite of what was done with loan and annuity calculations of earlier chapters. The first example determines the three components separately. The present value of the five annual payments is determined. Second, the present value of the salvage value is determined. Finally, the present value is recorded. These three components are then summed to get the present worth. The middle example in figure 7-4 combined the five years of payments and the savage value in one PV function. The annual payments are the pmt argument and the salvage value is the fv argument. The result is added to the present value to get the present worth. The last example used the NPV function and the cash flow line. The present value of $1,000 is added to this to get the present worth. Although not recommended, the alternative of prefacing the pmt and fv arguments in the PV function with a minus sign works as well. 4 Therefore the project overall earns $66.24 beyond the 8% return. Therefore, after paying the 8% interest and receiving the five periods of cash flows as shown, the company comes out, in today's money, ahead by $66.24. This $66.24 is the present worth. Figure 7-3: Calculations for Present Worth Example A short video discussing this is at Present Worth. Case Example: Acme Building Cleaners Acme Building Cleaners (ABC) cleans office buildings in a large city. Most of the work is done after offices close. They have a good reputation with reasonable costs, but profits are decreasing and some potential new clients feel their costs are too high. The CEO requested his staff to find cost reductions that would not diminish their quality. Two mutually exclusive proposals are being evaluated in detail using one building for the analysis. One proposal is to use robotic floor cleaners. One person could feasibly manage and maintain several of these at once. This person could move from cleaner to cleaner and remove collected dirt, replenish cleaning supplies, and replace batteries. The required investment in the robots would be $40,000 plus $10,000 for programming and set up for a total of $50,000. A second proposal is to purchase manually guided cleaning machines. They would be faster than present hand tools since they would be machined paced with fewer missed spots, etc. The Advanced Manual cleaners would require an investment of $30,000. The cash flows of the two proposals in addition to the cash flow if neither is implemented is shown in figure 7-2 5 Figure 7-2 Acme Building Cleaners Proposal Cash Flows The increased cash flow in Year 1 due to adopting the Robot alternative will be $34,650- $25,000 = $9,650. Similarly the increased cash flow in Year 1 due to adopting the Advanced manual alternative will be $36,750-$25,000 = $11,750. The same calculations are performed for years two and 3. These as shown in figure 7-3 are the cash flows that will be used with the investment in year zero to make an evaluation. Figure 7-3: Acme Building Cleaners Incremental Cash Flow Alternatives ACME: Present Worth The first proposal criterion to be discussed is present worth. The present worth is the present value of future cash flows plus the year zero cash flow. The standard sign conventions of being negative if outgoing and positive if received are followed. If the present worth of an alternative is equal to or greater than zero, it means that the MARR has been met or exceeded and it meets organizational requirements. Now this will applied to the Acme Building Cleaners Case. The computation for the present worth is shown in figure 7-6 using the data from the above summary table. Thus, from a present worth perspective, the Advanced Manual alternative is the better choice as is has the larger present worth of $694. Figure 7-6: Acme Building Cleaners Present Worth 6 ACME: Future Worth Criterion Another criterion that can be used to evaluate proposals is future worth. It is computed directly from the present worth. If you know the PW, you know FW by the relation FV = PV x (1 + r) ^ n. Alternatively, the spreadsheet FV function can be used as shown in figure 7-7. Note that a minus sign is needed in front of the FV function since it is being used for \"worth\" calculations. Since the future worth is \"equivalent\" (this does not mean equal) to the present worth, the selection resulting from using present worth will be the same as when using present worth. If the analysis shows a FW >= 0, then the MARR will be achieved. For independent alternatives, all would be approved if the FW >= 0. For mutually exclusive alternatives, the alternative with the greatest FW would be selected. Therefore, from a future worth perspective, the Advanced Manual cleaner is the better financial choice. Figure 7-7: Acme Building Cleaners Future Worth ACME: Annual Worth Criterion The annual worth (AW) criteria for investments converts all cash flows including year zero through year n into equal or constant period payments. Annual worth has intuitive appeal to some people because it shows the annual effect of a project over its time span. However, it can be misleading since large upfront investments require the cash flow upfront that an annual worth value can camouflage, and cash flow is important. Nevertheless, annual worth is of benefit in a situation where alternatives have varying patterns of cash flow as it provides a constant annual number over the lifetime of the project. The annual worth is calculated from the present worth using the spreadsheet PMT function as shown in figure 7-8. The annual worth criterion works similarly (equivalently) to the present worth and future worth criteria. If the AW >= 0, then the proposal should achieve or surpass the MARR. For independent alternatives, all would be approved if the AW >= 0. For mutually exclusive alternatives, the alternative with the greatest AW would be selected. The same conclusion can be reached for Acme Building Cleaners, which it should since annual worth is equivalent to present worth and future worth. 7 Figure 7-8: Acme Building Cleaners Annual Worth A video segment showing the calculation of present worth, future worth and annual worth for Acme can be viewed at PW_FW_AW. Payback Period Proposals can involve changes that do not affect revenues. One example is process changes that reduce costs but are not expected to change revenues. Other examples are security upgrades, software upgrades, etc. that do not directly affect revenues but will increase costs. All these result in negative cash flows. The change has to be done and the least costly approach needs to be selected, assuming everything else is equal. The criterion used to do make a selection is Present Least Cost (PLC). PLC is the same as the Present Worth criterion except that all cash flows are negative and the least negative is the chosen alternative. Least negative is actually the highest on a number scale, so there is no difference from Present Worth except for the signs. An example in figure 7-9, not from the ACME Building Cleaners case, illustrates this. In this example, replacement machine A cost $1,000 less to purchase, but it incurs higher annual costs. The Present Least Cost calculation shows that the replacement Machine B would be chosen since it is the lower of the two. A video segment on PLC is at PLC. Figure 7-9: Present Least Cost Criterion Payback Period The next criterion to be covered is the payback period criterion. The criterion is to judge investments by how fast they can be paid off using future savings. Individuals often use a simplified version of the payback period approach in their daily lives. For instance, should one buy a lawn tractor to mow one's lawn, or hire a lawn service? Suppose a lawn service would cost $250 a month. The investment in a $2500 lawn tractor would be paid for in 2500 / 250 = 10 months (two summers of lawn service?). Thus, the payback period of a lawn tractor would be ten months of use. Since the tractor mower is expected to last at least twice that long with minimal maintenance, the purchase looks 8 advisable, assuming one has the time and desire to \"do it yourself\". If not, the opportunity cost or benefit of doing something else should be included in the analysis. Payback Period Criterion with Equal Payments The payback criterion uses the number of periods as a criterion as opposed to the present value, future value and payment. An organization's approval criterion states the number of years cash flows have to cover the cost of an investment. For example, a three-year payback period would require that sufficient cash flow be received by the end of year three to cover the investment. A simple example in figure 7-10 illustrates this. Three alternatives are considered with different year-0 investments and annual cash flows. Since the annual payments are equal, the NPER function, as shown in columns G and H, can be used to determine the number of periods to pay off the investment. The company uses a MARR of 8% and a payback criterion of no more than 4 years. Consider first that these are independent alternatives where any to all of them could be chosen. The acceptable alternatives that take four or less years to pay back their investment are Option A at 3.9 years and Option C at 3.7 years. Option B at 4.3 years would not be approved. If instead the options were mutually exclusive where only one could be chosen, the choice would be Option C as it has the shortest time to payback. Figure 7-10: Payback Period Calculation with MARR = 8%. Prior to the introduction of time value of money concepts into proposal evaluations, the payback evaluations essentially used a MARR = 0%. Figure 7-11 shows the same analysis for the above example but with a MARR = 0%. Since no financial costs are included, the calculations could be simplified to be the investment divided by the annual cash flow. This yields the same result (e.g. for Option A, $29,000 / $9,000 = 3.2 years). Figure 7-11: Payback Period Calculation with MARR = 0%. 9 For this case, all three Options would be approved if they are independent choices and Option C would be chosen if mutually exclusive. Discounted Payback Period Criterion with Unequal Payments If the annual cash flows vary, the payback analysis is a little more complex. Consider the situation shown in figure 7-12 where five years of data has been collected. The company uses a 3-year payback criterion for this type of investment and a MARR of 12%. Figure 7-12: Data for Unequal Payments example Since the payback period is three years, only the first three years are listed in figure 713. For each proposal, the net present value of the future cash flows are determined and added to the year-0 investment to get the present worth. Only Proposal A meets the 3-year criterion. Figure 7-13: Three-Year Payback Analysis The discounted payback period can also be performed using a tabulation of the future worth of each alternative as shown in figure 7-14. The payback year is the one in which the future worth turns positive. If the pay back criterion (required number of years) is less than this year, the project is rejected. Note that if this future value is converted to a present worth, the same value results as found in figure 7-12. As shown, the present value of the year-3 future value of $2,465 is $1,755. The two approaches are equivalent. Figure 7-14: Discounted Payback Period Criterion Discounted Payback period is used by some organizations because it is simple and intuitive. Discounted payback period analysis is the same as the present or future worth criteria 10 with the project life span set to be the number of payback years. A video segment on Payback period can be viewed at Payback. INTERNAL RATE OF RETURN CRITERION Investors regularly measure their success by a percentage return on investment. Loans for home, cars, education are based on interest rates, so people in general are comfortable with rates of return. Present worth, future worth, and annual worth correctly evaluate proposals, but some executives feel more comfortable with a number that indicates the percentage return that will be earned on an investment. A present worth of say $1 million dollars sounds good, but a 20% return on one's investment may be more convincing to some. The spreadsheet function IRR is used to compute the internal rate of return when the annual cash flows are not equal. The RATE function can be used if the cash flow payments are all equal. The IRR and RATE spreadsheet algorithms used in spreadsheet functions to determine a rate conduct a search (essentially trial and error) for the rate that makes the present worth of the cash flows zero. At a PW = 0, the MARR will be achieved. This is true because the MARR is used to determine the PW. Put another way, if a proposal has a PW= 0, the investment is forecasted to earn the MARR rate of return. The MARR is an EAR. The EAR is alternatively referred to as the return on investment (ROI), annual rate of return, or internal rate of return (IRR). If a MARR or PIR is stated with nonannual compounding, it normally should be converted to an annual PIR = EAR. Because the IRR is a ratio as discussed previously, many factors can complicate the IRR calculation. In the following subsections, we will look at these situations. Return on Investment: Single Investment and Single Return At one time, the term return on investment (ROI) criterion was in vogue. The \"return\" in Return in Investment is the gain associated with an investment. If an investment of $100,000 results in cash flow of $120,000, the gain is $20,000 and the return on the investment is $20,000 / $100,000 = 20%. The equation for this traditional ROI criterion is: ROI = (Return - Investment) / Investment This traditional ROI equation, as shown here, treats all investments and returns as happening at the beginning and end of the same period, typically the current period or period 0. This approach assumed that there was not a time value of money (e.g. no interest, inflation, etc.) so that future returns could simply be summed. For instance, consider the cash flow situation in figure 7-15. The sum of all future cash flows is $1,100,000 as shown in cell C16 of figure 7-15. The investment in year zero is $600,000. Shown at the bottom are both the traditional ROI calculation and the internal rate of return (IRR). An ROI of 83.33% is obviously outlandish. The investor does not earn interest of 83.3% on the investment each year. To be more accurate, either the investment of $600,000 has to be considered as being invested at the beginning of each of the five years, or the sum of the returns needs to be divided by 5. This yields an annual average rate of 16.67%, but it is not accurate since it ignores the time value of money and the compounding of interest. The ROI equation is only accurate if the MARR equals zero (cost of capital, inflation and risk are all zero), 11 or in the situations of investments and returns in the same period. The IRR is the accurate annual return of 16.30%. This section focuses on the IRR. Figure 7-15: ROI: Incorrect results for Multiple Years Periodic Cash Flows. The IRR function can be used to determine the IRR for both equal and unequal payments, and it must be used if the periodic payments are not all equal. The following considerations are important when using the IRR function, and if these are not followed, a #NUM or a mistaken result will occur. The data must be in contiguous columns as shown in figure 7-16 or contiguous rows. Since the cash flow for each year is listed, they can be equal or unequal. The only argument in the IRR function is the range of cells containing the cash flow values (= IRR(C26:H26) in figure 7-16). The values themselves cannot be entered in the IRR function, only cell addresses and they must be stated as a range (e.g. C26:H26). This range must start with period zero, where the investment commonly occurs. Investments are recorded as negative amounts (paid out of your pocket). At least one negative amount and one positive amount must exist in the list of values. Since the year-zero amount is usually an investment and therefore negative, this normally happens. The RATE function can be used to compute the IRR if the periodic payments are all equal. If an investment of $600 was initially made (the PV), five equal annual returns of $200 were received in years 1-5, and also a salvage value of $100 was received in year 5 (the FV), the RATE function can be used to determine the internal rate of return as shown in figure 7-16. 12 Figure 7-16: Example of IRR with Equal Periodic Cash Flows Note that for cost minimization (Present Least Cost) situations where cash flows are all negative, the IRR cannot be determined. Independent vs. Mutually Exclusive Alternatives As noted several time previously, proposals are of two types where multiple alternatives are involved. One type is that one alternative up to all alternatives could be approved. For instance, consider if investment proposals are submitted to an investment committee from various groups in an organization independently. If funds are available, all could potentially be approved, but each one has to meet the investment criteria of the internal rate of return exceeding the MARR. The second type is mutually exclusive alternatives, where only one of several alternatives can be chosen. A typical situation of this is for where only one of several competing machines will be chosen to perform a task. The one with the largest IRR would be chosen. Both independent and mutually exclusive alternatives will be illustrated using the following case of a fictitious company named University Athletic Wear (UAW). UAW is evaluating several new product proposals. UAW uses a MARR of 15% and a time span of five years for project evaluations. The forecasted cash flows for each alternative are shown in figure 7-17. Figure 7-17: UAW Example Data used with IRR Explanation Independent. We will first assume that resources are available for one or more products. That is, the approve-reject decision for each is independent of the others. In figure 7-18, the internal rate of return is computed for each alternative using the IRR function and the data from figure 7-17. If 13 the IRR is greater than the MARR, the alternative is approved. So with the MARR of 15%, Shorts are rejected and Shirts, Jackets and Caps are approved. Figure 7-18: UAW Example using IRR with Independent Alternatives Mutually Exclusive Now assume that only one of the four products concerning the alternative new products can be selected because of a lack of financial or human resources. That is, the choice of one new product excludes all the others. This is a mutually exclusive choice. This requires a multiple step process to eliminate the bias of the investment size inherent in the IRR ratio. The first step is to eliminate any alternative that has an IRR less than the MARR. The IRRs are determined as shown in figure 7-19. This step eliminates \"Shorts\" as its IRR of 12.35% is less than the MARR of 15%. Note that although the Cap alternative has the largest IRR, its investment being smaller than the others may bias the IRR calculations. Therefore, it may not be the best choice. Figure 7-19: Mutually Exclusive IRR Analysis Step 1: Sort by Investment The second step is to sort the remaining alternatives in increasing order of investment as shown in figure 7-20. Sorting these in order of the size of the investment is needed to sequence through the alternative two at a time to see if the incremental investment of one alternative over the other is justified by the corresponding incremental cash flow. 14 Figure 7-20: Mutually Exclusive IRR Analysis: Eliminate where IRR