cis

Project 1 Sales 2017 Sheet

Overview

You track the invoices of food sales for your company. You need to format and calculate sale totals.

Task

☐ 1

Open the Practice Exam 3 workbook.

☐ 2

Rename the Sales sheet to 2017 Sales.

☐ 3

Change the Theme color to Blue II.

☐ 4

Change the Tab Color of the 2017 Sales sheet to Blue Accent 2.

☐ 5

Without deleting the text, remove the hyperlink from A3 on the 2017 Sales sheet.

☐ 6

Apply the Style 40% Accent 4 to cell A3.

☐ 7

Wrap the text in row 5, then center it.

☐ 8

Merge and Center A3:G3. Then make it 18pt., bold, vertically align middle

☐ 9

Insert a row at the top of the sheet and make the row height 45.

☐ 10

At A1, insert the picture Health Valley Logo.png and resize the height to 0.8” (the width will automatically adjust).

☐ 11

Apply the Picture Effect: Shadow, Offset Diagonal Bottom Left and then apply a Picture Border: Black, text 1 with a Weight of 1 point.

☐ 12

Create a formula to complete each of the invoice totals in the Total Sale column.

☐ 13

Create Named Ranges: Name: SaleAmount Cell range: D7:D18

Name: TaxAmount Cell range: E7:E18

Name: ShipAmount Cell range: F7:F18

☐ 14

In cell K7 use the correct named range (SaleAmount) in a function to calculate the total sales.

In cell K8, use the correct named range in a function to calculate the total tax.

In cell K9, use the correct named range in a function to calculate the total shipping.

☐ 15

Format K7:K9 as Accounting with 2 decimals.

☐ 16

Set the print orientation to Landscape.

☐ 17

In C19, type your first and last name.

☐ 18

Without deleting it, modify the Name column so it doesn’t show.

Project 2 Addresses Sheet

Overview

You need to import customer addresses and make them reader friendly.

☐ 1

Create a new sheet and name It Addresses.

☐ 2

On the Addresses sheet, import the text file Employee addresses. It is tab delimited. Place the import in cell A1.

☐ 3

In the cell F2, use a function to insert only the State abbreviation from E2.

In the cell G2, use a function to insert only the Zip from E2.

In the cell H2, use functions to combine the customers first name and last name in all capitals . The first name and last name should be separated by a space.

Use Auto Fill to bring the formulas in F2:H2 down through row 19.

☐ 4

In the cells A1:H1: Apply the Style Heading 3.

☐ 5

Autofit column G.

Change the column width of H to 25.

☐ 6

Change all occurrences of the zip code 10028 to 10027.

☐ 7

Insert 3 rows at the top of the sheet.

☐ 8

Create a text box across cells B1:G3 with the text NY & CT Customers.

Format with box fill with Teal, Accent 6.

Format the text in the text box: 18pt, Bold, Centered (horizontally and vertically) and with a WordArt Style: Pattern Fill – White, Text 2, Dark Upward Diagonal, Shadow.

☐ 9

Create a page break before row 13.

Repeat rows 1:4 to print on the top of each page.

Create a center header: Employee Addresses, bold, 14pt.

Create a center footer: the page number.

Scale the data on the sheet to fit the width of 1 page (NOTE: Do not fit the height or it will negate the page break.)

Project 3 Annual Sheet

Overview

You want to display the highest Quarterly Sales and display certain products in a chart format.

☐ 1

Create a New Sheet and name It Annual.

☐ 2

Cut the cells A26:F41 on the Addresses sheet and paste into cell A1 on the Annual sheet. AutoFit columns A:F.

☐ 3

Make the order of the sheets: 2017 Sales, Annual, Ledger, Addresses.

☐ 4

On Annual, delete rows 3:4.

☐ 5

In Cell A1: replace Nationwide Budgeted Sales with Al’s Sporting Goods (ASG)

Hint: Make sure your capital letters and spaces match. If the columns are not wide enough, auto fit the column widths.

☐ 6

In the cells, A1:F1, merge and center the title.

In the cells, A2:F2, merge and center the date.

☐ 7

Format the text in Cells A1:F2: 14pt; Fill: Green, Accent 4, Lighter 60%

☐ 8

Format the numbers in cells B5:F14: Accounting format no decimals

☐ 9

On the Annual worksheet use the data in cells A4:E13 to Insert a 3-D Clustered Column Chart beginning in cell A16. The products should be on the horizontal axis.

Size the chart to 4 inches by 6 inches.

☐ 10

Display only the categories (by filtering): Baseball Bats, Olympic Frisbees, Kayaks, Tennis Racquets.

☐ 11

Switch the Row/Column data so the Quarters are on the category axis.

☐ 12

Create the Chart Title: Yearly Sales. Name the chart: 4Qtrs

☐ 13

Apply the Chart Style 3.

☐ 14

Using a format that will update even if the values change, highlight the top three items for each Quarter (each quarter must be done separately) with a custom format: orange fill and black text.

☐ 15

Copy the cells A4:F14 and Paste in cell A38 with the data transposed.

Make the titles in row 38 fit on two lines and make sure all the numbers display.

Project 4 Ledger Sheet

Overview

You need to analyze customer’s bank transactions for certain expenses.

☐ 1

On the Ledger sheet, sort the data simultaneously by both Name (A to Z) and Date (oldest to newest).

☐ 2

In cell H4 use a formula to find the number of expenses for rent.

In cell H5 use a formula to find the number of expenses on or before 2/28/2005.

☐ 3

In cell H7 use a formula to find out the total amount of transactions for rent.

In cell H8 use a formula to find out the total amount of transactions on or after 3/1/2005.

☐ 4

In cell H12 use a formula to find the average expenses on or before 2/28/2005.

In cell H13 use a formula to find the average expenses on or after 3/1/2005.

☐ 5

Convert the ledger data (A3:E66) to a table and apply the style Medium Table 13.

☐ 6

Name the table with your last name and first initial.

☐ 7

Copy the table range and paste it beginning at A70.

☐ 8

Convert the table at A70 to a range without the formatting.

☐ 9

Filter the data in cells A70:E133 for rent or medicine expenses that occurred after 2/3/2005.

☐ 10

Set a print area for G3:H13.

☐ 11

Add the property for Subject: Project for CIS122L and a Comment: Upload this file on completion.

☐ 12

Save with your last name, first initial, and workbook name Practice Exam 3 (e.g. SmithJ Practice Exam 3). Submit the file via the link on the assignments page.