## 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:
• Make a spreadsheet which looks like this.
• You can change the formatting to your pleasing, but try to make the spreadsheet look nice.
• Add at least one salesman, making up your own data.
• HOWEVER, all of the cells in the Total column, the Total plus Bonus column, the Pay in British Pound column, and the Best Week column should have formulas, and not numerical entries.
• Pay close attention to relative and absolute addresses as you copy your formulas.
• Use copy and paste to transfer the values from the input data file to Excel. The values have been separated by tabs, and so should be easy to paste into an Excel spreadsheet. You can also simply open the file in Excel and allow it to convert your input values.

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.

• First, publish your page showing the values. The chart can be included in this file or you can publish it as a webpage on its own. Call the web page "assign7.html" and put a link to it on your "assignments.html" page.

• Then, publish it again showing the formulas. This can be done as follows: Under "Tools," "Options," (or "Preferences" ) with the "View" tab, click on "Formulas" under the "Window Options" so that when you publish the sheet the formulas will be displayed and not the values. Make certain this page has a link to it, too!

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