I really need some help on my homework assignment please. Please review the attached instructions and all that is highlighted in green is not complete. Thank you very much!

IST 204: Excel Essentials for Business Assignment 6 15 Points Download the Assignment 6 Template file from Blackboard. Rename the file as LastNameFirstNameA6 (e.g. BestMarkA6). Sarah Roth needs your help evaluating three different loan options and analyzing the stock prices of one of Hawks \"R\" Us, Inc.'s competitors. Loan Options Worksheet Sarah has the information about three separate $500,000 loans from three different banks. However, each loan is missing one critical piece of information. Loan #1: ABC Bank 5 Years Compounded Quarterly (i.e. 4 Periods/Year) 8% Annual Interest Rate Payment = ? Present Value of the Loan = $500,000 Future Value of the Loan = $0 (i.e. the Loan will be 100% Paid at the End of 5 Years) Loan #2: XYZ Bank 4 Years Compounded Semi-Annually (i.e. 2 Periods/Year) Annual Interest Rate = ? Periodic Payment = $76,000 Present Value of the Loan = $500,000 Future Value of the Loan = $0 IST 204 Assignment 6 Page 1 Loan #3: RST Bank Years = ? Compounded Quarterly Annual Interest Rate = 6.5% Payment = $40,000 Present Value of the Loan = $500,000 Future Value of the Loan = $0 Your task is to use Excel financial functions to calculate the missing piece of information for each of the three loans. When using Excel financial functions, pay close attention to the compounding period being used. The financial functions apply an interest rate per period and the payment per period to the principal over a specified number of periods. It does not matter if the compounding period is months, days, quarters, years, or some other specified period. A financial function applies the appropriate rate and payments for the specified number of times. If the RATE and NPER (i.e. number of periods) arguments and the PMT (i.e. payment) are not all consistent with the compounding period duration, the wrong values will be calculated. The payment on a loan of 8% per year for five years compounded once per year is different from the payment on that same loan amount compounded quarterly with a rate of 2% per quarter (8% divided by 4) over 20 quarters (5 years multiplied by 4 quarters). For the loan being offered by ABC Bank, the interest rate per year is 8% over a period of five years. Because the compounding period is quarterly, a rate of 2% per quarter is applied over 20 separate periods. The value that Sarah is calculating with the PMT function is the payment per quarter. Sarah will use the PMT function to calculate the payment per quarter. She will substitute the appropriate values for each argument of the PMT function. Excel's basic financial functions are RATE (solves for the interest rate per period), NPER (solves for the number of periods), PMT (solves for the value of the payment per period), PV (solves for the initial value, the amount in or out at the beginning of the financial transaction), and FV (solves for the future value, the amount in or out at the end of the financial transaction). IST 204 Assignment 6 Page 2 Step 1: Use the PMT function to calculate the periodic payment (i.e. quarterly payment) for the loan from ABC Bank. You must use cell references in your PMT function instead of typing in values. Your formula in cell E4 should be =PMT(D4/C4,B4*C4,F4,G4). You can ignore the \"type\" argument in the PMT function. D4/C4 represents the quarterly interest rate. B4*C4 represents the total number of periods. F4 represents the present value of the loan, which is the full amount of the loan (i.e. $500,000). G4 represents the future value of the loan, which is $0 because the loan will be 100% paid at the end of 5 years. Check Figure: The periodic payment for the loan from ABC Bank is ($30,578.36). The amount is displayed as a negative value because it represents a cash outflow (i.e. Hawks \"R\" Us, Inc. would pay $30,578.36 per quarter to ABC Bank). Step 2: Use the RATE function to calculate the annual interest rate for the loan from XYZ Bank. You must use cell references in your RATE function instead of typing in values. You can ignore the \"type\" and \"guess\" arguments in the RATE function. Before using the RATE function, you must type the appropriate values into cells B6, C6, E6, F6, and G6. Remember, the periodic payment amount should be negative because it represents a cash outflow. Hint: You will need to multiply the RATE function by the number of periods per year (i.e. cell C6). Check Figure: The annual interest rate for the loan from XYZ Bank is 9.13%. Step 3: Use the NPER function to calculate the duration of the loan from RST Bank in years. You must use cell references in your NPER function instead of typing in values. You can ignore the \"type\" argument in the NPER function. Hint: You will need to divide your NPER function by the number of periods per year (i.e. cell C8). Check Figure: The duration in years of the loan from RST Bank is 3.52. Stock Prices Worksheet Sarah now needs you to help her analyze the daily stock prices of one of Hawks \"R\" Us, Inc.'s competitors. Dick's Sporting Goods, Inc. is a publicly traded company. Sarah will use the information about Dick's to create a report for the CEO of Hawks \"R\" Us, Inc. Step 1: Use Google Finance (https://www.google.com/finance) to download the daily stock data for Dick's Sporting Goods, Inc. from January 4, 2016 to February IST 204 Assignment 6 Page 3 29, 2016 into an Excel spreadsheet. The stock symbol for Dick's Sporting Goods, Inc. is DKS. It is your job to figure out how to download this data. Once you have downloaded the stock data from January 4, 2016 to February 29, 2016, copy and paste the Date, Open, High, Low, and Close information into the Stock Prices Worksheet (starting in cell A1). Make sure that the columns are wide enough to properly display the data. Do not copy and paste the Volume data. Once you have copied and pasted the data into the Stock Prices Worksheet, you no longer need the Excel file that you downloaded from Google Finance. Step 2: Type the label \"Increase/Decrease/Same\" into cell F1. Set the column width of column F to 23. Type N/A into cell F2. Write an IF Statement in cell F3 that returns the word Increase if the close price on that day was higher than the close price on the previous day. The IF Statement should return the word Decrease if the close price on that day was lower than the close price on the previous day. If the close prices were the same, the IF Statement should return the word Same. Fill the formula down to cell F40. Step 3: Type the label \"# of Increase\" into cell H5. Type the label \"# of Decrease\" into cell H6. Set the column width of column H to 20. Use the COUNTIF function in cell I5 to return the number of Increase values in cells F3:F40. Use the COUNTIF function in cell I6 to return the number of Decrease values in cells F3:F40. Check Figures: Cell I5 = 18. Cell I6 = 20. Step 4: Type the label \"Highest\" into cell H8. Type the label \"Lowest\" into cell H9. Use a formula in cell I8 to determine the biggest value in column C. Use a formula in cell I9 to determine the smallest value in column D. Step 5: Type the label \"Second Highest Close\" into cell H11. Use the LARGE function in cell I11 to determine the second highest value in column E. You need to figure out how to use the LARGE function on your own. Check Figure: Cell I11 = 42.82. Step 6: Center and bold the headings in cells A1:F1. Format cells I8, I9, and I11 as Currency with two decimal places. Save your file and upload it to Blackboard using the appropriate submission link. IST 204 Assignment 6 Page 4 Option Duration in Years Hawks "R" Us, Inc. Project Financing Options Annual Interest Rate Periodic Payment (PMT) Periods/Year ABC Bank 5 4= XYZ Bank 4 2 RST Bank 3.52 4 8.00% Present Value (PV) Future Value (FV) $500,000.00 $0.00 ($76,000.00) 6.50% ($30,578.36) $500,000.00 $0.00 ($40,000.00) $500,000.00 $0.00 Date Open High Low Close Increase/Decrease/Same 29-Feb-16 42.86 43.42 42.45 42.47 N/A 26-Feb-16 42.97 43 41.94 42.87 25-Feb-16 42.1 42.92 41.28 42.82 24-Feb-16 40.5 42.23 40 42.02 # of Increase 23-Feb-16 39.46 41.1 39.36 40.43 # of Decrease 22-Feb-16 38.8 39.52 38.5 39.32 19-Feb-16 39.36 39.42 37.94 38.5 Highest 18-Feb-16 39.74 39.74 38.82 39.45 Lowest 17-Feb-16 39.38 40.06 39.38 39.82 16-Feb-16 38.09 39.4 37.61 39.04 Second Highest Close 12-Feb-16 36.77 37.51 36.06 37.43 11-Feb-16 36.9 37.22 36.22 36.57 10-Feb-16 37.9 38.43 37.18 37.42 9-Feb-16 37.71 38.54 37.22 37.88 8-Feb-16 37.92 38.09 37.19 37.97 5-Feb-16 38.82 38.97 37.96 38.37 4-Feb-16 38.47 39.15 37.5 38.98 3-Feb-16 39.02 39.21 37.82 38.86 2-Feb-16 39.03 39.47 38.64 38.97 1-Feb-16 38.51 39.4 38.25 38.93 29-Jan-16 37.97 39.24 37.71 39.08 28-Jan-16 38.36 38.75 37.6 37.77 27-Jan-16 37.89 38.23 37.57 38.07 26-Jan-16 36.28 38.12 36.08 38.11 25-Jan-16 36.88 37.41 36 36.13 22-Jan-16 37.34 37.53 36.65 36.97 21-Jan-16 36.45 38.41 36.09 36.93 20-Jan-16 34.1 36.51 33.79 36.13 19-Jan-16 34.51 34.68 33.7 34.36 15-Jan-16 33.44 34.46 33.44 34.24 Google Finance Beta available in: Hong Kong - Canada - U.S. - China - U.K. Information is provided "as is" and solely for informational purposes, not for trading purposes or advice, and may be delayed. To see all exchange delays, please see disclaimer. 2016 Google - Google Home - Blog - Help - Report a Problem - Privacy Policy - Terms of Service