RELIABLE PAPERS ONLY PLEASE

Unit 7    [IT153: Spreadsheet Applications] 

 

GEL Assignment and Assignment 7 Details and Rubrics

There are two Assignments due in Unit 7.

1. GEL 2.3 Assignment 2. Assignment 7

Note: you will be turning in two assignments for this unit; the regular Assignment 7, plus the GEL-2.3 Assignment.

 

Unit 7 GEL 2.3 Assignment

Outcomes addressed in this activity:

Course outcome(s): GEL-2.3: Solve real-world problems using mathematical skills.

Instructions

Sandy worked overtime this past week and would like to know what her Gross and Net Pay would be. Please help Sandy calculate this using the following given data. The policy of her workplace is that she will earn time and a half for any hours worked over 40.

Pay Rate $10 / Hour

Federal Tax Rate 20%

Hours Worked 50 State Tax Rate 10%

FICA 5%

Using the Excel Application or a Calculator calculate Sandy’s Gross Pay and Net Pay. Show all detail, formulas and steps you used to arrive at your answer. Submit your GEL 2.3 Assignment to the GEL 2.3 Dropbox by the end of the unit.

Unit 7    [IT153: Spreadsheet Applications] 

  The rubric assesses your ability to solve real-world problems using mathematical skills.

All deliverables should be professionally formatted and should be free of spelling errors. Points deducted from the grade for each error are at your instructor’s discretion.

Unit 7 GEL 2.3 rubric – 30 points

Not Assessed

No work was received by the instructor.

No Progress

Student work demonstrates no understanding or progress towards achievement of this outcome.

Introductory

Student work demonstrates no ability to solve real-world problems using mathematical skills.

Emergent

Student work demonstrates minimal ability to solve real-world problems using mathematical skills.

Practiced Student work demonstrates adequate ability to solve real-world problems using mathematical skills.

Proficient

Student work demonstrates proficient ability to solve real-world problems using mathematical skills.

Mastery

Student work demonstrates superior ability to solve real-world problems using mathematical skills.

Unit 7    [IT153: Spreadsheet Applications] 

 

Assignment 7 (2 of 2)

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. Be sure to review the assignment rubric below before you begin.

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 Developers 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 7 Assignment data file located in Doc Sharing.

2. Save as Assignment 7 – Your name and 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.

Unit 7    [IT153: Spreadsheet Applications] 

 

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. PrintObject = False.

h. Close the Properties window. i. Click the View Code button on the Ribbon, while the Command Button control is

selected. j. 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.

Unit 7    [IT153: Spreadsheet Applications] 

 

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.

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 by: 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.

Unit 7    [IT153: Spreadsheet Applications] 

 

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 Control Button (column 1, row 1).

b. Draw the button to the right of the spreadsheet data. The Assign Macro dialog box appears.

c. Click on the PrintFormulasVersion macro. d. Press the OK button. e. Right click on the button and select Edit Text. f. Change “Button 1” to “Print.”

5. Delete all blank worksheets from the workbook.

6. Save the workbook.

Submit the saved workbook into the Unit 7 Dropbox by the end of Unit 7. Your workbook should consist of 3 worksheets named Richie’s Subprime, Data Set A and Data Set B.

All deliverables should be professionally formatted and should be free of spelling errors. Points deducted from the grade for each error are at your instructor’s discretion.

Unit 7    [IT153: Spreadsheet Applications] 

 

Assignment 7 Grading Rubric = 50 Points

Assignment Requirements Points possible

Points earned

1. Created the ActiveX Command Button and changed properties as specified.

0 - 5

2. Coded the Command Button control properly. 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"). Checked your code carefully for accuracy and ensured that your cells are named accordingly.

0 - 10

3. Created two copies of the Richie’s Prime Time worksheet within the workbook, renamed as Data Set A and Data Set B.

0 - 5

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; Down Payment = $3500; Rate = 8.75%; and Years = 6; (b) Total Price Paid = $25,500; Down Payment = $4,000; Rate = 15.75% and Years = 11. The resulting Monthly Payment should be (a) $742.92 and (b) $343.65.

5. Saved the workbook.

0 - 5

6. Setup the Record Macro button. a. When the Record Macro dialog box appears, name the

macro PrintFormulasVersion, assign the short cut key CTRL+V, and add your name in the description box and store the macro in this workbook.

0 - 5

b. Recorded the following to become a macro: i. Press CTRL+ ACCENT mark (‘). ii. Click the Page Layout tab on the Ribbon.

0 - 5

Unit 7    [IT153: Spreadsheet Applications] 

 

iii. Click the Scale to Fit button arrow on the Ribbon. iv. When the Page Setup dialog box appears v. Click Landscape in the area. vi. Click Fit to in the Scaling area. vii. Click the Print button in the Page Setup dialog box. viii. Click the OK button in the Print dialog box. ix. Press CTRL+ACCENT mark (‘). x. Click the Scale to Fit Dialog Box Launcher on the

Ribbon. xi. Click Portrait in the orientation area. xii. In the scaling area, click Adjust to and type 100 in

the Adjust to box. xiii. Click the OK button. xiv. Click the Stop Recording button on the Developer

tab on the Ribbon.

7. 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).

b. Draw the button to the right of the spreadsheet data. The Assign Macro dialog box appears.

c. Click on the PrintFormulasVersion macro. d. Press the OK button. e. Right click on the button and select Edit Text. f. Change “Button 1” to “Print.

0 - 4

8. Saved your Workbook.

0 - 1

9. Ensured your assignment shows a clear understanding of the concepts covered in the Unit as applied in this assignment from the Required Readings and Step-by-Step Examples in the text. Ensure that the work presented reflects appropriate use of Microsoft Excel's features, Functions and Professional Formatting.

0 - 10

Submitted late (point deductions as per policy as stated in the Syllabus)

TOTAL POSSIBLE POINTS: 0 - 50

Points deducted for spelling or formatting

Adjusted total points