The manager of the sales force, Bill Spokane, has asked you to help him build an Excel workbook that he could use to maintain data about the sales people. 

He has already recorded the sales for the six salespeople, but he has asked you to put in formulas that would calculate a commission at 5% on monthly sales, total sales, and total commissions for each salesperson. 

He would also like to have a summary worksheet that shows the sales and commissions of the sales force. Management has decided to give a bonus to its sales force based on their performance for the year. Bill has asked you to calculate the bonus amount based on the table provided in the Compensation worksheet.

1) Group the worksheets for the six salespeople. Enter formulas in the Smith worksheet that will calculate the monthly commission (using the amount in cell B9 of the Compensation worksheet), the total sales, and total commission. (Note: since you have the worksheets in a group, you should only have to enter the formulas once, in one worksheet).

  

Transcribed Image Text:

File Paste A2 10 July 1 James Smith 2 3 4 January 5 February 6 March 7 April 8 May 9 June 21 22 23 24 25 Home Insert Clipboard Leady Cut Copy Format Painter 1 August 12 September 13 October 14 November 15 December 16 17 Total 18 19 20 G A Arial Master List Draw BIU- Page Layout F Font 10 A A A Sales Salary By Location B 5 Formulas Data Review View 25890 349980 356890 234898 234988 789789 78978 789878 187898 456543 23400 678766 Sales Summary Alignment ab C Wrap Text Commission Merge & Center - D E 5 Help General $% 9 Number LL Tell me what you want to do F .0 .00 .00 .0 Filter Fun Subtotal Fun Smith Mitchell Overton G Conditional Format as Cell Formatting Table Styles Styles Scott H I Walton Thompson TO H HH Insert Delete Format J Cells K Compensation AutoSum Fill- Clear L Display Settings Y A O Sort & Find & Filter Select - Editing M N Share O Activate Windows Go to Settings to activate Windows. + A + 100% File Paste A2 10 July 1 James Smith 2 3 4 January 5 February 6 March 7 April 8 May 9 June 21 22 23 24 25 Home Insert Clipboard Leady Cut Copy Format Painter 1 August 12 September 13 October 14 November 15 December 16 17 Total 18 19 20 G A Arial Master List Draw BIU- Page Layout F Font 10 A A A Sales Salary By Location B 5 Formulas Data Review View 25890 349980 356890 234898 234988 789789 78978 789878 187898 456543 23400 678766 Sales Summary Alignment ab C Wrap Text Commission Merge & Center - D E 5 Help General $% 9 Number LL Tell me what you want to do F .0 .00 .00 .0 Filter Fun Subtotal Fun Smith Mitchell Overton G Conditional Format as Cell Formatting Table Styles Styles Scott H I Walton Thompson TO H HH Insert Delete Format J Cells K Compensation AutoSum Fill- Clear L Display Settings Y A O Sort & Find & Filter Select - Editing M N Share O Activate Windows Go to Settings to activate Windows. + A + 100%

Answer rating: 100% (QA)

1 Grouping Worksheets for Efficiency Rightclick on any salesperson tab Smith Mitchell etc Select Group Sheets This action groups all salesperson worksheets together allowing you to enter formulas in o... View the full answer