YO16_XL_CH05_GRADER_PC_HW - Golf Sales 1.2

Project Description:

The Red Bluff Golf Course & Pro Shop generates revenue through its golfers, golfer services, and pro shop sales. Aleeta Herriott, the pro shop manager, receives revenue data on a monthly basis. She would like to have some reports developed that will help to track sales and to analyze her profit margins. She has a workbook started with some sample data and wants you to continue developing some reports. These reports will help Aleeta make educated decisions about the business, such as which items to place on sale or discontinue.

Steps to Perform:

Step

Instructions

Points Possible

1

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

0

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 toCcard. If the value is true, the result will beCharge. If the value is false, the result will be blank. Copy the formula down to cell Q30.

0.75

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 beNo Match. If the value is false, the result will beCoupon Match. Copy the formula down to cell M30.

0.25

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, thenGoal Metis the result. If the value is false, thenUnder Goalis the result.

0.25

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 beEverything Else. Be sure to use absolute references where necessary. Copy the formula down to cell R30.

0.25

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.

0.25

7

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

Copy the edited formula through cell R30.

0.25

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 isAsst Managerand if the value in cell B9 is greater than0.25. If both tests are true, then the result will beOn Targetor else the result will beIncrease Sales.

0.25

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 beNon_Local. If both tests are false, then the result will beLocal. Be sure to use absolute references where necessary. Copy the formula down to cell L30.

0.25

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 isAccessories. The result if true will beBig Ticket Itemor else the result if false will be blank.

Copy the formula down to cell S30.

0.25

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 toMorningor if the value in K9 is equal toAfternoon. The second logical argument of the AND function will determine if the value in R9 is equal toHighor if the value in R9 is equal toUltra. The value if true will be1. The value if false will be0.

Copy the formula down to cell T30.

0.25

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 is1. If the value is false, the result is0.

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

0.5

13

On the RevenueReport worksheet, in cell B9, enter a COUNTIF function that uses the named rangeTrans_Groupas 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 rangeCoupon_Numas the range and cell E13 as the criteria.

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

0.75

14

On the RevenueReport worksheet, click cell F8. Enter a COUNTIFS function. For Criteria_range1, enter the named rangeShift. For Critera1, enter an absolute reference to cellE6. For Criteria_range2, enter the named rangeCoupon_Target. For Criteria2, enterE8. Copy the formula, without formatting, to cell F11.

0.25

15

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

0.25

16

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

The Average_range is the named rangeLine_Item_Total.

The Criteria_range1 is the named rangeCust_Cat.

The Critera1 is$B$14.

The Criteria_range2 is the named rangePay_Type.

The Criteria2 isA16.

Copy the formula through cell B18.

0.5

17

On the RevenueReport worksheet, in cell B22, enter a SUMIF function that uses the named rangeShiftfor the range, the criteria from cellB21, andLine_item_Totalfor the sum range.

In cell B4, enter a SUMIF function that usesCust_Catfor the range, the criteria from cellA4, andLine_Item_Totalfor the sum range.

Copy the formula through cell B6.

0.25

18

On the RevenueReport worksheet, click in cell B25 and enter a SUMIFS function that uses the named rangeLine_Item_Totalfor the Sum_range, the named rangeSKU_Catfor the first criteria range, cell reference$A25for first criteria, the named rangeCust_Catfor second criteria range, and the cell referenceB$24for 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 rangeEmp_IDas the range, the criteria inA6, and the named rangeLine_Item_Totalas the sum range.

In cell B13, enter a SUMIFS function that used the named rangeLine_Item_Totalas the sum range,SKU_Catas the first criteria range, criteria in cellB11as the first criteria, the named rangeEmp_IDas the second criteria range, and the criteria in cellA6as the second criteria. Divide the SUMIFS function by cellB12.

In cell B15, add two SUMIF function together. The fist SUMIF function uses the named rangeEmp_Idas the range, the criteria in cellA6, and the named rangeSales_Point1as the sum range. The second SUMIF function uses the named rangeEmp_IDas the range, the criteria in cellA6, and the named rangeSales_Point2as the sum range.

1

19

Click the DatabaseTotals worksheet. Click cell G2, typeNon_Localand 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 rangeDataSetas the Database, cellB4as the field, and the rangeA1:K2as the criteria.

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

0.5

20

Click the EmployeeReport worksheet. In cell B16, enter an approximate match VLOOKUP function that looks up the value in cellB15from column2of theIncentivePtstable array.

0.25

21

On the Transactions worksheet, in cell O9, enter an exact match VLOOKUP function that looks up the value of cellC9from column6of theSKU_Listtable. Copy the formula down to cell O30.

In cell N9. enter an exact match VLOOKUP function that looks up the value of cellF9from column2of theCouponstable array. Copy the formula down to cell N30.

In cell I9, enter an exact match VLOOKUP function that looks up the value in cellC9from column3of theSKU_Listtable array. Copy the formula down to cell I30.

0.75

22

On the DataInputs worksheet, select range B12:G17. Name the selected rangeAH_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 cellF6from theAH_ReportTabletable array. For the Row_index_num, typeD8+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 cellB9from row2of theShiftstable 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 cellA4from row6in cell rangeB4:F9of theDataInputstable array. Click cell B9, enter the formula=B6/B8.

0.75

23

On the RevenueReport worksheet, in cell E14, enter a MATCH function that has a lookup value ofE13, from the lookup array range nameCoupon_Numwith a match type of0.

0.5

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 typeINDEX(DataInputs!I27:I31,

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

1

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.

1.5

26

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

0.5

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.

1

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.

0.75

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.

0

Total Points

14