Lab 3_Problems & Exercises (3)

Credit Risk Data (Using database functions , for example "DSUM") Loan Purpose Checking Savings Months Customer Small Appliance $0 $739 13 Furniture $0 $1,230 25 New Car $0 $389 19 Furniture $638 $347 13 Education $963 $4,754 40 Furniture $2,827 $0 11 New Car $0 $229 13 Business $0 $533 14 Small Appliance $6,509 $493 37 Small Appliance $966 $0 25 Business $0 $989 49 New Car $0 $3,305 11 Business $322 $578 10 New Car $0 $821 25 New Car $396 $228 13 Used Car $0 $129 31 Furniture $652 $732 49 New Car $708 $683 13 Repairs $207 $0 28 Education $287 $12,348 7 Furniture $0 $17,545 34 Furniture $101 $3,871 13 Furniture $0 $0 25 Furniture $0 $485 37 New Car $0 $10,723 11 Business $141 $245 22 Used Car $0 $0 19 Used Car $2,484 $0 49 Small Appliance $237 $236 37 Small Appliance $0 $485 19 Education $335 $1,708 37 Small Appliance $3,565 $0 31 Small Appliance $0 $407 13 Business $16,647 $895 16 Business $0 $150 49

Furniture $0 $544 19 Small Appliance $0 $10,853 25 Used Car $374 $0 25 Large Appliance $0 $409 49 Furniture $828 $391 9 Furniture $0 $322 13 Small Appliance $829 $583 7 Small Appliance $0 $12,242 25 Furniture $0 $479 19 New Car $939 $496 19 New Car $0 $466 25 New Car $889 $1,583 37 Furniture $876 $1,533 31 Small Appliance $893 $0 16 Business $12,760 $4,873 13 Furniture $0 $0 13 Small Appliance $0 $717 22 Small Appliance $959 $7,876 28 Small Appliance $0 $4,449 25 Other $0 $0 25 Business $0 $104 25 Repairs $0 $897 19 New Car $698 $4,033 16 Furniture $0 $945 13 Furniture $0 $836 25 Small Appliance $0 $325 19 Small Appliance $12,974 $19,568 13 Furniture $0 $803 13 Small Appliance $317 $10,980 13 Business $0 $265 13 Repairs $0 $609 31 Small Appliance $0 $1,851 12 Furniture $192 $199 25 New Car $0 $500 28 New Car $0 $509 16 Used Car $0 $270 25 New Car $0 $457 13 Used Car $0 $260 25 New Car $942 $3,036 25 Small Appliance $0 $643 19

Small Appliance $0 $956 25 Furniture $0 $636 22 New Car $2,641 $0 13 Used Car $0 $1,519 40 Business $0 $922 19 Furniture $0 $180 5 Used Car $0 $701 22 Small Appliance $0 $296 16 Small Appliance $887 $519 7 Business $0 $800 49 Furniture $0 $736 13 Small Appliance $0 $11,838 7 Small Appliance $0 $364 5 New Car $18,408 $212 13 New Car $497 $888 16 Used Car $0 $999 25 Small Appliance $946 $0 16 Business $986 $578 28 Education $8,122 $136 22 Furniture $0 $734 37 Business $778 $861 49 Other $645 $855 25 Furniture $0 $4,486 10 New Car $682 $2,017 37 New Car $19,812 $0 25 Business $0 $500 25 Used Car $0 $859 31 Business $859 $3,305 25 Small Appliance $0 $1,218 13 New Car $0 $9,016 49 New Car $0 $11,587 22 Furniture $0 $8,944 25 Repairs $0 $807 25 Small Appliance $0 $867 31 Small Appliance $795 $16,804 49 Furniture $0 $347 16 Furniture $0 $836 16 Furniture $0 $142 7 Furniture $0 $169 19 Other $852 $3,613 61

Small Appliance $2,846 $0 13 Small Appliance $231 $702 10 Repairs $0 $2,688 10 Small Appliance $17,366 $0 16 Small Appliance $0 $425 13 New Car $332 $214 25 Small Appliance $242 $0 19 New Car $0 $272 7 Business $929 $124 9 Small Appliance $0 $17,124 13 Used Car $0 $612 49 New Car $0 $862 49 Furniture $0 $146 25 New Car $0 $14,190 37 Used Car $0 $396 49 Small Appliance $0 $519 31 Used Car $646 $0 25 New Car $538 $344 13 Furniture $0 $204 31 Small Appliance $0 $148 43 Furniture $0 $435 19 Small Appliance $0 $914 19 New Car $135 $0 37 Used Car $2,472 $0 37 New Car $0 $412 25 New Car $10,417 $19,811 13 Small Appliance $211 $822 8 Small Appliance $16,630 $0 11 Furniture $0 $3,369 25 Furniture $642 $0 13 Small Appliance $0 $707 7 Small Appliance $296 $818 19 Business $898 $177 22 New Car $478 $4,071 10 New Car $315 $466 13 New Car $122 $460 37 Furniture $0 $991 7 Small Appliance $0 $17,653 22 Education $0 $497 41 Business $670 $4,014 31

