1 Open the file Excel_7E_Condo_Loan_Advertising.xlsx downloaded with this project. 2 Display the Condo Purchase workshee
Posted: Fri May 20, 2022 5:09 pm
1
Open the
file Excel_7E_Condo_Loan_Advertising.xlsx downloaded
with this project.
2
Display the Condo Purchase worksheet. In cell B5, insert the PMT
function using the data from the range B2:B4—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 payment of $6,250.
3
Click cell B5, and then use Goal Seek to change the amount of
the loan so that the payment goal
is 6250Then, in cell A7,
type Option #1 Reduce the Loan
Use the Format Painter to apply the cell style 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. If necessary, press ESC to cancel the moving border.
4
Click cell B5, and then use Goal Seek to change the period of
the loan (of the original data) so that the payment
is 6250 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.
If necessary, press ESC to cancel the moving border. Click cell A1,
and then Save your workbook.
5
Display the Payment Table worksheet, in the range A2:B4, enter
the following row titles and data. (The Currency
[0] cell style is already applied to cell B2.)
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.
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.
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. Click cell A1.
10
Display the Advertising Costs by Quarter sheet. 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
11
Display the Name Manager, click Digital_Costs, and then in the
Refers to box, edit as necessary so that the end of the range is
cell E15. Select the Billboard_Costs and then Edit the name
to Outdoor_CostsClick cell A1, and then Save
your workbook.
12
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. To cell B9, apply
the Total cell style. Click cell A1, and then click Save.
13
At the bottom of the workbook window, right-click any sheet tab
name, and then click Select All Sheets. With the four worksheets
grouped, from the Page Layout tab, display the Page Setup dialog
box, and then insert a Custom Footer in the left section that
includes the file name. Click the Margins tab, and then center the
worksheets Horizontally on the page. Click the Payment Table
worksheet, and then set the Orientation of this sheet to
Landscape.
14
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 number. On the left, click Save.
15
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 and
close the file, and then submit for grading.
Open the
file Excel_7E_Condo_Loan_Advertising.xlsx downloaded
with this project.
2
Display the Condo Purchase worksheet. In cell B5, insert the PMT
function using the data from the range B2:B4—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 payment of $6,250.
3
Click cell B5, and then use Goal Seek to change the amount of
the loan so that the payment goal
is 6250Then, in cell A7,
type Option #1 Reduce the Loan
Use the Format Painter to apply the cell style 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. If necessary, press ESC to cancel the moving border.
4
Click cell B5, and then use Goal Seek to change the period of
the loan (of the original data) so that the payment
is 6250 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.
If necessary, press ESC to cancel the moving border. Click cell A1,
and then Save your workbook.
5
Display the Payment Table worksheet, in the range A2:B4, enter
the following row titles and data. (The Currency
[0] cell style is already applied to cell B2.)
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.
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.
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. Click cell A1.
10
Display the Advertising Costs by Quarter sheet. 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
11
Display the Name Manager, click Digital_Costs, and then in the
Refers to box, edit as necessary so that the end of the range is
cell E15. Select the Billboard_Costs and then Edit the name
to Outdoor_CostsClick cell A1, and then Save
your workbook.
12
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. To cell B9, apply
the Total cell style. Click cell A1, and then click Save.
13
At the bottom of the workbook window, right-click any sheet tab
name, and then click Select All Sheets. With the four worksheets
grouped, from the Page Layout tab, display the Page Setup dialog
box, and then insert a Custom Footer in the left section that
includes the file name. Click the Margins tab, and then center the
worksheets Horizontally on the page. Click the Payment Table
worksheet, and then set the Orientation of this sheet to
Landscape.
14
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 number. On the left, click Save.
15
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 and
close the file, and then submit for grading.