Entree Plate son #: 32997 ser Nane: CFAR Res 2 Cashier Name: Suat Reynolds PE 2029 btotal 2 Safe 00 RXXXXXX **** Module 9 Exploring Financial Tools and Functions | Excel EX 9-63 16. After the term of the lease is over, the company will return the sheet metal press and receive the security deposit back. In cell G41, enter the value of the security deposit from cell B15 as a posi- tive cash flow. 17. To assess the time value of money, Jim will assume a 5.25% discount rate. Enter this value into cell B19. To express this as a monthly percentage, in cell B20, enter a formula to divide the value of cell B19 by 12. 18. Calculate the net present value of buying the sheet metal press. In cell B21, add the initial investment in cell F4 to the present value of owning and then reselling the equipment after three years. To determine the present value of owning the equipment, use the NPV function with the monthly discount rate in cell B20 as the rate of return and the values in the F5:F41 as the cash flows for owning and using the equipment. 19. Calculate the cost leasing the sheet metal press in current dollars. In cell B22, calculate the net present value by adding the initial cost of the security deposit in cell G4 to the value returned by the NPV function for the discount rate in cell B20 and the cash flows in the range G5:G41. 20. Determine whether buying is less expensive than leasing. In cell B23, enter an IF function that displays the text BUY if the net present value of buying the equipment is greater than the net present value of leasing the equipment; otherwise display the text LEASE. 21. Save the workbook. 22. The decision to buy versus lease is closely related to the time value of money. If the discount rate is high, then Eagle Manufacturing will be selling the sheet metal press in three years for dollars of sub- stantially reduced value. Redo your analysis by changing the discount rate in cell B19 to 6.50%. 23. Save the workbook as NP_EX_9_Eagle2 in the location specified by your instructor, and then close it. Case Problem 2 dod for this case Problem: NP_EX_9-4.xlsx APPLY metal press. range Case Problem 1 Data File needed for this Case Problem: NP_EX_9-3.xlsx Eagle Manufacturing Jim Helt is a financial manager at Eagle Manufacturing, a steel manufac- turer specializing in construction projects ranging from support structures used in large buildings and highways to decorative railings for new homes and apartments. For each piece of industrial equipment the company needs, Jim must evaluate whether it is better to purchase the equipment or to lease the equipment for several years before replacing it with newer models. Currently, Jim must choose between buying a large-capacity hydraulic steel metal press for $35,000 or leasing that machinery for three years for $500 a month. You'll use the Excel financial function to compare the cost of buying versus leasing. Complete the following: 1. Open the NP_EX_9-3.xlsx workbook located in the Excel9 > Case1 folder included with your Data Files. Save the workbook as NP_EX_9_Eagle in the location specified by your instructor 2. In the Documentation worksheet, enter your name and the date. 3. In the Buy vs. Lease worksheet, in cell B4, enter $35,000 as the purchase price of the sheet 4. The sheet metal press has a salvage value of $15,000 after 120 months, or 10 years. In the B5:36, enter the salvage value and the salvage time (in months). 5. If the company does opt to buy the sheet metal press, the company will purchase a service maintenance contract that will cover maintenance costs for the next three years. In cell B9, enter $950 as the cost of this contract. 6. If the company buys the sheet metal press it will also have to pay sales tax on the purchase. In cell B10, enter 3.25% as the sales tax rate. In cell B11, enter a formula to calculate the amount of sales tax by multiplying the sales tax rate by the current price of the equipment. 7. If the company decides to buy the sheet metal press, Jim believes that it can be sold after three years for 90% of its depreciated value. In cell B12, enter 90% as the resale percentage. 8. If the company decides to lease this equipment, Eagle Manufacturing will have to pay a $2,500 security deposit and a monthly payment of $500. Enter these values in the range B15:B16. 9. The table in columns D through G will be used to track the monthly cost of buying versus leasing over the next 36 months. In cell E4, enter a formula that shows the current value of the equipment entered in cell B4. 10. Calculate the value of the equipment as it depreciates each year as follows: a. In cell E5, calculate the difference between the value in cell E4 and the depreciation of the sheet metal press in the first month of use using the DB function. Use cells $B$4, $B$5, and $B$6 for the Cost, Salvage, and Life arguments and use cell D5 for the Period argument. b. Use AutoFill to fill the formula in cell E5 through the range E6:E40. Fill the formulas without formatting. 11. In cell F4, enter as a negative cash flow the initial cost of purchasing the sheet metal press by adding the cost of the equipment in cell B4, the cost of the service contract in cell B9, and the cost of the sales tax in cell B11. 12. For Month 1 through Month 36, the company will not have to make any payments on the sheet metal press. Enter 0 as the cash flow values in the range F5:F40. 13. After Month 36, the company will sell sheet metal price at a reduced value. In cell F41, enter as a positive cash flow the final depreciated value of the equipment in cell E40 multiplied by the resale percentage in cell B12. 14. If the company chooses to lease the sheet metal press it must first pay the security deposit. In cell G4, enter as a negative cash flow the cost of the security deposit on the digital equipment entered in cell B15. 15. Every month Eagle Manufacturing must pay the leasing fee. In the range G5:G40, enter as a negative cash flow the monthly lease payment from cell B16. Entree Plate son #: 32997 ser Nane: CFAR Res 2 Cashier Name: Suat Reynolds PE 2029 btotal 2 Safe 00 RXXXXXX **** Module 9 Exploring Financial Tools and Functions | Excel EX 9-63 16. After the term of the lease is over, the company will return the sheet metal press and receive the security deposit back. In cell G41, enter the value of the security deposit from cell B15 as a posi- tive cash flow. 17. To assess the time value of money, Jim will assume a 5.25% discount rate. Enter this value into cell B19. To express this as a monthly percentage, in cell B20, enter a formula to divide the value of cell B19 by 12. 18. Calculate the net present value of buying the sheet metal press. In cell B21, add the initial investment in cell F4 to the present value of owning and then reselling the equipment after three years. To determine the present value of owning the equipment, use the NPV function with the monthly discount rate in cell B20 as the rate of return and the values in the F5:F41 as the cash flows for owning and using the equipment. 19. Calculate the cost leasing the sheet metal press in current dollars. In cell B22, calculate the net present value by adding the initial cost of the security deposit in cell G4 to the value returned by the NPV function for the discount rate in cell B20 and the cash flows in the range G5:G41. 20. Determine whether buying is less expensive than leasing. In cell B23, enter an IF function that displays the text BUY if the net present value of buying the equipment is greater than the net present value of leasing the equipment; otherwise display the text LEASE. 21. Save the workbook. 22. The decision to buy versus lease is closely related to the time value of money. If the discount rate is high, then Eagle Manufacturing will be selling the sheet metal press in three years for dollars of sub- stantially reduced value. Redo your analysis by changing the discount rate in cell B19 to 6.50%. 23. Save the workbook as NP_EX_9_Eagle2 in the location specified by your instructor, and then close it. Case Problem 2 dod for this case Problem: NP_EX_9-4.xlsx APPLY metal press. range Case Problem 1 Data File needed for this Case Problem: NP_EX_9-3.xlsx Eagle Manufacturing Jim Helt is a financial manager at Eagle Manufacturing, a steel manufac- turer specializing in construction projects ranging from support structures used in large buildings and highways to decorative railings for new homes and apartments. For each piece of industrial equipment the company needs, Jim must evaluate whether it is better to purchase the equipment or to lease the equipment for several years before replacing it with newer models. Currently, Jim must choose between buying a large-capacity hydraulic steel metal press for $35,000 or leasing that machinery for three years for $500 a month. You'll use the Excel financial function to compare the cost of buying versus leasing. Complete the following: 1. Open the NP_EX_9-3.xlsx workbook located in the Excel9 > Case1 folder included with your Data Files. Save the workbook as NP_EX_9_Eagle in the location specified by your instructor 2. In the Documentation worksheet, enter your name and the date. 3. In the Buy vs. Lease worksheet, in cell B4, enter $35,000 as the purchase price of the sheet 4. The sheet metal press has a salvage value of $15,000 after 120 months, or 10 years. In the B5:36, enter the salvage value and the salvage time (in months). 5. If the company does opt to buy the sheet metal press, the company will purchase a service maintenance contract that will cover maintenance costs for the next three years. In cell B9, enter $950 as the cost of this contract. 6. If the company buys the sheet metal press it will also have to pay sales tax on the purchase. In cell B10, enter 3.25% as the sales tax rate. In cell B11, enter a formula to calculate the amount of sales tax by multiplying the sales tax rate by the current price of the equipment. 7. If the company decides to buy the sheet metal press, Jim believes that it can be sold after three years for 90% of its depreciated value. In cell B12, enter 90% as the resale percentage. 8. If the company decides to lease this equipment, Eagle Manufacturing will have to pay a $2,500 security deposit and a monthly payment of $500. Enter these values in the range B15:B16. 9. The table in columns D through G will be used to track the monthly cost of buying versus leasing over the next 36 months. In cell E4, enter a formula that shows the current value of the equipment entered in cell B4. 10. Calculate the value of the equipment as it depreciates each year as follows: a. In cell E5, calculate the difference between the value in cell E4 and the depreciation of the sheet metal press in the first month of use using the DB function. Use cells $B$4, $B$5, and $B$6 for the Cost, Salvage, and Life arguments and use cell D5 for the Period argument. b. Use AutoFill to fill the formula in cell E5 through the range E6:E40. Fill the formulas without formatting. 11. In cell F4, enter as a negative cash flow the initial cost of purchasing the sheet metal press by adding the cost of the equipment in cell B4, the cost of the service contract in cell B9, and the cost of the sales tax in cell B11. 12. For Month 1 through Month 36, the company will not have to make any payments on the sheet metal press. Enter 0 as the cash flow values in the range F5:F40. 13. After Month 36, the company will sell sheet metal price at a reduced value. In cell F41, enter as a positive cash flow the final depreciated value of the equipment in cell E40 multiplied by the resale percentage in cell B12. 14. If the company chooses to lease the sheet metal press it must first pay the security deposit. In cell G4, enter as a negative cash flow the cost of the security deposit on the digital equipment entered in cell B15. 15. Every month Eagle Manufacturing must pay the leasing fee. In the range G5:G40, enter as a negative cash flow the monthly lease payment from cell B16