January Restaurant Revenue
Your cousin Anthony owns a restaurant in Columbus, Ohio. The restaurant manager tracks daily revenue for the lunch and dinner hours. Anthony wants to analyze revenue by weekday for both the lunch and dinner hours for January. You will add subtotals of revenue by days of the week and by meal time for Anthony. At the end of the year, he wants to analyze data by days of the week and quarters for the whole year. You will create a Pivot Table and a PivotChart to organize the data. Refer to Figure 5.46 as you complete this exercise.
a. Open e05p1Revenue and save it as e05p1 Revenue_LastFirst.
b. Ensure that the January worksheet is active. Complete the following steps to sort the data:
• Click the Data tab and click Sort in the Sort & Filter group to open the Sort dialog box.
• Click the Sort by arrow and select Weekday.
• Click the Order arrow and select Custom List to open the Custom Lists dialog box. Select Sunday, Monday, Tuesday in the Custom lists section and click OK.
• Click Add Level in the Sort dialog box, click the Then by arrow, and then select Meal.
• Click the Order arrow for Meal, select Z to A to list Lunch before Dinner, and click OK.
c. Click Subtotal in the Outline group. Complete the following steps in the Subtotal dialog box:
• Click the At each change in arrow and select Weekday.
• Click the Use function arrow and select Average.
• Keep the Revenue check box selected and click OK.
d. Add a second-level subtotal by meal by completing the following steps:
• Click Subtotal in the Outline group.
• Click the At each change in arrow and select Meal.
• Keep the Average function selected and keep the Revenue check box selected.
• Click the Replace current subtotals check box to deselect it. Click OK.
e. Click the 2 outline symbol to collapse the list to see the weekday and grand averages. Which weekday produced the highest revenue? (Saturday) Which weekday produced the lowest revenue? (Monday) Click the 3 outline symbol to expand the list to see weekday subtotals for lunch and dinner. Increase the width of column C so that the labels fully display.
f. Click the Yearly Data sheet tab. The Revenue table lists lunch and dinner revenue for every day in 2018. The Weekday column is coded where 1 = Sunday and 7 = Saturday. Click the Weekdays sheet tab. The Days table contains two columns: the Weekday codes with their respective weekday in the Day column. Click the Yearly Data sheet tab.
g. Click the Data tab, click Relationships in the Data Tools group to open the Manage Relationships dialog box, and then and complete the following steps:
• Click New to open the Create Relationship dialog box.
• Click the Table arrow and select Revenue (the main table). Click the Column (Foreign) arrow and select Weekday.
• Click the Related Table arrow and select Days. Click the Related Column (Primary) arrow and select Weekday.
• Click OK to close the Create Relationship dialog box. Click Close to close the Manage Relationships dialog box.
h. Complete the following steps to create a Pivot Table using the related tables:
• Click the Insert tab and click Pivot Table in the Tables group to open the Create Pivot Table dialog box.
• Click the Add this data to the Data Model check box in the Choose whether you want to analyze multiple tables section. Click OK. Double-click the Sheet1 tab, type Pivot Table, and then press Enter.
• Click ALL at the top of the Pivot Table Fields List to display all table names.
• Click Revenue at the top of the Pivot Table Fields List to display the fields for the Revenue table.
• Click the Lunch and Dinner check boxes in the Pivot Table Fields List to display these fields in the VALUES area.
• Click Days in the Pivot Table Fields List to display the fields for the Days table.
• Click the Day check box in the Pivot Table Fields List to add this field to the ROWS area.
i. Modify the PivotTable by doing the following:
• Click the Row Labels arrow in cell A3 and select Sort A to Z. (Note that this action sorts in sequential order by weekday, not alphabetical order by weekday name.)
• Type Weekday in cell A3 and press Enter.
• Click the Design tab, click the More button in the Pivot Table Styles group, and then click Pivot Style Light 17.
• Click the Banded Rows check box in the Pivot Table Style Options group.
j. Format the values by doing the following:
• Click cell B4, click the Analyze tab, and then click Field Settings in the Active Field group.
• Type Lunch Revenue in the Custom Name box.
• Click Number Format, click Accounting, click the Decimal places arrow to display 0 click OK in the Format Cells dialog box, and then click OK in the Value Field Settings dialog box.
• Click cell C4 and click Field Settings in the Active Field group.
• Type Dinner Revenue in the Custom Name box.
• Click Number Format, click Accounting, click the Decimal places arrow to display O. click OK in the Format Cells dialog box, and then click OK in the Value Field Settings dialog box.
• Click the Pivot Table Name box in the Pivot Table group on the Analyze tab, type Weekday Revenue, and then press Enter.
k. Insert a timeline by completing the following steps:
• Click Insert Timeline in the Filter group to open the Insert Timelines dialog box.
• Click the Date check box and click OK to display the Date timeline. Move the Date timeline so that the top-left corner starts in cell A13.
• Click the MONTHS arrow in the Date timeline and select QUARTERS.
• Click the tile below 04 in the timeline to filter the data to reflect weekday totals for the fourth quarter only (October through December).
l. Create a PivotChart from the Pivot Table by doing the following.
• Right-click the Pivot Table sheet tab, select Move or Copy, click Pivot Table in the Before sheet list, click the Create a copy check box to select it, and then click OK.
• Ensure that the Pivot Table (2) sheet tab is active. Click the Date timeline window and press Delete.
• Click the Analyze tab, click PivotChart in the Tools group, and then click OK in the Insert Chart dialog box to create a default clustered column chart.
• Click the Day arrow in the bottom-left corner of the PivotChart, click the Friday and Saturday check boxes to deselect these weekdays so that you can focus on the other days of the week where sales are lower. Click OK
• Click the Shape Height box in the Size group on the Format tab, type 3.5, and then press Enter. Click in the Shape Width box, type 6, and then press Enter
• Click CHART ELEMENTS on the right of the chart, click the Chart Title check box, and click CHART ELEMENTS to close the menu.
• Click the Chart Title placeholder, type 2018 Revenue, and then press Enter.
• Move the chart so that the top-left corner starts in cell A12.
• Click the Analyze tab, click the Field Buttons arrow in the Show/Hide group, and then select Hide All to hide the buttons within the chart area.
m. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on each worksheet.
n. Save and close the file. Based on your instructor's directions, submit e05pl Revenue_LastFirst.
Login | Sign Up
