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. 

Final Exam

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.

 

Steps to Perform:

Step Instructions Points Possible
1 Open Excel, and then open the downloaded file final_exam.xlsx. 0
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
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. For example, the country currency for Germany should be Euro. 5
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 (using the Euro € 123 format for Germany). 5
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
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 your name on the right side. 5
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
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
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 for the first month (use absolute references where appropriate and leave the result as a negative value). In cell F11, use the PPMT function to calculate the principal payment for the first month (use absolute references where appropriate  and leave the result as a negative value). In cell G11, use relative references to add the values in cells C11 and F11. 10
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 B12, 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
11 In cell G5, reference the payoff date of the loan (the date of the last payment). Be sure to use a relative reference to the cell containing the date. 5
12 Use the SUM function in cell G7 to calculate the total amount paid over the course of the loan. 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
13 Format all cells containing dollar amounts to Accounting format, if necessary. Format all cells containing dates to display the date using the default Date format and center the dates in the cells. 5
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
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
16 Use Find & Replace to replace all instances of Full with Full Professor in the Rank column (use Ctrl-F to display Find & Replace window). AutoFit the width of column D. 5
17 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. 5
18 Using the data in A2:J8, insert a PivotTable starting in cell A10. Add Department to the Rows, Gender to the Columns and Salary to the Values area. Format salaries to the Accounting format with no decimal places. 5
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
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
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
Total Points 100

 

 

HelpHub
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Choose HelpHub

HelpHub

Quality Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

HelpHub

Qualified Writers

We have hired a team of professional writers experienced in academic and business writing. Most of them are native speakers and PhD holders able to take care of any assignment you need help with.

StudyAcer

Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account.

StudyAcer

On Time Delivery

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. We will always strive to deliver on time.

StudyAcer

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text.

StudyAcer

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Help Hub

Place your order

Fill in the order form and provide all details of your assignment.

Help Hub

Proceed with the payment

Choose the payment system that suits you most.

Help Hub

Receive the final file

Once your paper is ready, we will email it to you.

HelpHub Writing Services

No need to work on essay at night. Sleep tight, we will cover your back. We offer all kinds of essay writing services.

HelpHub HelpHub

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

HelpHub HelpHub

Admissions

Admission Essays

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

HelpHub HelpHub

Editing

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

HelpHub HelpHub

Revision

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied.