PLease help me answers the following:

1

Open the downloaded file e03ch06_grader_pc_GolfMarketingA. Save the file as e03ch06_grader_pc_GolfMarketingA_LastFirst using your last and first name.

2

On the GolfData worksheet, insert 10 blank rows moving the data set down to row 11.

CreateAn Excel data table, with headers, from the data in A11:H41.

Name the table GolfData

Using the data in A11:H41, create range names from the top row of each column.

3

Add a column to the table named Tax in cell I11.

In I12, enter a formula that multiples the value in cell B3 on the GolfReport worksheet by a structured reference to cell E12 on the GolfData worksheet. Use an absolute reference where necessary.

Format the Tax column with Accounting Number format.

Note, Mac users, copy the formula down through the column, if necessary.

4

Filter data for party size of 2.

Filter the data for food orders greater than 60.

Copy the filtered data including the column headers in row 11 and paste the filtered data in cell A1 of a new sheet named Filter. Autofit the column width of the pasted data.

5

On the GolfData worksheet, copy the table headings in row 11 to cell A1.

Clear all table filters.

In F2, type Hotel. CreateAn advanced filter using A1:I2 as the criteria range.

In E2, type >100. In H2, type TX. CreateAn advanced filter using A1:I2 as the criteria range.

In D3, type 1. In F3, type Local. CreateAn advanced filter using A1:I3 as the criteria range.

Copy the filtered data including the column headers in row 11 and paste it in cell A1 of a new worksheet named Filter2. Autofit the column width of the pasted data.

6

On the GolfData worksheet, insert a slicer for the Cust_Type field. Move the slicer so the top left corner is in cell L2; drag the bottom edge so there is no extra white space.

Select Unknown and Local in the slicer, and then clear the filter. Then click Unknown.

Insert a second slicer for the Home_State field. Move the slicer so the top left corner is in cell L11.

Change the caption of the Home_State slicer to Home State. Change the size and properties of the Home State slicer to 3 columns.

Change the caption of the Cust_Type slicer to Customer Type. In the Customer Type slicer, select Mix.

Apply the Slicer Style Dark 2 to the Customer Type slicer. Apply the Slicer Style Dark 2 to the Home State slicer. Note, depending on the version of Office used, the slicer style name may be Light Orange, Slide Style Dark 2.

Note, Mac users, do not create slicers. Instead, on the GolfData sheet, filter the data for records with the Cust_TypeAs Mix.

7

On the GolfData worksheet, in cell A5, type Average

In cell B6, type Overall

In cell B7, type Filtered

In cell B8, type By State

In cell B9, type Customer Type

In cell D8, type NM

In cell D9, type Hotel

Click cell E8, type Number of Records

In cell E9, type Sum of Food Order

Select columns B:E, and then auto fit the column width.

Bold the text in cell ranges A5:B9, and E8:E9.

8

In cell C6, enter an AVERAGE function to average the named range Food_Order.

In cell C7, enter a SUBTOTAL function that averages the named range Food_Order.

Select cell range C6:C9, press and hold CTRL, and then select cell F9. Click the Home tab, and then in the Number group, click Accounting Number Format

9

In cell C8, enter an AVERAGEIF function that uses the named range Home_State for the range, cell D8 for the criteria, and the named range Food_Order for the average range.

In cell C9, enter an AVERAGEIF function that uses the named range Cust_Type for the range, cell D9 for the criteria, and named range Food_Order for the average range.

10

In cell F8, enter a SUBTOTAL function that counts the named range Num.

In cell F9, enter a SUBTOTAL function that will sum the named range Food_Order.

11

Save the workbook, exit Excel, and then submit your workbook according to your instructor's directions with the worksheets in the following order: GolfData, Filter2, Filter, GolfReport.

Please note: This project continues in part B, which can be found in the Product Shared Library.

B C D E F G H 1 Num Date Tee_Time Party_Size Food_Order Cust_Type Carts Home State 98 1/8/15 9:54:00 AM 4 $ 155.20 Hote 2 MI 102 2/16/15 8:36:00 AM 2 5 63.90 Hotel 1 FL 106 3/21/15 7:36:00 AM AS 114.60 Unknown 31 111 3/30/15 7:12:00 AM S 90.00 Local 2 NM 120 5/30/15 3:00:00 PM 2 5 30.20 Local ONM 122 7/19/15 8:42:00 AM 3 5 90.30 Hotel 3 AZ LD 00 126 8/11/15 11:00:00 AM 3 71.25 Unknown 2 135 2/23/16 9:42:00 AM 4 5 136.00 Mix 2 KS 10 138 3/30/16 2:06:00 PM 3 S 112.20 Hotel 2 MN 11 145 4/5/16 11:36:00 AM 59.10 Hotel 1 LA 153 9/19/16 12:18:00 PM 1 25.25 Mix 2 MI 13 155 11/18/16 12:36:00 PM $ 69.00 Local 2 NM 14 158 12/10/16 4:54:00 PM 133.00 Local ONM 15 165 12/24/16 8:48:00 AM 1 $ 20.40 Local 1 NM 16 169 1/15/17 8:12:00 AM 3 5 83.70 Mix 1 FL 17 170 1/20/17 7:24:00 AM 3 s 82.95 Local 2 NM 18 176 1/30/17 11:00:00 AM 4 5 123.00 Hotel 2 OK 19 178 2/16/17 4:12:00 PM 2 $ 61.80 Hotel 1 AR 20 179 3/1/17 3:48:00 PM 3 $ 99.00 Local 2 NM 21 182 3/3/17 4:48:00 PM 4 5 143.60 Local 3 NM 22 185 3/8/17 7:06:00 AM 3 $ 120.15 Mix 2 MO 186 3/12/17 9:00:00 AM 3 114.60 Hotel 3 AZ 24 187 4/26/17 4:18:00 PM 3 112.65 Hotel 2 CO 25 189 10/30/17 1:06:00 PM 161.60 Hotel 2 TX 26 190 11/5/17 10:00:00 AM 4 181.40 Hotel 2 NY 27 191 11/13/17 12:36:00 PM 75.40 Local 28 192 12/1/17 11:18:00 AM 3 in in in in in in 2 NM 92.40 Hotel 2 OH 29 195 12/3/17 11:12:00 AM 4 S 108.00 Local 1 NM 30 196 12/13/17 10:00:00 AM 3 $ 111.90 Hotel 2 WI 31 199 12/31/17 11:18:00 AM AS 126.00 Mix 2 CO 32 33 34 246 35 36 GolfData GolfReport +