PLease help me

Step

Instructions

1

Open the downloaded file, e03ch05_grader_pc_GolfSales.xlsx. Save the workbook as e03ch05_grader_pc_GolfSales_LastFirst using your last and first name. If a Security Warning message displays, click the Enable Editing button.

2

On the DataInputs worksheet, in cell I48, paste the current named ranges.

On the Transactions worksheet, in cell Q9, enter an IF function that will test if the value in cell D9 is equal to Ccard. If the value is true, the result will be Charge. If the value is false, the result will be blank. Copy the formula down to cell Q30.

3

On the Transactions worksheet, in cell M9, enter an IF function that will determine if the value in cell H9 is not equal to the value in cell L9. If the value is true, the result will be No Match. If the value is false, the result will be Coupon Match. Copy the formula down to cell M30.

4

On the RevenueReport worksheet, in cell E21, enter an IF function that determines if the SUM of the Trans_Qty range is greater than cell E20. If the value is true, then Goal Met is the result. If the value is false, then Under Goal is the result.

5

On the Transactions worksheet, in cell R9, enter an IF function to determine if the value in P9 is less than the value in cell K20 on the DataInputs worksheet. If the value is true, then the results will be cell I20 on the DataInputs worksheet. If the value is false, then the result will be Everything Else. Be sure to use absolute references where necessary. Copy the formula down to cell R30.

6

On the Transactions worksheet, click cell R9, and then press F2 to enter edit mode. Select "Everything Else", including the quotes, and replace it by entering an IF function that determines if the value in cell P9 is less than the value in cell K21 on the DataInputs worksheet. If the value is true, then the result is the value in cell I21 on the DataInputs worksheet. If the value is false, then the result is the value in cell I22 on the DataInputs worksheet. Be sure to use absolute references where necessary. Copy the formula through cell R30.

7

