i have the spreadsheet solved please guide me through to the next part, do not w

ID: 2724542 • Letter: I

Question

i have the spreadsheet solved please guide me through to the next part, do not waste time on solving this again.. help me calculate the NPV and IRR.. kindly show me some steps

Investment analysis-The Baldwin Company (adapted from Ross, Westerfield and Jaffe, Corporate finance) The Baldwin Company, originally established in 1962 to make footballs, is now a leading producer of tennis balls, baseballs, footballs, and golf balls. In 1970 the company introduced "High Flite", its first line of opportunities in whatever businesses seem to have potential for cash flow. In 2000 W.C high-performance golf balls. The Baldwin management has sought eadows, vice-president of the Baldwin Company, identified another segment of the sports ball market that looked promising and that he felt was not adequately served by larger manufactures That market was for brightly coloured bowling balls, and he believed a large number of wlers valued appearance and style above performance. He also believed that it would be difficult for competitors to take a dvantage of the opportunity because of Baldwin's cost advantages and because of its ability to use its highly developed marketing skills As a result, in July 2009 the Baldwin Company decided to evaluate the marketing potential of brightly coloured bowling balls. Baldwin sent a questionnaire to consumers in three markets Philadelphia, Los Angeles and New Haven. The results of the three much better than expected and supported the conclusion that the brightly coloured bowling questionnaires were ball could achieve a 10- to 15-percent share of the market. Of course, some people at Baldwin complained about the cost of the test marketing, which was $250.000 Baldwin Company is now considering investing in a machine to produce In any case, the bowling balls. The bowling balls would be produced in a building owned.by the firm and located near Los Angeles. This building, which is partly vacant a nd would otherwise not be used, has a current market value of $150.000 (including the land) orking with his staff, Meadows is summarises his assumptions as follows. The cost of the bowling-ball preparing an analysis of the proposed new product. He machine is $200.000 ight-line depreciation). The machine has an estimated market value at the end of five (strai years of $30.000. Production by year during the five-year life of the machine is expected as follows: bowling-balls in the first year will be Meadows believes that the price of the bowling balls will increase at only 2 percent per year as compared to the anticipated general inflation rate of 5 percent. Conversely, the plastic used to produce bowling balls is rapidly becoming more expensi production costs (salaries, raw materials,..) are expected to grow at 10 percent per year First-year production costs will be $10 per unit 5.000 units, 8.000 units, 12.000 units, 10.000 units and 6.000 units. The price of the $20. The bowling ball market is highly competitive, so The risk adjusted rate of return is 17%. Taxes on earnings are at 34%. The vice-president asks you to put all the available data on the spreadsheet and to evaluate the proposed investment mulation of NPV with as required rate of return 0%, 8%, 20%, 30%, 35% (and put this simulation in a graph) Indicate on the graph with an arrow the IRR and explain the position of IRR

Explanation / Answer

NPV is the sum of present values of all cash flows of a project. The PV factors are calculated using the following formula:

PV factor = 1/(1+ Rate)^t

year

Cash flow

PV factor 17%

-200000

46600

0.854701

39829.06

63232

0.730514

46191.83

82567.36

0.624371

51552.63

65833.46

0.53365

35132.03

41349.7

0.456111

18860.06

NPV

-8434.39

Therefore, NPV is -8434.39

IRR can be calculated using IRR function in the excel.

=IRR( input all cash flows).

After solving you’ll get IRR of 15.17%.