Software Applications

Unit 2    [IT153: Spreadsheet Applications] 

 

Assignment Details and Rubric  

Outcomes addressed in this activity: Unit Outcomes:

 Enter formulas by typing them and by selecting cells  Use the built-in functions of SUM, AVERAGE, MAXIMUM and MINIMUM, and use them

properly in Excel worksheets

Course Outcome:

IT153-2: Create formulas and functions.

Accounts Receivable Balance Worksheet Problem: You are a part- time assistant in the accounting department at Aficionado Guitar Parts, a Chicago- based supplier of custom guitar parts. You have been asked to use Excel to create formulas and functions in order to generate a report that summarizes the monthly accounts receivable balance (see below). A chart of the balances also is desired. The customer data in Table 1 (below) is available for test purposes.

Instructions Part 1:

Unit 2    [IT153: Spreadsheet Applications] 

 

Perform the following tasks: Download the Unit 2 data file from Doc Sharing and save the workbook using the file name: Unit 2 Assignment Your Name.

1. Format the worksheet as described in the following instructions. Change the theme of the worksheet to the Trek. 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 1 and 2 to 30 and row 3 to 36.00 points and row 12 to 30.00 points.

3. Format the column titles in row 3 and row titles in the range A11: A14, as shown in the finished worksheet above (Hint: Use Alt+Enter to wrap text). Center and Middle Align the column titles in the range A3: G3. Apply the Heading 3 cell style to the range A3: G3. Apply the Currency and Total cell styles 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. Build the following formula(s) to determine the service charge in column F and the new balance in column G for the first customer. Copy the two formulas down through the remaining customers.

a. Service Charge (cell F4) = 3.25% * (Beginning Balance – Payments – Credits)

b. New Balance (G4) = Beginning Balance + Purchases – Credits – Payments + Service Charge

5. Determine the totals in row 11 using the SUM Function.

6. 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.

7. Format the numbers as follows: a. a. Assign the Currency style to the cells containing numeric data in the ranges B4: G4 and

B11: G14. b. b. Assign a Comma style to the range B5: G10.

8. Use conditional formatting to change the formatting to Light red Fill with Dark Red Text in any cell

in the range F4: F10 that contains a value greater than 10.

9. Change the worksheet name from Sheet1 to Accounts Receivable and the sheet tab color to the Red standard color. Change the worksheet header with your name (Right Side), course number

Unit 2    [IT153: Spreadsheet Applications] 

 

(Left Side, and footer with the date (Left Side) and page number (Right Side). (Use Footer tools do not type).

10. Spell check the worksheet. Preview the worksheet in landscape orientation. Save the workbook using the file name, Unit_2_Assignment _Your Name.

Instructions Part 2: In this part of the exercise, you will create a 3-D Bar chart on a new Chart sheet in the workbook (Chart Figure below). If necessary, use Excel Help to obtain information on inserting a chart on a separate chart sheet in the workbook. (Do not copy the chart- use the Move Chart tool).

1. Open the workbook Part 1 Aficionado Guitar Parts Accounts Receivable Balance Report workbook created in Part 1. (If not already open).

2. Use the ctrl key and mouse to select the nonadjacent chart ranges A4: A10 and G4: G10. That is, select the range A4: A10 and while holding down the ctrl key, select the range G4: G10.

 $‐  $100.00  $200.00  $300.00  $400.00  $500.00  $600.00  $700.00  $800.00

Cervantes, Katriel

Cummings, Trenton

Danielsson, Oliver

Kalinowski, Jadwiga

Lanctot, Royce

Raglow, Dora

Tuan, Lin

Accounts Receivable

Unit 2    [IT153: Spreadsheet Applications] 

 

3. Click the Bar button (Insert tab | Charts group) and then select Clustered Bar in 3-D in the 3-D Bar area. When the chart is displayed on the worksheet, click the Move Chart button (Chart Tools Design tab | Location group). When the Move Chart dialog box appears, click New sheet and then type Bar Chart for the sheet name. Click the OK button (Move Chart dialog box). Change the sheet tab color to the Green standard color.

4. When the chart is displayed on the new worksheet, click the Series 1 series label and then press the delete key to delete it. Click the chart area, which is a blank area near the edge of the chart, click the Shape Fill button (Chart Tools Format tab | Shape Styles group), and then select Subtle Effect – Orange, Accent 6. Repeat for the Plot area (middle of the chart) Click one of the bars in the chart. Click the Shape Fill button (Chart Tools Format tab | Shape Styles group) and then select the Green standard color. Click the Chart Title button (Chart Tools Layout tab | Labels group) and then select Above Chart in the Chart Title gallery. If necessary, use the scroll bar on the right side of the worksheet to scroll to the top of the chart. Click the edge of the chart title to select it and then type Accounts Receivable as the chart title.

5. Drag the Accounts Receivable tab at the bottom of the worksheet to the left of the Bar Chart tab to reorder the sheets in the workbook. Delete any unused worksheets.

6. Save the workbook. Submit the assignment to the Unit 2 Dropbox.

All deliverables should be professionally formatted and should be free of spelling errors. Points deducted from the grade for each error are at your instructor’s discretion.

Unit 2 Assignment grading rubric = 50 points

Assignment Requirements Points Possible

Points Earned

The worksheet title and subtitle are entered and formatted. 0 - 1

Column widths and row heights are adjusted. 0 - 1

Column and row titles are entered and formatted. 0 - 1

Formulas are correctly entered for columns F and G. 0 - 5

Unit 2    [IT153: Spreadsheet Applications] 

 

Totals in row 11 are entered correctly. 0 - 5

The maximum, minimum, and average values are entered correctly in rows 12-14.

0 - 6

Numbers are formatted appropriately. 0 - 5

Conditional formatting is applied. 0 - 3

A worksheet title and a color are applied to the sheet tab. 0 - 1

The document properties are changed, as specified by the instructor (fit on a single page in landscape).

0 - 1

Student’s information is added to the worksheet Header & Footer. 0 - 4

The workbook is saved as “Unit_2_Assignment _Your Name.” 0 - 1

A Clustered Bar in 3-D is created and moved to a new worksheet. 0 - 4

The chart has appropriate data and formatting. 0 - 5

The sheets are reordered in the workbook Worksheet, then Chart and extra blank worksheets deleted.

0 - 2

The submitted assignment reflects the concepts from the Chapter Readings and the Step-by-steps.

0 - 5

TOTAL POSSIBLE POINTS: 0 - 50

Points deducted for spelling or formatting

Adjusted total points