Microsoft Office Tutorials and References
In Depth Information
In the Lab
Instructions Part 1: Create a worksheet similar to the one shown in Figure 2 – 77. Include the i ve columns
of customer data in Table 2 – 6 in the report, plus two additional columns to compute a service charge
and a new balance for each customer. Assume no negative unpaid monthly balances.
Figure 2 – 77
Perform the following tasks:
1. Enter and format the worksheet title Aficionado Guitar Parts and worksheet subtitle
Monthly Accounts Receivable Balance Report in cells A1 and A2. Change the theme
of the worksheet to the Trek theme. Apply the Title cell style to cells A1 and A2. Change
the font size in cell A1 to 28 points. Merge and center the worksheet title and subtitle across
columns A through G. Change the background color of cells A1 and A2 to the Red standard
color. Change the font color of cells A1 and A2 to the White theme color. Draw a thick box
border around the range A1:A2.
2. Change the width of column A to 20.00 points. Change the widths of columns B through G to
12.00 points. Change the heights of row 3 to 36.00 points and row 12 to 30.00 points.
3. Enter the column titles in row 3 and row titles in the range A11:A14, as shown in Figure 2 – 77.
Center the column titles in the range A3:G3. Apply the Heading 3 cell style to the range A3:G3.
Apply the Total cell style to the range A11:G11. Bold the titles in the range A12:A14. Change the
font size in the range A3:G14 to 12 points.
4. Enter the data in Table 2 – 6 in the range A4:E10.
5. Use the following formulas to determine the service charge in column F and the new balance
in column G for the i rst customer. Copy the two formulas down through the remaining
a. Service Charge (cell F4) = 3.25% * (Beginning Balance – Payments – Credits)
b. New Balance (G4) = Beginning Balance + Purchases – Credits – Payments + Service Charge
6. Determine the totals in row 11.
7. Determine the maximum, minimum, and average values in cells B12:B14 for the range B4:B10, and
then copy the range B12:B14 to C12:G14.