International Travel Currency Converter Country Traveling To U.S. Dollars to Exchange Country Currency Amount Due Customer Germany Mexico Japan $ 1.000,00 $ 100,00 $ 500,00 Country Argentina Candada England France Germany Hong Kong India Israel Japan Mexico Russia South Korea Venezuela Currency Exchange Rate for $1.00 US Peso 3,3500 Dollar 1,5603 Pound 0,6200 Euro 0,9525 Euro 0,6354 Dollar 7,7980 Rupee 47,8401 Shekel 4,7783 Yen 107,2900 Peso 10,2721 Rubles 31,8300 Won 1.185,8000 Bolivar 1.603,7500 I Wanna Cool Boat! Enter Loan Parameters as Indicated Principal $65.000 Annual Interest 8,50% Date of First Payment 9/1/2017 Term of Loan (years) 5 Term of Loan (months) Payment Number Date Beginning Balance Loan Summary Information Monthly Payment Payoff Date Total $ Payments to Make Total $ Interest to be Paid Regular Payment Interest Paid Principal Reduction mary Information Ending Balance LastName FirstName Department Rank From Nelsen Beth Finance Full Columbus OH Smith Tom Finance Full Odessa TX Parker Mathew Accounting Full Young Jeff Jacobson Andrew Gender Salary 1988 F $ 56.589 1983 M $ 61.417 Miami FL 1985 M $ 62.142 Management Assistant Austin TX 2001 M $ 47.763 Management Full 1982 M $ 60.531 Rais Mary Finance Instructor Macon GA 2004 F $ 31.250 Memphis TN YearHired Retirement Matching % 4,00% Participates in RetirementRetirement Plan Matching $ Yes Yes Yes Yes Yes No Office 2016 – myitlab:grader – Instructions Excel Project YO_Excel16_V1_Cap Project Description: You are planning on taking a vacation abroad later this year. You have designed a currency converter so you can determine how much money you need to take into each country. Once you return, you will be purchasing a new boat, so you are also working on an amortization table in order to adjust your budget. Of course, you are working on these tasks in your spare time at work as an administrative assistant for the CIS Department where you are working on several worksheets to evaluate faculty data. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Points Possible Instructions 1 Open Excel, and then open the downloaded file yo_e_capstone_grader.xlsx. Save the file as yo_e_capstone_grader_LastFirst using your last and first name. 0.000 2 On the Currency Exchange worksheet, format the title International Travel Currency Converter as bold with 14 pt font. Merge and center the range A1:D1. 5.000 3 Use the VLOOKUP function, the data on the Currency Table worksheet, and appropriate relative and absolute cell references to display the country’s currency name in its respective cell in row 5, replacing the existing text. 5.000 4 Use the VLOOKUP function and the Currency Table to calculate the amount due based on each country’s exchange rate in each country’s respective cell in row 6. Be sure to use absolute cell references where necessary. Change the monetary symbol accordingly for each conversion. 5.000 5 Format the range A3:D4 with Yellow fill (under Standard Colors) and a thick outside border. Format the range A5:D6 with Green fill (under Standard Colors) and a thick outside border. 5.000 6 Format the Currency Exchange worksheet to print in landscape orientation, with the data centered horizontally. Insert a header with the current date (using the &[Date] tag) inserted on the left and the text Excel Student on the right side. 5.000 7 Display the Personal Loan worksheet. Insert a formula in cell C8 to calculate the total number of payments for the loan using relative cell references. 5.000 8 In cell G4, use the PMT function to calculate the monthly payment for the loan. Be sure to use relative cell references. The function should return a negative value. 5.000 9 In cell A11, enter 1 as the first payment number and in cell B11, enter 9/1/2017 as the first payment date. In cell C11, insert a relative reference to the loan amount and in cell D11, insert an absolute reference to the monthly payment amount. In cell E11, use the IPMT function to calculate the interest paid and in cell F11 use the PPMT function to calculate the principle deduction. Be sure to use absolute cell references where necessary in the IPMT and PPMT functions. In cell G11, use relative references to add the values in cells C11 and F11. 10.000 Updated: 02/24/2017 1 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Excel Project Step Instructions Points Possible 10 Select the range D11:G11, and then use the fill handle to copy the functions to row 12. In cell A12, enter 2 as the second payment number and in cell B11, enter 10/1/2017 as the second payment date. In cell C12, insert a relative reference to the ending balance in cell G11. Select the range A11:B12, and then use the fill handle to complete the columns through row 70. Select the range C12:G12, and then use the fill handle to complete the amortization table. 5.000 11 In cell G5 reference the payoff date of the loan. Be sure to use a relative reference to the cell containing the date. 5.000 12 Use the SUM function in cell G7 to calculate the total amount paid over the course of the loan, replacing the existing value. Use the SUM function in cell G8 to calculate the total interest paid over the course of the loan. Both functions should return negative values. 5.000 13 Format all cells containing dollar amounts to currency, if necessary. Format all cells containing dates to display the date in the mm/dd/yyyy format and center the dates in the cells. 5.000 14 Create a copy of the Faculty Data worksheet to the right of the last sheet tab. Rename the new worksheet as Faculty Data Exam. 5.000 15 On the Faculty Data Exam worksheet, sort the table data by Department, then by Rank, both in ascending order. Insert a comment in cell J3 with the text Matching Rate Should Be Raised! 5.000 16 Use Find & Replace to replace all instances of Full with Full Professor in the Rank column. AutoFit the width of column D. 5.000 17 Use the Quick Analysis tool to insert Data Bars in the Salary column. Change color of the data bars to Green (under Solid Fill). 5.000 18 In cell J3, use the IF function to calculate the retirement matching for each faculty member participating in the retirement plan. For participating members, the function should multiply their salary by the retirement matching percentage. For all other members, the function should return 0. Be sure to make the reference to cell J1 an absolute reference in the function. Use the fill handle to copy the function down through the column. Apply the currency format to the values and align the text to the right of the cell. 5.000 19 Display the Faculty Data worksheet, and then create a table with headers using the data in the range A2:J8. Change the name of the table to FacultyData. Sort in ascending order by YearHired, and then filter the table to show only those faculty hired between 1982 and 1985. 5.000 20 Using the filtered data results, select the nonadjacent ranges of C2:C5 and H2:H5 then insert a 2-D pie chart. Move and resize the pie chart so that it fills the range A10:E21. Change the title of the pie chart to Salary for College of Business Hires 1982-1985. Change the font of the chart title to 10.5 pt. 5.000 21 Save the workbook. Ensure that the worksheets are in the following order: Currency Exchange, Currency Table, Personal Loan, Faculty Data, and Faculty Data Exam. Save the workbook, exit Excel, and then submit your file as directed by your instructor. 0.000 Total Points Updated: 02/24/2017 2 100.000 Current_Instruction.docx
Purchase answer to see full attachment