Get Data Data from Picture Refresh All Edit Links C14 xfx A B 1 20 pts. 2 C D Stocks Currencies Sort Filter Advanced Text to Columns Remove Duplicates Consolidate What-If Analysis Subtotal Solver E F G H J K L M N 4 Solver settings (if Solver is used) must be saved. A supermarket XYZ is determined to please its customers with a customer advantage card. Currently a certain percentage of all shoppers (given below) is loyal to XYZ. A loyal customer shops at XYZ some % of the time (also given below). Others shop at XYZ for a smaller % of time. A typical customer spends a given amount each week. This amount as well as the profit margin is the same for loyal customers and others at XYZ. The advantage card is expected to increase the percentage of loyal customers and the percentage of time loyal customers will shop at XYZ is given below. These percentages will not change for others. 7 8 9 What is the maximum cost of the advantage card per dollar and the profit per week for the program to be profitable (break even)? 10 11 Average Spending/wk: $ 175.00 12 Profit margin per 1$: 0.04 13 Cohort size: 100 Any (positive) cohort size Cost of Advantage Card per 1$: Profit per week: Do not round this value. 14 15 CURRENT (NO CARD) Pct. 16 Loyal to XYZ: 17 Others: 18 Cohort-based calculations: 19 30.0% 70.0% 80.0% 10.0% % of Time go % of time go to XYZ elsewhere 20.0% 90.0% Remember that you may add as many calcuated cells as needed. This model will be tested on different sets of input data. % of Time go 20 WITH ADVANTAGE CARD Pct. to XYZ 21 Expected increase: 10.0% 10.0% 22 23 24 25 26 27 28 29 30 31 32 ReadMeFirst P1 P2 P3 + a R