A B $75,000.00 5.50% 5 7.75% 1 Turquoise Oasis Spa 2 Investment Analysis 3 4 Bond Details 5 Par Value 6 Coupon Rate 7 Maturity (in years) 8 Coupon Payment (annually) 9 Yield to Maturity (YTM) 10 Present Value (PV) 11 12 Investment Details 13 Present Value 14 Term (in years) 15 Interest Rate (compounded quarterly) 16 Total Contribution 17 Future Value 18 Total Earned 19 $350,000.00 5 6% 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37_ PresentAndFuture Value NetPresentValue Irregul Turquoise Oasis Spa Net Present Value Analysis 00 Own Discount Rate Total Initial Investment Input Values 4.57% $100,000.00 Cash Flows Initial Investment $58,450.00 $46,612.00 $34,003.00 9 10 Year 0 11 Year 1 12 Year 2 13 Year 3 14 15 Net Present Value 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 PresentAndFutureValue NetPresentValue IrregularNPV Intern AI J Turquoise Oasis Spa B D 1 Turquoise Oasis Spa Irregular Net Present Value Analysis 2 3 10% vou +wn 4 Required Rate of Return (RRR) 5 Dates 6 7 8 9 10 11 12 13 4/1/19 8/1/19 11/1/19 2/1/20 3/1/20 4/1/20 7/1/20 9/1/20 11/1/20 2/1/21 3/1/21 9/1/21 Cash Flows $90,000.00 Initial Investment $8,500.00 $7,500.00 $6,500.00 $4,000.00 $5,000.00 $8,750.00 $9,400.00 $10,000.00 $14,000.00 $14,340.00 $12,900.00 14 15 16 17 18 19 20 Irregular Net Present Value 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 PresentAndFutureValue NetPresentValue IrregularNPV JL A B D E F G H Turquoise Oasis Spa Internal Rate of Return Analysis Option 1 Cash Flows 5 Year o ($125,000) Year 1 $40,000 Year 2 $40,000 Year 3 $40,000 e Year 4 $40,000 O 1 2 Internal Rate of 3 Return (IRR) 4 Option 2 Cash Flows Year o ($125,000) Year 1 $65,000 Year 2 $35,000 Year 3 $20,000 Year 4 $15,000 Year 5 $15,000 Year 6 $10,000 Internal Rate of Return (IRR) 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6. PresentAndFutureValue NetPresentValue IrregularNPV InternalRate OfReturn A B D E F 1 Turquoise Oasis Spa 2 Irregular Internal Rate of Return Analysis 3 4 Required Rate of Return (RRR) 9.75% 5 6 Dates Cash Flows 7 3/31/20 $ 130,000.00 Initial Investment 8 6/30/20 $18,000.00 9 9/30/20 $25,000.00 10 10/31/20 $45,000.00 11 3/1/21 $17,000.00 12 5/31/21 $12,500.00 13 9/30/21 $10,000.00 14 12/31/21 $5,000.00 15 4/30/22 $3,000.00 16 17 Irregular Internal Rate of Return 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 NetPresentValue IrregularNPV InternalRate OfReturn IrregularlRR A B D E F G 1 2 3 Cost of asset 4 Salvage value 5 Useful life 6 Turquoise Oasis Spa Asset Straight-Line Depreciation Table $40,000.00 $3,871.00 5 Accumulated Depreciation depreciation at expense for year year end Book value at year end End of Year 1 2 3 4 5 Net Book Value 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 NetPresentValue IrregularNPV InternalRate OfReturn IrregularlRR Depreciation Turquoise Oasis Spa Asset Declining Balance Depreciation Table Cost of asset $27,500 Salvage value $2,097 Useful life 5 Accumulated depreciation at Depreciation expense for year year end End of Year 1 2 3 nmn Book value at year end $27,500.00 $27,500.00 $27,500.00 $27,500.00 $27,500.00 Net Book Value $0.00 $0.00 $0.00 $0.00 $0.00 4 5 IrregularlRR Depreciation Depreciation2 Depreciation3 A B D E F ou - wn 1 Turquoise Oasis Spa 2 Asset Double Declining Balance Depreciation Table 3 Cost of asset $27,500 4 Salvage value $2,097 5 Useful life 5 6 Book value at End of Year year end 7 8 1 Accumulated Depreciation depreciation at expense for year year end $0.00 $0.00 $0.00 $0.00 $0.00 3 w N $27,500.00 $27,500.00 $27,500.00 $27,500.00 $27,500.00 Net Book Value 4 5 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 IrregularlRR Depreciation Depreciation2 Depreciation3 Description Create Date By Whom mm/dd/yyyy Your Office Mod. Date By Whom Workbook Name e06ch11 grader_pc_pt1_Financial Analysis Last Version Backup Name Mod. Description 2 3 Create Date Sheet Name Creator Purpose IrregularlRR Depreciation Depreciation2 Depreciation3 Documentation + 1 Start Excel. Download and open the file named Excel_Ch11_Prepare_Financial_Analysis_PartB.xlsx. Grader has automatically added your last name to the start of the filename. Save the file to the location where you are storing your files. 2 In addition to analyzing possible expansion finance options from a bank, managers at the Turquoise Oasis Spa would like to analyze some possible investment opportunities to help finance the expansion and generate revenue in the long term. On the PresentAndFutureValue worksheet, in cell B8 calculate the Coupon Payment (annually) of the investment from the bank's perspective. The first step in determining whether or not this is a worthy investment is to calculate the present value of the investment. In cell B10, calculate the present value of the investment from the bank's perspective. 3 4 In cell B16, reference the present value cell, as the total investment will be a one time, lump- sum investment The future value function, or FV function, is used to calculate the value of an investment with a fixed interest rate and term, as well as to calculate periodic payments over a specific period of time. In cell B17, calculate the future value of the investment from the bank's perspective. 5 6 In cell B18, enter a formula to calculate the total amount earned by subtracting the total contribution from the future value. 7 8 On the NetPresentValue worksheet, in cell B10 insert a cell reference to reference the initial investment amount, which will be the cash outflow for year 0. The net present value function, or NPV function, is used to determine the value of an investment by analyzing a series of future incoming and outgoing cash flows that are expected to occur over the life of the investment. In cell B15, calculate the net present value for the investment from the bank's perspective. Add the value of the initial investment to the result. 9 The irregular net present value function, or XNPV function, determines the value of an investment or business by analyzing an irregular time series of incoming and outgoing cash flows. On the IrregularNPV worksheet, in cell B20 calculate the net present value of the investment based on the irregular cash flows. 10 Both investment options will have a higher internal rate of return than the risk-free investment option of 10.50%. However, option 2 has the higher IRR and should be chosen over option 1. On the InternalRate OfReturn worksheet, in cell B13 calculate the internal rate of return for investment option 1. In cell E12 calculate the internal rate of return for investment option 2. 11 On the IrregularlRR worksheet, in cell B17 calculate the irregular internal rate of return. This investment will have an internal rate of return of 5.61% and does not meet the required rate of return of 9.75%. 12 A straight-line depreciation table indicates the cost of the asset, salvage value, and useful life of the assets. On the Depreciation worksheet, in cell B8 calculate the straight-line depreciation to begin creating the straight-line depreciation table. Copy the formula down to cell B12. 13 In cell C8, reference cell B8 to reference the accumulated depreciation for the first year. 14 In cell C9, calculate the accumulated depreciation for the second year. Copy the formula down to C12. 15 In cell D8, calculate the book value at the end of the first year and lock the cell reference to B3. Copy the formula down to D12. The book value at the end of year five will be the same as the estimated salvage value. 16 The equipment was placed into service at the beginning of May of the first year. Therefore, the period for the first year will be eight-May through December is eight months. On the Depreciation 2 worksheet, in cell B8 calculat the declining balance depreciation and lock the cell references to cells B3:B5 to begin creating the declining balance depreciation table. 17 The equipment will be used from January to December for the remaining four time periods. In cell B9, paste the function from B8 and modify the function to calculate the depreciation for the second year. Copy the formula down to cell B12. 18 On the Depreciation 3 worksheet, in cell B8 calculate the double declining balance depreciation and lock the cell references to cells B3:B5 to begin creating the double declining balance depreciation table. Copy the formula down to cell B12. Notice that you would be able to deduct higher depreciation on your taxes. However, your net book value at the end of the five years would be less than it would be if you used the DB function. A B $75,000.00 5.50% 5 7.75% 1 Turquoise Oasis Spa 2 Investment Analysis 3 4 Bond Details 5 Par Value 6 Coupon Rate 7 Maturity (in years) 8 Coupon Payment (annually) 9 Yield to Maturity (YTM) 10 Present Value (PV) 11 12 Investment Details 13 Present Value 14 Term (in years) 15 Interest Rate (compounded quarterly) 16 Total Contribution 17 Future Value 18 Total Earned 19 $350,000.00 5 6% 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37_ PresentAndFuture Value NetPresentValue Irregul Turquoise Oasis Spa Net Present Value Analysis 00 Own Discount Rate Total Initial Investment Input Values 4.57% $100,000.00 Cash Flows Initial Investment $58,450.00 $46,612.00 $34,003.00 9 10 Year 0 11 Year 1 12 Year 2 13 Year 3 14 15 Net Present Value 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 PresentAndFutureValue NetPresentValue IrregularNPV Intern AI J Turquoise Oasis Spa B D 1 Turquoise Oasis Spa Irregular Net Present Value Analysis 2 3 10% vou +wn 4 Required Rate of Return (RRR) 5 Dates 6 7 8 9 10 11 12 13 4/1/19 8/1/19 11/1/19 2/1/20 3/1/20 4/1/20 7/1/20 9/1/20 11/1/20 2/1/21 3/1/21 9/1/21 Cash Flows $90,000.00 Initial Investment $8,500.00 $7,500.00 $6,500.00 $4,000.00 $5,000.00 $8,750.00 $9,400.00 $10,000.00 $14,000.00 $14,340.00 $12,900.00 14 15 16 17 18 19 20 Irregular Net Present Value 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 PresentAndFutureValue NetPresentValue IrregularNPV JL A B D E F G H Turquoise Oasis Spa Internal Rate of Return Analysis Option 1 Cash Flows 5 Year o ($125,000) Year 1 $40,000 Year 2 $40,000 Year 3 $40,000 e Year 4 $40,000 O 1 2 Internal Rate of 3 Return (IRR) 4 Option 2 Cash Flows Year o ($125,000) Year 1 $65,000 Year 2 $35,000 Year 3 $20,000 Year 4 $15,000 Year 5 $15,000 Year 6 $10,000 Internal Rate of Return (IRR) 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6. PresentAndFutureValue NetPresentValue IrregularNPV InternalRate OfReturn A B D E F 1 Turquoise Oasis Spa 2 Irregular Internal Rate of Return Analysis 3 4 Required Rate of Return (RRR) 9.75% 5 6 Dates Cash Flows 7 3/31/20 $ 130,000.00 Initial Investment 8 6/30/20 $18,000.00 9 9/30/20 $25,000.00 10 10/31/20 $45,000.00 11 3/1/21 $17,000.00 12 5/31/21 $12,500.00 13 9/30/21 $10,000.00 14 12/31/21 $5,000.00 15 4/30/22 $3,000.00 16 17 Irregular Internal Rate of Return 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 NetPresentValue IrregularNPV InternalRate OfReturn IrregularlRR A B D E F G 1 2 3 Cost of asset 4 Salvage value 5 Useful life 6 Turquoise Oasis Spa Asset Straight-Line Depreciation Table $40,000.00 $3,871.00 5 Accumulated Depreciation depreciation at expense for year year end Book value at year end End of Year 1 2 3 4 5 Net Book Value 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 NetPresentValue IrregularNPV InternalRate OfReturn IrregularlRR Depreciation Turquoise Oasis Spa Asset Declining Balance Depreciation Table Cost of asset $27,500 Salvage value $2,097 Useful life 5 Accumulated depreciation at Depreciation expense for year year end End of Year 1 2 3 nmn Book value at year end $27,500.00 $27,500.00 $27,500.00 $27,500.00 $27,500.00 Net Book Value $0.00 $0.00 $0.00 $0.00 $0.00 4 5 IrregularlRR Depreciation Depreciation2 Depreciation3 A B D E F ou - wn 1 Turquoise Oasis Spa 2 Asset Double Declining Balance Depreciation Table 3 Cost of asset $27,500 4 Salvage value $2,097 5 Useful life 5 6 Book value at End of Year year end 7 8 1 Accumulated Depreciation depreciation at expense for year year end $0.00 $0.00 $0.00 $0.00 $0.00 3 w N $27,500.00 $27,500.00 $27,500.00 $27,500.00 $27,500.00 Net Book Value 4 5 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 IrregularlRR Depreciation Depreciation2 Depreciation3 Description Create Date By Whom mm/dd/yyyy Your Office Mod. Date By Whom Workbook Name e06ch11 grader_pc_pt1_Financial Analysis Last Version Backup Name Mod. Description 2 3 Create Date Sheet Name Creator Purpose IrregularlRR Depreciation Depreciation2 Depreciation3 Documentation + 1 Start Excel. Download and open the file named Excel_Ch11_Prepare_Financial_Analysis_PartB.xlsx. Grader has automatically added your last name to the start of the filename. Save the file to the location where you are storing your files. 2 In addition to analyzing possible expansion finance options from a bank, managers at the Turquoise Oasis Spa would like to analyze some possible investment opportunities to help finance the expansion and generate revenue in the long term. On the PresentAndFutureValue worksheet, in cell B8 calculate the Coupon Payment (annually) of the investment from the bank's perspective. The first step in determining whether or not this is a worthy investment is to calculate the present value of the investment. In cell B10, calculate the present value of the investment from the bank's perspective. 3 4 In cell B16, reference the present value cell, as the total investment will be a one time, lump- sum investment The future value function, or FV function, is used to calculate the value of an investment with a fixed interest rate and term, as well as to calculate periodic payments over a specific period of time. In cell B17, calculate the future value of the investment from the bank's perspective. 5 6 In cell B18, enter a formula to calculate the total amount earned by subtracting the total contribution from the future value. 7 8 On the NetPresentValue worksheet, in cell B10 insert a cell reference to reference the initial investment amount, which will be the cash outflow for year 0. The net present value function, or NPV function, is used to determine the value of an investment by analyzing a series of future incoming and outgoing cash flows that are expected to occur over the life of the investment. In cell B15, calculate the net present value for the investment from the bank's perspective. Add the value of the initial investment to the result. 9 The irregular net present value function, or XNPV function, determines the value of an investment or business by analyzing an irregular time series of incoming and outgoing cash flows. On the IrregularNPV worksheet, in cell B20 calculate the net present value of the investment based on the irregular cash flows. 10 Both investment options will have a higher internal rate of return than the risk-free investment option of 10.50%. However, option 2 has the higher IRR and should be chosen over option 1. On the InternalRate OfReturn worksheet, in cell B13 calculate the internal rate of return for investment option 1. In cell E12 calculate the internal rate of return for investment option 2. 11 On the IrregularlRR worksheet, in cell B17 calculate the irregular internal rate of return. This investment will have an internal rate of return of 5.61% and does not meet the required rate of return of 9.75%. 12 A straight-line depreciation table indicates the cost of the asset, salvage value, and useful life of the assets. On the Depreciation worksheet, in cell B8 calculate the straight-line depreciation to begin creating the straight-line depreciation table. Copy the formula down to cell B12. 13 In cell C8, reference cell B8 to reference the accumulated depreciation for the first year. 14 In cell C9, calculate the accumulated depreciation for the second year. Copy the formula down to C12. 15 In cell D8, calculate the book value at the end of the first year and lock the cell reference to B3. Copy the formula down to D12. The book value at the end of year five will be the same as the estimated salvage value. 16 The equipment was placed into service at the beginning of May of the first year. Therefore, the period for the first year will be eight-May through December is eight months. On the Depreciation 2 worksheet, in cell B8 calculat the declining balance depreciation and lock the cell references to cells B3:B5 to begin creating the declining balance depreciation table. 17 The equipment will be used from January to December for the remaining four time periods. In cell B9, paste the function from B8 and modify the function to calculate the depreciation for the second year. Copy the formula down to cell B12. 18 On the Depreciation 3 worksheet, in cell B8 calculate the double declining balance depreciation and lock the cell references to cells B3:B5 to begin creating the double declining balance depreciation table. Copy the formula down to cell B12. Notice that you would be able to deduct higher depreciation on your taxes. However, your net book value at the end of the five years would be less than it would be if you used the DB function