You are the marketing analyst for a local restaurant. The restaurant, periodically conducts advertising campaigns which include offers for gift cards, free appetizers, and priority seating. The restaurant has established a membership system and has assigned a passport number to its members and is able to track dining history and the amount spent at each visit. The attached file Homework2_dataSource.xlsx contains transactions over a two year period. Each record includes the transaction number, the date of visit, the amount of bill, and the members passport number.

1. Use Pivot table to identify the most recent transaction, frequency, and monetary value for each passport member.

2. Use lookup functions to assign scores for the recency, frequency, and monetary values. The following cutoff points can be used:

Recency Cutoffs		Frequency Cutoffs		Monetary Value Cutoffs
1/1/2010	1	0	1	$0	1
1/1/2013	2	5	2	$800	2
9/1/2013	3	10	3	$1500	3

3. Generate descriptive statistics using charts, tables, averages, medians, modes, or other summary statistics.

4. Generate predictive statistics using correlations, regressions, or other forecasting techniques.

5. Provide insights and marketing recommendations for the company.

6. 

Home Page Layout Formulas Data Insert X Cut Copy Calibri (Body) - 11 A- A Paste Format D1 fx B D Customer ID# 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 648 867 807 985 854 1055 1044 980 827 1026 960 911 759 893 776 630 972 927 845 770 793 911 866 701 952 883 792 767 1016 992 1076 877 667 616 600 903 Date of purchase 10/1/14 9/29/14 9/29/14 9/28/14 9/28/14 9/27/14 9/26/14 9/26/14 9/26/14 9/25/14 9/25/14 9/25/14 9/25/14 9/24/14 9/24/14 9/23/14 9/22/14 9/22/14 9/22/14 9/22/14 9/20/14 9/19/14 9/19/14 9/18/14 9/16/14 9/16/14 9/15/14 9/15/14 9/14/14 9/13/14 9/11/14 9/11/14 9/11/14 9/8/14 9/8/14 9/7/14 9/7/14 9/6/14 9/5/14 9/5/14 9/5/14 9/4/14 9/4/14 Money spent $787.28 $1,342.26 $1,723.02 $1,311.56 $2,527.02 $1,345.61 $943.20 $617.80 $914.87 $947.90 $1,958.46 $268.14 $1,476.42 $1,142.11 $1,406.58 $2,241.58 $947.22 $2,542.02 $530.95 $136.51 $672.92 $911.32 $415.43 $2,544.65 $2,250.38 $2,284.70 $1,539.48 $848.20 $649.70 $1,866.96 $1,561.43 $400.06 $1,696.65 $2,159.25 $1,104.42 $1,849.77 $298.02 $1,697.27 $1,759.42 $2,021.01 $2,228.22 $1,678.01 $1,021.06 41 42 43 44 591 628 947 734 636 873 792 Home Page Layout Formulas Data Insert X Cut Copy Calibri (Body) - 11 A- A Paste Format D1 fx B D Customer ID# 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 648 867 807 985 854 1055 1044 980 827 1026 960 911 759 893 776 630 972 927 845 770 793 911 866 701 952 883 792 767 1016 992 1076 877 667 616 600 903 Date of purchase 10/1/14 9/29/14 9/29/14 9/28/14 9/28/14 9/27/14 9/26/14 9/26/14 9/26/14 9/25/14 9/25/14 9/25/14 9/25/14 9/24/14 9/24/14 9/23/14 9/22/14 9/22/14 9/22/14 9/22/14 9/20/14 9/19/14 9/19/14 9/18/14 9/16/14 9/16/14 9/15/14 9/15/14 9/14/14 9/13/14 9/11/14 9/11/14 9/11/14 9/8/14 9/8/14 9/7/14 9/7/14 9/6/14 9/5/14 9/5/14 9/5/14 9/4/14 9/4/14 Money spent $787.28 $1,342.26 $1,723.02 $1,311.56 $2,527.02 $1,345.61 $943.20 $617.80 $914.87 $947.90 $1,958.46 $268.14 $1,476.42 $1,142.11 $1,406.58 $2,241.58 $947.22 $2,542.02 $530.95 $136.51 $672.92 $911.32 $415.43 $2,544.65 $2,250.38 $2,284.70 $1,539.48 $848.20 $649.70 $1,866.96 $1,561.43 $400.06 $1,696.65 $2,159.25 $1,104.42 $1,849.77 $298.02 $1,697.27 $1,759.42 $2,021.01 $2,228.22 $1,678.01 $1,021.06 41 42 43 44 591 628 947 734 636 873 792