Want answers to the assignment Below?

Text or Whatsapp Olivia at +1 (307) 209-4351”>iLab> Step 4: Create an
Excel Application With Macro and Macro Button

·” title=”Listen to this page using ReadSpeaker”>Listen”>What’s this?

iLab Overview


You are an intern at First National Bank working
in the loan department, and your boss has asked you to prepare the monthly
“New Loan Report” for the Board of Directors. This analysis report
will clearly list and summarize all new loans for residential housing in the
past month. The summary area includes the loan statistics as labeled data in
the data file. The format of the report should be appropriate for the Board of
Directors for the First National Bank.

Be sure to watch the tutorials at the end of the Lesson



Submit your assignment to the Dropbox, located
at the top of this page. For instructions on how to use the Dropbox, read these”>step-by-step instructions.

(See the Syllabus
section “Due Dates for Assignments & Exams” for due dates.)

Submit one workbook to the Dropbox. (Note that
files containing macros have the extension, .xlsm rather than .xlsx.) When
submitting the workbook, provide a comment in the Dropbox comments area
explaining what you learned from completing this iLab activity.

File naming convention: If your name is Jane
Doe, then your file should be named very similar to: Doe_J_Week2_iLab.xlsm

Click to download the”>Grading Rubric for Lab

Required Software

Microsoft Office: Excel

Options for accessing Microsoft Excel 2013:

Use a personal copy on
your PC. You can request a copy of Microsoft Office 2013 via the Student
Software Store icon on the Course Resources Page

If you are a MAC user,
click to read the”>MAC User Information.

virtual lab to access Microsoft Excel in the virtual lab.
For additional virtual lab information and tutorials on saving files, click the
iLab icon on the Course Resources page.

Lab Steps


Be sure you have reviewed the Tutorials located
on the Lesson page before you begin the iLab.

Please do not rely solely on
the videos to complete this week’s iLab.The videos provide detailed examples,
using the Week2_BIS155_Tutorial_Loan_Calculations_Student.xlsx workbook.
Applying the examples to the loan assignment will give you both practice and
instruction what to complete.

For example, your final assignment file should
include three worksheets (Documentation, Loan Data, and Loan Calculator), each
with a corresponding example, as shown in the video tutorials.

Step 1: Open, Save,
and Document the Workbook

Download the”>Week2_iLab_FirstNatlBank_Students.xlsx
Workbook. Open the file in Excel, and save it as the file name as
describe above (i.e., Doe_J_Week2 iLab.xlxsm). [Note: You may get a popup
message that asks you to “Enable Saving”. Simply click
“Yes” to Enable and you will be able to save the file]

It is recommended that
as you progress through this assignment, that you work on what you have learned
so far, in terms of readability. Be sure to keep your worksheet professional
with easy to read column headings, shading, spacing, boxes, logos, and so on.
Remember to label your worksheets with appropriate labels (i.e., not sheet1,
sheet2, etc.).

Add a documentation
sheet that provides a spreadsheet title, author, date, and purpose. Format the
documentation sheet. The bank’s logo is dark blue, so use a color scheme that
reflects that logo. Select complementary Font and Fill colors for the
documentation sheet. Ensure that the sheet tab is named Documentation and that
the documentation sheet is the first sheet in the workbook.

Below is an example of what your documentation
page may look like. Future assignments will all be similar, when asked to
create a documentation page.

devry bis155 week 2 ilab Latest 2016 March.gif” alt=”Sample documentation page”>

If you have any questions, please post in the Q
& A Forum or contact your instructor.

Step 2: Create

Functions are used to calculate the interest
rate, down payment, monthly payment, and average selling price for each
residential home in the worksheet. You need to create a formula to determine
the down payment. Finish the calculations by using the appropriate functions to
complete the Loan Statistics summary area of the worksheet.

Use a VLOOKUP function
to determine the interest rates in column D. There is a tutorial video on
creating and using VLookup tables at the end of the Lesson page.

Calculate the down
payment by multiplying the results of a VLOOKUP function by the selling price.
Enter the formula in column E.

Calculate the amount to
be financed by subtracting the down payment from the selling price. Enter the
formula in column F.

Use a PMT function to
determine the monthly payments in column G. You should assume that the payment
is being made at the beginning of the month. Remember that the Interest Rate
being shown is an annual percentage rate (APR), yet the payments are being made
monthly. Be sure to make your adjustment to the interest rate for this. In
addition, the term of the loan is being shown in years. Be sure to adjust the
years to months in your PMT function argument.

Use appropriate formulas
and functions to calculate our loan statistics for the month.

Number of Loans

Lowest Amount Financed

Total Amount Financed

Highest Amount Financed

Step 3: Format the

Now that you have finished the calculations, you
must format the worksheet in a professional manner suitable for delivering to
the Board of Directors. Your final worksheet will look something like the
worksheet below.

devry bis155 week 2 ilab Latest 2016 March.gif”>

Format all money figures
as currency with two decimals and all percentages as percents.

Adjust columns sizes to
fit the data.

Merge and center titles
and size appropriately.

Change font color to
dark blue.

Add a small graphic
appropriate for the purpose of the worksheet.

Separate sections of the
worksheet and provide borders as appropriate.

Step 4: Create an
Excel Application With Macro and Macro Button

Add a new worksheet to
the workbook, and name it Loan calculator.

Create a user input
section that asks users to input the following:

What is the price of the

Do you wish to put any
additional money down?

Pay at the beginning of
the month?

Create an Output section
that uses the user-provided data and calculates the mortgage payments for a
15-year loan, a 20-year loan, and a 30-year loan.

Add user input
validation to ensure that users input at least $50,000 for the cost of the home
and answer Yes/No to the payment question.

Unlock the cells in
which users will input data so that you may protect the sheet to keep users
from changing other areas.

Create a Macro.

Use Insert Shapes to add
a Macro button that will allow users to clear values and start over. Name the
button Enter New Values.

Protect the worksheet,
but do NOT require a password.

Format the worksheet so
that it is easy to understand and use. The following is a sample of what your
final worksheet might look like:

devry bis155 week 2 ilab Latest 2016 March.gif”>

Save your file as an xlsm file and submit to the
Dropbox. Make sure you post a comment about what you learned when submitting
the file.

Remember This

At the end of this step, you will save the file
as a .xlsm file rather than a .xlsx file. This is the filetype that allows you
to save a file that includes a Macro.


Other Questions.

We Accept

Order your Assignment today and save 15% with the discount code ESSAYHELP