pic 1

pic 2

pic 3

need excel solved with analysis. asap

This assignment is based on real-world data from a banking transaction account preference conjoint study conducted by a major regional bank. Liberty Bank conducted a conjoint study to find out how valuable different attributes of everyday transaction accounts were to its customers, and to identify the account that best serves the needs of customers. The bank was particularly interested in three attributes minimum monthly balance required to waive monthly service fee (MINBAL), monthly service fee (FEE), and the ability to withdraw cash from other network ATMs (ON ATMs), i.e., from ATMs of other banks. Each of these attributes can take one of the three levels listed below. MINBAL - Minimum monthly balance required to waive a monthly service fee. - $0 versus $500 versus $1000 FEE - Monthly service fee. - \$0 versus $3 versus $6 ON ATM - Availability and cost for using ATM networks of other banks. - not available versus available and free versus available but costs $0.75 per transaction. The study used a fractional factorial design in which respondents were asked to rank their preferences from most to least preferred for nine Liberty transaction accounts listed below. Besides the preference ranking of bank accounts, the study also collected demographic and lifestyle characteristics of respondents, which allowed the bank to group customers into three major segments. You have been involved as a consultant by Liberty Bank's Marketing Department to help analyse the conjoint data and identify the best transaction product to offer. The dataset contains the bank account preferences and segment membership information (i.e. segments to which each respondent belongs to) for 18 respondents. (Note: Preferences are obtained by reverse coding the ranks assigned by respondents to different accounts and so, you can use the preferences directly as the Y variable in the regression analysis) The data set also shows the segment sizes and estimated average monthly bank account balances for the three segments. The profit that the bank gets from an account depends on the average monthly balance in the account and margin that the bank makes on that account balance (i.e. profit from an account = average monthly balance percentage margin). Projected percentage margin that the bank will make on account balances for each of the nine accounts (i.e. the margins (\%) that Liberty Bank makes on account balances) is also given in the dataset. Your tasks are as follows: 1. Calculate the part-worths of account attribute levels. 2. Calculate the relative importance of different attributes and provide insights on the attribute preferences of the three segments. (Hint: Compute the average importance assigned by each segment to each attribute and discuss about the differences between the segments in terms of the importance assigned to different attributes) 3. Liberty Bank has two major competitors in the studied market: Enterprise Bank and Origin Bank, and they offer the following transaction products: Enterprise Bank: Minimum balance of $500; monthly fee of $6; free ON ATM access. Origin Bank: Minimum balance of $1000; monthly fee of $3;$0.75 per transaction for ON ATM access. In this market, for each of the nine bank account profiles, calculate the purchase probabilities if Liberty Bank introduces the profile, using the multinomial logit model (Note: Liberty Bank will introduce only one of the nine profiles). 4. Estimate the segment-wise market shares and the overall market shares of each of the nine profiles. Identify the profile that will yield the highest market share. (Hint: Calculate segment wise market shares (average purchase probabilities) of each of the 9 profiles. Then calculate the overall market share by using segment sizes as weights) 5. Projected margin (\%) for each of the nine profiles is given in the data worksheet. Estimated profit from a transaction account for a customer segment can be calculated as follows: No. of. Customers in the segment Market share of the product in the segment x Average bank balance of a customer in the segment margin (\%) from the product. The total customer base in the studied market is 100,000 customers. Calculate the total profits of each of the nine profiles and identify the most profitable product. (Hint: No. of customers in each segment = segment size total customer base. Total profits from a product = sum of segment wise profits for the three segments) This assignment is based on real-world data from a banking transaction account preference conjoint study conducted by a major regional bank. Liberty Bank conducted a conjoint study to find out how valuable different attributes of everyday transaction accounts were to its customers, and to identify the account that best serves the needs of customers. The bank was particularly interested in three attributes minimum monthly balance required to waive monthly service fee (MINBAL), monthly service fee (FEE), and the ability to withdraw cash from other network ATMs (ON ATMs), i.e., from ATMs of other banks. Each of these attributes can take one of the three levels listed below. MINBAL - Minimum monthly balance required to waive a monthly service fee. - $0 versus $500 versus $1000 FEE - Monthly service fee. - \$0 versus $3 versus $6 ON ATM - Availability and cost for using ATM networks of other banks. - not available versus available and free versus available but costs $0.75 per transaction. The study used a fractional factorial design in which respondents were asked to rank their preferences from most to least preferred for nine Liberty transaction accounts listed below. Besides the preference ranking of bank accounts, the study also collected demographic and lifestyle characteristics of respondents, which allowed the bank to group customers into three major segments. You have been involved as a consultant by Liberty Bank's Marketing Department to help analyse the conjoint data and identify the best transaction product to offer. The dataset contains the bank account preferences and segment membership information (i.e. segments to which each respondent belongs to) for 18 respondents. (Note: Preferences are obtained by reverse coding the ranks assigned by respondents to different accounts and so, you can use the preferences directly as the Y variable in the regression analysis) The data set also shows the segment sizes and estimated average monthly bank account balances for the three segments. The profit that the bank gets from an account depends on the average monthly balance in the account and margin that the bank makes on that account balance (i.e. profit from an account = average monthly balance percentage margin). Projected percentage margin that the bank will make on account balances for each of the nine accounts (i.e. the margins (\%) that Liberty Bank makes on account balances) is also given in the dataset. Your tasks are as follows: 1. Calculate the part-worths of account attribute levels. 2. Calculate the relative importance of different attributes and provide insights on the attribute preferences of the three segments. (Hint: Compute the average importance assigned by each segment to each attribute and discuss about the differences between the segments in terms of the importance assigned to different attributes) 3. Liberty Bank has two major competitors in the studied market: Enterprise Bank and Origin Bank, and they offer the following transaction products: Enterprise Bank: Minimum balance of $500; monthly fee of $6; free ON ATM access. Origin Bank: Minimum balance of $1000; monthly fee of $3;$0.75 per transaction for ON ATM access. In this market, for each of the nine bank account profiles, calculate the purchase probabilities if Liberty Bank introduces the profile, using the multinomial logit model (Note: Liberty Bank will introduce only one of the nine profiles). 4. Estimate the segment-wise market shares and the overall market shares of each of the nine profiles. Identify the profile that will yield the highest market share. (Hint: Calculate segment wise market shares (average purchase probabilities) of each of the 9 profiles. Then calculate the overall market share by using segment sizes as weights) 5. Projected margin (\%) for each of the nine profiles is given in the data worksheet. Estimated profit from a transaction account for a customer segment can be calculated as follows: No. of. Customers in the segment Market share of the product in the segment x Average bank balance of a customer in the segment margin (\%) from the product. The total customer base in the studied market is 100,000 customers. Calculate the total profits of each of the nine profiles and identify the most profitable product. (Hint: No. of customers in each segment = segment size total customer base. Total profits from a product = sum of segment wise profits for the three segments)