Microsoft Office Tutorials and References
In Depth Information
General Project Decisions
While creating an Excel worksheet, you need to make several decisions that will determine the
appearance and characteristics of the ﬁ nished worksheet. As you create the worksheet required
to meet the requirements shown in Figure 4–2, you should follow these general guidelines:
1. Create and format the Loan Payment Calculator section of the worksheet. The Loan
Payment Calculator section requires a compact and understandable interface where both
loan ofﬁ cers and customers can view the results of possible loan situations quickly. This
section requires complex ﬁ nancial calculations such as present value and future value of
a loan. Excel’s ﬁ nancial functions can be used to solve these problems. As with the other
two sections of the worksheet, this section of the worksheet should be formatted to
make it distinct from the other two sections of the worksheet.
2. Create and format the Interest Rate Schedule section of the worksheet. The Interest Rate
Schedule depends on values in the Loan Payment Calculator section of the worksheet. If
those values are placed in the top row of the Interest Rate Schedule, then payment, interest,
and cost values for various interest rates can be computed in the columns in this section.
3. Create and format the Amortization Schedule section of the worksheet. The amortization
schedule relies on formulas speciﬁ ed in the requirements document (Figure 4–2). This
section of the worksheet also should include subtotals and a total to provide additional
insight to the users of the worksheet.
4. Specify and name print areas of the worksheet. As speciﬁ ed in the requirements document,
users of the worksheet require the option to print the individual sections of the worksheet.
Excel allows you to name these sections and then print the sections by name.
5. Determine which cells to protect and unprotect in the worksheet. When creating a workbook
that will be used by others, the spreadsheet designer should consider which cells another user
should be able to manipulate. For the Loan Payment Calculator, the user needs to modify
only the item, price, down payment, rate, and number of years of the loan. All other cells in
the worksheet should be protected from input by the user of the worksheet.
In addition, using a sketch of the worksheet can help you visualize its design. The sketch
of the worksheet (Figure 4–3) consists of titles, column and cell headings, location of data
values, and a general idea of the desired formatting.
As shown in the worksheet sketch shown in Figure 4–3, the three basic sections of the
worksheet are (1) the Loan Payment Calculator on the upper–left side, (2) the Interest Rate
Schedule data table on the lower–left side, and (3) the Amortization Schedule on the right
side. The worksheet will be created in this order.