excel

BUS 120 Spreadsheet Applications

Final Exam – Problem 1

Name:

1) Name the data in the cell range B26 – I37 as “Supply_Inventory”.

2) Sort the data by location. (leave “Total” at the end)

3) Format columns C – G as “Comma Style” Format-(Accounting with no $, no decimals)

4) Calculate the totals for each location (by row) and for each product (by column).

5) In cell C35 create a formula that calculates a 30% decrease in the number of cake pans. (Hint: multiply the Total in C33 by .70)

6) Use the fill handle to copy this into cells D35 – G35.

7) In cell C37 calculate the average the number of cake pans in each storage area. Copy this formula to columns D – G.

8) Bold, Center and Underline the column headings

9) Use conditional formatting to highlight any Cake pan location (column C) that is greater than 30 with Red text, no fill.

10) Use conditional formatting to highlight any Pie pan location (column D) that is less than 40 with Yellow text and light Yellow fill.

11) In cell I29 calculate the a 10% increase in the total of Location 1 (I26 times G29).

12) Use the fill handle to copy this into cells I30 – I32, locking any cells that need to be locked.

13) Create a Pie chart on a new worksheet of the total inventory items (Column G) by storage location. Hint: Do not include the total in row 33.

14) Add Data labels to your chart.

15) Add a Title to your chart with “Baking Supply Company” as the 1st line and “Total Inventory by Location” on line 2.

16) For the data below Center the first two lines (26 – 27) over columns B – G.

17) Resize any columns that do not fit the cells.

18) Set the print range to include rows 26 – 37, add gridlines and preview your report to be sure it is on one page.

19) Add your name as the center footer, save the file and move to the next sheet.

Baking Supply Company Change 1.1

Inventory, in cases

Location Cake Pans Pie Pans Spatulas Bowls Total What if?

Storage 3 40 34 59 23

Storage 2 29 43 47 52

Storage 1 55 55 57 38

Storage 5 28 32 41 43

Total

30% drop

Average

Problem 2

1) Name the range N21:P31 “Name_Lookup”

2) Use VLOOKUP formulas to find an exact match for the First and Last names of the employees based on their employee numbers. (Hint: use the range name or be sure to lock cells as needed)

3) In column K, calculate the Annual compensation as Annual Salary plus Bonus plus Benefits.

4) In cell G37 use the SUMIF formula to total any Annual Compensation greater than $75,000.

5) Insert a table for the data below.

6) Add a total row to the table. Sum columns G – K and count column C in the Total Row.

7) Format columns G – K as currency with no decimals.

8) Sort the table on Last Name (column D).

9) Change the table style to one you like.

10) In cell L22, create an IF formula that puts “NYC” in column L if column F is “New York”, if not put “Other.” Copy this formula to the remaining rows in column L.

11) Filter the table for any employee hired between 1/1/2013 and 12/31/2013. In Cell G40, type (do not use a formula) the results for count of employees.

12) Clear the filter.

13) Filter the table for any employee with a last name that begins with M. In cell G42, type (do not use a formula) the results for the total Annual Compensation. Clear the filter.

14) Insert a comment in cell H26 that includes your name and “This salary seems high.”

15) Rename the tab (sheet name) to Payroll Data and change the tab color to Blue

16) Enter your name as a center header, save the file, and submit your file.

Name Lookup

Employee

Number

First

Name

Last

Name Hire Date Branch

Monthly

Salary

Annual

Salary

Annual

Bonus

Benefits

Dollars

Annual

Compensation

Employee

Number

First

Name

Last

Name

1211 2/12/2013 New York 4650 55800 1370 12834 1211 Mary Lane

4515 4/1/2014 Boston 5970 71640 5725 16477.2 2120 Ellen Meeb

4187 5/6/2013 Philadelphia 7550 90600 6525 20838 2213 Jake Gray

2599 12/10/2013 Boston 8450 101400 7550 23322 2599 Martha Macky

2213 2/15/2015 Boston 2950 35400 680 8142 3955 Jen Richards

6588 3/25/2013 New York 2750 33000 630 7590 4187 Debbie Davie

2120 6/23/2012 New York 4225 50700 1320 11661 4450 Mark Mollen

4450 8/3/2015 Philadelphia 6750 81000 5900 18630 4515 Leanne Waters

9887 9/29/2014 Philadelphia 8500 102000 7650 23460 6588 Paul Early

3955 5/12/2013 Boston 3750 45000 795 10350 9887 Hank Randal

Compensation > 75,000

Count of employees hired between 1/1/2013 and 12/31/13

Total Compensation for employee with a last name that begins with M

A B C D E F G H I J K L M N O P

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41