Excel Spreadsheet and Writing Assignment

XYZ Corporation manufactures textiles. Their manufacturing facility is almost fully automated and has several machines that were installed about 15 years ago. These machines are getting old and often have to be shut down for maintenance and repairs. These machines do not have latest technology to adapt to the contemporary designs that are in demand.

The marketing manager Ms. Jane Austen has given you the task to conduct a thorough analysis and offer your recommendations, with justification. There are two alternatives under consideration.

Alternative 1

It may be economical to repair the old machines and continue using them for five more years. This would involve the cost of repairs and downtime. However, there will be substantial savings since investment in new machines will not be required. The designs produced are old, however it is expected that these will keep selling at a slow and moderate pace for another five years.

Alternative 2

It may be worthwhile to replace these old machines with new machines. Although these machines will require substantial initial investment, these machines produce at a faster pace with much less defective lots. In addition, there will be huge savings for repairs and downtime. Since the technology is new, these machines can be easily adapted to modify the designs based on changes in market demand. The suppliers have indicated a useful life of five years for these machines. After this period, it is expected that these machines could be sold to another smaller textile factory at reasonable price at that time.

You have gathered all the data fir these two alternatives and have come up with the following cash flows based on incremental analysis. You have considered all possible costs and revenues expected. However, the future economic situation is uncertain and the WACC (weighted average cost of capital) of raising capital for all projects for the company is also difficult to ascertain with precise accuracy. Based on the probabilistic analysis, you have come up with three possible scenarios for WACC which could be 10%, or 13%, or 15%

Cash Flow Estimates are as follows:

Alternative 1: Repair

Year Cash Flow

0 : 50,000

1: 15,500

2: 20,100

3: 18,900

4:17,100

5: 13,700

Alternative 2: Replace

Year Cash Flow

0: -400,000

1: 61,300

2: 155,000

3: 127,800

4:126,900

5:125,100

Excel Spreadsheet Assignment:

Create an Input section on Excel with all the given estimates of cash flows and WACC. If you have any other assumptions, please incorporate those in this input screen Create an output screen in Excel and calculate the following capital budgeting decision criteria for each assumed WACC. (Have three separate calculations under three WACC) Payback Period (create a table for calculations) Discounted Payback Period (create a table for this calculation) Net Present Value (NPV) (Use Excel function to calculate) Internal Rate of Return (IRR) (Use Excel function to calculate) Modified Internal Rate of Return MIRR (Use Excel function to calculate) Profitability Index (PI) (write a formula to calculate this)

Write =IF() statements to give your decision to accept or reject the project under each criteria Give the Final overall decision under each scenario (of WACC) considering all above six options In a separate space on output area, make a table for calculating the crossover rate for the two NPV profiles (use =IRR() function with differential cash flows) Calculate the NPV profiles for the two alternatives by assuming a range of WACC starting with 0% and going up to 25% (increments of 5%) Make one graph of these two NPV profiles. Label your graph with a title, X and Y axis, IRR, and cross over rate (clearly marked and shown) The spreadsheet should be a model such that if the input screen cash-flows are changed, the answer and recommendation should change automatically in the spreadsheet.

The spreadsheet should be created independently and not copied from any source. You may watch several videos that are available on internet to help you guide. The hardest part would be to write the in-built functions to calculate the payback and discounted payback periods.

Please use only Excel functions and give cell references of the numbers in the input area. If the numbers in input are changed, your spreadsheet should automatically change all answers. Please do not solve problems on calculator and enter any numbers or values in the output screen. It is important to use only Excel functions and cell references.

Grade will be awarded for accuracy, adaptability, setup, ease of movement, and graphing skills.