PLEASE ANSWER IN EXCEL WITH FORUMLAS SHOWING!!!

Read the Maru Batting Center case in your course pack, and answer Questions 17 in the Excel spreadsheet provided in the case packet. During any week, by random draw, you may be asked to present the material during the synchronous session, so you must be prepared.

Questions:

What is the customer acquisition cost to Maru Batting Center for the following customers?

A Little Leaguer

A Summer Slugger

An Elite Ballplayer if MBC places the ad in the local baseball enthusiasts magazine

An Elite Ballplayer if MBC purchases the list and invites all target customers to the gala event

An Entertainment Seeker

Without discounting cash flows to take into account the time value of money, how soon will MBC break even on the following customers? In all cases, assume that revenues and variable costs to staff the cages occur on an ongoing basis but the acquisition costs are one-time events.

A Little Leaguer

A Summer Slugger

An Elite Ballplayer if MBC places the ad in the local baseball enthusiasts magazine

An Elite Ballplayer if MBC purchases the list and invites all target customers to the gala event

An Entertainment Seeker

Taking into account the time value of money and assuming that 100 percent of a customer segment will have experienced attrition once the net present value of annual profits per customer falls below 100, what is the lifetime value to MBC of the following customers?

A Little Leaguer

A Summer Slugger

An Elite Ballplayer if MBC places the ad in the local baseball enthusiasts magazine

An Elite Ballplayer if MBC purchases the list and invites all target customers to the gala event

An Entertainment Seeker

Which is the most attractive customer segment for MBC to target? Explain your reasoning.

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 companys 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 percent) and a lower retention rate (65%), which can be made up for by purchasing slightly lower-quality jerseys, reducing the cost of sponsorship to just 600 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 Little League customers, it is more attractive than the Minato Ward. Should MBC pursue the Chiyoda Ward sponsorship? Explain your reasoning.

Maru'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 20 batting cage hours in Year 1. (Assume all Elite Ballplayer customers book exactly 20 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 immediately increase the retention rate of these customers to 75 percent. Should MBC offer this promotion? Explain your reasoning.

Submission note: All answers from these questions need to be presented in a PowerPoint format with answers provided in one to two slides per question. You can embed XL spreadsheets in your PowerPoint presentation if needed.

Bonus Challenge

Alternatively, Maru has considered ignoring her brothers advice and targeting the Elite Ballplayers segment by attempting to increase the launch partys 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 20 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 invitation response rate to 29%. Should she offer this promotion? Explain your reasoning.

\begin{tabular}{lrrrrr} & & & Elite & Elite & \\ & Little & Summer & Ballplayers & Ballplayers & Entertainment \\ & Leaguers & Sluggers & (Print Ad) & (Party) & Seekers \\ & 1,000 & 1,500 & 300 & 12,500 & 50 \\ Contact Cost & 10.0% & 15.0% & 0.5% & 25.0% & 2.5% \\ Response Rate & ??? & ??? & ??? & ??? & ??? \\ Acquisition Cost & & & & & \\ Workers Needed & 2 & 1 & 1 & 1 & 2 \\ Worker Labor Cost & 1,500 & 1,500 & 1,500 & 1,500 & 1,500 \\ Instructors Needed & 1 & 0 & 1 & 1 & 0 \\ Instructor Hourly Labor Cost & 3,000 & N/A & 4,500 & 4,500 & N/A \\ \hline Total Cost Per Hour & ??? & ??? & ??? & ??? & ??? \\ & & & & & \\ Hourly Price Charged & 6,500 & 3,000 & 7,500 & 7,500 & 74,000 \\ Hourly Margin & ??? & ??? & ??? & ??? & ??? \\ Hourly Margin \% & ??? & ??? & ??? & ??? & ??? \\ Annual Hours & 10.0 & 4.0 & 20.0 & 20.0 & 1.5 \\ Annual Margin & ??? & ??? & ??? & ??? & ??? \\ & & & & & \\ Retention Rate & 75.0% & 50.0% & 60.0% & 60.0% & 35.0% \end{tabular} \begin{tabular}{lrrrrr} & & & Elite & Elite & \\ & Little & Summer & Ballplayers & Ballplayers & Entertainment \\ & Leaguers & Sluggers & (Print Ad) & (Party) & Seekers \\ & 1,000 & 1,500 & 300 & 12,500 & 50 \\ Contact Cost & 10.0% & 15.0% & 0.5% & 25.0% & 2.5% \\ Response Rate & ??? & ??? & ??? & ??? & ??? \\ Acquisition Cost & & & & & \\ Workers Needed & 2 & 1 & 1 & 1 & 2 \\ Worker Labor Cost & 1,500 & 1,500 & 1,500 & 1,500 & 1,500 \\ Instructors Needed & 1 & 0 & 1 & 1 & 0 \\ Instructor Hourly Labor Cost & 3,000 & N/A & 4,500 & 4,500 & N/A \\ \hline Total Cost Per Hour & ??? & ??? & ??? & ??? & ??? \\ & & & & & \\ Hourly Price Charged & 6,500 & 3,000 & 7,500 & 7,500 & 74,000 \\ Hourly Margin & ??? & ??? & ??? & ??? & ??? \\ Hourly Margin \% & ??? & ??? & ??? & ??? & ??? \\ Annual Hours & 10.0 & 4.0 & 20.0 & 20.0 & 1.5 \\ Annual Margin & ??? & ??? & ??? & ??? & ??? \\ & & & & & \\ Retention Rate & 75.0% & 50.0% & 60.0% & 60.0% & 35.0% \end{tabular}