Click cell R9, and then press F2 to enter edit mode. Select DataInputs!$I$22, replace it by typing IF(P9

Copy the edited formula through cell R30.

8

On the EmployeeReport worksheet, and click cell B17 and enter an IF function with a nested AND function. For the logical test, enter an AND function to test if the value in cell B4 is Asst Manager and if the value in cell B9 is greater than 0.25. If both tests are true, then the result will be On Target or else the result will be Increase Sales.

9

On the Transactions worksheet, click cell L9 and enter an IF function with a nested OR function. The logical test will use the OR function to determine if F9 is equal to cell I5 on the DataInputs worksheet or if F9 is equal to I7 on the DataInputs worksheet. If either test is true, then the result will be Non_Local. If both tests are false, then the result will be Local. Be sure to use absolute references where necessary. Copy the formula down to cell L30.

10

On the Transactions worksheet, click cell S9, enter an IF function with a nested NOT function. The logical test will use the NOT function to determine if the value in I9 is Accessories. The result if true will be Big Ticket Item or else the result if false will be blank.

Copy the formula down to cell S30.

11

On the Transactions worksheet, click cell T9. Enter an IF function with an AND with nested OR functions. The logical test will be an AND function. The first logical argument of the AND function will determine if the value in cell K9 is equal to Morning or if the value in K9 is equal to Afternoon. The second logical argument of the AND function will determine if the value in R9 is equal to High or if the value in R9 is equal to Ultra. The value if true will be 1. The value if false will be 0.

Copy the formula down to cell T30.

12

On the Transactions worksheet, click cell U9. Enter an IF function. The logical test of the IF function is an OR function where the first logical test is an AND function. The AND function tests to see if the value in cell H9 equals the value of cell I4 on the DataInputs worksheet and if the value in cell P9 is greater than the value of cell J14 on the DataInputs worksheet. Logic2 of the OR function is a second nested AND function that tests to see if the value of cell H9 is equal to the value of cell I13 on the DataInputs worksheet and the value in cell P9 is greater than the value of cell J14 on the DataInputs worksheet. If the value of the logical test is true, the result is 1. If the value is false, the result is 0.

Be sure to use absolute references where necessary to be able to copy the formula to cell U30.

13

On the RevenueReport worksheet, in cell B9, enter a COUNTIF function that uses the named rangeTrans_Group as the range and cell I20 on the DataInputs tab as the criteria. Copy the formula down, without formatting, to cell B12.

In cell E16, enter a COUNTIF function that uses the named range Coupon_Num as the range and cell E13 as the criteria.

In cell E4, enter a COUNTIF function that uses the named range Line_Item_Total as the range and ">="&D4 as the criteria.

14

On the RevenueReport worksheet, click cell F8. Enter a COUNTIFS function. For Criteria_range1, enter the named range Shift. For Critera1, enter an absolute reference to cell E6. For Criteria_range2, enter the named range Coupon_Target. For Criteria2, enter E8. Copy the formula, without formatting, to cell F11.

15

On the RevenueReport worksheet, click cell B15. Enter an AVERAGEIF function where the range is the named range Cust_Cat. The criteria type is the value entered in cell B14, and the average range is the named range Line_Item_Total.

16

On the RevenueReport worksheet, click cell B16 and enter an AVERAGEIFS function.

The Average_range is the named range Line_Item_Total.

The Criteria_range1 is the named range Cust_Cat.

The Critera1 is $B$14.

The Criteria_range2 is the named range Pay_Type.

The Criteria2 is A16.

Copy the formula through cell B18.

17

On the RevenueReport worksheet, in cell B22, enter a SUMIF function that uses the named range Shift for the range, the criteria from cell B21, and Line_item_Total for the sum range.

In cell B4, enter a SUMIF function that uses Cust_Cat for the range, the criteria from cell A4, and Line_Item_Total for the sum range.

Copy the formula through cell B6.

18

On the RevenueReport worksheet, click in cell B25 and enter a SUMIFS function that uses the named range Line_Item_Total for the Sum_range, the named range SKU_Cat for the first criteria range, cell reference $A25 for first criteria, the named range Cust_Cat for second criteria range, and the cell reference B$24 for the second criteria. Copy the formula through cell B28. With the range B25:B28 selected, copy the formula to cell range B25:D28.

On the EmployeeReport worksheet tab, in cell B6, enter a SUMIF function that uses the named range Emp_ID as the range, the criteria in A6, and the named range Line_Item_Total as the sum range.

In cell B13, enter a SUMIFS function that used the named range Line_Item_Total as the sum range, SKU_Cat as the first criteria range, criteria in cell B11 as the first criteria, the named range Emp_ID as the second criteria range, and the criteria in cell A6 as the second criteria. Divide the SUMIFS function by cell B12.

In cell B15, add two SUMIF function together. The fist SUMIF function uses the named range Emp_Id as the range, the criteria in cell A6, and the named range Sales_Point1 as the sum range. The second SUMIF function uses the named range Emp_ID as the range, the criteria in cell A6, and the named range Sales_Point2 as the sum range.

19

Click the DatabaseTotals worksheet. Click cell G2, type Non_Local and then press ENTER. This value is the constraint for filtering the records; only records that are nonlocal customers will be used in the DSUM function. In cell B5, enter a DSUM function that uses the named range DataSet as the Database, cell B4 as the field, and the range A1:K2 as the criteria.

Click cell A2, type Check and then press ENTER. This changes the value reflected in B5 immediately to $32.95.

20

Click the EmployeeReport worksheet. In cell B16, enter an approximate match VLOOKUP function that looks up the value in cell B15 from column 2 of the IncentivePts table array.

21

On the Transactions worksheet, in cell O9, enter an exact match VLOOKUP function that looks up the value of cell C9 from column 6 of the SKU_List table. Copy the formula down to cell O30.

In cell N9. enter an exact match VLOOKUP function that looks up the value of cell F9 from column 2 of the Coupons table array. Copy the formula down to cell N30.

In cell I9, enter an exact match VLOOKUP function that looks up the value in cell C9 from column 3 of the SKU_List table array. Copy the formula down to cell I30.

22

On the DataInputs worksheet, select range B12:G17. Name the selected range AH_ReportTable. With range B12:G17 still select, name the column data using the names in the top row (row 12) as the range names.

On the RevenueReport worksheet, in cell E8, enter an exact match HLOOKUP function that looks up the value in cell F6 from the AH_ReportTable table array. For the Row_index_num, type D8+1.

Copy the formula down through E11.

On the Transactions worksheet, in cell K9, enter an approximate HLOOKUP function that looks up the value in cell B9 from row 2 of the Shifts table array. Copy the formula down to cell K30 to overwrite the static values.

On the EmployeeReport worksheet, in cell B8, enter an exact match HLOOKUP function that looks up the value in cell A4 from row 6 in cell range B4:F9 of the DataInputs table array. Click cell B9, enter the formula =B6/B8.

23

On the RevenueReport worksheet, in cell E14, enter a MATCH function that has a lookup value of E13, from the lookup array range name Coupon_Num with a match type of 0.

24

On the RevenueReport worksheet, in cell E15, enter the formula =INDEX(Trans_Qty,E14)*INDEX(Retail,E14).

On the EmployeeReport worksheet, in cell B20, enter the formula =INDEX(Level_1,A20). Copy the formula down to B22.

Click cell B4. Type =MATCH(A4,DataInputs!L27:L31).

Press F2 to edit cell B4. Click in the Formula Bar to position the insertion point to the right of the = sign, and then type INDEX(DataInputs!I27:I31,

Position the cursor at the end of the formula, type ) and then press ENTER.

25

On the Transactions worksheet, in cell J9, enter the formula =INDEX(DataInputs!$I$27:$I$31,MATCH(G9,DataInputs!$J$27:$J$31,0)). Fill the formula down through J30.

Click the EmployeeReport worksheet, and then click cell B12.

On the EmployeeReport worksheet, in cell D12 and D13, enter two test functions.

In cell D12, type =MATCH(B11,DataInputs!A5:A9,0)

In cell D13, type =MATCH(A4,DataInputs!B4:F4,0)

Click cell B12. Since the MATCH functions work, the complex function can be constructed. Type =INDEX(Goals,MATCH(B11,DataInputs!A5:A9,0),MATCH(A4,DataInputs!B4:F4,0))

Select cells D12:D13, press DELETE to remove the test functions.

26

On the EmployeeReport worksheet, in the formula in cell B20, replace Level_1 with =INDIRECT($B$19). Copy the formula to B22.

27

On the Transactions worksheet, in cell I9, edit the formula to include the IFERROR function that will return a blank if there is an error with the result in the formula. Copy the edited formula to cell I30.

In cell J9, edit the formula to include an IFERROR function that will return a blank if there is an error with the result in the formula.

Click cell K9, edit the formula to include an IFERROR that will return a blank if there is an error with the result in the formula. Copy the edited formulas in J9:K9 through cells J30:K30.

In cell L9, edit the formula by adding in IF function the will test if the value in cell F9 is not equal to blank. If the value is false, the result will be blank. Copy the formula down to cell L30.

In cell M9, edit the formula by adding in IF function the will test if the value in cell H9 is not equal to blank. If the value is false, the result will be blank. Copy the formula down to cell M30.

In cell N9,edit the formula include the IFERROR function that will return a 0 if there is an error with the result in the formula.Copy the formula down to cell N30.

Click cell O9, edit the formula include the IFERROR function that will return a 0 if there is an error with the result in the formula. Copy the formula down to cell O30.

Click cell P9, enter the following function =ROUND(O9*E9-(O9*E9*N9),2). Copy the formula down to P30.

28

On the Transactions worksheet, in cell R9 edit the formula by adding in IF function the will test if the value in cell A9 is not equal to blank. If the value is false, the result will be blank. Copy the formula down to R30.

In cell S9,edit the formula by adding in IF function the will test if the value in cell A9 is not equal to blank. If the value is false, the value is false, the result will be blank. Copy the formula down to S30.

29

Ensure that the worksheets are correctly named and placed in the following order in the workbook: RevenueReport, EmployeeReport, Transactions, DataInputs, and DatabaseTotals. Save the workbook, exit Excel and submit your file as directed by your instructor.

A1 X v fx Revenue Report B C D E F G Revenue Report 2 3 Customer Type Revenue Ad Hoc Transaction Listing Number of transactions >= to value 4 Non_Local 25.00 5 Local 6 Unknown shift Category Morning AH_Coupon_Targets Transaction Count Report Category Coupon_Target 8 Transaction Listing Number of transactions 1 Local 9 LOW - Less than $25 Non_Local 0 MEDIUM-$25 or more and less than $100 W N 11 HIGH - $100 or more and less than $250 A 12 ULTRA - $250 or more 13 Coupon T453X 14 Customer Type Local First Transaction Used 15 Average Sale Amount of transaction 16 Cash Number of times coupon used 17 Check 18 Ccard 19 Revenue Goal S 1,000.00 20 Qty Sold Goal 50 21 Morning Qty Sold Status 22 Sum of Shift Qty Sold and Revenue Status 23 24 Sum of Revenue Local Non_Local Unknown 25 Clothing 26 Clubs 27 Accessories 28 Shoes 29 30 31 32 33 34 35 36 38 RevenueReport EmployeeReport Transactions Datalnputs DatabaseTotals +A1 V fx Employee Sales Report A B C D E F G H Employee Sales Report Sales Staff Position Hample Asst Manager Staff ID Number Total Sales Revenue 6 15 Overall Goal $ 1,250.00 9 Percent of Goal 10.2% 10 11 Category Clothing 12 Category Goal S 200.00 13 Percent of Category Goal 14 15 Incentive Points 16 |Incentive Points Status 17 Asst Mgr Status 18 19 Reward Level Level_1 20 21 22 W NH 23 36 37 38 RevenueReport EmployeeReport Transactions Datalnputs Database Totals +B C D G Transactions 0 Q Range of possible Data A9:H208 Raw Data Import Area Calculated Fields Trans_ID Trans_Time SKU POO0023455 Pay_Type Trans_Qty Coupon_Num Emp_ID Cust_Cat 7:02 AM T981011 Cash SKU_Cat Emp_Position Shift 7:34 AM R483P24 Cash 3 38 Local Coupon_Target Coupon_Hit Accessories |Associate Coupon_Amt Retail Card_Charge Big_Ticket_Item Sales_Point1 Sales_Point2 9:34 AM X349R39 Cash 24 Non_Local Morning Line_Item_Total Local Coupon Match Trans_Group Accessories 0%% $ 12.00 $ 36.00 POD0023457 Asst Manager Morning 24 Non_Local Local No Match Clothing Asst Manager Morning Local 0% $ 2.00 5 Medium 00023458 10:15 AM X487P48 Ccard 1 T453X 15 Non_Local No Match Clubs Associate Morning Non_Local 0% $ 49.95 5 2.00 Low 49.95 Coupon Match Medium Big Ticket Item 1:49 AM T981011 Check 1:45 PM C584T23 Ccard 38 Local Accessories Associate 15%% $ 419.00 5 Morning 38 Unknown Local Coupon Match Shoes Associate Afternoon Local 0% $ 12.00 5 356.15 |Charge Ultra Big Ticket Item 24.00 2:25 PM C583K29 Ccard 2 1382p 15 Loca No Match Asst Manager 0% $ 49.00 5 Low Shoes Local 49.00 Charge Coupon Match Medium Big Ticket Item 2:34 PM| R483P24 Cash Afternoon 38 Local Accessories Associate 5% $ 59.00 $ Afternoon Local 0% 5 2.00 5 56.05 |Charge Medium 2:51 PM F833K19 Cash w 38 Non Local Coupon Match Big Ticket Item Accessories Associate Local 6.00 No Match Accessories Asst Manager 0%% $ 5.95 5 Low 1:23 PM F375P67 Ccard Afternoon Low 1:41 PM C483789 Ccard 24 Non_Local 17.85 1 T453X Afternoon Local 5:59 PM C492R32 Ccard 15 Unknown Clothing No Match Asst Manager 1 26 Non_Local |Afternoon Non_Local 0% $ 15.45 5 No Match Associate Evening 15% $ 14.95 5 30.90 Charge Medium Clothing Local No Match 0% $ 39.95 5 12.71 Charge Low Big Ticket Item 6:04 PM| C581T49 Cash 2 1382P 26 Local Accessories Associate Evening Local 39.95 |Charge Coupon Match 5% 5 11.39 5 Medium Big Ticket Item 6:11 PM T981011 Ccard 7:18 PM F833K19 Check 7:21 PM T822748 Check W N 76 Local Accessories Associate 76 Unknown Evening Local 21.64 Low 2 C384R Accessories Associate Coupon Match Local 0%% $ 12.00 5 24.00 Charge Low 7:32 PM F232P37 Ccard 76 Non_Local Evening No Match Clothing Associate Evening Non_Local 0%% $ 5.95 5 17.85 Low 26 Non_Local Coupon Match Accessories Associate 10%% $ 32.95 5 7:39 PM | C583K29 Cash 15 Non Local Evening Local No Match Shoe Asst Manager 0% $ 129.00 5 59.31 Medium Big Ticket Item Evening Local No Match 0% $ 59.00 5 29.00 |Charge High 59.00 Medium Big Ticket Item RevenueReport EmployeeReport Transactions Datainputs Database Totals + Enter 90%D G Sales Goals M N Tyner Hample Thurman Coupons S Accessories 400 Yammel 200 Coupon_Code 6 Clothing 200 T453X Coupon_Per 200 Coupon_Notes 300 300 T382P 15%% Online Travel Promotion Coupon_Target Non Local Shoes SOO 150 700 700 C3848 10%% Local Magazine Promotion Local Total 250 Travel Agent Promotion Non Local 10 1250 1450 1450 11 Ad Hoc Reports 12 AH_Shifts 1 Morning AH_Trans_Groups Low AH_Coupon_Targets Local AH_Customer_Types Non Local AH_Prod_Categories Sales Points AH_Pay_Types Accessories Customer 2 Afternoon Medium Transaction target 3 Evening High Mon_Local Local Clothing Cash Non_Local Ccard Local 100.00 Unknown Clubs 50.00 Data Name to count shift Ultra Trans Group Coupon Target Customer Type Shoes |Check 18 Prod Ca Pay Type 19 Shifts Transaction Groups 0 Shift Start Time 21 Shift 6:00 AM 12:00 PM Morning 5:00 PM Greater than or equal Low Less than 22 Afternoon Evening Medium 25 High 25 100 Ultra 350 25 250 26 Item ID Employees 27 Description Category 28 Windbreaker (Resort) Wholesale Dothing Unit oxition Windren Pro (right-handed Clubs 29.00 LG Retail Price 242 00 49.95 ID First Name 29 1981011 Asst Manager Last Name 30 Resort golf balls Accessories 3.00 6 pi 419.00 12 00 Asst Manager 15 Ramal Trimble Comforts Assodate 24 Pat Hample 49.00 16 Tyrone Tyner 31 Trimbell Striders Shoes Shoes 27.00 Thurman 32 8483P24 Resort Tees Accessories 29.00 59.00 Assodate Assodate as Jemral Willcox 33 Resort divot key chain Accessories 123 2.00 76 Christy Yammel F375P57 Grabber golf gloves Accessories 5.95 C48318 C492R32 Resort cap Dothing 3.45 5.44 15.45 Incentive System Points 37 CS81749 Resort polo shirt Clothing 14.95 golf shoe Insert Accessories 17.00 39 95 O Get those points cranking golf button, shirt 4.32 11 39 15 Level 1 rewards T822748 golf button, shirt Clothing 25 Level 2 rewards 40 Clothing 12 00 X 56 ZE golf button, shirt Clothing 12 00 LG 32 95 so Level 3 rewards 41 X740029 Windren pro (left-handed) 12.00 MED 32 95 100 Level 4 rewards 42 Clubs 255.00 453.00 C8.84P23 Resort golf bag (elite) 79.00 Reward Levels with Rewards R239157 Resort golf bag (carryall) Accessories Accessories 129.00 Level 1 Resort golf easy go pull cart Level_2 Accessories 38.00 Level_3 19.00 37.00 97.00 CoffeeShop Gift Card, $5 Coffeeshop Gift Card, $10 Restaurant Gift Card Pizza Night Level_4 Level S Movie Gift Card Discount 5%% for massage 10% for massage Movie Night Shopping spree Steak dinner for two Gardener's Delight Zoo Package Massage Package Tech Shop Gift Card Golf Outing Named Ranges RevenueReport EmployeeReport Transactions DataInputs DatabaseTotals +A C D E F G H Pay_Type Trans Qty Cust_Cat SKU_Cat Emp_Position Shift Coupon_Target Coupon_Hit Retail Line Item_Total Card Cha Retail 5 Sum of Filter 10 11 12 13 RevenueReport EmployeeReport Transactions Datalnputs Database Totals + Ready