41 M Single 41 Own 1 M Divorced 54 Own 40 F Divorced 43 Own 42 M Married 33 Own 74 M Single 34 Own 0 M Single 39 Other 26 M Single 34 Own 60 M Single 30 Own 33 M Single 48 Own 1 F Divorced 19 Own 119 M Married 29 Rent 3 F Divorced 26 Rent 75 M Single 42 Rent 8 M Single 30 Rent 5 M Married 38 Own 5 M Married 21 Own 36 F Divorced 32 Rent 6 M Single 35 Own 10 M Divorced 46 Own 28 M Single 37 Own 114 M Single 39 Own 16 F Divorced 29 Own 32 M Single 27 Rent 5 F Divorced 22 Rent 60 M Single 40 Own 10 M Single 44 Own 8 M Single 26 Own 46 F Divorced 57 Own 17 M Single 34 Own 4 M Married 47 Own 115 M Single 46 Own 31 M Single 38 Own 0 F Divorced 52 Own 0 M Single 34 Own 5 M Single 29 Other 9 M Single 39 Own 81 M Single 36 Own 28 M Single 25 Own 69 M Single 59 Own 85 M Single 27 Own

15 M Single 67 Rent 19 M Single 26 Own 9 F Divorced 24 Own 3 M Single 21 Other 8 M Married 46 Own 48 M Single 22 Rent 2 F Divorced 20 Rent 55 M Divorced 60 Rent 37 M Single 25 Own 1 F Divorced 21 Rent 14 F Divorced 22 Own 54 M Single 29 Own 111 M Single 56 Own 2 M Single 32 Rent 14 M Divorced 30 Own 1 M Married 27 Rent 0 F Divorced 37 Own 2 M Single 39 Own 5 M Married 26 Own 19 M Divorced 32 Own 24 M Single 31 Own 14 M Single 35 Own 93 M Single 31 Own 96 M Single 64 Own 2 M Divorced 27 Own 11 F Divorced 31 Own 4 M Married 32 Own 35 M Single 73 Own 48 F Divorced 30 Own 4 M Married 42 Own 85 M Single 45 Own 100 M Single 27 Own 3 F Divorced 22 Rent 7 M Single 26 Own 36 M Single 33 Own 1 F Divorced 27 Own 19 F Divorced 24 Rent 3 M Married 21 Own 0 F Divorced 28 Rent 0 M Single 51 Own

5 F Divorced 55 Own 0 M Single 29 Own 7 F Divorced 32 Own 18 M Single 53 Own 21 M Single 33 Own 65 F Divorced 56 Other 17 M Single 33 Other 49 M Single 46 Other 0 M Single 54 Other 71 F Divorced 22 Own 22 M Single 35 Rent 52 M Single 46 Own 28 F Divorced 29 Own 31 F Divorced 22 Rent 118 M Married 53 Own 36 M Divorced 42 Own 35 M Married 20 Rent 22 F Divorced 35 Own 61 F Divorced 33 Own 17 M Married 26 Own 11 M Single 46 Other 15 F Divorced 28 Own 65 M Single 38 Own 23 M Single 35 Own 14 M Single 47 Own 13 M Single 23 Own 103 M Single 56 Other 24 F Divorced 28 Rent 34 M Single 48 Own 20 M Single 27 Own 15 M Married 23 Own 7 F Divorced 24 Rent 3 F Divorced 22 Own 119 M Single 42 Rent 29 M Single 25 Own 54 F Divorced 47 Other 90 F Divorced 32 Rent 13 M Single 25 Rent 22 M Single 36 Own 28 M Single 25 Own

14 M Married 26 Own 0 F Divorced 53 Own 0 F Divorced 66 Own 36 F Divorced 23 Own 62 M Single 25 Own 4 M Single 24 Own 28 M Single 31 Other 81 F Divorced 57 Other 57 M Divorced 44 Own 0 M Divorced 42 Own 2 F Divorced 27 Rent 67 M Single 51 Own 119 M Single 23 Other 13 M Single 31 Own 2 F Divorced 22 Rent 12 M Single 35 Own 9 M Single 37 Other 107 M Single 35 Own 33 M Single 30 Own 83 M Single 49 Other 24 F Divorced 32 Own 114 M Single 52 Own 5 M Single 45 Own 16 M Married 23 Own 111 M Single 41 Own 18 F Divorced 28 Own 101 M Single 30 Own 102 M Single 50 Own 75 F Divorced 58 Rent 29 M Single 36 Own 42 M Single 23 Rent 77 M Single 48 Own 21 M Single 37 Own 19 M Single 20 Rent 20 F Divorced 23 Own 31 F Divorced 39 Own 9 M Single 34 Other 27 F Divorced 22 Own 30 M Single 21 Own 59 M Single 38 Rent

14 M Single 36 Other 99 M Single 26 Own 89 M Single 47 Own 21 M Single 38 Other 10 M Single 27 Rent 2 M Single 25 Own 6 M Single 28 Own 90 M Single 67 Own 1 M Married 25 Own 95 M Married 34 Own 32 M Single 38 Other 62 M Single 41 Other 46 M Single 26 Own 92 M Single 35 Own 73 M Single 45 Other 23 F Divorced 32 Own 9 M Divorced 47 Other 40 M Married 24 Own 5 M Divorced 30 Own 2 M Single 33 Own 16 F Divorced 23 Rent 0 F Divorced 21 Rent 7 M Single 36 Other 41 M Single 30 Own 22 M Single 52 Other 27 M Married 27 Own 5 F Divorced 44 Own 47 M Single 26 Own 17 M Single 24 Own 65 F Divorced 24 Own 26 M Single 50 Own 93 M Married 31 Own 105 F Divorced 38 Own 40 M Single 28 Own 3 M Single 48 Own 109 M Single 56 Other 3 F Divorced 31 Own 4 F Divorced 28 Own 24 M Single 26 Own 21 F Divorced 25 Rent

