computer application homework 2
Please read everything carefully in advanced. Complete both questions and upload the individual Excel
solution files for each by the due date. Also, be sure to visit your assigned Lab on or before February 27th
to have your assignment graded. Assignments not demonstrated will receive a grade of zero.
Problem 1: [25 marks] Winter is here and you have decided to make the most of it by purchasing a
snowmachine. You started saving up for the purchase last fall, but when you look at your bank balance it is
clear that you need to finance part of the purchase. You have a down payment and now wish to develop an
amortization schedule so you can plan your payments. You hope to borrow no more than $10,000. Interest
rates are low, but may soon be rising. Because the rates will change depending upon the brand of
snowmachine and the dealer, you wish to examine several different payment plans. You hope to pay off the
loan in 5 years.
Develop a worksheet that will show the monthly payment, the beginning and ending balance for each year of
the loan, the annual cost of the loan, and the annual interest paid for each year of the loan.
Instructions: With a blank worksheet on the screen, create the worksheet partially shown in Figure E4A 1.
Perform the following tasks:
1. Select the entire worksheet and change the font size to 12.
2. Change the column widths to the following: A = 11.00; B, C, and E = 20.00; D = 19.00.
3. Change the row heights to the following: 1, 5, and 12 = 39.00.
4. Enter the worksheet title, Snow Machine Loan Analysis, in cell A1. Merge and center cell A1 across the
range A1:E1. Change the font type to Biondi, the font size to 20, the font color to white and the
background to dark blue.
5. Enter the following labels: A2 = Principal; A3 = Rate; A4 = Years; C4 = Payment; E4 = per month; A5 =
Year; B5 = Beginning Balance; C5 = Ending Balance; D5 = Total Paid; E5 = Interest. Change the font color
to dark blue and bold all the labels except E4. Add a thick dark blue line under the titles in A5 through
6. Enter the =Now() function in cell E2 to display the current date and format as shown in Figure 1. (Note:
The date will be different from that in Figure 1 so you will need to change the number format.)
7. Enter the principal amount of $10,000 in cell B2. Format the cell to currency format with no decimal
8. Enter the interest rate of 4.90% in cell B3. Format the cell as shown in Figure 1.
9. Enter the number 5 in cell B4 for the number of years.
10. Enter the function =PMT in cell D4 to calculate the monthly payment on a loan of $10,000 (cell B2) at
4.90% (cell B3) for 5 years (cell B4). Format it as shown in Figure 2-1.
11. Enter 1 in A6 and 2 in A7, then use the fill handle to fill the range A6:A10 with the numbers 1 through 5.
12. Enter the formula =B2 in cell B6 to reference the principal, which is the beginning balance for year 1.
13. Enter the =PV function in cell C6 to determine the ending balance for year 1.
14. Enter the formula =$D$4 * 12 in cell D6 to determine the annual amount paid on the loan.
15. Enter the formula =D6-(B6-C6) in cell E6 to calculate the amount of interest paid for the year.COSC1702 Winter 2015 Project #2
Due: February 13th, 2015 by 11:55pm
16. Enter the formula =C6 in cell B7 to obtain the beginning balance for year 2. Copy this formula to the
17. Copy the =PV function entered in cell C6 to the range C7:C10.
18. Copy the formula in cell D6 to the range D7:D10.
19. Copy the formula in cell E6 to the range E7:E10. If all is done properly, the value in cell C10 should be
20. Use the =SUM function in cells D11 and E11 to sum the payment and interest amounts.
21. Format all cells as shown in Figure 2-1.
22. In cell A12, enter the label, The Effect of Various Interest Rates. Format this entry the same as the
formatted entry in cell A1 (Hint: use format painter).
23. Enter the following labels: B13 = Rate; C13 = Total Paid; D13 = Total Interest.
24. Enter the formula =D11 in cell C14 and the formula =E11 in cell D14.
25. Enter and format the interest rates shown in Figure 2-1 into the range B15:B21.
26. Create a one-variable data table that displays the total amount paid and the total amount of interest for
the 5 year amortization schedule created in this exercise. Format the results.
27. Rename the Sheet 1 tab to Car Loan.
Our Service Charter
Excellent Quality / 100% Plagiarism-FreeWe employ a number of measures to ensure top quality essays. The papers go through a system of quality control prior to delivery. We run plagiarism checks on each paper to ensure that they will be 100% plagiarism-free. So, only clean copies hit customers’ emails. We also never resell the papers completed by our writers. So, once it is checked using a plagiarism checker, the paper will be unique. Speaking of the academic writing standards, we will stick to the assignment brief given by the customer and assign the perfect writer. By saying “the perfect writer” we mean the one having an academic degree in the customer’s study field and positive feedback from other customers.
Free RevisionsWe keep the quality bar of all papers high. But in case you need some extra brilliance to the paper, here’s what to do. First of all, you can choose a top writer. It means that we will assign an expert with a degree in your subject. And secondly, you can rely on our editing services. Our editors will revise your papers, checking whether or not they comply with high standards of academic writing. In addition, editing entails adjusting content if it’s off the topic, adding more sources, refining the language style, and making sure the referencing style is followed.
Confidentiality / 100% No DisclosureWe make sure that clients’ personal data remains confidential and is not exploited for any purposes beyond those related to our services. We only ask you to provide us with the information that is required to produce the paper according to your writing needs. Please note that the payment info is protected as well. Feel free to refer to the support team for more information about our payment methods. The fact that you used our service is kept secret due to the advanced security standards. So, you can be sure that no one will find out that you got a paper from our writing service.
Money Back GuaranteeIf the writer doesn’t address all the questions on your assignment brief or the delivered paper appears to be off the topic, you can ask for a refund. Or, if it is applicable, you can opt in for free revision within 14-30 days, depending on your paper’s length. The revision or refund request should be sent within 14 days after delivery. The customer gets 100% money-back in case they haven't downloaded the paper. All approved refunds will be returned to the customer’s credit card or Bonus Balance in a form of store credit. Take a note that we will send an extra compensation if the customers goes with a store credit.
24/7 Customer SupportWe have a support team working 24/7 ready to give your issue concerning the order their immediate attention. If you have any questions about the ordering process, communication with the writer, payment options, feel free to join live chat. Be sure to get a fast response. They can also give you the exact price quote, taking into account the timing, desired academic level of the paper, and the number of pages.