CSS 341 - Machine Problem #3

More Problem Solving with Decision and Loop Constructs

Working with Spreadsheets, File Opening, Modularization

Due: Sun, November 1, at midnight  (Note change of date and time)

(50% credit for up to 24 hr late)

Very happy with what we have done for their employee examination board (KDNEPB), the Keystone Employee Organization (KEO) has just awarded us another million-dollar contact ($1.74M to be exact). We need to help the KEO develop a web-page so their members can compute their loan repayment schedules with the KEO.

As part of employee benefits, KEO offers a loan service for its members at discounted rates. Each member can negotiate a loan package, by agreeing on the following information with the loan officer:

Loan Amount – how much the member wants to borrow

Interest Rate – what is the annual interest rate

Monthly Payment – monthly payments

Our job is to develop support information for KEO loan officers so that they can make an informed decision. KEO told us that they want a web-based tool where the loan officer can key in the above three numbers after discussing them with the customer and receive the following information:

Total Amount Paid – how much the member would have paid at the end of the loan

Total Interest Paid – how much of the above amount is interest

Months to repay – how many months it will take to repay the loan.

In addition, KEO wants us to support an option on our web-based solution that allows the KEO loan officer, with the click of a button, to get an Excel-based report describing the details of the loan. This report must be displayed on the screen and provide an option for the KEO officer to save the report as an Excel spreadsheet.

On the saved report, the KEO wants to include:  A customer name or identifier, the above three pieces of  input information;  the above three computed results; and a detailed month-by-month breakdown of loan balance at the start of the month, the payment for that month, and how much interest was paid that month.

Here is the loan repayment policy of KEO, for each month, the Beginning Balance being the balance at the start of that month:

Interest Paid = Beginning Balance * Interest Rate / 12 // interest rate is annual rate

End Balance = Beginning Balance – Monthly Payment + Interest Paid     // interest HURTS!!

For example, if I borrow $100, with monthly payment of $5, at an annual interest rate of 12%, then, for the first month:

Begin Balance = $100

Interest Paid = $100 * (0.12/12) = $1 ; 12% is 12/100 is 0.12

End Balance = $100 - $5 + $1 = $96   ; $100 beginning balance, $5 payment, $1 interest

Now, for the second month, the beginning balance would be $96, Interest would be $0.96, End Balance would be $91.96, etc. etc.

Note, your web-based solution and the accompanying Excel report must allow the KEO loan officer the option of iterating through different input numbers. This is to say, you must calculate a fresh clean web-output (and Excel report) each time the officer wants to submit new data. When a satisfactory set of data is obtained, the officer saves the detailed Excel report.  We are told we should not entertain any loan that takes more than 30 years to repay.  So in our solution, if a loan takes more than 30 years to repay, we print out a warning message, and allow the officer to try new data.

Finally, the KEO office manager indicates that she wants these Excel spreadsheet reports saved in Workbook named “KEOLoanReports”.  In that workbook, there should be a fresh worksheet for each saved report and the name of that worksheet (the label on its tab) should be the customer last name or identifier.  If the Workbook with the above name already exists, the program should add a new worksheet to it.  If the workbook doesnot exist, the program  should create the new workbook, save it with the given name, and then add the new worksheet to it.  At the end of the day, the workbook “KEOLoanReports” should contain a set of worksheets, one for each customer with their identifier or name on the tab.

Now, let’s go show KEO their $1.74M is buying something powerful and useful!! J

Hint: These are some of the Excel functions I used in my implementation in addition to the ones we have alrady seen in class examples and are presented in my example report:

activeSheet.Range().Select – to select a range of excel cells

XlAppl.Applcation.Selection = “” – to clear the selected cells

xlAppl.Application.Workbooks.Add   - to add a new workbook (it becomes the active one)

xlAppl.ActiveWorkbook – the active workbook object

activeWorkbook.Name  - name property of the active Workbook

activeWorkbook.SaveAs(“XXXX”)

xlAppl.Application.WorkSheets.Add() – Add (insert) a new worksheet in active workbook  (it becomes the active one)

xlAppl.ActiveSheet  - the active worksheet object

activeSheet.Name – the name  propoerty of the activeSheet object (apears on sheet tab)

activeSheet.Cells(row,col).Value =   to place value in a cell

activeSheet.Range.Value = to place value in a range

Here is an example loan report based on a $100- loan, at 12% interest rate, with $5 per month payment plan. To help you format your Excel loan report, here is the formatting code I used. You are welcome to use this in your solution if you wish.

Here is another example loan report based on a $1347- loan, at 13.7% interest rate, with $173.23 per month payment plan.

WATCH OUT: Besides checking the input data to make sure it is valid, you should also check to make sure the initial payment covers the interest; otherwise, you may have an infinite loop on your hand!   For example, with the $100 loan at 12% annual rate, what will happen if the payment plan is $0.5 per month? Trap these errors gracefully – allowing the loan officer to continue from where she was.

Documentation of process:  In addition, please hand in one page that describes the software development process you and your partner applied to this problem.  Give the reader a description of how you conceptualized the project, addressed it as a team, and brought it to completion.  Be specific about your tasks.  How did  you test and debug this program? A flow chart is not required here – but it might be a useful way to describe the organization of the project.

This programming assignment contributes 6.4 % towards your final grade for this course.

I will open your program with Internet Explorer and test it with a wide range of input data.  All errors in input should be trapped and  handled well.

Suggstions on Process:

1. This program is more complex than the previous ones.  You may choose to organize your work somewhat differently than in the previous problems.  Perhaps divide up the tasks, code the modules assigned, check each other’s work, combine the pieces, and begin testing.  You will need to be working parallel.

2. You are expected to develop modular code at this point.  Study your task carefully and divide up the task into logically consistent modules.  There should be a large number of simple modules rather than a small number of more complex  ones.

3. Many of these tasks are the same or similar to ones you would carry out in other programs.  So, try to design the modules that are generic so that they can operate in a different program with only minor changes, or none at all.

Suggestions on Modularization

To give you some sense of one approach to modularizing this problem, the folling is a list of subprograms and functions that appeared in a solution to the problem.  The names are self explanatory to the degree that you can get a good idea of the task done by each:

· OpenExcelWorkBook

· InitializeExcelPage

· OpenExcelWorkSheet

· ValidInput

· ClearDataCell

· ValidPaymentPlan

· TooLongToRepay

· RandomNumber

· SubmitButton_onClick

· ShowDetails_onClick

· ClearAllOutput

· LoanAmount_onFocus

· InterestRate_onFocus

· MonthlyPayment_onFocus

· OK_onClick

· Quit_onClick

· HTML page rendering the web page.