need excel solutions for each and every step including analysis.

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. 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. 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)