Instructions Part 1:



[pic]

Instructions Part 1:

Create a worksheet similar to the one shown in Figure 2-89. Include the six columns of

customer data in Table 2-9 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. The printouts don NOT have to be in color – black and white is fine. Perform the following tasks.

1. Enter and format the worksheet title, worksheet subtitle, column titles and row titles. Use a font type of your choice for the worksheet titles. The Cooper Black font is shown in Figure 2-89. Bold and italicize the column titles in row 3. Add a bottom border to the column titles in row 3. Center the column titles in the range B3:H3. Bold and italicize the titles in the range A11:A14. Add a top and double bottom border to the range A11:H11. The background for Fife’s Finer Furniture should light gray not green and with a 6.25% gray pattern..

2. Enter the data in Table 2-9. (Use the supplied Excel file )

3. Use the following formulas to determine the service charge in column G and the new balance in column H for the first customer. Copy the two formulas down through the remaining customers. a. Service Charge (cell G4) = 1.95% * (Beginning Balance – Payments – Credits) or = 0.0195 (C4 - E4 - F4) b. New Balance (H4) = Beginning Balance + Purchases – Payments – Credits + Service Charge or C4 + D4 - E4 - F4 + G4

4. Determine the totals in row 11.

5. Determine the maximum, minimum, and average values in cells C12:C14 for the range C4:C10 and then copy the range C12:C14 to D12:H14.

6. Change the width of column A to 11.00 characters. Change the widths of columns B through H to best fit. Change the heights of row 3 to 27.75 and row 12 to 30.00 points.

7. Assign the Currency style with a floating dollar sign to the cells containing numeric data in the ranges C4:H4 and C11:H14. Assign the Comma style (currency with no dollar sign) to the range C5:H10.

8. Use conditional formatting to change the formatting to white bold font on a black background in any cell in the range H4:H10 that contains a value greater than or equal to 15000.

9. Change the widths of columns C through H to best fit again, if necessary.

10. Change the worksheet name from Sheet1 to Accounts Receivable.

11. Enter your name, course, Excel Skills Test, date, and instructor name in the range A16:A20.

12. Spell check the worksheet. Preview and then print the worksheet in landscape orientation. Save the workbook using the file name Lab 2-2 Fife’s Finer Furniture Monthly Accounts Receivable.

13. Print the range A3:D14. Print the formulas version on one page. Close the workbook without saving the changes. Hand in the three printouts to your instructor. Staple your printouts with the bar chart.

Instructions Part 2: This part requires that you use the Chart Wizard button on the Standard toolbar to draw a 3-D Bar chart. If necessary, use the Type a question for help box on the menu bar to obtain information on drawing a Bar chart on a separate sheet in the workbook.

With the Lab 2-2 Fife’s Finer Furniture Monthly Accounts Receivable workbook open, draw the 3-D Bar chart showing each customer’s total new balance as shown in Figure 2-90 on the next page. Use the CTRL key and mouse to select the nonadjacent chart ranges B4:B10 and H4:H10. The customer names in the range B4:B10 will identify the bars, while the data series in the range H4:H10 will determine the length of the bars. Click the Chart Wizard button on the Standard toolbar. When the Chart Wizard - Step 1 of 4 - Chart Type dialog box is displayed, select the Bar Chart type and Chart sub-type Clustered bar with a 3-D visual effect (column 1, row 2). Click the Next button twice to display the Chart Wizard - Step 3 of 4 - Chart Options dialog box. Add the chart title Accounts Receivable. Click the Next button and select As new sheet to draw the bar chart on a new worksheet. Change the worksheet name from Chart1 to Bar Chart. Drag the Accounts Receivable tab to the left of the Bar Chart tab to reorder the sheets in the workbook. Save the workbook using the same file name as in Part 1.

Include patterns with the colors on the bars in the chart to patterns. (Hint: double click on the bars this should bring up the “Format Data Series” then click on “Fill Effects” and then click on patterns and choose a pattern of your choice.) The color of the bars will be the same but now will include patterns.)

Preview and print the chart. Hand in the printout to your instructor. Staple the bar chart to above printouts.

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download