In Practice 2024 Integrated Capstone Project 6 Part B - Excel
SIMnet Excel project instructions
· Excel Projects Help
In Practice 365/2024 Integrated Capstone Project 6 Part B - Excel Steps to complete this project:
Mark the steps as checked when you complete them.
1.Open the InterestRates-Excel-ICP-6b Excel workbook start file.
The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.
2.Enable content in the security warning.
3.Format and rename the worksheet.
4.Rename the worksheet tab InterestRates.
5.Insert a row above the first row and type Historic 30-Year Mortgage Interest Rates as the title in cell A1.
Merge and center the title first row across columns A:C.
6.Change the width of columns A, B, and C to 12.
Right-align column headings in columns B and C.
7.Apply bold format to the title and column headings.
8.Apply Percent number format with 2 decimal places to the rate figures in column B.
9.Apply Percent number format with 1 decimal place to the points figures in column C.
10.Insert a formula to average data and format the worksheet.
11.Type Averages in cell A14 and apply bold formatting to this label.
12.Insert an AVERAGE function in cell B14 to average the historic interest rates.
13.Copy this AVERAGE function to C14.
14.Apply bold format to the average figures in row 14 and make sure the average figures match the decimal places of the figures above them.
15.Insert a chart object and apply a chart style.
16.Select A2:C13 and insert a line chart as an object in the same worksheet.
17.Click the Chart launcher arrow to open the Insert Chart dialog box.
18.Select the Line chart in the Line category on the All Charts tab and select the second Line chart option.
19.Change the style to Style 7.
20.Place and size the chart to fit in cells E1 through K14.
21.Delete the place holder for the chart title.
22.Format the horizontal axis with no line (there should not be a line above years in the horizontal axis)
23.Save the workbook and leave it open (Figure 2).
Images
Figure 2 ICP 6b InterestRates worksheet completed
24.Import data from an Access database query into a new worksheet in Excel.
Get Data from the Integrated Project 6a Access database [Data tab, Get & Transform Data tab].
25.Select the ClientQuery and Load To a Table into a New worksheet.
26.Rename the worksheet tab MorrowListings.
27.Insert column headings and functions to calculate monthly payments.
28.Type the following in cell I1 as a two-row column heading. Use Alt+Enter to break column heading into two rows.
Monthly Payment
30-year, 4%
29.Type the following in cell J1 as a two-row column heading.
Monthly Payment
15-year, 3.5%
30.Select columns I and J and change the column width to 16.
Right align the headings and contents of columns I and J.
31.Select the first row and change the row height to 30.
32.Insert a PMT function in cell I2 to calculate the monthly payment based on the following:
4% annual interest rate with monthly payments (4%/12)
30-year loan with monthly payments (30*12)
List price of the home (B2) less a $30,000 down payment (B2-30000)
33.In cell J2, insert a PMT function to calculate the monthly payment based on the following:
3.5% annual interest rate with monthly payments
15-year loan with monthly payments
List price of the home less a $30,000 down payment
34.Change format of the dates in column A to Short Date.
35.Change number format of the values in column B to Currency and 0 decimal places.
36.Save and close the workbook. (Figure 3).
Images
Figure 3 ICP 6b MorrowListings worksheet completed
37.Upload and save your completed InterestRates-Excel-ICP-6b Excel workbook.
38.Submit project for grading.
IMPORTANT: This is Part B of a 4 part project. After completing this part, go to the project: In Practice 365/2024 Integrated Capstone Project 6 Part C.