Freshman Focus Excel Project #2

Problem:

You are a spreadsheet intern for Emily’s Music Emporium, a popular Seattle-based music store with outlets in major cities across the United States. You have been asked to use Excel to generate a report that summarizes the monthly balance due. A graphic breakdown of the data also is desired. The customer data in Table 1 is available for you to use.

Instructions Part 1:

Create a professional looking worksheet similar to the one shown in Figure 1. Include the five columns of customer data in Table 1 in your report, plus two additional columns to compute a service charge and a new balance for each customer. Assume no negative unpaid monthly balances. Perform the following tasks. Feel free to customize it and use additional appropriate formatting when desired.

  1. Enter and format the worksheet title “Emily’s Music Emporium” and worksheet subtitle, “Monthly Balance Due Report” in cells A1 and A2.
  2. Change the appearance of the title and subtitle. Use appropriate formatting and be sure to change the: font, font size, and font color.
  3. One at a time, merge and center the title and the subtitle between columns A and G.
  4. Change the background color of the title and subtitle cells.
  5. Draw a thick box border around the range A1:A2.
  6. Change the width of column A to 18.00 characters. Change the width of columns B through G to 15.00. Change the heights of row 3 to 30.00 and row 12 to 33.00.
  7. Enter the column titles in row 3 and the row titles in the range A11:A14 as shown in Figure 1. Bold the column titles, add a bottom border to the column titles in the range A3:G3. Bold the titles in the range A11:A14. Add a top and bottom border to the range A11:G11.
  8. Enter the data from Table 2 in the range A4:E10.

Table 1

Customer / Beginning Balance / Credits / Payments / Purchases
Aaronoli, Lester / 356.12 / 123.30 / 15.00 / 17.50
Calibratski, Hans / 126.50 / 12.55 / 22.00 / 23.75
Sanchez, Juanita / 619.45 / 63.50 / 30.75 / 12.99
Perez, Carlos / 89.21 / 32.10 / 10.00 / 81.05
Pital, Nalin / 201.75 / 0.00 / 28.00 / 69.26
Eagelton, Latro / 915.25 / 2.15 / 75.00 / 18.00
Dae-Song, Melinda / 731.28 / 35.00 / 100.00 / 34.00
  1. Use the following formulas 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.
  2. Service Charge (cell F4) = 2.15% * (Beginning Balance – Payments – Credits) or

= 0.0215 * (B4 – C4 – D4)

  1. New Balance (G4) = Beginning Balance + Purchases – Payments – Credits + Service Charge. Or =B4 + E4 – C4 – D4 + F4
  1. Determine the totals in row 11.
  2. Determine the maximum =max(B4:B10), minimum =min(B4:B10), and average = average(B4:B10), values in cells B12:B14 for the range B4:B10 and then copy the range B12:B14 to C12:G14.
  3. Use the format cells command in the shortcut menu to format the numbers as follows:
  4. Assign the Currency style with a floating dollar sign to the cells containing numeric data in the ranges B4:G4 and B11:G14
  5. Assign the Comma style (currency with no dollar sign) to the range B5:G10.
  6. Enter your name in the left most section of the page header.
  7. Spell-check the worksheet. Set the page orientation to landscape. Preview the document and make sure that it fits on one page. Adjust the page breaks if needed.
  8. Print your worksheet and then print your worksheet using the formula view.

Figure 1:

Instructions Part 2:

This part requires that you use the Chart Wizard button on the Standard toolbar to draw a 3-D Bar Chart with a cylindrical shape. If necessary, use the “Type a Question for Help” box on the menu bar to obtain information on drawing a chart on a separate sheet in the workbook.

With your completed part one of this project open, follow the steps below in order to create a chart that looks similar to the one in Figure 2.

  1. 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 then while holding down the CTRL key, select range G4:G10. The customer names in the range A4:A10 will identify the cylindrical bars, while the data series in the range G4:G10 will determine the length of the bars.
  2. Click on the Chart Wizard button on the standard toolbar. When the Chart Wizard opens follow these four steps:
  3. Select the Cylinder in the chart type list and “Bar with a Cylinder” shape in the chart sub-type area (column 1, row 2).
  4. Click the next button twice.
  5. Add the chart title “Balance Due” then click Next.
  6. Select “As a new sheet” to draw the bar chart on a new worksheet. Click the finish button.
  7. When the chart is displayed, click the wall behind the bars and click the Fill Color button on the formatting toolbar. Select a color for the background.
  8. Click on a bar to select all of the bars. Click on the Fill Color button on the formatting toolbar and select a color for the bars.
  9. Change the worksheet name from chart1 to “Bar Chart.”
  10. Set the page orientation to landscape. Preview the document and make sure that it fits on one page. Adjust the page breaks if needed.
  11. Print your bar chart worksheet.

Figure 2