V22.0004 Computers in Principle and Practice
Fall, 2007

Assignment # 7 - Excel

Assignment 7

Your Web Site has been so successful that you have gone into business creating Web Sites professionally. In order to keep you company busy, you have employed several sales people, and you need to keep track of their salaries which are paid on commission. Using Microsoft Excel, you are to create a spreadsheet that totals the commissions you will pay each sales person during a four week period in November. Total each person's salary, and then apply a 'Bonus Percentage' which is the same percent for all sales people and is recorded in a cell by itself. Next, convert that total-salary-plus-bonus into British Pounds. In each row, also compute each sales person's best week for the month. You should also total each of the columns.

Part A

  1. First, here are a few points about creating the spreadsheet itself:

2. Create two macros (by using the record function in TOOLS / MACROS / RECORD NEW MACRO) - one of which will sort the data in Ascending order by Name, and the other which will sort the data in Descending order by Total-plus-Bonus. Create a new toolbar that has a button on it for each macro.

2. Prepare a pie chart showing the pay per salesman that looks something like the chart in the sample.

3. Publish two pages as follows: one with the data visible and chart and one with the formulas showing.

Part B

Next, using Word, you will use the data file you have created above to send out a Mail Merge letter to each sales person. (Use TOOLS: DATA MERGE MANAGER for this.)

  1. First, it may help (particular under Windows) to go and name your data area in Excel. To do this, highlight the area of your data including the column headings (But NOT the chart title) and type a name in the Name Box (the same area where you also choose Functions). This will allow you to use this same named data area in Word.
  2. The letter master (aka Main Document) should refer to the sales person by name, and give their monthly Total-plus-Bonus both in dollars and in British Pounds. You should also report each sales person's best week in the letter.
  3. Merge the document to create a letter for each sales person. Use "Merge to a New Document" and save the document.
  4. Send the following three files as email attachments to your grader email account:

a. The Original Excel Data base which includes the macros and the toolbar
b. The Word Form Letter (Main Document)
c. The Word Merged Letters

Working with Toolbars in Excel

Assign the macros to a new toolbar:

On a PC:

    1. Go to TOOLS / CUSTOMIZE / TOOLBARS / NEW and give the toolbar a name
    2. Then Click on COMMANDS / MACROS and drag the "custom macro" button to the toolbar
    3. Right-click on the button for a drop-down menu with options to edit the name, picture, and to assign a macro

On a Mac:

    1. Go to TOOLS / CUSTOMIZE / TOOLBARS / NEW and give the toolbar a name
    2. Then Click on COMMANDS / MACROS and drag the "custom macro" button to the toolbar
    3. Control-click on the button for a drop-down menu: Select PROPERTIES to assign a name and picture and then select ASSIGN MACRO to assign a macro to the button

Attach the toolbar to the worksheet:

In the CUSTOMIZE menu for Toolbars, select your toolbar, and then select ATTACH. Use the COPY button so that the name of your toolbar appears in the right-hand column. Remember to save your workbook again after attaching the toolbar.