Excel

this assigment have to be done in Excel. I have uploaded two documents. The first one are the instructions and the second one is the format with Excel that I have to complete with the instructions mentioned. 

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

Condo Purchase

Condo Purchase

Jesse Jewelers
Amount of Loan $615,000
Period (years) 10
Interest Rate (per year) 5.25%
Payment (per month)

Payment Table

versus Months

Rates
Loan Options for Condo Purchase:

Rates
Payment Options
Number of Monthly Payments

Advertising Costs

by Quarter

Jesse Jewelers
Advertising Costs by Quarter

13809

by Quarter

All U.S. Stores
1st Qtr 2nd Qtr 3rd Qtr 4th Qtr
Local Newspapers 14471 13809 2006
Regional Newspapers 13735 13031 15365 1692
National Newspapers 12489 12674 12927 13042
Specialty Newspapers 1648 3188 3628 1202
Online Newspapers 13827 12452 12451 14821
Internet 13342 13443 13555 14545
Email 14549 15525 16740 12448
TV 18821 20403 21211 21958
Travel Podcasts 12342 13475 15794 15948
Women’s Magazines 12308 21101 20034 19105
Men’s Magazines 18637 188357 16662 21118
Billboard Costs 12294 13418 14461 13473
Total 158462 340877 176637 151358

Annual Advertising Costs

Jesse Jewelers

Advertising Costs
Total
Annual Costs for All U.S. Stores
Newspaper_Costs
Digital_Costs
Magazine_Costs
Outdoor_Costs

Grader – Instructions Excel

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

2

0

1

6

Project

GO

16

_XL_CH0

4

_GRADER_4E_HW – Condo Loan 1.1

Project Description:

In this project, you will use a PMT function to calculate a value and then use it in a two-variable data table. You will create named ranges and use them in formulas.

Steps to Perform:

4

5

10

7

10

5

10

10

9

7

7

10

5

1

0

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 B

5

, 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,5

9

8

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

7

, 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 6

15

000 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 A

13

, 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 A

14

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

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%

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.

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.

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.

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.

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.

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

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.

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.

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

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.

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.

Total Points

100

Created On: 07/05/2019 1 GO16_XL_CH04_GRADER_4E_HW – Condo Loan 1.1

Order a unique copy of this paper

600 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
Top Academic Writers Ready to Help
with Your Research Proposal

Order your essay today and save 25% with the discount code GREEN