Steps for

Apply appropriate visual formatting to the data table. This includes creating a title for the document, adding your name and the creation date, adding a title to the table, visually formatting the table's headers and contents, applying currency formatting to relevant cells $($using the Japanese yen symbol rather than the dollar sign$),$ and anything else you feel is appropriate. Additionally, any new calculations should be placed in an appropriate location and properly labeled.

In a new column in the data file, calculate the acquisition cost for each segment.

Calculate the expected annual margin for each segment.

Calculate the CLV for each segment.

alc$5)$ Calculate the breakeven time periods for each segment.

$6)$ what is the most attractive customer segment for MBC to target? Explain your reasoning.

$7)$MBC has been approached by Little League representatives from the nearby Chiyoda ward who are eager to gain the jersey subsidy the Minato ward has enjoyed due to the company's sponsorship. Because the parents of Chiyoda Little Leaguers will have to travel a greater distance, Maru believes there will be a lower response rate $(8\%)$ and a lower retention rate $(65\%),$ which she can make up for by purchasing slightly lowerquality jerseys, reducing the cost of sponsorship to just $600$ yen per player. However, the Chiyoda ward representatives demand that theirs be the only ward receiving such a sponsorship, which means MBC must choose between the two wards. The Chiyoda representatives argue that because their ward has twice the number of potential customers, it is more attractive than the Minato ward. $($Assume the Minato ward league has $150$ players and the Chiyoda ward league, therefore, has $300$ players. Note that this isn't the same as saying MBC will have $150$ and $300$ customers from those leagues.$)$ Should MBC pursue the Chiyoda ward sponsorship instead? Explain your reasoning.

$8)$Maru$\text{'}$s brother suggested she focus on the Elite Ballplayers segment, targeting it by offering a $500$ discount on all future purchases to Elite Ballplayers who purchase at least twenty cage hours in Year $1.($Assume all Elite Ballplayer customers book exactly twenty hours each year.$)$ Although this will decrease the amount MBC can bill Elite Ballplayers $($from $7,500$ per hour to $7,000$ per hour from Year $2$ onward$),$ Maru believes it will increase the retention rate of these customers to $75$ percent immediately. Should MBC offer this promotion? Explain your reasoning.

$9)$ Alternatively, Maru has considered ignoring her brother's advice and targeting the Elite Ballplayers segment by attempting to increase the launch party's response rate. Instead of offering customers a discount from Year $2$ onward, MBC could offer each launch party attendee who commits to purchasing at least twenty batting cage hours a free professional$-$grade baseball bat with the Yakult Swallows logo$.$ This would cost MBC an additional $10,000$ for each new Elite Ballplayer customer, but Maru believes it would increase the launch party's invitation response rate to $29$ percent. Should she offer this promotion? Explain your reasoning.

$10)$Sensitivity Analysis

a$.$ Find the point where the following segments become unprofitable $($i$.$e$.,$ a negative customer lifetime value$)$ by changing different assumptions made about the segment. Specifically, you'll find the unprofitability point when by changing price, response rate, and retention rate.

i$.$ Elite Ballplayers $($Party$)$

Price

Response rate

Retention rate

ii$.$ Elite Ballplayers $($Print Ad$)$

Price

Response rate

Retention rate

b$.$ In a few sentences, interpret what MBC can learn about the segment and the promotional strategies from the sensitivity analysis. For instance, you may reflect on whether the values you found are substantially larger$/$smaller and whether it's likely that these scenarios will come to pass $($e$.$g$.,$ is it likely that Elite Ballplayers will end paying the lower price, or having the lower response or retention rates$).$ You should also reflect on how confident MBC should be about the data they used for each variable initially. Is it based on past experience or some other reliable source or is it based intuition and fuessing? Last, you should indicate if this analysis changes any recommendation you made earlier. E$.$g$.,$ is the promotion with the higher expected CLV also more risky, with a higher chance of being unprofitable?