NEED AS SOON AS POSSIBLE PLZ!!

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 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. Created On: 11/5/2021 YO19_ExcelCh11 Prepare - Financial Analysi AccessibilityInvestigate o Focus oop DD . 21 AaBbCcDdEe AaBbceDdEe AaBbCcD AaBbCcDdE Heading 1 Heading 2 v Emphasis v Normal Nor Step Instructions 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 legularnex, 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 lateralRate OfRetum 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 IrregularBR 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 worksheet, in cell B8 calculate 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 worksheet, in cell BB 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 Delete Paste BTU Merge & Center $ % 9 Conditional Format Cell Formatting as Table Styles Format BS K L M N Turquoise Oasis Spa Investment Analysis 575,000.00 5.50 7.75 Bond Details 5 Par Value 6 Coupoo Rate 7 Maturity nya Coupon Payment annually Viesto Maturity (YTMO 10 Punt Value 11 12 Ismet Details 11 Present Value 14 Termin year) 15 merte compounded quarterly 16 Total Contbution 17 Future Value 18 Total Curned 19 20 21 $150,000.00 5 23 24 25 26 27 28 29 10 31 32 33 34 35 36 17 31 39 40 41 42 Depreciation Decond Internal Return Deprecatore Dec be INPY PresentAndFutValue Net Present Value Ready Paste V Marge & Center $ % ) Egy Conditional Format Cell Formatting as Table Styles Al x fx Turquoise Oasis Spa A 8 D L K M 1 2 Turquoise Oasis Spa Net Present Value Analysis Discount Rate Total Initial Investment Input Values 4.57% $100,000.00 8 Cash Flows Initial Investment $58,450.00 $46.612.00 $34,000.00 9 10 Year 11 Year 1 12 Year 2 Year 14 15 Net Present Value 16 17 18 19 20 21 22 23 24 25 26 27 2B 29 30 31 32 33 34 35 36 37 40 41 42 Presente NetPresentValue Irregular PV Rate Offer frog Depreciation Deprecation Deproton Ready Paste BIVE MI Merge & Center $ %) Conditional Format Cell Formatting as Table Styles A1 fie Turquoise Oasis Spa C D L M Turquoise Oasis Spa Irregular Net Present Value Analysis MAN Required Rate of Return IRRR) TOX Cash Flows $90,000.00 Initial restment $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.10.00 $12.900.00 Dates 7 4/1/19 8: 8/1/19 9 11/1/19 10 2/1/20 11 3/1/20 12 4/1/20 13 7/1/20 14 9/1/20 15 11/1/20 16 2/1221 17 1/1/21 18 9/1/21 19 20 Irregular Net Present Value 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 19 40 41 42 NorPresente Irregular PV intamattotum Infogul Detroit Detector Derudition Presentada 17 Ready Merge & Center $% 28-98 Conditional Format Cel Formatting as Table Styles A1 x A fx Turquoise Oasis Spa D Turquoise Oasis Spa Internal Rate of Return Analysis 1 Option 1 Cash Flows S Year 5125.000 G Year 1 $40,000 Year 2 $40,000 8 Yew 3 $40,000 9 Year 4 $40,000 30 11 12 Internal Rute of 13 Return IRRI 14 15 16 Option 2 Cash Flows Year 15125,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 ORRI BNNKUNNSBRUCE 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 14 35 36 37 38 40 41 Nel Present Value Irregular InternalFateorReturn Irregular Deprecated Declaim Depreciation Presentandfuture Value 12 Ready ili = EE Merge & Center $ % ) Conditional Format Cell Formatting as Table Styles Al fx Turquoise Oasis Spa C D E F G H L M N 1 Turquoise Oasis Spa Irregular internal Rate of Return Analysis Required Rate of Return 9.75% Dates Cash Flows 7 3/31/20 $130,000.00 initial investment 8 6/30/20 $18,000.00 9 9/30/20 525,000.00 10 30/11/20 $45,000.00 11 371221 $17.000.00 12 5/3123 $12,500.00 13 9/30/21 $10,000.00 3273121 $5,000.00 15 4/30/22 $3.000.00 16 17 replar internal Rate of Return 18 19 20 21 22 23 24 25 26 27 29 30 31 12 33 36 35 36 37 38 39 40 41 42 Detection NPV Internet Irregularis Depe Deprecatione NePrenent ProtondFuture T Ready 2 Delete Paste BIU Merge & Center $ % ) 8:28 Conditional Format Cell Formatting as Table Styles Form xfx Turquoise Oasis Spa C D L N A P 1 Turquoise Oasis Spa 2 Asset Straight-Line Depreciation Table 3 Cost of $40,000.00 Salvage value 53,871,00 Useful life Accumulated Depreciation depreciation at Bock value expens for ye Trend TEEM End of Yew 2 3 9 10 11 12 5 Net Rock Vue 14 15 16 17 15 39 21 22 23 24 25 27 28 29 30 31 32 33 34 35 36 17 38 40 Depreciation De Dry tregu NPV Interim Netrenie Prendre Ready LE Paste %) Cell Merge & Center 03-38 Format Formatting as Table Styles B3 vfx 27500 K L M A Turquoise Oasis Spa 2 Asset Declining balance Depreciation Table 3 Cost of $27.500 4 Salyvale $2.097 Uwe End of Tear 1 2 3 4 5 Accumulated Depreciation depreciation Book value pe for end Year End $0.00 $22,500.00 30.00 $27500.00 $0.00 $27.500.00 50.00 $27,500.00 $0.00 $27.500,00 Net Book Value S 10 11 12 13 34 15 16 17 18 19 20 21 za 23 24 25 26 27 28 29 30 31 33 35 36 37 11 39 40 reg stratorum Depreciation Ingur Presentando Natywne Depreciation Average: $14799 Deprecation Count: 2 Sum 52 Ready G H Al 1x Turquoise Oasis Spa A C D 1 Turquoise Oasis Spa 2 Asset Double Declining Balance Depreciation Table Cost of asset 527,500 Salvage value $2,097 Uulite 5 Accumulated Depreciation depreciation Book values expense for you year end Varend $0.00 $27.500.00 $0.00 527,500.00 $0.00 $27,500.00 $0.00 $27500.00 $0.00 $27.500.00 Net Book Value End of Year 1 5 2 10 3 11 4 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 25 30 31 32 33 34 35 36 37 31 19 internetu Depreciation Decond Depreciation negu Net Presente Preserende IT Ready 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 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. Created On: 11/5/2021 YO19_ExcelCh11 Prepare - Financial Analysi AccessibilityInvestigate o Focus oop DD . 21 AaBbCcDdEe AaBbceDdEe AaBbCcD AaBbCcDdE Heading 1 Heading 2 v Emphasis v Normal Nor Step Instructions 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 legularnex, 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 lateralRate OfRetum 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 IrregularBR 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 worksheet, in cell B8 calculate 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 worksheet, in cell BB 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 Delete Paste BTU Merge & Center $ % 9 Conditional Format Cell Formatting as Table Styles Format BS K L M N Turquoise Oasis Spa Investment Analysis 575,000.00 5.50 7.75 Bond Details 5 Par Value 6 Coupoo Rate 7 Maturity nya Coupon Payment annually Viesto Maturity (YTMO 10 Punt Value 11 12 Ismet Details 11 Present Value 14 Termin year) 15 merte compounded quarterly 16 Total Contbution 17 Future Value 18 Total Curned 19 20 21 $150,000.00 5 23 24 25 26 27 28 29 10 31 32 33 34 35 36 17 31 39 40 41 42 Depreciation Decond Internal Return Deprecatore Dec be INPY PresentAndFutValue Net Present Value Ready Paste V Marge & Center $ % ) Egy Conditional Format Cell Formatting as Table Styles Al x fx Turquoise Oasis Spa A 8 D L K M 1 2 Turquoise Oasis Spa Net Present Value Analysis Discount Rate Total Initial Investment Input Values 4.57% $100,000.00 8 Cash Flows Initial Investment $58,450.00 $46.612.00 $34,000.00 9 10 Year 11 Year 1 12 Year 2 Year 14 15 Net Present Value 16 17 18 19 20 21 22 23 24 25 26 27 2B 29 30 31 32 33 34 35 36 37 40 41 42 Presente NetPresentValue Irregular PV Rate Offer frog Depreciation Deprecation Deproton Ready Paste BIVE MI Merge & Center $ %) Conditional Format Cell Formatting as Table Styles A1 fie Turquoise Oasis Spa C D L M Turquoise Oasis Spa Irregular Net Present Value Analysis MAN Required Rate of Return IRRR) TOX Cash Flows $90,000.00 Initial restment $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.10.00 $12.900.00 Dates 7 4/1/19 8: 8/1/19 9 11/1/19 10 2/1/20 11 3/1/20 12 4/1/20 13 7/1/20 14 9/1/20 15 11/1/20 16 2/1221 17 1/1/21 18 9/1/21 19 20 Irregular Net Present Value 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 19 40 41 42 NorPresente Irregular PV intamattotum Infogul Detroit Detector Derudition Presentada 17 Ready Merge & Center $% 28-98 Conditional Format Cel Formatting as Table Styles A1 x A fx Turquoise Oasis Spa D Turquoise Oasis Spa Internal Rate of Return Analysis 1 Option 1 Cash Flows S Year 5125.000 G Year 1 $40,000 Year 2 $40,000 8 Yew 3 $40,000 9 Year 4 $40,000 30 11 12 Internal Rute of 13 Return IRRI 14 15 16 Option 2 Cash Flows Year 15125,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 ORRI BNNKUNNSBRUCE 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 14 35 36 37 38 40 41 Nel Present Value Irregular InternalFateorReturn Irregular Deprecated Declaim Depreciation Presentandfuture Value 12 Ready ili = EE Merge & Center $ % ) Conditional Format Cell Formatting as Table Styles Al fx Turquoise Oasis Spa C D E F G H L M N 1 Turquoise Oasis Spa Irregular internal Rate of Return Analysis Required Rate of Return 9.75% Dates Cash Flows 7 3/31/20 $130,000.00 initial investment 8 6/30/20 $18,000.00 9 9/30/20 525,000.00 10 30/11/20 $45,000.00 11 371221 $17.000.00 12 5/3123 $12,500.00 13 9/30/21 $10,000.00 3273121 $5,000.00 15 4/30/22 $3.000.00 16 17 replar internal Rate of Return 18 19 20 21 22 23 24 25 26 27 29 30 31 12 33 36 35 36 37 38 39 40 41 42 Detection NPV Internet Irregularis Depe Deprecatione NePrenent ProtondFuture T Ready 2 Delete Paste BIU Merge & Center $ % ) 8:28 Conditional Format Cell Formatting as Table Styles Form xfx Turquoise Oasis Spa C D L N A P 1 Turquoise Oasis Spa 2 Asset Straight-Line Depreciation Table 3 Cost of $40,000.00 Salvage value 53,871,00 Useful life Accumulated Depreciation depreciation at Bock value expens for ye Trend TEEM End of Yew 2 3 9 10 11 12 5 Net Rock Vue 14 15 16 17 15 39 21 22 23 24 25 27 28 29 30 31 32 33 34 35 36 17 38 40 Depreciation De Dry tregu NPV Interim Netrenie Prendre Ready LE Paste %) Cell Merge & Center 03-38 Format Formatting as Table Styles B3 vfx 27500 K L M A Turquoise Oasis Spa 2 Asset Declining balance Depreciation Table 3 Cost of $27.500 4 Salyvale $2.097 Uwe End of Tear 1 2 3 4 5 Accumulated Depreciation depreciation Book value pe for end Year End $0.00 $22,500.00 30.00 $27500.00 $0.00 $27.500.00 50.00 $27,500.00 $0.00 $27.500,00 Net Book Value S 10 11 12 13 34 15 16 17 18 19 20 21 za 23 24 25 26 27 28 29 30 31 33 35 36 37 11 39 40 reg stratorum Depreciation Ingur Presentando Natywne Depreciation Average: $14799 Deprecation Count: 2 Sum 52 Ready G H Al 1x Turquoise Oasis Spa A C D 1 Turquoise Oasis Spa 2 Asset Double Declining Balance Depreciation Table Cost of asset 527,500 Salvage value $2,097 Uulite 5 Accumulated Depreciation depreciation Book values expense for you year end Varend $0.00 $27.500.00 $0.00 527,500.00 $0.00 $27,500.00 $0.00 $27500.00 $0.00 $27.500.00 Net Book Value End of Year 1 5 2 10 3 11 4 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 25 30 31 32 33 34 35 36 37 31 19 internetu Depreciation Decond Depreciation negu Net Presente Preserende IT Ready