This problem is designed to acquaint you with the process of reading and analyzing data to make business decisions. Please note that the raw data alone doesn't always indicate the correct choice. Rather, the data is viewed in context of other business priorities to fully inform the decision-making process. For this problem, you will use the following Microsoft Excel Online file to review data and then answer the questions. Feel free to click on the Excel Online link now to open the spreadsheet it will open in a separate browser tab. If you need help navigating Excel or knowing how to complete the tasks related to this problem, please click here to access Microsoft Support. Open spreadsheet Scenario You are the marketing analyst for Better Beans Coffee Company, which has nine stores nationwide. The company wants to build two additional stores. Your executive team has decided that rather than expand to new markets, they want Better Beans to begin opening additional stores in existing markets. While this will create cannibalization in the short term, it will create marketing and operating efficiencies as more stores are opened in each city. As a scrappy and growing startup, Better Beans does not yet have access to complex marketing analytics software. Fortunately, you are an expert at gathering market data from inside and outside the company and crunching accurate numbers with nothing more than an Excel spreadsheet. You have been tasked with calculating the two best markets for opening an additional store. You have already calculated two things that allow you to estimate the net additional revenue in each market after adding a second store: Revenue for a second store in each market The revenue lost from estimated cannibalization at the first store. Important note: Due to the high investments already made in existing stores, management has specified that any market where cannibalization is 30% or more should be eliminated from consideration.

Questions:

1. Ignoring cannibalization rates for now, what two markets have the highest net revenue increases when adding a second store?

Atlanta and Dallas

Los Angeles and Boston

Dallas and Los Angeles

Houston and Los Angeles

Atlanta and Boston

2. What two markets should be chosen for a second store based on management's criteria that the cannibalization rate for the existing store should be less than 30%?

Note: Cannibalization rates and net revenue increase amounts need to be considered when making this determination.

Orlando and Portland

Atlanta and Dallas

Atlanta and Portland

Atlanta and Boston

Orlando and Boston

Existing Store	Revenue	Second Store Revenue Estimate	Cannibalization Estimate	Revenue Drop Due to Cannibalization	Net Revenue Increase for Market (Second Store Revenue - Cannibalization)
Los Angeles	$1,950,000	$1,650,000	50%	$975,000	$675,000
Houston	$1,800,000	$1,760,000	45%	$810,000	$950,000
Orlando	$1,900,000	$1,570,000	30%	$570,000	$1,000,000
Atlanta	$3,600,000	$2,605,000	5%	$180,000	$2,425,000
Chicago	$2,500,000	$2,050,000	40%	$1,000,000	$1,050,000
San Diego	$1,500,000	$1,475,000	25%	$375,000	$1,100,000
Portland	$1,550,000	$1,377,500	5%	$77,500	$1,300,000
Dallas	$2,200,000	$2,035,000	30%	$660,000	$1,375,000
Boston	$1,100,000	$1,100,000	25%	$275,000	$825,000