need input ASAP

8 0 original value of $325 e to sell the the resale value for the computer system after 36 months that will cause the ne buying to exactly match the net present value of leasing 17. Save the workbook as Digital Frames 3, and then close it puter system for $12,000. Use Goal Seek to determine t present value of f Case Problem 2 D. :a File, needed for this Case Problem: College.xlsx College Savers Eric Hoiland is a financial consultant at College Savers who works whyoung children to help them save for the rising c stofcollege. Currently, he s helping laine and Lucia Juarez develop a savings plan for their daughter, Adelita, who is 9. The couple's goa save enough money over the next 10 years to fund Adelita's four years of college. They plan annual deposits into their savings fund starting immediately and continue them through Adelita's rs. When Adelita begins college, they'll withdraw money at the beginnin er much of the cost of tuition, room, and board. They have several options deposit. Eric has asked you to help set up an Excel workbook to determine whether different savings ill cover the cost of Adelita's college education. Complete the following: with families to cov for the amount to Open the College workbook located in the Excel9 Case2 folder included with your Data Files and then save the workbook as College Savers in the location specified by your instructor In the Documentation worksheet, enter your name and the current date. In the Savings Plan worksheet, enter Adelita Juarez as the student name in cell B4, her age of 9 in cell B5, her years until starting college as 10 in cell B6, and her parents Jaime and Lucia Juarez in cell B7 4. Based on current data, the one-year cost of college is $21,000. Enter this value in cell B10 ts are projected to increase at an annual rate of 6.25 percent. Enter this value in cell B11 The savings portolio Eric devised has a historic rate of return of 4.70 percent. Enter this value in cell B15 7. Adelita's parents plan to make an initial investment of $18,000 into the fund. Enter this value positive cash flow in cell B16. 8. After their initial investment, they plan to make a deposit of $10,000 every year on December 1 Enter this positive cash flow value in cell B17 9. Adelita's grandmother has pledged to make a contribution of $150 every year on Adelit birthday July 17) and on Christmas. Enter this positive cash flow value in cell B18 10. In the range H5:H45, enter the deposits made to the savings fund based on the dates specified in the previous step. Use cell references to the values you entered in column B of the worksheet. The initial investment occurs on March 1 of Year 0. Note that Adelita's parents and her randmother will continue to make contributions even as Adelita is attending college 11. Adelita's grandmother plans to make a special gift of $750 on Adelita's 18th birthday. Enter this value in cell H30 as a positive cash flow in place of the u Explore 12. College tuition is projected to be due on August 15 of the value of the first year of college under the inflation ra cell E33, and the current one-year usual $150 contribution. te suggested in cell B11, the year value in cost in cell B10. The value should appear,as a negative cash flow. x,592 Excel I Tutorial 9 Exploring Financial Tools and Functions 13. Repeat Step 12 for cells H38, H42, and H46 to calculate the future cost of Adelita's junior, and senior years. i4. In the range 15:146, calculate the net cash flow of the deposits to the savings fund and the o icat t there might not be enough money for college but that this analysis does not take into account the withdrawals to pay for Adelita's education. Note that the final cash flow is negative, ind interest that would be earned by the savings fund. 15. In cell 812, calculate the total cost of four years of college by adding the values you calculated in cells H34, H38, H42, and H46. 16. In cell B19, use a SUMIF function to calculate the sum of positive cash flows in the range H5:H46. This will represent the total deposits made by the family. Explore 17. In cell B22, calculate the net present value of the proposed savings plan in curen dollars. Use the XNPV function with the rate of return in cell B15, the cash flow values in the range H5:H46, and the date values in the range G5:G46. 18. In cell B23, use an IF function to display YES if the net present value is greater than or equal o (meaning that the savings plan will cover college costs) and NO if otherwise. 19. Appis conditional formatting to the value in cell 823 to display YES values in dark green on a light gieen ba kground and NO values in dark red on a light red background 2G. Save the workbook 21. Use Goal Seak to determi.ie the size of the initia: investment that will result in a net present value of O for the savings plan (covering the cost of college 22. Save the workbook as College Savers 2 23. Restore cell B16 to its original value of $18,000. Use Goal Seek to determine the size of the annual deposit that will result in a net present value of O for the savings fund. 24. Save the workbook as College Savers 3, and then close it. Case Problem 3 Data File needed for this Case Problem: Red.xlsx Red Stone Rocks Stephen Rawlings is an accounts manager at Red Stone Rocks, a quarry lo near Castle Rock, Colorado. He is currently analyzing a proposal for excavating a new stit ma ea that is believed to have a rich vein of Colorado red stone that, according to recent will be able to support a new dig for the next 20 vea u loc Site in ent estima rer 8 0 original value of $325 e to sell the the resale value for the computer system after 36 months that will cause the ne buying to exactly match the net present value of leasing 17. Save the workbook as Digital Frames 3, and then close it puter system for $12,000. Use Goal Seek to determine t present value of f Case Problem 2 D. :a File, needed for this Case Problem: College.xlsx College Savers Eric Hoiland is a financial consultant at College Savers who works whyoung children to help them save for the rising c stofcollege. Currently, he s helping laine and Lucia Juarez develop a savings plan for their daughter, Adelita, who is 9. The couple's goa save enough money over the next 10 years to fund Adelita's four years of college. They plan annual deposits into their savings fund starting immediately and continue them through Adelita's rs. When Adelita begins college, they'll withdraw money at the beginnin er much of the cost of tuition, room, and board. They have several options deposit. Eric has asked you to help set up an Excel workbook to determine whether different savings ill cover the cost of Adelita's college education. Complete the following: with families to cov for the amount to Open the College workbook located in the Excel9 Case2 folder included with your Data Files and then save the workbook as College Savers in the location specified by your instructor In the Documentation worksheet, enter your name and the current date. In the Savings Plan worksheet, enter Adelita Juarez as the student name in cell B4, her age of 9 in cell B5, her years until starting college as 10 in cell B6, and her parents Jaime and Lucia Juarez in cell B7 4. Based on current data, the one-year cost of college is $21,000. Enter this value in cell B10 ts are projected to increase at an annual rate of 6.25 percent. Enter this value in cell B11 The savings portolio Eric devised has a historic rate of return of 4.70 percent. Enter this value in cell B15 7. Adelita's parents plan to make an initial investment of $18,000 into the fund. Enter this value positive cash flow in cell B16. 8. After their initial investment, they plan to make a deposit of $10,000 every year on December 1 Enter this positive cash flow value in cell B17 9. Adelita's grandmother has pledged to make a contribution of $150 every year on Adelit birthday July 17) and on Christmas. Enter this positive cash flow value in cell B18 10. In the range H5:H45, enter the deposits made to the savings fund based on the dates specified in the previous step. Use cell references to the values you entered in column B of the worksheet. The initial investment occurs on March 1 of Year 0. Note that Adelita's parents and her randmother will continue to make contributions even as Adelita is attending college 11. Adelita's grandmother plans to make a special gift of $750 on Adelita's 18th birthday. Enter this value in cell H30 as a positive cash flow in place of the u Explore 12. College tuition is projected to be due on August 15 of the value of the first year of college under the inflation ra cell E33, and the current one-year usual $150 contribution. te suggested in cell B11, the year value in cost in cell B10. The value should appear,as a negative cash flow. x,592 Excel I Tutorial 9 Exploring Financial Tools and Functions 13. Repeat Step 12 for cells H38, H42, and H46 to calculate the future cost of Adelita's junior, and senior years. i4. In the range 15:146, calculate the net cash flow of the deposits to the savings fund and the o icat t there might not be enough money for college but that this analysis does not take into account the withdrawals to pay for Adelita's education. Note that the final cash flow is negative, ind interest that would be earned by the savings fund. 15. In cell 812, calculate the total cost of four years of college by adding the values you calculated in cells H34, H38, H42, and H46. 16. In cell B19, use a SUMIF function to calculate the sum of positive cash flows in the range H5:H46. This will represent the total deposits made by the family. Explore 17. In cell B22, calculate the net present value of the proposed savings plan in curen dollars. Use the XNPV function with the rate of return in cell B15, the cash flow values in the range H5:H46, and the date values in the range G5:G46. 18. In cell B23, use an IF function to display YES if the net present value is greater than or equal o (meaning that the savings plan will cover college costs) and NO if otherwise. 19. Appis conditional formatting to the value in cell 823 to display YES values in dark green on a light gieen ba kground and NO values in dark red on a light red background 2G. Save the workbook 21. Use Goal Seak to determi.ie the size of the initia: investment that will result in a net present value of O for the savings plan (covering the cost of college 22. Save the workbook as College Savers 2 23. Restore cell B16 to its original value of $18,000. Use Goal Seek to determine the size of the annual deposit that will result in a net present value of O for the savings fund. 24. Save the workbook as College Savers 3, and then close it. Case Problem 3 Data File needed for this Case Problem: Red.xlsx Red Stone Rocks Stephen Rawlings is an accounts manager at Red Stone Rocks, a quarry lo near Castle Rock, Colorado. He is currently analyzing a proposal for excavating a new stit ma ea that is believed to have a rich vein of Colorado red stone that, according to recent will be able to support a new dig for the next 20 vea u loc Site in ent estima rer