Due: 10-27-2014 Project 6 Description 40 Points
Using Conditional Expressions, Aggregates and Charting
In Spreadsheets
Warning: You must use the data and instructions below for generating your Project 6 spreadsheet and chartsheet. Any other data will not be graded. Do not use data from the in-class lab session, my sample document, or my lab discussion document.
The Richardson Medical Devices has decided to pay 3.5% as the base commission to its salespeople to stimulate sales but adds an additional 2.5% as the bonus commission for sales greater than or equal to $290,000. So any sales over $290,000 receives a 6% commission. The company pays each salesperson a monthly base salary. The management has recorded each employee’s sales for this quarter. This information - employee name, employee's monthly base salary, and recorded quarterly sales - follows:
Baker, Tim $5,900 $380,123
Learner, Joseph $7,550 $277,200
Albright, Barbara $7,000 $340,345
Mourissee, Lynn $6,175 $289,987
Noble, Richard $4,575 $220,800
Smith, Walter $6,790 $296,459
The above information needs to be entered on the worksheet and the recorded sales figures are referred to in the ‘if’ statements.
Use conditional formatting to highlight the Recorded Quarterly Sales greater than the $290,000 base amount; use a colored font and provide a different backcolor.
With this data, you have been asked to develop a worksheet calculating the amount of commission and the quarterly salary for each employee. This commission is a single column made possible by using an if formula. The following formulas can be used to obtain this information:
Commission Amount = 3.5% timesthe Recorded Sales and an additional 2.5% times the amount over $290,000 of the Recorded Sales. This can be expressed either as
=if(RS > 290000, RS * 3.5% + (RS – 290000) * 2.5%, RS * 3.5%)
or
=if(RS > 290000, (RS – 290000) * 6% + 290000 * 3.5%, RS * 3.5%)
where you should substitute the relative cell reference to recorded quarterly sales value for that salesperson.
Quarterly Salary = Employee Monthly Base Salary * 3 + Commission Amount
This means that you should have five columns of data – the three input columns: Employee Name, Monthly Base Salary, and Recorded Sales, and the two calculated columns: Commission Amount and Quarterly Salary. Your data layout should be somewhat like the below sample –
except showing the data rows for all of the salepeople. Notice that the column headers are centered both horizontally and vertically.
Include a total, average value, highest value, and lowest value for Monthly Base Salary, Recorded Sales and Quarterly Salary. This means that you should not move the columns around to permit use of fill across but you should use the copy and paste of these aggregate functions.
You need a title for the spreadsheet and the title should span all the columns in the data area – A through E by using the merge and center operation. Create a 3-D Pie Chart using the employee's quarterly salary. This 3-D Pie Chart should be embedded on the current worksheet. Also create a 2-D Column Chart showing the Employee Base Salary and the Commission amount. The 2-D Column Chart should use a separate chart sheet. Use data labels on both charts. You need a title on both charts and an X-axis and Y-axis title on the 2-D Column Chart. Use the concepts and techniques presented in the two chapters on using MS Excel to create and format the worksheet and chart. You should be able to get the base information and the embedded 3-D Pie Chart on a single sheet. Make sure that both sheets have meaningful names. You may have to adjust some of the font sizes on the charts. You need a header and footer section for both the worksheet and the chartsheet.
This Spreadsheet project is worth 40 points. Name your attached file ini_project6.xlsx where ini are your initials and submit it through the Assignment Manager.
Project 6 Rubric /Item / Description / Points /
1 / Correct filename format / 2
2 / Header section on both worksheet and chartsheet / 2
3 / Footer section on both worksheet and chartsheet with name, date / 2
4 / Only two sheets in workbook and sheets renamed / 2
5 / Title line on worksheet with merged cells spanning cols A thru E / 2
6 / Column headers use word wrap, center alignment for both horizontal
and vertical directions, border/shading / 2
7 / Correct values from the project description in first three input data
columns for each row / 3
8 / Use conditional formatting in Recorded Sales column / 2
9 / Commission uses correct =if(…) statement / 3
10 / Quarterly salary uses correct formula / 1
11 / Sum, average, max, min functions used / 4
12 / Above functions used in only the three specified columns / 3
13 / Pie chart is an embedded chart in the first worksheet / 1
14 / Title on pie chart / 1
15 / Data labels on pie chart / 1
16 / Correct legend series / 1
17 / Correct data series / 1
18 / Company data and pie chart fit on a single page / 1
19 / 2-D column chart as a separate chart sheet / 2
20 / Title on 2-D column chart / 1
21 / X and Y axes titles on 2-D column chart / 1
22 / Data labels used on 2-D column chart / 1
23 / Correct 2 data series used / 1
Total / 40
Spreadsheet Techniques found in MS Office 2013
Conditional Formatting EX 100 - 103
Aggregate Functions EX 14 - 15, EX 81 - 87
If function http://spreadsheets.about.com/od/iffunctions/ss/2010-08-03-Excel-2007-If-Function-Step-By-Step-Tutorial.htm
Adding charts EX 37 - 40
Moving chart to Chart Sheet EX 41 - 42
JRNorth 3 Fall 2014