GO16_XL_CH04_GRADER_4E_HW – Condo Loan 1.1

Step

Instructions

Points Possible

1

Start Excel. Download, save, and open the workbook named go16_xl_ch04_grader_4e_hw.xlsx.

0

2

Display the Condo Purchase worksheet. In cell B5, insert the PMT function using the data from the range B2:B5—be sure to divide the interest rate by 12 multiply the years by 12 and display the payment as a positive number. The result, $6,598.44, is larger than the maximum desired payment of $6,250.

10

3

Use Goal Seek to change the amount of the loan so that the payment is $6,250. Then, in cell A7, type Option #1 Reduce the Loan To cell A1, apply the Title cell style, and then Copy the format from cell A1 to cell A7. Copy the range A2:B5, and then Paste the Values & Number Formatting to cell A8. In cell B2, type 615000 to restore the original loan amount.

7

4

Use Goal Seek to change the period of the loan (of the original data) so that the payment is $6,250. Then, in cell A13, type Option #2 Increase Years Format the cell the same as cell A7. Copy the range A2:B5, and then Paste the Values & Number Formatting to cell A14. Display the value in cell B15 with two decimal places, and then in cell B3, type 10 to restore the original value. Save your workbook.

4

5

Display the Payment Table worksheet, in the range A2:B4, enter the following row titles and data. Amount of Loan $615,000 Period (months) 120 Interest Rate (per year) 5.25%

5

6

In cell C8, type 60—the number of months in a 5-year loan. In D8, type 120—the number of months in a 10-year loan. Fill the series through cell H8. Apply Bold and Center to the range C8:H8.

10

7

Beginning in cell B9, enter varying interest rates in decrements of 0.5% beginning with 7.5% and ending with 4.0% If necessary, format all the interest rates with two decimal places, and then apply Bold and Center to the range B9:B16.

10

8

In cell B8, enter a PMT function using the information in cells B2:B4. Be sure that you convert the interest rate to a monthly rate and that the result displays as a positive number.

5

9

Create a Data table in the range B8:H16 using the information in cells B2:B4 in which the Row input cell is the Period and the Column input cell is the Interest rate. Copy the format from cell B8 to the results in the data table. Format cell D16 with the Note cell style as the payment option that is close to but less than $6,250 per month.

10

10

Display the Advertising Costs by Quarter worksheet, and then apply the Currency [0] cell style to the range B6:E6 and to the range B18:E18. Apply the Comma [0] cell style to the range B7:E17, and then apply the Total cell style to the range B18:E18.

9

11

Name the following ranges: B6:E10 Newspaper_Costs B11:E14 Digital_Costs B15:E16 Magazine_Costs B17:E17 Billboard_Costs Insert a new row 15. In cell A15, type Business Podcasts In cell B15, type 12500 In cell C15, type 11525 In cell D15, type 14455 In cell E15, type 13009

7

12

Display the Name Manager, click Digital_Costs, and then change cell E14 to E15. Select the Billboard_Costs and then Edit the name to Outdoor_Costs Save your workbook.

7

13

Display the Annual Advertising Costs sheet. In cell B5, type =sum(N and sum the values using the appropriate range name in the displayed list of functions. Repeat for the other named ranges. From the Formulas tab, in the Function Library group, use AutoSum to sum all the costs. Apply Currency [0], Comma [0], and Total cell styles to the appropriate cells.

10

14

In the sheet tab row, right-click any sheet tab, and then click Select All Sheets. Display the Page Setup dialog box, click the Margins tab, and then under Center on page, click Horizontally. Click the Header/Footer tab, click Custom Footer, and then in the Left section, insert the File Name. Click OK two times. Ungroup the sheets, display the Payment Table sheet. Set the Orientation of this sheet to Landscape and set the Scale to 85%.

5

15

Display Backstage view, click Show All Properties. On the list of Properties, in the Tags box, type payment table, advertising costs In the Subject box, type your course name and section #. Under Related People, be sure that your name displays—edit if necessary. On the left, click Print. Under Settings, click the Print Active Sheets arrow, and then click Print Entire Workbook. At the bottom of the window, click Next Page to scroll through the worksheets. On the left, click Save.

1

16

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Condo Purchase, Payment Table, Advertising Costs by Quarter, Annual Advertising Costs. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

0

Total Points

100