Microsoft Office Tutorials and References
In Depth Information
Make It Right
Extend Your Knowledge continued
1. Enter the data table title and subtitle as shown in cells I1 and I3 in Figure 4–67. Change the width
of column H to 0.50 characters. Merge and center the titles over columns I through S. Format the
titles as shown using the Title cell style for both the title and subtitle, a font size of 22 for the title,
and a font size of 16 for the subtitle. Change the column widths of columns I through S to 11.00
2. For a two–input data table, the formula you are analyzing must be assigned to the upper–left cell in
the range of the data table. Cell C14 contains the future value formula to be analyzed. Therefore,
enter =C14 in cell I4.
3. Use the ﬁ ll handle to create two lists of percents (a) 3.00% through 12.00%, in increments of 0.50%
in the range I5:I23; and (b) 3.00% through 7.50% in increments of 0.50% in the range J4:S4.
4. Select the range I4:S23. Click the Data tab on the Ribbon and then click the What–If Analysis
button on the Ribbon. Click the Data Table command on the What–If Analysis gallery. When Excel
displays the Table dialog box, enter c5 (employee percent invested) in the Row input cell box and c8
(expected annual return) in the Column input cell box. Click the OK button to populate the table.
5. Format the two–input data table as shown in Figure 4–67.
6. Use conditional formatting to change the format of the cell in the two–input data table that is equal
to the future value in cell C14 to white bold font on a red background.
7. Protect the worksheet so that the user can select only unlocked cells (C3:C6 and C8:C9).
8. Change the document properties as speciﬁ ed by your instructor. Change the worksheet header
with your name, course number, and other information requested by your instructor. Change the
print orientation to landscape. Print the worksheet using the Fit to option. Print the formulas
version of the worksheet.
9. Save the workbook using the ﬁ le name Extend 4–1 401(k) Planning Sheet Complete.
Analyze a workbook and correct all errors and/or improve the design.
Functions, Custom Borders, Cell Names, What–If Analysis, and Protection
Instructions: Start Excel. Open the workbook Make It Right 4–1 Financial Calculator. See the inside
back cover of this topic for instructions for downloading the Data Files for Students, or see your
instructor for information on accessing the ﬁ les required for this topic. Correct the following design
and formula problems so that the worksheet appears as shown in Figure 4–68.
1. The worksheet is protected with no unprotected cells. Unprotect the worksheet so that the
worksheet can be edited.
2. Correct the Monthly Payment formula in cell C7 and the Total Interest formula in cell C8. The
monthly payment should equal $2,078.97 and the total interest should equal $419,214.32.
3. Change the thick box border to a dark red thick box border. Change the thick border separating
columns B and C to a dark red light border.
4. Use Goal Seek to change the down payment in cell C3 so that the monthly payment is $1,850.00 as
shown in Figure 4–68.
5. Name the range B1:C9, Loan_Calculator.
6. Assign the names in column E to the adjacent cells in column F. Edit the formulas in cells F5 and
F6 and change the cell references to their corresponding names.