Corrected Excel work

Step

Instructions

Max. Points

Obtained Points

1

Start Excel. Open the downloaded Excel file named Exploring_e

0

1_Grader_IR.xlsx.

2

On the Session1 worksheet, in cell A

3

, enter Start Date as the text. In cell B3, enter

7

/7/

11

as the date.

0.0

On the Session1 worksheet, in cell E7, create a formula that will calculate the session fee with member discount.

10

4

On the Session1 worksheet, change the value in cell D

8

to 280, and then clear the contents in cell A

13

.

2.0

00

5

On the Session1 worksheet, in cell H7, create a formula that will calculate the total fees collected for the Introduction to Watercolor class.

1

0.000

6

On the Session1 worksheet, use the fill handle to copy the formulas in cells E7 and H7 down through row 11.

1

6.0

0

0

8.000

Display the formulas on the Session1 worksheet, and then hide the formulas on the spreadsheet. Delete the worksheet labeled Sheet1.

On the Session1 worksheet tab, insert a new row above row 5.

4.0

00

9

On the Session1 worksheet, move the range A3:B3 to A

14

:B14.

On the Session1 worksheet, delete row 3.

On the Session1 worksheet, copy the range A5:E11. Click on the Class Fees worksheet tab, and then paste the copied range into cell A4.

5.25

12

On the Class Fees worksheet, change the width of column A to 30.00 (2

15

pixels). Change the width of columns D and E to 1

4.5

7 (107 pixels).

6.000

On the Session1 worksheet, copy the range A1:H2. Paste the values (without formatting) onto the Class Fees worksheet in cell A1.

5.0

00

On the Class Fees worksheet, merge and center the text in cell A1 across the range A1:E1, and then merge and center the text in cell A2 across the range A2:E2.

On the Class Fees worksheet, select the range A4:E5 and add a bottom border. With the range still selected, change the fill color to Tan, Background 2.

16

On the Class Fees worksheet, apply the Accounting Number format with zero decimal points to the range D6:E10.

1.2

17

On the Class Fees worksheet, change the page setup options so that the data is centered horizontally on the page.

18

On the Class Fees worksheet, create a header that will display the text Class Fees in the center section of the header area. Print preview the Class Fees worksheet. Return to Normal view.

19

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Class Fees; Session1; Session2; Session3. Save the workbook. Close the workbook, and then exit Excel. Submit the workbook as directed.

Total Score

100.0

00

64.450

Start Excel. Open the downloaded Excel workbook named Exploring_e02_Grader_EOC.xlsx.

In the Circular Referencing Warning message box, click OK, and then close the Help window that opens automatically. Assign the name Membership to the range A18:C

20

.

In cell B2, enter a function that will return the value of today’s date.

In cell C5, create a VLOOKUP function that returns the membership cost that corresponds with the membership type entered in cell B5. The function will look for an exact match in the range named Membership, and return the membership cost from the second column.

Copy the VLOOKUP function down to cells C6:C13.

3.0

In cell E5, enter an IF function that will calculate the annual total. If the client has a Locker (D5) is “Yes”, then add the Cost (C5) to the Locker Fee (B22). Otherwise, the function will return the Cost only. Ensure that B22 is entered as an absolute reference.

Copy the IF function down to cells E6:E13.

In cell G5, enter a formula that will multiply the Annual Total (E5) by Years (F5) to calculate Total Due. Copy the formula down to cells G6:G13.

7.0

In Cell H5, create a VLOOKUP function that returns the down payment that corresponds with the membership type entered in cell B5. The function will look for an exact match in the range named Membership, and return the down payment from the third column.

Copy the VLOOKUP function down to cells H6:H13.

The formula in cell I5 is incorrect, and contains a circular reference. Edit the formula so that is calculates the difference between Total Due and Down Payment. Copy the formula down to cells I6:I13.

In cell J5, enter a function to calculate a monthly payment for the client. The function will refer to the annual interest rate (B23) divided by B24, a loan term (F5) multiplied by B24, and the balance due (I5). Refer to B23 and B24 as mixed cell references (B$23 and B$24). Refer to I5 as a negative value (-I5) so that your result returns a positive number.

Copy the payment function down to cells J6:J13.

In cell H18, enter a function that counts the number of entries in the range A5:A13.

In cell H19, enter a function that finds the lowest value in the range J5:J13.

In cell H20, enter a function that finds the average value in the range J5:J13.

In cell H21, enter a function that finds the highest value in the range J5:J13.

