I need an Excel Spreadsheet attached as to how you solved this. I have found the

Question

I need an Excel Spreadsheet attached as to how you solved this. I have found the following:

Payback period A: 2.6 B: 2.82
NPV A: 83,300 B: 103,610

I am not sure how to get IRR for these. I thought it was 26.7% and 47.5% but the computer program tells me I am wrong. At a loss here as to how to get "proper" IRR.

Lou Barlow, a divisional manager for Sage Company, has an opportunity to manufacture and sell one of two new products for a five-year period. His annual pay raises are determined by his division’s return on investment (ROI), which has exceeded 23% each of the last three years. He has computed the cost and revenue estimates for each product as follows:

  

  

The company’s discount rate is 14%.

  

Click here to view Exhibit 8B-1 and Exhibit 8B-2, to determine the appropriate discount factor using tables.

  

Required:

1. Calculate the payback period for each product. (Round your answers to 2 decimal places.)

2. Calculate the net present value for each product. (Round discount factor(s) to 3 decimal places.)

3. Calculate the internal rate of return for each product. (Round percentage answers to 1 decimal place. i.e. 0.1234 should be considered as 12.3% and round discount factor(s) to 3 decimal places.)

4. Calculate the project profitability index for each product. (Round discount factor(s) to 3 decimal places. Round your answers to 2 decimal places.)

5. Calculate the simple rate of return for each product. (Round percentage answers to 1 decimal place. i.e. 0.1234 should be considered as 12.3%.)

6a. For each measure, identify whether Product A or Product B is preferred.

6b. Based on the simple rate of return, Lou Barlow would likely:

rev: 11_10_2016_QC_CS-66978

Product A Product B Initial investment: Cost of equipment (zero salvage value) $ 260,000 $ 480,000 Annual revenues and costs: Sales revenues $ 330,000 $ 430,000 Variable expenses $ 152,000 $ 202,000 Depreciation expense $ 52,000 $ 96,000 Fixed out-of-pocket operating costs $ 78,000 $ 58,000

Explanation / Answer

21.49%

Calculation Of pay back period Product A Product B Year Amount Cumulative Amount Cumulative 1 100000 100000 170000.00 170000.00 2 100000 200000 170000.00 340000.00 3 100000 300000 170000.00 510000.00 4 100000 400000 170000.00 680000.00 5 100000 500000 170000.00 850000.00 PayBack For A 2 yrs+ 60000/100000 Payback for B 2 + 140000/170000 2.6 Yrs 2.823 yrs Calculation Of Net present Value Particulars Product A($) Product B($) Sales 330000 430000 Less: variable Exp. -152000 -202000 Less: Fixed Cost -78000 -58000 Cash Flow 100000 170000 PVAF(14%,5yrs) 3.433 3.433 Disc. Cashflow 343300.00 583610 Less: Initial Investment 260000.00 480000 Net Present Value 83300.00 103610 (Deprication not effect the cashflow, so its not considered) Calculation Of IRR Assuming If Rate of Return Is 30% then 0.76923077 Product A Product B 0.59171598 Cash Flow 100000 170000 0.45516614 PVAF(30%,5yrs) 2.46 2.46 0.3501278 Discounted Cashflow 246000.00 418200 0.26932907 Initial Investment 260000.00 480000 2.43556975 NPV -14000.00 -61800 IRR Lower Rate + (Higher NPV/Higher NPV - Lower NPV)* difference Product A 27.69784173 27.70 appx Product B 14% +103610/(103610+61800)*16 24.02212684 24.022 appx Profiability Index Discounted Cashflow / Initial Investment Product A Product B 343300/260000 583160/480000 1.32 1.214916667 Simple Rate Of Return Product A Product B 83300/260000 103160 0.32 0.214916667 32%

21.49%

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.