Formulas, Functions and Formatting
Outcomes addressed in this activity:
Unit Outcomes addressed in this unit:
Properly use the built-in functions of SUM, AVERAGE, MAXIMUM, and MINIMUM in Excel worksheets.
Create and move a clustered 3-D bar chart.
Course Outcome practiced in this unit:
IT153-1: Create spreadsheets to solve business problems.
Purpose: This assignment will allow you the opportunity to practice your Excel skills and test your knowledge of the readings in the unit.
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:
Aficionado Guitar Parts balance report
Perform the following tasks:
Download the Unit 2 Data File. Use it for this assignment.
Format the worksheet as described in the following instructions:
Change the theme of the worksheet (Page Layout | 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 a red color.
Change the font color of cells A1 and A2 to a theme color.
Draw a thick box border around the range A1: A2.
Change the width and heights
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.
Format the column titles
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.
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.
Service Charge (cell F4) = 3.25% * (Beginning Balance – Payments – Credits)
New Balance (G4) = Beginning Balance + Purchases – Credits – Payments + Service Charge
Determine the totals in row 11 using the SUM Function.
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.
Format the numbers as follows:
Assign the Currency style to the cells containing numeric data in the ranges B4: G4 and B11: G14.
Assign a Comma style to the range B5: G10.
Use conditional formatting to change the formatting to a light red fill with a dark red text in any cell in the range F4: F10 that contains a value greater than 10.
Change the worksheet name from Sheet1 to Accounts Receivable and the sheet tab color to a red color. Change the worksheet header with your name (Right Side), course number (Left Side), footer with the date (Left Side) and page number (Right Side). (Use Footer tools do not type).
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.).
Accounts Receivable Bar Graph
Open the workbook Part 1 Aficionado Guitar Parts Accounts Receivable Balance Report workbook created in Part 1. (If not already open).
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.
Click the Bar button (Insert tab | Charts group) and then select 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.
Format the Chart
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 – Orange.
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.
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.
Save the workbook. Submit to the Dropbox for Unit 2.
Also review the university policy on plagiarism. If you have any questions, please contact your professor.
Formulas, Functions and Formatting