51 F Divorced 41 Other 37 F Divorced 24 Rent 23 M Single 29 Own 17 M Single 25 Own 40 M Single 35 Own 23 M Single 35 Own 17 M Single 26 Own 30 M Single 29 Own 92 M Single 59 Own 52 F Divorced 34 Other 69 M Single 41 Other 40 M Divorced 35 Own 106 M Single 27 Rent 13 M Single 41 Own 4 F Divorced 30 Rent 0 M Married 27 Own 24 F Divorced 65 Own 11 F Divorced 19 Rent 3 F Divorced 33 Own 5 F Divorced 24 Rent 7 M Married 40 Rent 91 M Single 62 Own 25 F Divorced 33 Other 70 M Married 29 Own 5 F Divorced 22 Own 51 M Single 35 Other 53 M Single 30 Own 103 F Divorced 28 Own 6 F Divorced 28 Own 39 M Single 44 Own

Advanced Filter Years Job Credit Risk a. Loan Purpose 3 Unskilled Low New Car 1 Skilled High 4 Management High 2 Unskilled High b. Loan Purpose 3 Skilled Low Furniture 1 Skilled Low 3 Unskilled Low 1 Unskilled Low c. Loan Purpose 2 Skilled High Education 1 Skilled High 2 Management High 2 Unskilled Low d. Loan Purpose 1 Skilled Low Used Car 1 Skilled High 3 Unskilled Low 4 Management Low e. Loan Purpose 2 Skilled High New Car 2 Skilled Low Used Car 4 Skilled Low 2 Skilled High Loan Purpose 4 Skilled High New Car 4 Skilled High 4 Skilled High 2 Management High The formulas 2 Unskilled Low 3 Skilled Low Even if you a 4 Skilled High To make und 1 Skilled Low You should s 4 Skilled Low 2 Skilled Low 4 Skilled High 3 Skilled Low 2 Skilled Low 4 Skilled Low 4 Skilled High

1 Unskilled Low 1 Skilled High 2 Unskilled Low 1 Skilled High 3 Skilled High 4 Unemployed Low 3 Unskilled Low 2 Skilled Low 2 Skilled High 1 Skilled Low 2 Skilled Low 3 Skilled High 4 Skilled High 2 Skilled High 4 Skilled Low 1 Skilled High 4 Unskilled Low 2 Skilled High 4 Skilled Low 3 Management Low 4 Management High 3 Skilled Low 3 Unskilled Low 3 Skilled High 2 Skilled High 2 Unskilled High 4 Unskilled High 3 Unskilled Low 4 Unskilled Low 4 Skilled High 3 Skilled Low 4 Unskilled Low 1 Management High 1 Management Low 4 Skilled High 2 Unskilled High 4 Unskilled Low 4 Skilled Low 3 Skilled High 2 Skilled Low

1 Skilled High 1 Unemployed High 2 Skilled Low 4 Unemployed Low 4 Skilled Low 4 Unskilled Low 4 Skilled High 2 Unskilled High 2 Skilled High 3 Skilled High 4 Skilled High 4 Skilled High 1 Skilled High 4 Skilled High 2 Skilled Low 4 Unskilled Low 1 Skilled Low 1 Unskilled Low 4 Skilled High 3 Skilled Low 2 Skilled Low 4 Skilled High 2 Unskilled High 2 Skilled High 1 Skilled Low 2 Skilled High 1 Skilled Low 4 Skilled Low 1 Skilled Low 3 Skilled High 4 Unskilled High 2 Skilled High 4 Skilled Low 4 Skilled High 4 Skilled High 2 Unskilled Low 1 Unskilled High 4 Unskilled High 2 Management Low 2 Skilled Low

2 Skilled Low 4 Management Low 4 Management Low 4 Skilled High 4 Skilled High 1 Skilled Low 3 Skilled Low 2 Skilled High 1 Unemployed High 4 Skilled High 3 Skilled High 3 Skilled Low 4 Skilled High 4 Skilled Low 4 Unskilled Low 4 Skilled Low 2 Skilled High 2 Unskilled High 4 Skilled High 3 Management High 2 Unskilled Low 4 Skilled High 2 Skilled High 1 Skilled Low 4 Skilled Low 2 Skilled High 3 Skilled Low 4 Management High 3 Unskilled High 2 Skilled Low 1 Unskilled High 4 Unskilled Low 4 Unskilled High 3 Skilled High 3 Skilled Low 3 Skilled Low 4 Management Low 4 Management Low 3 Skilled Low 2 Management Low

4 Skilled High 2 Skilled Low 2 Management High 1 Skilled Low 3 Unskilled High 4 Skilled High 4 Skilled Low 2 Unskilled High 3 Skilled Low 4 Skilled High 1 Skilled Low 2 Management Low 4 Unskilled High 1 Unskilled High 2 Unskilled Low 4 Management High 2 Unemployed Low 4 Unskilled Low 2 Unemployed Low 2 Skilled Low 2 Skilled Low 2 Skilled Low 3 Management Low 4 Skilled Low 1 Skilled High 2 Unskilled High 3 Unskilled High 4 Unskilled Low 4 Unskilled Low 1 Unskilled High 4 Skilled Low 4 Skilled Low 4 Unskilled High 2 Skilled Low 3 Skilled Low 3 Skilled High 4 Skilled High 1 Skilled Low 4 Unemployed High 1 Management High

