CountOstrich1917
PLEASE I NEED HELP WITH THE FORMULA IN STEP 19, THE FORMULA SEEMS…
PLEASE I NEED HELP WITH THE FORMULA IN STEP 19, THE FORMULA SEEMS TO NOT WORKING.
Eagle Manufacturing Jim Helt is a financial manager at Eagle Manufacturing, a steel manufacturer 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 metal press.
4. The sheet metal press has a salvage value of $15,000 after 120 months, or 10 years. In the range B5:B6, 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.
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 positive 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 substantially 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.
Step-by-step solution
Show all steps
85% (20 ratings) for this solution
Step 1/24
Analyzing Data
MS Excel used different formulas and functions for maintaining a large amount of data easily and also created worksheets. Many organizations used MS Excel for data analysis because it is very clear and explained information very well by using multiple tools and graphs.
Step 2/24
Using excel financial functions
1.
In the first step, open the NP_EX_9-3.xlsx workbook from the given source files as shown below:
Then save the excel file as NP_EX_9_Eagle in any preferred location as shown below:
Step 3/24
2.
In the second step, open the documentation sheet, and then in cell B3 enter the name, and in the cell, B4 enter the date shown below:
Step 4/24
3.
In this step, go to the Buy vs. Lease worksheet, and then go to cell B4 and enter $35,000 as shown below:
Step 5/24
4.
In this step, go to cell B5 and enter $15,000 and then go to cell B6 and enter 120 as shown below:
Step 6/24
5.
In this step, go to cell B9 and enter $950 which is the cost of the maintenance contract as shown below:
Step 7/24
6.
In this step, go to cell B9 and enter 3.25% which is the sales tax rate as shown below:
Then go to cell B11 and enter formula =B4*B10 which helps to determine the amount of sales tax as shown below:
Step 8/24
7.
In this step, go to cell B12 and enter 90% which is the depreciated value of the sheet metal press after 3 years as shown below:
Step 9/24
8.
In this step, go to cell B15 and enter $2,500 and then go to cell B16 and enter $500 as shown below:
Step 10/24
9.
In this step, go to cell E4 and enter formula =B4 which helps to determine the current value of the equipment as shown below:
Step 11/24
10.
In this step, the following calculations are done to determine the value of the equipment as shown below:
a. In part a, go to cell E5 and enter function =E4-DB($B$4,$B$5,$B$6,D5) which helps to determine the depreciation of metal press in the first month as shown below:
b. Then use the AutoFill command till cell E40 as shown below:
Step 12/24
11.
In this step, go to cell F4 and enter formula =B4+B9+B11 which helps to determine the initial cost of purchasing a sheet metal press as shown below:
Step 13/24
12.
In this step, enter 0 in the range F5:F40 because the company does not charge any payments as shown below:
Step 14/24
13.
In this step, go to cell F41 and enter formula =E40*B12 which helps to determine the reduced cost of sheet metal press as shown below:
Step 15/24
14.
In this step, go to cell G4 and enter formula =B15 which helps to determine the security deposit of the sheet metal press as shown below:
Step 16/24
15.
In this step, select range G5:G40 and enter formula =B16 which helps to determine the leasing fee of the manufacturing as shown below:
Step 17/24
16.
In this step, go to cell G41 and enter formula =B15 which helps to determine the amount of received security deposit as shown below:
Step 18/24
17.
In this step, go to cell B19 and enter 5.25% which discount rate assumed by Jim as shown below:
Then go to cell B20 and enter formula =B19/12 which helps to determine the monthly percentage as shown below:
Step 19/24
18.
In this step, go to cell B21 and enter function = F4+NPV(B20,F5:F41) which helps to determine the net present value of purchasing the sheet metal press as shown below:
Step 20/24
19.
In this step, go to cell B22 and enter function =G4+NPV(B20,G5:G41) which helps to determine the cost of leasing the sheet metal press as shown below:
Step 21/24
20.
In this step, go to cell B23 and enter function =IF(B21>B22,”BUY”,”LEASE”) which helps to determine that the purchasing is less expensive than leasing as shown below:
Step 22/24
21.
To save the workbook press the Ctrl+S key which helps to save the excel file as shown below:
Step 23/24
22.
In this step, go to cell B19 and enter 6.50% and then review the workbook. Due to the increment in the discount rate, the price of sheet metal press is reduced as shown below:
Step 24/24
23.
To save the workbook go to the File tab and then click on Save as command and then choose the preferred location and then enter the file name NP_EX_9_Eagle2 as shown below:
Then click the ok button, to save the file as shown below:
