Emily Cho, the consultant for The AM Bakery shows you the following table with district profitability and asks for a report on which district is most profitable, along with a brief explanation.

THE AM Bakery

Annual Revenue and Profit by District

District Number of Stores Annual Revenue Annual Profit

District $15$ $ $3,926,503$ $ $1,050,686$

District $243,749,2561,165,390$

District $365,568,5741,769,757$

District $453,690,9131,217,799$

District $566,159,9272,212,114$

District $644,927,4922,056,521$

Total $3028,022,6659,472,267$

Required:

Perform an analysis on the district revenue and profit data that would allow you to write a short report with your conclusions. The relevant data has been included within the following file:

Click here to access the Assignment $1-55$ Student File

Assignment Steps:

Download the Assignment $1-55$ Student File and open it within Microsoft Excel.

Select cell E$4$ and type ROS, then select cell F$4$ and type Profit$/$Store$.$

Notice that the entries are shown in bold, as this formatting is already applied to the adjacent cells in the range A$4$:D$4.$

Within cell E$5$ enter the formula $=$D$5/$C$5$ to calculate Return on Sales as Annual Profit divided by Annual Revenue.

With cell E$5$ active select the Percent Style option within the Number group on the Home menu, then select the Increase Decimal option within the Number group on the Home menu to display one decimal place.

Within cell F$5$ enter the formula $=$D$5/$B$5$ to calculate Profit per Store as Annual Profit divided by Number of Stores.

Highlight the range E$5$:F$5,$ click a drag the fill handle $($small square at the bottom$-$right of the highlighted range$)$ down to the range E$11$:F$11,$ and release the mouse.

Within the range A$14$:D$14$ enter the following headers in order: Ranking, Profit by Total Profits, Profit by Average Store Profits, and Profitability by Return$-$on Sales.

Highlight the range A$14$:D$14,$ choose the Bottom Border option from the Font group on the Home menu, choose the Center option from the Alignment group on the Home menu, and choose the Wrap Text option from the Alignment group on the Home menu.

Within the range A$15$:A$20$ enter the following rankings in order: $1,2,3,4,5,6.$

Highlight the range A$15$:A$20$ and choose Center from the Alignment group on the Home menu.

Highlight the range A$4$:F$10$ and choose the Sort option within the Sort & Filter group on the Data menu.

Within the Sort dialog box select Annual Profit within the Column box of the Sort by row, select Largest to Smallest within the Order box, and click OK$.$

Highlight the range A$5$:A$10,$ choose Copy from the Clipboard group on the Home menu, highlight the range B$15$:B$20,$ and choose Paste from the Clipboard group on the Home menu.

Highlight the range A$4$:F$10$ and choose the Sort option within the Sort & Filter group on the Data menu.

Within the Sort dialog box select Profit$/$Store within the Column box of the Sort by row, and click OK$.$

Highlight the range A$5$:A$10,$ choose Copy from the Clipboard group on the Home menu, highlight the range C$15$:C$20,$ and choose Paste from the Clipboard group on the Home menu.

Highlight the range A$4$:F$10$ and choose the Sort option within the Sort & Filter group on the Data menu.

Excel Analytics turnover

Within the Sort dialog box select ROS within the Column box of the Sort by row, and click OK$.$

Highlight the range A$5$:A$10,$ choose Copy from the Clipboard group on the Home menu, highlight the range D$15$:D$20,$ and choose Paste from the Clipboard group on the Home menu.

Save your progress by choosing Save from the File menu. You may now answer all questions for Assignment $1-55.$