Solved by verified expert:homework help
osborne_preteststarter.xlsx
pre_test_2_instructions.docx
Unformatted Attachment Preview
Best Bank
Home Loans – 2016
Last Name
Al-Asiri
Barra
Chapman
DeLonge
Eckmann
Francis
Geist
Hill
Ibarra
Justice
Klein
LaRoche
Martinez
Norman
O’Leary
Piper
Quinton
Rafiq
Smith
Loan Years
15
20
25
30
First Name
Cost of Home Down Pay Loan Years
Muhammad
300000
60000
Raquel
422000
80000
Dennis
175000
24000
Edgar
250000
44000
Julian
450000
77000
Maria
346000
70000
Roman
383000
80000
Jennifer
147000
32000
Daniel
267000
50000
Anna
505000
88000
Colvin
394000
80000
Leslie
224000
35000
Hannah
198000
40000
Oscar
310000
58000
Jennie
274000
50000
Penny
200000
40000
Jovan
371000
70000
Abdul
249000
40000
Allison
177000
32000
20
25
15
20
30
25
25
15
20
20
30
20
30
25
20
15
20
25
25
Loan APR
2.8%
3.1%
3.5%
4.0%
15
20
25
30
PMI required for down pay
less than:
PMI Monthly Rate:
Number of Payments/Year:
Loan Years
20%
0.042%
12
# of Loans
Best Bank
Transactions – August 2016
Withdrawals flagged at or greater than:
Trans_Date Trans_Time
42611 0.997188678
42602 0.103182475
42590 0.773586519
42600 0.196830866
42592 0.916273621
42589 0.893980366
42608 0.051578465
42591 0.090655992
42586 0.466314116
42601 0.052244348
42606 0.264167942
42596 0.782548432
42610 0.597965346
42585 0.945666007
42603 0.829882191
42613 0.069670134
42602 0.128822404
42604 0.305417592
42603 0.595909864
42589 0.303150943
42590 0.334802473
42595 0.536556792
42613 0.717175421
42605 0.318959403
42599 0.516076936
42589 0.655113417
42593 0.572365491
42594 0.880836641
42586 0.127443546
42611 0.810090453
42607 0.375879139
42608 0.246200046
42610 0.098137696
42602 0.186851358
42598 0.103219693
42592 0.373792065
42594 0.999545871
42592 0.962784229
42599 0.393651247
42602
0.89312152
42591 0.072437209
Acct_ID
A00001
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00002
A00003
A00003
A00003
A00003
A00003
A00003
A00003
A00003
A00003
A00003
A00003
A00003
A00003
A00003
A00003
A00003
A00003
A00004
A00004
A00004
A00004
$2,000
Trans_Type
Withdrawal
Deposit
Deposit
Withdrawal
Deposit
Deposit
Deposit
Withdrawal
Withdrawal
Deposit
Withdrawal
Withdrawal
Deposit
Deposit
Withdrawal
Deposit
Withdrawal
Withdrawal
Deposit
Withdrawal
Deposit
Deposit
Withdrawal
Deposit
Withdrawal
Withdrawal
Deposit
Withdrawal
Deposit
Withdrawal
Withdrawal
Deposit
Deposit
Deposit
Withdrawal
Withdrawal
Deposit
Deposit
TRANSFER
Deposit
Deposit
Trans_Amount Trans_Status
$
276.20
$
391.35
$
460.53
$
386.11
$
316.16
$
385.85
$
452.59
$
143.74
$
297.54
$
51.08
$
408.94
$
455.41
$
196.33
$
210.68
$
122.03
$
24.35
$
415.47
$
199.07
$
306.44
$
99.49
$
419.16
$
67.31
$
413.90
$
489.78
$
31.85
$
274.12
$
398.37
$
337.37
$
72.08
$
438.96
$
399.84
$
244.21
$
93.31
$
448.78
$
101.53
$
275.58
$
105.64
$
244.45
$
271.54
$
112.70
$
269.87
42583
42585
42600
42588
42610
42596
42597
42606
42612
42584
42591
42598
42595
42609
42591
42592
42611
42588
42583
42607
42587
42593
42585
42590
42610
42586
42605
42603
42602
42590
42600
42591
42585
42596
42584
42590
42600
42588
42596
42587
42592
42598
42584
42583
42605
42595
42584
0.3808055
0.081437443
0.658328112
0.055116346
0.93264031
0.515954244
0.725158776
0.491935433
0.809866475
0.810828496
0.425816872
0.126845402
0.042902416
0.036597161
0.929757563
0.403935566
0.42513167
0.45774959
0.959948444
0.928546161
0.874051109
0.720245391
0.111259643
0.64191621
0.831028062
0.0065784
0.583446766
0.081304858
0.492434731
0.620469446
0.35584913
0.834259786
0.136876993
0.53993689
0.443602503
0.000683905
0.622307727
0.391697996
0.003849482
0.641494681
0.606230653
0.999590985
0.871980268
0.412898128
0.742568302
0.056576156
0.83472319
A00004
A00004
A00004
A00004
A00004
A00005
A00005
A00005
A00005
A00005
A00005
A00005
A00005
A00005
A00005
A00006
A00006
A00006
A00006
A00006
A00006
A00006
A00007
A00007
A00007
A00007
A00007
A00007
A00007
A00007
A00007
A00007
A00007
A00008
A00008
A00008
A00008
A00008
A00008
A00008
A00008
A00008
A00008
A00009
A00009
A00009
A00009
Deposit
Withdrawal
Withdrawal
Withdrawal
Deposit
Withdrawal
Deposit
Deposit
Deposit
Deposit
Withdrawal
Deposit
Withdrawal
Deposit
Deposit
Withdrawal
Withdrawal
Deposit
Withdrawal
Deposit
Withdrawal
Deposit
Withdrawal
Deposit
Deposit
Withdrawal
Deposit
Withdrawal
Withdrawal
Withdrawal
Deposit
Withdrawal
Deposit
Deposit
Deposit
Withdrawal
Withdrawal
Withdrawal
Withdrawal
Deposit
Deposit
Deposit
Deposit
TRANSFER
Withdrawal
Deposit
Withdrawal
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
213.14
453.37
298.75
248.43
107.30
326.98
151.17
243.36
426.19
171.11
251.83
305.86
388.86
444.06
262.91
315.58
407.02
47.88
117.13
429.17
252.40
140.46
72.69
212.47
151.44
413.81
199.67
318.03
493.28
312.62
326.48
112.21
264.39
347.19
24.30
90.12
2,117.73
470.13
447.73
202.08
434.11
182.92
351.67
368.46
59.17
132.26
198.57
42602
42594
42585
42603
42607
42603
42585
42610
42606
42607
42603
42613
42585
42593
42587
42603
42586
42584
42589
42599
42605
42593
42609
42591
42596
42598
42611
42599
42590
42584
42602
42585
42609
42608
42595
42606
42612
42586
42595
42610
42591
42590
42589
42583
42604
42594
42596
0.478263377
0.680071748
0.583404637
0.906389244
0.783326926
0.551548506
0.516494174
0.065383291
0.145197418
0.134539366
0.953181981
0.346255913
0.773702284
0.406873152
0.726871543
0.691519781
0.199746229
0.495436698
0.189799496
0.668229123
0.290384056
0.301463223
0.125141387
0.391117149
0.429487644
0.66013729
0.062167425
0.357094439
0.202882784
0.010519625
0.309986109
0.221603936
0.59669444
0.19406463
0.504990386
0.682561211
0.699521953
0.535882872
0.664597234
0.417160798
0.859461278
0.699881293
0.145300395
0.330010766
0.183937844
0.743440567
0.118635519
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00009
A00010
A00010
A00010
A00010
A00010
A00010
A00010
A00010
A00010
A00010
A00010
A00010
A00010
A00011
A00011
A00011
A00011
A00011
A00011
A00012
Deposit
Deposit
Withdrawal
Deposit
Deposit
Withdrawal
Deposit
Deposit
Deposit
Withdrawal
Withdrawal
Withdrawal
Withdrawal
Withdrawal
Withdrawal
Deposit
Withdrawal
Withdrawal
Withdrawal
Deposit
Deposit
Deposit
Deposit
Deposit
Deposit
Deposit
Deposit
Withdrawal
Withdrawal
Withdrawal
Withdrawal
Withdrawal
Withdrawal
Deposit
Deposit
Withdrawal
Deposit
Withdrawal
Deposit
Deposit
Withdrawal
Withdrawal
Withdrawal
TRANSFER
Deposit
Deposit
Deposit
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
44.59
315.30
270.24
77.81
331.99
278.83
321.31
293.59
390.21
375.53
401.91
333.11
296.54
134.75
97.14
449.49
427.90
412.86
294.88
164.49
169.59
388.47
169.17
112.97
258.81
253.00
53.87
5,285.62
275.94
355.67
169.99
193.64
14.94
18.64
421.09
398.36
82.38
129.59
472.13
384.18
124.35
198.98
88.96
50.46
324.43
444.33
64.23
42593
42603
42583
42586
42601
42600
42585
42591
42593
42609
42586
42587
42596
42608
42607
42589
42604
42607
42601
42593
42587
42590
42602
42596
42591
42601
42585
42595
42613
42599
42613
42586
42607
42607
42612
42593
42589
42600
42596
42592
42586
42587
42594
42609
42593
42589
42593
0.961333572
0.834865988
0.375338256
0.31212727
0.218698617
0.713213547
0.197691317
0.349819787
0.914233593
0.528607549
0.109583924
0.21756429
0.076770475
0.853835758
0.007832846
0.992270167
0.221170991
0.100046358
0.110774418
0.040409206
0.729396755
0.511198528
0.195829407
0.900963874
0.210501797
0.137347524
0.820243519
0.063273466
0.996013239
0.820644646
0.488038097
0.257115301
0.164383719
0.414660468
0.411155597
0.021820819
0.945853266
0.79347433
0.690643932
0.433889482
0.277514317
0.700221508
0.958538669
0.399390828
0.558478991
0.423107476
0.010630804
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00012
A00013
A00013
A00013
A00013
A00013
A00013
A00013
A00014
A00014
A00014
A00014
A00014
A00014
A00014
A00014
A00014
A00014
A00014
A00014
A00014
A00014
A00014
A00014
A00015
A00015
A00015
A00015
Withdrawal
Deposit
Deposit
Withdrawal
Withdrawal
Withdrawal
Deposit
Deposit
Deposit
Withdrawal
Deposit
Withdrawal
Deposit
Deposit
Deposit
Deposit
Deposit
Deposit
Withdrawal
Withdrawal
Withdrawal
Withdrawal
Deposit
Withdrawal
Withdrawal
Withdrawal
Deposit
Withdrawal
Deposit
Withdrawal
Deposit
Deposit
Withdrawal
TRANSFER
Deposit
Deposit
Withdrawal
Withdrawal
Deposit
Withdrawal
Deposit
Deposit
Withdrawal
Withdrawal
Deposit
Withdrawal
Deposit
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
79.98
88.04
70.24
455.59
451.84
86.39
259.62
185.62
248.70
442.55
42.52
205.33
290.61
189.55
355.51
19.90
79.06
482.85
102.27
16.02
250.26
209.47
326.48
357.54
39.41
225.97
448.75
24.70
432.02
244.53
59.76
241.42
45.17
120.11
172.96
143.92
65.62
160.20
108.02
237.22
437.12
193.97
151.36
495.21
315.27
282.63
64.28
42612
42597
42608
42599
42591
42586
42593
42585
42596
42611
42595
42588
42585
42586
42600
42612
42603
42600
42592
42585
42588
42608
42592
42602
42595
42587
42590
42610
42603
42601
42593
42590
42596
42593
42610
42588
42599
42590
42600
42586
42602
42596
42594
42583
42606
42597
42589
0.938983702
0.516301208
0.500526512
0.923046146
0.363028008
0.610350977
0.343451142
0.376790077
0.207334367
0.994090161
0.679740284
0.229157382
0.442818164
0.737622779
0.589656329
0.703941212
0.354314166
0.171600906
0.596140195
0.152520155
0.073351017
0.825932058
0.650928315
0.375943756
0.182315492
0.115340435
0.427471948
0.207390976
0.877465607
0.708270397
0.229918296
0.853646524
0.078070018
0.61963879
0.010942492
0.794384233
0.076234202
0.530877261
0.907752206
0.490576293
0.81534355
0.321636687
0.376534369
0.709912019
0.456308255
0.077892714
0.329865854
A00016
A00016
A00016
A00016
A00017
A00017
A00017
A00017
A00018
A00018
A00018
A00018
A00019
A00019
A00019
A00020
A00020
A00020
A00020
A00020
A00020
A00021
A00021
A00021
A00021
A00022
A00022
A00022
A00022
A00023
A00023
A00023
A00023
A00023
A00023
A00023
A00023
A00023
A00023
A00023
A00023
A00024
A00024
A00024
A00024
A00024
A00024
Deposit
Withdrawal
Withdrawal
Deposit
Deposit
Deposit
Deposit
Deposit
Deposit
Withdrawal
Deposit
Withdrawal
Deposit
Deposit
Deposit
Withdrawal
Deposit
Withdrawal
Withdrawal
Withdrawal
Deposit
Withdrawal
Withdrawal
Withdrawal
Withdrawal
Deposit
Deposit
Deposit
Withdrawal
Withdrawal
Withdrawal
Deposit
Withdrawal
Withdrawal
Deposit
Deposit
Deposit
Deposit
Deposit
Deposit
Withdrawal
Deposit
Deposit
Withdrawal
Deposit
Withdrawal
Deposit
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
45.22
338.93
142.73
62.44
218.61
131.87
406.89
309.74
46.46
268.48
174.19
435.39
10.45
493.64
262.90
182.83
169.76
450.99
241.83
289.46
17.71
230.02
374.93
246.15
365.85
224.05
118.05
422.68
255.88
376.50
356.49
464.25
136.45
455.39
316.33
271.67
224.28
145.83
396.72
491.17
317.46
207.64
455.87
303.40
393.48
316.38
389.30
42606
42602
42611
42596
42595
42608
42598
42591
42609
42585
0.855484869
0.572424863
0.098223303
0.961010018
0.909455664
0.774509297
0.22589749
0.453860344
0.056761955
0.993937415
A00024
A00024
A00024
A00025
A00025
A00025
A00026
A00026
A00026
A00026
TRANSFER
Withdrawal
Deposit
Deposit
Deposit
Withdrawal
Deposit
Withdrawal
Deposit
Deposit
$
$
$
$
$
$
$
$
$
$
20.29
2,446.01
216.64
261.82
489.91
291.38
448.54
386.43
195.91
248.12
Best Bank
Loan Analysis
Amount of Loan
APR
Years
Payments / Year
Payment
$
10,000.00
8.00%
2
12
$452.27
Amorization Table
Beginning Balance Interest Paid Principle Paid
Period
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Ending Balance
nk
ysis
Data Table
$ 9,000.00 $ 10,000.00 $ 11,000.00 $ 12,000.00
4.0%
4.5%
5.0%
5.5%
6.0%
6.5%
7.0%
7.5%
8.0%
8.5%
9.0%
Office 2016 – myitlab:grader – Instructions
Excel Project
Pre-Test 2
Project Description:
For the purposes of this assessment, you will provide data analysis services as a consultant for The Best Bank
Company. The starter file is a spreadsheet containing mostly raw data. You must follow the instructions to
analyze and format the data so that it can be easily understood and used by management to make decisions.
Following the instructions in order is important, particularly within each worksheet. Remember to use cell
references where possible, and avoid hardcoding values.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Points
Possible
Instructions
1
On Sheet1, change the name of the worksheet to Loans , and change the tab color to Red.
2.000
2
Insert 2 new columns to the left of Column E. In E4:F4, type Loan Amount and Down Pay % ,
in that order.
2.000
3
Autofit any columns as necessary. Column width does not count against you on this
assessment, so you are free to set columns as needed.
0.000
4
Bold the column headers in row 4, and add a thin bottom border to cells A4:K4.
2.000
5
Format the values under Cost of Home and Down Pay with Accounting Number Format.
2.000
6
Under Loan Amount, calculate the amount of the loan for the first customer, Mr. Al-Asiri, by
subtracting his down payment from his cost of home. Copy the function down to complete the
calculation for the other customers.
4.000
7
To determine if any customers need mortgage insurance, you’ll need to calculate the
percentage of the total that each customer has paid upfront. Under Down Pay %, divide
each customer’s down payment by the cost of their home. Format the values as a percentage
with 1 decimal place.
5.000
8
In H4:K4, type the following column headers: APR , Monthly Payment , Monthly PMI , and
Total Payment . Bold them if they are not bold automatically.
3.000
9
Examine the information provided in cells A26:C36. Refer to this information in your
calculations where possible rather than hardcoding values. Cells A27:B30 are a table for use
with lookup functions, so create a named range for those cells called lookup_table .
4.000
Under APR, use the appropriate lookup function to look up the appropriate APR for each
customer in the lookup_table range, based on their Loan Years. Use exact match.
4.000
10
Updated: 08/28/2016
1
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
Under Monthly Payment, use the PMT function to calculate each customer’s monthly
payment. Place a negative sign in the function where appropriate to make the result value a
positive number.
11
12
4.000
Remember that the loan years and APR are annual, and will need to be converted to monthly
values in the function. Remember to reference the information provided on the worksheet in
your function, and use relative and absolute references as appropriate.
Customers that put less than 20% down on their home will need PMI (mortgage insurance).
Create an IF function in cell J5 to determine if the first customer needs PMI. If his down
payment is less than 20%, he’ll need the insurance. It is calculated by multiplying his loan
amount by the PMI Monthly Rate. If false, the PMI is 0.
4.000
Refer to the information provided on the worksheet when constructing your function. Use
relative and absolute references as necessary.
13
Under Total Payment, add each customer’s monthly payment with their monthly PMI to
determine the total amount they pay each month.
2.000
14
Format the APR values in column H as percentage with 1 decimal place. Format the remaining
dollar values with accounting number format.
3.000
15
In cell H27, use the COUNTIF function to count the number of 15 year loans in the dataset.
In cells H28:H30, count the number of 20, 25, and 30 year loans.
4.000
16
Switch to Sheet2. This is a large dataset of banking transactions queried from the company’s
database. Your company has a policy of flagging any withdrawals of $2000 or more for
additional review. Each transaction type is explained in the legend at the top.
2.000
Begin by renaming the worksheet Transactions and changing the tab color to Yellow.
17
18
Convert the dataset, beginning in A7, into an excel table. The Trans_Date field is a date
value; apply the short date format to those values. The Trans_Time field is a time value;
apply the time format to those values.
In cell F7, you will create a nested function to determine if the first transaction needs to be
flagged. IF a transaction type is a withdrawal AND greater than or equal to $2000, it should
cause the cell to display the word “FLAG” . If that’s false, the cell should remain blank.
2.000
8.000
Use structured references when creating the function. When referencing cells outside the
table, use relative and absolute references as appropriate.
19
Create a conditional formatting rule for the Trans_Status table column, using “A text that
contains…” Any cell containing the word FLAG should have light red fill with dark red text.
3.000
20
Create a copy of the Transactions worksheet, placing it before Sheet3. Rename the new
worksheet Transfers , and set its tab color to Orange.
2.000
Updated: 08/28/2016
2
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Excel Project
Step
Instructions
Points
Possible
21
On the Transfers worksheet, under the Trans_Type field, use the table’s built-in text filter
(not the checkboxes) to make sure that only records that equal TRANSFER are displayed. Sort
the records by Trans_Date, oldest to newest. Rename the table TransTbl2 .
4.000
22
Rename the Sheet3 worksheet What_If , and change its tab color to Blue.
2.000
23
On the What_If worksheet, use Goal Seek to determine what interest rate would result in a
payment of $440.00 . Accept the solution and leave the values in place.
4.000
Complete the amortization table beginning in A12. The beginning balance in B12 should
reference the Amount of Loan in the loan information provided on the worksheet.
Use IPMT and PPMT functions for Interest Paid and Principle Paid. Place a negative sign in the
functions where appropriate to make the result values a positive number.
24
8.000
Subtract Principle Paid from the Beginning Balance to produce the Ending Balance.
25
Beginning Balance in B13 should reference the Ending Balance in E12. Use relative and
absolute references as appropriate.
Under the Data Table section, you will determine what the Payment will be using different
loan amounts and APRs. In G11, insert a reference to the Payment of the loan. Give the cell
custom formatting to cause it to display as blank. Complete the 2-variable data table using the
Loan Amount and APR values provided on the worksheet as input cells.
8.000
26
Select ranges G12:G22 and K12:K22. Navigate to Recommended Charts in the ribbon, and
choose a Line chart. Move the chart below the data table, into range G24:K35.
4.000
27
Give the chart a title of Monthly Payment by APR . Edit the vertical axis, giving it a major units
of 10.0 .
4.000
28
Use Format painter and merge & center to format the titles on all worksheets. They should
match the title in cells A1:A2 of the What_if worksheet. Extend the titles to the edge of the
content on the worksheets, but no further.
4.000
29
Review your work and submit the assessment to the MyITLab assignment that it was
downloaded from.
0.000
Total Points
Updated: 08/28/2016
3
100.000
Current_Instruction.docx
…
Purchase answer to see full
attachment
