JUST ANSWER THE QUESTION IN THE EXCEL SHEET BELOW
Question 1 Hi Tone % of p 100 110 120 130 105 -500000 -550000 -600000 -650000 -525000 AW-H -313293 -333399 -353505 -373610 -323346 % of P 80 90 100 Extra-S P -640000 -720000 -800000 -880000 -960000 -1040000 AW-E -239731 -258100 -276468 -294837 -313206 -331574 110 120 130 -400,000 -380,000 AW vs % of P -360,000 -340,000 -320,000 -300,000 $ -280,000 -260,000 -240,000 % of P 90 100 80 110 120 130 140 -220,000 60 70 -200,000 Question 2 % of p AW-H 100 $76,707 110 $56,601 120 $36,495 130 $16,390 % of P 80 90 100 110 AW-E $110,269 $91,900 $73,532 $55,163 $36,794 $18,426 120 130 Question 3 Year 0 1 2 3 Hi-Tone -525,000 240,000 240,000 - 160,000 240,000 240,000 365,000 Cum. Hi -525,000 -285,000 -45,000 -205,000 35,000 275,000 640,000 Extra-X -800,000 230,000 230,000 230,000 230,000 230,000 440,000 Extra (-) Hi Cum. CF -275,000 -275,000 -10,000 -285,000 -10,000 -295,000 390,000 95,000 - 10,000 85,000 -10,000 75,000 75,000 150,000 4 5 6 AROR 13% PW ROR 290,296 26% 320,249 22% Project Part (A) As an enginner in RedOak company, your department head asked you to perform an analysis of installing MAP/TOP in the manufacturing planning devision of your company. (MAP/TOP is a communications integration software system developed by Boeing). The entire system would be implemented and operated by one of two consultant groups: Hi Tone or Extra-s. The first cost (includes MAP/TOP software, hardware, implementation and training costs) that your company will agree to pay is still in question. The possible variation of the initial cost estimate Pis included in the consultants' proposals below. (100% of P means use the whole amount of estimated P). Consultant Hi Tone Extra-S First cost estimate P, $ -500,000 -800,000 Variation in P. % From 100% to 130% of P From 80% to 130% of P AOC $/year - 150,000 -120,000 Salvage value, $ 125,000 210,000 Life of contract, years 3 6 Revenue 390,000 350,000 1- Develop the spreadsheet accompained by a scatter chart that graphs AW values (exclude revenue CF) vs the possible variations in first cost P, using 10% increments of P. MARR = 10% per year ante a- If the first cost of Hi-Tone is 100% of its P and the first cost of Extra-S is above 120% of its P, which alternative would you select? Answer: b- What is the perentage range of Hi-Tone first cost (P) that will always lead to the selection of Extra-S? Answer: 2- Suppose the annual revenue using Hi-Tone 390,000 and the annual revenue using Extra-S is 350,000. Find the AW for both alternatives using the range values of P. 0--If the cast range for both alternatives is 100% - 110%, which alternative would you select? Answer: b-If the cost range for both alternative is 120% - 130%, which alternative would you select? Answer: c- What is the perentage range of Extra-S first cost (P) that will always lead to its selection? Answer: 3- Assuming the first cost of Hi-Tone is 105% of its P and the the first cost of Extra-S is 100% of its P, put in table the net cash flow (including revenue) for both alternatives from year 0 to year 6. a- Find the PW of both alternative using NPV function, select the better alternative. Answer: b-Use IRR function to find the ROR of each alternative. Answer: C-Use ROR analysis to selet the suitable alternative. Answer: Question 1 Hi Tone % of p 100 110 120 130 105 -500000 -550000 -600000 -650000 -525000 AW-H -313293 -333399 -353505 -373610 -323346 % of P 80 90 100 Extra-S P -640000 -720000 -800000 -880000 -960000 -1040000 AW-E -239731 -258100 -276468 -294837 -313206 -331574 110 120 130 -400,000 -380,000 AW vs % of P -360,000 -340,000 -320,000 -300,000 $ -280,000 -260,000 -240,000 % of P 90 100 80 110 120 130 140 -220,000 60 70 -200,000 Question 2 % of p AW-H 100 $76,707 110 $56,601 120 $36,495 130 $16,390 % of P 80 90 100 110 AW-E $110,269 $91,900 $73,532 $55,163 $36,794 $18,426 120 130 Question 3 Year 0 1 2 3 Hi-Tone -525,000 240,000 240,000 - 160,000 240,000 240,000 365,000 Cum. Hi -525,000 -285,000 -45,000 -205,000 35,000 275,000 640,000 Extra-X -800,000 230,000 230,000 230,000 230,000 230,000 440,000 Extra (-) Hi Cum. CF -275,000 -275,000 -10,000 -285,000 -10,000 -295,000 390,000 95,000 - 10,000 85,000 -10,000 75,000 75,000 150,000 4 5 6 AROR 13% PW ROR 290,296 26% 320,249 22% Project Part (A) As an enginner in RedOak company, your department head asked you to perform an analysis of installing MAP/TOP in the manufacturing planning devision of your company. (MAP/TOP is a communications integration software system developed by Boeing). The entire system would be implemented and operated by one of two consultant groups: Hi Tone or Extra-s. The first cost (includes MAP/TOP software, hardware, implementation and training costs) that your company will agree to pay is still in question. The possible variation of the initial cost estimate Pis included in the consultants' proposals below. (100% of P means use the whole amount of estimated P). Consultant Hi Tone Extra-S First cost estimate P, $ -500,000 -800,000 Variation in P. % From 100% to 130% of P From 80% to 130% of P AOC $/year - 150,000 -120,000 Salvage value, $ 125,000 210,000 Life of contract, years 3 6 Revenue 390,000 350,000 1- Develop the spreadsheet accompained by a scatter chart that graphs AW values (exclude revenue CF) vs the possible variations in first cost P, using 10% increments of P. MARR = 10% per year ante a- If the first cost of Hi-Tone is 100% of its P and the first cost of Extra-S is above 120% of its P, which alternative would you select? Answer: b- What is the perentage range of Hi-Tone first cost (P) that will always lead to the selection of Extra-S? Answer: 2- Suppose the annual revenue using Hi-Tone 390,000 and the annual revenue using Extra-S is 350,000. Find the AW for both alternatives using the range values of P. 0--If the cast range for both alternatives is 100% - 110%, which alternative would you select? Answer: b-If the cost range for both alternative is 120% - 130%, which alternative would you select? Answer: c- What is the perentage range of Extra-S first cost (P) that will always lead to its selection? Answer: 3- Assuming the first cost of Hi-Tone is 105% of its P and the the first cost of Extra-S is 100% of its P, put in table the net cash flow (including revenue) for both alternatives from year 0 to year 6. a- Find the PW of both alternative using NPV function, select the better alternative. Answer: b-Use IRR function to find the ROR of each alternative. Answer: C-Use ROR analysis to selet the suitable alternative
