Rubic and spreadsheet attached
20170426143249copy_of_unit_8_data_file.xlsm
20170426143043it153_unit8_assignment__1_.pdf
Unformatted Attachment Preview
Richie’s Subprime
Richie’s Subprime
Minimal Down Payment Is Our Goal
Price
Down Payment
Financing Amount
Year
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$75.000,00
$4.500,00
$70.500,00
Beginning
Balance
$70.500,00
$68.315,64
$65.908,52
$63.255,94
$60.332,85
$57.111,67
$53.562,00
$49.650,35
$45.339,80
$40.589,68
$35.355,14
$29.586,81
$23.230,23
$16.225,43
$8.506,31
Rate
Years
Monthly Payment
Ending
Balance
$68.315,64
$65.908,52
$63.255,94
$60.332,85
$57.111,67
$53.562,00
$49.650,35
$45.339,80
$40.589,68
$35.355,14
$29.586,81
$23.230,23
$16.225,43
$8.506,31
$0,00
Subtotal
Down Payment
Total Cost
Page 1
Paid On
Principal
$2.184,36
$2.407,12
$2.652,59
$2.923,09
$3.221,18
$3.549,67
$3.911,65
$4.310,55
$4.750,13
$5.234,53
$5.768,34
$6.356,57
$7.004,80
$7.719,13
$8.506,31
$70.500,00
9,75%
15
$746,85
Interest
Paid
$6.777,85
$6.555,09
$6.309,62
$6.039,12
$5.741,03
$5.412,54
$5.050,56
$4.651,66
$4.212,08
$3.727,68
$3.193,87
$2.605,63
$1.957,41
$1.243,08
$455,90
$63.933,12
$4.500,00
$138.933,12
Unit 8: Assignment – 65 points
Unit outcomes addressed in this unit:
Use functions and formulas to determine data outcomes.
Course outcome(s) practiced in this unit:
GEL-2.3: Solve real-world problems using mathematical skills.
Scenario:
Richie’s Subprime provides financing for customers who lack the usual down payments for a
loan. The chief financial officer has asked you to automate the entry of financing data into a
worksheet that computes the monthly payment and total cost of a loan.
Preliminary Steps:
1. Start Excel. If the Developer tab does not display on the Ribbon, complete the
following steps:
a. Select File | Options. The Excel Option dialog box appears.
b. Click on Customize Ribbon. The Customize Ribbon dialog box appears.
c. Check the Developer Option check box in the right pane.
d. Press the OK button. The Developer tab now appears on the Ribbon.
2. Enable macros by completing the following steps:
a. Click on the Developer tab.
b. Click the Macro Security button. The Trust Center dialog box appears.
c. Click “Enable all macros.”
d. Press the OK button.
Part 1 Instructions:
1. Open the Unit 8 Assignment data file located in Doc Sharing.
2. Remember to save your files as an Excel Macro-Enabled Workbook file type.
3. Set up a macro using VBA:
a. Click the Developer tab on the Ribbon.
b. Click the Insert button.
c. Click the Command Button (column 1, row 1) in the ActiveX Controls area.
d. Draw the button at the top of column F as shown in the figure below.
e. Select the Command Button control you created on the worksheet.
f. Click the Properties button on the Ribbon.
g. Change the following properties, if necessary.
i. Caption = Financing Data
ii. Font = Bold.
iii. Print Object = False.
h. Close the Properties window.
i. Click the View Code button on the Ribbon, while the Command
j.
Button control is selected.
Enter following procedure code for the Command Button.
Range(“C3:C4”).ClearContents. Range(“F3:F4”).ClearContents.
Range(“G18”).Select.
Range(“Price”).Value = InputBox(“Total purchase price?”, “Enter”).
Range(“Down_Payment”).Value = InputBox(“Down payment?”, “Enter”).
Range(“Rate”).Value = InputBox(“Annual interest rate?”, “Enter”).
Range(“Years “).Value = InputBox(“Length of loan in years?”, “Enter”).
Note: Be sure to check your code carefully for accuracy and ensure that your cells are
named accordingly. See below.
4. Create two copies of the worksheet within the workbook. Rename the copied
worksheets as Data Set A and Data Set B.
5. Use the Command to determine the monthly payment for the following loan
financing data in the respective worksheets for each data set:
a. Total Price Paid = $45,000; Down Payment = $3500; Rate = 8.75%;
and Years = 6. Monthly Payment should be $742.92.
b. Total Price Paid = $25,500; Down Payment = $4,000; Rate = 15.75%; and
Years = 11. Monthly Payment should be $343.65.
6. Save the workbook as Unit_8_Assignment_Your_Name.
Part 2 Instructions:
Perform the following tasks to add a macro to the workbook and add a button to the
worksheet that executes the macro: Open the workbook, if not open, and select
Richie’s Subprime worksheet.
1. Confirm that all macros are enabled:
a. Click the Macro Security button on the Developer Ribbon. The Trust
Center dialog box is displayed,
b. Ensure that the “Enable all Macros” option has been selected.
2. Create a macro that prints the formulas version of the worksheet by doing the
following:
a. Click the Record Macro button on the Developer tab on the Ribbon.
b. When the Record Macro dialog box appears, name the macro
PrintFormulasVersion, assign the short cut key CTRL+Shift+V, add your
name in the description box, and store the macro in this workbook.
c. Click OK to start the macro recording process.
3. Record the macro:
a. Press CTRL+ ACCENT mark (‘).
b. Click the Page Layout tab on the Ribbon.
c. Click the Scale to Fit button arrow on the Ribbon.
d. When the Page Setup dialog box appears:
i. Click Landscape in the area.
ii. Click Fit to in the Scaling area.
iii. Click the Print button in the Page Setup dialog box.
iv. Click the OK button in the Print dialog box.
e. Press CTRL+ACCENT mark (‘).
f. Click the Scale to Fit Dialog Box Launcher on the Ribbon.
g. Click Portrait in the orientation area.
h. In the scaling area, click Adjust to and type 100 in the Adjust to box.
i. Click the OK button.
j. Click the Stop Recording button on the Developer tab on the Ribbon.
4. Create a Print button on Richie’s Subprime worksheet by doing the following:
a. Click the Insert button on the Developer Ribbon and select the Forms
b.
c.
d.
e.
f.
Control Button (column 1, row 1).
Draw the button to the right of the spreadsheet data. The Assign
Macro dialog box appears.
Click on the PrintFormulasVersion macro.
Press the OK button.
Right click on the button and select Edit Text.
Change “Button 1” to “Print.”
5. Delete all blank worksheets from the workbook.
6. Save the workbook.
7. Save as Unit 8_Assignment_Your_Name and submit to the Unit 8 Dropbox.
Unit 8 Assignment grading rubric = 65 points
Assignment Requirements
1. Created the ActiveX Command Button (5) and changed
properties as specified. (5)
2. Coded the Command Button control properly.
Points
Possible
0–10
0–2
Range(“C3:C4”).ClearContents.
0–2
Range(“F3:F4”).ClearContents. Range(“G18”).Select.
0–2
Range(“Price”).Value = InputBox(“Total purchase
price?”, “Enter”).
Range(“Down_Payment”).Value = InputBox(“Down
payment?”, “Enter”).
Range(“Rate”).Value = InputBox(“Annual interest
rate?”, “Enter”).
Range(“Years “).Value = InputBox(“Length of loan in
years?”, “Enter”).
Checked your code carefully for accuracy and ensured that
your cells are named accordingly.
3. Created two copies of the Richie’s Prime Time worksheet
within the workbook, (4) renamed as Data Set A (2) and
Data Set B. (2)
4. Used the Macro Button(s) to determine the monthly
payment for the following loan financing data in the
respective worksheets for each data set: (a) Total Price
Paid = $45,000; (1) Down Payment = $3500; (1) Rate =
8.75%; (1) and Years = 6; (1) (b) Total Price Paid =
$25,500; (1) Down Payment = $4,000; (1) Rate = 15.75%
(1) and Years = 11. (1) The resulting monthly payment
should be (a) $742.92 (1) and (b) $343.65. (1)
5. Setup the Record Macro button.
a. When the Record Macro dialog box appears,
name the macro PrintFormulasVersion (1),
assign the short cut key CTRL+V, (1) and add
your name in the description box and store the
macro in this workbook. (1)
6. Recorded the following to become a macro:
a. Press CTRL+ ACCENT mark (‘). (1)
b. Click the Page Layout tab on the Ribbon. (1)
c. Click the Scale to Fit button arrow on the Ribbon.
(1)
d. When the Page Setup dialog box appears
i. Click Landscape in the area. (1)
ii. Click Fit to in the Scaling area. (1)
iii. Click the Print button in the Page Setup
dialog box. (1)
0–2
0–2
0–2
0–2
0–2
0–8
0–10
0–3
0–12
Points
Earned
iv. Click the OK button in the Print dialog box.
e. Press CTRL+ACCENT mark (‘). (1)
f. Click the Scale to Fit Dialog Box Launcher on the
Ribbon. (1)
g. Click Portrait in the orientation area. (1)
h. In the scaling area, click Adjust to and type 100 in
the Adjust to box. (1)
i. Click the OK button. (1)
•
Click the Stop Recording button on the
Developer tab on the Ribbon. (1)
6. Added a print button to the Richie’s Subprime
worksheet by doing the following:
a. Click the Insert button on Developer Ribbon and
select the Command Button (column 1, row 1).
(1)
b. Draw the button to the right of the spreadsheet
data. (1) The Assign Macro dialog box appears.
c. Click on the PrintFormulasVersion macro. (1)
d. Press the OK button. (1)
e. Right click on the button and select Edit Text. (1)
f. Change “Button 1” to “Print. (1)
Total Points
0–6
0–65
…
Purchase answer to see full
attachment
Recent Comments