This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: 1 WEEK-1: HOMEWORK/LAB ASSIGNMENTS INTRODUCTION TO EXCEL LEARNING OBJECTIVES (WEEK-1) 1. To learn basic Excel concepts (various copy/paste options, drag down issues, print screen and create word reports, fixed and relative cell addressing, creating professional looking black/white (not haphazard!) graphs). We will cover this in one or two lectures. Lab-1 : Finish the tutorial and then solve problems 1, 4, 5, 6, and hand solve 7 in your homework then show it to the tutors and get a check mark on the attendance sheet before you leave.. DEVELOPMENT OF LOAN AMORTIZATION TABLE In this exercise, we will learn how to use spreadsheets to solve simple accounting problems. We will also learn how easy it is to use multiple formulas within Excel. The handout is prepared with instructions to be used in Microsoft’s Excel 2007. Some of the instructions are similar if an earlier version of Excel is used; however, it is required that you use Excel 2007 or a later version for all the exercises in this class. Loan Amortization tables show how much you owe a bank every month when you borrow money from them. This specific problem is a useful practical example adapted from a problem discussed in Engineering with Excel , by R.W Larson. Let us assume that you recently graduated and bought a brand new Ford Mustang (with cool white stripes, of course!). Thanks to government bailouts you got a nice deal of $25,000 at an annual percentage rate (APR) of 6%, with no down payment. You want to pay the loan within 5 years. In this exercise, you will create an amortization table (monthly basis) that shows your payment, how much went as interest, how much principal was paid, and the amount you owe to the bank. The primary concept in preparing this table is that, each time you make a payment, you have to pay interest on the outstanding borrowed amount, called Principal. The amount that exceeds the interest amount paid reduces the principal before the next payment. Before we proceed into the detailed calculations, we have to set up a spreadsheet for your loan. We will design a general layout to organize information and make it easy to understand. 2 Figure 1.0 Setting up the spreadsheet Figure 1.1 Spreadsheet’s layout after setting the column width Click on the cell A1 and type “Loan Amortization Table”. It can be observed that the cell width is insufficient to fit the contents of the cell. To adjust the cell size, move the cursor to the area shown in Figure 1.0 and click and drag to resize the cell width. Alternatively, you can double click in the area to automatically adjust the cell width to fit the contents. Make the font bold; this is the heading for the worksheet. Every worksheet should have a heading; this is not a necessity, but it is recommended to keep the spreadsheet organized. Click on cell A3 and type “Loan Amount”, “APR” in cell A4, “Loan Term” in cell A5, “Payments/year” in the cell A6. These are called labels. If the worksheet is filled with numbers, it will be difficult for the user to understand it. So, we worksheet is filled with numbers, it will be difficult for the user to understand it....
View Full Document
- Fall '09