CSC 101 Introduction to Computers Spring 2008 Unit #5 - Microsoft Excel/H4> Instructor: Susan E Traudt Sue@ttsw.com / Sue@ox.ttsw.com

Home Survivor's Guide Schedule Course Units & Assignments Article Summaries

Topics this unit will cover: Navigating Excel Saving, previewing, and printing spreadsheets Entering data and formulas into cells Inserting and deleting rows Formating cells, and workbooks Cutting and pasting cell contents What-if Analysis Printing formulas vs. printing data Using Excel functions Using Excel to calculate loan information

Goals

• Confidence using Excel to create a spreadsheet
• Understand different ways of entering formulas
• Understand and use Excel functions
• Be able to conduct a What-if Analysis
• Be able to find the monthly payment, and other loan information

Points Available to Earn in this Unit

Required Assignments - 22 points

• Tutorial Exercises from Go! with Microsoft Excel - 1 point each, 7 points total
• Budget & What-if Analysis - 10 points
• Auto Loa Comparison - 15 points

Lectures & Reference Material

How to use the PMT Function in Excel - PMTFunction.doc

How to use the PPMT and IPMT Functions in Excel - PPMTFunction.doc

Excel Tutorial - http://einstein.cs.uri.edu/tutorials/csc101/pc/excel97/fill.html

Using What-If Analysis - http://www.umbc.edu/oit/sans/helpdesk/Microsoft/Excel/HOWTO_WhatIf_Analysis.html

Reading & Assignments

1. Go! with Microsoft Excel, project 1A - pgs. 3-34 and 1B - pgs. 35-63 - Due Date: Monday, March 31, 2008 - 1 point each

2. Go! with Microsoft Excel, project 2A - pgs. 91-118 and 2B - pgs. 119-144 - Due Date: Wednesday, April 2, 2008 - 1 point each

3. Excel Monthly Budget and What-if Analysis -

   Using the following information, create a three month budget using Excel. You should put an appropriate title on the top, i.e. Sue's Monthly Budget, the list the months across the top of the column of your spreadsheet and the expense or income down a column on the left hand side.

   Place your income first, then your expenses. Using formulas, compute your total income, total expenses, and the amount of money your have left (or that you are short) for each month. Then compute the totals for the entire three month period.

   Your Income

   • $2400 from your full-time job
   • $240, $300, $150 for month 1-3 from your part-time job selling tupperware

   Your Expenses

   • Rent: $700 per month
   • Car Insurance: $220 per month
   • Food: $320, $400, $380
   • Clothing: $500, $120, $200
   • Savings: $200 per month
   • Gas/Travel/Parking: $120, $300, $130
   • Entertainment: $100, $90, $140
   • Other: $90, $40, $160

   For your basic budget, you should find the following both for each month, and for all three months,

   • Your total income
   • Your total expenses
   • What your have left over each month (total income - total expenses)
   • What percentage of your overall total expenses each total expense is and what percentage of your overall total expenses each months total expense is.

   Once you have completed your basic budget, print out your spreadsheet, then printout your spreadsheet showing your formulas.

   Next, perform the following What-If analysis - Note: each possible change is independent from each other possible change

   What-If Analysis

   • You receive a 5% raise
   • Your rent increases 15%
   • During month 1 you have an accident with your car, your car insurance doubles for the remaining months.
   • The price of food increases by 10%, the price of gas increases by 15%, you spend 20% less on clothing
   • You want to get a new computer. You would like to double the amount you are currently putting into savings. Can you do it?

   Print out your spreadsheets showing what your budget will look like with each possible change. Attach all your spreadsheets together and turn hem in.

   Sample Spreadsheet - MonthlyBudget.xls

   Due Date: Monday, April 7, 2008 - 10 points

4. Go! with Microsoft Excel, project 3A - pgs. 171-199, 3B - pgs. 200-210, and 3C - pgs, 211-215 - Due Date: Monday, April 7, 2008 - 1 point each

5. Which car loan is the best??

   You've graduated from college and have your first real job - Congratulations. Now, you need to purchase a new car. You've found one that you like, but you will need o take out a loan for $15,000. You have three different banks, Bank A, Bank B, and Bank C, from which you can take the loan. Each bank is offering you different interest rates and different amounts of time to pay the loan back. The terms are as follows:

   Bank A
   Interest Rate - 7% annually
   Time to pay loan back - 2.5 years

   Bank B
   Interest Rate - 5.5% annually
   Time to pay loan back - 3 years

   Bank C
   Interest Rate - 6% annually
   Time to pay loan back - 2 years

   In Excel. set up the terms for each of the three banks, the use the PMT function to calculate what your monthly payment will be for each of the loans. Which bank will require the largest monthly payment? Which will require the smallest monthly payment?

   Now, set up a section of your spreadsheet showing an entry for each payment for each of the loan offers. Then use the PPMT function to calculate the amount going toward the loan principal for each payment. Then use the IPMT function to find the amount of interest for each payment. Once you have done that you can sum the amount of interest that you will end up paying for each loan. Which offer will end up costing you the least amount in interest?

   Print out the spreadsheet with your calculation, the answers t the questions above, and a copy of the spreadsheet showing your formulas. Make sure all the pages have your name on them and turn them in.

   How to use the PMT Function in Excel - PMTFunction.doc

   How to use the PPMT and IPMT Functions in Excel - PPMTFunction.doc

   - Due Date: Wednesday, April 9, 2008 - 15 points