1 Skilled High 2 Skilled Low 2 Unskilled High 4 Skilled Low 1 Unskilled Low 4 Unskilled Low 2 Management High 4 Skilled High 2 Skilled High 4 Unskilled Low 4 Skilled Low 2 Skilled High 3 Management High 1 Skilled High 1 Unskilled High 2 Skilled Low 2 Skilled High 2 Unskilled High 4 Management High 4 Skilled Low 2 Unemployed Low 4 Skilled Low 4 Unskilled High 3 Skilled Low 2 Skilled High 3 Skilled High 4 Skilled High 4 Skilled High 2 Skilled Low 2 Skilled Low 4 Unemployed High 3 Unskilled Low 4 Skilled High 2 Skilled High 2 Skilled Low 2 Management High 3 Unskilled High 3 Unskilled High 2 Management Low 1 Unskilled High

2 Unskilled High 4 Unskilled Low 4 Management Low 2 Management Low 1 Skilled Low 3 Unskilled Low 4 Management Low 2 Skilled Low 3 Unskilled Low 4 Skilled High 4 Skilled High 4 Unskilled Low 1 Unskilled Low 2 Skilled Low 1 Unemployed High 2 Management Low 2 Skilled Low 1 Skilled Low 1 Skilled High 2 Skilled High 2 Skilled High 3 Management High 4 Skilled Low 4 Management High 2 Unskilled High 2 Skilled High 4 Management Low 4 Management Low 1 Skilled Low 2 Skilled High 2 Management Low 3 Skilled Low 4 Skilled Low 2 Skilled Low 2 Skilled High 1 Skilled Low 3 Unskilled Low 1 Skilled Low 3 Skilled High 4 Management High

2 Skilled Low 2 Management High 2 Skilled High 3 Management High 2 Unskilled Low 4 Unskilled Low 4 Skilled Low 4 Skilled High 2 Management Low 2 Management Low 2 Skilled Low 4 Unskilled High 3 Management High 4 Skilled High 4 Skilled Low 3 Skilled High 4 Skilled High 3 Skilled Low 3 Management High 2 Skilled Low 4 Skilled Low 2 Skilled High 4 Skilled Low 2 Skilled High 4 Skilled High 3 Skilled Low 4 Skilled High 4 Unskilled High 3 Skilled Low 2 Skilled Low 2 Skilled High 4 Skilled High 2 Skilled Low 4 Skilled High 2 Skilled High 4 Unskilled High 4 Skilled High 3 Skilled Low 2 Skilled Low 2 Skilled High

2 Skilled Low 4 Unemployed Low 4 Skilled Low 2 Skilled Low 2 Skilled Low 2 Unskilled High 4 Unskilled High 4 Unskilled High 4 Management High 3 Management Low 4 Skilled High 4 Skilled Low 4 Skilled High 2 Skilled High 4 Skilled High 4 Skilled Low 4 Skilled Low 1 Management Low 2 Skilled High 4 Skilled High 2 Skilled Low 4 Skilled Low 2 Skilled High 1 Skilled Low 4 Skilled Low 2 Skilled High 4 Skilled High 2 Skilled Low 3 Skilled High 3 Skilled Low 4 Unskilled High 4 Unskilled Low 4 Skilled Low 4 Skilled High 2 Skilled High 4 Skilled Low 4 Unskilled Low 4 Skilled Low 2 Skilled High 2 Management High

4 Skilled Low 4 Unskilled Low 4 Skilled Low 4 Skilled High 2 Skilled High 1 Skilled Low 3 Skilled Low 4 Management High 2 Skilled Low 1 Skilled Low 4 Skilled High 4 Management High 4 Skilled High 4 Skilled Low 4 Skilled High 2 Skilled Low 4 Skilled Low 3 Unskilled High 4 Unskilled High 3 Skilled High 4 Skilled High 4 Skilled High 4 Skilled High 2 Management Low 4 Skilled High 2 Skilled High 1 Skilled Low 2 Skilled Low 1 Skilled Low 2 Skilled High 2 Skilled Low 2 Unskilled Low 4 Skilled High 3 Skilled High 3 Unskilled Low 2 Management High 4 Skilled High 2 Skilled Low 3 Skilled High 4 Unskilled High

4 Management High 4 Skilled Low 2 Skilled Low 2 Skilled High 3 Skilled Low 2 Unskilled Low 3 Unskilled Low 4 Management Low 4 Unskilled High 4 Skilled High 4 Skilled Low 3 Skilled High 4 Skilled Low 3 Skilled High 4 Skilled Low 2 Management High 3 Unskilled Low 4 Skilled High 4 Skilled High 3 Skilled High 4 Skilled Low 4 Skilled Low 4 Unskilled High 3 Skilled Low 2 Unskilled High 4 Skilled Low 4 Skilled High 2 Skilled High 2 Skilled High 3 Management Low

