Computer Science and Computer Engineering 120
Computerized Information Systems
Fall 2006



Lab 10

Goal:

Lab 10 is intended as a review of Excel and Financial problems. The lab will consist of three worksheets that should be included in one workbook. Name the sheets appropriately and delete any unused sheets.

CPI:

Format this spreadsheet carefully. Suppose that you were going to make the presentation for a class or meeting and planned to use the spreadsheet during that presentataion.

The Consumer Price Index is an attempt by the United States Government to measure the average change in cost of common goods and services purchased by households. In 2004 the index rose by 3.3 percent. The most recent figures as of Nov. 16, 2006 as shown at the official website http://www.bls.gov/cpi/ say the CPI for all goods for all cities was just 1.3% for the past 12 months, a remarkably low number.

Assume the rate of inflation remains constant at fairly typical 3.3% for the next 40 years. Create a spreadsheet that allows the user to input the amount spent on goods in 2006 and the rate of increase in the CPI and shows the expected cost of those goods for each of the next 40 years.

For example, suppose that it cost $100 to buy an assortment of goods today and the CPI is 10% per year. (Fortunately the CPI currently is much lower than 10%) Then one year from now those goods would cost about $110 and $121 two years from now.

Also create a chart on its own chart page showing that the cost of those goods over the 40 year period.

Hint: The solution to this problem is exactly the same as if you were going to put the money in a savings account where the APR equalled the rate of increase in the CPI and the amounts were compounded once a year.

Grade book:

Professor Jones has asked you to build a spreadsheet for grading her class. The student scores are available in the sheet http://www.cs.plu.edu/courses/csce120/fall06/download/excel/lab10.xls. You can add additional columns and rows as seem appropriate. The last column should have the automatically computed letter grades using the scale shown below. The final sheet should be formatted nicely so you can impress Professor Jones with your abilities

The class normally will be graded as follows.

However, if the score on the final is higher than the average of the first 3 exams the score on final, then the low exam score is dropped and student is graded as follows:

Hint: The sum of the best two exams can be computed by adding all three exams and subtracting the minimum score.

The grading scale is as follows:

Also calculate the class average, median, and maximum for each column or average where it makes sense to do so. Add nicely labeled pie chart on the same page showing the distribution of grades.

Payday loans:

This problem must be solved in a fashion that makes it easy for the paper grader to find your solution for each part and your method for the solution must be readable and obvious but your solution does not have to be formatted in a "pretty" way. http://www.cs.plu.edu/courses/csce120/fall06/download/excel/lab10.xls shows a simple minded example of what your sheet might look like. The fee and the amount of the loan must be changable assumptions.

The website for one Payday loan company (Checkmate) specifies that for payday loans (in Washington) they charge $15 for a $100 paycheck loan due in 7 to 30 days.

1. Determine the APR for this loan if it is paid in 7 days ( 7/365 of a year). This can be done with the rate function or just by setting up small table and using goal seek. (You can check your solution for this part by going to their website http://www.callcheckmate.com/) Used the "Fee Schedule" link in the left side bar. The rates in this problem are Washington. It might interesting to look at the rates in other states such as Colorado.
2. Suppose that the person at the end of a week has enough money to pay the $15 fee but not enough to pay off the principle so they just borrow the $100 again. Suppose this action continues for a whole year (slightly over 52 weeks). How much interest would they pay for the full year?
3. Suppose that the person borrowed $100 at APR calculated in part a. compounded weekly. Assume that they are unable make any payments on the loan and they just let the balance grow for 1 full year. At the end of the year, how much would they owe? (This action is not realistic because the loan company would cut the customer off after not making any payments.)

Extra credit: 2 point bonus Obviously any reasonable customer would borrow the money for 30 days instead of 7 days. Write your solutions for this problem in a fashion that allows the user to input the number of days for the loan. Assume there 365 days in a year (so there will be slightly over 52 weeks in a year and slightly over 12 "months" in a year).. You must point out your eligibility for this extra credit in your e-mail to the paper grader.

Grading:

Your spreadsheets will be graded on meeting the requirements, correctness, general looks, and the impression they leave the reader.

Submitting your workbook:

1. Use the URL http://www.cs.plu.edu/submit/login.php
2. Select your class (csce120-brink-secX, Fall 2006 where X is 1 or 3) from the drop down box.
3. Enter your submit system username and password.
4. Click "Login".
5. In the add Assignment box, browse to find the workbook for Lab 10.
6. Select "Lab 10" for the assignment and then click "Upload".
7. Double check to make sure the file has been uploaded.
8. Click "Sign Off" near the top right hand corner of the window.

Due date:

Wednesday, Nov. 22. There will be a 24 hour grace period. After the grace period late submittals will not be accepted except with written permission from the instructor and will be subject to a 20% per day. (Late assignments turned in over 24 hours after the due date are 2 days late and are subject to a 40% penalty.)

Revised: Nov. 26, 2006