In cell H22, enter a function that finds the median value in the range J5:J13.

Format the range H19:H22 with the Accounting format.

Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

56.0

Start Excel. Open the downloaded Excel workbook named Exploring_e02_Grader_IR.xlsx.

In cell G5, enter a formula that calculates the state sales tax by multiplying the base cost of the car (in F5) by 7%. Copy the formula down to cells G6:G16.

11.0

In cell H5, enter a formula that adds the sales tax (in G5) to the base price of the car (in F5). Copy the formula down to cells H6:H16.

In cell I5, enter a function to calculate a monthly payment for buyers. The function will refer to the annual interest rate (in D21) divided by 12, a loan term of 48 months, and the total cost of the car (in H5). Refer to the annual interest rate (D21) as an absolute cell reference. Copy the function down to cells I6: I16.

In cell J5, create a VLOOKUP function that returns the salesperson commission that corresponds with the base cost of the car (in F5). Use the range $C$24:$D$28 as the Lookup Table, and return the commission rate from Column 2.

Copy the VLOOKUP function down to cells J6:J16.

In cell G19, enter a function to sum cells H5:H16.

In cell G20, enter a function to average the cells in the range H5:H16.

In cell G21, enter a function to find the lowest value in the range H5:H16.

In cell G22, enter a function to find the highest value in the range H5:H16.

In cell G23, enter a function that will return the value of today’s date.

In cell G24, enter an IF function that will calculate a bonus for the part-time sales agents. If the value in cell G19 (Total for all sales) is greater than 250000, then multiply G19 by .015. Otherwise, the function will return a value of 0 (i.e. no bonus).

Format cell G24 with the Currency format and two decimal places.

In cell C17, enter a function that counts the number of entries in the range C5:C16.

8

9.0

Start Excel. Download and open the workbook named Exploring_e01_Grader_EOC.xlsx.

Enter the text Royalty Statement in cell A1. Merge and center the title over the range A1:C1. Change the font size of the title to 16, and apply bold formatting. Change the font color to Purple (under Standard Colors).

Insert a new row between rows 13 and 14. Enter Standard Discount Rate in cell A14, 0.55 in cell B14, and 0.5 in cell C14. Format the two values in B14 and C14 as Percent Style.

In cell B15, enter the formula to calculate the price to the bookstore. The price to the bookstore is calculated by subtracting the standard discount from the retail price. The discount is calculated by multiplying the retail price (B13) by the standard discount rate (B14).

In cell B16, enter the formula to calculate the net retail sales. The net retail sales number is found by multiplying the net units sold by the retail price (B13). The net units sold is calculated by subtracting the returned units (B9) from the gross units sold (B8).

In cell B20, enter the formula to calculate the royalties to the author. Royalties are calculated by multiplying the net retail sales (B16) by the applicable royalty rate (B19).

In cell B21, enter the formula to calculate the royalty per book. This amount is calculated by multiplying the retail price of the book (B13) by the royalty rate (B19).

Copy the formulas from the B15:B16 to C15:C16 and from B20:B21 to C20:C21.

3.00

Apply the Accounting Number Format to the monetary values in the ranges B13:C13, B15:C16, and B20:C21.

7.50

Apply bold formatting and the Purple, Accent 4, Lighter 40% fill color to the Units Sold heading in cell A7.

Indent the text in cells A13:A16 two times.

Apply an Outside Border (default settings) to the range A7:C10. Apply the same border style to the ranges A12:C16 and A18:C21.

Insert a new worksheet on the right side of the Jacobs worksheet. Rename the new worksheet Lopez. Change the Lopez sheet tab color to Dark Blue (under Standard Colors).

Copy the data from the range A1:C21 in the Jacobs worksheet, and paste it into the same cells in the Lopez worksheet.

Change the following values on the Lopez worksheet:

Cell:

New value:

B4

Lopez

B8

5000

C8

15000

B9

400

C9

175

B13

19.95

C13

7.95

Select both worksheets and center the data horizontally on the page when printed. Insert a custom header with the name Cecil Kramer in the left section, the Sheet Name code in the center, and the File Name code in the right section.

With both sheets selected, select the options to display the gridlines and row and column headings when printed.

On the Lopez worksheet only, display the cell formulas.

In Normal view, on the Lopez worksheet, adjust the width of column A to 11.00 (165 pixels). Adjust the widths of columns B and C of the Lopez worksheet to 7.00 (108 pixels).

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Jacobs, Lopez. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

100.00

83.50