r Criteria Checking Savings Months CustoMonths EmploGender Marital Status M Single Average $872.42 (Challenge: Why if you put "F" for Gender is their an "#DIV/0!" Checking Savings Months CustoMonths EmploGender Marital Status Single Average $1,908.00 Checking Savings Months CustoMonths EmploGender Marital Status Average $135.50 Checking Savings Months CustoMonths EmploGender Marital Status <=12 Average $2,962.54 Checking Savings Months CustoMonths EmploGender Marital Status >=36 M >=36 M Average $1,155.85 Checking Savings Months CustoMonths EmploGender Marital Status M Single Average $872.42 s have been in cells Q23 and Q6 for you to compare are not sure what DAVERAGE is doing compare the two formulas and see which derstanding formulas easier try clicking on Q23 then click in the formula bar to th see the formula with colours and the colours match areas on the sheet as follow

Age Housing Years Job Credit Risk High " error in the Average Savings calculation? Try "M") Age Housing Years Job Credit Risk Rent Age Housing Years Job Credit Risk Unskilled Age Housing Years Job Credit Risk Age Housing Years Job Credit Risk Age Housing Years Job Credit Risk High h cell references are similar in the ribbon above ws:

Loan PurposeChecking Savings Months CMonths EGender Marital StAge Furniture Single Average 1908

Housing Years Job Credit Risk Rent

Qu. This question is a great example of how a Bank or Retailers of different produc Why do you think savings are important related to credit risk? In example a. "New Car" "Single" "Males" how can a Bank Manager use this da Can you explain the decision that is being made here?

cts can use Demographics and Savings data for individuals ata and "Credit Risk" to decide if a client John Single, with average savings of $350, can get a loan?

Cereal (Using "IF" functions to identify red-flags OR unusual items) Name Type Calories Protein Fat Sodium Fiber Almond Delight A 110 2 2 200 1 Apple Cinnamon Cheerios C 110 2 2 180 1.5 Apple Jacks D 110 2 0 125 1 Basic 4 C 130 3 2 210 2 Bran Chex H 90 2 1 200 4 Bran Flakes A 90 3 0 210 5 Cap'n'Crunch H 120 1 2 220 0 Cheerios H 110 6 2 290 2 Cracklin' Oat Bran C 110 3 3 140 4 Cream of Wheat (Quick) C 100 3 0 80 1 Crispix B 110 2 0 220 1 Crispy Wheat & Raisins A 100 2 1 140 2 Double Chex C 100 2 0 190 1 Froot Loops B 110 2 1 125 1 Frosted Flakes B 110 1 0 200 1 Frosted Mini-Wheats H 100 3 0 0 3 Fruit & Fibre Dates C 110 5 3 213 2 Fruitful Bran A 120 3 0 240 5 Fruity Pebbles B 110 1 1 135 0 Golden Crisp C 100 2 0 45 0 Golden Grahams D 110 1 1 280 0

Carbo Sugars Vitamins Rating Sales rating 45 14 8 25 34.38484 443 review 0 sale 150 10.5 10 25 29.50954 543 review 0 11 14 25 33.17409 54 review 1 18 8 25 37.03856 353 review 0 15 6 25 49.12025 64 0 13 5 25 53.31381 134 0 12 12 25 18.04285 75 review 1 17 1 25 50.765 53 0 10 7 25 40.44877 346 review 0 21 0 0 64.53382 35 0 21 3 25 46.89564 175 0 11 10 25 36.1762 36 review 1 18 5 25 44.33086 745 review 0 11 13 25 32.20758 24 review 1 14 11 25 31.43597 174 review 0 14 7 25 58.34514 308 0 13 10 0 32.20758 94 review 1 14 12 25 41.01549 74 review 1 13 12 25 28.02577 35 review 1 11 15 25 35.25244 75 review 1 15 9 25 23.80404 25 review 1

review review review review review review review review review an you write the logical function (IF) forumula which will calculate whether there needs to be a review. You know the drill ... have a look at the Solutions worksheet after you have given it a try.

Qu. Why do you think the Rating measure versus the Sales for Cereal is interesting Would you expect a Hi Rating and Lo Sales for Kellogg's Nutrigrain? What decision might a Sales Manager make when using this information? This sort of information would be very useful for a Manager of Kelloggs Austra

g? alia or Sanitarium to red-flag any problems with decreasing sales!

Shirt Production (VLOOKUP function used to access product selling price FORMULAE IN COLUMN B Product Type C NONE Product Size l NONE Number of Units 20 NONE Coulmn Offset 4 Value of Order 1340 Size Product S M L XL Sales price per A 15 17 18 19.5 size and Pdt B 21 26 29 32 C 43 52 67 78 D 3 5 8 9 E 29 34 38 49 F 63 69 73 81 LOOKUP TABLE FOR COLUMN OFFSET VALUE L 4 M 3 S 2 XL 5 Try editing the "Product type", "Product size" and "Number of units" Note: When you edit these three fields in this worksheet of Shirt Production you should get a very good idea of how VLOOKUP works. This will be explained in class by your instructor Important: Try to understand the last item in the C7 & C8 formulas being 2 and C7, respectively See if you VLOOKUP and C8. Check the

u can write the the P formula for cells C7 e solutions if any probs

Sales Transactions: July 14 Cust ID Region Payment Transaction Code Source Amount Product 10001 East Paypal 93816545 Web $20.19 DVD 10002 West Credit 74083490 Web $17.85 DVD 10003 North Credit 64942368 Web $23.98 DVD 10004 West Paypal 70560957 Email $23.51 Book 10005 South Credit 35208817 Web $15.33 Book 10006 West Paypal 20978903 Email $17.30 DVD 10007 East Credit 80103311 Web $177.72 Book 10008 West Credit 14132683 Web $21.76 Book 10009 West Paypal 40128225 Web $15.92 DVD 10010 South Paypal 49073721 Web $23.39 DVD 10011 South Paypal 57398827 Email $24.45 Book 10012 East Credit 34400661 Web $20.39 Book 10013 North Paypal 54242587 Web $19.54 DVD 10014 East Credit 62597750 Web $151.67 Book 10015 West Credit 51555882 Web $21.01 DVD 10016 West Paypal 54332964 Web $22.91 DVD 10017 West Credit 26623353 Email $19.51 Book 10018 West Paypal 78594431 Web $20.16 Book 10019 West Credit 89385348 Web $17.53 DVD 10020 West Credit 69868417 Web $17.74 DVD 10021 North Paypal 59660276 Email $17.16 Book 10022 West Credit 25456590 Web $205.58 Book 10023 South Credit 93283893 Email $18.12 Book 10024 West Credit 45991123 Web $20.04 DVD 10025 West Paypal 79121745 Email $23.21 DVD 10026 West Credit 80685117 Email $22.79 DVD 10027 West Credit 56686474 Web $16.91 DVD 10028 South Credit 25270813 Web $20.22 Book 10029 East Paypal 59736137 Web $18.36 DVD 10030 West Paypal 79615191 Email $206.80 Book 10031 North Paypal 55365094 Email $17.95 DVD 10032 East Credit 79118930 Web $18.29 Book 10033 South Paypal 84470584 Web $18.55 Book 10034 West Credit 71097636 Web $18.82 Book 10035 West Credit 73290219 Web $16.35 Book 10036 East Credit 92093991 Web $16.30 DVD 10037 South Paypal 11165609 Web $217 Book 10038 East Credit 79944825 Web $16.15 DVD 10039 North Credit 59537977 Web $18.78 DVD

10040 South Credit 37870882 Web $150.99 Book 10041 West Credit 59747081 Web $21.39 DVD 10042 West Credit 33511221 Web $16.60 DVD 10043 South Paypal 69676186 Web $23.81 Book 10044 South Credit 72150231 Web $15.87 Book 10045 West Credit 64874923 Web $20.82 DVD 10046 South Credit 79755506 Web $21.15 Book 10047 South Paypal 43322747 Web $19.66 DVD 10048 West Paypal 57979095 Email $21.02 DVD 10049 West Credit 96485037 Web $23.13 DVD 10050 West Paypal 85636284 Web $15.17 DVD 10051 East Paypal 42519148 Web $209.51 Book 10052 West Credit 59845178 Web $16.03 Book 10053 North Credit 47961093 Email $16.17 DVD 10054 East Credit 32857450 Web $18.37 DVD 10055 East Credit 23437096 Web $15.96 Book 10056 North Paypal 23846199 Web $19.29 Book 10057 West Paypal 15630914 Email $16.49 DVD 10058 East Paypal 64471213 Web $18.12 Book 10059 East Credit 70288635 Web $18.22 DVD 10060 East Credit 46067931 Web $18.32 Book 10061 West Credit 73400603 Web $23.77 DVD 10062 East Paypal 31794035 Web $24.35 Book 10063 South Credit 72954240 Email $20.13 DVD 10064 North Credit 12364851 Email $20.77 DVD 10065 North Credit 19974213 Email $16.98 DVD 10066 East Credit 68753569 Email $19.40 Book 10067 East Paypal 77232784 Email $23.49 Book 10068 South Credit 94731015 Web $15.58 Book 10069 South Credit 49007475 Web $21.94 DVD 10070 South Credit 71384600 Email $229.73 Book 10071 West Credit 15282110 Web $16.06 Book 10072 South Credit 87012305 Web $22.21 DVD 10073 West Credit 27742544 Email $21.58 DVD 10074 North Paypal 97981670 Email $16.09 Book 10075 East Credit 83670405 Email $16.10 DVD 10076 East Credit 99063530 Web $15.95 DVD 10077 South Credit 25978103 Email $17.77 Book 10078 North Credit 81824666 Web $19.30 DVD 10079 West Paypal 86833489 Web $21.75 Book 10080 West Paypal 73512800 Web $20.51 Book 10081 North Credit 11673210 Web $16.14 DVD

10082 West Paypal 76787805 Email $157.76 Book 10083 South Credit 34610946 Web $21.55 Book 10084 North Paypal 69586073 Web $21.85 DVD 10085 East Credit 87017416 Web $21.70 DVD 10086 East Paypal 37371293 Web $20.31 Book 10087 East Credit 27497600 Email $23.62 Book 10088 East Credit 29510284 Web $216.37 Book 10089 North Paypal 40878208 Web $21.99 DVD 10090 West Credit 83375454 Email $18.20 DVD 10091 East Credit 61236522 Email $17.31 Book 10092 West Credit 68788857 Web $23.94 Book 10093 West Paypal 58309878 Email $174.25 Book 10094 West Paypal 84324439 Web $20.26 DVD 10095 North Credit 90647889 Web $18.73 DVD 10096 North Paypal 31225474 Web $22.88 Book 10097 West Paypal 79286039 Web $19.15 DVD 10098 South Credit 69628094 Email $15.33 Book 10099 West Paypal 19891764 Web $20.82 DVD 10100 West Credit 21992857 Web $20.61 DVD 10101 East Credit 40572972 Web $16.43 DVD 10102 West Credit 80218197 Web $21.10 DVD 10103 North Paypal 72072353 Email $21.64 Book 10104 West Paypal 44250706 Email $18.06 Book 10105 East Credit 31062653 Web $19.35 Book 10106 North Credit 84047393 Web $23.70 DVD 10107 North Credit 59891368 Web $18.93 Book 10108 North Credit 47234209 Email $16.83 Book 10109 West Credit 47893510 Web $22.19 Book 10110 West Credit 23513829 Web $23.90 Book 10111 East Credit 20993720 Web $17.47 DVD 10112 West Credit 58724265 Email $209.37 Book 10113 North Credit 47687764 Web $18 DVD 10114 East Credit 53008101 Web $22.83 Book 10115 East Credit 68494188 Email $20.31 DVD 10116 South Paypal 40357817 Web $22.06 DVD 10117 North Paypal 91328383 Web $15.22 Book 10118 South Paypal 51497241 Web $20.60 DVD 10119 East Credit 42829269 Web $18.25 DVD 10120 East Credit 56174714 Web $174.18 Book 10121 North Credit 17210514 Email $19.58 DVD 10122 West Paypal 40504819 Web $17.91 Book 10123 West Credit 58186991 Web $22.90 Book

10124 North Paypal 46376047 Web $22.26 Book 10125 South Credit 95760408 Web $19.04 Book 10126 East Credit 73024614 Web $17.42 DVD 10127 West Paypal 63167563 Web $18.54 Book 10128 North Credit 83800724 Web $19.74 DVD 10129 West Credit 11739665 Web $22.03 DVD 10130 West Paypal 74393415 Web $236.49 Book 10131 South Paypal 30372359 Email $19.30 DVD 10132 East Credit 47768495 Email $23.73 DVD 10133 West Paypal 74154714 Web $19.96 Book 10134 East Credit 33525138 Web $20.75 DVD 10135 South Paypal 84542864 Web $22.37 DVD 10136 West Credit 24537107 Email $24.03 DVD 10137 West Credit 74241899 Web $24.59 DVD 10138 West Paypal 33200655 Email $155.91 Book 10139 East Credit 89349547 Email $16.43 Book 10140 South Paypal 83528887 Web $15.71 Book 10141 West Paypal 21113649 Web $15.19 Book 10142 West Paypal 35126822 Web $21.35 DVD 10143 West Credit 98692914 Web $19.47 DVD 10144 West Credit 96105789 Web $21.49 DVD 10145 East Paypal 72991138 Web $22.20 DVD 10146 East Paypal 77775458 Web $21.15 DVD 10147 East Credit 71420485 Web $15.16 DVD 10148 East Credit 55498553 Email $15.71 Book 10149 West Paypal 93904863 Web $24.65 Book 10150 West Paypal 37998977 Email $24.88 Book 10151 East Paypal 24697741 Email $17.49 Book 10152 West Credit 77906388 Web $19.71 DVD 10153 South Credit 79915334 Web $17.33 Book 10154 West Credit 50624253 Web $15.56 DVD 10155 East Credit 32851119 Web $18.94 DVD 10156 North Credit 79812666 Web $22.86 Book 10157 South Paypal 45319579 Web $15.18 Book 10158 South Credit 44466808 Web $22.46 Book 10159 West Credit 26950438 Email $21.39 Book 10160 South Credit 66610830 Email $22.17 DVD 10161 North Paypal 45496161 Web $234.63 Book 10162 North Paypal 57085887 Web $24.97 Book 10163 West Credit 86987062 Web $15.72 Book 10164 East Credit 75029194 Web $24.35 Book 10165 West Paypal 16712886 Email $16.09 Book

10166 West Credit 39307303 Web $23.51 Book 10167 North Paypal 41334963 Web $22.59 Book 10168 East Credit 58630343 Web $15.59 DVD 10169 West Credit 87184105 Web $190.81 Book 10170 West Paypal 35358631 Web $21.12 Book 10171 North Credit 55749730 Web $24.60 DVD 10172 North Paypal 62374456 Web $21.22 DVD 10173 West Credit 84556568 Web $21.78 DVD 10174 West Credit 57605353 Web $16.54 DVD 10175 East Credit 45033697 Email $177.32 Book 10176 South Credit 33917941 Web $21.50 Book 10177 West Paypal 39654675 Web $24.65 DVD 10178 North Paypal 47532285 Web $19.43 DVD 10179 West Credit 85998809 Web $21.12 DVD 10180 West Credit 34960635 Web $18.10 DVD 10181 East Credit 85117076 Email $24.40 Book 10182 South Credit 67865323 Web $19.37 Book 10183 West Credit 55061563 Email $19.17 DVD 10184 South Credit 58022125 Email $241.77 Book 10185 West Paypal 25679000 Web $19.65 Book 10186 West Paypal 35078468 Web $19.88 Book 10187 West Paypal 75772325 Web $15.18 DVD 10188 West Paypal 25433486 Web $15.08 Book 10189 North Paypal 65056232 Web $23.74 Book 10190 North Credit 96077043 Web $19.44 Book 10191 South Credit 68380003 Web $17.70 DVD 10192 West Credit 92733708 Web $16.99 DVD 10193 East Paypal 17547620 Email $16.13 Book 10194 West Paypal 95291830 Web $24.80 DVD 10195 West Paypal 49471722 Web $17.52 Book 10196 North Paypal 70336893 Web $23.63 Book 10197 West Credit 44142213 Web $23.03 DVD 10198 West Paypal 69832322 Web $21.03 DVD 10199 South Credit 54284580 Web $21.88 Book 10200 South Credit 69967343 Email $24.86 Book 10201 North Credit 70932816 Email $21.43 DVD 10202 South Paypal 74082072 Web $16.32 DVD 10203 East Credit 92299116 Web $17.20 DVD 10204 North Paypal 33160396 Web $17.87 DVD 10205 South Credit 22141389 Web $17.27 DVD 10206 South Credit 43297905 Web $19.76 Book 10207 East Paypal 72307242 Web $17.10 Book

10208 West Credit 66131853 Email $15.66 Book 10209 West Credit 71755916 Web $22.37 Book 10210 East Credit 55102089 Web $15.81 DVD 10211 South Credit 25266837 Web $18.75 DVD 10212 West Credit 17246696 Email $192.41 Book 10213 South Credit 55149876 Email $242.52 Book 10214 North Credit 66024609 Web $20.40 Book 10215 East Credit 74962881 Web $24.71 DVD 10216 East Credit 66903731 Web $21.49 DVD 10217 West Paypal 71026884 Web $22.26 DVD 10218 West Credit 86140667 Web $22.39 Book 10219 East Credit 97905965 Email $21.01 Book 10220 West Credit 40197352 Web $226.15 Book 10221 West Paypal 43741856 Web $20.67 Book 10222 West Credit 78186031 Web $21.72 DVD 10223 East Credit 58045939 Email $16.34 Book 10224 West Credit 16151482 Web $19.19 Book 10225 West Credit 16578164 Email $19.21 DVD 10226 West Credit 96323938 Web $16.06 Book 10227 South Paypal 77228031 Web $20.87 DVD 10228 West Credit 10779898 Web $15.33 DVD 10229 West Credit 85174502 Web $23.58 DVD 10230 North Credit 73359370 Email $15.34 Book 10231 North Paypal 10400774 Web $216.20 Book 10232 East Credit 69035250 Email $21.85 DVD 10233 West Credit 45792515 Web $23.59 Book 10234 North Paypal 28433265 Web $20.44 Book 10235 East Credit 55311936 Web $22.05 DVD 10236 West Paypal 64115201 Email $20.42 Book 10237 West Credit 66071683 Email $20.49 DVD 10238 West Credit 97687340 Web $161.46 Book 10239 South Paypal 41078038 Web $19.69 DVD 10240 South Paypal 55142477 Web $24.16 Book 10241 East Credit 41419462 Web $22.80 DVD 10242 East Paypal 39303323 Web $243.70 Book 10243 West Paypal 95949085 Web $210.38 Book 10244 West Paypal 28911817 Email $161.50 Book 10245 South Credit 36561487 Email $21.92 DVD 10246 West Credit 71502183 Web $23.75 DVD 10247 West Credit 54077093 Email $23.74 DVD 10248 West Paypal 92266350 Web $21.67 Book 10249 West Paypal 60490288 Email $22.04 Book

10250 North Paypal 72701137 Email $17.83 DVD 10251 South Paypal 32435141 Web $23.60 Book 10252 North Credit 48330352 Web $19.90 Book 10253 East Credit 17689891 Email $209.20 Book 10254 East Credit 67182932 Email $24 Book 10255 West Credit 97950489 Web $17.81 Book 10256 West Paypal 50561229 Email $24.77 DVD 10257 East Credit 70759248 Web $21 DVD 10258 North Paypal 77616151 Web $191.43 Book 10259 South Paypal 79551499 Web $24.52 DVD 10260 East Credit 90656731 Web $17.34 DVD 10261 East Paypal 33909737 Web $17.19 Book 10262 West Credit 63841931 Web $22.55 Book 10263 South Credit 30506370 Web $15.35 DVD 10264 South Credit 81572757 Web $23.20 Book 10265 West Credit 70596149 Web $241.65 Book 10266 West Paypal 95125046 Email $242.40 Book 10267 West Paypal 10754185 Web $23.01 DVD 10268 East Credit 88506060 Email $17.22 DVD 10269 North Paypal 80319080 Web $15.96 DVD 10270 East Credit 27016365 Web $15.45 DVD 10271 North Credit 80034508 Email $17.41 DVD 10272 East Credit 76677689 Web $21.64 Book 10273 South Credit 96855830 Web $157.86 Book 10274 West Paypal 54775836 Web $18.17 DVD 10275 West Credit 18066842 Web $18.73 Book 10276 West Paypal 28240563 Web $17.24 Book 10277 South Credit 24796034 Web $23.91 DVD 10278 South Credit 60979466 Web $22.12 Book 10279 West Credit 44558261 Web $15.25 DVD 10280 South Credit 49683597 Web $24.77 Book 10281 West Paypal 70748780 Email $20.28 Book 10282 South Paypal 80637514 Email $20.33 DVD 10283 South Credit 77963353 Web $16.90 Book 10284 South Paypal 55003920 Web $16.47 Book 10285 West Paypal 66231568 Web $21.18 Book 10286 West Credit 49290839 Web $15.86 Book 10287 West Credit 65745301 Web $24.42 Book 10288 South Paypal 18744208 Web $222.38 Book 10289 South Paypal 52683186 Web $188.85 Book 10290 East Credit 87677897 Web $15.32 Book 10291 West Credit 78943440 Email $24.71 Book