cis

Project 1 - Nationwide Sales Workbook – Employees sheet

Overview

You manage the employee records and sales figures for Nationwide Sales. You need to add new employees and format them, then calculate additional employee data.

Tasks

☐ 1

Open the Sales Summary workbook.

☐ 2

From the Nationwide HR Records workbook, copy the entire Employees worksheet (not just the data) into the Sales Summary workbook.

☐ 3

Import the tab delimited file New Hires 2017 to C1:M7 on the Proper Case sheet.

☐ 4

In A1 on the Proper Case sheet, type the function to change cell C1 from all capital letters to proper capitalization (e.g. Crown).

Copy to B1 and then fill A1:B1 down to row 7.

☐ 5

Convert A1:B7 to constant values. Then delete columns C:D.

☐ 6

Copy A1:K7 to A53 on the Employees sheet.

☐ 7

On the Proper Case sheet, type your first and last name in Q1.

☐ 8

Hide the Proper Case sheet.

☐ 9

In E5 on the Employees sheet, calculate the age by subtracting their DOB from the current date (use the TODAY function for the current date) and then dividing that result by 365.25; extend to E6:E59.

☐ 10

In H5, calculate the tenure by subtracting the Hire Date from the current date (using the TODAY function) and then dividing the result by 365.25; extend to H6:H59.

☐ 11

Using the percentage in L2, calculate in L5 next year’s higher salary. (Note: The formula must include the cell name L2, not the number.) Extend to L6:L59.

☐ 12

Format the New Salaries as currency with no decimal places.

☐ 13

Format all of the Hire Dates like 11-Nov-99

☐ 14

In the Title column, create a function that inserts Mr. if the Gender is M and Ms. if it is not; extend to M6:M59.

☐ 15

Use a function in the Name column to concatenate M5, B5, A5 and include spaces so the first row result is: Mr. Joel Albert (with a space between each of the three words); extend to N6:N59.

☐ 16

Use a function in the Full Name column to remove any excess spaces from the entries in the Name column; extend to O6:O59.

☐ 17

Insert a new column between L and M. Type the heading Bonus.

☐ 18

At the top of the new column, create a formula that will calculate the Bonus as follows:

If the New Salary will be <$25,000 it’s $500

If the New Salary will be >=$100,000 it’s $1000

Otherwise it’s $800.

☐ 19

Extend to M6:M59.

☐ 20

Set the following: Print Area: A1:O59

Margins: Narrow

Repeat row 4 on all pages

Scale to fit all columns on one page.

Project 2 – Personnel sheet

Overview

For Nationwide Sales, you need to calculate various employee statistics.

Tasks

☐ 1

On the Personnel sheet, give G7:G77 the range name FT_PT

☐ 2

Using the named range for the data in the FT/PT column, in the first two rows in the PT/FT Counts table, use the appropriate function to calculate the number of employees working full time (FT) and the number working part time (PT).

☐ 3

In K9, use the COUNTBLANK( ) function and FT_PT range name to display the number of employees with missing full time/part time information.

☐ 4

Type your first and last name in I3. (That is the letter i.)

☐ 5

Leaving the values intact, find all cells with Data Validation and clear that data validation.

☐ 6

Edit the comment that says: “EEO complaint filed” by adding: Complaint settled.

☐ 7

Turn the range B6:H77 into a table with Table Style: Medium 12. Name the table tblPersonnel.

☐ 8

Add a Total Row.

☐ 9

Remove the title Total. Remove the Sum from the Salary column; add an Average to the Hire Date column; add a Count to the Gender column.

☐ 10

In the table, remove duplicates when all fields are the same.

☐ 11

Hide column I. (That is the letter i.)

Project 3 - National Sales sheet

Overview

You need to summarize the annual sales figures.

Tasks

☐ 1

In cell G4 on the National Sales sheet, create a formula to add cells R5 and R16 from the Regional Sales sheet; extend the formula to G5:G12.

☐ 2

Add a 3-Triangles icon set to F4:F12; change the formatting rule so that when the value is >=1000, the icon is a green triangle; when the value is <1000 and >= -1000 the icon is a yellow rectangle. (Do not hide the red triangles.)

☐ 3

In H4:H12, add Line Sparklines for the data range B4:E12.

☐ 4

Change the Vertical Axis so that both the Minimum and Maximum values are the same for all sparklines; add only a High Point Marker that is Orange, Accent 6.

☐ 5

Add a hyperlink in A14 that links to A3 on the Regional Sales sheet.

☐ 6

On the National Sales sheet, insert a 2-D Line Chart to represent A3:E12.

☐ 7

Chart Title: Quarterly Sales

Alternate Text Title: Quarterly Sales Line Chart for Nationwide Sales

Chart Style: Style 4

☐ 8

Format the title with WordArt style Gradient Fill, Aqua, Accent 1, Reflection

☐ 9

Add a Primary Vertical Axis Title: Sales in Thousands

☐ 10

Add Primary Major Vertical Gridlines

☐ 11

Legend Border: Solid Line, Orange, Accent 6, Weight 1

☐ 12

Move the chart to a new chart sheet (not a regular worksheet); name the sheet Sales Chart

Project 4 - Regional Sales sheet

Overview

You have been asked to outline the regional sales, draw an org chart diagram, and remove any document properties.

Tasks

☐ 1

On the Regional Sales sheet, indent A5:A13 and A16:A24 one tab stop (one indent).

☐ 2

Outline A3:R26.

☐ 3

Use the outline tools to hide the detailed data for Quarter1, Quarter2, and Quarter3, leaving Oct, Nov, Dec, and the quarters displayed;

Display only the Eastern, Western, and National Totals. (It’s okay if “Eastern Sales” and “Western Sales” in A4 and A15 are also displayed.)

☐ 4

On the Org Chart sheet, insert SmartArt of type Organization Chart

☐ 5

Top box: CEO

Second level box: COO

Third level boxes (from left to right): Dir Div 1; Dir Div 2; Dir Div 3

☐ 6

On the same line as COO, add a Shape After that displays CFO.

☐ 7

Change the colors to: Dark 2 Outline

☐ 8

Find and remove all document properties including hidden ones like Author and Company.

☐ 9

Save the workbook as your last name and first initial with Sales Summary (e.g. SmithJ Sales Summary). Submit the file via the link on the Assignments page.