Reference no: EM132377344

Business and Information Technology Assignment - Ontario Tech University, Canada

1. Time Share Loan Data File needed for this exercise: TimeShare.xlsx

Problem: You have been hired as a loan officer for Durham Vacation properties, a time share company in North Oshawa. As part of your responsibilities, you calculate loan information for customers who purchase timeshares. You will use Excel to perform loan related calculations and summarize your data.

Enter Your Name and Student ID

(a) Open the workbook named TimeShare.xlsx and save it as Durham TimeShare.xlsx.

(b) In the Documentation worksheet, in cell A1, enter the title Durham Time Shares. In cell B3 enter your name. In cell B4 enter your student number. In cell B5 enter the completion date of your assignment and in cell B6 enter a sentence to describe the purpose of the workbook.

(c) Save your workbook.

Preliminary Calculations

(a) In cell B2, enter today's date.

(b) In cells D9:H9 and J9:K9, enter appropriate formulas and/or functions to compute the Down payment, % Financed, Amount financed, Mortgage rate, Rate per period, # of Pmt periods, and Payment amount according the information provide in Table 1.

(c) Use the fill handle to copy the formulas down to row 25.

(d) In cell K29 enter a function to calculate the total monthly payment amount.

(e) Save your workbook.

Down payment	The Down payment amount is computed based the client's credit score(C9) and house cost (B9). If the credit score is below 450, then the down payment 15% of the house cost, otherwise, 5% of the house cost (see cells B4:B6).
% Financed	% Financed is based on the house cost and the down payment.
Amount financed	The Amount financed is the difference between house cost and down payment.
Mortgage rate	The Mortgage rate is based on clients' credit score (see Table 2).
Rate per period	The Rate per period is based on the interest rate (mortgage rate) and # of payments per year (assume every loan is paid monthly).
# of Pmt periods	The # of Pmt periods is based on the loan term (years) and # of payment per year (again, assume every loan is paid monthly)..
Payment amount	The payment (monthly payment) is based on amount financed, rate per period, and # of payment periods.
Table 1: Loan Related Formulas

 

Credit Score	Interest Rate
< 100	15%
100 - 600	8%
600 - 750	6.5%
> 750	4.25%
Table 2: Mortgage Rate Table

Summarize Data

(a) In cells B32:B36, enter appropriate functions to calculate the total # of loans, lowest, highest, average, and median monthly payment.

(b) In cells B39 and B40, enter appropriate functions to calculate the number of clients with credit scores below and above 450.

(c) In cells B43 and B44, enter appropriate functions to calculate the total amount financed by clients with credit scores below and above 450.

(d) Save your workbook.

Charts and Finalize the Workbook

(a) Create a pie chart based the information computed in cells B39 and B40.

(b) Create a bar chart based the information computed in cells B43 and B44. Add appropriate title, labels and axis titles to both charts.

(c) Format your workbook as desired (Headings, Font, Fill color, Number format...etc.).

(d) Create a footer with your name on the left side, the sheet name code in the center, and the file name on the right side.

(e) Save and close the workbook.

2. Durham Auto Insurance Data File needed for this exercise: Insurance.xlsx

Problem: In this exercise you will work with a spreadsheet containing the following information for a group of drivers:

Name	Birthdate	Annual Income	Points
...	...	...	...

Your task is to set-up the worksheet with necessary lookup tables, formulas and functions to calculate the insurance premium for each driver.

Enter Your Name and Student ID

(a) Open the workbook named Insurance.xlsx and save it as Durham Auto Insurance.xlsx.

(b) In the Documentation worksheet, in cell A1, enter the title Durham Auto Insurance. In cell B3 enter your name. In cell B4 enter your student number. In cell B5 enter the completion date of your assignment and in cell B6 enter a sentence to describe the purpose of the workbook.

(c) Save your workbook.

Preliminary Calculations

The insurance company you work for has the following rules for calculating the premium:

• The base premium is calculated based on Table 3.
• A surcharge is applied to the premium for every point on the record based on Table 4.
• A surcharge is applied based on driver's income (see Table 5).
• A discount of $150 is given if the driver is older than 45 years of age, an annual income of more than $60,000 and has less than 2 points on his/her record.

Age	Base Premium
< 25	$2500
>= 25 and < 35	$2200
>= 35 and < 45	$2000
>= 45 and < 55	$1700
>= 55 and < 65	$1400
> 65	$1100
Table 3: Base Insurance Premium per Year

 

Age	Surcharge per point
< 25	$200
>= 25 and < 45	$150
> 45	$120
Table 4: Surcharge per Point

 

Annual Income	Surcharge
< $25,000	$150
$25,000 - $45,000	$100
$45,000 - $100,000	$80
> 100,000	$50
Table 5: Surcharge based on annual Income

(a) In columns C, E, F and G, enter appropriate formulas/functions to calculate the Age, Premium per Year and Premium per Month for each driver.

(b) In rows 14 to 16, enter functions to calculate the minimum, maximum and average of Annual Income, Points, Premium per Year, and Premium per Month.

(c) Save your workbook.

Charts and Finalize the Workbook

(a) Create a clustered bar chart on the worksheet using drivers' name and premium per month columns. Move the chart to its own sheet and name the sheet Bar Chart. Add appropriate chart title and axis title to the chart.

(b) Format your workbook as desired (Headings, Font, Fill color, Number format...etc.).

(c) Save and close the workbook.

Attachment:- Business and Information Technology Assignment File.rar