# CSIS2010 The Red Bluff Golf Course Transactions worksheet

*label*Business Finance

*timer*Asked: Feb 7th, 2019

*account_balance_wallet*$30

### Question Description

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 I14 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 J13 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**.

13-On the RevenueReport worksheet, in cell B9, enter a COUNTIF function that uses the named range**Trans_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 |

18-

On the RevenueReport worksheet, click in cell B25 and enter a SUMIFS function that uses the named range 19- Click
the DatabaseTotals worksheet. Click cell G2, type Click cell A2, type 20-
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-
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.

### Unformatted Attachment Preview

**Tags:**CSIS2010 COUNTIF function VLOOKUP function IFERROR function Transactions worksheet IF function

## Tutor Answer

Please let me know if you need anything to b

Revenue Report

Customer Type

Non_Local

Local

Unknown

Revenue

$

$

$

Transaction Listing

LOW - Less than $25

MEDIUM - $25 or more and less than $100

HIGH - $100 or more and less than $250

ULTRA - $250 or more

Number of transactions

8

7

2

1

Customer Type

Average Sale

Cash

Check

Ccard

Local

$

$

$

$

Sum of Shift

Morning

$

468.10

Sum of Revenue

Clothing

Clubs

Accessories

Shoes

Local

$

$

$

$

Non_Local

$ 149.21

$ 356.15

111.64 $ 179.75

112.10 $ 59.00

744.11

223.74

79.56

37.29

21.21

24.00

68.05

2019020812452020190207195524e03ch05_grader_pc_golfsales__2_.xlsx

Ad Hoc Transaction Listing

Number of transactions >= to value

$

25.00

Shift Category

Transaction Count Report

1

2

3

4

Morning

Category

Coupon

First Transaction Used

Amount of transaction

Number of times coupon used

T453X

AH_Coupon_Targets

Coupon_Target

Local

#N/A

#N/A

#N/A

Revenue Goal

Qty Sold Goal

Qty Sold Status

Qty Sold and Revenue Status

Unknown

$

$

$

$

10

$

$

0

0

0

0

4

419.00

2

1,000.00

50

Under Goal

12.71

17.85

49.00

2019020812452020190207195524e03ch05_grader_pc_golfsales__2_.xlsx

Employee Sales Report

Sales Staff

Hample

Staff ID Number

15

Overall Goal

Percent of Goal

Category

Category Goal

Percent of Category Goal

Incentive Points

Incentive Points Status

Asst Mgr Status

Reward Level

Position

Asst Manager

Total Sales Revenue

$

539.96

$

Clothing

$

1,250.00

43.2%

200.00

6%

4

Get those points cranking

On Target

Level_1

1 CoffeeShop Gift Card, $5

2 Movie Gift Card

3 Discount 5% for massage

2019020812452020190207195524e03ch05_grader_pc_golfsales__2_.xlsx

Transactions

Range of possible Data

A9:H208

Raw Data Import Area

Trans_ID

Trans_Time SKU

Pay_Type Trans_Qty Coupon_Num

P000023455

7:02 AM T981Q11 Cash

3

P000023456

7:34 AM R483P24 Cash

1

P000023457

9:34 AM X349R39 Cash

1

P000023458

10:15 AM X487P48 ...

*flag*Report DMCA

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors